从 8 秒到 300ms：GROUP BY 慢查询改写实战

问题背景：GROUP BY 为什么会突然变慢

在订单、日志、埋点等业务表中，按天、按状态、按用户分组统计非常常见。很多 SQL 写法本身没有语法问题，但随着数据量增长，GROUP BY 会扫描大量重复索引记录，再做临时表或文件排序，最终表现为：CPU 飙升、慢查询增多、业务报表超时。

先构造一张可复现的订单明细表：

CREATE TABLE order_event (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  tenant_id INT NOT NULL,
  order_status TINYINT NOT NULL,
  event_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  KEY idx_tenant_status_date (tenant_id, order_status, event_date)
) ENGINE=InnoDB;

-- 向表中写入约 100 万行测试数据
-- tenant_id：1 到 100
-- order_status：0 到 4
-- event_date：2024-01-01 到 2024-12-31
INSERT INTO order_event(tenant_id, order_status, event_date, amount)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT
  1 + MOD(n, 100),
  MOD(n, 5),
  DATE_ADD('2024-01-01', INTERVAL MOD(n, 365) DAY),
  ROUND(10 + MOD(n, 990) / 10, 2)
FROM seq;

典型慢查询如下：

SELECT tenant_id, order_status, MAX(event_date) AS last_date
FROM order_event
WHERE tenant_id = 1
GROUP BY tenant_id, order_status;

这条 SQL 的过滤字段和分组字段都在联合索引中，看起来应该很快，但实际仍可能扫描某个租户下的上万条记录。原因是普通索引扫描需要遍历该租户下所有状态的索引项，再完成聚合；而理想做法是每个分组只读取第一条或最后一条索引记录，这就是松散索引扫描的核心价值。

执行路径对比：三种 GROUP BY 优化方式

MySQL 处理 GROUP BY 时，常见路径有三类。

方案	扫描方式	适合场景	主要限制
全表或普通索引扫描	扫描满足条件的大量行	分组选择性低、聚合值覆盖广	扫描行数多，容易产生临时表
紧凑索引扫描	利用索引有序性减少排序	索引顺序与 GROUP BY 基本一致	仍可能遍历大量重复分组
松散索引扫描	跳跃读取每个分组的边界记录	MIN/MAX、DISTINCT、少量分组列	对索引前缀、谓词和聚合函数要求严格

下面这张图展示了普通扫描与松散扫描的差异：

松散索引扫描：让索引按分组跳跃

对于下面这种查询，分组列是索引的前两列，聚合列是索引第三列：

SELECT tenant_id, order_status, MAX(event_date) AS last_date
FROM order_event
WHERE tenant_id = 1
GROUP BY tenant_id, order_status;

已有索引：

KEY idx_tenant_status_date (tenant_id, order_status, event_date)

在 MySQL 8.0 中，可以通过 EXPLAIN ANALYZE 查看实际执行情况：

EXPLAIN ANALYZE
SELECT tenant_id, order_status, MAX(event_date) AS last_date
FROM order_event
WHERE tenant_id = 1
GROUP BY tenant_id, order_status;

重点关注：

• rows 是否接近实际分组数量，而不是租户下全部记录数；
• Extra 中是否出现 Using index for group-by；
• 是否出现 Using temporary 或 Using filesort；
• actual loops、actual rows 与耗时分布。

如果优化器选择普通索引扫描，可以先收集统计信息，再验证执行计划：

ANALYZE TABLE order_event;

EXPLAIN
SELECT tenant_id, order_status, MAX(event_date) AS last_date
FROM order_event
WHERE tenant_id = 1
GROUP BY tenant_id, order_status;

需要强调，松散索引扫描不是加了联合索引就必然发生。它通常要求查询只检索索引列，GROUP BY 列满足最左前缀，且聚合形式适合边界读取，例如 MIN(column)、MAX(column)。如果写成 COUNT(*)、SUM(amount)，即使索引有序，也往往需要读取每个分组内的大量记录，不能指望松散扫描完成全部聚合。

派生表下推改写：先缩小分组范围，再回表补列

真实业务通常不会只查 MAX(event_date)，还需要订单金额、备注、创建时间等字段。此时直接按明细列分组，或者用非索引列排序，很容易破坏松散扫描。更实用的写法是：先用派生表或通用表表达式在窄索引上完成分组定位，再回主键关联原表补充数据。

例如，要查询每个租户、每种状态最近一天的订单金额：

SELECT t.tenant_id, t.order_status, t.event_date, t.amount
FROM order_event AS t
JOIN (
    SELECT tenant_id, order_status, MAX(event_date) AS max_date
    FROM order_event
    WHERE tenant_id BETWEEN 1 AND 20
    GROUP BY tenant_id, order_status
) AS g
  ON t.tenant_id = g.tenant_id
 AND t.order_status = g.order_status
 AND t.event_date = g.max_date;

内层派生表只访问 (tenant_id, order_status, event_date) 这个覆盖索引，负责快速得到每个分组的最大日期；外层再用三列条件回表。若一天内可能存在多条订单，需要进一步保证唯一性：

SELECT t.*
FROM order_event AS t
JOIN (
    SELECT MAX(id) AS max_id
    FROM order_event
    WHERE tenant_id BETWEEN 1 AND 20
    GROUP BY tenant_id, order_status, event_date
) AS g
  ON t.id = g.max_id;

但这条 SQL 的分组包含 event_date，无法直接得到每个状态的最新日期。可以拆成两层：第一层找最大日期，第二层找该日期下最大主键。

SELECT t.*
FROM order_event AS t
JOIN (
    SELECT MAX(id) AS max_id
    FROM order_event AS t1
    JOIN (
        SELECT tenant_id, order_status, MAX(event_date) AS max_date
        FROM order_event
        WHERE tenant_id BETWEEN 1 AND 20
        GROUP BY tenant_id, order_status
    ) d
      ON t1.tenant_id = d.tenant_id
     AND t1.order_status = d.order_status
     AND t1.event_date = d.max_date
    GROUP BY t1.tenant_id, t1.order_status, t1.event_date
) x
  ON t.id = x.max_id;

MySQL 8.0 对派生表有 derived table merge、condition filter 等优化能力，但并非所有派生表都会合并。包含 GROUP BY、DISTINCT、聚合函数的派生表通常会物化为内部临时表，再与外层表连接。所谓“下推”改写，本质是把高选择性的过滤条件和轻量聚合放进内层，让内层尽量使用覆盖索引，把昂贵的回表次数限制为分组数量。

可以用如下命令观察派生表策略：

EXPLAIN FORMAT=TREE
SELECT t.tenant_id, t.order_status, t.event_date, t.amount
FROM order_event AS t
JOIN (
    SELECT tenant_id, order_status, MAX(event_date) AS max_date
    FROM order_event
    WHERE tenant_id BETWEEN 1 AND 20
    GROUP BY tenant_id, order_status
) AS g
  ON t.tenant_id = g.tenant_id
 AND t.order_status = g.order_status
 AND t.event_date = g.max_date;

如果看到内层使用 idx_tenant_status_date，且外层按主键或高选择性索引查找，通常说明改写方向正确。

索引设计：GROUP BY、WHERE 与聚合列如何排序

联合索引设计不能只背“等值条件在前、排序列在后”，还要结合数据分布。对于：

WHERE tenant_id = ?
GROUP BY order_status
SELECT MAX(event_date)

推荐索引为：

ALTER TABLE order_event
ADD INDEX idx_tenant_status_date (tenant_id, order_status, event_date);

原因是：

1. tenant_id 是等值过滤，先固定索引前缀；
2. order_status 是分组列，在同一租户内天然有序；
3. event_date 是 MAX 聚合列，放在末尾可直接读取分组边界。

如果查询还经常按 event_date 做范围过滤，则要区分目标：

WHERE tenant_id = ?
  AND event_date >= ?
  AND event_date < ?
GROUP BY order_status

范围列放在分组列之后更利于分组有序性：

ALTER TABLE order_event
ADD INDEX idx_tenant_status_date2 (tenant_id, order_status, event_date);

但如果业务主要按日期范围扫描，分组只是次要需求，则可能更适合：

ALTER TABLE order_event
ADD INDEX idx_tenant_date_status (tenant_id, event_date, order_status);

没有万能索引顺序，关键是用 EXPLAIN ANALYZE 比较扫描行数、回表次数和临时表成本。

避坑总结：慢查询改写中最容易犯的错

1. 只看 key 命中索引，忽略扫描行数

常见错误现象是 EXPLAIN 显示使用了联合索引，但查询仍然耗时数秒。命中索引不等于高效，如果 rows 接近整个租户的全部记录，并且 Extra 中没有 Using index for group-by，数据库仍在扫描大量重复索引项。修正方法是让过滤列、分组列、聚合列符合联合索引顺序，并用 EXPLAIN ANALYZE 验证实际行数，而不是只看索引名称。

2. 在分组索引列后随意加范围条件

例如索引为 (tenant_id, event_date, order_status)，查询按 tenant_id 等值、event_date 范围过滤后再按 order_status 分组。由于范围列之后的分组列无法保持全局有序，优化器更容易使用临时表或文件排序。修正方法是根据核心 SQL 重新设计索引：以分组为目标可使用 (tenant_id, order_status, event_date)，以日期范围过滤为目标再保留另一套索引，通过真实执行计划取舍。

3. 把 SUM/COUNT 误当成松散扫描场景

MIN/MAX 可以读取有序索引的边界，但 SUM(amount)、COUNT(*) 必须知道每个分组内有多少记录。若查询选择了非索引字段 amount，还会发生大量回表。修正方法是为高频统计建立覆盖索引，例如 (tenant_id, order_status, event_date, amount)，或者通过汇总表、增量物化结果减少实时扫描。

4. 派生表过滤条件写在外层，导致内层聚合过大

错误写法是先对全表 GROUP BY，外层再 WHERE tenant_id = 1。虽然优化器有时能进行条件下推，但不要依赖优化器猜测。修正方法是把强过滤条件直接写入派生表内部，例如 WHERE tenant_id BETWEEN 1 AND 20，让内层先基于窄索引生成少量分组，再执行外层关联。

5. 忽略重复数据，导致 JOIN 后行数翻倍

用 tenant_id + order_status + event_date 回表时，如果同一天有多条订单，JOIN 结果可能从每个分组一行变成多行。修正方法是明确业务口径：需要最新一条就使用 MAX(id) 二次定位；需要当天汇总则在外层继续 GROUP BY 并使用 SUM、COUNT，不要把明细连接误写成聚合结果。

GROUP BY 优化的核心不是记住某个 Extra 提示，而是理解索引顺序、扫描行数、回表次数和临时表成本。先用覆盖索引完成轻量分组，再用最小主键集合回表补列，往往能把原本数秒的聚合查询压缩到数百毫秒以内。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。