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

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

现在数据库有一张订单表(经过简化的中间表),表结构如下:

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:

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如下:

 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’的时候……

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

通过执行计划发现,虽然扫描的行数变多了,但是只进行了一次全表扫描,而且是simple简单查询,所以执行效率自然就高了:

针对这个问题,如果大家有更好的方案或思路,欢迎留言

总结

到此这篇关于mysql巧用sum、case和when优化统计查询的文章就介绍到这了,更多相关mysql优化统计查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐