1. 子查询是什么为什么后端和数据分析都得掌握我第一次真正被 SQL 子查询“教育”了一回是在一个统计报表的改造现场。一条主查询里嵌套了三个子查询老板在旁边催着我定位慢在哪而我盯着屏幕只能把每个括号一层层拆开人工推演拆到一半脑子就烧了。后来我才意识到真正让我痛苦的其实不是子查询语法而是我不知道它是怎么执行的、什么时候该用、什么时候不该用。所谓子查询简单说就是一条完整的 SELECT 语句被括号包住之后嵌在另一条 SELECT、INSERT、UPDATE、DELETE 语句的里面。外层 SQL 拿内层返回的结果继续做筛选、计算、排序或者关联。听起来很简单但在真实业务里它几乎天天出现查“比平均分高的学生”、查“没下过单的用户”、查“每个品类卖得最好的商品”这些需求用子查询都可以写成一眼能看懂的 SQL。举一个最基础、也最经典的例子SELECT id, name, city FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total_amount 1000 );这里括号内的部分就是一个子查询它先把满足条件的user_id查出来外层再根据这个结果集合去users表里找人。如果你是后端开发、数据分析师或者经常要和 MySQL 打交道的人这个东西属于基本功不掌握的话写复杂统计 SQL 会很吃力。1.1 一句话定义外加一段能直接看懂的示例再往深一步说子查询的完整定义是嵌套在外部查询内部的一条查询语句它可以在外层查询执行之前、执行过程中甚至逐行执行时被反复计算并把返回值交给外层使用。为了方便后面讲我先把本文会用到的几张简化表定义出来。注意字段和索引都是从真实项目里抽取出来的简化版本重在讲逻辑。CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(50), status INT ); CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, total_amount DECIMAL(10,2), status VARCHAR(20), created_at DATETIME, KEY idx_user_id (user_id) ); CREATE TABLE products ( id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2) );有了这些表后面的所有案例都能直接跑起来。你可以理解为users是用户主表orders是订单流水表products是商品表。实际项目里这些表可能会拆得更细但子查询的写法和执行逻辑不会变。1.2 真实业务里哪些场景必须用子查询很多人问我子查询到底能不能不用答案是能很多子查询确实可以改写成 JOIN。但有些场景子查询反而是最贴近业务表达、最不容易写错的方案。我总结了几类“更适合用子查询”的情况需要用聚合函数的结果去做比较比如“找出价格高于全站平均价的商品”。需要先算出一个临时集合再把这个集合当成一张表继续参与查询这时候可以用 FROM 子查询。需要判断“是否至少存在一条符合条件的记录”EXISTS 子查询非常合适。需要在 SELECT 列表里直接输出某个关联统计值比如每个用户的下单次数。子查询不是万能的但它是 SQL 表达能力的重要拼图。你不需要刻意制造嵌套但遇到上面的场景时应该条件反射般想到“这里可以用子查询”。2. 子查询的分类先把骨架搭起来再谈细节子查询如果只靠死记硬背很容易混乱。但如果你先按“返回什么结果”和“跟外层有没有关系”两个维度分类整个骨架就清楚了。2.1 按返回结果分标量、行、表第一类标量子查询。它返回的结果只有一个值也就是一行一列。比如下面这条语句SELECT product_name, price FROM products WHERE price ( SELECT AVG(price) FROM products );AVG(price)返回的是一个数值这个子查询就是标量子查询。写了标量子查询之后你不能再指望它返回多行否则外层使用比较时会直接报错。第二类行子查询。它返回一行但可以有多个列。比如SELECT * FROM employees WHERE (salary, department_id) ( SELECT salary, department_id FROM employees WHERE id 1001 );这在 MySQL 里完全合法括号里用一个“行构造器”和子查询返回的一整行做比较。实际项目里用到的频率不算高但遇到这种写法时你要能看懂。第三类表子查询也叫派生表。它返回一个多行多列的结果集通常放在 FROM 后面被当成一张临时表使用。比如SELECT u.name, t.order_cnt FROM users u JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status paid GROUP BY user_id ) t ON u.id t.user_id WHERE t.order_cnt 3;这里的括号部分就是表子查询外层把它当作一张临时表t去 JOIN。注意MySQL 要求这种派生表必须有别名所以你不能省略t。2.2 按内外层依赖分非相关子查询和相关子查询这是整个子查询体系里最重要、也最影响性能的一个分类。非相关子查询也叫独立子查询。它内部不引用外层查询的任何字段可以先单独执行一次得到结果之后外层再继续处理。刚才那个WHERE price (SELECT AVG(price) FROM products)就是典型例子内层先算平均价外层拿这个固定值去做比较。相关子查询则完全相反。它的内层语句里引用了外层表的字段所以外层每处理一行内层都可能要重新执行一次。看这个例子SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) AS order_cnt FROM users u;内层的o.user_id u.id用到了外层users表的u.id这两个查询是绑在一起的。理论上外层有多少个用户内层就可能跑多少次。这个概念我在后面讲执行代价和优化时还会重点提大家可以先记住相关子查询的灵活度高但性能风险也高。2.3 按出现位置分SELECT、FROM、WHERE、HAVING、ORDER BY子查询可以出现在 SQL 的很多位置而不同位置的写法约束和优化逻辑并不完全一样。在 SELECT 列表里一般返回标量作为额外输出的一列。在 FROM 子句里作为派生表必须有别名。在 WHERE 子句里可以用在IN、EXISTS、比较运算符、ANY、ALL等场景。在 HAVING 子句里用于整体分组后的过滤条件比如过滤出“平均分高于整体平均分”的分组。在 ORDER BY 子句里可以用子查询的结果作为排序依据。把这些位置都试一遍之后你会发现子查询其实是一套很灵活的“嵌套逻辑”。写的时候最关键的是先想清楚我想要的是一个值、一列、还是一张表想清楚这个位置才不会放错。3. 高频实战场景拆解理论讲再多不落地都是空的。我直接把日常开发里出现频率最高的七类子查询场景一个个拆开讲每段都配有能直接抄走的 SQL。3.1 WHERE 后接标量子查询找出超过平均线的数据最常用、也最基础的一个场景是先算出一个聚合值再用它去过滤外层数据。假设我想找出所有价格高于全站平均价格的商品SELECT product_name, price FROM products WHERE price ( SELECT AVG(price) FROM products );这条语句的执行顺序非常直观先执行括号里的SELECT AVG(price) FROM products得到比如 299然后再执行外层的WHERE price 299。这种写法比先查平均价、再写第二条 SQL 要简单而且能保证两条语句之间平均价不会因为中间数据变动而产生偏差。需要注意的是标量子查询必须保证只返回一行一列。如果子查询返回多行外层用、这类运算符就会直接报错。所以写标量子查询时要确认聚合函数或者主键筛选能让它退化成单值。3.2 FROM 子查询把聚合结果当成一张临时表有些需求是先做一次聚合得到一个新的统计表然后再和其他表关联。这种情况最适合 FROM 子查询。比如我要找出“有效订单超过 3 个的用户及下单数”SELECT u.name, t.order_cnt FROM users u INNER JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status paid GROUP BY user_id ) t ON u.id t.user_id WHERE t.order_cnt 3;这里括号里的子查询先按user_id分组算出每个用户的订单数再把结果当成临时表t和users表 JOIN。外层可以直接使用t.order_cnt这个别名逻辑非常清晰。这个场景最容易踩的坑是忘记给派生表加别名。MySQL 对派生表强制要求别名不写就会直接报语法错误。另外一个容易被忽略的点是子查询内部的 ORDER BY 如果外层没有配合 LIMIT 使用很多情况下会被优化器忽略掉这一点后面专题讲。3.3 IN / NOT IN 集合判断最常见的过滤写法这里先展示最常见的 IN 子查询。比如查“所有上海用户下的订单”SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE city 上海 );这条 SQL 的意思是先把子查询里的用户 id 集合查出来再拿orders.user_id去判断是否在这个集合里。IN 子查询写起来很舒服但有一点要特别注意子查询结果集中不能有 NULL否则在 NOT IN 的场景下会出大问题。我专门在后面的“避坑指南”里讲这里先不展开。通常遇到“排除某些用户”也就是 NOT IN 的时候我更推荐改成 NOT EXISTS安全系数更高。3.4 EXISTS / NOT EXISTS存在性判断的最优解之一EXISTS 是专门用来做“存在性判断”的。比如我要找“下过单且订单金额超过 1000 的用户”SELECT id, name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.total_amount 1000 );这段 SQL 的逻辑是每取一个用户就去 orders 表里查有没有满足条件的记录只要查到一条就立刻返回真不再继续往下扫。所以 EXISTS 更关心“有没有”不关心查到的是什么这也是为什么子查询里很多人喜欢写SELECT 1而不是SELECT *。在实际执行时EXISTS 子查询往往是相关子查询因为内层用了外层表的u.id。但 MySQL 优化器有可能会把一些 EXISTS 改写成 semi-join 来提升效率所以不是说相关子查询就一定慢而是要看执行计划。3.5 ANY / ALL跟一套值做多行比较ANY 和 ALL 很容易混淆我教给大家一个记忆方法ANY相当于“至少其中一个”ALL相当于“所有里面都满足”。假设我要找出“价格比任何一款手机都便宜的平板电脑”SELECT * FROM products p WHERE p.category 平板 AND p.price ANY ( SELECT price FROM products WHERE category 手机 ); ANY表示只要比子查询返回的任意一个手机价格低就算满足。如果手机价格是 3999、4999、5999那么平板价格只要小于任意一个比如低于 5999 就会被选中。如果我反过来要找“比所有手机都贵的平板”就要用 ALLSELECT * FROM products p WHERE p.category 平板 AND p.price ALL ( SELECT price FROM products WHERE category 手机 );如果手机价格最高是 5999那么这个平板必须比 5999 高。ANY和ALL在业务里不算高频但一旦遇到“跟一组动态值比较”的条件它们能省很多事情。3.6 SELECT 列表中的子查询边上查边补列有时候我希望在主查询的结果里直接带出一个统计值比如“查询用户信息的同时列出每位用户的订单数”SELECT u.id, u.name, ( SELECT COUNT(*) FROM orders o WHERE o.user_id u.id ) AS order_cnt FROM users u;这个写法的好处是非常直白不需要提前聚合。但代价也很明显它通常是一个相关子查询外层每走一个用户内层都要执行一次 COUNT。如果 users 表只有几十行没问题如果 users 表有几十万行最好提前用 GROUP BY 聚合后再 LEFT JOIN而不是直接在 SELECT 列表里放子查询。3.7 多级嵌套子查询里套子查询子查询可以嵌套好几层比如先查出满足条件的一组用户再在这组用户里继续筛最后查订单SELECT * FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE id IN ( SELECT user_id FROM user_vip_level WHERE level 5 ) );这里最内层先查高等级用户中间层根据结果回来筛用户表最外层再查订单。多级嵌套本身不复杂但可读性和性能都会随着层级变深而下降。真实项目里如果遇到三层以上的嵌套我通常会先停下来想想是不是拆成多个步骤或者用 JOIN 改写会更合适。4. 执行顺序和执行计划别让直觉蒙骗你很多人学子查询时被告知“先执行内层再执行外层”这句话在简单场景下成立但在 MySQL 的真实执行过程中并不总是如此。这不是语法问题而是优化器可能会把子查询改写成其他执行方式。4.1 先内后外多数情况成立但别忽略优化器对于非相关子查询比如WHERE price (SELECT AVG(price) FROM products)优化器通常会先把内层算出来然后执行外层。但对于IN子查询MySQL 可能会使用 semi-join 优化把它执行成类似 JOIN 的流程而不是傻乎乎地先物化一个完整的临时集合再外层扫描。再比如 EXISTS 相关子查询MySQL 也可能尝试半连接优化。所以在不查看执行计划的情况下你靠“先内后外”的直觉去推测性能很容易翻车。我见过有人看见相关子查询就一口咬定“一定慢”结果 EXPLAIN 出来之后发现优化器改写得非常好根本不存在逐行全表扫的问题。所以记住一句经验先按逻辑理解子查询再用 EXPLAIN 验证真实的执行路径两头都不能丢。4.2 用 EXPLAIN 看穿子查询MySQL 的EXPLAIN是拆解子查询执行过程的利器。你只需要在原来的 SQL 前面加上 EXPLAINEXPLAIN SELECT id, name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id AND o.total_amount 1000 );你会在结果里看到这些关键字段select_type代表这个查询是普通查询、子查询、还是依赖外层的相关子查询。比较常见的值有 SIMPLE、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED。table这一步在访问哪张表。type访问类型从好到差大致是 system、const、eq_ref、ref、range、index、ALL。possible_keys 和 key优化器可能使用和实际使用的索引。rows预估扫描的行数。Extra额外信息比如 Using where、Using index、Using filesort 等。如果你在select_type里看到DEPENDENT SUBQUERY就意味着这是相关子查询内层要依赖外层传入的值去执行。这种情况下rows 预估值会非常直观地告诉你简单的一条 SQL 背后可能扫描了多少行数据。4.3 相关子查询为什么比非相关子查询更危险同样逻辑下非相关子查询可能只执行一次相关子查询却可能执行 N 次。外层有 1 万行内层每次扫描 1 万行理论代价就是 1 亿行级别的扫描这个量级对很多线上业务来说直接就是灾难。当然不是所有相关子查询都一定慢。如果内层的关联字段有索引比如orders.user_id有索引每次相关子查询通过索引去查找效率会高很多。但我还是建议大家形成条件反射看到相关子查询先想想外层数据量有多大再想想能不能改写。5. 性能优化与改写技巧子查询能不能写出好性能关键不在于“尽量不用子查询”而在于“知道什么时候用、怎么用以及怎么帮优化器拿到合适的执行计划”。5.1 索引放在哪些字段上子查询的索引选择和普通查询没有本质区别核心原则是让 WHERE 条件和关联条件能通过索引快速圈定范围。以IN (SELECT user_id FROM users WHERE city 上海)为例内层users.city如果有索引内层可以快速筛选外层orders.user_id如果有索引外层也能快速匹配。对于相关子查询比如WHERE o.user_id u.idorders.user_id上的索引尤其重要。我给同事做 SQL 优化时最常做的一件事就是检查 EXPLAIN 里的 key 字段。如果 key 是 NULL同时 type 是 ALL说明这个子查询在走全表扫描这时候不管怎么写 SQL性能上限都很低。5.2 相关子查询改写为 JOIN“把相关子查询改成 JOIN”是我用得最多的一招。比如这个 EXISTS 写法SELECT DISTINCT u.* FROM users u JOIN orders o ON o.user_id u.id AND o.total_amount 1000;逻辑上它和前面“下过单且订单金额超过 1000 的用户”是一致的。JOIN 写法把相关计算变成了普通的连接操作优化器处理 JOIN 的经验更丰富执行计划通常更稳定。但这里有个前置条件如果orders和users之间是一对多关系JOIN 会导致外层记录重复所以需要使用DISTINCT或者GROUP BY去重。DISTINCT 本身也有成本所以不能无脑说 JOIN 一定比 EXISTS 好。我的习惯是如果只是判断“存在”我会先试 EXISTS再看 EXPLAIN。如果优化器把 EXISTS 处理成普通的外层扫描加相关子查询我才会考虑改成 JOIN。如果还需要统计数量、汇总字段直接 JOIN 加 GROUP BY 通常更自然。5.3 派生表合并与物化在 MySQL 5.7 和 8.0 中派生表有两种处理方式合并和物化。合并是指 MySQL 把 FROM 子查询展开直接和外层查询合并成一条执行计划物化是指 MySQL 先把子查询执行结果落到一个内部临时表再去参与外部计算。什么时候物化通常当子查询内部有聚合、DISTINCT、LIMIT 等不容易合并的操作时MySQL 会选择物化。物化本身并不一定慢但如果物化出来的临时表没有有效索引外部访问它时仍然可能扫描大量数据。给一个实用的建议如果你需要“先排序后分页”的效果比如从子查询里取最近 10 条记录再和其他表关联必须保证子查询内部有 ORDER BY 和 LIMIT。否则 MySQL 优化器很可能因为合并而忽略排序最后结果完全不符合预期。5.4 优化器不买账时怎么办有时候你精心写完的子查询EXPLAIN 一看还是全表扫描。这时候我一般按这个顺序排查先看 SELECT 列和 WHERE 条件是否对上了索引优先给关联字段补索引。再看子查询结果集的大小。如果内层返回的数据量很小我可能会直接把子查询拆出来在应用层先查一次小结果集再拼进第二条 SQL。这个方法不够“优雅”但在紧急优化场景里非常可靠。最后才考虑各种优化器开关和 hint。线上环境不建议为了单条 SQL 乱调全局参数。记住SQL 优化的第一原则是“减少数据扫描范围”别只盯着语法层面改来改去。6. 我踩过的坑整理成一份避坑指南前面讲了很多正确做法这一节全是实打实的翻车记录。以下每一个问题我都在真实项目里见过而且不止一次。6.1 标量子查询返回多行有次同事写了一条类似下面的 SQLSELECT * FROM users WHERE id ( SELECT user_id FROM orders WHERE total_amount 100 );表面看起来没什么问题但total_amount 100的订单不止一条子查询返回了多行外层用去比较就直接报错Subquery returns more than 1 row。解决方式很简单如果业务上确实想匹配多个用户就把改成IN如果只想要其中一条子查询里要加上明确的排序和LIMIT 1。这个错误是子查询新手几乎必踩的一关。6.2 NOT IN 遇到 NULL悄悄吞掉所有结果这个坑非常隐蔽。假设我要查“从没下过单的用户”SELECT * FROM users WHERE id NOT IN ( SELECT user_id FROM orders );如果orders.user_id这一列里有 NULL结果会变成 0 行。原因是 SQL 的三值逻辑id NOT IN (1, 2, NULL)在逻辑上等价于id ! 1 AND id ! 2 AND id ! NULL而id ! NULL的结果是 NULL最终整个条件不为真所有行都被过滤掉。这种场景我强烈建议写成 NOT EXISTSSELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.user_id u.id );如果必须用 NOT IN就先把 NULL 排除掉比如WHERE user_id IS NOT NULL。但习惯了用 NOT EXISTS 之后你会省掉很多莫名其妙的排查时间。6.3 ORDER BY 在子查询里没生效有人在 FROM 子查询里写了 ORDER BY以为外层能按这个顺序输出结果发现乱序。比如SELECT * FROM ( SELECT id, product_name, price FROM products WHERE category 平板 ORDER BY price DESC ) t LIMIT 10;如果子查询里没有 LIMITMySQL 优化器很可能在合并派生表时直接忽略内层 ORDER BY。只有在子查询里同时使用ORDER BY ... LIMIT n排序才更有可能保留下来。所以当你想“先排序后取前几条再关联”时一定要把 LIMIT 写进子查询里否则就是白排。6.4 别让相关子查询在 SELECT 列表里跑全表我见过最典型的一条慢 SQL就是把 3.6 节那种写法直接用在百万级用户表上SELECT u.id, u.name, ( SELECT COUNT(*) FROM orders o WHERE o.user_id u.id ) AS order_cnt FROM users u;外层扫一百万个用户内层每次再去扫订单表哪怕每条记录都有索引总成本也非常可观。正确处理思路是先用 GROUP BY 把订单聚合好再通过 JOIN 带出结果SELECT u.id, u.name, COALESCE(t.order_cnt, 0) AS order_cnt FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) t ON u.id t.user_id;两条 SQL 的业务结果一致但执行效率可以差出好几个数量级。相关子查询写在 SELECT 列表里一定要先量一量外层数据量。6.5 子查询不是越嵌套越高级能减则减最后分享一个经验子查询虽然强大但真的不是嵌套得越多越厉害。我见过一段五层嵌套的统计查询写法看起来确实“很炫”但实际执行时优化器把它拆成了一堆相关子查询线上跑一次要好几秒。后来我把它拆成两步先物化中间结果到临时表再用 JOIN 串起来执行时间从秒级降到了毫秒级。子查询的价值应该体现在“让逻辑更清晰”而不是“让结构更复杂”。当你发现一段子查询已经需要拿纸笔逐层分析时就该考虑改写了。每次写完子查询我都会顺手加一发 EXPLAIN确认一下优化器是不是真的按照我预想的方式在执行。这个习惯帮我避掉了很多性能坑也希望你能带进自己的日常开发里。
