1. 子查询解决的业务问题和它的执行直觉1.1 同一个需求三次查询与一条SQL的差别带新人的时候我经常用这样一个需求开场查出工资高于公司平均工资的所有员工。让新人先用三条SQL做写出来大概是这样的SELECT AVG(salary) FROM emp;拿到结果比如是 7820.50然后手动填进第二条查询SELECT ename, salary FROM emp WHERE salary 7820.50;如果平均工资变了或者换一个部门、换一个时间段又得重新查一次、重新填一次。这个流程最大的问题是你让数据库多干了一轮活更麻烦的是中间结果靠人肉搬运容易出错。你要是把这条SQL直接写进报表系统明天平均工资一变报表数字就错得莫名其妙。子查询干的事情就是把这三次查询压缩成一条SQLSELECT ename, salary FROM emp WHERE salary (SELECT AVG(salary) FROM emp);内层的SELECT AVG(salary) FROM emp先执行算出平均值把结果交给外层的 WHERE 条件继续比较。对 MySQL 来说它会在内部帮你安排好执行顺序不需要你手动维护中间结果。这就是子查询的第一直觉理解一个查询的结果作为另一个查询的输入数据源。1.2 子查询的本质结果集作为另一个查询的输入再往深一层想。SQL 是声明式语言你告诉数据库我要什么而不是怎么找。子查询就是在这种思路下自然而然长出来的东西构造查询的时候你会发现很多过滤条件本身不是一个固定值而是一个需要先算一算才知道的结果。比如查工资比所有财务部员工都高的人这里的财务部员工的工资不是一个常量而是一组数据你必须先查出来才能继续比。子查询就是把这组数据内联进了条件里。我自己的一个经验是学子查询最容易卡住的不是语法而是脑子里的执行模型。你只需要记住一个简化模型——MySQL 先执行子查询把结果返回给外层查询去用大部分语法问题都能想通。至于实际执行时优化器会不会把子查询改写那是另一层面的事后面第五部分专门讲性能时再说。搞懂这个本质之后第二个问题接踵而至子查询返回的结果有好几种形状。返回一个数字、返回一列数字、返回一整行数据、返回一张临时表这四种情况在 MySQL 里写法完全不同。这就是接下来要说的四类子查询。2. 四类子查询标量、列、行、表各自怎么认怎么写2.1 标量子查询必须保证返回一个值标量子查询是最简单的一种子查询只返回一个值一行一列。它最常见的使用位置是 WHERE 条件里跟、、这样的单值比较运算符搭配。SELECT ename, salary FROM emp WHERE salary (SELECT AVG(salary) FROM emp);这个子查询返回一个平均值所以外层能拿它跟 salary 直接比较。标量子查询还可以出现在 SELECT 子句里作为一个计算列SELECT ename, salary, (SELECT AVG(salary) FROM emp) AS avg_salary FROM emp;这样每行记录后面都会带一个全公司的平均薪资做对照非常适合做报表。写标量子查询最需要留意的是它必须保证只返回一行一列。只要子查询里没写好条件、返回了两行MySQL 就会报错-- 如果表里有两个部门都叫财务部这条SQL直接报错 SELECT ename, salary FROM emp WHERE dept_id (SELECT dept_id FROM dept WHERE dname 财务部);报错信息我后面会详细讲。这里先说结论凡是用了的标量子查询子查询结果一定得是单行否则等于告诉 MySQL 我不知道你想比哪一行。2.2 列子查询与 IN 的黄金搭档列子查询返回的是单列多行可以用 IN、ANY、ALL 这些关键字来承接。最基础的就是 INSELECT ename, salary FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE city 上海);子查询返回上海所有部门的 dept_id比如是 10、20、30 三行外层再用dept_id IN (10,20,30)去过滤。这其实就是把一个之前需要两三次 JOIN 才能完成的查询变得非常直白。遇到 NOT IN 时有一个特别隐蔽的坑如果子查询结果里含有 NULLNOT IN 会整体失效一条数据都查不出来。这个我放到第六部分的翻车现场里详细拆这里先记住能用 NOT EXISTS 就用 NOT EXISTS别轻易碰 NOT IN。2.3 行子查询整行对齐的比较行子查询返回一行多列写法上必须用圆括号把多列包在一起整体跟另一个行比较SELECT ename, salary FROM emp WHERE (job, salary) ( SELECT job, salary FROM emp WHERE eid 10086 );这条SQL的意思是去找所有岗位和工资都跟10086号员工一样的人。子查询查出来的是一行包含 job 和 salary 两个字段外层把每一行员工的 (job, salary) 组成一个行值跟子查询那一行做整体比较。行子查询看起来是 MySQL 支持的语法里用得最少的一种习惯之后你会发现它其实很适合按多列条件找相似记录的场景。但注意如果子查询返回的不只一行照样报 Subquery returns more than 1 row。2.4 表子查询FROM 子句的派生表表子查询也叫派生表返回多行多列整体像一张临时表常出现在 FROM 子句里SELECT t.dept_id, t.cnt FROM ( SELECT dept_id, COUNT(*) AS cnt FROM emp GROUP BY dept_id ) t WHERE t.cnt 5;这里子查询先按部门统计人数外层再从这个统计结果里捞人数大于5的部门。注意这条SQL里我给派生表起了个别名t没有别名 MySQL 会直接报错Every derived table must have its own alias原因很简单外层的t.dept_id必须有一个可以引用的表名。就算你不引用列名MySQL 也强制要求派生表必须带别名这个语法规定从老版本到 8.0 一直存在别在这上面交学费。表子查询的应用场景非常广比如分页后用外部条件过滤、从聚合结果里再聚合、取每组前 N 条之类的复杂需求基本都是靠派生表实现的。3. WHERE 只是入口——SELECT、FROM、HAVING 里的子查询各有各的脾气3.1 SELECT 子句的标量关联子查询最容易被忽略的子查询位置其实是 SELECT 子句。它通常配合关联条件使用子查询内部引用外层查询的列逐行去计算。SELECT e.ename, e.salary, e.dept_id, (SELECT d.dname FROM dept d WHERE d.dept_id e.dept_id) AS dept_name FROM emp e;对 emp 的每一行MySQL 都拿当前的e.dept_id去 dept 表里找对应的部门名拼在当前行后面。这种写法的执行代价要格外小心如果外层有 10 万行且子查询里的 dept_id 没有索引就要做 10 万次查找。所以我在实践里用 SELECT 子句中的关联子查询时一定会确认关联字段有索引否则宁可用 LEFT JOIN 把它改写掉SELECT e.ename, e.salary, e.dept_id, d.dname FROM emp e LEFT JOIN dept d ON d.dept_id e.dept_id;什么时候必须保留 SELECT 里的子查询比如你想在每行后面附上全公司的平均工资做对比而这个平均值是个聚合结果用 JOIN 会放大行数用子查询反而干净SELECT e.ename, e.salary, (SELECT AVG(salary) FROM emp) AS company_avg FROM emp e;3.2 FROM 子句的派生表给小结果集起个名字FROM 子句的子查询本质上就是派生表前面已经提到基本写法。这一小节的实操意义在于派生表很多时候是一次性中间结果不要重复去物化一份很大的数据。看一个实际场景。你想查各部门平均工资以及平均工资高于全公司平均的部门SELECT t.dept_id, t.avg_salary FROM ( SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id ) t WHERE t.avg_salary (SELECT AVG(salary) FROM emp);注意这里派生表t只做了一层聚合数据量不会太大MySQL 会把物化后的结果缓存起来外层再过滤。如果你在派生表里写复杂 JOIN再在外层做同样复杂的查询那就等于让 MySQL 多跑一遍白白浪费。另一个细节派生表里能不能用 ORDER BY能用但很多情况下没意义。如果一个派生表只是用来被外层过滤里面的排序结果除非配合 LIMIT 做取前几条再过滤否则最终顺序由外层决定内层排序经常被优化器忽略。MySQL 8.0.14 之后还支持了 LATERAL 派生表可以引用同一 FROM 子句中前面表的列这让每组的 TOP N 查询能写得更简洁SELECT d.dname, sub.eid, sub.salary FROM dept d, LATERAL ( SELECT e.eid, e.salary FROM emp e WHERE e.dept_id d.dept_id ORDER BY e.salary DESC LIMIT 3 ) sub;这对选手动的分组建 TOP N需求是很大的语法解放不过使用场景相对进阶先把普通派生表用熟再上不迟。3.3 HAVING 子句子查询做分组门槛HAVING 是对分组后的结果做过滤它也支持子查询。典型的用法是把全体平均水平作为分组门槛SELECT dept_id, AVG(salary) AS avg_salary FROM emp GROUP BY dept_id HAVING AVG(salary) (SELECT AVG(salary) FROM emp);这条SQL查的是哪些部门的平均工资高于全公司平均水平。这里子查询的结果是一个标量全公司平均它跟 HAVING 里再聚合出来的AVG(salary)比较。我见过的初学者错误是在 HAVING 里直接写salary (SELECT ...)把原始行字段跟聚合结果混用。记住 HAVING 里你只能引用分组列和聚合函数原始行的 salary 在这里是没有意义的。4. EXISTS、ANY、ALL这三个关键字最难缠语义差异必须先理清4.1 EXISTS 只关心有没有和 IN 不是一回事EXISTS 的子查询不返回具体数据只返回有没有记录这个布尔结果。子查询写 SELECT 什么字段都无所谓SELECT 1甚至SELECT NULL都行MySQL 只判断是否能查到一行。SELECT d.dept_id, d.dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp e WHERE e.dept_id d.dept_id );这条SQL的语义是只要 dept 表里的某个部门有至少一个员工就返回这个部门。对比 IN 的写法SELECT dept_id, dname FROM dept WHERE dept_id IN (SELECT dept_id FROM emp);看起来结果一样实际执行差异可能很大。EXISTS 走的是从外层表取一行到内层去探测的逻辑所以它特别适合外层结果集小、内层关联字段有索引的场景。IN 则常常被 MySQL 改写成半连接semi-join跟 EXISTS 的差距在优化器层面会不断变化不能一概而论说谁一定快。但有一个场景我必须强调NOT IN 遇到 NULL 会翻车而 NOT EXISTS 不会。这是无数人在生产库里踩过的坑我专门在第六部分展开。4.2 ANY 与 ALL比较运算符的放大镜ANY 和 ALL 必须跟比较运算符配合使用它们的语义直白地讲就是 ANY(子查询)大于子查询返回的任意一个值也就是大于其中最小的值 ALL(子查询)大于子查询返回的所有值也就是大于其中最大的值 ANY(子查询)小于任意一个值即小于最大的值 ALL(子查询)小于所有值即小于最小的值举个例子。查工资比销售部任意一名员工都高的非销售部员工SELECT ename, salary FROM emp WHERE salary ANY ( SELECT salary FROM emp WHERE dept_id 20 ) AND dept_id 20;再查工资比销售部所有人都高的SELECT ename, salary FROM emp WHERE salary ALL ( SELECT salary FROM emp WHERE dept_id 20 ) AND dept_id 20;如果是处理大于所有的需求我建议直接改成聚合函数写法语义更清晰性能也通常更好SELECT ename, salary FROM emp WHERE salary (SELECT MAX(salary) FROM emp WHERE dept_id 20) AND dept_id 20;4.3 等价改写关系IN、ANY、ALL与聚合函数的换算这一节整理一个等价关系表我平时改写 SQL 时经常用到直接给了结论原写法等价写法x IN (子查询)x ANY (子查询)x NOT IN (子查询)x ALL (子查询)x ALL (子查询)x (SELECT MAX(x) FROM ...)x ALL (子查询)x (SELECT MIN(x) FROM ...)x ANY (子查询)x (SELECT MIN(x) FROM ...)x ANY (子查询)x (SELECT MAX(x) FROM ...)特别警告一个易错点x ANY (子查询)并不等于x NOT IN (子查询)。 ANY表示只要与其中一个值不相等就成立很可能把本来应该过滤掉的记录全部放进来。举个例子如果子查询返回 [10, 20, 30]salary ANY(...)对于 salary10 的记录来说1020 为真所以这条记录会被选出来——这显然不是你写 NOT IN 的意图。还有一个隐蔽细节ALL 在子查询返回空集合时返回 TRUE所以salary ALL (空结果)会变成全表都满足条件ANY 在空集合时返回 FALSE。改写成语义明确的聚合查询MAX/MIN 对空结果返回 NULL比较结果为 UNKNOWN条件不成立能帮你规避这个逻辑坑。5. 子查询的性能账为什么慢、怎么用 EXPLAIN 看清真相、如何改写5.1 相关子查询重复执行的代价子查询慢的最大根源是关联子查询对外层每一行都要执行一次。拿一个典型 SQL 举例SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.dept_id e.dept_id) AS dept_name FROM emp e;如果 emp 有 20 万行子查询在有索引的情况下执行 20 万次单点查询可能还好但如果 dept_id 上没有索引这 20 万次子查询全是全表扫描性能直接雪崩。在 EXPLAIN 的输出里这种子查询会被标记为DEPENDENT SUBQUERY一看就知道是关联子查询。看到这个词第一反应就应该是这里可能有一张被反复全表扫的关联表赶紧看内层关联字段有没有索引。5.2 EXPLAIN 怎么读子查询部分用 EXPLAIN 看子查询相关执行计划重点看几类关键词输出标记含义处理建议DEPENDENT SUBQUERY相关子查询外层每行执行一次优先检查关联字段索引或改写为 JOINSUBQUERY非相关子查询一般执行一次相对安全关注物化开销MATERIALIZED子查询被物化成临时表关注物化结果大小加好索引UNCACHEABLE SUBQUERY子查询每次都要重算如用了 RAND() 等非确定性函数尽量避免这类写法MySQL 8.0 之后还可以用EXPLAIN ANALYZE直接看实际执行时间和循环次数EXPLAIN ANALYZE SELECT ename, salary FROM emp WHERE dept_id IN (SELECT dept_id FROM dept WHERE city 上海);它会输出类似Nested loop inner join这样的执行细节你能直观看到优化器到底把 IN 子查询改写成了什么连接方式。实话讲把EXPLAIN ANALYZE养成习惯之后你会发现自己对 SQL 性能的直觉会准很多至少不会再凭感觉瞎猜。5.3 实用改写套路JOIN、EXISTS、临时表我根据自己的实战经验整理了几条子查询改写的主路径大家在现场遇到慢查询直接按这个方向排查第一把 SELECT 子句里的关联子查询改成 LEFT JOIN。因为前者本质上是逐行去查后者一次连接搞定。注意改完之后要检查有没有行数放大问题必要时加 DISTINCT。-- 原写法 SELECT e.ename, (SELECT d.dname FROM dept d WHERE d.dept_id e.dept_id) AS dept_name FROM emp e; -- 改写 SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON d.dept_id e.dept_id;第二EXISTS 与 IN 的选择要基于数据分布。外层表小、内层表大且有索引时EXISTS 风格通常表现更好外层表大、内层结果集小IN 或半连接路径可能更优。MySQL 8.0 的优化器会自动做一些重写但搞清楚原理仍然能帮你判断执行计划是否合理。第三带聚合的慢子查询先物化再加工。如果 IN 子查询里带着 GROUP BY、聚合函数优化器往往只能物化子查询结果。这时候你可以在应用层先把子查询跑一遍把结果作为常量列表传入 IN或者让派生表走哈希连接看看 EXPLAIN 里有没有出现 hash join通常物化后的连接效率并不差。第四不要无脑改成 JOIN。JOIN 会带来行数放大一个部门里 10 个员工你 JOIN 完就多出 9 行重复数据有时还得 DISTINCT 回去反而更慢。我见过不少把简单的IN子查询改成 JOIN 反而拖慢全表的案例。6. 实践中最常见的翻车现场与排查思路6.1 NOT IN 遇到 NULL看起来对的 SQL 查不到数据这是子查询里最经典、影响面最大的坑。假设 emp 表里有员工dept 表里有部门但恰好 dept 表中有一个dept_id为 NULL 的记录或者 emp 中有员工没分配部门dept_id 为 NULL。你想查没有分配在任何部门记录里的员工SELECT * FROM emp WHERE dept_id NOT IN (SELECT dept_id FROM dept);这条SQL在很多人眼里天经地义但执行结果经常是空集甚至一条都不返回。原因在于 SQL 的三值逻辑。NOT IN等价于 ALL当子查询结果里出现 NULL 时dept_id NULL的结果是 UNKNOWN不是 TRUE。而 WHERE 只保留 TRUE 的记录UNKNOWN 全部被过滤掉于是整个查询返回空。排查思路很简单把子查询单独跑一遍看看有没有 NULL或者直接用这条能防坑的写法SELECT * FROM emp WHERE dept_id NOT IN (SELECT dept_id FROM dept WHERE dept_id IS NOT NULL);但我更推荐直接养成用 NOT EXISTS 的习惯SELECT e.* FROM emp e WHERE NOT EXISTS ( SELECT 1 FROM dept d WHERE d.dept_id e.dept_id );NOT EXISTS 的语义是不存在这样一条部门记录完全不涉及 NULL 的比较逻辑上更不容易出错性能上配合索引也往往表现更好。6.2 子查询返回多行Subquery returns more than 1 row这个报错几乎所有写子查询的人都会遇到而且第一次遇到时最容易懵。它的意思很简单你用了这种单值比较但子查询返回了两行以上。最常见的场景是SELECT ename FROM emp WHERE dept_id (SELECT dept_id FROM dept WHERE city 上海);如果上海有多个部门这个子查询返回 10、20 两行MySQL 不知道拿哪一行跟 dept_id 比较直接报错。排查步骤我建议按顺序走把子查询单独摘出来跑一遍数一下返回多少行确认需求到底是想匹配一个值还是匹配一组值——前者用子查询加条件保证单行后者改成 IN、ANY 或 EXISTS如果是聚合类需求检查子查询里是不是漏了 GROUP BY或者漏了限定条件比如限定某个具体部门。顺手说一种容易漏的情况子查询里忘加限制条件碰巧表数据量小的时候不报错数据量一大立刻炸。所以上线前一定要在接近生产数据量的环境里跑一遍。6.3 别踩的界限子查询里 LIMIT 和 ORDER BY 的版本差异子查询里用 LIMIT在不同版本里态度完全不同。老版本 MySQL5.x 时代里很多包含 LIMIT 的子查询直接不让用报错信息常是This version of MySQL doesnt yet support LIMIT IN/ALL/ANY/SOME subquery你要是在 WHERE dept_id IN (子查询) 里写 LIMIT大概率会撞上这个错误。处理办法是包一层派生表SELECT ename FROM emp WHERE dept_id IN ( SELECT dept_id FROM ( SELECT dept_id FROM dept ORDER BY dept_id LIMIT 2 ) x );再说 ORDER BY。在子查询里写 ORDER BY除非搭配 LIMIT否则很多时候是没有意义的。因为子查询结果如果没有 LIMIT优化器可能直接忽略排序你以为是取最大的一条实际取出来的是全量。就算你配了 LIMIT也要明白MySQL 8.0.31 之后对派生表里有 LIMIT 却没有 ORDER BY的写法变得非常警惕取出来的行可能是随机的。所以我的建议很简单子查询里用 LIMIT务必同时写 ORDER BY让它明确我要的是排序后的前几条能不用 LIMIT 子查询就别用很多需求改写成窗口函数比如 ROW_NUMBER更干净MySQL 8.0 里窗口函数已经足够成熟。6.4 静默不出结果的等值判断子查询返回 NULL还有一种 错的没报错但就是查不到数据 的坑比报错更麻烦。SELECT ename FROM emp WHERE dept_id (SELECT dept_id FROM dept WHERE dname 不存在的部门);子查询返回的结果是 NULL那么dept_id NULL的结果是 UNKNOWNWHERE 不保留 UNKNOWN于是整个查询返回空集。没有报错、没有警告就是结果为空。排查这种问题时先单独跑一下子查询看返回值是不是 NULL或者考虑用NULL 安全等于来明确我允许 NULL 参与比较。但更合理的做法是如果子查询可能查不到数据先在外面做个判断或者在应用层把子查询结果单独查一次。我在带团队的时候反复交代一句话子查询里查不到数据不是错误但不提前想清楚 查不到时外层要怎么办才是真正的错误。6.5 一个综合案例的完整排查链路说一个我自己实际处理过的慢查询能把这几个坑串起来。某次线上有个报表SQL查近30天有订单的客户SELECT c.customer_id, c.customer_name FROM customer c WHERE c.customer_id IN ( SELECT customer_id FROM orders WHERE order_status PAID AND order_date NOW() - INTERVAL 30 DAY )表不大customer 3 万行orders 80 万行但这个查询每次都要 4 秒多。EXPLAIN 一看IN 子查询走了物化orders 全表扫描后被物化成临时表再跟 customer 连接。我做的第一件事是把 order_status 和 order_date 的复合索引补上ALTER TABLE orders ADD INDEX idx_status_date (order_status, order_date);索引生效后物化的数据量从 80 万行缩到近 30 天实际成交的几万行查询降到 0.8 秒。第二件事是加了一个 customer_id 为空的安全判断。订单表里历史数据有脏数据customer_id 偶尔是 NULL。虽然这次查询里有 INNER JOIN 语义不会选出来但后续有同事用同样的子查询改 NOT IN 需求时直接中了 6.1 说的 NULL 坑。我最后把这段 SQL 统一改成 EXISTS 写法两拨人的需求都能覆盖而且语义更稳SELECT c.customer_id, c.customer_name FROM customer c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.customer_id AND o.order_status PAID AND o.order_date NOW() - INTERVAL 30 DAY )结论很清晰子查询本身不慢慢的是没有索引、没有想清楚 NULL 边界、没有选对关联写法。最后再分享一个我的调试心得。遇到子查询相关的问题先用五步法走一遍第一步把子查询单独抽出来跑确认返回内容的形状第二步明确这个形状匹配的是哪种子查询类型第三步检查内层涉及字段的索引第四步用 EXPLAIN ANALYZE 验证实际执行路径第五步判断是否需要改写为 JOIN 或 EXISTS。这五步走完九成的问题都能定位到根因剩下的基本就是业务需求自己没定义清楚。子查询这东西学的时候觉得不就是嵌套嘛真正在生产环境把它用明白、用稳靠的还是对执行模型和数据分布的持续理解。