MySQL巧⽤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上加上索引之后，效率提⾼了很多，⼤概五秒钟就查询出了结果：
查看执⾏计划发现扫描的⾏数减少了很多，不再进⾏全表扫描了：
mysql group by order by
这当然还不够快，如果当数据量达到百万级别的话，查询速度肯定是不能容忍的。⼀直在想有没有⼀种办法，能否直接遍历⼀次就查询出所有的结果，类似于遍历java中的list集合，遇到某个条件就计数⼀次，这样进⾏⼀次全表扫描就可以查询出结果集，结果索引，效率应该会很⾼。在⽼⼤的指引下，利⽤sum聚合函数，加上这种“陌⽣”的⽤法，有效的解决了这个问题。
具体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中hen的⽤法就不做过多的解释了，这条SQL很容易理解，先对⼀条⼀条记录进⾏遍历，group by 对⽇期进⾏了分类，sum聚合函数对某个⽇期的值进⾏求和，重点就在于hen对sum的求和巧妙的加⼊了条件，当o_source = 'CDE'的时候，计数为1，否则为0；当o_source='SDE'的时候......
这条语句的执⾏只花了⼀秒多，对于五⼗多万的数据进⾏这样⼀个维度的统计还是⽐较理想的。
通过执⾏计划发现，虽然扫描的⾏数变多了，但是只进⾏了⼀次全表扫描，⽽且是SIMPLE简单查询，所以执⾏效率⾃然就⾼了：
针对这个问题，如果⼤家有更好的⽅案或思路，欢迎留⾔
总结
到此这篇关于MySQL巧⽤sum、case和when优化统计查询的⽂章就介绍到这了，更多相关MySQL优化统计查询内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。