1. 从一段真实业务需求说起子查询到底解决什么问题前几天排查一个线上慢查询业务方丢过来一段SQL说页面加载要七八秒用户快把客服电话打爆了。我先看了一眼表结构和数据量又跑了一遍EXPLAIN问题很快就定位到了——三层嵌套子查询最内层扫了全表中间层又做了文件排序外层再逐个匹配。这大概是MySQL子查询最典型也最容易被写坏的场景之一。先给不太熟的朋友补个基础定义子查询就是嵌套在另一个SQL父查询内部的SELECT语句它可以出现在WHERE、FROM、HAVING、SELECT列表、甚至UPDATE/DELETE语句中。你可能会问明明用JOIN也能实现大部分需求为什么还要用子查询因为有些逻辑用子查询表达更直观比如“找出比部门平均工资高的员工”“查询最近一单金额超过500的用户”——这类需求如果硬改成JOIN要么需要先聚合生成临时表要么得用窗口函数绕一大圈代码可读性反而更差。子查询这个语法从MySQL 4.1就开始支持了经历过好几个版本的优化器改写策略迭代。5.6之前性能经常被吐槽5.7开始引入了半连接优化和物化策略8.0又对派生表做了Lateral Derived优化、窗口函数配合子查询也更好用了。所以这篇文章不是简单罗列语法而是结合我实际排查和优化过的案例把子查询的分类、执行原理、性能陷阱、改写技巧一次讲透。2. 子查询的七种形态与实战拆解2.1 标量子查询最灵活但最容易忽视NULL标量子查询返回的是一行一列的单个值可以用在几乎所有需要单个值的地方。举几个最常见的场景-- 查询每个员工的工资与部门平均工资的差额 SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id) AS diff FROM employee e;这段SQL中SELECT AVG(salary)返回的是一个数值这就是标量子查询。实际开发中标量子查询最常见的问题不是性能而是NULL参与运算导致结果变成NULL。如果某个部门还没有员工内层AVG返回NULL外层salary - NULL的结果就是NULL。很多报表因此出现大面积的空值排查半天才发现是子查询返回了NULL。我的习惯是凡是参与数值运算的标量子查询外层一定要加IFNULL或者用COALESCE兜底SELECT emp_name, salary, salary - COALESCE((SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id), 0) AS diff FROM employee e;标量子查询还有个隐蔽的性能问题如果它在SELECT列表里并且是相关子查询引用了外层表的字段MySQL会对每一行外层记录都执行一次内层查询。也就是说外层有10万行内层就执行10万次。这在数据量小的时候没感觉一旦上了规模就是灾难性的后面我会专门讲相关子查询的优化方案。2.2 列子查询与行子查询语法糖背后的逻辑列子查询返回一列多行通常配合IN、ANY、ALL使用-- 找出所有在“销售部”待过的员工 SELECT emp_name FROM employee WHERE emp_id IN ( SELECT emp_id FROM dept_history WHERE dept_name 销售部 );行子查询返回一行多列可以同时比较多个字段-- 找出与张三在同一个部门且薪资相同的员工 SELECT emp_name FROM employee WHERE (dept_id, salary) ( SELECT dept_id, salary FROM employee WHERE emp_name 张三 );这里有个需要留意的点MySQL对行子查询的等值比较支持得不错但如果比较的是多个字段且其中某个字段允许NULL行为可能和直觉不一致。比如(dept_id, salary) (NULL, 5000)这种比较在SQL的三值逻辑下永远不是TRUE查询结果会静默丢失。我建议能用列子查询加IN解决的就不要用行子查询组合比较因为可读性更好优化器处理IN的改写策略也成熟很多。2.3 表子查询与派生表FROM子句里的临时视图表子查询出现在FROM子句中它的结果集被当作一张派生表使用必须起一个别名SELECT d.dept_name, tmp.avg_salary FROM department d JOIN ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) tmp ON d.dept_id tmp.dept_id;这里tmp就是派生表MySQL8.0之前派生表默认是物化的也就是说MySQL会先把内层查询的结果存在一张内部临时表里再做外层JOIN。8.0.14之后优化器支持了Lateral Derived Optimization允许派生表引用前面表的数据配合LATERAL关键字使用SELECT d.dept_name, tmp.emp_name FROM department d LEFT JOIN LATERAL ( SELECT emp_name FROM employee WHERE dept_id d.dept_id ORDER BY salary DESC LIMIT 1 ) tmp ON TRUE;这个写法可以很方便地实现“取每个部门工资最高的员工”以前需要用窗口函数或者反连接技巧现在一条LATERAL派生表就搞定了。我实测过在分层汇总类需求中LATERAL派生表比传统相关子查询的性能好很多因为它可以被优化器识别并采用更合理的连接顺序。2.4 EXISTS与IN谓词子查询的抉择EXISTS和IN是日常开发中用的最多的两种谓词子查询但很多人并不清楚它们在MySQL内部的不同处理路径。先说结论半连接场景下MySQL 5.7 对IN和EXISTS的改写策略已经非常接近并没有绝对的好坏之分关键取决于数据分布和索引情况。简单记忆外层表小、内层表大且内层查询能命中索引时IN通常表现更好外层表大、内层表小且需要快速短路判断时EXISTS配合索引通常更稳NOT IN一定要小心NULL值一旦子查询结果中有NULLNOT IN的判定会变成“不是UNKNOWN”导致整个查询返回空集。相比之下NOT EXISTS不会踩这个坑。举个例子公司有个接口要查询“从未下单的用户”新手往往写SELECT user_id, user_name FROM users WHERE user_id NOT IN ( SELECT user_id FROM orders );只要orders表里有任何一条user_id为NULL的记录这条查询就永远返回空。这不是MySQL的bug而是SQL标准中NULL的逻辑语义。正确写法应该用NOT EXISTSSELECT u.user_id, u.user_name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );我处理过好几次生产事故都是NOT IN遇到NULL导致报表数据清零排查起来还很绕。所以我的铁律是遇到NOT IN先问一句子查询列允许NULL吗允许就立刻改成NOT EXISTS。3. 子查询的执行计划与性能优化深挖3.1 看懂EXPLAIN中的子查询线索优化子查询的第一件事是学会看执行计划。MySQL的EXPLAIN输出中和子查询直接相关的字段主要在select_type列常见取值有select_type 取值含义常见场景PRIMARY最外层查询所有SQL都有SUBQUERY非相关子查询会被物化WHERE中独立的IN子查询DEPENDENT SUBQUERY相关子查询每行执行一次EXISTS、相关标量子查询MATERIALIZED子查询被物化为临时表IN子查询被优化为物化DERIVEDFROM子句中的派生表派生表查询UNIONUNION语句中的子查询联合查询看到DEPENDENT SUBQUERY就要警觉它意味着内层查询会依赖外层行数据逐行执行。DEPENDENT并不是优化器给出的“相关性描述”而是执行方式的声明对应的是每行重复执行的嵌套循环策略。另一种值得留意的是MATERIALIZED它说明子查询结果被提前物化成了一张内部临时表。物化本身不是坏事它避免了重复执行但如果物化出来的临时表很大且没有合适的索引后续Join时可能出现大表驱动小表的尴尬这个时候需要检查临时表的访问路径。3.2 半连接与物化MySQL如何悄悄改写你的SQLMySQL优化器中有一个非常重要的概念叫半连接Semi-join专门处理IN、EXISTS、ANY等形式的子查询。半连接的含义是不关心子查询匹配了多少行只关心“是否存在至少一行匹配”。优化器会把这类子查询改写为半连接执行计划可选的策略主要包括FirstMatch对外层每一行只要在内层找到第一条匹配就停止扫描。这是最朴素也最常用的策略等价于EXISTS的短路逻辑。DuplicateWeedout把内层匹配结果去重后再做普通连接。适用于子查询内部本身会产生重复行的场景。LooseScan利用内层表的索引对每个分组只取第一条匹配值避免重复扫描。Materialization先把子查询结果物化成临时表在临时表上建索引再做连接。这些策略不是DBA手动指定的是优化器基于统计信息自动选择的。但在某些情况下优化器会失明。比如内外层表的数据分布严重倾斜、统计信息过期、或者WHERE条件里写了函数导致索引失效优化器就可能选错策略。这时候可以手动改写成JOIN形式来绕过错误执行计划也可以调整optimizer_switch中的半连接开关。我在故障排查时见过最典型的案例一个IN子查询被优化器选成了FirstMatch但内层表过滤后选择性太差导致外层每一行都要扫描大量内层记录。把optimizer_switch里的firstmatch临时关闭强制走物化查询时间从3秒降到0.1秒。3.3 子查询改JOIN的黄金法则很多优化文章提倡“子查询一律改JOIN”这个说法过于绝对。JOIN改写后由于普通JOIN会产生重复行往往还要额外加DISTINCT去重反而可能引入新的性能问题。我的黄金法则是能保持语义不变的前提下才改JOIN。IN子查询改JOIN一般要加DISTINCTEXISTS改成JOIN则不需要去重但要注意多表连接会放大行数。优先检查索引而不是优先改写法。子查询慢很多情况下不是子查询本身的问题而是缺少合适的索引。相关子查询优先考虑改写为派生表或EXISTS而不是盲目改JOIN因为相关子查询意味着N1模式改成JOIN可能直接用哈希连接或索引嵌套循环解决。聚合类子查询改成JOINGROUP BY往往能利用索引和优化器的排序优化。用一句话总结子查询和JOIN只是同一逻辑的两种不同表达最终执行性能取决于优化器能不能把SQL翻译成高效的执行计划。我们的目标是给优化器更多选择而不是替代它做决策。4. 实战案例从慢查询到秒回的完整优化4.1 场景构建与初始SQL为了讲清楚我构造一个贴近真实业务的模拟场景。某电商平台有两张表orders订单表约200万行order_id、user_id、amount、status、order_timeusers用户表约50万行user_id、user_name、user_level业务需求是找出所有“黄金会员”中最近30天内有下单但订单金额低于100元的用户。新手第一版SQL通常写成相关子查询SELECT u.user_id, u.user_name FROM users u WHERE u.user_level gold AND EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY );单独看这条SQL的执行计划还行但如果users表中gold会员有20万EXISTS子查询每行执行一次orders表上又没有(user_id, amount, order_time)的联合索引后果就是20万次全表扫描耗时轻松超过10秒。4.2 逐步拆解与改写第一步我不急着改SQL先检查索引。orders表上原来只有主键和user_id单列索引user_id过滤后还要回表判断amount和order_time这是一个典型的索引覆盖不足问题。我先加上联合索引ALTER TABLE orders ADD INDEX idx_user_amount_time (user_id, amount, order_time);加完索引后再跑一遍EXISTS版本性能提升已经很明显因为内层查询可以直接走索引覆盖扫描。第二步考虑把相关子查询改成半连接JOIN。由于EXISTS只关心存在性JOIN时不会产生重复行无需额外DISTINCTSELECT DISTINCT u.user_id, u.user_name FROM users u JOIN orders o ON o.user_id u.user_id WHERE u.user_level gold AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY;注意这里我加了DISTINCT原因是理论上一个用户可能有多条符合条件的订单不加DISTINCT用户会重复出现。虽然在这个业务场景下一对多的可能性不小所以保留DISTINCT更稳妥。第三步进一步考虑能不能减少外层扫描的行数。如果users表上user_level没有索引那每次都要全表扫描50万行去过滤gold会员这也是一个优化点ALTER TABLE users ADD INDEX idx_level (user_level);4.3 优化效果对比我在模拟环境实测了三组数据均为本地测试数据结果供参考优化阶段执行耗时扫描行数备注原始EXISTS无联合索引8.6秒订单表多次全表扫描相关子查询20万次全表扫描加联合索引后的EXISTS0.45秒订单表索引范围扫描索引覆盖生效改JOIN并保留DISTINCT0.32秒两张表均走索引优化器选择半连接策略最终版本再加大缓冲池0.28秒基本命中缓存冷启动场景接近这个案例的核心结论是第一步永远是索引第二步才是SQL改写。很多开发同学一上来就改写SQL结果索引没建换什么写法都白搭。5. 常见问题与避坑实录5.1 子查询的NULL陷阱前面提过NOT IN遇到NULL的问题这里再补两个容易被坑的场景。第一个是NOT IN子查询中包含NULL。看这个例子SELECT user_name FROM users WHERE user_id NOT IN (1, 2, NULL);直觉上应该返回除了1、2之外的所有用户实际结果却是一个空集。原因user_id NULL的结果是UNKNOWNNOT IN等价于user_id 1 AND user_id 2 AND user_id NULL只要有一个AND条件是UNKNOWN整条过滤条件就是UNKNOWN行不会被选中。第二个是聚合函数内部的NULL。COUNT(col)会跳过NULLAVG(col)也会忽略NULL参与计算。如果你在子查询里聚合而目标列本身有NULL值统计结果会和预期不一致。常见解决方式是提前用WHERE col IS NOT NULL过滤或者在聚合函数里显式处理。5.2 相关子查询的隐式性能杀手在开发环境测试相关子查询数据量只有几千行时效果很好一上线到生产环境几百万行就瞬间卡死。这类问题最隐蔽因为单看SQL完全找不出问题。我的排查习惯是遇到DEPENDENT SUBQUERY第一反应是确认外层驱动表的行数。如果外层只有几十行相关子查询完全没问题如果外层几十万行每条内层查询哪怕只有0.01秒总耗时也要几十分钟。优化手段按优先级排序给内层子查询的关联字段加索引把相关子查询改写成非相关的IN子查询改写成JOIN交优化器决定连接顺序如果逻辑复杂拆成多条SQL在应用层做内存关联。我个人实际经验是可读性优先保留子查询性能敏感路径一律改JOIN或拆SQL。生产环境稳定性比代码优雅重要得多。5.3 子查询与索引失效的边界子查询中经常包含函数或隐式类型转换这会让索引失效。比如WHERE DATE(order_time) 2024-01-01即使order_time上有索引因为套了DATE函数MySQL无法使用索引范围扫描只能全表扫描。更隐蔽的是隐式类型转换。如果user_id是VARCHAR类型而子查询传入的是INTMySQL会把字符串列转成数字再比较索引也可能失效。排查这类问题可以直接用EXPLAIN看type列如果从ref降级成ALL优先考虑函数或类型转换的影响。常见的保留索引写法-- 不推荐对索引列使用函数 WHERE DATE(order_time) 2024-01-01 -- 推荐使用范围条件 WHERE order_time 2024-01-01 AND order_time 2024-01-025.4 子查询的常见执行计划速查给一张速查表方便日常排查执行计划特征可能原因优先处理方式DEPENDENT SUBQUERY 全表扫描相关列缺索引加索引或改JOINMATERIALIZED 大临时表子查询结果集过大检查子查询过滤条件考虑改EXISTSSUBQUERY DERIVEDFROM子查询全表扫描给派生表关联字段加索引Impossible WHERE子查询条件恒为假检查NULL和常量条件Using temporary / Using filesort排序和去重过大优化索引减少扫描行数这套速查表是我自己排查慢查询时经常对照的清单遇到没见过的执行计划形态先按表格分类再顺着方向排查效率会高很多。6. 几个值得记住的子查询扩展用法6.1 子查询与窗口函数配合MySQL 8.0开始支持窗口函数子查询和它的配合可以解决很多“取每组TopN”的问题。以前实现“每个部门工资最高的前两人”需要用相关子查询或者复杂的自连接现在可以这样写SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) tmp WHERE rn 2;这里的派生表子查询把窗口函数的计算结果当作一列外层再基于rn过滤逻辑简洁清晰性能也远好于同逻辑的相关子查询版本。6.2 子查询直接驱动UPDATE和DELETE子查询还可以用在UPDATE和DELETE中实现精确的批量操作-- 批量删除“近一年未下单”的用户 DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR ) t );这里需要特别注意的是MySQL不允许直接在同一张表上同时进行SELECT和DELETE所以要包一层派生表来绕开限制。这种写法有坑如果子查询结果非常大DELETE操作会长时间持有行锁建议分批删除DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR LIMIT 5000 ) t );一批删除5000行循环执行避免长时间锁表。我处理过不止一次因为一次性DELETE数据量过大导致主从延迟飙升的事故都是靠分批删除解决的。6.3 子查询优化器的版本差异最后提一个版本相关的问题。不同MySQL版本对子查询的处理差异很大如果要做版本升级一定要重新压测子查询相关SQL。以我的经历来说5.6到5.7的升级中IN子查询的执行计划从单纯的SUBQUERY变成了支持Materialization和Semi-join很多原本慢的查询肉眼可见地变快了。而5.7到8.0的升级中派生表的Merge和Lateral能力增强但同时也可能因为哈希连接、窗口函数等新特性的引入导致一些子查询的代价估算发生改变。升级前跑一遍全量慢查询回归是我见过最有效的防坑手段。不要光看兼容性文档要用真实数据和真实SQL测试执行计划的变化。写在最后的实操心得做了这么多年数据库优化我的体会是子查询不是洪水猛兽真正危险的是对执行机制的无知。该用子查询的时候就大胆用但写完务必做三件事第一跑EXPLAIN确认select_type和执行策略第二检查相关子查询的嵌套层级尽量控制在两层以内第三对该子查询涉及的所有关联列确认有索引。最后再分享一个小技巧如果查询中用了IN子查询且怀疑优化器选错策略可以在SQL前面加EXPLAIN FORMATJSON输出里会有optimized_away、semijoin_strategy、materialized_from_subquery这些详细信息它们比单看EXPLAIN表格更直观地展示优化器到底做了什么决策。遇到玄学性能问题把JSON执行计划拉出来对照着看比盲目改SQL靠谱得多。 MySQL子查询性能优化实战：从慢查询到秒回的完整指南
1. 从一段真实业务需求说起子查询到底解决什么问题前几天排查一个线上慢查询业务方丢过来一段SQL说页面加载要七八秒用户快把客服电话打爆了。我先看了一眼表结构和数据量又跑了一遍EXPLAIN问题很快就定位到了——三层嵌套子查询最内层扫了全表中间层又做了文件排序外层再逐个匹配。这大概是MySQL子查询最典型也最容易被写坏的场景之一。先给不太熟的朋友补个基础定义子查询就是嵌套在另一个SQL父查询内部的SELECT语句它可以出现在WHERE、FROM、HAVING、SELECT列表、甚至UPDATE/DELETE语句中。你可能会问明明用JOIN也能实现大部分需求为什么还要用子查询因为有些逻辑用子查询表达更直观比如“找出比部门平均工资高的员工”“查询最近一单金额超过500的用户”——这类需求如果硬改成JOIN要么需要先聚合生成临时表要么得用窗口函数绕一大圈代码可读性反而更差。子查询这个语法从MySQL 4.1就开始支持了经历过好几个版本的优化器改写策略迭代。5.6之前性能经常被吐槽5.7开始引入了半连接优化和物化策略8.0又对派生表做了Lateral Derived优化、窗口函数配合子查询也更好用了。所以这篇文章不是简单罗列语法而是结合我实际排查和优化过的案例把子查询的分类、执行原理、性能陷阱、改写技巧一次讲透。2. 子查询的七种形态与实战拆解2.1 标量子查询最灵活但最容易忽视NULL标量子查询返回的是一行一列的单个值可以用在几乎所有需要单个值的地方。举几个最常见的场景-- 查询每个员工的工资与部门平均工资的差额 SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id) AS diff FROM employee e;这段SQL中SELECT AVG(salary)返回的是一个数值这就是标量子查询。实际开发中标量子查询最常见的问题不是性能而是NULL参与运算导致结果变成NULL。如果某个部门还没有员工内层AVG返回NULL外层salary - NULL的结果就是NULL。很多报表因此出现大面积的空值排查半天才发现是子查询返回了NULL。我的习惯是凡是参与数值运算的标量子查询外层一定要加IFNULL或者用COALESCE兜底SELECT emp_name, salary, salary - COALESCE((SELECT AVG(salary) FROM dept_emp WHERE dept_id e.dept_id), 0) AS diff FROM employee e;标量子查询还有个隐蔽的性能问题如果它在SELECT列表里并且是相关子查询引用了外层表的字段MySQL会对每一行外层记录都执行一次内层查询。也就是说外层有10万行内层就执行10万次。这在数据量小的时候没感觉一旦上了规模就是灾难性的后面我会专门讲相关子查询的优化方案。2.2 列子查询与行子查询语法糖背后的逻辑列子查询返回一列多行通常配合IN、ANY、ALL使用-- 找出所有在“销售部”待过的员工 SELECT emp_name FROM employee WHERE emp_id IN ( SELECT emp_id FROM dept_history WHERE dept_name 销售部 );行子查询返回一行多列可以同时比较多个字段-- 找出与张三在同一个部门且薪资相同的员工 SELECT emp_name FROM employee WHERE (dept_id, salary) ( SELECT dept_id, salary FROM employee WHERE emp_name 张三 );这里有个需要留意的点MySQL对行子查询的等值比较支持得不错但如果比较的是多个字段且其中某个字段允许NULL行为可能和直觉不一致。比如(dept_id, salary) (NULL, 5000)这种比较在SQL的三值逻辑下永远不是TRUE查询结果会静默丢失。我建议能用列子查询加IN解决的就不要用行子查询组合比较因为可读性更好优化器处理IN的改写策略也成熟很多。2.3 表子查询与派生表FROM子句里的临时视图表子查询出现在FROM子句中它的结果集被当作一张派生表使用必须起一个别名SELECT d.dept_name, tmp.avg_salary FROM department d JOIN ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) tmp ON d.dept_id tmp.dept_id;这里tmp就是派生表MySQL8.0之前派生表默认是物化的也就是说MySQL会先把内层查询的结果存在一张内部临时表里再做外层JOIN。8.0.14之后优化器支持了Lateral Derived Optimization允许派生表引用前面表的数据配合LATERAL关键字使用SELECT d.dept_name, tmp.emp_name FROM department d LEFT JOIN LATERAL ( SELECT emp_name FROM employee WHERE dept_id d.dept_id ORDER BY salary DESC LIMIT 1 ) tmp ON TRUE;这个写法可以很方便地实现“取每个部门工资最高的员工”以前需要用窗口函数或者反连接技巧现在一条LATERAL派生表就搞定了。我实测过在分层汇总类需求中LATERAL派生表比传统相关子查询的性能好很多因为它可以被优化器识别并采用更合理的连接顺序。2.4 EXISTS与IN谓词子查询的抉择EXISTS和IN是日常开发中用的最多的两种谓词子查询但很多人并不清楚它们在MySQL内部的不同处理路径。先说结论半连接场景下MySQL 5.7 对IN和EXISTS的改写策略已经非常接近并没有绝对的好坏之分关键取决于数据分布和索引情况。简单记忆外层表小、内层表大且内层查询能命中索引时IN通常表现更好外层表大、内层表小且需要快速短路判断时EXISTS配合索引通常更稳NOT IN一定要小心NULL值一旦子查询结果中有NULLNOT IN的判定会变成“不是UNKNOWN”导致整个查询返回空集。相比之下NOT EXISTS不会踩这个坑。举个例子公司有个接口要查询“从未下单的用户”新手往往写SELECT user_id, user_name FROM users WHERE user_id NOT IN ( SELECT user_id FROM orders );只要orders表里有任何一条user_id为NULL的记录这条查询就永远返回空。这不是MySQL的bug而是SQL标准中NULL的逻辑语义。正确写法应该用NOT EXISTSSELECT u.user_id, u.user_name FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id );我处理过好几次生产事故都是NOT IN遇到NULL导致报表数据清零排查起来还很绕。所以我的铁律是遇到NOT IN先问一句子查询列允许NULL吗允许就立刻改成NOT EXISTS。3. 子查询的执行计划与性能优化深挖3.1 看懂EXPLAIN中的子查询线索优化子查询的第一件事是学会看执行计划。MySQL的EXPLAIN输出中和子查询直接相关的字段主要在select_type列常见取值有select_type 取值含义常见场景PRIMARY最外层查询所有SQL都有SUBQUERY非相关子查询会被物化WHERE中独立的IN子查询DEPENDENT SUBQUERY相关子查询每行执行一次EXISTS、相关标量子查询MATERIALIZED子查询被物化为临时表IN子查询被优化为物化DERIVEDFROM子句中的派生表派生表查询UNIONUNION语句中的子查询联合查询看到DEPENDENT SUBQUERY就要警觉它意味着内层查询会依赖外层行数据逐行执行。DEPENDENT并不是优化器给出的“相关性描述”而是执行方式的声明对应的是每行重复执行的嵌套循环策略。另一种值得留意的是MATERIALIZED它说明子查询结果被提前物化成了一张内部临时表。物化本身不是坏事它避免了重复执行但如果物化出来的临时表很大且没有合适的索引后续Join时可能出现大表驱动小表的尴尬这个时候需要检查临时表的访问路径。3.2 半连接与物化MySQL如何悄悄改写你的SQLMySQL优化器中有一个非常重要的概念叫半连接Semi-join专门处理IN、EXISTS、ANY等形式的子查询。半连接的含义是不关心子查询匹配了多少行只关心“是否存在至少一行匹配”。优化器会把这类子查询改写为半连接执行计划可选的策略主要包括FirstMatch对外层每一行只要在内层找到第一条匹配就停止扫描。这是最朴素也最常用的策略等价于EXISTS的短路逻辑。DuplicateWeedout把内层匹配结果去重后再做普通连接。适用于子查询内部本身会产生重复行的场景。LooseScan利用内层表的索引对每个分组只取第一条匹配值避免重复扫描。Materialization先把子查询结果物化成临时表在临时表上建索引再做连接。这些策略不是DBA手动指定的是优化器基于统计信息自动选择的。但在某些情况下优化器会失明。比如内外层表的数据分布严重倾斜、统计信息过期、或者WHERE条件里写了函数导致索引失效优化器就可能选错策略。这时候可以手动改写成JOIN形式来绕过错误执行计划也可以调整optimizer_switch中的半连接开关。我在故障排查时见过最典型的案例一个IN子查询被优化器选成了FirstMatch但内层表过滤后选择性太差导致外层每一行都要扫描大量内层记录。把optimizer_switch里的firstmatch临时关闭强制走物化查询时间从3秒降到0.1秒。3.3 子查询改JOIN的黄金法则很多优化文章提倡“子查询一律改JOIN”这个说法过于绝对。JOIN改写后由于普通JOIN会产生重复行往往还要额外加DISTINCT去重反而可能引入新的性能问题。我的黄金法则是能保持语义不变的前提下才改JOIN。IN子查询改JOIN一般要加DISTINCTEXISTS改成JOIN则不需要去重但要注意多表连接会放大行数。优先检查索引而不是优先改写法。子查询慢很多情况下不是子查询本身的问题而是缺少合适的索引。相关子查询优先考虑改写为派生表或EXISTS而不是盲目改JOIN因为相关子查询意味着N1模式改成JOIN可能直接用哈希连接或索引嵌套循环解决。聚合类子查询改成JOINGROUP BY往往能利用索引和优化器的排序优化。用一句话总结子查询和JOIN只是同一逻辑的两种不同表达最终执行性能取决于优化器能不能把SQL翻译成高效的执行计划。我们的目标是给优化器更多选择而不是替代它做决策。4. 实战案例从慢查询到秒回的完整优化4.1 场景构建与初始SQL为了讲清楚我构造一个贴近真实业务的模拟场景。某电商平台有两张表orders订单表约200万行order_id、user_id、amount、status、order_timeusers用户表约50万行user_id、user_name、user_level业务需求是找出所有“黄金会员”中最近30天内有下单但订单金额低于100元的用户。新手第一版SQL通常写成相关子查询SELECT u.user_id, u.user_name FROM users u WHERE u.user_level gold AND EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.user_id AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY );单独看这条SQL的执行计划还行但如果users表中gold会员有20万EXISTS子查询每行执行一次orders表上又没有(user_id, amount, order_time)的联合索引后果就是20万次全表扫描耗时轻松超过10秒。4.2 逐步拆解与改写第一步我不急着改SQL先检查索引。orders表上原来只有主键和user_id单列索引user_id过滤后还要回表判断amount和order_time这是一个典型的索引覆盖不足问题。我先加上联合索引ALTER TABLE orders ADD INDEX idx_user_amount_time (user_id, amount, order_time);加完索引后再跑一遍EXISTS版本性能提升已经很明显因为内层查询可以直接走索引覆盖扫描。第二步考虑把相关子查询改成半连接JOIN。由于EXISTS只关心存在性JOIN时不会产生重复行无需额外DISTINCTSELECT DISTINCT u.user_id, u.user_name FROM users u JOIN orders o ON o.user_id u.user_id WHERE u.user_level gold AND o.amount 100 AND o.order_time NOW() - INTERVAL 30 DAY;注意这里我加了DISTINCT原因是理论上一个用户可能有多条符合条件的订单不加DISTINCT用户会重复出现。虽然在这个业务场景下一对多的可能性不小所以保留DISTINCT更稳妥。第三步进一步考虑能不能减少外层扫描的行数。如果users表上user_level没有索引那每次都要全表扫描50万行去过滤gold会员这也是一个优化点ALTER TABLE users ADD INDEX idx_level (user_level);4.3 优化效果对比我在模拟环境实测了三组数据均为本地测试数据结果供参考优化阶段执行耗时扫描行数备注原始EXISTS无联合索引8.6秒订单表多次全表扫描相关子查询20万次全表扫描加联合索引后的EXISTS0.45秒订单表索引范围扫描索引覆盖生效改JOIN并保留DISTINCT0.32秒两张表均走索引优化器选择半连接策略最终版本再加大缓冲池0.28秒基本命中缓存冷启动场景接近这个案例的核心结论是第一步永远是索引第二步才是SQL改写。很多开发同学一上来就改写SQL结果索引没建换什么写法都白搭。5. 常见问题与避坑实录5.1 子查询的NULL陷阱前面提过NOT IN遇到NULL的问题这里再补两个容易被坑的场景。第一个是NOT IN子查询中包含NULL。看这个例子SELECT user_name FROM users WHERE user_id NOT IN (1, 2, NULL);直觉上应该返回除了1、2之外的所有用户实际结果却是一个空集。原因user_id NULL的结果是UNKNOWNNOT IN等价于user_id 1 AND user_id 2 AND user_id NULL只要有一个AND条件是UNKNOWN整条过滤条件就是UNKNOWN行不会被选中。第二个是聚合函数内部的NULL。COUNT(col)会跳过NULLAVG(col)也会忽略NULL参与计算。如果你在子查询里聚合而目标列本身有NULL值统计结果会和预期不一致。常见解决方式是提前用WHERE col IS NOT NULL过滤或者在聚合函数里显式处理。5.2 相关子查询的隐式性能杀手在开发环境测试相关子查询数据量只有几千行时效果很好一上线到生产环境几百万行就瞬间卡死。这类问题最隐蔽因为单看SQL完全找不出问题。我的排查习惯是遇到DEPENDENT SUBQUERY第一反应是确认外层驱动表的行数。如果外层只有几十行相关子查询完全没问题如果外层几十万行每条内层查询哪怕只有0.01秒总耗时也要几十分钟。优化手段按优先级排序给内层子查询的关联字段加索引把相关子查询改写成非相关的IN子查询改写成JOIN交优化器决定连接顺序如果逻辑复杂拆成多条SQL在应用层做内存关联。我个人实际经验是可读性优先保留子查询性能敏感路径一律改JOIN或拆SQL。生产环境稳定性比代码优雅重要得多。5.3 子查询与索引失效的边界子查询中经常包含函数或隐式类型转换这会让索引失效。比如WHERE DATE(order_time) 2024-01-01即使order_time上有索引因为套了DATE函数MySQL无法使用索引范围扫描只能全表扫描。更隐蔽的是隐式类型转换。如果user_id是VARCHAR类型而子查询传入的是INTMySQL会把字符串列转成数字再比较索引也可能失效。排查这类问题可以直接用EXPLAIN看type列如果从ref降级成ALL优先考虑函数或类型转换的影响。常见的保留索引写法-- 不推荐对索引列使用函数 WHERE DATE(order_time) 2024-01-01 -- 推荐使用范围条件 WHERE order_time 2024-01-01 AND order_time 2024-01-025.4 子查询的常见执行计划速查给一张速查表方便日常排查执行计划特征可能原因优先处理方式DEPENDENT SUBQUERY 全表扫描相关列缺索引加索引或改JOINMATERIALIZED 大临时表子查询结果集过大检查子查询过滤条件考虑改EXISTSSUBQUERY DERIVEDFROM子查询全表扫描给派生表关联字段加索引Impossible WHERE子查询条件恒为假检查NULL和常量条件Using temporary / Using filesort排序和去重过大优化索引减少扫描行数这套速查表是我自己排查慢查询时经常对照的清单遇到没见过的执行计划形态先按表格分类再顺着方向排查效率会高很多。6. 几个值得记住的子查询扩展用法6.1 子查询与窗口函数配合MySQL 8.0开始支持窗口函数子查询和它的配合可以解决很多“取每组TopN”的问题。以前实现“每个部门工资最高的前两人”需要用相关子查询或者复杂的自连接现在可以这样写SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) tmp WHERE rn 2;这里的派生表子查询把窗口函数的计算结果当作一列外层再基于rn过滤逻辑简洁清晰性能也远好于同逻辑的相关子查询版本。6.2 子查询直接驱动UPDATE和DELETE子查询还可以用在UPDATE和DELETE中实现精确的批量操作-- 批量删除“近一年未下单”的用户 DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR ) t );这里需要特别注意的是MySQL不允许直接在同一张表上同时进行SELECT和DELETE所以要包一层派生表来绕开限制。这种写法有坑如果子查询结果非常大DELETE操作会长时间持有行锁建议分批删除DELETE FROM users WHERE user_id IN ( SELECT user_id FROM ( SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id u.user_id WHERE o.order_id IS NULL AND u.register_time NOW() - INTERVAL 1 YEAR LIMIT 5000 ) t );一批删除5000行循环执行避免长时间锁表。我处理过不止一次因为一次性DELETE数据量过大导致主从延迟飙升的事故都是靠分批删除解决的。6.3 子查询优化器的版本差异最后提一个版本相关的问题。不同MySQL版本对子查询的处理差异很大如果要做版本升级一定要重新压测子查询相关SQL。以我的经历来说5.6到5.7的升级中IN子查询的执行计划从单纯的SUBQUERY变成了支持Materialization和Semi-join很多原本慢的查询肉眼可见地变快了。而5.7到8.0的升级中派生表的Merge和Lateral能力增强但同时也可能因为哈希连接、窗口函数等新特性的引入导致一些子查询的代价估算发生改变。升级前跑一遍全量慢查询回归是我见过最有效的防坑手段。不要光看兼容性文档要用真实数据和真实SQL测试执行计划的变化。写在最后的实操心得做了这么多年数据库优化我的体会是子查询不是洪水猛兽真正危险的是对执行机制的无知。该用子查询的时候就大胆用但写完务必做三件事第一跑EXPLAIN确认select_type和执行策略第二检查相关子查询的嵌套层级尽量控制在两层以内第三对该子查询涉及的所有关联列确认有索引。最后再分享一个小技巧如果查询中用了IN子查询且怀疑优化器选错策略可以在SQL前面加EXPLAIN FORMATJSON输出里会有optimized_away、semijoin_strategy、materialized_from_subquery这些详细信息它们比单看EXPLAIN表格更直观地展示优化器到底做了什么决策。遇到玄学性能问题把JSON执行计划拉出来对照着看比盲目改SQL靠谱得多。