SQL 优化案例一则-蒲公英云

导读：

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

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

擅长SQL优化，数据核对

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

$640?wx\_fmt=png$

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

背景说明：

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

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

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

$640?wx\_fmt=png$

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

表结构如下：

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;

执行计划如下图所示：

$640?wx\_fmt=jpeg$

原文中有如下语言：

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

原文所示执行计划：

$640?wx\_fmt=jpeg$

思路：

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

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

重现了类似执行计划：

root@mysql3306.sock>[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)

优化过程：

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

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

$640?wx\_fmt=jpeg$

我们先看下执行计划，这里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
root@mysql3306.sock>[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);

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

root@mysql3306.sock>[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)