提到SQL子查询，不知道你有没有过这样的阶段：刚写报表那会儿，为了找“每个类目销量最高的商品”，我在WHERE后面套了一层又一层IN，跑出结果的那一刻还挺得意，等线上数据涨到几十万行，DBA在群里甩了一句“这条SQL谁写的”，才明白什么叫打脸。后来在慢查询日志里反复看到自己埋的雷，才老老实实把子查询这套东西从头捋了一遍。
这篇文章不念文档，我按踩过的坑和用顺手的套路来写：先讲子查询的底层执行逻辑，再给一套能直接抄的标准写法，然后对比JOIN、CTE、窗口函数怎么选，后半部分重点聊慢子查询的优化实录，以及复购率、数据去重这些真实业务场景里最常用的配方。不管你是刚入行的开发，还是已经在写复杂报表的同事，应该都能找到能直接拿走的东西。
1. 子查询到底是什么：先搞清楚底层执行逻辑
1.1 三类分类和两种执行方式，决定性能走向
子查询说到底是嵌套在其他SQL语句（SELECT、INSERT、UPDATE、DELETE）里的SELECT查询。它出现的位置很多：SELECT列表、WHERE条件、FROM位置、HAVING条件，甚至ORDER BY里。按返回结果，常见分三类：
- 标量子查询：返回一行一列，相当于一个单值，配合
=、>、<使用。 - 列子查询/行子查询：返回多行一列或一行多列，配合
IN、ANY、ALL使用。 - 表子查询（派生表）：返回多行多列，出现在FROM后面，被当成临时表。
但真正决定性能的，不是返回类型，而是执行方式。这一块很多人一开始没搞明白：
- 非相关子查询：先执行内层，拿到完整结果集，再把结果交给外层。逻辑顺序是先内后外。
- 相关子查询：内层SQL引用了外层的列，外层每一行产生后，内层都要重新执行一次。本质是一种嵌套循环。
生活类比一下：查“公司里工龄满三年的人”，你完全可以先把名单列出来，再逐个核对，这是独立子查询；但如果问“每个部门工龄最短的是谁”，你就得挨个部门去排查一遍，这是相关子查询。相关子查询不是不能写，而是很多人没意识到它“一行一查”的特性，数据量一旦上来，慢得毫无意外。
1.2 为什么有的子查询特别慢：EXPLAIN里的DEPENDENT SUBQUERY
我在MySQL里排查慢SQL，第一反应永远是EXPLAIN。如果看到Extra列出现DEPENDENT SUBQUERY，基本说明这条子查询是相关子查询，走的是逐行执行。举个例子：
EXPLAIN SELECT * FROM orders o WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.level = 'vip');如果优化器选择把子查询物化，你会在Extra里看到Materialize或者Select tables optimized away，这还算好；但如果看到DEPENDENT SUBQUERY，意味着orders表每一行都要去走一次users表。orders有10万行，users 5万行，那最坏情况就是10万次索引查找，如果users表上的索引还不合适，基本是灾难现场。
MySQL从5.6开始对IN类子查询引入了semi-join半连接优化，5.7之后更成熟，所以不是说所有子查询都慢到不能碰，而是你得会看执行计划。判断标准很简单：先看type列有没有从ALL变成range、ref、eq_ref、const，再看Extra里有没有DEPENDENT字样。这两个地方看懂，一条子查询“为什么慢”基本就能说清楚了。
2. 常用且容易被写错的四类子查询实战
2.1 WHERE后面用IN和比较运算：三个坑踩一次就记住
WHERE后面接子查询是最常见的用法，用来限定主查询的过滤范围。比如：
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level = 'vip');这个写法的意思是“先找出VIP用户ID集合，再取这些用户的订单”。逻辑没问题，但坑都在细节里。
第一个坑是NULL。如果子查询结果里有NULL，那IN的结果不是TRUE也不是FALSE，而是NULL，外层查询会直接过滤掉本应匹配的行。尤其是NOT IN，子查询结果只要含一个NULL，整个外层查询可能一行都不返回。遇到这种需求，我一般建议改成NOT EXISTS，语义更准，执行计划往往也更友好。
第二个坑是子查询返回多列。WHERE user_id IN (SELECT id, name FROM users WHERE ...)这种写法，MySQL直接报Operand should contain 1 column(s)。原因很简单：IN后面的集合只能是单列。
第三个坑是权限和过滤条件。子查询里忘了加状态过滤会导致结果集过大，比如查“最近30天活跃VIP的订单”，你得确保活跃条件写在子查询里，而不是在外层JOIN之后再过滤。在子查询里先缩小结果集，永远比在外层靠WHERE硬筛高效。
2.2 SELECT列表里的标量子查询：只能返回一行一列
SELECT列表里放子查询，用来给主查询的每一行追加一个“计算出来的列”。我最早写报表特别喜欢这么干：
SELECT u.id, u.username, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt FROM users u;这种写法跑起来没问题，但它是一个典型的相关子查询：users表每返回一行，orders表就被查一次。如果users有1万行，这个标量子查询就要执行1万次。数据量小的时候无所谓，一旦users表变大，or orders表没有索引，性能就崩了。
另外，标量子查询返回结果只能是“一行一列”，如果子查询查出了多行，MySQL会直接报错：
ERROR 1242 (21000): Subquery returns more than 1 row所以我的经验是：标量子查询只适合“按主键或唯一索引取单值”的场景，比如取用户最近一次登录时间。如果一个外层行需要同时取订单数、总金额、最近购买时间三个指标，就不要写三个标量子查询，改成先GROUP BY聚合一次生成派生表，再JOIN进来，SQL的可维护性会好很多。
2.3 FROM后面的派生表：不要忘了别名
FROM后面接子查询，是把子查询结果当作临时表来用，这种写法叫派生表。比如查每个部门业务线的工资峰值，可以这么写：
SELECT dept_id, MAX(salary) FROM ( SELECT dept_id, salary FROM emp WHERE hire_date >= '2020-01-01' ) t GROUP BY dept_id;注意最后那个t，MySQL要求派生表必须有别名。没有别名，解析器根本不知道怎么引用这张表。这个报错信息看起来像语法问题，实际是“给表起个名字”这种基本规范。
派生成表还有一个隐藏问题：它可能被物化成一个临时表，如果派生表数据量大，会占用内存或磁盘临时空间。MySQL 8.0里的derived_merge优化可以把部分简单派生表合并到主查询里，避免物化，但遇到GROUP BY、DISTINCT这类操作时还是会物化。所以我的习惯是：派生表尽量在内部先做聚合、去重、过滤，把结果集压小再给外层用，这也是“小表驱动大表”的核心思路。
2.4 UPDATE和DELETE里更新同一张表：必须包一层
这是我几乎每年都能在代码评审里遇到一次的经典问题。需求很常见：把某个部门工资低于平均的员工统一加薪10%。直观写法：
UPDATE emp SET salary = salary * 1.1 WHERE id IN (SELECT id FROM emp WHERE dept_id = 10);MySQL会直接丢出来一段很熟悉的报错：
ERROR 1093 (HY000): You can't specify target table 'emp' for update in FROM clause原因很简单：你正在UPDATE的emp表，同时又被子查询读取，MySQL担心“边读边写”会产生不可预期的结果，所以直接禁止。解决办法就是再套一层派生表，让MySQL先把数据物化出来，脱离原表：
UPDATE emp SET salary = salary * 1.1 WHERE id IN (SELECT eid FROM (SELECT id AS eid FROM emp WHERE dept_id = 10) t);DELETE同理。这层“套娃”看起来有点丑，但是官方认可的解法，也是面试里常问的“MySQL不允许修改同一张表”的标准答案。对这个报错的正确反应不应该是“换一种数据库”，而是明白：一条SQL里如果你要动一张表，就别在同一层直接读它，给数据一个中间层。
3. 子查询之外的三种替代方案怎么选
3.1 JOIN、CTE、窗口函数：先看这个对比再决定
子查询不是解决问题的唯一手段，尤其当它变得难以阅读、性能又差的时候，就得考虑替代方案。我按实际使用频率排个对比：
| 替代方案 | 适用场景 | 限制与注意点 | 典型例子 |
|---|---|---|---|
| JOIN | 需要把两张表的列平铺在一起做关联或聚合 | 一对多会让行数膨胀，需要COUNT(DISTINCT)或先聚合再JOIN | 订单表JOIN用户表取用户昵称 |
| CTE（WITH） | 子查询被多处引用、需要递归查询、想让SQL读起来有层次 | MySQL 8.0+才支持，5.7及以下不能用 | WITH t AS (SELECT ...) SELECT ... |
| 窗口函数 | 分组内排行、同组比较、累计值 | MySQL 8.0+支持，Hive、SQL Server较新版也支持，5.7无 | ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) |
举个典型例子：每个部门工资前三名的员工。用相关子查询也能写：
SELECT * FROM emp e WHERE (SELECT COUNT(*) FROM emp e2 WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary) < 3;这条SQL逻辑上是“统计比自己工资高的人数，小于3就是前三”，完全正确但非常绕，而且emp大表上就是典型的DEPENDENT SUBQUERY，性能隐患很大。换成窗口函数，一眼能看懂：
SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rk FROM emp e ) t WHERE rk <= 3;窗口函数把“分组排行”这个操作内置了，性能和可读性双赢，所以MySQL 5.7用户升级到8.0之后，这类需求强烈建议直接用窗口函数。
3.2 别急着替代：有些场景子查询反而是最优解
但要说清楚，我不是让你把所有子查询都改写成JOIN或窗口函数。现实中有些场景，子查询就是最合适的选择。
第一个是EXISTS。判断“这个用户有没有下过单”，用EXISTS既能表达语义，又能借助索引提前终止扫描。生活类比：判断“这个班有没有人迟到”，只要看到一个人迟到就可以返回结果，不需要遍历完所有人。EXISTS天然干这个事。
第二个是HAVING里跟聚合值比较。比如“找出平均工资高于公司平均的部门”，这个场景必须引用聚合结果，子查询写起来最直接：
SELECT dept_id, AVG(salary) avg_sal FROM emp GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM emp);第三个是在Hive这类分析引擎里，有些NOT IN场景改LEFT JOIN加IS NULL也能做，但子查询语义更贴近原始需求，反而容易让人看出业务意图。子查询存在的意义并不仅仅是性能，更多时候是表达“我要的是一批满足条件的值”。读懂需求再选写法，比无脑套用某个“最佳实践”重要得多。
4. 慢子查询优化实录：定位、分析、改造三步走
4.1 三步定位慢子查询：慢查询日志和EXPLAIN怎么用
线上遇到一条SQL很慢，第一步不是猜，而是先让它自己说话。在MySQL里可以临时打开慢查询日志：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;超过2秒的查询就会被记录到慢查询日志文件里。然后可以用mysqldumpslow工具按执行次数、耗时排序，把最离谱的几条捞出来。拿到具体SQL之后，最关键的一步是EXPLAIN。
EXPLAIN SELECT * FROM orders o WHERE o.user_id IN (SELECT u.id FROM users u WHERE u.last_login < '2023-01-01');EXPLAIN输出里，主要看三列：
- type列：聚簇扫描ALL最差，index全索引扫描稍好，range、ref、eq_ref、const依次变好。
- key列：实际用到的索引，如果为NULL说明没走索引。
- Extra列：如果出现
DEPENDENT SUBQUERY、Using temporary、Using filesort，基本可以锁定问题了。
我见过太多人一上来就“加索引、改JOIN”一顿操作，结果连EXPLAIN都没跑过。正确顺序永远是：先看清楚执行计划，再说优化方向。MySQL 8.0还提供了EXPLAIN ANALYZE，可以直接看到每一步的实际执行时间，比自己脑补靠谱得多。
4.2 三个改造案例：从IN到EXISTS，从大派生表到先聚合
案例1：IN子查询导致的驱动表选择不当。
原始SQL长这样，orders表很大，users表也不小：
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 0 AND last_login < '2022-01-01');EXPLAIN里如果发现orders作为驱动表全表扫了，就是优化器选择了不合理的驱动方向。改造思路两条：一是确认users表status和last_login上有合适的联合索引，让子查询本身很快；二是手动改写为EXISTS，让执行计划按外层小表驱动：
SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 0 AND u.last_login < '2022-01-01' );注意MySQL 5.7和8.0对IN的semi-join优化已经比较到位，不是所有IN改成EXISTS都有质变。我一般先用EXPLAIN对比两条语句，哪个执行计划好就用哪个，避免“玄学优化”。
案例2：派生表过大，聚合放错了位置。
有些同事写统计SQL，喜欢把明细数据全部放进派生表，再在外层做GROUP BY：
SELECT u.username, t.total_amount FROM users u LEFT JOIN ( SELECT user_id, amount FROM orders WHERE create_time >= '2024-01-01' ) t ON u.id = t.user_id GROUP BY u.username;这个写法会导致orders的数据量大时，物化出一张大临时表，内存和磁盘都吃紧。正确做法是把聚合放在派生表内部，让临时表先瘦身：
SELECT u.username, t.total_amount FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM orders WHERE create_time >= '2024-01-01' GROUP BY user_id ) t ON u.id = t.user_id;一句话总结：在子查询里先做过滤和聚合，压缩结果集，再让外层JOIN或查询。把“大而全”的数据带到外层，是派生表性能差的头号原因。
案例3：子查询条件列没有索引。
这个最简单也最容易被忽略。子查询的WHERE列如果没索引，哪怕子查询逻辑写得再好，也是全表扫描。我当时给一个有问题的SQL做优化，EXPLAIN一眼扫过去，内层WHERE dept_id = ?那一列没有索引，type是ALL。建完索引：
CREATE INDEX idx_dept_id ON emp(dept_id);再跑EXPLAIN，内层的type从ALL变成了ref，整体SQL耗时直接降了一个数量级。优化子查询的时候，先检查内层条件列和外层关联列的索引，永远是最低成本高回报的动作。
5. 子查询在真实业务场景里的三个经典配方
5.1 复购率SQL：先聚合再统计，子查询是天然阶梯
复购率是电商、零售运营里最高频的指标之一。它的定义一般是“在统计周期内下单2次及以上的用户占下单用户的比例”。直接写一条SQL统计，子查询是绕不开的：
SELECT CONCAT(ROUND(SUM(repurchase_flag) / COUNT(*) * 100, 2), '%') AS repurchase_rate FROM ( SELECT user_id, CASE WHEN COUNT(DISTINCT DATE(order_time)) >= 2 THEN 1 ELSE 0 END AS repurchase_flag FROM orders WHERE order_time >= '2024-01-01' AND order_time < '2025-01-01' GROUP BY user_id ) t;这里派生表的逻辑是：先把每个用户的下单天数算出来，打上复购标记，再在外层做“有复购用户/总用户”的比例计算。这种“内层细粒度，外层粗粒度”的两层结构，其实是子查询最标准的应用方式。如果还要统计复购用户里的VIP占比，只需要再嵌套一层或JOIN用户表，结构依然是这个思路。
5.2 一条SQL清理重复数据：三种写法对拍
数据重复是个老生常谈的话题。接口超时重试、脚本重复执行、并发插入，都会让表里出现重复记录。最经典的场景是同一张表里根据某个业务键去重，保留最小ID。
第一种写法，直白的NOT IN，但在MySQL里会碰到前面说的1093报错。所以必须包一层：
DELETE FROM t WHERE id NOT IN ( SELECT mid FROM ( SELECT MIN(id) AS mid FROM t GROUP BY business_key ) temp );第二种写法，JOIN DELETE，我觉得最好理解，也不需要套那么多层：
DELETE t1 FROM t t1 JOIN t t2 ON t1.business_key = t2.business_key AND t1.id > t2.id;意思就是保留每个business_key里ID最小的那一行，其余的删掉。“自己JOIN自己”在DELETE里是允许的，而且执行效率通常不错。
第三种写法，窗口函数配一版，适合MySQL 8.0：
DELETE FROM t WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY id) rn FROM t ) tmp WHERE rn > 1 );注意这里即使有窗口函数，外层DELETE的IN子查询依然需要再包一层，否则还是会撞上1093。三条SQL结果一致，选哪条取决于你的MySQL版本和习惯。个人偏好JOIN DELETE，因为它不需要嵌套那么多层，排查逻辑也直观。
5.3 AI生成SQL和MyBatis-Plus建表：子查询依然是基本功
最近看到不少同事用AI生成SQL，确实方便，但我建议生成后务必人工核两件事：第一，表名和列名有没有真实对应表结构，AI很容易“一本正经地编造字段”；第二，语法版本兼容性，AI特别爱给窗口函数，如果你的库还是MySQL 5.7，跑都跑不起来。
AI生成子查询也经常犯三个错：标量子查询返回多行没用IN包一层、NOT IN没考虑NULL导致结果为空、相关子查询用在大表上却没有索引。这些都是本文前面聊过的基础问题，会看执行计划，就不会被AI带偏。
再说MyBatis-Plus根据Java实体类生成建表SQL这个方向。严格说MyBatis-Plus本身不直接干这事，但可以自己写一个启动时自动建表的工具类：反射实体类的@TableName、@TableId、@TableField注解，拼CREATE TABLE语句，字段类型做映射，比如String映射成VARCHAR(255)、Integer映射成INT、BigDecimal映射成DECIMAL(18,2)、LocalDateTime映射成DATETIME。核心思路给个简版：
TableInfo tableInfo = TableInfoHelper.getTableInfo(entityClass); String tableName = tableInfo.getTableName(); List<TableFieldInfo> fieldList = tableInfo.getFieldList(); // 拼接 CREATE TABLE IF NOT EXISTS ...这个工具能省去手工维护建表脚本的麻烦。但建表只是起点，真正跨表统计时，JOIN和子查询还是得手写或拼出来。我见过太多人实体类字段建得飞起，结果报表SQL里一条关联子查询都写不利索。工具能帮你生成表，帮不了你生成业务逻辑，SQL基本功始终得自己过关。
6. 常见报错与排查记录：一张表速查
6.1 高频报错对照表
我把这几年实际遇到的子查询报错整理成一张速查表，覆盖MySQL和SQL Server两类常见库：
| 报错信息 | 常见原因 | 快速处理 |
|---|---|---|
| 1242 Subquery returns more than 1 row | 标量子查询返回多行 | 加LIMIT 1，或根据业务改成IN |
| 1241 Operand should contain 1 column(s) | IN后的子查询返回了多列 | 检查子查询SELECT的列数，确保单列 |
| 1093 You can't specify target table for update | UPDATE/DELETE时读取了同一张表 | 再包一层派生表 |
| Conversion failed converting date and/or time from character string | SQL Server中字符串与DATETIME比较失败 | 统一CAST为DATETIME类型再比较 |
| You have an error in your SQL syntax | 括号不匹配、派生表缺别名 | 逐个括号检查，补上表别名 |
| Subquery returns more than 1 row导致UPDATE失败 | 用子查询给列赋值时结果多行 | 改为聚合函数，或用窗口函数取一条 |
SQL Server那个日期转换的报错，实际场景很典型：子查询返回的类型是字符串，外层要跟一个DATETIME列比较，SQL Server在隐式转换时直接报错。解决办法就是显式转换：
SELECT * FROM t WHERE create_time >= CAST((SELECT max_str_date FROM config WHERE id = 1) AS DATETIME);这类问题排查时别急着改业务逻辑，先确认两边数据类型是不是同一个“物种”。
6.2 安全底线：子查询相关的SQL注入必须堵死
最后必须说一个安全问题。为什么SQL注入那么顽固？因为很多系统还在用字符串拼接SQL。比如登录场景写出的代码可能是这样的：
String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";如果用户在username框里输入' OR '1'='1，拼出来的SQL条件就变成：
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'xxx';'1'='1'恒为真，登录直接被绕过。子查询也可以被注入利用，比如在IN子查询的位置拼一段恶意查询，读取不该看的数据。这不是危言耸听，而是任何一个写SQL的人都应该默认知道的底线。
防御手段其实不复杂：MyBatis里用#{}参数占位符，而不是${}直接拼接；JDBC用PreparedStatement；存储过程里避免拼动态SQL；账户单独授权，应用账号不给DROP、TRUNCATE权限。安全这件事，不需要你把所有攻击手法都背下来，只需要记住一条原则：用户输入永远只能是“数据”，永远不能变成“SQL结构”。
最后聊几句经验
把SQL子查询从头到尾捋一遍之后，我最大的感受是：子查询本身不是性能问题的根源，真正的问题是我们不清楚它的执行方式就随手乱用。我现在的习惯是，写一条带子查询的SQL之前，先问自己三句话：它是独立执行还是逐行执行？内层结果集会很大吗？条件列有没有索引？然后顺手跑一次EXPLAIN验证。能用窗口函数和CTE提高可读性的地方，我会尽量用，但遇到EXISTS这种天然适合的场景，也绝不会为了“代码好看”去硬改JOIN。说到底，SQL没有银弹，只有理解数据分布和使用场景之后的选择。如果你刚开始接触子查询，我的建议就一条：多建两张表，造点重复数据和订单数据，把文里的每一条SQL都敲一遍，报错亲手踩一遍。踩过的坑长了记性，下次写出来的SQL，就真的不一样了。
