尧图网页设计 
现在面试但凡问数据库基本绕不开 DQL。哪怕你简历上写的是“熟悉 MySQL”对方第一道题往往就是让你写一条关联查询或者直接抛一句DQL 和 SQL 到底什么关系很多人一听就懵觉得 DQL 不就是 select 吗对也不对。DQLData Query Language是查询语言在 SQL 体系里特指以 SELECT 为核心的查询语句但它不代表只能做简单的数据抽取。排序、去重、分组、聚合、子查询、关联、窗口计算、甚至部分逻辑处理都能用 DQL 完成。我在实际带人的时候发现新人往往陷入两个极端一是觉得 select 太简单不值得系统学二是把 DQL 和 MySQL 的杂七杂八功能混在一起抓不住主线。这两种心态都不对。DQL 是数据库操作里最复杂、最有深度、也最影响性能的部分一条写得好的查询和一条写得烂的查询差距可能不是几毫秒而是几十倍甚至直接拖垮线上业务。这篇文章我就把 DQL 的完整脉络捋一遍从语句执行顺序、单表操作细节、多表 JOIN 和子查询、窗口函数到执行计划和索引利用按我平时排查问题的思路来写。即使你现在只会 select * from xxx看完应该也能写出一条合格且有性能意识的生产级查询。1. 一条 DQL 语句的真实执行链路SQL 不是从上往下跑的新手最难纠正的一个思维是SQL 是顺序执行的从 FROM 开始一行一行读。实际上SQL 是描述性语言——你告诉数据库“我要什么结果”而不是“你要怎么一步步算”。这句话我在刚入行的时候根本理解不了直到我看了一次慢查询日志才真正明白执行顺序这东西有多重要。1.1 SELECT 六大子句的逻辑执行顺序写 DQL 时我们的书写顺序是 SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但数据库引擎实际的处理顺序完全不是这样。一条完整的 SELECT 语句逻辑层面的执行顺序如下FROM含 JOIN→ WHERE → GROUP BY → HAVING → SELECT含窗口函数 → ORDER BY → LIMIT我给很多人画过这个顺序几乎所有人第一次看都觉得反直觉。但请记住这是理解 DQL 整个体系的钥匙。为什么 WHERE 里不能用聚合函数比如WHERE COUNT(*) 100因为 WHERE 是在 GROUP BY 聚合之前执行的行级过滤条件此时聚合操作还没发生当然不能用聚合函数。HAVING 为什么能过滤聚合结果因为 HAVING 排在 GROUP BY 之后作用对象已经是分组后的结果集了。SELECT 中的列别名为什么不能被 WHERE 引用别名是在 SELECT 阶段才生成的WHERE 阶段根本还未生成别名。搞清楚这个顺序能解决一批很典型的“代码看着没问题跑起来就报错”的场景。我举个例子-- 错误写法WHERE 阶段还没有 year 字段 SELECT YEAR(create_time) AS year, COUNT(*) FROM orders WHERE YEAR(create_time) 2024;这条其实不报错因为它没在 WHERE 里用别名。真正会报错的写法是WHERE year 2024这个别名在 WHERE 阶段不存在。所以最稳妥的做法是老老实实写成YEAR(create_time) 2024或者放到 HAVING 里但 HAVING 的性能通常比 WHERE 差能前置过滤就前置。包含 JOIN 的场景顺序还要在前ON 条件在 FROM 阶段触发先确定关联后的中间结果集WHERE 再基于这个中间结果集做过滤。很多人困惑“为什么 ON t1.id t2.id AND t2.status 1 和 WHERE t2.status 1 结果一样又不一样”本质就是因为 ON 和 WHERE 的执行时机不同。左连接时ON 的约束作用在驱动表的匹配过程WHERE 约束的是最终结果集所以 LEFT JOIN 场景下把被驱动表右表的过滤条件写在 WHERE 里会导致外连接退化成内连接的效果。这一点在后面的 JOIN 章节我会专门再讲。1.2 从 MySQL 架构层面看 DQL 是怎么被“跑”出来的逻辑执行顺序是纸面上的规则落到 MySQL 引擎里还要经过一系列真正的执行过程。一条 DQL 语句从客户端发出到返回结果大致经过这些环节连接处理器校验账号权限在系统表里检查你是否有该表的 SELECT 权限。解析器Parser先做词法分析把字符串拆成 token再做语法分析生成解析树。语法错误在这一步直接爆出来比如关键字拼错、括号不匹配。预处理器进一步检查表和列是否存在、别名是否有歧义然后对语句做权限的二次校验。优化器Optimizer这个环节最关键。MySQL 会分析哪张表先驱动、选择哪个索引、JOIN 顺序怎么安排、子查询要不要改写成 JOIN然后生成执行计划。你现在看到的大部分慢查询优化本质上都是在帮优化器做更好的选择。执行器按照执行计划逐行调用存储引擎接口做数据读取和行过滤。存储引擎层也就是 InnoDB 干实事的地方——从内存 Buffer Pool 或磁盘页中读取记录返回给上层。我在排查线上问题时会先用 EXPLAIN 看执行计划对应优化器产物再根据 type 和 key 判断是走索引还是全表扫描。多个慢查询对比后会发现大部分慢查询的问题不在 CPU 或磁盘而在优化器没选择你预期的索引路径。理解执行链路的另一个好处是你能预判某条语句大概在哪个环节出错、在哪个环节耗时高而不是一上来就瞎调参。提示DQL 的终极优化思想就是两条——减少扫描的数据量、减少返回的数据量。前者靠索引后者靠过滤条件写的准确度和必要的投影列裁剪。后面所有查询技巧基本都是围这两条展开的。2. 单表 DQL 操作过滤、聚合、排序和分页里的细节单表查询看起来简单但真正把 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT 用对、用透才算入了 DQL 的门。我见过太多人在单表查询里栽跟头光是GROUP BY和HAVING的边界问题就能筛掉一半的候选人。2.1 WHERE 的几种过滤层级等值、范围、模糊、条件与 NULL 的坑WHERE 子句负责行级过滤执行时机在所有聚合和投影之前所以它是 DQL 中最能影响性能的部分。基本类型就那几类等值过滤、范围过滤BETWEEN AND和IN、模糊过滤LIKE、联合条件AND/OR以及 NULL 判断IS NULL/IS NOT NULL。写 WHERE 有几个容易忽略的细节第一NULL 永远不能和等号做匹配。WHERE col NULL不会报错但也不会返回任何行因为 SQL 的三值逻辑里 NULL 就代表未知未知和未知比不出来结果。必须用IS NULL或IS NOT NULL。-- 错误习惯 SELECT * FROM user WHERE deleted NULL; -- 正确写法 SELECT * FROM user WHERE deleted IS NULL;第二IN列表很大或很小的时候优化器的处理策略不一样。列表很小时等价于多个等值 OR列表很大时 MySQL 会基于基数估算是否要转成临时表半连接。实际开发中IN列表超过几百项就该考虑 JOIN 或临时表了不然解析和估算本身就会消耗时间。第三LIKE前缀通配符的问题。LIKE %keyword%会导致索引失效因为无法从 B 树有序性中做前缀匹配但LIKE keyword%可以利用索引。业务场景如果确实需要前后模糊匹配我一般建议引入全文索引或搜索引擎而不是硬写在 MySQL 的 LIKE 里。第四OR 的索引利用。WHERE a 1 OR b 2如果 a、b 各有单列索引MySQL 可能会走索引合并Index Merge但合并消耗不小条件多时不如改写为UNION或者建一个联合索引来得稳。2.2 GROUP BY 的分组语义与聚合函数的真实行为GROUP BY 将结果集按一个或多个列拆成若干分组分组后再把聚合函数作用于每个分组。这个“分组后再聚合”的语义是理解聚合查询的核心。写聚合查询时最大的误区有两个。误区一SELECT 的列没出现在 GROUP BY 中。比如SELECT user_id, product_id, MAX(amount) FROM orders GROUP BY user_id;如果 MySQL 开启ONLY_FULL_GROUP_BY默认是开的这条会直接报错product_id 既不在聚合函数里也不在 GROUP BY 中。这其实是合理的因为一个 user_id 分组下可能有多个 product_id数据库不知道该选哪一个。关闭这个模式虽然也能跑但取到的 product_id 是随机行为线上绝对不能这么搞。误区二把 WHERE 能过滤的事情丢给 HAVING。比如“查每个用户 2024 年的订单总额只要总额超过 1000 的”可以写SELECT user_id, SUM(amount) AS total FROM orders WHERE order_year 2024 GROUP BY user_id HAVING total 1000;WHERE 在分组前过滤掉不满足的行减少了进入分组的数据量HAVING 只处理分组后的结果。如果把order_year 2024也放进 HAVING语义没错但性能差得多——所有年份的数据都得先分组再过滤。再补充一个聚合函数的使用心得。COUNT(*)、COUNT(1)、COUNT(col)三个看起来差不多实际行为不同COUNT(*)统计表中所有行数COUNT(1)也统计所有行数唯一的差异只是写法COUNT(col)只统计该列非 NULL 的行数。所以想统计某列的实际有效值数量就用COUNT(col)想查总行数用COUNT(*)就好。还有SUM(col)遇到全 NULL 分组时返回 NULL 而不是 0要配合IFNULL(SUM(col), 0)使用这点在报表开发里相当关键。2.3 ORDER BY、LIMIT 和深分页问题的解决思路ORDER BY 排序本身不难但没有用好索引时MySQL 会生成一张临时文件做 filesort也就是把结果集全部读出来在磁盘上排一遍一旦结果集大这里的开销会相当吓人。为什么联合索引(a, b)能直接满足ORDER BY a, b因为索引本身有序引擎按索引顺序读出来就是排好序的结果。反过来ORDER BY b或者ORDER BY a DESC, b ASC通常无法直接利用索引顺序混合排序方向和跳过前导列的排序都会让优化器放弃索引有序性。LIMIT 深分页是生产环境最典型的性能陷阱。看这条语句SELECT * FROM orders ORDER BY id LIMIT 100000, 20;它的执行逻辑是找到前 100020 行丢掉前 100000 行只返回 20 行。前 100000 行的扫描和排序全部是白白浪费的。这个场景的常见解法有两种而且都是我在实际项目中验证过的第一种延迟关联先缩小范围再回表取整行。子查询先只查主键 id跳过大量无用的回表和整行数据扫描SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;第二种基于索引位置做条件翻页也就是记住上一页最后一条记录的位置。通常用WHERE id 上一页最大id ORDER BY id LIMIT 20。这种写法在数据量大时性能稳定因为每次都只扫描 20 行左右。缺点是翻页跳转不方便适合“下滑加载更多”的 App 场景不适合传统分页组件。3. JOIN 和子查询多表查询的底层逻辑与常见坑点真正的 DQL 复杂度集中体现在多表关联和子查询里。左连接变内连接、ON与WHERE的边界、EXISTS与IN的选择这些都是面试必问、工作必用的知识点也是排查数据结果异常时最容易定位的源头。3.1 三种 JOIN 的本质与前前后后的驱动关系JOIN 关联的本质是笛卡尔积基础上的匹配过滤先按某种方式把一个笛卡尔积的空间确定下来再用 ON 条件把匹配的行挑出来。INNER JOIN 只保留两边都匹配的行LEFT JOIN 保留左表全部行右表没匹配上就补 NULLRIGHT JOIN 反过来。生产上我基本不用 RIGHT JOIN把右表当驱动表改写为 LEFT JOIN 逻辑会更清晰也更符合业务阅读习惯。JOIN 执行时真正棘手的是驱动顺序。MySQL 的小表驱动大表原则就是告诉优化器先读取驱动表的范围再去被驱动表上逐行探测匹配关系被驱动表如果能走索引每次探测就是索引查找总成本就是“驱动表行数 × 被驱动表索引查找成本”。所以让结果集小的表当驱动表通常更高效。我之前接手过一个线上慢查询12 张小表关联关键是驱动顺序选错了优化器把一张 5000 行的小表放到被驱动位置另一张 500 万行的大表当驱动表每次探测都要扫大表的普通索引单次查询跑出来 8 秒多。后来通过调整 JOIN 顺序加了一个冗余的关联条件让优化器重新评估直接把耗时拉到了 40 毫秒以下。这个案例说明执行计划不是黑盒它是可以被理解和干预的。3.2 ON 与 WHERE 的时机差异左连接“退化”的典型场景LEFT JOIN 中最容易踩的坑是把右表的过滤条件写进 WHERE。考虑这样一个业务场景查所有用户及其 2024 年的订单数没有订单的用户也要保留。-- 期望结果保留没有订单的用户 SELECT u.id, u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.year 2024 GROUP BY u.id, u.name;这条语句的结果会让没有订单的用户直接消失。因为 LEFT JOIN 先把所有用户和所有订单做完外连接右表无匹配时 order 的列全是 NULL然后 WHERE 阶段执行o.year 2024NULL 行被过滤掉外连接变成了内连接的效果。正确做法是在 ON 阶段就把年度限制带进去因为 ON 的职责是定义连接时右侧表参与匹配的行此时的过滤效果不会影响左侧表保留行SELECT u.id, u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.year 2024 GROUP BY u.id, u.name;这个坑在面试中特别经典因为候选人写出来的 SQL 往往“看着能跑”但查出来的统计数字不对而且不易察觉。我自己排查过多次类似的数仓报表差异最后都定位到同一个逻辑右表条件错放在 WHERE 里。还有一个类似的坑是“多表 JOIN 时过滤条件放哪”。INNER JOIN场景下 ON 和 WHERE 的结果一致因为它们都是做行的匹配约束WHERE 后置但最终交集不变LEFT JOIN场景下则完全不同。所以判断标准很简单如果外连接需要保留一侧的完整数据所有对另一侧表的过滤条件都应写在 ON 子句中。3.3 IN、EXISTS 和 JOIN子查询写法如何影响真实性能子查询和关联查询很多时候能写成同一个业务结果但底层执行策略不同。IN在 MySQL 里可能被优化成半连接semi-join也就是“只要存在匹配就返回”EXISTS则是逐行去探测子查询是否存在匹配行。大表驱动小表时用IN内层表数据量小且外层大、小表驱动大表时用EXISTS内层大表走索引逐行探测是常见经验法则但 MySQL 优化器近年来也会自动改写所以更稳妥的做法是直接看 EXPLAIN。此外标量子查询要谨慎。SELECT 子句里每行都执行一次子查询如果外层返回 10 万行内层子查询就会执行 10 万次。任何一个内层查询稍微慢一点整条语句就会被放大 10 万倍。虽然 MySQL 会尝试对相关子查询做缓存但效果不稳定。能改写成 JOIN 的优先 JOIN。4. 窗口函数DQL 中最该掌握的高级计算能力窗口函数Window Function是 DQL 中经常被低估的部分。很多开发者处理“排名、同期对比、累加、分组内 TopN”这类需求时还在用子查询 临时变量 自连接拼来拼去代码又长又容易错。窗口函数直接把“基于当前行所在分组做计算”这件事变成了独立的语法维度。4.1 OVER 子句的完整语法PARTITION BY、ORDER BY 和窗口框窗口函数的标准结构是三件套函数本身 OVER ()内定义的分区PARTITION BY 组内排序ORDER BY 可选的窗口框ROWS BETWEEN ... AND ...。和普通GROUP BY的一个核心区别是窗口函数不会导致行数收缩。每一行输出都保留只是在这一行旁边额外计算出一个窗口值。拿一个典型的例子来说查每个用户的订单金额排名以及按时间累计的消费金额。SELECT user_id, order_date, amount, RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount FROM orders;RANK()返回组内金额排名SUM(amount) OVER (...)用ROWS BETWEEN定义了滑动的窗口范围——从组内第一行到当前行这样就得到了“当前时刻的累计消费额”。如果你不加ROWS BETWEEN默认的窗口框是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是从组内起点到当前行所有和当前行排序值相等的行。这个细微差异在 ORDER BY 有重复值时会复现出不同的数值是窗口函数的经典暗坑。4.2 常见窗口函数的适用场景与挑选技巧我把实际工作中最常用的一批窗口函数整理成一张速查表方便用到的时候直接对着挑函数类别代表函数适用场景特别注意排名ROW_NUMBER、RANK、DENSE_RANK排行榜、分组 TopNRANK 有并列跳号DENSE_RANK 不跳号聚合SUM、AVG、COUNT、MAX、MIN累计值、移动平均结合 ROWS BETWEEN 定义窗口偏移LAG、LEAD环比、同比、差值对比LAG(col, n) 取前 n 行首尾FIRST_VALUE、LAST_VALUE组内最大最小值对应记录LAST_VALUE 要配合 ROWS 定义避免只取到当前行排名类的三个函数差异是高频考点。ROW_NUMBER()无论有没有并列都依次给 1、2、3RANK()遇到并列会跳号比如两个第一名后直接是第三名DENSE_RANK()并列不跳号两个第一名后还是第二名。业务要做“并列第一但下一名跳过”的效果用 RANK要做稳定行号用 ROW_NUMBER要做紧凑排名用 DENSE_RANK。我用窗口函数解决过一个比较棘手的问题取每个用户最近三笔订单的品类分布。如果用普通 GROUP BY行数会收缩拿不到多行明细如果自连接条件复杂且爆炸。改成如下写法就干净很多SELECT user_id, order_id, category FROM ( SELECT user_id, order_id, category, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 3;内层先给每个用户的行按时间倒序编号外层过滤前 3 行。这是取分组 TopN 的标准套路也是窗口函数最典型的实战应用之一。它利用子查询把窗口计算结果先算出来再在外层做条件过滤。窗口函数不能直接出现在 WHERE 里这是很多人一开始常犯的语法错误——WHERE rn 3如果写在同一个 SELECT 里会直接报“Unknown column”。4.3 窗口函数与 GROUP BY 的组合使用窗口函数也可以作用在已经有 GROUP BY 的结果集之上也就是说分组聚合之后的结果当作基准再套一层窗口函数做二次分析。这种场景在报表里非常常用。比如统计每个用户订单总额后再算所有用户的平均订单总额SELECT user_id, SUM(amount) AS total_amount, AVG(SUM(amount)) OVER () AS avg_amount, SUM(amount) / AVG(SUM(amount)) OVER () AS ratio FROM orders GROUP BY user_id;这里AVG(SUM(amount)) OVER ()先把 GROUP BY 得到的每组总额再取全体平均SUM(amount)是普通聚合OVER ()不写分区就是对整体开窗。很多人在这一步卡住因为窗口函数里其实还可以嵌套聚合函数。理解了这个写法你写报表 SQL 的层次感会提升一个档次。5. 执行计划与索引利用把慢 DQL 调成快 DQLDQL 写得对不代表写得好。真正衡量一条查询是否合格要拿 EXPLAIN 出来看执行计划。执行计划里藏着索引利用情况、扫描行数、排序方式、连接方式这些才是优化查询的决策依据。把 DQL 从“能跑”变成“跑得快”是区分业务代码和工程能力的一道分水岭。5.1 EXPLAIN 关键字段一眼定位查询性能瓶颈执行EXPLAIN SELECT ...得到的结果表我重点看四个字段。type访问类型从好到差大致是systemconsteq_refrefrangeindexALL。工作中只要看到ALL全表扫描在大表上出现基本就是性能隐患。range是范围扫描可用ref是非唯一索引等值匹配正常eq_ref是唯一索引等值匹配比较理想。key实际用的索引如果为 NULL说明这一行没用索引。rows预估扫描行数这个数字不是精确值但数量级能说明问题。同一业务语句rows 从 100 万降到 1000优化就是成功的。Extra出现Using filesort表示排序没走索引额外做了一次文件排序出现Using temporary表示用了临时表常见于 GROUP BY 或 DISTINCT 没用好索引出现Using index是好事表示覆盖索引连回表都省了。EXPLAIN 只能看到预估执行计划如果怀疑实际执行差异可以用EXPLAIN ANALYZEMySQL 8.0拿到真实的执行时间、扫描行数和各阶段耗时。之前排查一条线上 5 秒的统计查询EXPLAIN 显示预估只扫 2 万行但 EXPLAIN ANALYZE 实测扫了 2000 万行最后发现是统计信息过期导致优化器选错索引ANALYZE TABLE之后就正常了。这类问题光看 EXPLAIN 是会被骗的这正是我用实证工具的原因。5.2 索引失效的几种典型写法到底哪里写歪了联合索引和单列索引的使用规则说简单也简单说细也细。我被问得最多的问题是“为什么我建了索引但 EXPLAIN 就是不走”。几种典型的索引失效场景我直接总结成清单对索引列做了函数运算WHERE YEAR(create_time) 2024不会走 create_time 索引改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01才行。隐式类型转换字符串列和数值比较时MySQL 会把列转成数值索引失效。最常见的就是WHERE phone 13800001234而 phone 列是 varchar。联合索引不满足最左前缀索引(a, b, c)可以支持a、a,b、a,b,c三种条件组合但WHERE b 1 AND c 2这种跳过 a 的查询用不了这个联合索引。反之如果跳过的是中间列如WHERE a 1 AND c 2只能用到 a 这一列的前缀部分。前导通配符 LIKELIKE %keyword%无法走索引LIKE keyword%可以。这不是玄学是 B 树前缀有序性的天然限制。OR 两边字段条件不统一一边有索引一边没有优化器可能放弃索引合并直接全表扫。用UNION拆开或重建联合索引都能解决。对索引列做计算WHERE amount 100 200不会走索引改为WHERE amount 100就正常了。规则的底层逻辑是B 树的叶子节点存的是原始列值函数或运算后的值无法直接做二叉查找。5.3 覆盖索引、回表和话说“SELECT *”的代价InnoDB 的二级索引叶子节点存的是索引列值 主键值。当你用二级索引查到目标行时如果查询需要的其他列不在索引里引擎就要拿着主键再去聚簇索引里取一次完整行这就是“回表”。回表次数多了查询自然慢。避免回表的最直接方法是让查询列全部包含在索引中这就是覆盖索引Using index。比如表上有联合索引(user_id, status)你执行SELECT user_id, status FROM t WHERE user_id 100这条查询只从二级索引就拿齐了所有需要的列完全不用回表。这也是我平时不太赞成在业务代码里无脑SELECT *的原因。SELECT *会强制读取所有列索引覆盖条件几乎不可能全部满足每行都要回表取完整数据同时把不需要的大字段比如 TEXT也拉出来白白增加网络传输和内存消耗。生产环境的规范通常是SELECT 只列出业务真正需要的列控制返回的字段集合配合覆盖索引让查询更轻。如果你的表列很多但每次只查其中两三个字段一个精心设计的联合索引能覆盖绝大多数高频查询性能提升非常明显。6. 一次真实慢查询的完整排查链路从现象到根因到修复排查经验比工具更能决定一条查询能不能被救回来。这里我完整复盘一个实际的慢查询案例把从发现问题、看执行计划、推断原因、修改方案、验证结果的整个过程都过一遍。这个案例在 DQL 优化上非常有代表性几乎覆盖了前面所有章节的要点。6.1 现象订单统计报表突然从 300ms 涨到 8s当时一个订单报表接口的查询语句是SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_cnt, SUM(o.amount) AS total_amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.status 1 WHERE u.reg_time 2024-01-01 GROUP BY u.user_id, u.user_name ORDER BY total_amount DESC LIMIT 100;线上监控显示这条 SQL 的 p95 耗时从 300ms 飙到 8 秒左右。单独把它拿出来跑确实要 8 秒多。这是我印象最深的一条慢查询因为它的每个部分看起来都合情合理LEFT JOIN 保留所有用户、统计有效订单、过滤注册时间、按金额降序取前 100没有任何“低级”错误。6.2 EXPLAIN 定位三个关键线索执行EXPLAIN后结果表里几个字段让我瞬间锁定了问题访问类型users 是 ALL也就是说 500 万注册用户做全表扫描。rows 估算orders 预计约 680 万行参与关联。Extra 字段Using temporary; Using filesort分组和排序都没走索引全部落在临时表和文件排序上。核心问题是驱动表选择失误。users 表全表扫描后再去关联 orders等于先拖出 500 万行用户再逐一去订单表匹配。这么大的驱动行数哪怕每次关联走索引总成本也是 500 万次索引探测而且 ORDER BY total_amount DESC 的排序发生在分组聚合之后结果集和临时表都很庞大filesort 直接把内存拖垮到磁盘排序。6.3 修复方案缩小驱动行数、改写为内连、验证结果第一步先把 WHERE 阶段就缩小 users 表的扫描范围。reg_time 2024-01-01说明只需要查 2024 年之后注册的用户但这条语句已经带了条件为什么还是 ALL因为 users 表上只有一个主键索引和以其他列建的索引reg_time 上没有可用的索引。所以第一个动作就是给users(reg_time)建普通索引让 WHERE 能走 range驱动行数直接砍到 50 万左右。第二步优化器选择 users 作为嵌套循环的驱动表但真正要保留的其实是“所有 2024 年后注册用户 有订单记录的用户”。如果订单数据量更大驱动行数更少那么完全可以反过来让 orders 先按 status1 过滤后当驱动表再关联用户。考虑到业务侧能接受去掉完全没有订单的用户报表本来就是看有效订单数据我把 LEFT JOIN 改写为 INNER JOIN这样优化器在评估时会优先选择过滤后更小的表当驱动表执行计划立刻好了很多。第三步处理临时表和文件排序。ORDER BY total_amount DESC是在聚合后的别名上排序无法在索引阶段直接完成因此必须有结果集排序动作。这里我选择把排序下推到子查询里让子查询先算出每个用户的金额再排序外层回表取用户名SELECT t.user_id, u.user_name, t.order_cnt, t.total_amount FROM ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE status 1 GROUP BY user_id ORDER BY total_amount DESC LIMIT 100 ) t LEFT JOIN users u ON t.user_id u.user_id;这个写法里orders 表只按 status1 过滤后做分组聚合先把 100 个用户 id 和聚合指标算出来再回表补 user_name。整个中间结果集从“几百万用户 几百万订单”变成了“100 行”临时表和排序压力下降了几个数量级。最后用 EXPLAIN 验证新的执行计划orders 走了 status 的索引rows 估算降到 40 万左右Extra 里不再有Using temporary只有一次基于「分组后 100 行」的文件排序或者直接在内存排序完成。线上灰度跑了一周p95 稳定在 120ms 以内。6.4 这个案例说明的四个通用优化原则复盘这次排查我把经验压缩成几条可复用的原则供你在自己的慢查询优化里参考先看访问类型全表扫描一定要减少。不管业务逻辑多明确大表 ALL 就意味着扫描成本失控第一步永远是把索引补齐。驱动顺序直接影响最终扫描量。执行计划里第一个表就是驱动表判断它会不会把大量行送到后续步骤。小结果集驱动大表所有 JOIN 和子查询都遵循这条。中间结果集越大后续的临时表、排序、回表越痛。看到Using temporary; Using filesort要敏感它们通常意味着聚合或排序需要一个完整的大结果集。业务表 JOIN 时能 INNER JOIN 就不要 LEFT JOIN。两者语义差异决定了优化器的选择余地很多报表查询从外层看都能接受内连接语义只是写的时候出于习惯用了 LEFT JOIN白白失去了优化空间。注意所有优化改完一定要回到业务验证结果。这种查询通常对应报表或接口改完不仅要看耗时还要和旧结果比对行数和关键统计值。DQL 优化的第一目标永远是结果正确第二目标才是性能达标。7. DQL 的技巧延展去重、条件聚合和同环比计算的实践经验除了核心语法DQL 在实际业务里经常要解决一些“看起来得写程序处理”的需求。其实这些需求都能在 SQL 层面完成三个最常见的场景是去重统计、条件聚合和同环比计算。把这几个技巧掌握住很多统计代码可以直接省掉。7.1 DISTINCT 的去重陷阱与替代方案DISTINCT 是对整个 SELECT 返回的列组合去重不是单独对某一列去重。SELECT DISTINCT user_id, status是去除 user_id 和 status 都相同的行如果你只想要“不同的 user_id”这个写法得到的结果是 user 和 status 的所有组合不是目标。单纯对单列去重我一般用 GROUP BY因为它更符合“按列聚合”的语义也更容易配合其他聚合字段。DISTINCT 在数据量大时往往在内存里维护一个临时 Set 结构去重占用不小。如果只是统计数量比如COUNT(DISTINCT user_id)能跑但尽量别在高基数列上频繁使用它有性能瓶颈。大数据量表里做 UV 统计我通常直接用COUNT(DISTINCT user_id)做小规模报表没问题但如果表上亿就得考虑用近似算法或者离线数仓了。7.2 条件聚合用 SUM CASE WHEN 在一个查询里完成多个统计指标业务报表里最常见的一个需求是“同时统计多种状态下的数量”。例如统计每个用户的有效订单数、取消订单数、退款订单数。常规做法是三个查询分别统计再合并但其实一条 DQL 就能完成SELECT user_id, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS valid_cnt, SUM(CASE WHEN status 2 THEN 1 ELSE 0 END) AS canceled_cnt, SUM(CASE WHEN status 3 THEN 1 ELSE 0 END) AS refunded_cnt FROM orders GROUP BY user_id;SUM(CASE WHEN ... THEN 1 ELSE 0 END)的原理是逐行判断条件满足则贡献 1不满足贡献 0然后把这个 0/1 序列加总得到的就是“满足条件的行数”。这种方法把一个表的多维度统计合并成一次扫描只回一次表性能远好于多次查询再在业务层拼接。判断某个条件时如果关心“是否至少存在一行”也可以用MAX(CASE WHEN ... THEN 1 ELSE 0 END)来实现布尔聚合。7.3 LAG/LEAD 实现同环比不用自连接就能算差额做报表时经常要算“今日订单量比昨日增长多少”。传统写法是自连接条件写a.date b.date - 1麻烦还容易错。用窗口函数 LAG/LEAD 就简单直观得多SELECT stat_date, order_cnt, LAG(order_cnt, 1) OVER (ORDER BY stat_date) AS prev_cnt, order_cnt - LAG(order_cnt, 1) OVER (ORDER BY stat_date) AS diff_cnt, ROUND( (order_cnt - LAG(order_cnt, 1) OVER (ORDER BY stat_date)) / LAG(order_cnt, 1) OVER (ORDER BY stat_date) * 100, 2 ) AS growth_rate FROM daily_order_stats ORDER BY stat_date;LAG(order_cnt, 1) OVER (ORDER BY stat_date)表示取当前行按 stat_date 排序后前一行即前一天的订单量。这样计算出的 diff_cnt 就是环比差值growth_rate 就是环比增长率。同比也一样把偏移量从 1 改成 365按日粒度即可。这种写法的优势是只扫一次表而且把“行与行之间的比较”这种原本需要用自连接或者程序来回处理的事直接交给 DQL 完成。8. 一个怎么强调都不过分的问题查询结果不对先从 DQL 的语义入手调优看性能但排查数据正确性问题得回到 DQL 语义本身。我最常遇到的数据异常不是索引问题而是语义理解问题。8.1 三值逻辑NULL 如何静默地改变结果集SQL 的 WHERE 条件处理 NULL 时用的是三值逻辑——TRUE、FALSE、UNKNOWN。一个简单条件如果遇到 NULL通常结果为 UNKNOWN它不会进入结果集。所以WHERE amount 100会把 amount 为 NULL 的行过滤掉但很多业务上你其实是想把“未填金额”的行也查出来。这种场景要显式加OR amount IS NULL。另外一个更隐蔽的坑是多条件组合和 NULL 的关系。比如WHERE status paid OR refund_amount 100如果某行 status 为 NULLrefund_amount 为 50按直觉应该被第二个条件选出但在三值逻辑里第一个条件结果为 UNKNOWNOR 的最终结果依然是 TRUE 吗——取决于第二个条件能不能独立判定为 TRUE。这里第二个条件是 TRUE所以整行还是会被选中。但如果两个条件都涉及 NULL结果就变成 UNKNOWN整行被排除。三值逻辑看起来像布尔逻辑但对 NULL 的处理截然不同排查数据对不上时优先怀疑涉及 NULL 的 WHERE 条件。8.2 去重和分组结果不一致时先怀疑 SELECT 列混入GROUP BY user_id和SELECT DISTINCT user_id理论上结果行数一致但如果 SELECT 里加入了不在 GROUP BY 中的列行数就可能不同。我在一次报表核对中遇到过按用户分组统计订单数业务方认为“应该只有 18 个用户”但查询结果返回了 23 行。最后发现 SELECT 里多选了product_name而 GROUP BY 后面没有它。MySQL 在非严格模式下会随意取该组的一个 product_name从而把同 user_id 拆成多行。这个教训让我养成了一个习惯写聚合查询时SELECT 里除了聚合函数外只能出现 GROUP BY 里有的列。8.3 字段的字符集和排序规则看似相等实际不相等多表 JOIN 时两表关联字段如果字符集或排序规则不一致MySQL 会隐式转换不仅导致索引失效还可能产生莫名的重复或缺失。比如 a 表 user_id 是 utf8mb4b 表 user_id 是 utf8字符集不同关联时无法直接使用 b 表索引执行计划会出现Using join buffer性能几乎打回全表扫描。排查这类问题的方法很简单看执行计划的 Extra 是否有Using join buffer然后检查两张表 JOIN 字段的 COLLATION 是否一致不一致就在表定义阶段统一或者在一侧显式用CONVERT统一字符集。这是我几次“为什么 JOIN 这么慢”的最终答案和索引无关纯粹是字符集问题。9. 最后一个实用建议把 DQL 当一门独立的技能来刻意训练很多人学数据库是从 CRUD 开始的insert、update、delete、select 各学一遍就以为会了。但 DQL 的复杂度远高于其他三类语句。写查询、优化查询、排查查询问题这三件事在业务开发里几乎每天发生它们的底层能力全部来自 DQL 的熟练度。我自己的训练方法是把业务报表当题库每天刻意练两三条先用常规写法再问自己能不能用窗口函数改写、能不能减少一次子查询、能不能让执行计划少一次 filesort。这种训练一开始可能慢但坚持半年后写 DQL 的直觉会变得非常准——一条 SQL 刚写完你就能预感到它会走什么计划、哪里可能出现临时表、返回多少行。这种预感不是玄学而是对执行顺序、索引结构、优化器偏好长期积累的映射。另外给你一个马上能用的检查清单每次写完一条 DQL 提交前按顺序过一遍SELECT 的列是否都在 GROUP BY 或聚合函数里LEFT JOIN 的右表过滤条件是否写在 ON 而不是 WHEREWHERE 里是否对索引列做了函数运算或类型转换ORDER BY 的排序列是否是索引列LIMIT 深分页是否用了延迟关联EXPLAIN 里有没有全表扫描和临时表。这几条过了查询基本不会出大事。DQL 的价值不在于语法本身而在于你用它能准确、高效地从数据里拿到你想要的答案。把这条主线想清楚你写的每一条 SELECT 都会不一样。 DQL查询优化全攻略：从执行顺序到索引利用的实战指南
现在面试但凡问数据库基本绕不开 DQL。哪怕你简历上写的是“熟悉 MySQL”对方第一道题往往就是让你写一条关联查询或者直接抛一句DQL 和 SQL 到底什么关系很多人一听就懵觉得 DQL 不就是 select 吗对也不对。DQLData Query Language是查询语言在 SQL 体系里特指以 SELECT 为核心的查询语句但它不代表只能做简单的数据抽取。排序、去重、分组、聚合、子查询、关联、窗口计算、甚至部分逻辑处理都能用 DQL 完成。我在实际带人的时候发现新人往往陷入两个极端一是觉得 select 太简单不值得系统学二是把 DQL 和 MySQL 的杂七杂八功能混在一起抓不住主线。这两种心态都不对。DQL 是数据库操作里最复杂、最有深度、也最影响性能的部分一条写得好的查询和一条写得烂的查询差距可能不是几毫秒而是几十倍甚至直接拖垮线上业务。这篇文章我就把 DQL 的完整脉络捋一遍从语句执行顺序、单表操作细节、多表 JOIN 和子查询、窗口函数到执行计划和索引利用按我平时排查问题的思路来写。即使你现在只会 select * from xxx看完应该也能写出一条合格且有性能意识的生产级查询。1. 一条 DQL 语句的真实执行链路SQL 不是从上往下跑的新手最难纠正的一个思维是SQL 是顺序执行的从 FROM 开始一行一行读。实际上SQL 是描述性语言——你告诉数据库“我要什么结果”而不是“你要怎么一步步算”。这句话我在刚入行的时候根本理解不了直到我看了一次慢查询日志才真正明白执行顺序这东西有多重要。1.1 SELECT 六大子句的逻辑执行顺序写 DQL 时我们的书写顺序是 SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT但数据库引擎实际的处理顺序完全不是这样。一条完整的 SELECT 语句逻辑层面的执行顺序如下FROM含 JOIN→ WHERE → GROUP BY → HAVING → SELECT含窗口函数 → ORDER BY → LIMIT我给很多人画过这个顺序几乎所有人第一次看都觉得反直觉。但请记住这是理解 DQL 整个体系的钥匙。为什么 WHERE 里不能用聚合函数比如WHERE COUNT(*) 100因为 WHERE 是在 GROUP BY 聚合之前执行的行级过滤条件此时聚合操作还没发生当然不能用聚合函数。HAVING 为什么能过滤聚合结果因为 HAVING 排在 GROUP BY 之后作用对象已经是分组后的结果集了。SELECT 中的列别名为什么不能被 WHERE 引用别名是在 SELECT 阶段才生成的WHERE 阶段根本还未生成别名。搞清楚这个顺序能解决一批很典型的“代码看着没问题跑起来就报错”的场景。我举个例子-- 错误写法WHERE 阶段还没有 year 字段 SELECT YEAR(create_time) AS year, COUNT(*) FROM orders WHERE YEAR(create_time) 2024;这条其实不报错因为它没在 WHERE 里用别名。真正会报错的写法是WHERE year 2024这个别名在 WHERE 阶段不存在。所以最稳妥的做法是老老实实写成YEAR(create_time) 2024或者放到 HAVING 里但 HAVING 的性能通常比 WHERE 差能前置过滤就前置。包含 JOIN 的场景顺序还要在前ON 条件在 FROM 阶段触发先确定关联后的中间结果集WHERE 再基于这个中间结果集做过滤。很多人困惑“为什么 ON t1.id t2.id AND t2.status 1 和 WHERE t2.status 1 结果一样又不一样”本质就是因为 ON 和 WHERE 的执行时机不同。左连接时ON 的约束作用在驱动表的匹配过程WHERE 约束的是最终结果集所以 LEFT JOIN 场景下把被驱动表右表的过滤条件写在 WHERE 里会导致外连接退化成内连接的效果。这一点在后面的 JOIN 章节我会专门再讲。1.2 从 MySQL 架构层面看 DQL 是怎么被“跑”出来的逻辑执行顺序是纸面上的规则落到 MySQL 引擎里还要经过一系列真正的执行过程。一条 DQL 语句从客户端发出到返回结果大致经过这些环节连接处理器校验账号权限在系统表里检查你是否有该表的 SELECT 权限。解析器Parser先做词法分析把字符串拆成 token再做语法分析生成解析树。语法错误在这一步直接爆出来比如关键字拼错、括号不匹配。预处理器进一步检查表和列是否存在、别名是否有歧义然后对语句做权限的二次校验。优化器Optimizer这个环节最关键。MySQL 会分析哪张表先驱动、选择哪个索引、JOIN 顺序怎么安排、子查询要不要改写成 JOIN然后生成执行计划。你现在看到的大部分慢查询优化本质上都是在帮优化器做更好的选择。执行器按照执行计划逐行调用存储引擎接口做数据读取和行过滤。存储引擎层也就是 InnoDB 干实事的地方——从内存 Buffer Pool 或磁盘页中读取记录返回给上层。我在排查线上问题时会先用 EXPLAIN 看执行计划对应优化器产物再根据 type 和 key 判断是走索引还是全表扫描。多个慢查询对比后会发现大部分慢查询的问题不在 CPU 或磁盘而在优化器没选择你预期的索引路径。理解执行链路的另一个好处是你能预判某条语句大概在哪个环节出错、在哪个环节耗时高而不是一上来就瞎调参。提示DQL 的终极优化思想就是两条——减少扫描的数据量、减少返回的数据量。前者靠索引后者靠过滤条件写的准确度和必要的投影列裁剪。后面所有查询技巧基本都是围这两条展开的。2. 单表 DQL 操作过滤、聚合、排序和分页里的细节单表查询看起来简单但真正把 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT 用对、用透才算入了 DQL 的门。我见过太多人在单表查询里栽跟头光是GROUP BY和HAVING的边界问题就能筛掉一半的候选人。2.1 WHERE 的几种过滤层级等值、范围、模糊、条件与 NULL 的坑WHERE 子句负责行级过滤执行时机在所有聚合和投影之前所以它是 DQL 中最能影响性能的部分。基本类型就那几类等值过滤、范围过滤BETWEEN AND和IN、模糊过滤LIKE、联合条件AND/OR以及 NULL 判断IS NULL/IS NOT NULL。写 WHERE 有几个容易忽略的细节第一NULL 永远不能和等号做匹配。WHERE col NULL不会报错但也不会返回任何行因为 SQL 的三值逻辑里 NULL 就代表未知未知和未知比不出来结果。必须用IS NULL或IS NOT NULL。-- 错误习惯 SELECT * FROM user WHERE deleted NULL; -- 正确写法 SELECT * FROM user WHERE deleted IS NULL;第二IN列表很大或很小的时候优化器的处理策略不一样。列表很小时等价于多个等值 OR列表很大时 MySQL 会基于基数估算是否要转成临时表半连接。实际开发中IN列表超过几百项就该考虑 JOIN 或临时表了不然解析和估算本身就会消耗时间。第三LIKE前缀通配符的问题。LIKE %keyword%会导致索引失效因为无法从 B 树有序性中做前缀匹配但LIKE keyword%可以利用索引。业务场景如果确实需要前后模糊匹配我一般建议引入全文索引或搜索引擎而不是硬写在 MySQL 的 LIKE 里。第四OR 的索引利用。WHERE a 1 OR b 2如果 a、b 各有单列索引MySQL 可能会走索引合并Index Merge但合并消耗不小条件多时不如改写为UNION或者建一个联合索引来得稳。2.2 GROUP BY 的分组语义与聚合函数的真实行为GROUP BY 将结果集按一个或多个列拆成若干分组分组后再把聚合函数作用于每个分组。这个“分组后再聚合”的语义是理解聚合查询的核心。写聚合查询时最大的误区有两个。误区一SELECT 的列没出现在 GROUP BY 中。比如SELECT user_id, product_id, MAX(amount) FROM orders GROUP BY user_id;如果 MySQL 开启ONLY_FULL_GROUP_BY默认是开的这条会直接报错product_id 既不在聚合函数里也不在 GROUP BY 中。这其实是合理的因为一个 user_id 分组下可能有多个 product_id数据库不知道该选哪一个。关闭这个模式虽然也能跑但取到的 product_id 是随机行为线上绝对不能这么搞。误区二把 WHERE 能过滤的事情丢给 HAVING。比如“查每个用户 2024 年的订单总额只要总额超过 1000 的”可以写SELECT user_id, SUM(amount) AS total FROM orders WHERE order_year 2024 GROUP BY user_id HAVING total 1000;WHERE 在分组前过滤掉不满足的行减少了进入分组的数据量HAVING 只处理分组后的结果。如果把order_year 2024也放进 HAVING语义没错但性能差得多——所有年份的数据都得先分组再过滤。再补充一个聚合函数的使用心得。COUNT(*)、COUNT(1)、COUNT(col)三个看起来差不多实际行为不同COUNT(*)统计表中所有行数COUNT(1)也统计所有行数唯一的差异只是写法COUNT(col)只统计该列非 NULL 的行数。所以想统计某列的实际有效值数量就用COUNT(col)想查总行数用COUNT(*)就好。还有SUM(col)遇到全 NULL 分组时返回 NULL 而不是 0要配合IFNULL(SUM(col), 0)使用这点在报表开发里相当关键。2.3 ORDER BY、LIMIT 和深分页问题的解决思路ORDER BY 排序本身不难但没有用好索引时MySQL 会生成一张临时文件做 filesort也就是把结果集全部读出来在磁盘上排一遍一旦结果集大这里的开销会相当吓人。为什么联合索引(a, b)能直接满足ORDER BY a, b因为索引本身有序引擎按索引顺序读出来就是排好序的结果。反过来ORDER BY b或者ORDER BY a DESC, b ASC通常无法直接利用索引顺序混合排序方向和跳过前导列的排序都会让优化器放弃索引有序性。LIMIT 深分页是生产环境最典型的性能陷阱。看这条语句SELECT * FROM orders ORDER BY id LIMIT 100000, 20;它的执行逻辑是找到前 100020 行丢掉前 100000 行只返回 20 行。前 100000 行的扫描和排序全部是白白浪费的。这个场景的常见解法有两种而且都是我在实际项目中验证过的第一种延迟关联先缩小范围再回表取整行。子查询先只查主键 id跳过大量无用的回表和整行数据扫描SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) t ON o.id t.id;第二种基于索引位置做条件翻页也就是记住上一页最后一条记录的位置。通常用WHERE id 上一页最大id ORDER BY id LIMIT 20。这种写法在数据量大时性能稳定因为每次都只扫描 20 行左右。缺点是翻页跳转不方便适合“下滑加载更多”的 App 场景不适合传统分页组件。3. JOIN 和子查询多表查询的底层逻辑与常见坑点真正的 DQL 复杂度集中体现在多表关联和子查询里。左连接变内连接、ON与WHERE的边界、EXISTS与IN的选择这些都是面试必问、工作必用的知识点也是排查数据结果异常时最容易定位的源头。3.1 三种 JOIN 的本质与前前后后的驱动关系JOIN 关联的本质是笛卡尔积基础上的匹配过滤先按某种方式把一个笛卡尔积的空间确定下来再用 ON 条件把匹配的行挑出来。INNER JOIN 只保留两边都匹配的行LEFT JOIN 保留左表全部行右表没匹配上就补 NULLRIGHT JOIN 反过来。生产上我基本不用 RIGHT JOIN把右表当驱动表改写为 LEFT JOIN 逻辑会更清晰也更符合业务阅读习惯。JOIN 执行时真正棘手的是驱动顺序。MySQL 的小表驱动大表原则就是告诉优化器先读取驱动表的范围再去被驱动表上逐行探测匹配关系被驱动表如果能走索引每次探测就是索引查找总成本就是“驱动表行数 × 被驱动表索引查找成本”。所以让结果集小的表当驱动表通常更高效。我之前接手过一个线上慢查询12 张小表关联关键是驱动顺序选错了优化器把一张 5000 行的小表放到被驱动位置另一张 500 万行的大表当驱动表每次探测都要扫大表的普通索引单次查询跑出来 8 秒多。后来通过调整 JOIN 顺序加了一个冗余的关联条件让优化器重新评估直接把耗时拉到了 40 毫秒以下。这个案例说明执行计划不是黑盒它是可以被理解和干预的。3.2 ON 与 WHERE 的时机差异左连接“退化”的典型场景LEFT JOIN 中最容易踩的坑是把右表的过滤条件写进 WHERE。考虑这样一个业务场景查所有用户及其 2024 年的订单数没有订单的用户也要保留。-- 期望结果保留没有订单的用户 SELECT u.id, u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.year 2024 GROUP BY u.id, u.name;这条语句的结果会让没有订单的用户直接消失。因为 LEFT JOIN 先把所有用户和所有订单做完外连接右表无匹配时 order 的列全是 NULL然后 WHERE 阶段执行o.year 2024NULL 行被过滤掉外连接变成了内连接的效果。正确做法是在 ON 阶段就把年度限制带进去因为 ON 的职责是定义连接时右侧表参与匹配的行此时的过滤效果不会影响左侧表保留行SELECT u.id, u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.year 2024 GROUP BY u.id, u.name;这个坑在面试中特别经典因为候选人写出来的 SQL 往往“看着能跑”但查出来的统计数字不对而且不易察觉。我自己排查过多次类似的数仓报表差异最后都定位到同一个逻辑右表条件错放在 WHERE 里。还有一个类似的坑是“多表 JOIN 时过滤条件放哪”。INNER JOIN场景下 ON 和 WHERE 的结果一致因为它们都是做行的匹配约束WHERE 后置但最终交集不变LEFT JOIN场景下则完全不同。所以判断标准很简单如果外连接需要保留一侧的完整数据所有对另一侧表的过滤条件都应写在 ON 子句中。3.3 IN、EXISTS 和 JOIN子查询写法如何影响真实性能子查询和关联查询很多时候能写成同一个业务结果但底层执行策略不同。IN在 MySQL 里可能被优化成半连接semi-join也就是“只要存在匹配就返回”EXISTS则是逐行去探测子查询是否存在匹配行。大表驱动小表时用IN内层表数据量小且外层大、小表驱动大表时用EXISTS内层大表走索引逐行探测是常见经验法则但 MySQL 优化器近年来也会自动改写所以更稳妥的做法是直接看 EXPLAIN。此外标量子查询要谨慎。SELECT 子句里每行都执行一次子查询如果外层返回 10 万行内层子查询就会执行 10 万次。任何一个内层查询稍微慢一点整条语句就会被放大 10 万倍。虽然 MySQL 会尝试对相关子查询做缓存但效果不稳定。能改写成 JOIN 的优先 JOIN。4. 窗口函数DQL 中最该掌握的高级计算能力窗口函数Window Function是 DQL 中经常被低估的部分。很多开发者处理“排名、同期对比、累加、分组内 TopN”这类需求时还在用子查询 临时变量 自连接拼来拼去代码又长又容易错。窗口函数直接把“基于当前行所在分组做计算”这件事变成了独立的语法维度。4.1 OVER 子句的完整语法PARTITION BY、ORDER BY 和窗口框窗口函数的标准结构是三件套函数本身 OVER ()内定义的分区PARTITION BY 组内排序ORDER BY 可选的窗口框ROWS BETWEEN ... AND ...。和普通GROUP BY的一个核心区别是窗口函数不会导致行数收缩。每一行输出都保留只是在这一行旁边额外计算出一个窗口值。拿一个典型的例子来说查每个用户的订单金额排名以及按时间累计的消费金额。SELECT user_id, order_date, amount, RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS amount_rank, SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_amount FROM orders;RANK()返回组内金额排名SUM(amount) OVER (...)用ROWS BETWEEN定义了滑动的窗口范围——从组内第一行到当前行这样就得到了“当前时刻的累计消费额”。如果你不加ROWS BETWEEN默认的窗口框是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW也就是从组内起点到当前行所有和当前行排序值相等的行。这个细微差异在 ORDER BY 有重复值时会复现出不同的数值是窗口函数的经典暗坑。4.2 常见窗口函数的适用场景与挑选技巧我把实际工作中最常用的一批窗口函数整理成一张速查表方便用到的时候直接对着挑函数类别代表函数适用场景特别注意排名ROW_NUMBER、RANK、DENSE_RANK排行榜、分组 TopNRANK 有并列跳号DENSE_RANK 不跳号聚合SUM、AVG、COUNT、MAX、MIN累计值、移动平均结合 ROWS BETWEEN 定义窗口偏移LAG、LEAD环比、同比、差值对比LAG(col, n) 取前 n 行首尾FIRST_VALUE、LAST_VALUE组内最大最小值对应记录LAST_VALUE 要配合 ROWS 定义避免只取到当前行排名类的三个函数差异是高频考点。ROW_NUMBER()无论有没有并列都依次给 1、2、3RANK()遇到并列会跳号比如两个第一名后直接是第三名DENSE_RANK()并列不跳号两个第一名后还是第二名。业务要做“并列第一但下一名跳过”的效果用 RANK要做稳定行号用 ROW_NUMBER要做紧凑排名用 DENSE_RANK。我用窗口函数解决过一个比较棘手的问题取每个用户最近三笔订单的品类分布。如果用普通 GROUP BY行数会收缩拿不到多行明细如果自连接条件复杂且爆炸。改成如下写法就干净很多SELECT user_id, order_id, category FROM ( SELECT user_id, order_id, category, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn FROM orders ) t WHERE rn 3;内层先给每个用户的行按时间倒序编号外层过滤前 3 行。这是取分组 TopN 的标准套路也是窗口函数最典型的实战应用之一。它利用子查询把窗口计算结果先算出来再在外层做条件过滤。窗口函数不能直接出现在 WHERE 里这是很多人一开始常犯的语法错误——WHERE rn 3如果写在同一个 SELECT 里会直接报“Unknown column”。4.3 窗口函数与 GROUP BY 的组合使用窗口函数也可以作用在已经有 GROUP BY 的结果集之上也就是说分组聚合之后的结果当作基准再套一层窗口函数做二次分析。这种场景在报表里非常常用。比如统计每个用户订单总额后再算所有用户的平均订单总额SELECT user_id, SUM(amount) AS total_amount, AVG(SUM(amount)) OVER () AS avg_amount, SUM(amount) / AVG(SUM(amount)) OVER () AS ratio FROM orders GROUP BY user_id;这里AVG(SUM(amount)) OVER ()先把 GROUP BY 得到的每组总额再取全体平均SUM(amount)是普通聚合OVER ()不写分区就是对整体开窗。很多人在这一步卡住因为窗口函数里其实还可以嵌套聚合函数。理解了这个写法你写报表 SQL 的层次感会提升一个档次。5. 执行计划与索引利用把慢 DQL 调成快 DQLDQL 写得对不代表写得好。真正衡量一条查询是否合格要拿 EXPLAIN 出来看执行计划。执行计划里藏着索引利用情况、扫描行数、排序方式、连接方式这些才是优化查询的决策依据。把 DQL 从“能跑”变成“跑得快”是区分业务代码和工程能力的一道分水岭。5.1 EXPLAIN 关键字段一眼定位查询性能瓶颈执行EXPLAIN SELECT ...得到的结果表我重点看四个字段。type访问类型从好到差大致是systemconsteq_refrefrangeindexALL。工作中只要看到ALL全表扫描在大表上出现基本就是性能隐患。range是范围扫描可用ref是非唯一索引等值匹配正常eq_ref是唯一索引等值匹配比较理想。key实际用的索引如果为 NULL说明这一行没用索引。rows预估扫描行数这个数字不是精确值但数量级能说明问题。同一业务语句rows 从 100 万降到 1000优化就是成功的。Extra出现Using filesort表示排序没走索引额外做了一次文件排序出现Using temporary表示用了临时表常见于 GROUP BY 或 DISTINCT 没用好索引出现Using index是好事表示覆盖索引连回表都省了。EXPLAIN 只能看到预估执行计划如果怀疑实际执行差异可以用EXPLAIN ANALYZEMySQL 8.0拿到真实的执行时间、扫描行数和各阶段耗时。之前排查一条线上 5 秒的统计查询EXPLAIN 显示预估只扫 2 万行但 EXPLAIN ANALYZE 实测扫了 2000 万行最后发现是统计信息过期导致优化器选错索引ANALYZE TABLE之后就正常了。这类问题光看 EXPLAIN 是会被骗的这正是我用实证工具的原因。5.2 索引失效的几种典型写法到底哪里写歪了联合索引和单列索引的使用规则说简单也简单说细也细。我被问得最多的问题是“为什么我建了索引但 EXPLAIN 就是不走”。几种典型的索引失效场景我直接总结成清单对索引列做了函数运算WHERE YEAR(create_time) 2024不会走 create_time 索引改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01才行。隐式类型转换字符串列和数值比较时MySQL 会把列转成数值索引失效。最常见的就是WHERE phone 13800001234而 phone 列是 varchar。联合索引不满足最左前缀索引(a, b, c)可以支持a、a,b、a,b,c三种条件组合但WHERE b 1 AND c 2这种跳过 a 的查询用不了这个联合索引。反之如果跳过的是中间列如WHERE a 1 AND c 2只能用到 a 这一列的前缀部分。前导通配符 LIKELIKE %keyword%无法走索引LIKE keyword%可以。这不是玄学是 B 树前缀有序性的天然限制。OR 两边字段条件不统一一边有索引一边没有优化器可能放弃索引合并直接全表扫。用UNION拆开或重建联合索引都能解决。对索引列做计算WHERE amount 100 200不会走索引改为WHERE amount 100就正常了。规则的底层逻辑是B 树的叶子节点存的是原始列值函数或运算后的值无法直接做二叉查找。5.3 覆盖索引、回表和话说“SELECT *”的代价InnoDB 的二级索引叶子节点存的是索引列值 主键值。当你用二级索引查到目标行时如果查询需要的其他列不在索引里引擎就要拿着主键再去聚簇索引里取一次完整行这就是“回表”。回表次数多了查询自然慢。避免回表的最直接方法是让查询列全部包含在索引中这就是覆盖索引Using index。比如表上有联合索引(user_id, status)你执行SELECT user_id, status FROM t WHERE user_id 100这条查询只从二级索引就拿齐了所有需要的列完全不用回表。这也是我平时不太赞成在业务代码里无脑SELECT *的原因。SELECT *会强制读取所有列索引覆盖条件几乎不可能全部满足每行都要回表取完整数据同时把不需要的大字段比如 TEXT也拉出来白白增加网络传输和内存消耗。生产环境的规范通常是SELECT 只列出业务真正需要的列控制返回的字段集合配合覆盖索引让查询更轻。如果你的表列很多但每次只查其中两三个字段一个精心设计的联合索引能覆盖绝大多数高频查询性能提升非常明显。6. 一次真实慢查询的完整排查链路从现象到根因到修复排查经验比工具更能决定一条查询能不能被救回来。这里我完整复盘一个实际的慢查询案例把从发现问题、看执行计划、推断原因、修改方案、验证结果的整个过程都过一遍。这个案例在 DQL 优化上非常有代表性几乎覆盖了前面所有章节的要点。6.1 现象订单统计报表突然从 300ms 涨到 8s当时一个订单报表接口的查询语句是SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_cnt, SUM(o.amount) AS total_amount FROM users u LEFT JOIN orders o ON u.user_id o.user_id AND o.status 1 WHERE u.reg_time 2024-01-01 GROUP BY u.user_id, u.user_name ORDER BY total_amount DESC LIMIT 100;线上监控显示这条 SQL 的 p95 耗时从 300ms 飙到 8 秒左右。单独把它拿出来跑确实要 8 秒多。这是我印象最深的一条慢查询因为它的每个部分看起来都合情合理LEFT JOIN 保留所有用户、统计有效订单、过滤注册时间、按金额降序取前 100没有任何“低级”错误。6.2 EXPLAIN 定位三个关键线索执行EXPLAIN后结果表里几个字段让我瞬间锁定了问题访问类型users 是 ALL也就是说 500 万注册用户做全表扫描。rows 估算orders 预计约 680 万行参与关联。Extra 字段Using temporary; Using filesort分组和排序都没走索引全部落在临时表和文件排序上。核心问题是驱动表选择失误。users 表全表扫描后再去关联 orders等于先拖出 500 万行用户再逐一去订单表匹配。这么大的驱动行数哪怕每次关联走索引总成本也是 500 万次索引探测而且 ORDER BY total_amount DESC 的排序发生在分组聚合之后结果集和临时表都很庞大filesort 直接把内存拖垮到磁盘排序。6.3 修复方案缩小驱动行数、改写为内连、验证结果第一步先把 WHERE 阶段就缩小 users 表的扫描范围。reg_time 2024-01-01说明只需要查 2024 年之后注册的用户但这条语句已经带了条件为什么还是 ALL因为 users 表上只有一个主键索引和以其他列建的索引reg_time 上没有可用的索引。所以第一个动作就是给users(reg_time)建普通索引让 WHERE 能走 range驱动行数直接砍到 50 万左右。第二步优化器选择 users 作为嵌套循环的驱动表但真正要保留的其实是“所有 2024 年后注册用户 有订单记录的用户”。如果订单数据量更大驱动行数更少那么完全可以反过来让 orders 先按 status1 过滤后当驱动表再关联用户。考虑到业务侧能接受去掉完全没有订单的用户报表本来就是看有效订单数据我把 LEFT JOIN 改写为 INNER JOIN这样优化器在评估时会优先选择过滤后更小的表当驱动表执行计划立刻好了很多。第三步处理临时表和文件排序。ORDER BY total_amount DESC是在聚合后的别名上排序无法在索引阶段直接完成因此必须有结果集排序动作。这里我选择把排序下推到子查询里让子查询先算出每个用户的金额再排序外层回表取用户名SELECT t.user_id, u.user_name, t.order_cnt, t.total_amount FROM ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE status 1 GROUP BY user_id ORDER BY total_amount DESC LIMIT 100 ) t LEFT JOIN users u ON t.user_id u.user_id;这个写法里orders 表只按 status1 过滤后做分组聚合先把 100 个用户 id 和聚合指标算出来再回表补 user_name。整个中间结果集从“几百万用户 几百万订单”变成了“100 行”临时表和排序压力下降了几个数量级。最后用 EXPLAIN 验证新的执行计划orders 走了 status 的索引rows 估算降到 40 万左右Extra 里不再有Using temporary只有一次基于「分组后 100 行」的文件排序或者直接在内存排序完成。线上灰度跑了一周p95 稳定在 120ms 以内。6.4 这个案例说明的四个通用优化原则复盘这次排查我把经验压缩成几条可复用的原则供你在自己的慢查询优化里参考先看访问类型全表扫描一定要减少。不管业务逻辑多明确大表 ALL 就意味着扫描成本失控第一步永远是把索引补齐。驱动顺序直接影响最终扫描量。执行计划里第一个表就是驱动表判断它会不会把大量行送到后续步骤。小结果集驱动大表所有 JOIN 和子查询都遵循这条。中间结果集越大后续的临时表、排序、回表越痛。看到Using temporary; Using filesort要敏感它们通常意味着聚合或排序需要一个完整的大结果集。业务表 JOIN 时能 INNER JOIN 就不要 LEFT JOIN。两者语义差异决定了优化器的选择余地很多报表查询从外层看都能接受内连接语义只是写的时候出于习惯用了 LEFT JOIN白白失去了优化空间。注意所有优化改完一定要回到业务验证结果。这种查询通常对应报表或接口改完不仅要看耗时还要和旧结果比对行数和关键统计值。DQL 优化的第一目标永远是结果正确第二目标才是性能达标。7. DQL 的技巧延展去重、条件聚合和同环比计算的实践经验除了核心语法DQL 在实际业务里经常要解决一些“看起来得写程序处理”的需求。其实这些需求都能在 SQL 层面完成三个最常见的场景是去重统计、条件聚合和同环比计算。把这几个技巧掌握住很多统计代码可以直接省掉。7.1 DISTINCT 的去重陷阱与替代方案DISTINCT 是对整个 SELECT 返回的列组合去重不是单独对某一列去重。SELECT DISTINCT user_id, status是去除 user_id 和 status 都相同的行如果你只想要“不同的 user_id”这个写法得到的结果是 user 和 status 的所有组合不是目标。单纯对单列去重我一般用 GROUP BY因为它更符合“按列聚合”的语义也更容易配合其他聚合字段。DISTINCT 在数据量大时往往在内存里维护一个临时 Set 结构去重占用不小。如果只是统计数量比如COUNT(DISTINCT user_id)能跑但尽量别在高基数列上频繁使用它有性能瓶颈。大数据量表里做 UV 统计我通常直接用COUNT(DISTINCT user_id)做小规模报表没问题但如果表上亿就得考虑用近似算法或者离线数仓了。7.2 条件聚合用 SUM CASE WHEN 在一个查询里完成多个统计指标业务报表里最常见的一个需求是“同时统计多种状态下的数量”。例如统计每个用户的有效订单数、取消订单数、退款订单数。常规做法是三个查询分别统计再合并但其实一条 DQL 就能完成SELECT user_id, SUM(CASE WHEN status 1 THEN 1 ELSE 0 END) AS valid_cnt, SUM(CASE WHEN status 2 THEN 1 ELSE 0 END) AS canceled_cnt, SUM(CASE WHEN status 3 THEN 1 ELSE 0 END) AS refunded_cnt FROM orders GROUP BY user_id;SUM(CASE WHEN ... THEN 1 ELSE 0 END)的原理是逐行判断条件满足则贡献 1不满足贡献 0然后把这个 0/1 序列加总得到的就是“满足条件的行数”。这种方法把一个表的多维度统计合并成一次扫描只回一次表性能远好于多次查询再在业务层拼接。判断某个条件时如果关心“是否至少存在一行”也可以用MAX(CASE WHEN ... THEN 1 ELSE 0 END)来实现布尔聚合。7.3 LAG/LEAD 实现同环比不用自连接就能算差额做报表时经常要算“今日订单量比昨日增长多少”。传统写法是自连接条件写a.date b.date - 1麻烦还容易错。用窗口函数 LAG/LEAD 就简单直观得多SELECT stat_date, order_cnt, LAG(order_cnt, 1) OVER (ORDER BY stat_date) AS prev_cnt, order_cnt - LAG(order_cnt, 1) OVER (ORDER BY stat_date) AS diff_cnt, ROUND( (order_cnt - LAG(order_cnt, 1) OVER (ORDER BY stat_date)) / LAG(order_cnt, 1) OVER (ORDER BY stat_date) * 100, 2 ) AS growth_rate FROM daily_order_stats ORDER BY stat_date;LAG(order_cnt, 1) OVER (ORDER BY stat_date)表示取当前行按 stat_date 排序后前一行即前一天的订单量。这样计算出的 diff_cnt 就是环比差值growth_rate 就是环比增长率。同比也一样把偏移量从 1 改成 365按日粒度即可。这种写法的优势是只扫一次表而且把“行与行之间的比较”这种原本需要用自连接或者程序来回处理的事直接交给 DQL 完成。8. 一个怎么强调都不过分的问题查询结果不对先从 DQL 的语义入手调优看性能但排查数据正确性问题得回到 DQL 语义本身。我最常遇到的数据异常不是索引问题而是语义理解问题。8.1 三值逻辑NULL 如何静默地改变结果集SQL 的 WHERE 条件处理 NULL 时用的是三值逻辑——TRUE、FALSE、UNKNOWN。一个简单条件如果遇到 NULL通常结果为 UNKNOWN它不会进入结果集。所以WHERE amount 100会把 amount 为 NULL 的行过滤掉但很多业务上你其实是想把“未填金额”的行也查出来。这种场景要显式加OR amount IS NULL。另外一个更隐蔽的坑是多条件组合和 NULL 的关系。比如WHERE status paid OR refund_amount 100如果某行 status 为 NULLrefund_amount 为 50按直觉应该被第二个条件选出但在三值逻辑里第一个条件结果为 UNKNOWNOR 的最终结果依然是 TRUE 吗——取决于第二个条件能不能独立判定为 TRUE。这里第二个条件是 TRUE所以整行还是会被选中。但如果两个条件都涉及 NULL结果就变成 UNKNOWN整行被排除。三值逻辑看起来像布尔逻辑但对 NULL 的处理截然不同排查数据对不上时优先怀疑涉及 NULL 的 WHERE 条件。8.2 去重和分组结果不一致时先怀疑 SELECT 列混入GROUP BY user_id和SELECT DISTINCT user_id理论上结果行数一致但如果 SELECT 里加入了不在 GROUP BY 中的列行数就可能不同。我在一次报表核对中遇到过按用户分组统计订单数业务方认为“应该只有 18 个用户”但查询结果返回了 23 行。最后发现 SELECT 里多选了product_name而 GROUP BY 后面没有它。MySQL 在非严格模式下会随意取该组的一个 product_name从而把同 user_id 拆成多行。这个教训让我养成了一个习惯写聚合查询时SELECT 里除了聚合函数外只能出现 GROUP BY 里有的列。8.3 字段的字符集和排序规则看似相等实际不相等多表 JOIN 时两表关联字段如果字符集或排序规则不一致MySQL 会隐式转换不仅导致索引失效还可能产生莫名的重复或缺失。比如 a 表 user_id 是 utf8mb4b 表 user_id 是 utf8字符集不同关联时无法直接使用 b 表索引执行计划会出现Using join buffer性能几乎打回全表扫描。排查这类问题的方法很简单看执行计划的 Extra 是否有Using join buffer然后检查两张表 JOIN 字段的 COLLATION 是否一致不一致就在表定义阶段统一或者在一侧显式用CONVERT统一字符集。这是我几次“为什么 JOIN 这么慢”的最终答案和索引无关纯粹是字符集问题。9. 最后一个实用建议把 DQL 当一门独立的技能来刻意训练很多人学数据库是从 CRUD 开始的insert、update、delete、select 各学一遍就以为会了。但 DQL 的复杂度远高于其他三类语句。写查询、优化查询、排查查询问题这三件事在业务开发里几乎每天发生它们的底层能力全部来自 DQL 的熟练度。我自己的训练方法是把业务报表当题库每天刻意练两三条先用常规写法再问自己能不能用窗口函数改写、能不能减少一次子查询、能不能让执行计划少一次 filesort。这种训练一开始可能慢但坚持半年后写 DQL 的直觉会变得非常准——一条 SQL 刚写完你就能预感到它会走什么计划、哪里可能出现临时表、返回多少行。这种预感不是玄学而是对执行顺序、索引结构、优化器偏好长期积累的映射。另外给你一个马上能用的检查清单每次写完一条 DQL 提交前按顺序过一遍SELECT 的列是否都在 GROUP BY 或聚合函数里LEFT JOIN 的右表过滤条件是否写在 ON 而不是 WHEREWHERE 里是否对索引列做了函数运算或类型转换ORDER BY 的排序列是否是索引列LIMIT 深分页是否用了延迟关联EXPLAIN 里有没有全表扫描和临时表。这几条过了查询基本不会出大事。DQL 的价值不在于语法本身而在于你用它能准确、高效地从数据里拿到你想要的答案。把这条主线想清楚你写的每一条 SELECT 都会不一样。