前几天有个朋友发来一条SQL三层子查询嵌套跑了小半天都不出结果问我怎么优化。我一看他是用相关子查询在百万行表里做逐行扫描关联字段上还没有索引不慢才怪。MySQL里的子查询subquery就是这么个东西用好了是利器用歪了是灾难而且不管是面试还是日常开发几乎绕不开它。这篇文章我不想堆空洞理论就从实际开发者的角度把MySQL子查询从头到尾盘一遍先讲清楚子查询到底是什么、哪些场景必须用它再把标量子查询、IN、EXISTS、派生表逐个拆开接着拿一个学生课程成绩库做完整实战最后重点聊聊性能优化和几个容易踩的坑。适合刚接触SQL的新人也适合写了几年SQL但一遇到子查询优化就头疼的老同学。1. 先搞清楚子查询是什么一个查询套在另一个查询里1.1 子查询的定义与三种基础形态子查询说白了就是一个SELECT语句嵌套在另一个SQL语句里被嵌套的那条SELECT通常用括号包起来。它内部的执行结果会作为外部查询的输入可以是一个值、一列数据、一行记录也可以是一整张临时表。根据返回值我习惯把子查询分成三种形态来看标量子查询返回一行一列比如(SELECT MAX(score) FROM score)就是一个数字或一个字符串可以像普通值一样放在比较表达式中。列子查询/行子查询返回一列多行或者一行多列最典型的就是WHERE student_id IN (SELECT student_id FROM ...)把子查询的结果当作一个集合。表子查询派生表返回一个多行多列的结果集出现在FROM子句后面相当于临时造了一张表例如FROM (SELECT ...) AS t。子查询可以出现的位置也很多WHERE子句、HAVING子句、FROM子句、SELECT的字段列表甚至在UPDATE和DELETE语句里也照样能用。很多人以为子查询只是“查询时用的”其实更新和删除操作中它更常用后面我会专门演示。不少初学者会把子查询和JOIN搞混。简单理解JOIN是把两张表横向拼接子查询是先算出一部分结果再交给外层逻辑更像“分步计算”。这两种方式在很多场景下能互相替代但语义和性能可能完全不同这个是第4章的重点。1.2 为什么需要子查询有些过滤条件必须先“算”出来很多场景用JOIN也能做但子查询有它不可替代的位置。最典型的是“WHERE条件里拿聚合结果做比较”。举个例子你想查“成绩高于全校平均分的学生”。SQL的语法规定WHERE子句里不能直接写聚合函数你不能写成SELECT * FROM score WHERE score AVG(score);这句话在MySQL里直接报错。正确的思路是先用子查询把平均分算出来再放到WHERE里去比较SELECT * FROM score WHERE score (SELECT AVG(score) FROM score);在这里子查询就相当于先算出“全校平均分”这个常量然后外部查询拿这个常量去逐行过滤。这种“先算一步再比一步”的逻辑用JOIN也能写但通常要绕一大圈远不如子查询直观。还有一种场景是希望筛选“主表中满足某个集合条件的记录”。比如“找出所有选过课的学生”“找出所有没有选课的学生”这类需求如果用JOIN会因为一对多关系让主表记录重复还得加DISTINCT而用子查询语义上更贴合人的思考习惯。尤其是“没有选课”这种否定条件NOT EXISTS和NOT IN的子查询写法几乎成了标准答案。再说一个实战中很常见的场景UPDATE和DELETE操作里需要引用另一张表的数据。比如“把所有低于课程平均分的成绩标记为待补考”你必须先算出每门课的平均分再去更新成绩表。这种场景子查询直接嵌入UPDATE语句里写起来非常顺手。所以我的判断是子查询不是“能用但尽量少用”的东西而是一个独立的思考层级。它在聚合比较、集合判断、派生表加工这三个方向上尤其有优势。2. 核心语法拆解从标量子查询到相关子查询2.1 标量子查询返回一行一列当普通值用标量子查询是入门最简单、也最容易踩坑的一种。它返回且只能返回一行一列比如SELECT student_id, name, (SELECT MAX(score) FROM score) AS max_score FROM student;这条SQL会在每个学生后面都带上一列全校最高分虽然这个例子业务意义不大但它很好展示了标量子查询的用法子查询的结果被当成一个字段值带在结果集里。更实际一点的用法是在WHERE里做比较。比如查“成绩高于全校平均分的学生记录”SELECT * FROM score WHERE score (SELECT AVG(score) FROM score);这里有个非常经典的报错如果子查询返回了多行MySQL会直接抛错错误码是1242提示信息是“Subquery returns more than 1 row”。很多人第一次遇到都懵了明明逻辑没问题为什么报错因为标量子查询的使用场景是“代替一个值”一个值只能有一个多行就违反了基本语义。还有一种情况是子查询结果为空。这个时候标量子查询不会报错而是返回NULL。于是会出现一个隐蔽问题外部用不等于比较时NULL参与比较会导致结果过滤不干净这个在第5章里会详细说。2.2 IN与行子查询把子查询结果当作集合当子查询返回一列多行时最常见的使用方式就是配合IN操作符。比如SELECT name FROM student WHERE student_id IN ( SELECT student_id FROM score WHERE course_id 1 );这条SQL先找出选了课程1的学生ID集合再从student表里把这些人查出来。它的执行逻辑很符合人的直觉“先找到条件集合再根据集合过滤”。MySQL还支持一种行子查询的写法把多个字段当作一个“组合值”来比较。比如我想查“每门课程的最高分记录”可以用SELECT * FROM score WHERE (course_id, score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );这里(course_id, score)是一个行构造器子查询返回每一门课的课程号和最高分外部用两列组合去匹配。这个方法可以解决“每组最大值”这类经典问题代码比手动写相关子查询简洁很多也不容易漏掉并列最高分的记录。用IN的时候要注意如果子查询结果集中包含NULL值IN本身还是能正常工作的——只要匹配到任意一个非NULL的值就会返回TRUE。但如果你用的是NOT IN一旦子查询结果里有NULL结果集大概率会变成“空集”。这个坑放到第5章集中讲。2.3 EXISTS与相关子查询内外联动的关键EXISTS是子查询体系里最难理解、也最重要的部分。它通常配合“相关子查询”一起出现所谓相关是指内层子查询引用了外层查询的字段内外两层产生联动。比如查询“至少选过一门课的学生”SELECT * FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id );这里的sc.student_id s.student_id就是关联条件。每取一个学生MySQL就会去score表里查一下这个学生有没有成绩记录。只要存在至少一条记录EXISTS就返回真这个学生就会被选中。EXISTS只关心“内层有没有记录返回”并不是真的要把记录取出来所以内层写SELECT 1、SELECT *甚至SELECT NULL都一样。它不参与NULL值的比较逻辑所以NOT EXISTS在处理“不存在”类需求时比NOT IN可靠得多。不过相关子查询是有性能代价的。如果外层表有10万行内层查询理论上就可能执行10万次这在MySQL里叫“逐行执行”。一旦内层查询少了索引性能会迅速恶化。很多“子查询慢”的传闻其实都来自相关子查询被滥用。这章先记住它的特点和风险第4章我专门讲优化办法。2.4 FROM子句里的派生表查询套查询的“临时表”子查询放在FROM子句后面就是派生表。它相当于在SQL执行过程中临时生成一张表供外层继续查询。派生表必须起别名否则MySQL会直接报错。SELECT course_id, avg_score FROM ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) AS t WHERE avg_score 80;这个例子里内层先算出每门课的平均分外层再筛出平均分大于80的课程。虽然这个特定需求用HAVING也能写但一旦你要对聚合结果做二次计算比如“过滤出平均分高于所有课程平均分平均值”的课程派生表几乎就是唯一直观的解法。MySQL 8.0对派生表做了不少优化比如derived_merge即优化器尝试把派生表和外部查询合并起来执行避免真正物化成一张临时表。但能不能合并成功要看外层查询的用法如果派生表里用了聚合函数、DISTINCT、GROUP BY等操作还是可能会物化成临时表。所以写派生表的时候不要理所当然认为它是“零成本的”也要看执行计划。3. 实战学生课程成绩库把子查询用到极致3.1 建表与造数一个可直接复现的成绩库光讲语法记不住我自己学SQL的时候最有效的办法就是建一套最简单的学生课程成绩库反复折腾。下面这个结构很基础但足够覆盖80%的子查询场景。CREATE TABLE student ( student_id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, class_name VARCHAR(20) ); CREATE TABLE course ( course_id INT PRIMARY KEY, course_name VARCHAR(40) NOT NULL, credit DECIMAL(2,1) ); CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,1), remark VARCHAR(20), KEY idx_student (student_id), KEY idx_course (course_id), KEY idx_score (score) );注意我给score表加了三个索引。这不是为了示范而示范而是因为后续几乎所有子查询案例都会在student_id、course_id、score这三个字段上做过滤和关联没有索引的话再好的查询写法也白搭。插入几条简单数据INSERT INTO student (student_id, name, class_name) VALUES (1, 张三, 一班), (2, 李四, 一班), (3, 王五, 二班), (4, 赵六, 二班); INSERT INTO course (course_id, course_name, credit) VALUES (1, 数据库, 3.0), (2, 操作系统, 2.5), (3, 计算机网络, 2.0); INSERT INTO score (student_id, course_id, score) VALUES (1, 1, 92.0), (1, 2, 85.0), (2, 1, 86.0), (2, 3, 78.0), (3, 2, 95.0), (3, 3, 88.0);这里有意让赵六没有任何成绩后面查“没有选课的学生”时正好派上用场。3.2 查询每门课的最高分记录IN和的差别先看一个非常经典的需求找出每门课程的最高分记录并把学生姓名等详情一起带出来。最直观的关联子查询写法是这样的SELECT * FROM score s WHERE score ( SELECT MAX(s2.score) FROM score s2 WHERE s2.course_id s.course_id );如果每门课只有一个最高分这种写法是对的。但实际数据里很可能两个学生正好考了一样的最高分这时候内层子查询就会返回两行外层用等于号比较直接报1242错误。更稳妥的写法是用行构造器配合INSELECT s.*, st.name FROM score s JOIN student st ON s.student_id st.student_id WHERE (s.course_id, s.score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );这条SQL先把每个课程的最高分查出来再回到score表里用多字段匹配能够同时取回并列最高分的所有人。我在实际项目里就遇到过“线上一门课出现并列最高分旧SQL漏数”的事故所以涉及这类统计需求时默认用IN比用安全。3.3 查询没有选课的学生NOT EXISTS 慎用 NOT IN这个需求直接对应“赵六”。不少人的第一反应是SELECT * FROM student WHERE student_id NOT IN ( SELECT student_id FROM score );看起来没问题student_id是的子查询字段而且score表里没有NULL所以在这个演示库里能跑出正确结果。但一旦score表的student_id字段允许为空或者某行数据意外写入了NULL这条SQL的结果就会变成空集。原因我放到5.2详细讲现在先给出更可靠的写法SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id );用NOT EXISTS逻辑就变成本质上的“不存在任何一条成绩记录才选中这个学生”。它不关心子查询里有没有NULL也不用担心优化器的特殊行为结果始终符合预期。写“不存在”类需求时我个人现在默认用NOT EXISTS除非能确认子查询字段绝对没有NULL才偶尔用NOT IN。3.4 UPDATE里的子查询同一张表的更新陷阱子查询不只在SELECT里常见UPDATE里也经常用。比如我想把“成绩低于全校平均分”的记录标记为待补考UPDATE score SET remark 待补考 WHERE score (SELECT AVG(score) FROM score);这条SQL在MySQL里会直接报错错误码是1093提示不能在同一张表上一边更新一边查。MySQL不允许UPDATE目标表和子查询引用的表是同一张表。解决办法是包一层派生表让MySQL把它当成一张“临时结果表”而不是原始目标表UPDATE score SET remark 待补考 WHERE score ( SELECT avg_score FROM ( SELECT AVG(score) AS avg_score FROM score ) AS t );如果是更复杂的“低于本课程平均分”最好直接用UPDATE JOIN的方式UPDATE score s JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) c ON s.course_id c.course_id SET s.remark 待补考 WHERE s.score c.avg_score;这种写法先构造各课程平均分的派生表再和成绩表做关联更新既绕过了同一张表的限制又保证了逻辑清晰。记住一个判断原则更新时如果子查询要引用目标表的数据优先考虑包一层派生表或者直接用JOIN更新。3.5 DELETE里的子查询安全删除的正确姿势删除操作里的子查询和更新类似。比如删除没有选课的学生DELETE FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id student.student_id );删除前务必先用SELECT验证一遍结果。我处理线上数据时的习惯是先把DELETE临时改成SELECTSELECT * FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id student.student_id );确认结果集没问题再改成DELETE执行而且通常放到事务里START TRANSACTION; DELETE FROM student WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id student.student_id ); -- 确认影响行数无误后提交 COMMIT;大表删除时要注意锁表问题DML会长期持有锁尽量避开业务高峰期分批删除。4. 性能对比与优化子查询真的慢吗4.1 子查询性能的争议来源优化器的执行策略很多老DBA一听到子查询就皱眉说“子查询慢能用JOIN就别用子查询”。这个说法在MySQL 5.5时代基本成立因为早期优化器对子查询的处理方式非常粗糙尤其是相关子查询经常会逐行执行外层有多少行内层就执行多少次。MySQL 5.6之后引入了大量子查询优化策略包括半连接转换、物化、Exists策略等。到了MySQL 8.0优化器已经相当成熟IN子查询在很多情况下会被自动改写成半连接执行性能并不比JOIN差甚至在某些场景更快。所以现在再拿“子查询等于慢”当结论已经过时了。正确做法是看执行计划。子查询本身没有原罪真正的问题是用户在写相关子查询时没有建立合适的索引或者三层嵌套把自己的逻辑搞得复杂到优化器也无从下手。4.2 用EXPLAIN判断子查询的执行方式EXPLAIN是分析SQL性能的第一工具。拿刚才“查询每门课最高分”的语句举例EXPLAIN SELECT s.*, st.name FROM score s JOIN student st ON s.student_id st.student_id WHERE (s.course_id, s.score) IN ( SELECT course_id, MAX(score) FROM score GROUP BY course_id );你会看到执行计划里有很多select_type值常见的有select_type含义SIMPLE普通查询没有子查询和UNIONPRIMARY最外层的查询SUBQUERY非相关子查询先算一次然后外层使用DEPENDENT SUBQUERY相关子查询外层每行都执行一次DERIVEDFROM中的派生表MATERIALIZED子查询结果被物化成临时表看到DEPENDENT SUBQUERY就要格外敏感如果外层记录多、内层关联字段没有索引基本可以断定这条SQL会慢。解决办法通常是在内层查询的关联字段上加索引。比如相关子查询的内层是WHERE sc.student_id s.student_id那score.student_id就必须有索引。演示库里我已经建了所以即便出现相关子查询性能也不会差太多。实际项目里很多人建表时漏了这类索引才是“子查询慢”的真正原因。4.3 子查询、JOIN、EXISTS怎么选一张表讲清楚到底哪种写法更好我的判断标准是先看语义再看执行计划。不搞“一刀切”。场景推荐写法说明WHERE里和聚合值比较标量子查询无法用JOIN表达必须先把聚合算出来过滤主表记录不关心重复EXISTS或INEXISTS避免了NULL问题语义更稳需要同时取关联表的字段JOIN子查询无法直接输出另一张表的字段查询“不存在”类记录NOT EXISTSNOT IN遇NULL会翻车谨慎使用对聚合结果做二次过滤派生表把中间结果包装成临时表每组分组的Top N窗口函数或相关子查询MySQL 8.0优先用窗口函数我自己写SQL的顺序是先保证逻辑正确用最容易理解的子查询把结果跑出来然后打开EXPLAIN看执行计划如果发现相关子查询在逐行扫描再考虑改写。比如把相关子查询改成JOIN或者把IN改成EXISTS然后对比两条SQL的执行计划用数据说话而不是“听别人说”。5. 常见问题与排查技巧实录5.1 Subquery returns more than 1 row最常见的1242报错这个报错高频到几乎所有写子查询的人都会遇到。错误信息ERROR 1242 (21000): Subquery returns more than 1 row原因很简单标量子查询或等于号比较时内层查出了多行数据。解决办法分情况如果业务上只需要任意一个值内层加LIMIT 1。如果是“每组最大值”这类需求用行构造器IN。如果原本就用错了逻辑应该改为EXISTS或IN。排查时可以先把子查询单独拎出来跑一遍看返回多少行往往一眼就能发现问题。5.2 NOT IN遇上NULL为什么查不到任何数据这是SQL里最著名的隐蔽坑之一。假设score表的student_id列允许为NULL并且确实有NULL值SELECT * FROM student WHERE student_id NOT IN (1, 2, NULL);这条SQL的结果是空集。原因是SQL的三值逻辑student_id无论等于哪个值与NULL比较时结果都是NULL而NULL在WHERE里会被当作不成立所以一行都查不出来。解决办法就是用NOT EXISTS替代。这也是MySQL面试题里高频出现的一个点面试官常拿它考候选人到底是否理解NULL语义。5.3 子查询里的ORDER BY和LIMIT容易被忽视的坑子查询里用ORDER BY排序再交给外层使用结果顺序不一定保得住。经典问题“取每个班级分数最高的学生之一”SELECT * FROM student s WHERE student_id IN ( SELECT student_id FROM score WHERE score GROUP BY class_name ORDER BY score DESC LIMIT 1 );这个写法本身问题很多因为子查询里ORDER BY和LIMIT与外层IN组合时语义可能有变化而且MySQL优化器可能改写掉内层顺序。更可靠的做法是外层排序SELECT * FROM score WHERE student_id IN (...) ORDER BY score DESC;派生表里的ORDER BY也不保证外层最终结果的顺序外层需要排序就必须在外层写ORDER BY。别把“内层排好序外层直接用”当作默认行为。5.4 优化器改写导致的“惊喜”结果和预期不一致MySQL优化器会在不影响语义的情况下重写SQL比如把IN子查询改写成半连接。半连接在去重逻辑上和普通JOIN不同有时候你以为会去重实际却没去有时候你以为不去重结果反而去重了。遇到“结果和预期不一致”时除了检查业务逻辑还要看两条线索一是EXPLAIN的执行计划看select_type发生了什么改变二是打开optimizer trace看优化器到底做了哪些改写SET optimizer_trace enabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;这个工具能直接输出优化器的决策过程非常直观。排查“被改写”类问题时比瞎猜高效得多。MySQL 8.0还支持NO_SEMIJOIN这类优化器hint可以强制关闭某种策略但生产环境不建议滥用更多是用来验证问题。写到这里子查询的日常用法、典型实战和关键坑基本都过了一遍。我个人每次写SQL的习惯是先把业务逻辑用最直白的方式写出来哪怕是三层嵌套跑通拿到正确结果再打开EXPLAIN看执行计划最后才考虑要不要改成JOIN、EXISTS或者用窗口函数。子查询本身不是洪水猛兽不了解原理就乱优化才是真正的灾难。最后再分享一个小技巧如果你要反复调试一条带子查询的SQL记得先给相关子查询里的关联列建索引比如今天这个例子里的score表student_id、course_id、score三个字段建好索引90%的子查询慢问题都能缓解。遇到奇怪结果优先怀疑NULL其次怀疑优化器改写按这两条思路排查大多数坑都能快速定位。