MySQL使用MAX、WHERE和GROUP BY语句查询速度过慢问题求助

问题核心原因 #

现有联合索引created_at_machine_id_idx(created_at,machine_id)顺序不符合查询逻辑：MySQL仅能利用该索引前缀过滤created_at <= '2021-11-14 07:45:00'的条件，需要扫描112万行后再过滤machine_id，最后还要做临时表分组、文件排序操作，是耗时高的核心原因。 #

优化方案1：调整联合索引顺序（最优通用方案） #

创建符合查询特征的覆盖联合索引，顺序遵循「等值/IN查询列 → 范围查询列 → 查询返回列」的原则：

CREATE INDEX idx_mid_created_id ON `table` (machine_id, created_at, id);

索引生效逻辑：

1. 最左前缀匹配machine_id IN (...)的条件，相同machine_id的索引数据连续存储，无需跨节点扫描
2. 后续created_at前缀可直接过滤满足时间条件的行
3. 最后id列已包含在索引中，实现覆盖索引无需回表查询
   调整索引后再执行原SQL，EXPLAIN结果会消除Using temporary和Using filesort，扫描行数降低到数万级别，耗时可压缩到百毫秒级。

优化方案2：SQL改写（适合IN列表元素较少的场景） #

如果IN列表中的machine_id数量不超过50个，可以将SQL改写为逐个machine_id查询的形式，完全避免分组排序开销：

SELECT 
    (SELECT MAX(id) FROM `table` WHERE machine_id = m.mid AND created_at <= '2021-11-14 07:45:00') AS id,
    m.mid AS machine_id
FROM (
    SELECT 30 AS mid UNION ALL SELECT 31 UNION ALL SELECT 43 UNION ALL SELECT 44 -- 补全所有IN列表中的machine_id
) AS m;

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