SQL 优化案例一则

转载

叶金荣 2021-07-27 15:17:58

文章标签 其它 文章分类 MySQL 数据库

导读：

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

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

擅长SQL优化，数据核对

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

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

背景说明：

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

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

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

SQL 优化案例一则_其它_02

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

表结构如下：

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;

执行计划如下图所示：

SQL 优化案例一则_其它_03

原文中有如下语言：

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

原文所示执行计划：

SQL 优化案例一则_其它_04

思路：

从以上条件推测，应该是 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: rangepossible_keys: syctimeday_index,osource_index          key: syctimeday_index      key_len: 48          ref: NULL         rows: 1     filtered: 100.00        Extra: Using index condition1 row in set, 1 warning (0.00 sec)

优化过程：

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

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

SQL 优化案例一则_其它_05

我们先看下执行计划，这里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: rangepossible_keys: syctimeday_index,osource_index          key: syctimeday_index      key_len: 4          ref: NULL         rows: 1     filtered: 100.00        Extra: Using index condition1 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: indexpossible_keys: syctimeday_index,osource_index,ix_index          key: ix_index      key_len: 82          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)