MySQL GROUP BY 原理与优化

我刚工作的时候，有次统计每个用户的订单总金额，写了SELECT user_id, SUM(amount) FROM orders GROUP BY user_id，结果执行了 60 秒还没出结果。DBA 帮我一看执行计划，发现没走索引，导致Using temporary（用临时表）。

今天咱们就来扒一扒GROUP BY的原理与优化，看完这篇，你就能把 60 秒的查询优化到 0.01 秒。

GROUP BY 的两种算法

MySQL 的GROUP BY有两种算法：松散索引扫描（Loose Index Scan）和紧凑索引扫描（Tight Index Scan）。

1. 松散索引扫描（Loose Index Scan）

最优情况：GROUP BY的字段是索引的前缀，MySQL 只需要扫描索引的不同值，不需要扫描所有行。

索引：(user_id, created_at) GROUP BY user_id → 能走松散索引扫描（user_id 是索引前缀）

为什么快？索引里user_id的不同值很少（比如 1000 个用户），MySQL 只需要读 1000 次索引，不用扫全表。

2. 紧凑索引扫描（Tight Index Scan）

次优情况：GROUP BY的字段是索引的前缀，但WHERE条件里有范围查询，导致要扫描索引的连续范围。

索引：(user_id, created_at) WHERE created_at > '2024-01-01' GROUP BY user_id → 走紧凑索引扫描（要扫描 created_at > '2024-01-01' 的所有行）

为什么慢？要扫描所有符合条件的行（可能很多）。

3. 用临时表（Using temporary，最慢！）

最坏情况：GROUP BY的字段没索引，或者没遵循最左前缀，MySQL 要先扫描所有行，放到临时表里，再分组。

没索引： GROUP BY user_id → 用临时表（Using temporary）

为什么最慢？要扫描全表，还要写临时表（可能写到磁盘）。

实战：优化一个慢 GROUP BY

假设有个订单表，要统计每个用户的订单总金额，很慢：

SELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;-- 执行 60 秒

第 1 步：看执行计划

EXPLAINSELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;

输出：

+----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 20000000 | Using temporary | +----+-------------+--------+------+---------------+------+---------+------+----------+----------------+

问题：

1. type = ALL（全表扫描）
2. 2. Extra = Using temporary（用临时表）

第 2 步：给 GROUP BY 字段加索引

-- 给 user_id 加索引CREATEINDEXidx_user_idONorders(user_id);

再看执行计划：

EXPLAINSELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;

输出：

+----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+ | 1 | SIMPLE | orders | index | NULL | idx_user_id | 5 | NULL | 20000000 | | +----+-------------+--------+-------+---------------+-----------------+---------+------+----------+-------+

优化效果：

1. type = index（索引扫描）
2. 2. Extra里没有Using temporary了（走索引，不需要临时表）
3. 3. 执行时间从 60 秒降到 0.1 秒（600 倍提升！）

第 3 步：用覆盖索引进一步优化

如果查询的字段都在索引里，不需要回表，性能更好。

-- 创建覆盖索引 (user_id, amount)CREATEINDEXidx_user_id_amountONorders(user_id,amount);

再看执行计划：

EXPLAINSELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;

输出：

+----+-------------+--------+-------+---------------+---------------------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------------------+---------+------+----------+-------------+ | 1 | SIMPLE | orders | index | NULL | idx_user_id_amount | 10 | NULL | 20000000 | Using index | +----+-------------+--------+-------+---------------+---------------------+---------+------+----------+-------------+

优化效果：

1. Extra = Using index（覆盖索引，不需要回表）
2. 2. 执行时间从 0.1 秒降到 0.01 秒（10 倍提升！）

GROUP BY 的坑：临时表

GROUP BY最大的坑是临时表（Temporary Table）。

什么时候会用临时表？

1. GROUP BY 的字段没索引
2. 2. GROUP BY 的字段没遵循最左前缀
3. 3. DISTINCT 和 GROUP BY 混用
4. 4. ORDER BY 和 GROUP BY 的字段不一样

临时表在哪？

• 内存临时表：数据量小（< tmp_table_size和< max_heap_table_size），存在内存里
• • 磁盘临时表：数据量大，写到磁盘（.MYD文件）
    性能差距：内存临时表快 100 倍！

怎么避免临时表？

方案 1：给 GROUP BY 字段加索引（最重要！）

-- 优化前：没索引，用临时表GROUPBYuser_id-- Using temporary-- 优化后：加索引，走索引CREATEINDEXidx_user_idONorders(user_id);GROUPBYuser_id-- 没有 Using temporary

方案 2：遵循最左前缀

-- 索引：(user_id, created_at)-- 能走索引GROUPBYuser_id-- 不能走索引（没遵循最左前缀）GROUPBYcreated_at-- Using temporary

方案 3：ORDER BY 和 GROUP BY 的字段要一样

-- 优化前：ORDER BY 和 GROUP BY 不一样，用临时表GROUPBYuser_idORDERBYcreated_at-- Using temporary-- 优化后：ORDER BY 和 GROUP BY 一样，走索引GROUPBYuser_idORDERBYuser_id-- 没有 Using temporary

方案 4：用 WHERE 限制范围（减少临时表数据量）

-- 优化前：没 WHERE，临时表数据量大GROUPBYuser_id-- 临时表 2000 万行-- 优化后：用 WHERE 限制范围，临时表数据量小WHEREcreated_at>'2024-01-01'GROUPBYuser_id-- 临时表 100 万行

实战建议

1. 给 GROUP BY 字段加索引（最重要！）

这是最重要的建议。GROUP BY的字段没索引，绝对会用到临时表，性能炸裂。

-- 优化前：没索引GROUPBYuser_id-- Using temporary-- 优化后：加索引CREATEINDEXidx_user_idONorders(user_id);GROUPBYuser_id-- 没有 Using temporary

2. 遵循最左前缀

如果 GROUP BY 的字段是联合索引，要遵循最左前缀。

-- 索引：(user_id, created_at)-- 能走索引GROUPBYuser_id-- 不能走索引（没遵循最左前缀）GROUPBYcreated_at-- Using temporary

3. ORDER BY 和 GROUP BY 的字段要一样

如果 ORDER BY 和 GROUP BY 的字段不一样，会用临时表。

-- 优化前：ORDER BY 和 GROUP BY 不一样GROUPBYuser_idORDERBYcreated_at-- Using temporary-- 优化后：ORDER BY 和 GROUP BY 一样GROUPBYuser_idORDERBYuser_id-- 没有 Using temporary

4. 用覆盖索引

如果查询的字段都在索引里，不需要回表，性能更好。

-- 优化前：要回表SELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;-- 优化后：覆盖索引CREATEINDEXidx_user_id_amountONorders(user_id,amount);SELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;-- Using index

5. 用 WHERE 限制范围

如果 WHERE 条件能过滤掉大部分行，临时表的数据量就小了，性能更好。

-- 优化前：没 WHERE，临时表数据量大GROUPBYuser_id-- 临时表 2000 万行-- 优化后：用 WHERE 限制范围WHEREcreated_at>'2024-01-01'GROUPBYuser_id;-- 临时表 100 万行

总结

• GROUP BY的两种算法：松散索引扫描（最优）、紧凑索引扫描（次优）
• • 最坏情况：用临时表（最慢）
• • 临时表：内存临时表（快）、磁盘临时表（慢）
• • 优化方案 1：给 GROUP BY 字段加索引（最重要！）
• • 优化方案 2：遵循最左前缀
• • 优化方案 3：ORDER BY 和 GROUP BY 的字段要一样
• • 优化方案 4：用覆盖索引
• • 优化方案 5：用 WHERE 限制范围
• • 实战建议：给 GROUP BY 字段加索引、遵循最左前缀、ORDER BY 和 GROUP BY 的字段要一样、用覆盖索引、用 WHERE 限制范围
    如果你能把GROUP BY的两种算法、临时表的坑、5 种优化方案讲清楚，面试官绝对觉得你是高级开发。

实战代码都在我本地跑过，你可以放心复制。如果有问题，欢迎评论区交流！