企业官网建设流程全解析

MySQL这个系列写到第22篇，正好轮到子查询（sub query）这个绕不开的重点。我见过太多人，刚开始觉得嵌套查询很神奇，写几条就觉得自己会了，结果一上生产环境，被一条慢得离谱的子查询教做人。子查询本质上就是“查询里的查询”，它解决的是没法用一个简单SELECT直接拿到的复杂条件或者中间结果。结合我这些年做过的报表、订单系统、成绩管理系统，可以负责任地说：会用子查询只是及格，会改子查询才是进阶，会躲子查询的坑才算真正读懂MySQL。这篇就从概念、分类、典型场景、性能优化到高频面试和踩坑记录，一次讲透，适合正在学SQL的初学者，也适合写了好几年SQL但没系统整理过子查询细节的开发。

1. 子查询是什么：先解决“为什么要嵌套”

1.1 一句大白话理解子查询执行顺序

子查询说白了就是套娃。外层查询是哥哥，内层查询是弟弟，MySQL执行的时候，弟弟先算完，把结果交给哥哥用。这个顺序很关键，很多人写子查询出错，就是没搞清楚“先内后外”这个基本逻辑。

举个例子，你想查“成绩高于全班平均分的学生”。平均分怎么来？得先算一个聚合，再拿每个学生的分数跟这个聚合值比。你不写子查询，就得先跑一条SELECT AVG(score)，把结果记下来，再粘到第二条SQL里。子查询就是替你把这两步合并了：

SELECT id, name, score FROM student WHERE score > (SELECT AVG(score) FROM student);

内层那个SELECT AVG(score)先跑，得出比如82.5，外层再拿每个学生的score去跟82.5比较。整个过程像你点外卖：你先下楼拿外卖（内层），拿上来才能吃（外层），不能先开吃再下楼。

MySQL执行时，大多数子查询会被优化器做等价改写（后面讲性能时会细说），但你在脑子里用“先内后外”这套逻辑去理解结果，基本不会跑偏。

1.2 子查询的四种形态：标量、列、行、表

子查询的分类不是考试用不上的死知识，而是你写SQL时的“选型依据”。看到需求，先判断要返回的是一行一列、一列多行、一行多列，还是多行多列，再决定用哪种写法、放哪个位置。

标量子查询用得最多，但也最容易踩“多行返回”的坑。只要内层查出两行以上，MySQL直接报错：Subquery returns more than 1 row。平时写代码时我习惯先确认子查询的聚合或主键条件能保证唯一性，再放到等值比较的位置，免得测试数据撞上限。

行子查询用得少，但也有妙用。比如同时查“语文和数学成绩都等于最高分的人”：

SELECT id, name FROM student WHERE (chinese, math) = (SELECT MAX(chinese), MAX(math) FROM student);

这条SQL一次把两科最高分拿出来，跟每行学生的两科成绩做行比较，语法简洁，逻辑也清楚。

1.3 建一套贯穿全文的示例表

讲了一堆概念，得有个能上手跑的场景。我重新建了一套“学生-课程-成绩”三张表，这是做成绩系统最常见的模型，也贴合学MySQL时最常用到的练习数据：

CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(20), class_id INT ); CREATE TABLE course ( id INT PRIMARY KEY, course_name VARCHAR(50) ); CREATE TABLE score ( student_id INT, course_id INT, score DECIMAL(5,2), PRIMARY KEY (student_id, course_id) ); INSERT INTO student VALUES (1, '张三', 101), (2, '李四', 101), (3, '王五', 102), (4, '赵六', 102), (5, '孙七', 103); INSERT INTO course VALUES (101, '语文'), (102, '数学'), (103, '英语'); INSERT INTO score VALUES (1, 101, 88), (1, 102, 92), (2, 101, 75), (2, 102, 68), (3, 101, 95), (3, 103, 80), (4, 102, 55), (5, 103, 60);

后文所有例子都基于这三张表。你自己练习时，要么直接照抄，要么用自己业务表替换表名，SQL套路是通用的。

2. 五大经典场景实操：从WHERE到FROM逐个拆解

2.1 WHERE中的比较运算与标量子查询

先看查询“分数高于语文平均分的学生姓名”：

SELECT s.name, sc.score FROM student s JOIN score sc ON s.id = sc.student_id WHERE sc.course_id = 101 AND sc.score > (SELECT AVG(score) FROM score WHERE course_id = 101);

这里内层只返回一个值，就是语文平均分。很多人会写出不带course_id = 101的内层查询，那就是所有课程的平均分了，等值比较的语义就错了。记住：标量子查询的过滤条件必须跟你外层要求对齐，内外表关系一定要心里有数。

再看一个HAVING里用子查询的：查“平均分超过总平均分的学生”：

SELECT student_id, AVG(score) AS avg_score FROM score GROUP BY student_id HAVING avg_score > (SELECT AVG(score) FROM score);

是的，HAVING里也能放子查询。GROUP BY先按学生分组，算出每个人的平均分，再跟全局平均比，用HAVING过滤组。

2.2 IN与NOT IN：列子查询最典型的战场

“查选修了课程ID为103（英语）的学生姓名”，用IN最直接：

SELECT id, name FROM student WHERE id IN ( SELECT student_id FROM score WHERE course_id = 103 );

内层返回一堆student_id，外层拿学生的id去跟这堆值匹配，有任何一个相等就算命中。这个场景也能用JOIN写，但IN的语义是“存在即可”，天然去重，不会因为一个学生选了两门课出现重复行。这是IN比起JOIN最容易踩的点：JOIN可能膨胀行数，IN不会。

NOT IN则用来查反集，比如“没选过任何课程的学生”：

SELECT id, name FROM student WHERE id NOT IN ( SELECT student_id FROM score );

这个例子在第4章会有个大坑：如果子查询的结果里出现NULL，NOT IN整个查询直接给你返回空集。这是个经典到不能再经典的SQL陷阱，务必重视。

2.3 EXISTS与NOT EXISTS：存在性判断换种写法

EXISTS跟IN不一样，它不关心子查询返回什么内容，只关心子查询有没有返回行。子查询第一条记录返回，EXISTS就成立，然后就停止继续扫了，所以有些场景EXISTS比IN快得多。

“查有成绩记录的学生”，用EXISTS写：

SELECT id, name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.id );

这里子查询里的s.id是外层传进来的，这种写法叫关联子查询。每扫描一行外层student，就把该行的id传给内层，内层去score表查有没有对应记录。SELECT 1是习惯写法，因为EXISTS只看有没有行，选什么列都无所谓，写1省得MySQL多做无谓的取列操作。

反过来说“查没有任何成绩记录的学生”，用NOT EXISTS：

SELECT id, name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.id );

在“判断某个东西是否存在”这件事上，我个人的排序是EXISTS/NOT EXISTS优先于IN/NOT IN，前者没有NULL陷阱，而且容易写索引友好的半连接（semi-join）。

2.4 FROM子查询：把中间结果当成一张临时表

FROM后面也能放子查询，查出来的结果会被当成一张临时表用，所以必须起别名，否则MySQL直接报错：Every derived table must have its own alias。

经典场景：查每门课、每个学生的分数排名中用到的“课程平均分”。先按课程聚合出平均分，再跟成绩表关联：

SELECT sc.student_id, sc.course_id, sc.score, t.avg_score FROM score sc JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) t ON sc.course_id = t.course_id;

内层子查询先把每门课的平均分算出来，形成一个只有course_id和avg_score两列的临时结果集，外层JOIN上去，两边按课程ID对上。做报表、做看板时这种写法非常常见，比一遍遍重复聚合高效得多，也避免了在SELECT里写一堆冗余子查询导致SQL长到没法看。

MySQL 8.0对派生表有优化，但默认情况下有个怪脾气：如果派生表里的ORDER BY没有LIMIT配合，可能会被优化器直接忽略（后面第4章细说）。所以你在子查询里写ORDER BY时，别指望外层一定认账。

2.5 SELECT子句里的标量子查询：顺手带出统计数据

SELECT后面也能放子查询，用于在每一行上展示一个跟他行相关的统计值。查“学生成绩明细，并带出该学生所有课程的平均分”：

SELECT s.name, sc.course_id, sc.score, (SELECT AVG(score) FROM score sc2 WHERE sc2.student_id = s.id) AS stu_avg FROM student s JOIN score sc ON s.id = sc.student_id;

内层根据外层当前行的学生ID，去统计这个学生自己的平均分，每行都会跑一次内层查询。这种写法简洁，能少写一个JOIN，但代价是行数多时性能感人，因为每一行都要执行一次子查询。数据量大时，我优先写成JOIN加GROUP BY，把子查询的重复计算变成一次聚合。

3. 性能与优化：子查询为什么慢，怎么改快

3.1 三个让子查询变慢的常见原因

很多人一听到子查询就摇头，说性能不行。其实子查询本身不是原罪，慢通常是因为这三个问题：

第一，关联子查询逐行执行。像上面EXISTS那种写法，外层表扫出多少行，内层就执行多少次，如果外层表有十万行，内层查询就执行十万次，哪怕每次都走索引，累计开销也不小。这个场景下，不考虑数据进行改写，性能很容易崩。

第二，派生表可能产生物化（Materialization）。MySQL 8.0之前，FROM子查询里的派生表经常被当作临时表物化出来，如果子查询里没有合适索引，后期跟外层表JOIN时就是全表对全表，慢得离谱。

第三，子查询中用不到索引。比如内层子查询对索引列做了函数处理，或者外层拿索引列跟一个无法确定的子查询结果比较，MySQL放弃索引，走了全表扫描。

3.2 IN、EXISTS、JOIN到底怎么选

网上关于IN和EXISTS哪个快的争论能吵几百楼，其实结论早就不该再是“小表驱动大表”一句话能概括的了。MySQL 5.6之后对IN子查询做了半连接优化，会把IN自动改写成半连接（semi-join），执行计划里能看到类似“Using where; Start temporary; End temporary”的标志。所以很多场景下，IN并没有你想的那么慢，反而EXISTS写法的关联子查询未必最优。

我整理了几张表的选型参考：

实操上我的习惯是：如果只是判断存在性，优先EXISTS；如果子查询的结果集真的非常小（比如几十条），IN也很稳；如果需要取子查询里的其他字段参与外层计算，那就干脆JOIN，别绕来绕去。拿数据实测永远比背口诀靠谱，每条SQL都丢EXPLAIN看一下，判断依据里有没有走出索引、有没有Using temporary、有没有Using filesort，这些标志都比网上争论有说服力。

3.3 关联子查询改写为JOIN的经典手法

入门时会写关联子查询，进阶就得会改写。拿“查每门课成绩高于该课程平均分的学生”来说，很自然的写法是：

SELECT student_id, course_id, score FROM score sc WHERE score > ( SELECT AVG(score) FROM score sc2 WHERE sc2.course_id = sc.course_id );

这个关联子查询每行执行一次内层聚合，效率不高。改为JOIN写法：

SELECT sc.student_id, sc.course_id, sc.score FROM score sc JOIN ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) t ON sc.course_id = t.course_id WHERE sc.score > t.avg_score;

改造的思路很清晰：内层先聚合一次得到每门课平均分，作为派生表，再跟成绩表关联，关联后边上的平均分是一行里已经算好的值，直接比较就行，不必每行重新算。这种改写对数据量大的表提升非常明显，我见过从几十秒优化到一两秒的真实案例。

改JOIN时要注意一个副作用：JOIN可能让行数变多。好在这个场景是一对一关联，平均分对每门课只有一条，不会膨胀。换成一对多场景，你就得考虑去重或者聚合后再关联了。

3.4 EXPLAIN里那些和子查询有关的信号

写SQL优化，光靠猜不行，得学会看执行计划。EXPLAIN SELECT的每条记录里跟子查询密切相关的有这几个：

type列：对子查询涉及的表，如果出现ALL，基本是全表扫描，子查询若能改成ref或eq_ref，说明索引生效了。子查询中where条件的列没索引，你建上索引，很多慢查询马上就好转。

Extra列：出现Using temporary，说明执行过程中建了临时表，子查询里的GROUP BY/DISTINCT常见，临时表数据量大时会影响性能；出现Using filesort，说明排序用了外部排序而不是索引顺序，通常意味着ORDER BY的列没有索引支撑；出现Start temporary/End temporary，是半连接优化的标志，说明IN子查询被优化成了半连接，这是好事，别看到temporary就以为出问题了。

还有跟“Subquery”字样相关的显示，比如Materialize，说明子查询被物化成了临时表，物化有成本，但有时候物化后能复用临时表，比反复执行强。遇到慢子查询，我的固定操作是先EXPLAIN，看type和Extra，判断是缺索引还是写法问题，然后逐项改进。

4. 高频疑问与踩坑手册：这些错我全犯过

4.1 UPDATE/DELETE子查询的同表限制

搜索热词里“mysql中更新子查询”和“mysql update语法”热度一直很高，确实，这地方坑太深了。你想把所有分数低于班级平均分的人加5分，直觉写法是：

UPDATE score SET score = score + 5 WHERE score < (SELECT AVG(score) FROM score);

MySQL直接给你一句：You can't specify target table 'score' for update in FROM clause。原因很简单：你正在UPDATE score，同时又在FROM子查询里读score，MySQL怕你在更新过程中读到不一致的数据，所以直接禁止。解决办法是套一层临时表，让MySQL认为你读的是另一份数据：

UPDATE score SET score = score + 5 WHERE score < ( SELECT avg_score FROM ( SELECT AVG(score) AS avg_score FROM score ) AS tmp );

核心就是给子查询再包一层，让它变成派生表。DELETE也同理。这是子查询在DML语句里最经典的坑，面试官也爱问，务必记住。

4.2 NOT IN遇上NULL，全员消失

刚才讲NOT IN时留了个伏笔，现在展开说。查“没选过任何课程的学生”，如果score表里存在这样的记录：student_id本身是NULL，后果会很可怕：

SELECT id, name FROM student WHERE id NOT IN ( SELECT student_id FROM score );

假如内层子查询的结果是{1, 2, NULL}，NOT IN的判断逻辑是：只要id不等于1且不等于2且不等于NULL，才算命中。但跟NULL比较的结果不是TRUE也不是FALSE，而是UNKNOWN，所以整条记录的判断变成UNKNOWN，被过滤掉。最终效果就是：只要子查询结果里出现一个NULL，整个NOT IN返回的结果集就是空的，好像所有学生都选过课一样。

彻底解决这个坑的方法，一是建表时把关联字段都加上NOT NULL约束，二是在SQL层面用NOT EXISTS代替NOT IN：

SELECT id, name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id = s.id );

写经历以后，我养成了个习惯：看见NOT IN必警惕，第一反应就是子查询里有没有NULL的可能。

4.3 子查询里的ORDER BY为什么经常“失灵”

写这句时你可能踩过：用FROM子查询当派生表，想先排序再取某个分组的第一条，结果发现排序根本没生效。比如：

SELECT * FROM ( SELECT * FROM score ORDER BY score DESC ) t GROUP BY course_id;

你希望每组取最高分那条，但MySQL优化器很可能把派生表的ORDER BY当成没用的东西，在合并（merge）时直接忽略。MySQL的逻辑是：如果order by不影响外层查询的最终结果，优化器就有权扔掉它。要让排序保留，一个土办法是在子查询里加LIMIT，让它不只是一个逻辑排序，而是一个真正要出结果的查询：

SELECT * FROM ( SELECT * FROM score ORDER BY score DESC LIMIT 100 ) t GROUP BY course_id;

但加了LIMIT又会有“只取前100条”的副作用。更正规的方案是用窗口函数（MySQL 8.0及以上）：

SELECT student_id, course_id, score FROM ( SELECT student_id, course_id, score, ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC) AS rn FROM score ) t WHERE rn = 1;

这彻底绕开了“子查询排序到底保不保留”的模糊地带，逻辑一眼到底。

4.4 字段名撞上保留字：反引号救你命

热词里有“mysql表中字段为关键字”，这也是新手常踩的雷。如果你建表时把一列命名为order、group、desc这类关键字，写SQL时不加反引号，MySQL直接报语法错误。比如：

SELECT * FROM orders WHERE order = 1;

MySQL看到order就懵了，这是关键字还是列名？解决办法是反引号：

SELECT * FROM orders WHERE `order` = 1;

子查询里也一样，无论外层还是内层用到了这种关键字列，都记得给它套反引号。最省事的方案是建表时避免用关键字当字段名，但有些老表确实改不了，那就只能在SQL里处处小心。顺便说一句，写子查询时给所有表起一个简短的别名，比如s、sc、t，不但能少打字，很多时候也能减少MySQL解析歧义。

4.5 子查询常见问题速查表

把经常被问到的点整理成一张表，方便你以后直接查：

这张表里的每个坑我都实打实踩过，不是从文档里抄来的。特别是那个UPDATE同表限制，当年在给一个成绩系统写补分脚本时被这条报错卡了半小时，包一层临时表立刻通畅，那感觉记忆犹新。

4.6 子查询在存储过程与报表里的扩展用法

本来没打算讲这个，但热词里出现了“mysql存储过程”，技能树可以顺带点一句。子查询在存储过程里最常用的地方是给变量赋值，比如：

DECLARE avg_score DECIMAL(5,2); SELECT AVG(score) INTO avg_score FROM score;

这种用法本质上是把标量查询结果写进变量，逻辑跟子查询一致。报表场景里，一个主报表对应多条统计子查询也是常态，比如“订单总金额、本月新增用户数、昨日活跃数”拼在一行里，用一条SELECT加三个标量子查询就能搞定，比连三次数据库接口强得多。但要注意，这种写法在数据量大时性能会下降，可以考虑用JOIN加CASE WHEN把多次扫描压缩成一次。

收尾之前，分享一次真实调优经历

写到最后，不知不觉又想多说一句自己的事。去年帮人优化过一个统计报表，三张表关联，条件里嵌了五六个IN子查询，都是判断“是否存在某类订单”“是否命中某标签”之类的逻辑。第一次跑，冷启动愣是花了十几秒，看EXPLAIN，内层子查询全表扫描，外层也不走索引。我当时做了两件事：第一，给子查询涉及的关联字段全建了索引；第二，把纯存在判断的IN换成EXISTS，把需要取数据的子查询改成了JOIN。改完再跑，直接掉到几百毫秒。那条SQL我到现在还记得，优化的本质不是背几个口诀，而是看清楚每一段子查询到底在执行什么、能否复用索引、能否减少扫描次数。

子查询这个东西，表面上是语法题，背下来就完事；实际上是思维题，你得时刻知道哪条数据的计算发生在哪一层。建议你学的时候，凡是看到一条子查询，都强迫自己先口算一遍执行顺序：“内层先出什么结果？外层拿这个结果怎么用？如果数据量大，这段能不能写成JOIN？”这样练上几十条，子查询就不是考试题了，而是你工具箱里最顺手的螺丝刀。