大家好,我是知数堂SQL 优化班老师 网名:骑龟的兔子
今天在刷头条的时候,看到下的文章
SQL优化:用case...when优化统计查询www.toutiao.com在下面留下如下留言,结果对方也同意了,就在这写下交流文章
为了阅读方便,把需要的资料引用到这里。
表结构如下
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 分别加上单列索引
create index syctimeday_index on statistic_order(syctime_day);
create index osource_index on statistic_order(o_source);
然后重现了差不多的执行计划
好了,现在开始,对这个优化开始二次修改了!!
我们先看下原来的执行计划
这里面的type为index 说明进行了索引全扫描,这里的索引只是起到了在group by 减少排序的作用,还有extra 部分是 using where
说明什么呢,说明这个数据库版本有可能是5.5!!! 或者
在实例中关掉了ICP !!
如果开5.6之后的版本加开ICP的话 这个SQL 应该是
如上图所示的执行计划 !!!
现在进行最后一步,我们从上面的执行计划中,可以看到这个where条件的过滤性很一般
那我们索性为了优化的话 创建联合索引如下
create index ix_index on statistic_order(syctime_day,o_source);
创建之后的执行计划如下
这就是我的最终的优化效果!!
谢谢大家~ 欢迎转发
我的微信公众号:SQL开发与优化(sqlturning)
我会在头条提供相应例子的视频,希望大家关注