Mysql优化案例 多条sql合并 case union与索引的关系

我们是类似农场的业务，可以去好友的农场帮他铲屎，
于是有一张铲屎表shovel_chicken_shit_record。主要字段如下

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `helper_id` int(11) NOT NULL COMMENT '帮助者ID',
  `be_helped_id` int(11) NOT NULL COMMENT '被帮助者ID',
  `create_date` date DEFAULT NULL COMMENT '创建日期',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',

helper_id就是谁去铲屎（用户），be_helped_id就是给谁铲屎（好友）。
业务需求是，用户去给好友铲屎的时候，进行校验：

• 用户一天最多帮同一个好友铲一次鸡屎
• 用户一天最多帮五个好友铲鸡屎
• 好友一天最多被五个不同用户帮忙铲鸡屎

那么需要从数据库查出来的信息：

• 这个用户今天已经铲了几次屎
• 这个好友今天已经被铲了几次屎
• 这个用户今天有没有给这个好友铲屎

最初的sql是这样子的.

select count(1) from shovel_chicken_shit_record where helper_id = 89274 and create_date = CURRENT_DATE;

select count(1) from shovel_chicken_shit_record where be_helped_id = 89275 and create_date = CURRENT_DATE;

select count(1) from shovel_chicken_shit_record where helper_id = 89274 and be_helped_id and create_date = CURRENT_DATE;

经过了三次查询。我觉得三次查询对数据库的请求次数太多了，每次请求都会占用对数据库连接的资源，网络的时间，请求次数多了是不利于高性能的。并且这三个查询有共同的条件create_date，就想能不能把三条sql合并，减少请求次数。于是和同事交换了一下意见，他给出了如下sql。

select count(1) from shovel_chicken_shit_record where helper_id = 89274 and create_date = CURRENT_DATE
union all
select count(1) from shovel_chicken_shit_record where be_helped_id = 89275 and create_date = CURRENT_DATE
union all
select count(1) from shovel_chicken_shit_record where helper_id = 89274 and be_helped_id = 89275 and create_date = CURRENT_DATE;

一次请求查出所需的三条数据，返回的结果里按顺序就能取出 这个用户今天已经铲了几次屎、这个好友今天已经被铲了几次屎、这个用户今天有没有给这个好友铲屎 这些数据。

仔细分析了一下，请求虽然只有一次，但在mysql里还是执行了三次查询（执行计划也可以看出），同一行记录的数据会被扫描三次，即便是走的索引，索引数也会跑三次，有没有更快性能，每条记录只会被扫描一次的操作，于是有了如下sql

SELECT 
IFNULL(SUM(CASE WHEN helper_id = 89274 and be_helped_id = 89275 THEN 1 ELSE 0 END),0) as count,
IFNULL(SUM(CASE WHEN helper_id = 89274 THEN 1 ELSE 0 END),0) as helpCount, 
IFNULL(SUM(CASE WHEN be_helped_id = 89275 THEN 1 ELSE 0 END),0) as beHelpCount 
FROM shovel_chicken_shit_record 
WHERE create_date = CURRENT_DATE()

看执行计划只有一次查询。然后就使用了这个sql，创建了(create_date, helper_id) ， (create_date, be_helped_id) 两个联合索引。按照预想的，每条记录只会被扫描一次，并且在select里面会用到联合索引的第二个字段，实际这个预想是错误的，当时不懂得看执行计划的key_len字段，也不懂得把B+树的结构和实际情况结合分析。

线上运行了一段时间，一年后，发现了慢sql。超过200毫秒的执行，一小时内出现了1158次。

再次分析这个sql，key_len=4，create_date字段可以从索引树里匹配到一天8万左右的数据，然后无论是使用(create_date, helper_id) 还是 (create_date, be_helped_id) ，实际上一条查询里只会选择一条索引，如果后续选择的是helper_id，那么be_helped_id就不会走索引，然后就会回表查询，在这一天8万的数据里扫描每一条记录的be_helped_id是否匹配，选择另一条索引亦然。

所以这个sql虽然优化成了一次查询，但是却降低了索引的利用率，我们只有选择再改成union all查询，虽然这意味着多次查询，但是能提升索引。很多事情好像都是如此，无法十全十美。

最后我们再做了一点改动，使用的是这个sql：

SELECT COUNT(IF(be_helped_id = 89275, 1, NULL)) AS count, COUNT(1) AS helpCount, 0 AS beHelpCount
FROM shovel_chicken_shit_record WHERE create_date = CURDATE() AND helper_id = 89274
UNION ALL
SELECT 0 AS count, 0 AS helpCount, COUNT(1) AS beHelpCount
FROM shovel_chicken_shit_record WHERE create_date = CURDATE() AND be_helped_id = 89275

count字段总会命中(create_date, helper_id) 或者 (create_date, be_helped_id) 的其中一个索引，索引可以把它的select放在其中一条查询里。