我刚工作的时候有次统计每个用户的订单总金额写了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 | ---------------------------------------------------------------------------------------------问题type ALL全表扫描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 | | ------------------------------------------------------------------------------------------------优化效果type index索引扫描Extra里没有Using temporary了走索引不需要临时表执行时间从 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 | ----------------------------------------------------------------------------------------------------------优化效果Extra Using index覆盖索引不需要回表执行时间从 0.1 秒降到 0.01 秒10 倍提升GROUP BY 的坑临时表GROUP BY最大的坑是临时表Temporary Table。什么时候会用临时表GROUP BY 的字段没索引GROUP BY 的字段没遵循最左前缀DISTINCT 和 GROUP BY 混用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方案 3ORDER 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_at2024-01-01GROUPBYuser_id-- 临时表 100 万行实战建议1. 给 GROUP BY 字段加索引最重要这是最重要的建议。GROUP BY的字段没索引绝对会用到临时表性能炸裂。-- 优化前没索引GROUPBYuser_id-- Using temporary-- 优化后加索引CREATEINDEXidx_user_idONorders(user_id);GROUPBYuser_id-- 没有 Using temporary2. 遵循最左前缀如果 GROUP BY 的字段是联合索引要遵循最左前缀。-- 索引(user_id, created_at)-- 能走索引GROUPBYuser_id-- 不能走索引没遵循最左前缀GROUPBYcreated_at-- Using temporary3. 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 temporary4. 用覆盖索引如果查询的字段都在索引里不需要回表性能更好。-- 优化前要回表SELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;-- 优化后覆盖索引CREATEINDEXidx_user_id_amountONorders(user_id,amount);SELECTuser_id,SUM(amount)FROMordersGROUPBYuser_id;-- Using index5. 用 WHERE 限制范围如果 WHERE 条件能过滤掉大部分行临时表的数据量就小了性能更好。-- 优化前没 WHERE临时表数据量大GROUPBYuser_id-- 临时表 2000 万行-- 优化后用 WHERE 限制范围WHEREcreated_at2024-01-01GROUPBYuser_id;-- 临时表 100 万行总结GROUP BY的两种算法松散索引扫描最优、紧凑索引扫描次优最坏情况用临时表最慢临时表内存临时表快、磁盘临时表慢优化方案 1给 GROUP BY 字段加索引最重要优化方案 2遵循最左前缀优化方案 3ORDER BY 和 GROUP BY 的字段要一样优化方案 4用覆盖索引优化方案 5用 WHERE 限制范围实战建议给 GROUP BY 字段加索引、遵循最左前缀、ORDER BY 和 GROUP BY 的字段要一样、用覆盖索引、用 WHERE 限制范围如果你能把GROUP BY的两种算法、临时表的坑、5 种优化方案讲清楚面试官绝对觉得你是高级开发。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流
