MySQL新手求助：如何高效优化日期统计查询语句？

嘿，作为MySQL新手能写出这样的统计查询已经很不错啦！不过你当前的写法确实有两个明显的问题：一是对MYTABLE做了4次全表扫描（3个子查询+外层的FROM MYTABLE），数据量越大效率越低；二是外层的FROM MYTABLE会让结果重复输出N行（N是表的行数），这完全没必要。咱们来一步步优化：

最推荐：用条件聚合（仅扫描一次表） #

这是最高效的写法，只需要对表做一次扫描，通过CASE语句实现按条件统计：

SELECT
  COUNT(CASE WHEN cr_dt >= '2018-04-01' AND cr_dt < '2018-05-01' THEN user_id END) AS April,
  COUNT(CASE WHEN cr_dt >= '2018-03-01' AND cr_dt < '2018-04-01' THEN user_id END) AS March,
  COUNT(CASE WHEN cr_dt >= '2018-02-01' AND cr_dt < '2018-03-01' THEN user_id END) AS February
FROM MYTABLE;

几点说明： #

• CASE语句满足条件时返回user_id，COUNT()会自动忽略NULL值（不满足条件的CASE返回NULL），刚好实现按月份统计。
• 把cr_dt > '2018-04'改成>= '2018-04-01'是为了消除歧义——MySQL会把'2018-04'隐式转换为'2018-04-01'，但明确写日期更清晰。
• 如果user_id不会为NULL，用COUNT(*)代替COUNT(user_id)会更高效，因为COUNT(*)不需要检查字段是否为空。

可选：先分组再按需转列 #

如果需要统计更多月份，先按月份分组统计再处理也是个不错的选择（返回行格式的结果，若需要列格式可以再转）：

SELECT
  DATE_FORMAT(cr_dt, '%Y-%m') AS month,
  COUNT(user_id) AS user_count
FROM MYTABLE
WHERE cr_dt >= '2018-02-01' AND cr_dt < '2018-05-01'
GROUP BY DATE_FORMAT(cr_dt, '%Y-%m')
ORDER BY month;

关键加速：给cr_dt加索引 #

不管用哪种写法，给cr_dt字段创建索引都能大幅提升查询速度，尤其是当表数据量很大的时候：

CREATE INDEX idx_mytable_cr_dt ON MYTABLE(cr_dt);

这样改完之后，不仅查询效率上去了，结果也不会重复输出。如果还有其他疑问，随时问哦！

内容的提问来源于stack exchange，提问作者kyun