MySQL子查询是个很神奇的东西你刚接触SQL时觉得它难懂写了几年后又发现它是排查慢SQL时最常被拎出来“扣字眼”的地方。前阵子帮团队处理一个报表慢查询SQL里嵌套了三层子查询单次执行要二三十秒拆开分析后才发现不是子查询本身的问题而是很多人把它当成了万能钥匙随手往任何位置塞结果既伤性能又难维护。我干脆把MySQL子查询从头到尾整理了一遍从概念、分类、执行逻辑、优化器行为到改写技巧都揉进这篇文章里适合刚学SQL的人当系统性笔记也适合写了两三年SQL但没认真梳理过子查询的开发者查漏补缺。本文用的全是可以在自己电脑上跑的MySQL 5.7/8.0例子不依赖任何在线环境的特定写法。为了保证尽量全我把它拆成了六个大块先说清楚子查询的底层逻辑然后按“出现位置”和“返回结果”两个维度重新分类再专门讲相关子查询和EXISTS最后是把子查询弄慢的坑和改写思路。直接开始。1. 先把子查询这件事说清楚嵌套的SELECT到底返回了什么很多人背过定义子查询就是“嵌套在其他查询里的SELECT”。但这句话太稀松平常了它没有回答那个最核心的问题一条SELECT塞进另一条SELECT之后到底算什么1.1 子查询的本质一个可以“插”在任意位置的临时结果集一个子查询本质上就是一条完整、独立、用括号包起来的SELECT语句。它的执行结果可能是单个值、一列数据、一行数据也可能是一整张表。外层查询拿到这个结果以后再根据自己的逻辑继续筛选、计算、关联。你可以把它理解成一个函数调用先执行内层拿到一个返回值再把这个返回值塞进外层表达式里。比如最常见的“查工资高于平均工资的员工”脑子里的第一反应通常是先算出平均工资再把员工表和这个平均值比较。写成SQL就是SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);这里的(SELECT AVG(salary) FROM employee)就是子查询它返回了一个数值外层把它当成普通条件来比较。正因为子查询的本质是“临时结果”所以子查询几乎可以出现在SQL语句的任意角落SELECT子句里可以放、FROM子句里可以放、WHERE和HAVING里可以放、ORDER BY后面也能放。这也引出了很多人的困惑点——到底什么位置该放什么类型的子查询答案其实很简单看你需要这个子查询返回什么要一个值就放表达式位置要一张表就放FROM里要一列值就放到IN、ANY、ALL这类集合运算的右边。1.2 建一套测试数据后面所有例子都跑在它上面空口说概念很难有体感我先把后面反复用到的一整套测试表建出来。这个场景很简单部门表、员工表、订单表属于业务系统里最常见的那类数据模型。CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL, location VARCHAR(20) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, department_id INT, salary DECIMAL(10, 2) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, order_amount DECIMAL(10, 2), order_date DATE );插入少量但存在差异的数据方便看出每种查询的含义INSERT INTO department VALUES (1, 技术部, A区), (2, 市场部, B区), (3, 财务部, A区); INSERT INTO employee VALUES (1, 员工A, 1, 10000.00), (2, 员工B, 1, 8000.00), (3, 员工C, 2, 9000.00), (4, 员工D, 2, 7000.00), (5, 员工E, 3, 6000.00); INSERT INTO orders VALUES (1, 1, 500.00, 2024-01-01), (2, 1, 300.00, 2024-01-05), (3, 2, 200.00, 2024-02-01), (4, 3, 400.00, 2024-02-10);先停在几张表上不要急着复制代码。后面每讲一种写法你都回到这个数据模型里跑一遍比死记语法有效得多。2. 按出现位置拆解SELECT、FROM、WHERE、HAVING里都能藏子查询子查询的“位置”决定了它的角色。我见过太多人只会写WHERE后跟IN的子查询然后一碰到复杂报表就傻眼。实际上四个主要位置各有各的用法和注意点。2.1 FROM子句的派生表把子查询当临时表用FROM子句里的子查询在MySQL里叫派生表Derived Table你可以直接把它的查询结果想象成一张临时表然后再去JOIN、再筛选。最典型的场景是先做一层聚合统计再和其他表关联。比如我想看“各部门平均工资超过8000元的部门名称和平均工资”。难点在于部门名称在department表平均工资必须从employee表里先GROUP BY算出来。这时候就适合先把平均工资算成派生表再去关联部门表SELECT d.department_name, t.avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) t JOIN department d ON d.department_id t.department_id WHERE t.avg_salary 8000;有两个坑必须记住。第一MySQL要求派生表必须写别名哪怕你后面完全没用到也要给个名字否则直接报语法错误。第二从MySQL 5.7开始默认会把符合条件的派生表合并进外层查询如果你在派生表里写了ORDER BY但外层没有LIMIT这个ORDER BY很可能被优化器直接忽略排序顺序不保证。正确的做法是要排序就在最外层排不要指望内层排序能稳定传出来。MySQL 8.0.14以后还可以用LATERAL派生表它允许内层子查询引用外层查询的列用来解决“每行动态算一个结果”的问题。但在日常业务里用得不算多先把普通派生表玩明白再说。2.2 WHERE子句的筛选比较、IN、ANY/ALL都在这里WHERE子句里的子查询最常见但它不是只有IN一种写法。它可以出现在普通比较运算符的右边也可以配合IN、NOT IN、ANY、SOME、ALL这些集合运算符。最简单的比较式筛选就是前面那个查工资高于平均工资的例子SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);如果子查询返回的不是一个值而是一列值那就交给IN处理。比如查“部门位置在A区的所有员工”SELECT emp_id, emp_name, department_id FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A区 );ANY和ALL相对冷门但偶尔遇到会很有用。ANY表示“只要比子查询返回的任意一个值大就算满足”等价于大于最小值ALL表示“要比所有值都大才算满足”等价于大于最大值。例如“找出工资大于A区任意部门员工工资的人”可以写成SELECT emp_id, emp_name, salary FROM employee WHERE salary ANY ( SELECT salary FROM employee WHERE department_id IN (1, 3) );这个SQL读起来有点绕但它确实是合法的而且解决的是“一组里任意一个”或“一组里全部”的比较需求。用之前先想清楚你的业务是“大于任何一个”还是“大于全部”写反了结果差异很大。2.3 SELECT和HAVING里的标量子查询边查边算SELECT子句里也能放子查询它返回一个值作为结果集中的一列。比如我想查每个员工的姓名以及他至今下了多少笔订单SELECT e.emp_name, (SELECT COUNT(*) FROM orders o WHERE o.emp_id e.emp_id) AS order_cnt FROM employee e;这个写法很直观但有个限制SELECT子查询必须返回标量也就是一行一列。如果返回了多行MySQL会直接报错Subquery returns more than 1 row。所以使用前心里要有数被查的那一侧要能保证唯一。HAVING子句里同样可以放子查询。比如“找出平均工资高于全公司平均工资的部门”SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) ( SELECT AVG(salary) FROM employee );注意HAVING里如果用到子查询通常和GROUP BY配合它作用的对象是分组以后的结果不是原始行。很多人误以为HAVING只是高级WHERE其实它是在分组聚合之后再做条件过滤执行顺序和WHERE完全不同。3. 按返回结果拆解标量、单列、单行、整表四种子查询各有脾气位置是子查询“放在哪”返回类型是子查询“返回什么”。这两个维度经常被混在一起连很多教程都没讲清楚。返回类型决定了子查询能用在哪些语法里也决定了报错姿势是什么。3.1 标量子查询只能返回一个值否则直接报错标量子查询返回一行一列也就是一个单独的值。它是最“听话”的一种子查询可以用在几乎任何普通表达式能出现的地方SELECT后、WHERE比较符右边、SET赋值等。SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM employee) AS diff FROM employee;这种写法在报表里特别常见它能在同一行同时显示员工工资和与平均工资的差值。使用时必须注意如果子查询返回多行整个SQL直接报错如果返回零行结果就是NULL不会报错但参与计算时要提防NULL传播。判断一个子查询是不是标量最粗暴的方法是看它有没有“唯一性保证”。比如用主键或唯一键去筛选通常就是标量用GROUP BY后再聚合也一定是标量。反过来条件里不带任何限制的SELECT department_id FROM employee想都不用想一定是多行结果。3.2 行子查询把多个列打包成一个“元组”去比较行子查询返回一行多列。它的用法比较特殊左括号里可以跟多个列右边用一行子查询结果去逐一对应。比如我要找“和员工A部门相同、工资也完全相同的那个人”可以写SELECT emp_id, emp_name, department_id, salary FROM employee WHERE (department_id, salary) ( SELECT department_id, salary FROM employee WHERE emp_id 1 );这里(department_id, salary)是一个行构造器等号右边的子查询通过主键emp_id1保证了只返回一行。两个括号里列的数量必须完全一致位置要一一对应否则MySQL会报列数不匹配的错误。这种写法在日常开发里不算高频但它对理解SQL的“行”这个维度挺有帮助。有时候你想表达“整行和某行一致”用行子查询比拆开写两个条件更贴近业务语义可读性反而更好。3.3 列子查询与IN/ANY/ALL搭配集合关系判断列子查询返回一列多行本质上是一组值的集合。这组值通常喂给IN、ANY、ALL等运算符。IN的语义是“外层值是否属于这个集合”SELECT emp_name FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A区 );这里有个经典性能陷阱如果你写的子查询本身要去重比如上面的结果可能返回多个重复department_idMySQL优化器通常会帮你处理。但你也可以在子查询里显式加DISTINCT让语义更明确。ANY和ALL与列子查询配合时语义要搞清 ANY (...)等价于 IN ANY (...)等价于大于集合里的最小值 ALL (...)等价于大于集合里的最大值比如“找出工资大于市场部所有人工资的员工”SELECT emp_name, salary FROM employee WHERE salary ALL ( SELECT salary FROM employee WHERE department_id 2 );用ALL有个隐藏坑如果右边的子查询结果集为空ALL条件在大多数数据库里会被判定为TRUE。这听起来很反直觉但在逻辑上是自洽的——“大于空集合中的所有值”这个命题不存在反例于是为真。所以写ALL之前一定要确认子查询不会查空。3.4 表子查询本质就是一张临时表表子查询返回多行多列通常只能出现在FROM子句里也就是前面讲的派生表。它的所有行为都可以理解为“凭空生成一张临时表”。SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) 8000;其实把上面这整段包一层括号再加个别名它就是一个表子查询。表子查询最大的价值在于“嵌套”你可以先聚合出一张临时表再基于这张临时表继续查。继续在外面套一层也是合法的但可读性会迅速恶化这也是后面第六章里我要劝你用CTE的原因。四种子查询的特点我常用一张表记返回类型返回形态典型出现位置典型报错标量子查询一行一列SELECT、WHERE比较符右侧Subquery returns more than 1 row行子查询一行多列WHERE行构造器右侧Operand should contain N column(s)列子查询多行一列IN、ANY、ALL右侧一般不报错结果可能为空集表子查询多行多列FROM子句忘记别名导致语法错误4. 相关子查询的执行逻辑外层带一行内层跟着跑一次前面讲的所有子查询执行时基本都是“先算内层把它当成一个固定的结果再算外层”。但有一类子查询不是这个路子它让很多新手栽过跟头那就是相关子查询Correlated Subquery。4.1 非相关子查询先算内层一次再算外层先看个对比。前面写过的“工资高于全公司平均工资”内层是SELECT AVG(salary) FROM employee这个查询结果和任何一行员工都没有关系完全独立。这种就叫非相关子查询它的执行逻辑非常直白先执行一次内层拿到一个常数平均值再去外层一行行比较。性能开销基本可以忽略。SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);这种“先算一次再复用”的模型最简单也是优化的理想形态。MySQL优化器遇到这类子查询通常会缓存结果不会傻傻地每行重新算一遍。4.2 相关子查询逐行“灵魂拷问”用对场景威力巨大相关子查询的内层会引用外层查询的列导致它没法独立执行。外层查一行内层就要带着这一行的参数跑一次。听起来很慢但它能解决一类非相关子查询做不到的问题每一行需要单独计算的对比。典型例子是“找出工资高于本部门平均工资的员工”。这个需求里每个员工的对比基准不一样技术部的员工要跟技术部平均工资比市场部的员工要跟市场部平均工资比。你没法用固定平均值解决只能让内层跟着外层的department_id动态变化SELECT e1.emp_id, e1.emp_name, e1.salary, e1.department_id FROM employee e1 WHERE e1.salary ( SELECT AVG(e2.salary) FROM employee e2 WHERE e2.department_id e1.department_id );执行时MySQL取外层第一行员工A带着它的department_id1去算技术部平均工资比较再取员工B还是department_id1又算一次技术部平均工资到了员工Cdepartment_id变成2再算市场部平均工资。这就是为什么相关子查询被叫作逐行“灵魂拷问”——外层有多少行内层就可能跑多少次。在数据量小的时候这种写法很爽逻辑直接。但表一大相关子查询就是性能杀手。优化思路通常有两个一个是用JOIN加聚合先把每部门的平均工资算成临时表再关联另一个是用窗口函数AVG() OVER(PARTITION BY department_id)MySQL 8.0已经支持写起来更优雅。例如SELECT emp_id, emp_name, salary, department_id FROM ( SELECT emp_id, emp_name, salary, department_id, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary FROM employee ) t WHERE salary dept_avg_salary;这种写法只需要扫一遍表效率比相关子查询高不少。能用窗口函数替代的场景我几乎不太会用相关子查询。4.3 EXISTS / NOT EXISTS存在性判断的正确打开方式EXISTS后面接的子查询不关心返回什么列它只问一个问题子查询会不会返回任何一行。如果返回了哪怕一行EXISTS为真否则为假。所以很多老手会写SELECT 1而不是SELECT *意义在于提醒自己和读者列名无关紧要存在性才重要。比如找“下过订单的员工”SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );EXISTS天然就是相关子查询因为内层通常都要通过外层的员工ID去订单表里找匹配记录。它的优势在于“提前终止”只要找到第一条满足条件的订单这一行员工就算命中不用继续把订单扫完。配合订单表上的emp_id索引效果非常好。这里必须说一个高频坑NOT EXISTS和NOT IN并不等价。NOT IN在遇到子查询结果里有NULL时会直接变成“未知”最终结果往往是空集不是你要的数据。想表达“没有订单记录”这种语义优先用NOT EXISTSSELECT e.emp_id, e.emp_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );很多线上事故就是有人图省事把NOT EXISTS改成了NOT IN然后整个报表查出来一片空白。第六章里我会再把这个NULL坑单独拎出来强调。5. 性能排障现场子查询为什么会慢以及MySQL优化器如何处理与其玄学地背“子查询慢、JOIN快”不如先搞清楚慢在哪个环节。很多情况下子查询被优化器处理得足够好你不比盲目改写。下面这个章节我用实际排障的思路来拆。5.1 三个让子查询变慢的常见原因第一个原因相关子查询的循环次数爆炸。外层有一万行内层就执行一万次即使每次内层只要几毫秒累计起来也是几十秒。这种慢最容易被忽略因为只看单条SQL文本你根本看不出执行次数。第二个原因派生表或子查询结果被反复物化成临时表且没有索引。所谓物化就是优化器把子查询的结果真正落成一张带数据的临时表放在内存或磁盘上。如果这张临时表很大或者外层查询要反复访问它但没有合适的索引性能就直线下降。第三个原因统计信息不准确导致优化器选择了错误策略。MySQL的优化器依赖表的行数和索引基数做成本估算。如果长期没更新统计信息它可能把一个本来能走索引的子查询判断成“物化更划算”或“扫描更划算”最终执行计划很糟糕。遇到诡异变慢的子查询先跑一下ANALYZE TABLE更新统计信息再测试我至少遇到两次是这个问题。5.2 透过EXPLAIN看优化器SUBQUERY、DERIVED、MATERIALIZED定位地址永远是执行计划。MySQL里用EXPLAIN看子查询时重点关注select_type列最常见的几个值是select_type含义PRIMARY最外层查询SUBQUERY非相关子查询通常执行一次DEPENDENT SUBQUERY相关子查询外层每行执行一次DERIVEDFROM子句里的派生表会被物化或合并MATERIALIZED子查询被物化成临时表UNCACHEABLE SUBQUERY每次执行结果都可能变化无法缓存看计划时先找DEPENDENT SUBQUERY只要出现它你就得怀疑循环次数。再看有没有DERIVED或MATERIALIZED如果有注意临时表的大小和是否使用到索引。MySQL 8.0的EXPLAIN FORMATTREE会把子查询的执行关系显示得更直观。比如对这条SQLEXPLAIN FORMATTREE SELECT emp_id, emp_name FROM employee WHERE emp_id IN (SELECT emp_id FROM orders);8.0的执行计划里可能会看到类似“Nested loop semijoin”的节点这说明优化器把IN子查询转换成了半连接semi-join而不是傻傻地物化整个orders表。这种“优化器替你做决定”的情况非常多所以别把“子查询一定慢”当成金科玉律。关于半连接可以简单理解它只拿外层表的每一行去子查询里找“能不能匹配上”匹配上就停不关心子查询里匹配了几条。这比把整个子查询结果先算出来再IN判断要高效很多尤其是在子查询结果集很大的场景。5.3 子查询改写JOIN的实战标准和反例那什么时候值得手动把子查询改写成JOIN我的判断标准很简单先看执行计划确认优化器没有做半连接物化等优化并且这个子查询可以无损地改成JOIN。最典型的改写场景是IN列子查询。例如SELECT e.emp_id, e.emp_name FROM employee e WHERE e.emp_id IN ( SELECT o.emp_id FROM orders o WHERE o.order_amount 100 );可以改写为SELECT DISTINCT e.emp_id, e.emp_name FROM employee e JOIN orders o ON o.emp_id e.emp_id WHERE o.order_amount 100;关键差别在于JOIN会把两表匹配的所有组合都保留而IN只需要“有匹配”所以要多加一个DISTINCT。如果你不加同一员工下过三笔符合条件的订单结果里就会出现三行这是IN与JOIN最常见的语义差异。但也要讲清楚反例。如果子查询里用了聚合函数、窗口函数或者你需要子查询结果保持NULL语义改写就要非常谨慎。比如“查工资高于本部门平均工资的员工”用相关子查询写很直观改写JOIN就得先把部门平均工资聚合出来再关联写法上未必更简单SELECT e.emp_id, e.emp_name, e.salary FROM employee e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) d ON d.department_id e.department_id WHERE e.salary d.avg_salary;这种改写把相关子查询变成了“一表一聚合一关联”执行次数从N次变成扫两次表加一次JOIN才是真正的性能优化。改写前最好用EXPLAIN确认优化器原本的策略确实不理想别为了“看起来专业”去改一段不慢的SQL。6. 我平时写子查询的几条习惯有的坑踩多了才总结出来最后这部分不是语法讲解是我自己写SQL时默认遵守的习惯。不保证每条都适合所有团队但照着做大概率能少挨几次夜班。6.1 逻辑清晰优先性能问题用分析说话同一个需求子查询和JOIN往往都能实现。我的第一选择永远是“谁更能表达业务语义就用谁”。子查询在这方面的优势是它能把“先算什么再算什么”这个过程写得很直白。比如“找出比平均工资高的人”子查询一眼就能看出来先算平均值。如果你直接用自连接JOIN写可能要想半天为什么有两张employee表。不要听风就是雨一听子查询就皱眉。先用EXPLAIN看执行计划再决定要不要改写。很多时候MySQL已经帮你处理得很好手动改写反而画蛇添足。6.2 涉及NULL时IN/NOT IN要格外小心这是我在实际项目里栽得最痛的一次。当时查“没有订单记录的员工”同事写的是NOT IN结果查出来永远是空集。原因就是子查询结果里有NULL。基础规则是这样的如果子查询返回的列表里哪怕有一个NULLNOT IN的结果往往就是“不成立”而不是“找到没匹配的行”。比如SELECT emp_name FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM orders);如果orders表里存在一行emp_id为NULL的记录条件emp_id NOT IN (1, 2, NULL)的逻辑结果不是TRUE或FALSE而是NULL/UNKNOWN。WHERE只保留TRUE行于是什么都不返回。所以凡是写NOT IN我都会先确认子查询列有没有NOT NULL约束。如果没有直接改用NOT EXISTS更安全性能也更好。6.3 嵌套太深就用CTE别叠三层以上子查询可以无限嵌套但不代表我们应该这么干。三层以内勉强能靠缩进读懂超过三层基本只有写它的人能维护一个月后再看就是天书。MySQL 8.0里我强烈建议用CTECommon Table Expression替代深嵌套子查询。比如前面那个“找本部门工资高于平均工资的员工”用CTE写是这样WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) SELECT e.emp_name, e.salary, d.avg_salary FROM employee e JOIN dept_avg d ON e.department_id d.department_id WHERE e.salary d.avg_salary;CTE的另一个好处是同一段中间结果可以被后续多个查询复用而普通子查询只能在一个地方使用。遇到一段逻辑要被引用两次的场景CTE能显著减少SQL膨胀。MySQL 8.0以下的版本没法用CTE那就尽量把嵌套层数控制在两层内或者把复杂逻辑拆成视图。6.4 上线之前用EXPLAIN ANALYZE看一眼真实执行时间写SQL不能靠猜尤其是上线到生产环境的报表SQL。MySQL 8.0.18开始支持EXPLAIN ANALYZE它能真正执行SQL并返回实际执行时间、行数和循环次数比单独看EXPLAIN的估算更可靠。EXPLAIN ANALYZE SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );结果里会显示类似“actual time0.1...0.2 rows1 loops5”的信息。这里loops尤其重要如果loops接近外层行数说明这就是相关子查询在逐行循环那就是下一步优化的主要对象。拿到真实数据后你改不改、改成什么都有了依据而不是凭感觉说“子查询太慢”。我在实际使用中最常做的一件事就是先用小数据量验证逻辑对不对再拿生产数据量做EXPLAIN ANALYZE最后把慢的候选SQL集中优化一遍。MySQL子查询本身没有善恶之分它只是SQL的一部分真正决定快慢的是你对数据结构、索引和优化器行为的理解。把这几个维度串起来你就能在正确的地方用子查询在合适的时候改写它也就不需要再记什么“子查询一定慢”的教条了。
MySQL子查询是个很神奇的东西你刚接触SQL时觉得它难懂写了几年后又发现它是排查慢SQL时最常被拎出来“扣字眼”的地方。前阵子帮团队处理一个报表慢查询SQL里嵌套了三层子查询单次执行要二三十秒拆开分析后才发现不是子查询本身的问题而是很多人把它当成了万能钥匙随手往任何位置塞结果既伤性能又难维护。我干脆把MySQL子查询从头到尾整理了一遍从概念、分类、执行逻辑、优化器行为到改写技巧都揉进这篇文章里适合刚学SQL的人当系统性笔记也适合写了两三年SQL但没认真梳理过子查询的开发者查漏补缺。本文用的全是可以在自己电脑上跑的MySQL 5.7/8.0例子不依赖任何在线环境的特定写法。为了保证尽量全我把它拆成了六个大块先说清楚子查询的底层逻辑然后按“出现位置”和“返回结果”两个维度重新分类再专门讲相关子查询和EXISTS最后是把子查询弄慢的坑和改写思路。直接开始。1. 先把子查询这件事说清楚嵌套的SELECT到底返回了什么很多人背过定义子查询就是“嵌套在其他查询里的SELECT”。但这句话太稀松平常了它没有回答那个最核心的问题一条SELECT塞进另一条SELECT之后到底算什么1.1 子查询的本质一个可以“插”在任意位置的临时结果集一个子查询本质上就是一条完整、独立、用括号包起来的SELECT语句。它的执行结果可能是单个值、一列数据、一行数据也可能是一整张表。外层查询拿到这个结果以后再根据自己的逻辑继续筛选、计算、关联。你可以把它理解成一个函数调用先执行内层拿到一个返回值再把这个返回值塞进外层表达式里。比如最常见的“查工资高于平均工资的员工”脑子里的第一反应通常是先算出平均工资再把员工表和这个平均值比较。写成SQL就是SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);这里的(SELECT AVG(salary) FROM employee)就是子查询它返回了一个数值外层把它当成普通条件来比较。正因为子查询的本质是“临时结果”所以子查询几乎可以出现在SQL语句的任意角落SELECT子句里可以放、FROM子句里可以放、WHERE和HAVING里可以放、ORDER BY后面也能放。这也引出了很多人的困惑点——到底什么位置该放什么类型的子查询答案其实很简单看你需要这个子查询返回什么要一个值就放表达式位置要一张表就放FROM里要一列值就放到IN、ANY、ALL这类集合运算的右边。1.2 建一套测试数据后面所有例子都跑在它上面空口说概念很难有体感我先把后面反复用到的一整套测试表建出来。这个场景很简单部门表、员工表、订单表属于业务系统里最常见的那类数据模型。CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL, location VARCHAR(20) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, department_id INT, salary DECIMAL(10, 2) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, order_amount DECIMAL(10, 2), order_date DATE );插入少量但存在差异的数据方便看出每种查询的含义INSERT INTO department VALUES (1, 技术部, A区), (2, 市场部, B区), (3, 财务部, A区); INSERT INTO employee VALUES (1, 员工A, 1, 10000.00), (2, 员工B, 1, 8000.00), (3, 员工C, 2, 9000.00), (4, 员工D, 2, 7000.00), (5, 员工E, 3, 6000.00); INSERT INTO orders VALUES (1, 1, 500.00, 2024-01-01), (2, 1, 300.00, 2024-01-05), (3, 2, 200.00, 2024-02-01), (4, 3, 400.00, 2024-02-10);先停在几张表上不要急着复制代码。后面每讲一种写法你都回到这个数据模型里跑一遍比死记语法有效得多。2. 按出现位置拆解SELECT、FROM、WHERE、HAVING里都能藏子查询子查询的“位置”决定了它的角色。我见过太多人只会写WHERE后跟IN的子查询然后一碰到复杂报表就傻眼。实际上四个主要位置各有各的用法和注意点。2.1 FROM子句的派生表把子查询当临时表用FROM子句里的子查询在MySQL里叫派生表Derived Table你可以直接把它的查询结果想象成一张临时表然后再去JOIN、再筛选。最典型的场景是先做一层聚合统计再和其他表关联。比如我想看“各部门平均工资超过8000元的部门名称和平均工资”。难点在于部门名称在department表平均工资必须从employee表里先GROUP BY算出来。这时候就适合先把平均工资算成派生表再去关联部门表SELECT d.department_name, t.avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) t JOIN department d ON d.department_id t.department_id WHERE t.avg_salary 8000;有两个坑必须记住。第一MySQL要求派生表必须写别名哪怕你后面完全没用到也要给个名字否则直接报语法错误。第二从MySQL 5.7开始默认会把符合条件的派生表合并进外层查询如果你在派生表里写了ORDER BY但外层没有LIMIT这个ORDER BY很可能被优化器直接忽略排序顺序不保证。正确的做法是要排序就在最外层排不要指望内层排序能稳定传出来。MySQL 8.0.14以后还可以用LATERAL派生表它允许内层子查询引用外层查询的列用来解决“每行动态算一个结果”的问题。但在日常业务里用得不算多先把普通派生表玩明白再说。2.2 WHERE子句的筛选比较、IN、ANY/ALL都在这里WHERE子句里的子查询最常见但它不是只有IN一种写法。它可以出现在普通比较运算符的右边也可以配合IN、NOT IN、ANY、SOME、ALL这些集合运算符。最简单的比较式筛选就是前面那个查工资高于平均工资的例子SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);如果子查询返回的不是一个值而是一列值那就交给IN处理。比如查“部门位置在A区的所有员工”SELECT emp_id, emp_name, department_id FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A区 );ANY和ALL相对冷门但偶尔遇到会很有用。ANY表示“只要比子查询返回的任意一个值大就算满足”等价于大于最小值ALL表示“要比所有值都大才算满足”等价于大于最大值。例如“找出工资大于A区任意部门员工工资的人”可以写成SELECT emp_id, emp_name, salary FROM employee WHERE salary ANY ( SELECT salary FROM employee WHERE department_id IN (1, 3) );这个SQL读起来有点绕但它确实是合法的而且解决的是“一组里任意一个”或“一组里全部”的比较需求。用之前先想清楚你的业务是“大于任何一个”还是“大于全部”写反了结果差异很大。2.3 SELECT和HAVING里的标量子查询边查边算SELECT子句里也能放子查询它返回一个值作为结果集中的一列。比如我想查每个员工的姓名以及他至今下了多少笔订单SELECT e.emp_name, (SELECT COUNT(*) FROM orders o WHERE o.emp_id e.emp_id) AS order_cnt FROM employee e;这个写法很直观但有个限制SELECT子查询必须返回标量也就是一行一列。如果返回了多行MySQL会直接报错Subquery returns more than 1 row。所以使用前心里要有数被查的那一侧要能保证唯一。HAVING子句里同样可以放子查询。比如“找出平均工资高于全公司平均工资的部门”SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) ( SELECT AVG(salary) FROM employee );注意HAVING里如果用到子查询通常和GROUP BY配合它作用的对象是分组以后的结果不是原始行。很多人误以为HAVING只是高级WHERE其实它是在分组聚合之后再做条件过滤执行顺序和WHERE完全不同。3. 按返回结果拆解标量、单列、单行、整表四种子查询各有脾气位置是子查询“放在哪”返回类型是子查询“返回什么”。这两个维度经常被混在一起连很多教程都没讲清楚。返回类型决定了子查询能用在哪些语法里也决定了报错姿势是什么。3.1 标量子查询只能返回一个值否则直接报错标量子查询返回一行一列也就是一个单独的值。它是最“听话”的一种子查询可以用在几乎任何普通表达式能出现的地方SELECT后、WHERE比较符右边、SET赋值等。SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM employee) AS diff FROM employee;这种写法在报表里特别常见它能在同一行同时显示员工工资和与平均工资的差值。使用时必须注意如果子查询返回多行整个SQL直接报错如果返回零行结果就是NULL不会报错但参与计算时要提防NULL传播。判断一个子查询是不是标量最粗暴的方法是看它有没有“唯一性保证”。比如用主键或唯一键去筛选通常就是标量用GROUP BY后再聚合也一定是标量。反过来条件里不带任何限制的SELECT department_id FROM employee想都不用想一定是多行结果。3.2 行子查询把多个列打包成一个“元组”去比较行子查询返回一行多列。它的用法比较特殊左括号里可以跟多个列右边用一行子查询结果去逐一对应。比如我要找“和员工A部门相同、工资也完全相同的那个人”可以写SELECT emp_id, emp_name, department_id, salary FROM employee WHERE (department_id, salary) ( SELECT department_id, salary FROM employee WHERE emp_id 1 );这里(department_id, salary)是一个行构造器等号右边的子查询通过主键emp_id1保证了只返回一行。两个括号里列的数量必须完全一致位置要一一对应否则MySQL会报列数不匹配的错误。这种写法在日常开发里不算高频但它对理解SQL的“行”这个维度挺有帮助。有时候你想表达“整行和某行一致”用行子查询比拆开写两个条件更贴近业务语义可读性反而更好。3.3 列子查询与IN/ANY/ALL搭配集合关系判断列子查询返回一列多行本质上是一组值的集合。这组值通常喂给IN、ANY、ALL等运算符。IN的语义是“外层值是否属于这个集合”SELECT emp_name FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A区 );这里有个经典性能陷阱如果你写的子查询本身要去重比如上面的结果可能返回多个重复department_idMySQL优化器通常会帮你处理。但你也可以在子查询里显式加DISTINCT让语义更明确。ANY和ALL与列子查询配合时语义要搞清 ANY (...)等价于 IN ANY (...)等价于大于集合里的最小值 ALL (...)等价于大于集合里的最大值比如“找出工资大于市场部所有人工资的员工”SELECT emp_name, salary FROM employee WHERE salary ALL ( SELECT salary FROM employee WHERE department_id 2 );用ALL有个隐藏坑如果右边的子查询结果集为空ALL条件在大多数数据库里会被判定为TRUE。这听起来很反直觉但在逻辑上是自洽的——“大于空集合中的所有值”这个命题不存在反例于是为真。所以写ALL之前一定要确认子查询不会查空。3.4 表子查询本质就是一张临时表表子查询返回多行多列通常只能出现在FROM子句里也就是前面讲的派生表。它的所有行为都可以理解为“凭空生成一张临时表”。SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) 8000;其实把上面这整段包一层括号再加个别名它就是一个表子查询。表子查询最大的价值在于“嵌套”你可以先聚合出一张临时表再基于这张临时表继续查。继续在外面套一层也是合法的但可读性会迅速恶化这也是后面第六章里我要劝你用CTE的原因。四种子查询的特点我常用一张表记返回类型返回形态典型出现位置典型报错标量子查询一行一列SELECT、WHERE比较符右侧Subquery returns more than 1 row行子查询一行多列WHERE行构造器右侧Operand should contain N column(s)列子查询多行一列IN、ANY、ALL右侧一般不报错结果可能为空集表子查询多行多列FROM子句忘记别名导致语法错误4. 相关子查询的执行逻辑外层带一行内层跟着跑一次前面讲的所有子查询执行时基本都是“先算内层把它当成一个固定的结果再算外层”。但有一类子查询不是这个路子它让很多新手栽过跟头那就是相关子查询Correlated Subquery。4.1 非相关子查询先算内层一次再算外层先看个对比。前面写过的“工资高于全公司平均工资”内层是SELECT AVG(salary) FROM employee这个查询结果和任何一行员工都没有关系完全独立。这种就叫非相关子查询它的执行逻辑非常直白先执行一次内层拿到一个常数平均值再去外层一行行比较。性能开销基本可以忽略。SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);这种“先算一次再复用”的模型最简单也是优化的理想形态。MySQL优化器遇到这类子查询通常会缓存结果不会傻傻地每行重新算一遍。4.2 相关子查询逐行“灵魂拷问”用对场景威力巨大相关子查询的内层会引用外层查询的列导致它没法独立执行。外层查一行内层就要带着这一行的参数跑一次。听起来很慢但它能解决一类非相关子查询做不到的问题每一行需要单独计算的对比。典型例子是“找出工资高于本部门平均工资的员工”。这个需求里每个员工的对比基准不一样技术部的员工要跟技术部平均工资比市场部的员工要跟市场部平均工资比。你没法用固定平均值解决只能让内层跟着外层的department_id动态变化SELECT e1.emp_id, e1.emp_name, e1.salary, e1.department_id FROM employee e1 WHERE e1.salary ( SELECT AVG(e2.salary) FROM employee e2 WHERE e2.department_id e1.department_id );执行时MySQL取外层第一行员工A带着它的department_id1去算技术部平均工资比较再取员工B还是department_id1又算一次技术部平均工资到了员工Cdepartment_id变成2再算市场部平均工资。这就是为什么相关子查询被叫作逐行“灵魂拷问”——外层有多少行内层就可能跑多少次。在数据量小的时候这种写法很爽逻辑直接。但表一大相关子查询就是性能杀手。优化思路通常有两个一个是用JOIN加聚合先把每部门的平均工资算成临时表再关联另一个是用窗口函数AVG() OVER(PARTITION BY department_id)MySQL 8.0已经支持写起来更优雅。例如SELECT emp_id, emp_name, salary, department_id FROM ( SELECT emp_id, emp_name, salary, department_id, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary FROM employee ) t WHERE salary dept_avg_salary;这种写法只需要扫一遍表效率比相关子查询高不少。能用窗口函数替代的场景我几乎不太会用相关子查询。4.3 EXISTS / NOT EXISTS存在性判断的正确打开方式EXISTS后面接的子查询不关心返回什么列它只问一个问题子查询会不会返回任何一行。如果返回了哪怕一行EXISTS为真否则为假。所以很多老手会写SELECT 1而不是SELECT *意义在于提醒自己和读者列名无关紧要存在性才重要。比如找“下过订单的员工”SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );EXISTS天然就是相关子查询因为内层通常都要通过外层的员工ID去订单表里找匹配记录。它的优势在于“提前终止”只要找到第一条满足条件的订单这一行员工就算命中不用继续把订单扫完。配合订单表上的emp_id索引效果非常好。这里必须说一个高频坑NOT EXISTS和NOT IN并不等价。NOT IN在遇到子查询结果里有NULL时会直接变成“未知”最终结果往往是空集不是你要的数据。想表达“没有订单记录”这种语义优先用NOT EXISTSSELECT e.emp_id, e.emp_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );很多线上事故就是有人图省事把NOT EXISTS改成了NOT IN然后整个报表查出来一片空白。第六章里我会再把这个NULL坑单独拎出来强调。5. 性能排障现场子查询为什么会慢以及MySQL优化器如何处理与其玄学地背“子查询慢、JOIN快”不如先搞清楚慢在哪个环节。很多情况下子查询被优化器处理得足够好你不比盲目改写。下面这个章节我用实际排障的思路来拆。5.1 三个让子查询变慢的常见原因第一个原因相关子查询的循环次数爆炸。外层有一万行内层就执行一万次即使每次内层只要几毫秒累计起来也是几十秒。这种慢最容易被忽略因为只看单条SQL文本你根本看不出执行次数。第二个原因派生表或子查询结果被反复物化成临时表且没有索引。所谓物化就是优化器把子查询的结果真正落成一张带数据的临时表放在内存或磁盘上。如果这张临时表很大或者外层查询要反复访问它但没有合适的索引性能就直线下降。第三个原因统计信息不准确导致优化器选择了错误策略。MySQL的优化器依赖表的行数和索引基数做成本估算。如果长期没更新统计信息它可能把一个本来能走索引的子查询判断成“物化更划算”或“扫描更划算”最终执行计划很糟糕。遇到诡异变慢的子查询先跑一下ANALYZE TABLE更新统计信息再测试我至少遇到两次是这个问题。5.2 透过EXPLAIN看优化器SUBQUERY、DERIVED、MATERIALIZED定位地址永远是执行计划。MySQL里用EXPLAIN看子查询时重点关注select_type列最常见的几个值是select_type含义PRIMARY最外层查询SUBQUERY非相关子查询通常执行一次DEPENDENT SUBQUERY相关子查询外层每行执行一次DERIVEDFROM子句里的派生表会被物化或合并MATERIALIZED子查询被物化成临时表UNCACHEABLE SUBQUERY每次执行结果都可能变化无法缓存看计划时先找DEPENDENT SUBQUERY只要出现它你就得怀疑循环次数。再看有没有DERIVED或MATERIALIZED如果有注意临时表的大小和是否使用到索引。MySQL 8.0的EXPLAIN FORMATTREE会把子查询的执行关系显示得更直观。比如对这条SQLEXPLAIN FORMATTREE SELECT emp_id, emp_name FROM employee WHERE emp_id IN (SELECT emp_id FROM orders);8.0的执行计划里可能会看到类似“Nested loop semijoin”的节点这说明优化器把IN子查询转换成了半连接semi-join而不是傻傻地物化整个orders表。这种“优化器替你做决定”的情况非常多所以别把“子查询一定慢”当成金科玉律。关于半连接可以简单理解它只拿外层表的每一行去子查询里找“能不能匹配上”匹配上就停不关心子查询里匹配了几条。这比把整个子查询结果先算出来再IN判断要高效很多尤其是在子查询结果集很大的场景。5.3 子查询改写JOIN的实战标准和反例那什么时候值得手动把子查询改写成JOIN我的判断标准很简单先看执行计划确认优化器没有做半连接物化等优化并且这个子查询可以无损地改成JOIN。最典型的改写场景是IN列子查询。例如SELECT e.emp_id, e.emp_name FROM employee e WHERE e.emp_id IN ( SELECT o.emp_id FROM orders o WHERE o.order_amount 100 );可以改写为SELECT DISTINCT e.emp_id, e.emp_name FROM employee e JOIN orders o ON o.emp_id e.emp_id WHERE o.order_amount 100;关键差别在于JOIN会把两表匹配的所有组合都保留而IN只需要“有匹配”所以要多加一个DISTINCT。如果你不加同一员工下过三笔符合条件的订单结果里就会出现三行这是IN与JOIN最常见的语义差异。但也要讲清楚反例。如果子查询里用了聚合函数、窗口函数或者你需要子查询结果保持NULL语义改写就要非常谨慎。比如“查工资高于本部门平均工资的员工”用相关子查询写很直观改写JOIN就得先把部门平均工资聚合出来再关联写法上未必更简单SELECT e.emp_id, e.emp_name, e.salary FROM employee e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) d ON d.department_id e.department_id WHERE e.salary d.avg_salary;这种改写把相关子查询变成了“一表一聚合一关联”执行次数从N次变成扫两次表加一次JOIN才是真正的性能优化。改写前最好用EXPLAIN确认优化器原本的策略确实不理想别为了“看起来专业”去改一段不慢的SQL。6. 我平时写子查询的几条习惯有的坑踩多了才总结出来最后这部分不是语法讲解是我自己写SQL时默认遵守的习惯。不保证每条都适合所有团队但照着做大概率能少挨几次夜班。6.1 逻辑清晰优先性能问题用分析说话同一个需求子查询和JOIN往往都能实现。我的第一选择永远是“谁更能表达业务语义就用谁”。子查询在这方面的优势是它能把“先算什么再算什么”这个过程写得很直白。比如“找出比平均工资高的人”子查询一眼就能看出来先算平均值。如果你直接用自连接JOIN写可能要想半天为什么有两张employee表。不要听风就是雨一听子查询就皱眉。先用EXPLAIN看执行计划再决定要不要改写。很多时候MySQL已经帮你处理得很好手动改写反而画蛇添足。6.2 涉及NULL时IN/NOT IN要格外小心这是我在实际项目里栽得最痛的一次。当时查“没有订单记录的员工”同事写的是NOT IN结果查出来永远是空集。原因就是子查询结果里有NULL。基础规则是这样的如果子查询返回的列表里哪怕有一个NULLNOT IN的结果往往就是“不成立”而不是“找到没匹配的行”。比如SELECT emp_name FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM orders);如果orders表里存在一行emp_id为NULL的记录条件emp_id NOT IN (1, 2, NULL)的逻辑结果不是TRUE或FALSE而是NULL/UNKNOWN。WHERE只保留TRUE行于是什么都不返回。所以凡是写NOT IN我都会先确认子查询列有没有NOT NULL约束。如果没有直接改用NOT EXISTS更安全性能也更好。6.3 嵌套太深就用CTE别叠三层以上子查询可以无限嵌套但不代表我们应该这么干。三层以内勉强能靠缩进读懂超过三层基本只有写它的人能维护一个月后再看就是天书。MySQL 8.0里我强烈建议用CTECommon Table Expression替代深嵌套子查询。比如前面那个“找本部门工资高于平均工资的员工”用CTE写是这样WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) SELECT e.emp_name, e.salary, d.avg_salary FROM employee e JOIN dept_avg d ON e.department_id d.department_id WHERE e.salary d.avg_salary;CTE的另一个好处是同一段中间结果可以被后续多个查询复用而普通子查询只能在一个地方使用。遇到一段逻辑要被引用两次的场景CTE能显著减少SQL膨胀。MySQL 8.0以下的版本没法用CTE那就尽量把嵌套层数控制在两层内或者把复杂逻辑拆成视图。6.4 上线之前用EXPLAIN ANALYZE看一眼真实执行时间写SQL不能靠猜尤其是上线到生产环境的报表SQL。MySQL 8.0.18开始支持EXPLAIN ANALYZE它能真正执行SQL并返回实际执行时间、行数和循环次数比单独看EXPLAIN的估算更可靠。EXPLAIN ANALYZE SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );结果里会显示类似“actual time0.1...0.2 rows1 loops5”的信息。这里loops尤其重要如果loops接近外层行数说明这就是相关子查询在逐行循环那就是下一步优化的主要对象。拿到真实数据后你改不改、改成什么都有了依据而不是凭感觉说“子查询太慢”。我在实际使用中最常做的一件事就是先用小数据量验证逻辑对不对再拿生产数据量做EXPLAIN ANALYZE最后把慢的候选SQL集中优化一遍。MySQL子查询本身没有善恶之分它只是SQL的一部分真正决定快慢的是你对数据结构、索引和优化器行为的理解。把这几个维度串起来你就能在正确的地方用子查询在合适的时候改写它也就不需要再记什么“子查询一定慢”的教条了。
