说个我早年写报表时遇到的真实需求：把每个部门工资最高的人捞出来。第一次写的时候我直接GROUP BY dept_id然后MAX(salary)，以为一句SQL收工，结果一看结果集，部门是出来了，可人员信息完全对不上——分组之后你只能拿到聚合值，拿不到那一行的其他字段。后来我才意识到，这种“先算出一个集合，再拿这个集合回去过滤明细”的场景，正是 MySQL SQL子查询最典型的用武之地。MySQL子查询这技术，说难不难，但真到了复杂统计、报表、数据清洗场景，用得好能省一半代码，用不好能把数据库拖垮。这篇我不打算照着官方文档念概念，而是按我实际使用的顺序，把子查询的分类、高频写法、性能优化、以及我踩过的几个真坑完整讲一遍。适合两种人：刚学SQL想一次把子查询体系搞明白的人，以及写了不少SQL但总觉得性能说不清、只会死记“IN慢EXISTS快”这种结论的人。1. 子查询的本质：MySQL到底怎么执行一条嵌套查询1.1 先分清三个维度：位置、形态、关联性子查询的定义其实一句话就能讲完：一条完整的SELECT语句，嵌套在另一条SQL语句的某个表达式里，这个内层SELECT就是子查询。外层不一定是SELECT，也可以是INSERT、UPDATE、DELETE，但日常开发里90%的战场都集中在SELECT。理解子查询，我建议从三个维度去拆，而不是死记“子查询有哪几种类型”这种列表。第一个维度是放的位置。子查询可以出现在WHERE后面做过滤条件，出现在FROM后面当临时表，出现在SELECT的投影列表里补一列，出现在HAVING里过滤分组结果，甚至出现在JOIN的ON条件里。位置不同，它的作用完全不同——WHERE子查询是“条件判定”，FROM子查询是“结果集复用”，SELECT子查询是“标量补列”。第二个维度是返回的形态。返回一个值的是标量子查询，返回一列多行的是列子查询，返回一行多列的是行子查询，返回多行多列的就是表子查询，也就是我们常说的派生表。形态直接决定了外层能不能用它、怎么用它。第三个维度是关联性。这是最影响性能的一个维度，也是不少人一开始忽略的：如果内层子查询不引用外层表的任何列，它叫非关联子查询；如果内层引用了外层表的列，比如WHERE o.user_id = u.user_id，它叫关联子查询。这俩在MySQL里的执行逻辑完全不同，下面展开说。1.2 非关联子查询：先查内层，只跑一次非关联子查询的执行逻辑最简单：MySQL先执行内层子查询，拿到完整结果集，再执行外层查询。内层只执行一次，结果可以被外层任意引用。比如这个需求：找出工资高于全公司平均工资的员工。SELECT employee_id, employee_name, salary FROM employee WHERE salary > ( SELECT AVG(salary) FROM employee );这里MySQL会先执行AVG(salary)，算出一个标量，比如8500，然后用这个8500去外层做普通过滤。整个过程非常直观，内层只跑一次，性能瓶颈只在于外层查询本身能不能走索引。很多人第一次接触子查询时容易懵，其实你可以把它理解成两步手写SQL：先单独跑内层，看到结果，比如“平均工资是8500”，然后手写一条WHERE salary > 8500的SQL。非关联子查询就是让MySQL自动帮你完成这件事，区别只是它在一条语句里完成，不用你手动复制结果。1.3 关联子查询：外层一行，内层跟着跑一遍关联子查询就完全不一样了。它会在外层查询的每一行上，把这一行的值带入内层子查询去执行。如果外层有1万行，内层子查询理论上要执行1万次，这就是俗称的N+1问题。看这个例子：查每个员工的工资，同时显示他所在部门的最高工资。SELECT e.employee_id, e.employee_name, e.dept_id, (SELECT MAX(e2.salary) FROM employee e2 WHERE e2.dept_id = e.dept_id ) AS dept_max_salary FROM employee e;注意内层的e2.dept_id = e.dept_id里的e.dept_id，它引用的是外层员工表的列。MySQL处理这条SQL时，相当于外层每读出一行员工记录，就拿这个员工的dept_id去内层查一遍部门最高工资，然后把结果放到这一行的dept_max_salary列上。如果你在这个外层的员工表上有合适的索引，内层查询可能很快；如果没索引，每次内层都是全表扫描，外层1万行就是1万次全表扫描，查询性能会差到让你怀疑人生。这也是判断一条SQL“会不会慢”的最直观依据：看到关联子查询，第一反应就是检查内层关联字段有没有索引。1.4 分类关系一览把上面三个维度合在一起，子查询的体系就清晰了：分类维度类型返回值典型位置按结果形态标量子查询单行单列SELECT、WHERE、HAVING按结果形态列子查询单列多行WHERE + IN按结果形态行子查询单行多列WHERE + 行构造器按结果形态表子查询（派生表）多行多列FROM按关联性非关联子查询内层独立执行一次任意位置按关联性关联子查询外层每行执行一次依赖外层列这张表你不用背，真正写代码的时候心里有个概念就行：先判断是关联还是非关联，再判断返回的形态能不能满足外层语法要求，最后考虑这个位置和写法会不会触发性能问题。后面几章所有的实战写法，都是在这个框架下展开的。2. 实战高频写法：四种子查询，用之前先想清楚语义2.1 WHERE + IN：把“集合包含”交给数据库列子查询最常见的用法就是跟在IN或NOT IN后面，语义是“外层这个字段的值，是否出现在子查询返回的集合里”。例子：找出所有在数码类目下过单的用户。SELECT user_id, user_name FROM user WHERE user_id IN ( SELECT DISTINCT user_id FROM trade_order WHERE category = '数码' );很多人会纠结内层要不要写DISTINCT，其实IN的结果集本身有去重语义，写不写对最终结果没影响。我自己习惯写DISTINCT，主要是为了让自己读SQL的时候知道这个集合的语义是“用户ID集合”，而不是“订单集合”。有一点要注意：IN后面接的子查询返回结果集如果很大，MySQL会把整个结果集加载进临时表参与判断，所以子查询里务必只返回需要的外层匹配字段，不要习惯性地SELECT *。返回字段越大，临时表开销越大。2.2 EXISTS：只关心“有没有”，不关心“返回什么”EXISTS是另一类高频子查询，语义是“判断是否存在满足条件的记录”。它和IN最大的区别是：EXISTS不关心内层返回什么列，只关心内层是否返回了行。例子：找出所有下过金额超过1000元订单的客户。SELECT c.customer_id, c.customer_name FROM customer c WHERE EXISTS ( SELECT 1 FROM trade_order o WHERE o.customer_id = c.customer_id AND o.amount > 1000 );这里内层写了SELECT 1，纯粹是习惯，优化器会忽略SELECT的字段列表，你写SELECT *也一样。EXISTS真正的执行语义是：只要找到一条满足o.customer_id = c.customer_id AND o.amount > 1000的记录，就立刻返回真，不再继续往下找，所以它很适合做“存在性判断”。在业务层面，IN和EXISTS的区别可以这么理解：IN是“我的值在不在你那个集合里”，EXISTS是“你那里能不能找到跟我匹配的行”。大部分场景两者可以互换，但语义上EXISTS更接近关联查询的思维，尤其适合子查询和外层查询条件复杂的场景。2.3 FROM后面的派生表：查询结果当临时表用FROM后面的子查询叫派生表，它的核心价值是：先通过子查询算出一张中间结果集，再让外层查询像操作普通表一样去操作它。例子：统计每个部门的员工人数，并显示部门名称。SELECT d.dept_name, t.emp_count FROM ( SELECT dept_id, COUNT(*) AS emp_count FROM employee GROUP BY dept_id ) t JOIN department d ON d.id = t.dept_id;这里最关键的语法点是：派生表必须有一个别名，也就是FROM (子查询) t里的t。漏掉别名会直接报ERROR 1248 (42000): Every derived table must have its own alias。我见过很多新手在报错后一脸茫然，其实解决办法就一句话：加个字母当别名。派生表在逻辑上就是把子查询的结果物化成一张临时表，但MySQL优化器不一定会真的物化，5.7版本之后有派生表合并优化，这个放到后面第4章细讲。2.4 SELECT投影里的标量子查询：补一列而不改变行数有时候你只是想在查询结果里顺手带一个聚合字段，比如订单列表上显示用户昵称。用JOIN也能做，但JOIN可能会改变结果集行数，比如被连接的表有重复数据时，行数会被放大。这时候标量子查询就很舒服：它只负责在每行上补一个值，不参与行的筛选和扩充。SELECT o.order_id, o.order_no, o.amount, (SELECT u.nick_name FROM user u WHERE u.user_id = o.user_id ) AS user_nick FROM trade_order o;注意，这里有个硬性前提：标量子查询必须保证最多返回一行，也就是“一行一列”。如果内层返回了多行，MySQL会直接报ERROR 1242 (21000): Subquery returns more than 1 row。这个错误我在第5章会专门讲排查思路。2.5 四种写法的语义对比写法语义适合场景典型风险WHERE + IN值属于某个集合集合判断、数据筛选结果集过大时临时表开销WHERE + EXISTS存在匹配即可存在性判断、关联子查询内层关联字段无索引时会很慢FROM + 派生表中间结果参与外层计算聚合结果二次加工漏写别名、派生表无法合并SELECT + 标量子查询每行补一个值列表页补字段返回多行直接报错你先把这个语义对应关系到脑子里，写SQL的时候对着需求选一种最像的，基本就不会跑偏。3. 性能真相：IN、EXISTS、JOIN到底谁快3.1 先破除“IN永远比EXISTS慢”的旧观念网上关于IN和EXISTS谁快的讨论，绝大多数来源于老版本的MySQL和Oracle经验，很多结论放在今天的MySQL里已经不太成立了。MySQL从5.6版本开始引入了半连接优化和子查询物化优化。简单说，当一个IN子查询条件合适时，优化器会把它改写成类似JOIN的执行计划——只保留匹配行，而不会真的“先把子查询整个跑完，再逐行去外层判断”。所以你经常能看到EXPLAIN里明明写的是IN，执行计划却出现了semijoin这样的字样。这说明优化器已经把它处理成了更高效的连接方式，而不是大家想象中的“慢速逐行判断”。反过来，关联EXISTS也未必就是老老实实逐行执行，MySQL优化器同样可能在合适的条件下把它转换为半连接。所以任何“这个写法一定快、那个写法一定慢”的结论，在今天的MySQL里都不可靠。3.2 子查询物化：MySQL处理IN的关键优化MySQL 5.6之后，对于非关联的IN子查询，优化器还可能使用物化策略：把子查询的结果集一次性写入内部临时表，并且自动在临时表上建立索引，然后再和外层表做连接或匹配。这个优化的意义在于：如果子查询的结果集比较小、外层表非常大，物化能避免“每读一行外层就要扫描一次全表”的最坏情况，而是把内层结果做成一个带索引的小表，外层用索引去匹配。你在EXPLAIN里看到select_type为MATERIALIZED，就说明子查询被物化成了临时表。所以遇到IN子查询时，不要第一反应就是“把它改成EXISTS”。先跑一下EXPLAIN，看看优化器走的是物化、半连接，还是逐行关联，再决定要不要改写。3.3 NOT IN 的经典 NULL 陷阱NOT IN绝对是我见过翻车率最高的子查询写法之一，而且翻车原因往往不是性能，而是结果完全错误。看这段SQL，需求是找出不在“已撤销”部门里的员工：SELECT employee_id, employee_name FROM employee WHERE dept_id NOT IN ( SELECT dept_id FROM dept WHERE dept_name = '已撤销' );如果dept表里存在某条记录的dept_id为NULL，这个查询的结果会永远返回空集。原因是SQL的三值逻辑：NOT IN等价于“不等于集合里任何一个值”，但集合里一旦有NULL，所有比较都会变成“不等于NULL”，而“不等于NULL”的结果是UNKNOWN，WHERE只保留TRUE，于是所有行都被过滤掉了。这个坑我在线上遇到过不止一次，排查方法很简单：单独查一下子查询里有没有NULL。解决方案也不复杂，用NOT EXISTS替代NOT IN，或者先在子查询里过滤掉NULL。SELECT employee_id, employee_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id AND d.dept_name = '已撤销' );3.4 执行计划怎么看出子查询的真相判断一条子查询SQL到底行不行，最高效的方式永远是EXPLAIN。这里我列几个最常出现的select_type字段，你看到它们就知道MySQL在干什么：EXPLAIN中的select_type含义优化建议DEPENDENT SUBQUERY关联子查询，外层每行都执行内层检查内层关联字段是否有索引SUBQUERY非关联子查询，只执行一次结果集大时可考虑改写为JOINMATERIALIZED子查询被物化为临时表说明MySQL选择了物化策略，通常问题不大DERIVED派生表被物化检查能否让优化器合并到外层其中最需要警惕的是DEPENDENT SUBQUERY。它不一定会慢，但如果外层行数大、内层关联字段又没有索引，那就是典型的N+1灾难。看到这个关键字，第一件事就是检查内层子查询的WHERE条件字段有没有索引。3.5 到底怎么选：一张决策参考表基于优化器的机制，我给一个粗粒度的选型参考，注意是参考，不是金科玉律：场景推荐倾向理由子查询结果集很小，外层很大IN结果集物化成小索引表外层很小，子查询结果集很大EXISTS外层逐行走内层索引更稳妥内外层都很大优先改写JOIN让优化器自由选择连接算法排除场景（NOT IN）优先NOT EXISTS规避NULL陷阱单纯在SELECT里补字段标量子查询或LEFT JOIN标量子查询不改变行数最后的建议永远是一句话：写完SQL先EXPLAIN，别背结论。4. 进阶技巧：行子查询、HAVING子查询与“每组最新一条”实战4.1 行子查询：多列组合比较行子查询返回的是“一行多列”，通常配合行构造器使用，也就是WHERE (a, b) = (SELECT ...)这种写法。MySQL 5.7以上支持得比较完整。典型场景：找出每个学生最新一次考试的全部记录。用行子查询可以这么写：SELECT * FROM exam_record WHERE (student_no, batch_no) IN ( SELECT student_no, MAX(batch_no) FROM exam_record GROUP BY student_no );这里内层子查询返回的是“每个学生的学号和最新批次号”这两列，外层用元组(student_no, batch_no)去匹配这个二元组集合。这个写法比传统做法少了一步自连接，语义也更直观。不过要注意，行子查询在有些情况下不一定能很好地利用索引，如果表很大、student_no和batch_no又没有联合索引，性能可能不理想。碰到实际问题时还是先EXPLAIN验证。4.2 HAVING 子句里的子查询：用全表聚合做分组门槛HAVING通常用来过滤分组后的聚合结果，而子查询可以在这里帮我们引入“全表范围的参照值”。例子：找出平均工资高于全公司平均水平的部门。SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employee );这个写法的关键是，内层的全公司平均工资是固定的标量，不随分组变化。逻辑上相当于先算出全球平均工资，比如7500，然后让每个部门的平均值和7500比较。这类子查询写起来简单，但要注意：HAVING里的聚合条件不会使用任何普通索引来加速分组过滤，数据量大时的瓶颈在GROUP BY本身。4.3 经典场景：每组取最新一条记录的两种写法“取每个用户最近一单”这类需求，是子查询进阶的试金石。两种主流写法各有优劣。第一种，关联子查询加LIMIT：SELECT * FROM trade_order o WHERE o.trade_order_id = ( SELECT o2.trade_order_id FROM trade_order o2 WHERE o2.user_id = o.user_id ORDER BY o2.create_time DESC, o2.trade_order_id DESC LIMIT 1 );注意我在ORDER BY里加了trade_order_id DESC作为并列情况的兜底。实际业务里同一用户同一秒下两单并不罕见，如果不加ID排序，LIMIT取出的那一条就不稳定。这个写法的性能高度依赖索引。如果(user_id, create_time, trade_order_id)有复合索引，内层查询只需要走索引定位到一行，整体性能非常好；如果没索引，外层每行都要全表扫描一次，直接爆炸。第二种，窗口函数（MySQL 8.0+）：SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY create_time DESC, order_id DESC ) AS rn FROM trade_order ) t WHERE rn = 1;窗口函数写法的优点是逻辑清晰，不需要自己用关联子查询绕来绕去；缺点是在数据量很大时，它需要先把所有行都计算一遍序号，再过滤出rn = 1的行，中间过程无法跳过。所以我的选择标准是：如果存在合适的复合索引，关联子查询写法很容易跑得飞快；如果索引建不上或者SQL要动态拼接排序条件，窗口函数写法更稳定。4.4 派生表合并：优化器的自动行为前面说派生表要被当作临时表，但其实MySQL 5.7之后的优化器有个派生表合并优化：只要条件满足，它会把派生表直接合并到外层查询，不再物化临时表。但如果派生表里使用了GROUP BY、LIMIT、窗口函数等操作，合并往往无法进行，优化器只能走物化路线，也就是在EXPLAIN里看到DERIVED。这不一定慢，但你要知道：在派生表里写的每一条聚合逻辑，都可能改变优化的路径。举个例子，如果某个派生表可以合并但你在末尾多写了一个没有意义的LIMIT 1000000，可能导致它无法合并、白白多一次物化。写SQL时如果发现执行计划里出现了DERIVED，可以先想想这个派生表能不能简化，或者把不需要的LIMIT去掉。4.5 三层嵌套以上：用CTE保住可读性子查询是可以无限嵌套的，但人眼看不了那么深。三层以上的嵌套，我强烈建议改用CTE来拆解，也就是WITH AS结构。CTE的本质是一个命名临时结果集，但它比子查询嵌套更容易阅读和调试。拿前面“每组最新一条”的窗口函数写法举例，用CTE重写会清晰很多：WITH newest_orders AS ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY create_time DESC, order_id DESC ) AS rn FROM trade_order ) SELECT user_id, order_id, amount FROM newest_orders WHERE rn = 1;CTE还能被后面的多个查询引用，不用像派生表那样反复粘贴同一段子查询。如果你维护的是别人写的三层嵌套SQL，改成CTE之后不管是排查问题还是传给别人，都会省力很多。5. 我见过的子查询翻车现场与排查链路5.1 ERROR 1242：标量子查询返回多行这个报错基本都出现在标量子查询的位置，报错信息是Subquery returns more than 1 row。场景很典型，比如你写了个标量子查询想取用户昵称，但内层WHERE条件不够精确，结果返回了两行。遇到这个错误，第一步不是改代码，而是把内层子查询单独复制出来跑一下，看看它到底返回了几行，然后问自己：我想要的是哪一行？如果业务上任意一行都可以，加LIMIT 1；如果必须在多行里选一个，就加上排序条件，用ORDER BY ... LIMIT 1明确取哪一行。5.2 NOT IN 返回空集：排查链路我有个朋友写过一条统计SQL，查“从未下过单的用户”，用了NOT IN (SELECT user_id FROM trade_order)，结果稳定返回0行。他的第一反应是数据问题，查了一个多小时才发现子查询里有NULL。所以遇到NOT IN空结果，我建议按这个顺序排查：先用SELECT * FROM (子查询) t WHERE t.user_id IS NULL检查子查询结果集里有没有NULL；如果有，直接改成NOT EXISTS，或者给子查询套一层WHERE user_id IS NOT NULL过滤。这个问题最大的迷惑性在于：SQL不报错，只是结果不对，没有经验的很容易怀疑业务逻辑。5.3 老版本里 LIMIT 和 IN 的组合报错在MySQL 5.7之前的版本，子查询里同时使用ORDER BY和LIMIT，外层再用IN接收，会直接报错：ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。碰到的解决办法是再包一层派生表，把排序和LIMIT的结果先变成一个临时结果集，再给外层IN用：SELECT * FROM trade_order WHERE user_id IN ( SELECT user_id FROM ( SELECT user_id FROM trade_order ORDER BY amount DESC LIMIT 10 ) top_users );现在8.0已经基本不会碰到这个报错了，但如果你的生产环境还是老版本，记住这个套路还是能省不少事的。5.4 关联子查询没有索引：一场N+1灾难关联子查询最典型的性能问题就是N+1。你以为写了一条SQL，实际上它可能执行了几万次内层查询。我遇到过一条列表接口SQL，外层是50万用户，内层用WHERE user_id = outer.user_id去查订单表，但订单表的user_id字段没有索引。结果就是50万次全表扫描，接口直接超时。后来在订单表上建了(user_id, create_time)复合索引，同样一条SQL，从几十秒降到了几百毫秒。这个案例值得记住：看到关联子查询，先看内层关联字段有没有索引，这是性价比最高的优化动作。索引建立之后，内层每次查询由全表扫描变成走索引的快速查找，量变引起质变。5.5 一个完整排查案例：统计复购用户从20秒到0.3秒用之前讲的知识，走一遍完整的排查链路。需求是统计最近90天至少下过两单的用户。第一版SQL，很直观，用关联标量子查询算每个用户的订单数：SELECT u.user_id, u.user_name FROM user u WHERE ( SELECT COUNT(*) FROM trade_order o WHERE o.user_id = u.user_id AND o.create_time >= NOW() - INTERVAL 90 DAY ) >= 2;这条SQL跑了20多秒，EXPLAIN显示DEPENDENT SUBQUERY，订单表的user_id和create_time都没有合适的索引，外层每读一个用户，内层就全表扫一遍。第二版做了两个改动：给订单表建(user_id, create_time)复合索引，同时把写法改成EXISTS加HAVING：SELECT u.user_id, u.user_name FROM user u WHERE EXISTS ( SELECT 1 FROM trade_order o WHERE o.user_id = u.user_id AND o.create_time >= NOW() - INTERVAL 90 DAY GROUP BY o.user_id HAVING COUNT(*) >= 2 );索引让内层查询只定位到该用户的少量订单，GROUP BY和HAVING在这个小范围内很快算完。同样的需求，执行时间从20多秒降到0.3秒。整个过程没改业务逻辑，只改了索引和写法。5.6 子查询优化自查清单最后整理一份我在提交SQL之前都会过一遍的清单：检查项说明内层子查询关联字段是否有索引关联子查询没索引，基本就是灾难是否存在NOT IN且子查询结果可能含NULL有就用NOT EXISTS改写标量子查询是否会返回多行会就加LIMIT或改成IN/派生表派生表是否有别名没有就报ERROR 1248嵌套层级是否超过三层超过就考虑用CTE拆开是否真的需要子查询简单JOIN加聚合能不能代替是否执行过EXPLAIN没看执行计划，别上线最后说点我自己的使用习惯。我并不是子查询原教旨主义者，遇到复杂统计需求，我的第一选择常常是先拆CTE逐段验证结果集，确认每一步数据都是对的，再回头看执行计划，最后才决定这条SQL里哪些部分用子查询、哪些改成JOIN。子查询用顺了确实能写出非常简洁的SQL，但它的性能边界也特别明显——临界点就在内层能不能走索引、优化器愿不愿意物化。我的经验是：写法上记住语义和分类，工程上永远把EXPLAIN的结果当第一依据。
说个我早年写报表时遇到的真实需求：把每个部门工资最高的人捞出来。第一次写的时候我直接GROUP BY dept_id然后MAX(salary)，以为一句SQL收工，结果一看结果集，部门是出来了，可人员信息完全对不上——分组之后你只能拿到聚合值，拿不到那一行的其他字段。后来我才意识到，这种“先算出一个集合，再拿这个集合回去过滤明细”的场景，正是 MySQL SQL子查询最典型的用武之地。MySQL子查询这技术，说难不难，但真到了复杂统计、报表、数据清洗场景，用得好能省一半代码，用不好能把数据库拖垮。这篇我不打算照着官方文档念概念，而是按我实际使用的顺序，把子查询的分类、高频写法、性能优化、以及我踩过的几个真坑完整讲一遍。适合两种人：刚学SQL想一次把子查询体系搞明白的人，以及写了不少SQL但总觉得性能说不清、只会死记“IN慢EXISTS快”这种结论的人。1. 子查询的本质：MySQL到底怎么执行一条嵌套查询1.1 先分清三个维度：位置、形态、关联性子查询的定义其实一句话就能讲完：一条完整的SELECT语句，嵌套在另一条SQL语句的某个表达式里，这个内层SELECT就是子查询。外层不一定是SELECT，也可以是INSERT、UPDATE、DELETE，但日常开发里90%的战场都集中在SELECT。理解子查询，我建议从三个维度去拆，而不是死记“子查询有哪几种类型”这种列表。第一个维度是放的位置。子查询可以出现在WHERE后面做过滤条件，出现在FROM后面当临时表，出现在SELECT的投影列表里补一列，出现在HAVING里过滤分组结果，甚至出现在JOIN的ON条件里。位置不同，它的作用完全不同——WHERE子查询是“条件判定”，FROM子查询是“结果集复用”，SELECT子查询是“标量补列”。第二个维度是返回的形态。返回一个值的是标量子查询，返回一列多行的是列子查询，返回一行多列的是行子查询，返回多行多列的就是表子查询，也就是我们常说的派生表。形态直接决定了外层能不能用它、怎么用它。第三个维度是关联性。这是最影响性能的一个维度，也是不少人一开始忽略的：如果内层子查询不引用外层表的任何列，它叫非关联子查询；如果内层引用了外层表的列，比如WHERE o.user_id = u.user_id，它叫关联子查询。这俩在MySQL里的执行逻辑完全不同，下面展开说。1.2 非关联子查询：先查内层，只跑一次非关联子查询的执行逻辑最简单：MySQL先执行内层子查询，拿到完整结果集，再执行外层查询。内层只执行一次，结果可以被外层任意引用。比如这个需求：找出工资高于全公司平均工资的员工。SELECT employee_id, employee_name, salary FROM employee WHERE salary > ( SELECT AVG(salary) FROM employee );这里MySQL会先执行AVG(salary)，算出一个标量，比如8500，然后用这个8500去外层做普通过滤。整个过程非常直观，内层只跑一次，性能瓶颈只在于外层查询本身能不能走索引。很多人第一次接触子查询时容易懵，其实你可以把它理解成两步手写SQL：先单独跑内层，看到结果，比如“平均工资是8500”，然后手写一条WHERE salary > 8500的SQL。非关联子查询就是让MySQL自动帮你完成这件事，区别只是它在一条语句里完成，不用你手动复制结果。1.3 关联子查询：外层一行，内层跟着跑一遍关联子查询就完全不一样了。它会在外层查询的每一行上，把这一行的值带入内层子查询去执行。如果外层有1万行，内层子查询理论上要执行1万次，这就是俗称的N+1问题。看这个例子：查每个员工的工资，同时显示他所在部门的最高工资。SELECT e.employee_id, e.employee_name, e.dept_id, (SELECT MAX(e2.salary) FROM employee e2 WHERE e2.dept_id = e.dept_id ) AS dept_max_salary FROM employee e;注意内层的e2.dept_id = e.dept_id里的e.dept_id，它引用的是外层员工表的列。MySQL处理这条SQL时，相当于外层每读出一行员工记录，就拿这个员工的dept_id去内层查一遍部门最高工资，然后把结果放到这一行的dept_max_salary列上。如果你在这个外层的员工表上有合适的索引，内层查询可能很快；如果没索引，每次内层都是全表扫描，外层1万行就是1万次全表扫描，查询性能会差到让你怀疑人生。这也是判断一条SQL“会不会慢”的最直观依据：看到关联子查询，第一反应就是检查内层关联字段有没有索引。1.4 分类关系一览把上面三个维度合在一起，子查询的体系就清晰了：分类维度类型返回值典型位置按结果形态标量子查询单行单列SELECT、WHERE、HAVING按结果形态列子查询单列多行WHERE + IN按结果形态行子查询单行多列WHERE + 行构造器按结果形态表子查询（派生表）多行多列FROM按关联性非关联子查询内层独立执行一次任意位置按关联性关联子查询外层每行执行一次依赖外层列这张表你不用背，真正写代码的时候心里有个概念就行：先判断是关联还是非关联，再判断返回的形态能不能满足外层语法要求，最后考虑这个位置和写法会不会触发性能问题。后面几章所有的实战写法，都是在这个框架下展开的。2. 实战高频写法：四种子查询，用之前先想清楚语义2.1 WHERE + IN：把“集合包含”交给数据库列子查询最常见的用法就是跟在IN或NOT IN后面，语义是“外层这个字段的值，是否出现在子查询返回的集合里”。例子：找出所有在数码类目下过单的用户。SELECT user_id, user_name FROM user WHERE user_id IN ( SELECT DISTINCT user_id FROM trade_order WHERE category = '数码' );很多人会纠结内层要不要写DISTINCT，其实IN的结果集本身有去重语义，写不写对最终结果没影响。我自己习惯写DISTINCT，主要是为了让自己读SQL的时候知道这个集合的语义是“用户ID集合”，而不是“订单集合”。有一点要注意：IN后面接的子查询返回结果集如果很大，MySQL会把整个结果集加载进临时表参与判断，所以子查询里务必只返回需要的外层匹配字段，不要习惯性地SELECT *。返回字段越大，临时表开销越大。2.2 EXISTS：只关心“有没有”，不关心“返回什么”EXISTS是另一类高频子查询，语义是“判断是否存在满足条件的记录”。它和IN最大的区别是：EXISTS不关心内层返回什么列，只关心内层是否返回了行。例子：找出所有下过金额超过1000元订单的客户。SELECT c.customer_id, c.customer_name FROM customer c WHERE EXISTS ( SELECT 1 FROM trade_order o WHERE o.customer_id = c.customer_id AND o.amount > 1000 );这里内层写了SELECT 1，纯粹是习惯，优化器会忽略SELECT的字段列表，你写SELECT *也一样。EXISTS真正的执行语义是：只要找到一条满足o.customer_id = c.customer_id AND o.amount > 1000的记录，就立刻返回真，不再继续往下找，所以它很适合做“存在性判断”。在业务层面，IN和EXISTS的区别可以这么理解：IN是“我的值在不在你那个集合里”，EXISTS是“你那里能不能找到跟我匹配的行”。大部分场景两者可以互换，但语义上EXISTS更接近关联查询的思维，尤其适合子查询和外层查询条件复杂的场景。2.3 FROM后面的派生表：查询结果当临时表用FROM后面的子查询叫派生表，它的核心价值是：先通过子查询算出一张中间结果集，再让外层查询像操作普通表一样去操作它。例子：统计每个部门的员工人数，并显示部门名称。SELECT d.dept_name, t.emp_count FROM ( SELECT dept_id, COUNT(*) AS emp_count FROM employee GROUP BY dept_id ) t JOIN department d ON d.id = t.dept_id;这里最关键的语法点是：派生表必须有一个别名，也就是FROM (子查询) t里的t。漏掉别名会直接报ERROR 1248 (42000): Every derived table must have its own alias。我见过很多新手在报错后一脸茫然，其实解决办法就一句话：加个字母当别名。派生表在逻辑上就是把子查询的结果物化成一张临时表，但MySQL优化器不一定会真的物化，5.7版本之后有派生表合并优化，这个放到后面第4章细讲。2.4 SELECT投影里的标量子查询：补一列而不改变行数有时候你只是想在查询结果里顺手带一个聚合字段，比如订单列表上显示用户昵称。用JOIN也能做，但JOIN可能会改变结果集行数，比如被连接的表有重复数据时，行数会被放大。这时候标量子查询就很舒服：它只负责在每行上补一个值，不参与行的筛选和扩充。SELECT o.order_id, o.order_no, o.amount, (SELECT u.nick_name FROM user u WHERE u.user_id = o.user_id ) AS user_nick FROM trade_order o;注意，这里有个硬性前提：标量子查询必须保证最多返回一行，也就是“一行一列”。如果内层返回了多行，MySQL会直接报ERROR 1242 (21000): Subquery returns more than 1 row。这个错误我在第5章会专门讲排查思路。2.5 四种写法的语义对比写法语义适合场景典型风险WHERE + IN值属于某个集合集合判断、数据筛选结果集过大时临时表开销WHERE + EXISTS存在匹配即可存在性判断、关联子查询内层关联字段无索引时会很慢FROM + 派生表中间结果参与外层计算聚合结果二次加工漏写别名、派生表无法合并SELECT + 标量子查询每行补一个值列表页补字段返回多行直接报错你先把这个语义对应关系到脑子里，写SQL的时候对着需求选一种最像的，基本就不会跑偏。3. 性能真相：IN、EXISTS、JOIN到底谁快3.1 先破除“IN永远比EXISTS慢”的旧观念网上关于IN和EXISTS谁快的讨论，绝大多数来源于老版本的MySQL和Oracle经验，很多结论放在今天的MySQL里已经不太成立了。MySQL从5.6版本开始引入了半连接优化和子查询物化优化。简单说，当一个IN子查询条件合适时，优化器会把它改写成类似JOIN的执行计划——只保留匹配行，而不会真的“先把子查询整个跑完，再逐行去外层判断”。所以你经常能看到EXPLAIN里明明写的是IN，执行计划却出现了semijoin这样的字样。这说明优化器已经把它处理成了更高效的连接方式，而不是大家想象中的“慢速逐行判断”。反过来，关联EXISTS也未必就是老老实实逐行执行，MySQL优化器同样可能在合适的条件下把它转换为半连接。所以任何“这个写法一定快、那个写法一定慢”的结论，在今天的MySQL里都不可靠。3.2 子查询物化：MySQL处理IN的关键优化MySQL 5.6之后，对于非关联的IN子查询，优化器还可能使用物化策略：把子查询的结果集一次性写入内部临时表，并且自动在临时表上建立索引，然后再和外层表做连接或匹配。这个优化的意义在于：如果子查询的结果集比较小、外层表非常大，物化能避免“每读一行外层就要扫描一次全表”的最坏情况，而是把内层结果做成一个带索引的小表，外层用索引去匹配。你在EXPLAIN里看到select_type为MATERIALIZED，就说明子查询被物化成了临时表。所以遇到IN子查询时，不要第一反应就是“把它改成EXISTS”。先跑一下EXPLAIN，看看优化器走的是物化、半连接，还是逐行关联，再决定要不要改写。3.3 NOT IN 的经典 NULL 陷阱NOT IN绝对是我见过翻车率最高的子查询写法之一，而且翻车原因往往不是性能，而是结果完全错误。看这段SQL，需求是找出不在“已撤销”部门里的员工：SELECT employee_id, employee_name FROM employee WHERE dept_id NOT IN ( SELECT dept_id FROM dept WHERE dept_name = '已撤销' );如果dept表里存在某条记录的dept_id为NULL，这个查询的结果会永远返回空集。原因是SQL的三值逻辑：NOT IN等价于“不等于集合里任何一个值”，但集合里一旦有NULL，所有比较都会变成“不等于NULL”，而“不等于NULL”的结果是UNKNOWN，WHERE只保留TRUE，于是所有行都被过滤掉了。这个坑我在线上遇到过不止一次，排查方法很简单：单独查一下子查询里有没有NULL。解决方案也不复杂，用NOT EXISTS替代NOT IN，或者先在子查询里过滤掉NULL。SELECT employee_id, employee_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM dept d WHERE d.dept_id = e.dept_id AND d.dept_name = '已撤销' );3.4 执行计划怎么看出子查询的真相判断一条子查询SQL到底行不行，最高效的方式永远是EXPLAIN。这里我列几个最常出现的select_type字段，你看到它们就知道MySQL在干什么：EXPLAIN中的select_type含义优化建议DEPENDENT SUBQUERY关联子查询，外层每行都执行内层检查内层关联字段是否有索引SUBQUERY非关联子查询，只执行一次结果集大时可考虑改写为JOINMATERIALIZED子查询被物化为临时表说明MySQL选择了物化策略，通常问题不大DERIVED派生表被物化检查能否让优化器合并到外层其中最需要警惕的是DEPENDENT SUBQUERY。它不一定会慢，但如果外层行数大、内层关联字段又没有索引，那就是典型的N+1灾难。看到这个关键字，第一件事就是检查内层子查询的WHERE条件字段有没有索引。3.5 到底怎么选：一张决策参考表基于优化器的机制，我给一个粗粒度的选型参考，注意是参考，不是金科玉律：场景推荐倾向理由子查询结果集很小，外层很大IN结果集物化成小索引表外层很小，子查询结果集很大EXISTS外层逐行走内层索引更稳妥内外层都很大优先改写JOIN让优化器自由选择连接算法排除场景（NOT IN）优先NOT EXISTS规避NULL陷阱单纯在SELECT里补字段标量子查询或LEFT JOIN标量子查询不改变行数最后的建议永远是一句话：写完SQL先EXPLAIN，别背结论。4. 进阶技巧：行子查询、HAVING子查询与“每组最新一条”实战4.1 行子查询：多列组合比较行子查询返回的是“一行多列”，通常配合行构造器使用，也就是WHERE (a, b) = (SELECT ...)这种写法。MySQL 5.7以上支持得比较完整。典型场景：找出每个学生最新一次考试的全部记录。用行子查询可以这么写：SELECT * FROM exam_record WHERE (student_no, batch_no) IN ( SELECT student_no, MAX(batch_no) FROM exam_record GROUP BY student_no );这里内层子查询返回的是“每个学生的学号和最新批次号”这两列，外层用元组(student_no, batch_no)去匹配这个二元组集合。这个写法比传统做法少了一步自连接，语义也更直观。不过要注意，行子查询在有些情况下不一定能很好地利用索引，如果表很大、student_no和batch_no又没有联合索引，性能可能不理想。碰到实际问题时还是先EXPLAIN验证。4.2 HAVING 子句里的子查询：用全表聚合做分组门槛HAVING通常用来过滤分组后的聚合结果，而子查询可以在这里帮我们引入“全表范围的参照值”。例子：找出平均工资高于全公司平均水平的部门。SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employee );这个写法的关键是，内层的全公司平均工资是固定的标量，不随分组变化。逻辑上相当于先算出全球平均工资，比如7500，然后让每个部门的平均值和7500比较。这类子查询写起来简单，但要注意：HAVING里的聚合条件不会使用任何普通索引来加速分组过滤，数据量大时的瓶颈在GROUP BY本身。4.3 经典场景：每组取最新一条记录的两种写法“取每个用户最近一单”这类需求，是子查询进阶的试金石。两种主流写法各有优劣。第一种，关联子查询加LIMIT：SELECT * FROM trade_order o WHERE o.trade_order_id = ( SELECT o2.trade_order_id FROM trade_order o2 WHERE o2.user_id = o.user_id ORDER BY o2.create_time DESC, o2.trade_order_id DESC LIMIT 1 );注意我在ORDER BY里加了trade_order_id DESC作为并列情况的兜底。实际业务里同一用户同一秒下两单并不罕见，如果不加ID排序，LIMIT取出的那一条就不稳定。这个写法的性能高度依赖索引。如果(user_id, create_time, trade_order_id)有复合索引，内层查询只需要走索引定位到一行，整体性能非常好；如果没索引，外层每行都要全表扫描一次，直接爆炸。第二种，窗口函数（MySQL 8.0+）：SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY create_time DESC, order_id DESC ) AS rn FROM trade_order ) t WHERE rn = 1;窗口函数写法的优点是逻辑清晰，不需要自己用关联子查询绕来绕去；缺点是在数据量很大时，它需要先把所有行都计算一遍序号，再过滤出rn = 1的行，中间过程无法跳过。所以我的选择标准是：如果存在合适的复合索引，关联子查询写法很容易跑得飞快；如果索引建不上或者SQL要动态拼接排序条件，窗口函数写法更稳定。4.4 派生表合并：优化器的自动行为前面说派生表要被当作临时表，但其实MySQL 5.7之后的优化器有个派生表合并优化：只要条件满足，它会把派生表直接合并到外层查询，不再物化临时表。但如果派生表里使用了GROUP BY、LIMIT、窗口函数等操作，合并往往无法进行，优化器只能走物化路线，也就是在EXPLAIN里看到DERIVED。这不一定慢，但你要知道：在派生表里写的每一条聚合逻辑，都可能改变优化的路径。举个例子，如果某个派生表可以合并但你在末尾多写了一个没有意义的LIMIT 1000000，可能导致它无法合并、白白多一次物化。写SQL时如果发现执行计划里出现了DERIVED，可以先想想这个派生表能不能简化，或者把不需要的LIMIT去掉。4.5 三层嵌套以上：用CTE保住可读性子查询是可以无限嵌套的，但人眼看不了那么深。三层以上的嵌套，我强烈建议改用CTE来拆解，也就是WITH AS结构。CTE的本质是一个命名临时结果集，但它比子查询嵌套更容易阅读和调试。拿前面“每组最新一条”的窗口函数写法举例，用CTE重写会清晰很多：WITH newest_orders AS ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY create_time DESC, order_id DESC ) AS rn FROM trade_order ) SELECT user_id, order_id, amount FROM newest_orders WHERE rn = 1;CTE还能被后面的多个查询引用，不用像派生表那样反复粘贴同一段子查询。如果你维护的是别人写的三层嵌套SQL，改成CTE之后不管是排查问题还是传给别人，都会省力很多。5. 我见过的子查询翻车现场与排查链路5.1 ERROR 1242：标量子查询返回多行这个报错基本都出现在标量子查询的位置，报错信息是Subquery returns more than 1 row。场景很典型，比如你写了个标量子查询想取用户昵称，但内层WHERE条件不够精确，结果返回了两行。遇到这个错误，第一步不是改代码，而是把内层子查询单独复制出来跑一下，看看它到底返回了几行，然后问自己：我想要的是哪一行？如果业务上任意一行都可以，加LIMIT 1；如果必须在多行里选一个，就加上排序条件，用ORDER BY ... LIMIT 1明确取哪一行。5.2 NOT IN 返回空集：排查链路我有个朋友写过一条统计SQL，查“从未下过单的用户”，用了NOT IN (SELECT user_id FROM trade_order)，结果稳定返回0行。他的第一反应是数据问题，查了一个多小时才发现子查询里有NULL。所以遇到NOT IN空结果，我建议按这个顺序排查：先用SELECT * FROM (子查询) t WHERE t.user_id IS NULL检查子查询结果集里有没有NULL；如果有，直接改成NOT EXISTS，或者给子查询套一层WHERE user_id IS NOT NULL过滤。这个问题最大的迷惑性在于：SQL不报错，只是结果不对，没有经验的很容易怀疑业务逻辑。5.3 老版本里 LIMIT 和 IN 的组合报错在MySQL 5.7之前的版本，子查询里同时使用ORDER BY和LIMIT，外层再用IN接收，会直接报错：ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'。碰到的解决办法是再包一层派生表，把排序和LIMIT的结果先变成一个临时结果集，再给外层IN用：SELECT * FROM trade_order WHERE user_id IN ( SELECT user_id FROM ( SELECT user_id FROM trade_order ORDER BY amount DESC LIMIT 10 ) top_users );现在8.0已经基本不会碰到这个报错了，但如果你的生产环境还是老版本，记住这个套路还是能省不少事的。5.4 关联子查询没有索引：一场N+1灾难关联子查询最典型的性能问题就是N+1。你以为写了一条SQL，实际上它可能执行了几万次内层查询。我遇到过一条列表接口SQL，外层是50万用户，内层用WHERE user_id = outer.user_id去查订单表，但订单表的user_id字段没有索引。结果就是50万次全表扫描，接口直接超时。后来在订单表上建了(user_id, create_time)复合索引，同样一条SQL，从几十秒降到了几百毫秒。这个案例值得记住：看到关联子查询，先看内层关联字段有没有索引，这是性价比最高的优化动作。索引建立之后，内层每次查询由全表扫描变成走索引的快速查找，量变引起质变。5.5 一个完整排查案例：统计复购用户从20秒到0.3秒用之前讲的知识，走一遍完整的排查链路。需求是统计最近90天至少下过两单的用户。第一版SQL，很直观，用关联标量子查询算每个用户的订单数：SELECT u.user_id, u.user_name FROM user u WHERE ( SELECT COUNT(*) FROM trade_order o WHERE o.user_id = u.user_id AND o.create_time >= NOW() - INTERVAL 90 DAY ) >= 2;这条SQL跑了20多秒，EXPLAIN显示DEPENDENT SUBQUERY，订单表的user_id和create_time都没有合适的索引，外层每读一个用户，内层就全表扫一遍。第二版做了两个改动：给订单表建(user_id, create_time)复合索引，同时把写法改成EXISTS加HAVING：SELECT u.user_id, u.user_name FROM user u WHERE EXISTS ( SELECT 1 FROM trade_order o WHERE o.user_id = u.user_id AND o.create_time >= NOW() - INTERVAL 90 DAY GROUP BY o.user_id HAVING COUNT(*) >= 2 );索引让内层查询只定位到该用户的少量订单，GROUP BY和HAVING在这个小范围内很快算完。同样的需求，执行时间从20多秒降到0.3秒。整个过程没改业务逻辑，只改了索引和写法。5.6 子查询优化自查清单最后整理一份我在提交SQL之前都会过一遍的清单：检查项说明内层子查询关联字段是否有索引关联子查询没索引，基本就是灾难是否存在NOT IN且子查询结果可能含NULL有就用NOT EXISTS改写标量子查询是否会返回多行会就加LIMIT或改成IN/派生表派生表是否有别名没有就报ERROR 1248嵌套层级是否超过三层超过就考虑用CTE拆开是否真的需要子查询简单JOIN加聚合能不能代替是否执行过EXPLAIN没看执行计划，别上线最后说点我自己的使用习惯。我并不是子查询原教旨主义者，遇到复杂统计需求，我的第一选择常常是先拆CTE逐段验证结果集，确认每一步数据都是对的，再回头看执行计划，最后才决定这条SQL里哪些部分用子查询、哪些改成JOIN。子查询用顺了确实能写出非常简洁的SQL，但它的性能边界也特别明显——临界点就在内层能不能走索引、优化器愿不愿意物化。我的经验是：写法上记住语义和分类，工程上永远把EXPLAIN的结果当第一依据。