MySql 性能调优之case when then

1 解释：

SELECT            
    case                   -------------如果
    when sex='1' then '男' -------------sex='1'，则返回值'男'
    when sex='2' then '女' -------------sex='2'，则返回值'女'  
    else 0              -------------其他的返回'其他’                                                                             
    end                 -------------结束                                                                                                 
   from   sys_user    --------整体理解： 在sys_user表中如果sex='1'，则返回值'男'如果sex='2'，则返回值'女' 否则返回'其他’

---用法一：

SELECT 
            CASE WHEN STATE = '1' THEN '成功' 
                 WHEN STATE = '2' THEN '失败'
            ELSE '其他' END  
            FROM  SYS_SCHEDULER  

---用法二：

SELECT STATE
            CASE WHEN '1' THEN '成功' 
                 WHEN '2' THEN '失败'
            ELSE '其他' END  
            FROM  SYS_SCHEDULER

2 场景

最近在做项目,涉及到开发统计报表相关的任务，由于数据量相对较多，之前写的查询语句查询五十万条数据大概需要十秒左右的样子，后来利用sum,case...when...重写SQL性能一下子提高到一秒钟就解决了。这里为了简洁明了的阐述问题和解决的方法，我简化一下需求模型。 在平常开发中，我们常常会遇到这样的一些统计的需求，通过统计有效数据来指导公司的运营，并计划未来的发展。 比如我们有一张订单表，当payment_method = 1 的时候 为支付宝，type=2的时候为微信支付。

CREATE TABLE  mall_order (
  `id` varchar(32) NOT NULL COMMENT '商品订单号',
  `title` varchar(100) DEFAULT NULL COMMENT '商品订单标题',
  `total_fee` bigint(20) DEFAULT '0' COMMENT '总金额',
  `create_time` datetime DEFAULT NULL COMMENT '创建商品订单的完整时间',
  `type` int(11) DEFAULT '1' COMMENT '类型',
  `shop_id` bigint(20) NOT NULL COMMENT '商品订单所属商户',
  `shop_name` varchar(100) DEFAULT NULL COMMENT '商户名称',
  `shop_serial` varchar(32) DEFAULT NULL COMMENT '商户编号',
  `status` int(11) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `source` int(11) NOT NULL DEFAULT '0' COMMENT '订单来源',
  `payment_method` int(11) NOT NULL DEFAULT '1' COMMENT '订单支付方式',
  `receive_goods_method` int(11) NOT NULL DEFAULT '0' COMMENT '收货方式',
  `deliver_fee` bigint(20) NOT NULL DEFAULT '0' COMMENT '快递费',
  `update_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
  `pay_deadline` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '发起支付的截止时间',
  `locked` tinyint(4) NOT NULL DEFAULT '0' COMMENT '优惠锁定标识，1：已锁定；0:未锁定',
  PRIMARY KEY (`id`),
  KEY `idx_shop_time` (`shop_id`,`create_time`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_shop_branch_time` (`shop_branch_id`,`create_time`) USING BTREE,
  KEY `idx_shop_customer` (`shop_id`,`customer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品订单'

如果我们写一个这样的统计sql:

SELECT 
  (SELECT SUM(total_fee) FROM mall_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 1) AS 'zhifubaoTotalOrderAmount',
  (SELECT COUNT(*) FROM mall_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 1) AS 'zhifubaoTotalOrderNum',
  (SELECT SUM(total_fee) FROM mall_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 2) AS 'weixinTotalOrderAmount',
  (SELECT COUNT(*) FROM mall_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 2) AS 'weixinTotalOrderNum'
 FROM mall_order S WHERE S.create_time > '2016-05-01' AND S.create_time < '2016-08-01' 
 GROUP BY S.create_time ORDER BY S.create_time ASC;

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

EXPLAIN SELECT 
  (SELECT SUM(total_fee) FROM product_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 1) AS 'zhifubaoTotalOrderAmount',
  (SELECT COUNT(*) FROM product_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 1) AS 'zhifubaoTotalOrderNum',
  (SELECT SUM(total_fee) FROM product_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 2) AS 'weixinTotalOrderAmount',
  (SELECT COUNT(*) FROM product_order SS WHERE SS.create_time = S.create_time AND SS.payment_method = 2) AS 'weixinTotalOrderNum'
 FROM product_order S 
 GROUP BY S.create_time ORDER BY S.create_time ASC;

此图为加了create_time 索引后的查询，查看执行计划发现扫描的行数减少了很多，不再进行全表扫描了：

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

如果当数据量达到百万级别的话，查询速度肯定是不能容忍的。一直在想有没有一种办法，能否直接遍历一次就查询出所有的结果，类似于遍历java中的list集合，遇到某个条件就计数一次，这样进行一次全表扫描就可以查询出结果集，结果索引，效率应该会很高， 同时在统计的时候避免多次查询。

为此，我们可以利用利用sum聚合函数，加上case...when...then...这种“陌生”的用法，有效的解决了这个问题。

 select S.create_time,
   sum(case when S.payment_method =1 then 1 else 0 end) as 'zhifubaoOrderNum',
   sum(case when S.payment_method =1 then total_fee else 0 end) as 'zhifubaoOrderAmount',
   sum(case when S.payment_method =2 then 1 else 0 end) as 'weixinOrderNum',
   sum(case when S.payment_method =2 then total_fee else 0 end) as 'weixinOrderAmount'
 from mall_order S where S.create_time > '2015-05-01' and S.create_time < '2016-08-01' 
 GROUP BY S.create_time order by S.create_time asc;

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

天： DATE_FORMAT(po.create_time,'%Y-%m-%d') AS statDate 
周： DATE_FORMAT(DATE_SUB(po.create_time,INTERVAL WEEKDAY(po.create_time) DAY),'%Y-%m-%d') AS statDate
月：DATE_FORMAT(po.create_time,'%Y-%m') AS statDate

mysql 函数：

select curdate();                       -- 获取当前日期
select DATE_ADD(curdate(),interval -day(curdate())+1 day)   -- 获取本月第一天
select last_day(curdate());   -- 获取当月最后一天
select date_add(curdate()-day(curdate())+1,interval 1 month ) -- 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual -- 获取当前月的天数

注：spring-mvc 中，SQlMap 再是用获取本月第一天时引用以下语句 ：

SELECT DATE_ADD(CURDATE(),INTERVAL 1-DAYOFMONTH(CURDATE()) DAY); -- 获取本月第一天

3 本期荐书：

豆瓣：https://book.douban.com/subject/25801248/

转载于:https://my.oschina.net/u/1187675/blog/1555760