mysql - 使用 Group By 和 Count 优化查询

Question

希望获得优化以下查询的建议，因为目前首次运行大约需要 20 秒的处理时间，具体取决于选择的用户数量和日期范围。

目的是返回每个用户的每个代码的计数。

有 27 个代码，每个用户都有上午和下午的考勤记录。

SELECT user,
    code,
    COUNT(code) AS total
FROM attendances AS attendances
WHERE attendances.user IN ('abc123', 'abc456', 'abc789')
    AND (
        attendances.date >= '2019-10-06' AND attendances.date <= '2019-10-11'
    )
GROUP BY user, code

表定义

索引

每个字段都有一个索引

location
source_id
date
user_recorded
code
user

解释结果

Answer 1

对于此查询:

SELECT user, code,
       COUNT(code) AS total
FROM attendances a
WHERE a.user IN ('abc123', 'abc456', 'abc789') AND
      a.date >= '2019-10-06' AND a.date <= '2019-10-11'
GROUP BY user, code;

你有问题。 user 和 date 上的索引不会在索引中使用(在 MySQL 中)。

一种方法是联合所有与(用户、日期、代码)上的索引:

(SELECT user, code, COUNT(*) AS total
 FROM attendances a
 WHERE a.user = 'abc123'
       a.date >= '2019-10-06' AND a.date <= '2019-10-11'
 GROUP BY user, code
) UNION ALL
(SELECT user, code, COUNT(*) AS total
 FROM attendances a
 WHERE a.user = 'abc456'
       a.date >= '2019-10-06' AND a.date <= '2019-10-11'
 GROUP BY user, code
) UNION ALL
(SELECT user, code, COUNT(*) AS total
 FROM attendances a
 WHERE a.user = 'abc789'
       a.date >= '2019-10-06' AND a.date <= '2019-10-11'
 GROUP BY user, code
);

这可以直接使用索引，并且速度应该快得多。