人人都会AI编程

13.6 分组、去重、聚合查询优化

更新时间：2026-07-11

分组（GROUP BY）、去重（DISTINCT）和聚合（COUNT、SUM、AVG 等）是日常开发中使用频率极高的操作。它们看似简单，但在数据量大的时候，一条没有优化好的聚合 SQL 可能把数据库拖垮。这类查询的优化核心在于：尽可能利用索引避免排序和建立临时表，因为这两者往往是性能瓶颈的根源。

13.6.1 分组（GROUP BY）优化的关键

MySQL 在执行 GROUP BY 时，通常有以下几种策略：

  • 松散索引扫描（Loose Index Scan）：如果 GROUP BY 的列是索引的前缀，并且查询不要求检索不包含在索引中的额外列，MySQL 可以只扫描索引中每个分组的第一条记录，直接完成聚合。这是最高效的方式。
  • 紧凑索引扫描（Tight Index Scan）：当满足部分索引条件，但需要读取所有匹配索引的记录，或者需要回表取数据时，会顺序扫描整个索引范围。
  • 使用临时表 + 文件排序：如果没有合适的索引可用，MySQL 会新建一张临时表，将所有行塞进去，然后对临时表做排序，最后输出结果。这种方式最慢，应尽量避免。

为了触发索引优化，一个好的分组查询往往需要满足联合索引的最左前缀原则。例如，有一个复合索引 idx_a_b_c(a, b, c)，在写 GROUP BY a,b 时可以利用这个索引分组，甚至 GROUP BY a 也可以，但 GROUP BY b 或 GROUP BY b,c 无法直接利用该索引。如果想了解更底层的原因，可以回顾第 5 章关于联合索引结构与最左前缀原则的内容。

实战中，你需要注意以下几点：

1. 用 WHERE 缩小分组范围

永远不要在分组前不设筛选条件。越小范围的数据分组，扫描的数据页越少，也越容易命中索引。例如：

-- 不理想：扫描全部订单
SELECT user_id, COUNT(*) 
FROM orders 
GROUP BY user_id;

-- 优化：只统计近30天的订单，可以结合索引(user_id, create_time)
SELECT user_id, COUNT(*) 
FROM orders 
WHERE create_time > '2025-01-01' 
GROUP BY user_id;

2. 尽量让 GROUP BY 列和 WHERE 列在同一索引中

如果 GROUP BY 的列和 WHERE 条件中的列可以组成一个覆盖索引，性能会非常好。同样是上面的例子，如果存在 INDEX(user_id, create_time)，优化器可以先利用索引做范围扫描，然后直接在索引内分组计数，避免回表和额外排序。

3. 防止不必要的排序

GROUP BY 默认会按照分组列排序（MySQL 8.0 之前的行为），即使你不需要排序。如果结果集很大，排序会消耗大量内存或磁盘临时文件。如果你只需要分组结果而不关心顺序，可以显式声明 ORDER BY NULL（在 MySQL 8.0 中，优化器已经弃用了隐式排序，但仍建议避免额外的排序开销）。在旧版本或复现场景中，可用它禁用排序：

SELECT category_id, COUNT(*) 
FROM products 
GROUP BY category_id 
ORDER BY NULL;

MySQL 8.0 中 GROUP BY 不再隐含排序，但如果你写了 ORDER BY 却没有用到索引排序，仍然会增加资源消耗。所以，即便不写 ORDER BY 也不会有额外排序开销，但需要明确这一行为变化。

4. GROUP BY 列的字段类型与编码一致性

如果 GROUP BY 的列在关联查询中来自不同表，需要注意字符集、排序规则是否一致。不一致时，MySQL 无法直接使用索引分组，会退化为全表扫描+临时表。建议在设计之初就统一表间关联字段的类型和编码。

13.6.2 去重（DISTINCT）的本质与优化

DISTINCT 在 MySQL 内部通常是作为 GROUP BY 的一种特例来实现的。如果去重列上没有索引，同样需要临时表或文件排序。优化方向与 GROUP BY 非常类似：

  • 用 GROUP BY 代替 DISTINCT， 在某些场景下，两者的执行计划可能完全一样，但在更复杂的查询中，显式写成 GROUP BY 可能让优化器更好地利用索引。
  • 利用唯一索引或无重复索引扫描：如果查询列上存在唯一索引，DISTINCT 操作可以直接跳过，因为值本身就唯一。如果索引列的基数很高，顺序扫描索引也能高效去重。
  • 避免对大范围结果集做 DISTINCT，在应用层通过业务逻辑保证不重复，或者在写入时用唯一约束防重，比事后去重要好得多。

例如，当你要统计不重复的用户 ID 时：

-- 假设 user_id 列上有索引 idx_user_id
SELECT DISTINCT user_id FROM login_log WHERE login_date = '2025-01-15';

优化器可能会使用索引 idx_user_id 进行松散扫描，跳过全部回表，直接去重。但如果加上别的列，比如 SELECT DISTINCT user_id, user_name FROM ...，就可能不得不建立临时表。因此，保持 DISTINCT 查询尽量只包含索引列，是重要的优化手段。

另一个常见的误用是 COUNT(DISTINCT col)。如果 col 列上索引合适，MySQL 可能会利用索引做松散扫描来直接计数。但如果需要同时 COUNT DISTINCT 多个列，或者配合 GROUP BY 时，性能会明显下降。此时可以考虑拆分为多个查询，或者在应用层合并，甚至使用近似计数（如 HyperLogLog）来满足可接受的误差。

13.6.3 聚合函数（COUNT、SUM、AVG）的优化技巧

聚合操作通常依赖索引来进行全表扫描或范围扫描。优化的基本原则依然是让索引覆盖查询，即查询所需要的列全在索引中，避免回表。

COUNT 函数的优化

COUNT(*) 和 COUNT(col) 在行为上有细微差别，性能也可能不同：

  • COUNT(*) 统计所有行数（包含 NULL），InnoDB 会选择最小的二级索引来扫描该表中所有的记录，因为索引比主键聚簇索引小，I/O 更少。这是 MySQL 自动做的优化。
  • COUNT(col) 只统计指定列不为 NULL 的行数。如果该列有索引，则扫描该索引；如果没有索引，只能全表扫描并过滤 NULL。
  • COUNT(1) 等同于 COUNT()，优化器会将其转化为 COUNT()。

因此，一般直接用 COUNT() 即可。但无论是 COUNT() 还是 COUNT(col)，全表计数的成本与数据量成正比。对于大表，每秒几万 QPS 的频繁全表计数不可取。常见的应对方式有：

  • 用缓存粗略统计：比如 Redis 计数器，配合定期校准。
  • 使用汇总表：对于历史数据，按小时或天预先聚合出总数，查询时直接读汇总表。
  • 利用 EXPLAIN 的 rows 估算值：如果不要求绝对精确，EXPLAIN SELECT COUNT(*) FROM table 返回的 rows 是一个估算值，查询成本极低，但可能会有误差，在统计信息更新不勤时尤其注意。

SUM 与 AVG 的优化

SUM 和 AVG 通常需要对某一列的全部值做计算，索引可以加速数据访问，但列值本身还是要逐行累加。如果 WHERE 条件能过滤大量数据，并且索引覆盖相关列，效果会很好。

注意数据类型的选择和溢出风险。例如，对 INT 列做 SUM 可能超过 INT 范围，应该使用 SUM(CAST(col AS BIGINT)) 或者在设计表阶段就将列设为 BIGINT。另外，AVG 是 SUM/COUNT，COUNT 不计 NULL，极容易在业务上犯错：如果你想要把 NULL 当作 0 来计算平均值，应该用 AVG(COALESCE(col, 0))。

聚合与 GROUP BY 结合

这是最常见的聚合报表场景，如“按分类统计订单量及金额”。性能好坏完全取决于索引设计。如果查询是：

SELECT category_id, COUNT(*), SUM(amount)
FROM orders
WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY category_id;

一个理想的索引是 (create_time, category_id, amount)。这样，通过范围扫描得到满足时间条件的索引段，在索引内就对 category_id 进行分组，并在扫描过程中累加 amount，全程不需要回表和额外排序。如果没有这样的索引，InnoDB 不得不先查出所有符合条件的行，放入临时表，再做分组聚合，性能天差地别。

13.6.4 避免临时表和文件排序

无论是 GROUP BY、DISTINCT 还是 UNION，只要执行计划中出现了 Using temporary 或 Using filesort，就标志着你应该去审视一下索引是否合理。这两个标志是出现临时表和文件排序的直接信号，也是聚合查询优化的第一观察点。

可以通过 EXPLAIN 查看是否出现了 Using temporary; Using filesort。一旦出现，说明数据太宽或者索引缺位。消除它们的方法通常有：

  • 增大索引覆盖度：让 GROUP BY 后的列和 SELECT 中的聚合列都在同一索引中，包括 WHERE 条件列。
  • 缩小结果集：用 WHERE 条件尽早过滤。
  • 改写查询：把复杂嵌套聚合拆分成多步，先聚合出子集，再归并。
  • 调整 tmp_table_size 和 max_heap_table_size：如果无法避免临时表，让它在内存中完成而非写入磁盘，也可以缓解延迟，但这只是缓冲，不是根本解决方案。

13.6.5 实战案例：优化一个慢聚合查询

假设你有一个日志表 access_log，包含字段 user_id、page_url、access_time，数据量上千万。业务需要按天统计独立访问用户数：

-- 原始慢 SQL
SELECT DATE(access_time) AS day, COUNT(DISTINCT user_id) AS uv
FROM access_log
WHERE access_time >= '2025-01-01' AND access_time < '2025-02-01'
GROUP BY day;

这条查询在没有合适索引时，会全表扫描，建立巨大临时表来同时处理分组和去重，耗时几秒甚至更久。优化方式：

  1. 建立联合索引 idx_time_user(access_time, user_id)。注意，索引列顺序是先时间后用户，因为 WHERE 优先过滤时间，GROUP BY 又是时间表达式，最左前缀原则允许索引同时用于筛选和分组。
  2. 修改查询使之能利用索引：因为 GROUP BY 使用了 DATE(access_time) 函数，这会导致索引失效（函数导致无法索引查找），所以可以考虑把 DATE(access_time) 替换为直接对 access_time 分组，或者使用生成列。更好的方式是改造语句：
-- 利用索引分组，避免函数
SELECT DATE(access_time) AS day, COUNT(DISTINCT user_id) AS uv
FROM access_log
WHERE access_time >= '2025-01-01' AND access_time < '2025-02-01'
GROUP BY DATE(access_time);   -- 仍然有函数，但是在有索引的前提下，优化器可能会使用松散扫描，也可能无法。

实际上，如果一定要按天分组，更好的做法是存储时增加一个 access_date 列，并在 (access_date, user_id) 上建索引。这是典型的空间换时间。如果没有这一列，可以尝试用等价的范围分组避免函数，但说实话日期函数在这种场景下是常见难题。一个折衷是使用 GROUP BY TO_DAYS(access_time)，但这同样涉及函数。最优解仍然是新增日期列。

该案例说明了：聚合查询优化不仅是对现有 SQL 的改写，更多时候需要从表结构设计阶段就考虑进去。Using temporary; Using filesort 一旦出现，就应该回头审视索引。 当你把索引设计为 (access_date, user_id) 后，查询就可以改写为对日期的精确范围扫描 + 松散索引扫描去重，执行时间可以从秒级降至毫秒级。

总之，分组、去重和聚合是 MySQL 优化中投入产出比很高的一块。只要牢记索引是避免排序和临时表的根本武器，并在 EXPLAIN 中警惕 Using temporary 和 Using filesort，绝大多数聚合查询的性能问题都可迎刃而解。