SQL 优化案例一则

た入场券 2023-06-05 11:22 14阅读 0赞

**导读：**

> 郑松华，知数堂SQL 优化班老师 
> 
> 现任 CCmediaService DBA，主要负责数据库优化相关工作
> 
> 擅长SQL优化 ，数据核对

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

![640?wx\_fmt=png][640_wx_fmt_png]

--------------------

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

**背景说明：**

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

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

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

![640?wx\_fmt=png][640_wx_fmt_png 1]

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

**表结构如下：**

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][640_wx_fmt_jpeg]

**原文中有如下语言：**

**在o\_source和syctime\_day上加上索引之后，效率提高了很多，大概五秒钟就查询出了结果：**

**原文所示执行计划 ：**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 1]

**思路：**

从以上条件推测，应该是 **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][640_wx_fmt_jpeg]

我们先看下执行计划，这里**type index** ,还有**key\_len 为48 **

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

\`syctime\_day\` varchar(15) DEFAULT NULL

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

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

因为这样所占用空间就小

现在把数据类型更改为**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)

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

**结论：**

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

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

谢谢大家~ 欢迎转发

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

END

--------------------

**点击下图小程序订阅**

**《SQL优化专栏》**

**get更多优化技能**

![640?wx\_fmt=png][640_wx_fmt_png]

***![640?wx\_fmt=png][640_wx_fmt_png 2]***

![640?wx\_fmt=gif][640_wx_fmt_gif]

扫码加入MySQL技术Q群

**（群号：****579036588）**

![640?wx\_fmt=jpeg][640_wx_fmt_jpeg 2]

[640_wx_fmt_png]: /images/20230601/cd24fe7963534e7886b3cf47d08cede5.png
[640_wx_fmt_png 1]: /images/20230601/c4b3bbe54e5f4814b1cf2356f5a99221.png
[640_wx_fmt_jpeg]: /images/20230601/303c57801fd24b5aa1adf9cf84228e7e.png
[640_wx_fmt_jpeg 1]: /images/20230601/ff957489d5914c1686e75857fd917fea.png
[640_wx_fmt_png 2]: /images/20230601/07cad079613b4d7abd6742378c78c554.png
[640_wx_fmt_gif]: /images/20230601/f64ccf9ac3354be3b419478bb61292fa.png
[640_wx_fmt_jpeg 2]: /images/20230601/14e882a9fa5a403890c6bfe1a3ed7770.png