本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
查询基础（SELECT / JOIN / 聚合 / 排序） · SELECT 与 WHERE

DISTINCT 的实现原理？为什么对大表慎用 DISTINCT？与 GROUP BY 实现差异？

How DISTINCT is implemented internally? Why use DISTINCT with caution on large tables? Differences in implementation compared to

答案语言

考察要点

这道题面试官想看你是否深入理解数据库的查询执行过程，而不只是停留在 SQL 语法层面。核心考察点包括：

  1. 去重算法：你是否知道 DISTINCT 底层依赖的两种核心算法——排序（Sorting）和哈希（Hashing）。
  2. 资源消耗：你是否明白这两种算法对 CPU、内存（Memory）和磁盘 I/O 的影响，并能解释为什么在大表上性能会急剧下降。
  3. 查询优化器：你是否了解优化器如何根据索引和数据情况选择最优的去重策略，以及 DISTINCT 和 GROUP BY 在优化器眼中的异同。

核心概念

DISTINCT 关键字用于从 SELECT 查询结果中移除重复的行。MySQL 实现 DISTINCT 主要有三种方式，其选择由查询优化器基于成本估算决定。

1. 使用索引（最高效）

当 DISTINCT 的列是索引的一部分或全部时，优化器可以利用索引的有序性来高效去重。这被称为“松散索引扫描”（Loose Index Scan）。

  • 原理：数据库直接在索引树上进行跳跃式扫描，每次只读取每个唯一键的第一条记录，然后直接跳到下一个唯一键，从而避免了读取所有数据再进行去重。
  • 执行计划：EXPLAIN 的 Extra 字段会显示 Using index for group-by 或类似的提示（在很多场景下，DISTINCT 会被优化为 GROUP BY）。
  • 示例：
    sql
     1-- 假设在 user_id 列上有索引
     2CREATE TABLE logs (
     3    id INT PRIMARY KEY AUTO_INCREMENT,
     4    user_id INT,
     5    action VARCHAR(50),
     6    KEY idx_user_id (user_id)
     7);
     8

     9-- 这个查询可以高效地利用 idx_user_id 索引
    10EXPLAIN SELECT DISTINCT user_id FROM logs;
    执行计划中的 type 可能是 range，Extra 会包含 Using index for group-by。

2. 使用临时表 + 排序（Sorting）

当没有合适的索引可以利用时，这是一种常见的 DISTINCT 实现方式。

  • 原理：
    1. MySQL 将查询结果集（满足 WHERE 条件的所有行）放入一个临时表。
    2. 如果数据量不大，临时表在内存中创建；如果数据量超过 tmp_table_size 和 max_heap_table_size 的限制，临时表会被转存到磁盘上。
    3. MySQL 对临时表中的数据按照 DISTINCT 的列进行排序（filesort）。
    4. 排序后，MySQL 遍历已排序的结果，只返回每组重复数据的第一行。
  • 资源消耗：
    • CPU：排序操作是 CPU 密集型的。
    • 内存/磁盘 I/O：如果数据量巨大，内存中的 sort_buffer 不足以容纳所有数据，就需要使用外部排序，在磁盘上创建多个临时文件进行归并排序，这将产生大量的磁盘 I/O，导致性能急剧下降。这就是对大表慎用 DISTINCT 的核心原因。
  • 执行计划：EXPLAIN 的 Extra 字段会显示 Using temporary; Using filesort。

3. 使用临时表 + 哈希（Hashing）

这是另一种不依赖索引的实现方式，在某些场景下比排序更优。

  • 原理：
    1. MySQL 创建一个内存临时表，这个临时表有一个 UNIQUE 索引（哈希索引）建在 DISTINCT 的列上。
    2. MySQL 遍历查询结果集，尝试将每一行插入到这个临时表中。
    3. 由于 UNIQUE 索引的存在，重复的行会插入失败，从而被自动忽略。
    4. 最后，返回临时表中的所有行。
  • 资源消耗：
    • 内存：主要消耗是哈希表本身。如果唯一值的数量非常多，这个哈希表可能会非常大，同样可能超出内存限制 (tmp_table_size) 而转为磁盘临时表，性能也会下降。
    • CPU：主要消耗在计算哈希值和哈希表操作上。
  • 执行计划：EXPLAIN 的 Extra 字段会显示 Using temporary。

DISTINCT 与 GROUP BY 的差异

特性SELECT DISTINCT col1, col2 FROM tSELECT col1, col2 FROM t GROUP BY col1, col2
语义返回唯一的行组合。按指定列对行进行分组，每个分组返回一行。
功能仅用于去重。更强大，可以配合聚合函数（COUNT, SUM, AVG 等）对每个分组进行计算。
优化器处理在没有聚合函数的情况下，MySQL 优化器通常会将 DISTINCT 改写为等价的 GROUP BY。因此，两者的执行计划和性能几乎完全相同。是分组聚合的标准实现。
ORDER BY 交互DISTINCT 和 ORDER BY 一起使用时，ORDER BY 的列必须是 SELECT 列表中的列。GROUP BY 默认可能会对结果进行隐式排序（老版本 MySQL），但新版本（如 8.0）已移除此行为。ORDER BY 可以作用于非聚合列或聚合结果。

结论：对于单纯的去重需求，DISTINCT 和 GROUP BY 在性能上没有本质区别。但 GROUP BY 的语义更通用。

常见低分回答

  1. “DISTINCT 就是比 GROUP BY 慢”：这是一个常见的误解。在没有聚合函数的场景下，优化器通常将 DISTINCT 视为 GROUP BY 的语法糖，生成相同的执行计划。说一个绝对比另一个慢是错误的。
  2. “DISTINCT 会全表扫描，所以很慢”：不准确。DISTINCT 慢的根本原因不是全表扫描（WHERE 子句可以限制扫描范围），而是在没有索引支持下去重操作（排序或哈希）本身的高昂成本，尤其是当数据量大到需要使用磁盘临时文件时。
  3. “用了 DISTINCT 索引就没用了”：错误。恰恰相反，为 DISTINCT 的列创建合适的索引是最高效的优化手段，可以实现“松散索引扫描”，避免临时表和排序。
  4. 混淆 COUNT(DISTINCT col) 和 SELECT DISTINCT col：前者是一个聚合函数，用于计算一列中不重复值的数量，它本身就会触发去重操作；后者是作用于整个查询结果集的去重。两者都会遇到大表性能问题，但作用域和用法完全不同。

工程实践与优化

  1. 优先使用索引：优化 DISTINCT 的第一原则是在 DISTINCT 的列上建立索引。复合索引的顺序也至关重要，应将 DISTINCT 的列放在索引的前缀。

  2. 缩小数据范围：在 DISTINCT 之前，使用尽可能强的 WHERE 条件来减少需要处理的数据量。数据量越小，排序或哈希的成本就越低。

  3. 使用 EXISTS 替代 DISTINCT：在某些 JOIN 场景下，EXISTS 或 IN 子查询可能比 DISTINCT 更高效。

    sql
     1-- 低效场景
     2SELECT DISTINCT t1.id
     3FROM table1 t1
     4JOIN table2 t2 ON t1.id = t2.t1_id;
     5

     6-- 优化后
     7SELECT t1.id
     8FROM table1 t1
     9WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t1.id = t2.t1_id);

    EXISTS 在找到第一个匹配项后就会停止搜索当前 t1.id，而 JOIN + DISTINCT 需要找出所有匹配项再进行去重。

  4. 业务层面避免：思考是否真的需要在查询时实时去重。

    • 数据建模：通过 UNIQUE 约束保证数据在写入时就是唯一的。
    • 预计算/物化视图：对于高频的去重查询，可以创建一个定时任务，将去重后的结果存入一个汇总表，业务直接查询汇总表。

  5. 查看执行计划：使用 EXPLAIN 分析 DISTINCT 查询。如果 Extra 中出现 Using temporary; Using filesort，这就是明确的性能瓶颈信号，需要立即进行优化（通常是加索引）。

常见追问与延伸题目

  1. 追问：如果数据量增长 100 倍，达到 10 亿级别，SELECT DISTINCT user_id FROM huge_logs; 该如何处理？

    • 回答要点 1 (在线查询)：在这种数据量下，即使有索引，实时查询的压力也可能非常大。首先，必须确保 user_id 上有索引。如果查询还是很慢，说明唯一 user_id 的数量（Cardinality）本身也极其巨大，索引扫描的成本依然很高。此时需要评估业务场景，如果允许一定的延迟，可以引导用户添加更多筛选条件（如时间范围）来缩小查询范围。
    • 回答要点 2 (离线分析)：如果这是一个分析类需求（例如，统计总共有多少活跃用户），绝不应该在生产的 OLTP 数据库上实时运行。应该使用大数据处理框架（如 Spark、Flink、ClickHouse）来处理。这些系统专为大规模数据聚合设计，通过分布式计算将任务分发到多个节点并行处理，效率远高于单机数据库。
    • 回答要点 3 (近似计算)：如果业务可以接受近似值而不是精确值（例如，估算 DAU），可以采用像 HyperLogLog 这样的概率数据结构。它可以用极小的内存空间（几KB）来估算海量数据中的基数，误差率很低。一些数据库（如 Redis, ClickHouse）原生支持，MySQL 中可以通过 UDF 或在应用层实现。

  2. 追问：COUNT(DISTINCT a, b) 这种多列去重计数，它的性能瓶颈和单列有什么不同？

    • 回答要点 1 (成本增加)：瓶颈原理相同，都是排序或哈希。但成本更高。因为组合键 (a, b) 的长度更长，排序时每个元素的比较更耗时，哈希时计算和存储的开销也更大。
    • 回答要点 2 (索引设计)：为了优化它，需要一个包含 (a, b) 或 (b, a) 的复合索引。索引的存在同样可以让数据库通过扫描索引来完成去重计数，避免临时表。
    • 回答要点 3 (唯一值增多)：多列组合的唯一值数量（Cardinality）通常会比单列多得多。这意味着用于去重的临时表或哈希表会更大，更容易触及内存瓶颈而转为磁盘操作，性能下降更显著。

  3. 追问：如果我用 GROUP BY 代替 DISTINCT，并且关闭优化器对 GROUP BY 的隐式排序，性能会更好吗？

    • 回答要点 1 (版本差异)：在 MySQL 8.0 及以上版本，GROUP BY 默认不再进行隐式排序，除非显式指定 ORDER BY。所以 SELECT ... GROUP BY a, b 和 SELECT ... GROUP BY a, b ORDER BY NULL 的效果是一样的。在老版本（如 5.7）中，GROUP BY 默认会排序，使用 ORDER BY NULL 可以禁用它，从而在某些场景下提升性能。
    • 回答要点 2 (与 DISTINCT 的关系)：如前所述，SELECT DISTINCT a, b 本质上就是为了得到一个无序的唯一结果集。优化器会将其转换为最高效的 GROUP BY 实现，这个实现本身就不会包含不必要的排序。因此，在现代 MySQL 版本中，手动用 GROUP BY ... ORDER BY NULL 替换 DISTINCT 通常不会带来任何性能提升，因为优化器已经帮你做了最好的选择。强调这一点能体现你对优化器行为的深刻理解。
← Previous
BETWEEN ... AND 是闭区间还是开区间？日期边界容易踩坑的地方？
Next →
LIMIT offset, count 的性能为什么会随 offset 增大而暴跌？如何用游标式翻页（keyset pagination）优化？