MySQL巧用sum、case和when优化统计查询

吾爱主题阅读：162 2024-04-02 08:00:41 评论：0

最近在公司做项目，涉及到开发统计报表相关的任务，由于数据量相对较多，之前写的查询语句查询五十万条数据大概需要十秒左右的样子，后来经过老大的指点利用sum,case...when...重写sql性能一下子提高到一秒钟就解决了。这里为了简洁明了的阐述问题和解决的方法，我简化一下需求模型。

现在数据库有一张订单表（经过简化的中间表），表结构如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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

项目需求是这样的:

统计某段时间范围内每天的来源编号数量，其中来源编号对应数据表中的o_source字段，字段值可能为cde,sde,pde,cse,sse。

来源分类随时间流动

一开始写了这样一段sql：

1 2 3 4 5 6 7 8 select s.syctime_day, ( select count (*) from statistic_order ss where ss.syctime_day = s.syctime_day and ss.o_source = 'cde' ) as 'cde' , ( select count (*) from statistic_order ss where ss.syctime_day = s.syctime_day and ss.o_source = 'cde' ) as 'sde' , ( select count (*) from statistic_order ss where ss.syctime_day = s.syctime_day and ss.o_source = 'cde' ) as 'pde' , ( select count (*) from statistic_order ss where ss.syctime_day = s.syctime_day and ss.o_source = 'cde' ) as 'cse' , ( select count (*) from statistic_order ss where ss.syctime_day = s.syctime_day and ss.o_source = 'cde' ) as 'sse' from statistic_order s where s.syctime_day > '2016-05-01' and s.syctime_day < '2016-08-01' group by s.syctime_day order by s.syctime_day asc ;

这种写法采用了子查询的方式，在没有加索引的情况下，55万条数据执行这句sql，在workbench下等待了将近十分钟，最后报了一个连接中断，通过explain解释器可以看到sql的执行计划如下：

每一个查询都进行了全表扫描，五个子查询dependent subquery说明依赖于外部查询，这种查询机制是先进行外部查询，查询出group by后的日期结果，然后子查询分别查询对应的日期中cde，sde等的数量，其效率可想而知。

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

查看执行计划发现扫描的行数减少了很多，不再进行全表扫描了：

这当然还不够快，如果当数据量达到百万级别的话，查询速度肯定是不能容忍的。一直在想有没有一种办法，能否直接遍历一次就查询出所有的结果，类似于遍历java中的list集合，遇到某个条件就计数一次，这样进行一次全表扫描就可以查询出结果集，结果索引，效率应该会很高。在老大的指引下，利用sum聚合函数，加上case...when...then...这种“陌生”的用法，有效的解决了这个问题。
具体sql如下：

1 2 3 4 5 6 7 8 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 ;

关于mysql中case...when...then的用法就不做过多的解释了，这条sql很容易理解，先对一条一条记录进行遍历，group by对日期进行了分类，sum聚合函数对某个日期的值进行求和，重点就在于case...when...then对sum的求和巧妙的加入了条件，当o_source = 'cde'的时候，计数为1，否则为0；当o_source='sde'的时候......

这条语句的执行只花了一秒多，对于五十多万的数据进行这样一个维度的统计还是比较理想的。