★ FEATURED ARTICLE 线上一条报表 SQL 跑了 42 秒单表八百多万行SELECT DISTINCT三个字段外加一个ORDER BY。我把它拆成GROUP BY再配上一条覆盖索引耗时掉到 1.2 秒。这类事情我碰到过太多次问题很少出在 MySQL DISTINCT 这个关键字本身而在于写的人并不清楚它背后到底做了什么。MySQL DISTINCT 看起来只是“去重”两个字真跑起来却可能建临时表、可能触发 filesort、可能把索引彻底废掉也可能什么都没干——因为优化器已经把它揉进了别的执行路径里。这篇把 DISTINCT 从语义、执行路径、索引配合、COUNT(DISTINCT)变体一直讲到改写手法适合已经会写基本 SQL、被慢查询折腾过的同学也适合刚接手别人留下的报表代码、想知道“这一大段到底慢在哪”的朋友。下面所有结论都基于常见版本5.7 与 8.0的实际行为版本差异我会单独标出来。1. DISTINCT 去重的是“整行”不是“某一列”1.1 一个被误解了很多年的写法很多人第一次用 DISTINCT脑子里想的是“我要把 city 这一列去重”。于是写下SELECT DISTINCT city, user_id FROM addr然后发现结果里 city 还是重复的就断定 DISTINCT “不好使”。其实 DISTINCT 的判定单位永远是 SELECT 列表里所有列的组合(city, user_id)这一对只要有一列不同就算两行。想要“每个 city 只留一行”必须明确告诉数据库“留哪一行”这已经不是去重问题而是取样问题——要么用GROUP BY city配聚合函数要么用窗口函数ROW_NUMBER()挑一条。我见过更隐蔽的版本SELECT DISTINCT a.id, b.status FROM a JOIN b ...开发者的意图是“a 表去重”但因为 SELECT 列表里带了 b 表的字段只要 b 有多条记录a.id 依然会重复出现。这时候正确的做法不是加 DISTINCT而是先想清楚我到底要 a 表的行还是要 a 和 b 的组合行如果要的是前者就该用 EXISTS 或者先在子查询里把 b 收敛成一行DISTINCT 在这里只是把问题掩盖了代价是白建一张临时表。判断方法很简单把你 SELECT 列表里的列全部列出来问自己“这些列完全相同就算重复吗”如果答案是“不是我只在乎其中一列”那你要的根本不是 DISTINCT。1.2 多列组合、NULL 与排序规则判定“相同”的三条隐含规则DISTINCT 说“相同”的时候背后有三条规则在起作用不理解这三条结果就会莫名其妙对不上。第一条是NULL 相等。SQL 里NULL NULL是 UNKNOWN但在 DISTINCT 和 GROUP BY 的语境下所有 NULL 被视为同一个值只会保留一条。这点和 WHERE 条件里的比较逻辑完全不同很多人第一次发现“去重后 NULL 只剩一行”时会愣一下。第二条是排序规则collation决定字符串是否相等。utf8mb4_general_ci这类_ci结尾的排序规则是大小写不敏感的abc和ABC会被判定为同一行DISTINCT 会合并它们换成utf8mb4_bin就区分大小写。这不是 DISTINCT 的锅是列定义决定的。如果业务上要求大小写敏感去重去重前必须显式改写SELECT DISTINCT BINARY name FROM t或者SELECT DISTINCT name COLLATE utf8mb4_bin FROM t但注意加了函数或 COLLATE 之后索引基本就用不上了要权衡。第三条是尾部空格的填充语义。8.0 默认的utf8mb4_0900_ai_ci是 NO PAD 类型a和a 被认为是不同的值而 5.7 常见的utf8mb4_general_ci属于 PAD SPACE比较时会忽略尾部空格两者被判为同一行。同一个库从 5.7 迁到 8.0DISTINCT 出来的条数变多八成就是这个原因而不是数据被谁改了。1.3 作用域DISTINCT 永远贴着 SELECT 列表DISTINCT 的作用范围只覆盖 SELECT 列表它对 WHERE、JOIN、GROUP BY 的中间结果不产生任何“提前去重”的效果。也就是说SELECT DISTINCT ... FROM a JOIN b ON ...的执行顺序依然是先按连接条件把结果集铺开再在投影阶段对最终列组合去重。连接放大一百倍你就是要处理一百倍的行再去重DISTINCT 一点忙都帮不上。这也是为什么“在 JOIN 后面加 DISTINCT”是我最不建议的写法。它不是错的它是贵的。真正省事的做法是在数据放大的源头拦掉这一点在第 6 节会详细展开。还有一个容易被忽略的细节SELECT DISTINCT COUNT(*) FROM t这种写法里聚合之后只有一行DISTINCT 没有任何作用属于纯冗余。同理SELECT DISTINCT * FROM t在单表上是毫无意义的开销因为主键保证行唯一只有存在 JOIN 或者视图展开时才可能产生重复。2. DISTINCT 的执行路径临时表、排序还是索引2.1 三条路径与 EXPLAIN 里的信号MySQL 实现 DISTINCT 大致有三条路开销差着数量级。第一条是建临时表去重。扫描驱动表把每一行的去重列扔进一张临时表插入前先查重内存临时表上通常靠哈希索引磁盘临时表上靠唯一索引或排序。EXPLAIN 的 Extra 里会看到Using temporary。这条路的成本跟你扫了多少行强相关跟最终剩多少行关系不大。第二条是排序去重。把结果集按去重列排序然后顺序扫描相邻行比较。Extra 里会看到Using temporary; Using filesort。排序是有内存上限的sort_buffer_size装不下就会退化成磁盘归并这时候磁盘 IO 会直接把查询拖垮。第三条是利用索引天然有序。如果 DISTINCT 的列正好能用上某个索引的前缀优化器可以只扫描索引、跳过重复值Extra 里会出现Using index for group-by也就是常说的松散索引扫描或者至少是Using index的覆盖扫描。这条路基本是免费的——扫描的 IL 数量级从“表有多少行”降到“有多少个不同的值”。所以判断一条 DISTINCT 快不快第一步永远是EXPLAIN看 Extra而不是凭感觉调参数。EXPLAIN SELECT DISTINCT city, province FROM user_addr WHERE province ZJ;如果结果是Using where; Using temporary那这条 SQL 在大表上基本没有救必须改索引或改写法。如果能看到Using index for group-by说明已经在最优路径上了别再折腾。2.2 用 Handler 计数器验证它到底扫了多少行EXPLAIN 只给估算值行数一多估得就不准。我习惯用 Handler 状态变量做一次实测它能告诉你这次查询在存储引擎层真实读了多少行比rows那一列可信得多FLUSH STATUS; SELECT DISTINCT city FROM user_addr WHERE province ZJ; SHOW SESSION STATUS WHERE Variable_name IN (Handler_read_key,Handler_read_next,Handler_read_rnd_next);Handler_read_next是顺着索引扫的行数Handler_read_rnd_next是全表顺序扫的行数。如果一条“只返回 30 个城市”的去重查询Handler_read_next显示读了几十万那说明它虽然在扫索引但扫的是密集索引扫描把每一行都读了一遍再比较相邻值而不是松散扫描。这时候把city放到索引更靠前的位置往往能立刻见效。这个方法在排查“为什么加了索引还是慢”时特别管用因为它能区分“索引没用上”和“索引用上了但用错了方式”两种完全不同的情况。2.3 临时表什么时候会从内存掉到磁盘真走到临时表那条路的时候还有一个关键分水岭临时表是在内存里还是落盘了。内存临时表的容量上限取tmp_table_size和max_heap_table_size里的较小值。一旦估算超出或者表里出现了 BLOB/TEXT 字段MySQL 会直接放弃内存路线转成磁盘临时表。8.0 之后内部临时表默认走 TempTable 引擎内存上限由temptable_max_ram控制默认 1GB超出部分会先尝试用 mmap 文件再退到 InnoDB 磁盘临时表。想确认自己的查询有没有踩这条线看两个全局状态就够了SHOW GLOBAL STATUS LIKE Created_tmp_tables; SHOW GLOBAL STATUS LIKE Created_tmp_disk_tables;两个值相除磁盘临时表占比如果超过 10%说明有大量查询在落盘。这时候要么优化 SQL 消灭临时表要么把tmp_table_size调大——但后者只是缓解治不了根。我个人的经验是单价超过 200ms 的报表查询里八成都能通过改索引或改写把Using temporary干掉比调参数收益大得多。3. DISTINCT 与 GROUP BY 的取舍3.1 语义上什么时候等价、什么时候不等价SELECT DISTINCT a, b FROM t和SELECT a, b FROM t GROUP BY a, b在 8.0 上基本等价优化器甚至会在内部互相改写。但“基本等价”不等于“随便选”有两个边界必须清楚。第一个边界是要不要写聚合函数。SELECT a, COUNT(*) FROM t GROUP BY a这件事 DISTINCT 做不到你必须用 GROUP BY。反过来如果只是想对整行去重两者在结果上没有区别。第二个边界是输出的确定性。如果你写SELECT a, b FROM t GROUP BY a在ONLY_FULL_GROUP_BY模式下会直接报错因为 b 既不在 GROUP BY 里也没被聚合——这其实是好事它帮你拦住了一个模糊语义。而SELECT DISTINCT a, b从一开始就明确了“按组合去重”不会产生这种歧义。所以我的偏好是纯粹的行去重用 DISTINCT需要分组计算用 GROUP BY不要为了“听说 GROUP BY 更快”故意把 DISTINCT 改写成 GROUP BY那只是换了个壳。3.2 8.0 之后排序行为的变化与 ORDER BY 的坑这里有一个历史包袱必须说清楚。在 5.7 及更早的版本里DISTINCT 的实现高度依赖排序临时表或 filesort所以很多人观察到的结果是“DISTINCT 出来的数据是排好序的”甚至有人写代码依赖这个顺序。这个顺序从来不是承诺在 8.0 里更是彻底不可靠。一旦 DISTINCT 走上松散索引扫描或者哈希去重路径输出顺序就完全变了。所以只要你的业务对顺序有要求就必须显式写ORDER BY一个字符都不能省。而写ORDER BY的时候又会踩另一个坑MySQL 从 5.7 开始对 DISTINCT 查询的 ORDER BY 列有硬性要求——排序列必须出现在 SELECT 列表里否则直接报错 3065-- 报错ORDER BY clause is not in SELECT list, references column t.status -- which is not in SELECT list; this is incompatible with DISTINCT SELECT DISTINCT city FROM user_addr ORDER BY status;解决办法只有两个要么把status加进 SELECT 列表但这样就改变了去重粒度结果会变多要么把排序放到外层去做——先用 DISTINCT 拿到确定的结果集再在应用层或者包一层子查询排序。后者在报表导出的场景里很常用。3.3 选型判断的四个问题我在评审别人的 SQL 时如果看到 DISTINCT通常会问四个问题基本能覆盖绝大多数误用。一是“你真正想要的粒度是什么”回答不出来就说明 SELECT 列表没想清楚。二是“这张表预计扫多少行”如果扫行数远超最终结果行数说明索引方向没优化。三是“加了 DISTINCT 之后结果行数变化多少”如果几乎没变化比如只从 10 万降到 9.9 万那 DISTINCT 基本是在做无用功说明上游连接产生了重复应该从连接侧解决。四是“业务能不能接受近似值”这个问题决定要不要上概率统计算法在计数场景里非常关键。4. 让 DISTINCT 走索引字段顺序与覆盖4.1 松散索引扫描的硬条件松散索引扫描是 DISTINCT 最快的执行方式但它有相当严格的前提条件我列成表看得更清楚条件说明常见的破坏方式只涉及单表参与去重的列必须来自同一张表多表 JOIN 后去重优化器直接放弃使用复合索引单列索引上效果有限需要多列组合只给单列建了索引去重列是索引前缀顺序可以不同但中间不能有缺口索引是 (a,b,c)去重的是 (a,c)没有范围查询干扰WHERE里的范围条件会让前缀失效WHERE dt 2024-01-01加去重列不在同一前缀不能有函数包裹去重列必须是裸列DISTINCT DATE(create_time)举个具体的例子。表上建了索引idx_prov_city (province, city)那么-- 能走松散索引扫描 SELECT DISTINCT province FROM user_addr; -- 也能顺序不一致不影响 SELECT DISTINCT city, province FROM user_addr; -- 走不了因为 city 单独在前缀之外8.0.13 之前 SELECT DISTINCT city FROM user_addr;最后这条在 8.0.13 之后有了新解法索引跳跃扫描Skip Scan。优化器可以在缺少最左列的情况下用“先枚举 province 的取值再在每个取值里扫 city”的方式命中索引。代价是最左列的基数不能太大如果 province 有三千个不同值跳跃的收益就没了。所以别指望 Skip Scan 兜底索引该建对还是要建对。4.2 复合索引字段顺序怎么排排索引字段顺序有个很实用的判断标准把 DISTINCT 里的字段全放进去然后按“等值查询字段 → 去重字段 → 排序字段”的顺序排。比如查询是SELECT DISTINCT city, province FROM user_addr WHERE country CN ORDER BY province最合适的索引是(country, province, city)。country是等值条件放最前province既是去重列又是排序列放中间city放最后。这样一次索引扫描就能同时满足过滤、去重、排序三个需求Extra 里不会再出现Using temporary或Using filesort。反过来的失败案例我也见过不少索引建成(city, province, country)看着字段齐全结果country的等值条件用不上只能全索引扫一遍去重还是得建临时表。排顺序这件事差一个位置性能差一个数量级。还有一个容易被忽视的点索引必须是覆盖的。如果查询只需要这三个字段索引里全部包含那就是纯索引扫描连回表都省了。一旦你在 SELECT 列表里多加一个不在索引里的字段每一行都要回表松散扫描瞬间退化成密集扫描性能立刻掉下来。所以我会养成习惯写完 DISTINCT 语句数一下 SELECT 列表里的列数跟索引覆盖的列数对一遍。4.3 表达式 DISTINCT 与函数索引SELECT DISTINCT DATE_FORMAT(create_time, %Y-%m) FROM orders这种按月去重的写法在 8.0.13 之前是彻底没救的——函数包裹让索引完全失效只能全表扫加临时表。8.0.13 引入了函数索引也叫表达式索引可以直接给表达式建索引ALTER TABLE orders ADD INDEX idx_month ((DATE_FORMAT(create_time, %Y-%m)));建完之后这个 DISTINCT 就能命中索引了。注意语法上必须用双层括号这是函数索引的固定写法少一层会报错。另外函数索引对写入有一点额外开销只在对查询性能有明确要求的场景用。如果版本不支持函数索引退而求其次的方案是加一个冗余的生成列generated column把表达式结果落成一列再给这一列建普通索引。这个方案在 5.7 上就能用代价是多占一点存储。5. COUNT(DISTINCT) 的深水区5.1 单列与多列的执行差异COUNT(DISTINCT col)是我见过被用出最多花样的地方。从原理上说它必须知道每个不同值的出现次数吗不需要它只需要知道有多少个不同的值所以理论上可以走松散索引扫描。但实际能不能走版本差异非常大。单列COUNT(DISTINCT)相对友好8.0 上只要列上有合适的索引Extra 里就能看到Using index for group-by扫描的是不同值的个数而不是行数。5.7 上的支持范围就窄一些很多时候退化成扫描索引全部条目。多列COUNT(DISTINCT a, b)在 5.7 上会被改写成派生表先物化一个去重的临时表再在外面 count。你能在 EXPLAIN 里看到derived那一行rows动辄上百万开销非常可观。8.0 对这条路做了优化满足条件时也能走索引但前提依然是“去重列构成索引前缀”。所以如果你在 5.7 上写多列COUNT(DISTINCT)觉得很慢那不是错觉是执行计划决定了的。-- 5.7 上大概率物化派生表 SELECT COUNT(DISTINCT province, city) FROM user_addr; -- 用拆分的方式绕开两列各建合适索引后分别统计 SELECT COUNT(*) FROM ( SELECT province, city FROM user_addr GROUP BY province, city ) t;5.2 超大数据量下的精确与近似方案当数据量到了“扫一遍索引都要几十秒”的级别就该考虑换思路了。这里没有银弹只有两套权衡。精确方案是预聚合。建立一个按天或者按小时的汇总表每天定时任务跑一次GROUP BY把结果固化下来查询时直接读汇总表。代价是实时性差、维护成本高好处是结果绝对准确、查询毫秒级返回。报表类需求我几乎都推荐这条路因为报表本来就不要求秒级实时。近似方案是概率基数统计算法比如 HyperLogLog 这类思路用极小的固定内存估算不同值的个数误差一般在 1% 以内。适合“UV 统计”这种业务上能接受少量误差的场景。落地的做法可以是在应用层用集合型数据结构累加也可以是定期跑批把结果写进估算结构。选之前一定要跟业务方确认误差范围统计数字对不上是要背锅的。还有一种折中位图。如果去重列的取值范围很小比如状态、城市、渠道这种枚举型字段可以为每个值维护一个 bitmap用位运算算基数既精确又极快。缺点是只适用于低基数字段高基数字段的内存会爆炸。5.3 分片统计的跨片去重数据分片之后COUNT(DISTINCT)会遇到一个数学上的坑各分片的去重数相加不等于全局去重数。同一个 province 在三个分片都出现简单相加会多算两次。正确的做法取决于业务容忍度。要求精确的话只能把去重键拉到一个地方做全局去重比如把所有分片的province, city组合拉到中间层用哈希集合去重代价是网络传输量和内存占用。要求不严格的话可以用估算算法各分片算出本地的哈希素描再合并素描得到全局估算值HyperLogLog 天生支持这种合并操作这也是它在分布式统计里流行的原因。我在实际项目里更倾向于“把去重键收敛到一张全局维表”这个笨办法。维表数据量小去重键的组合数通常远小于事实表行数维护成本可控查询时直接对维表 count省掉了所有跨片去重的麻烦。6. 把 DISTINCT 拆掉的四种改写手法6.1 EXISTS 改写消灭 join 放大最常见的 DISTINCT 来源是“一对多连接后去重”。比如查“下过订单的用户”很多人会写SELECT DISTINCT u.id, u.name FROM users u JOIN orders o ON o.user_id u.id WHERE o.status 1;一个用户下十单就要在结果集里处理十行再去重。改成 EXISTS 之后每命中一个用户就直接短路根本不会产生放大的行SELECT u.id, u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.status 1 );前提是orders(user_id, status)上有联合索引或者orders(user_id)上有索引否则子查询会变成全表扫反而更慢。改写前先看一眼orders表的索引这是必须做的功课。实测下来在十万级用户、百万级订单的量级上这个改写通常有 5 到 20 倍的提升而且结果集越大差距越明显。6.2 窗口函数改写取每组第一条“每个城市取最新一条记录”这类需求很多人会用 DISTINCT 加自连接硬凑写出来又长又慢。8.0 之后直接用窗口函数语义清晰执行也更快SELECT city, user_id, created_at FROM ( SELECT city, user_id, created_at, ROW_NUMBER() OVER (PARTITION BY city ORDER BY created_at DESC) AS rn FROM user_addr ) t WHERE rn 1;这里的核心变化是从“先放大再去重”变成了“排序后按组取第一条”。前者需要处理全部行后者虽然也要排序但排序是分区内进行的配合(city, created_at)索引可以大幅减少排序量。需要注意的是如果业务上允许并列同一城市同一时刻有多条ROW_NUMBER()会随便挑一条结果不稳定要稳定就用RANK()或者把ORDER BY的字段补全到唯一。这个细节我在生产上踩过同一份报表两次导出结果不一样查了半天才发现是并列值没处理。6.3 预聚合与冗余字段如果某个 DISTINCT 查询被高频调用比如首页的城市列表与其每次都去重不如把结果固化下来。两种做法一是建汇总表定时任务刷新。适合变化不频繁、实时性要求不高的维度数据。二是加冗余字段比如在用户表里冗余一个“最后下单时间”查询时直接过滤不需要每次都去 orders 表做去重扫描。冗余字段的代价是写入时要维护一致性通常放在事务里或者靠异步补偿。我倾向于用汇总表因为它的数据链路是单向的——只读不写出问题好回滚。冗余字段一旦维护逻辑出 bug脏数据很难追溯和修正。6.4 UNION ALL 加外层去重当 DISTINCT 的列很少、但过滤条件很复杂时可以拆成几个UNION ALL的子查询各自走最优索引再在外层统一去重SELECT DISTINCT city FROM ( SELECT city FROM user_addr WHERE province ZJ UNION ALL SELECT city FROM user_addr WHERE province JS ) t;这样每个子查询都能用上(province, city)索引做覆盖扫描外层只需要对少量结果去重。比一条大 SQL 走 OR 条件或者跨多个省份全表扫要快得多。但有个坑必须提UNION不带 ALL本身就会去重而且是排序或者哈希去重成本不低。如果你已经在外层写了 DISTINCT内层还写 UNION等于去重了两次。我见过有人为了“保险”两个都写结果性能直接砍半。7. 踩坑实录与排查速查表7.1 加了 DISTINCT 反而更慢的三种情况情况一本来就没有重复。表上主键唯一查询只涉及单表加 DISTINCT 完全是白建临时表。这种写法我见过很多动机通常是“防止万一有重复”但代价是每次查询都多一次全量去重。情况二SELECT 列表里有大字段。SELECT DISTINCT *或者带了 TEXT、JSON 列内存临时表不支持这类字段直接落盘。八百多万行的表落盘去重能跑出分钟级。解决办法要么是只选需要的列要么改用GROUP BY加预聚合。情况三DISTINCT 挡住了索引提前终止。带LIMIT的分页查询里如果没有 DISTINCTMySQL 扫够 N 行就能停加了 DISTINCT 之后为了确保去重结果正确有时候不得不把所有候选行都处理完才知道前 N 个去重值是什么。除非排序和去重列跟索引顺序完全一致优化器才能恢复提前终止的能力。7.2 结果对不上排序规则与尾部空格线上出现过一类问题同一份 SQL测试环境去重后 1200 行生产环境 1250 行数据量级差不多就是数不对。排查下来是两个库的字符集排序规则不一致——测试库是utf8mb4_general_ciPAD SPACE忽略尾部空格生产库是 8.0 默认的utf8mb4_0900_ai_ciNO PAD。数据里有一批带尾部空格的脏数据两个库判定的“相同”标准不同去重结果自然不同。排查这类问题的顺序是先SHOW CREATE TABLE对比两边列的字符集和排序规则再抽样看看有没有大小写混杂、首尾空格、全角半角混用的数据。如果确认是数据质量问题优先清洗数据而不是在 SQL 里到处加TRIM()或者BINARY——那会让索引彻底失效把性能问题变成更大的性能问题。7.3 排查速查表现象最可能的原因排查动作处理方向EXPLAIN 出现 Using temporary去重列用不上索引前缀看索引定义与 SELECT 列顺序调整复合索引顺序或补索引EXPLAIN 出现 Using filesort排序走了内存/磁盘排序看 ORDER BY 列是否在索引里让排序列进入索引或去掉不必要的排序加了 DISTINCT 没变快上游 join 放大严重对比加/不加 DISTINCT 的返回行数改 EXISTS 或预聚合去重结果条数对不上排序规则或尾部空格差异对比两库表定义抽样查脏数据统一排序规则清洗数据COUNT(DISTINCT) 很慢多列去重物化了派生表看 EXPLAIN 是否有 derived改写成子查询 GROUP BY 或建索引DISTINCT 结果顺序不可靠8.0 不再保证隐式排序检查 SQL 是否显式 ORDER BY显式加 ORDER BY或在外层排序内存临时表落盘结果集超 tmp_table_size 或含大字段查 Created_tmp_disk_tables 占比减少 SELECT 列或改预聚合我个人在实际操作中的体会是DISTINCT 本身并不复杂复杂的是它前面那一长串 FROM、JOIN、WHERE 决定的“输入有多脏”。真正要治的不是 DISTINCT 这个关键字而是它上游产生重复的那一环。下次再看到一条慢的去重 SQL先别急着调参数先看 EXPLAIN 的 Extra、再数一数加了 DISTINCT 前后行数差多少大概率就能定位到问题在索引、在连接、还是在数据本身。
阅读完成 · 觉得有帮助？
