SQL 优化案例一则

导读：

郑松华，知数堂SQL 优化班老师 

现任 CCmediaService DBA，主要负责数据库优化相关工作

擅长SQL优化 ，数据核对

想阅读更多内容请点击订阅专栏

---

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

背景说明：

今天在刷头条的时候，看到下面的文章

https://www.toutiao.com/a6727944177943839243/

看到他的SQL还有优化的空间，经过他的同意写下这篇文章

为了阅读方便，把需要的资料引用到这里。

表结构如下：

CREATE TABLE `statistic_order` (
 `oid` bigint(20) NOT NULL,
 `o_source` varchar(25) DEFAULT NULL COMMENT '来源编号',
 `o_actno` varchar(30) DEFAULT NULL COMMENT '活动编号',
 `o_actname` varchar(100) DEFAULT NULL COMMENT '参与活动名称',
 `o_n_channel` int(2) DEFAULT NULL COMMENT '商城平台',
 `o_clue` varchar(25) DEFAULT NULL COMMENT '线索分类',
 `o_star_level` varchar(25) DEFAULT NULL COMMENT '订单星级',
 `o_saledep` varchar(30) DEFAULT NULL COMMENT '营销部',
 `o_style` varchar(30) DEFAULT NULL COMMENT '车型',
 `o_status` int(2) DEFAULT NULL COMMENT '订单状态',
 `syctime_day` varchar(15) DEFAULT NULL COMMENT '按天格式化日期',
 PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

原文作者优化后的SQL 版本如下：

select S.syctime_day,
 sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
 sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
 sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
 sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
 sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
 from statistic_order S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01'
 GROUP BY S.syctime_day order by S.syctime_day asc;

执行计划如下图所示：

原文中有如下语言：

在o_source和syctime_day上加上索引之后，效率提高了很多，大概五秒钟就查询出了结果：

原文所示执行计划 ：

思路：

从以上条件推测，应该是 o_source 和syctime_day 分别加上单列索引

create index syctimeday_index on statistic_order(syctime_day);
 
create index osource_index on statistic_order(o_source);

重现了类似执行计划：

[e-mail]>[test3]>desc select S.syctime_day,
    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
    ->  from statistic_order S where S.syctime_day > '2015-05-01' 
  ->  and S.syctime_day < '2016-08-01'
    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: S
   partitions: NULL
         type: range
possible_keys: syctimeday_index,osource_index
          key: syctimeday_index
      key_len: 48
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

优化过程：

下面开始对这个优化开始二次修改：

我们先看下原来的执行计划

我们先看下执行计划，这里type index ,还有key_len 为48 

从表结构中有如下字段定义

  `syctime_day` varchar(15) DEFAULT NULL

我们定义表结构的时候，最好是，什么数据类型定义什么类型

这里如果是日期类型就应该选用date 类型

因为这样所占用空间就小

类型	(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值

现在把数据类型更改为date 类型如下 ：

CREATE TABLE `statistic_order2` (
  `oid` bigint(20) NOT NULL,
  `o_source` varchar(25) DEFAULT NULL,
  `o_actno` varchar(30) DEFAULT NULL,
  `o_actname` varchar(100) DEFAULT NULL,
  `o_n_channel` int(2) DEFAULT NULL,
  `o_clue` varchar(25) DEFAULT NULL,
  `o_star_level` varchar(25) DEFAULT NULL,
  `o_saledep` varchar(30) DEFAULT NULL,
  `o_style` varchar(30) DEFAULT NULL,
  `o_status` int(2) DEFAULT NULL,
  `syctime_day` date DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `syctimeday_index` (`syctime_day`),
  KEY `osource_index` (`syctime_day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[e-mail]>[test3]>desc select S.syctime_day,
    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
    ->  from statistic_order2 S where S.syctime_day > '2015-05-01' and S.syctime_day < '2016-08-01'
    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: S
   partitions: NULL
         type: range
possible_keys: syctimeday_index,osource_index
          key: syctimeday_index
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

就会发现 key_len 为 4  

还有一个从原来的执行计划的fiter为50可以看出范围太大，就会发生大量的回表操作，也是一个相对负担的操作，那为了优化创建联合索引如下：

create index ix_index on statistic_order2(syctime_day,o_source);

创建之后的执行计划如下：

 [e-mail]>[test3]>desc select S.syctime_day,
    ->  sum(case when S.o_source = 'CDE' then 1 else 0 end) as 'CDE',
    ->  sum(case when S.o_source = 'SDE' then 1 else 0 end) as 'SDE',
    ->  sum(case when S.o_source = 'PDE' then 1 else 0 end) as 'PDE',
    ->  sum(case when S.o_source = 'CSE' then 1 else 0 end) as 'CSE',
    ->  sum(case when S.o_source = 'SSE' then 1 else 0 end) as 'SSE'
    ->  from statistic_order2 S where S.syctime_day+0 > '2015-05-01' and S.syctime_day+0 < '2016-08-01'
    ->  GROUP BY S.syctime_day order by S.syctime_day asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: S
   partitions: NULL
         type: index
possible_keys: syctimeday_index,osource_index,ix_index
          key: ix_index
      key_len: 82
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

这样就达到了我所要的最终的优化效果！！

结论：

如果在不更改表结构的情况下，创建ix_index 这个索引，这样也可以达到优化效果。

因为本案例的整体的表的数据量不大，改变列的属性达到的效果差不多，但是随着数据量的增加，差距就会更加明显。

谢谢大家~ 欢迎转发

如有关于SQL优化方面疑问需要交流的，请加入QQ群（579036588），并@骑兔子的龟 就可与我联系

END

---

点击下图小程序订阅

《SQL优化专栏》

get更多优化技能

知数堂

这里有好课，为职场助攻

现招聘课程销售助理数枚

不限地区，不限年龄

若您懂“数”又自带流量

欢迎投简历加入我们

扫码了解岗位详情

扫码加入MySQL技术Q群

（群号：579036588）