SQL嵌套查询是指在一个SQL查询的语句中，包含另一个完整的查询语句，常见于子查询、IN子查询、EXISTS子查询等场景。当数据量较小时，嵌套查询的执行效率通常可以满足业务需求，但数据量倍增后，查询耗时可能从几十毫秒突然上升到几秒甚至几十秒，这种现象大多和执行计划的阈值跳变直接相关。
SQL嵌套查询的基本执行逻辑
嵌套查询的执行逻辑会根据子查询的类型有所不同，常见的执行方式有两种：
- 子查询先执行：先执行内层子查询得到结果集，再将结果集作为外层查询的过滤条件，比如
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status=1)这类IN子查询，在数据量较小时通常会采用这种方式。 - 合并执行：数据库优化器将内层子查询和外层查询合并，生成统一的执行计划，避免子查询结果的临时存储，比如部分EXISTS子查询会被优化成连接查询执行。
执行计划阈值跳变是什么
数据库优化器在生成执行计划时，会根据统计信息估算不同执行方式的成本，选择成本最低的方案。这里存在一个阈值，当子查询返回的结果集大小、表的数据量等统计信息超过这个阈值时，优化器会切换执行方式，这就是执行计划阈值跳变。
比如MySQL中对于IN子查询，有一个semijoin相关的优化阈值，当子查询预估返回行数小于阈值时，会采用子查询先执行的方式，当超过阈值时，会切换为物化子查询或者转换为连接查询的方式，两种方式的性能差异可能非常大。
数据量倍增后阈值跳变导致变慢的原因
当数据量倍增后，统计信息发生变化，很容易触发执行计划的阈值跳变，常见的性能问题场景如下：
1. 子查询物化带来的额外开销
当子查询返回的结果集超过阈值时，优化器可能会选择将子查询结果物化到临时表中，这个过程需要额外的IO和内存开销，如果临时表无法放在内存中，还会触发磁盘临时表，性能会大幅下降。
2. 连接顺序错误
切换执行计划后，优化器可能选择错误的连接顺序，比如原本小表驱动大表的执行方式，跳变后变成大表驱动小表，导致扫描行数成倍增加。
3. 索引失效
新的执行计划可能没有使用到合适的索引，比如原本子查询可以使用索引过滤，跳变后转换为全表扫描，数据量倍增后全表扫描的耗时就会急剧上升。
如何分析执行计划阈值跳变
可以通过查看SQL的执行计划，对比数据量变化前后的执行计划差异，定位阈值跳变的问题。
查看执行计划的方法
以MySQL为例，使用EXPLAIN命令查看执行计划：
-- 查看嵌套查询的执行计划
EXPLAIN SELECT * FROM user_table
WHERE user_id IN (
SELECT user_id FROM order_table WHERE order_status = 1
);
执行计划关键字段说明
| 字段名 | 说明 |
|---|---|
| select_type | 查询类型，SUBQUERY表示子查询，MATERIALIZED表示物化子查询，PRIMARY表示外层查询 |
| type | 访问类型，ALL表示全表扫描，ref表示索引查找，range表示范围扫描 |
| rows | 预估扫描行数，数值越大性能越差 |
| Extra | 额外信息，Using temporary表示使用临时表，Using filesort表示使用文件排序 |
如果数据量倍增后，select_type从SUBQUERY变成MATERIALIZED，或者type从ref变成ALL，同时rows数值大幅上升，就说明发生了执行计划阈值跳变。
解决嵌套查询阈值跳变变慢的优化方案
- 改写嵌套查询为连接查询：大部分嵌套查询都可以改写为JOIN查询，避免优化器选择不稳定的执行计划，比如上面的IN子查询可以改写为：
SELECT DISTINCT u.* FROM user_table u JOIN order_table o ON u.user_id = o.user_id WHERE o.order_status = 1;
- 更新统计信息：如果统计信息过期，优化器的估算会不准确，及时更新表的统计信息，让优化器能做出正确的执行计划选择。
- 强制指定执行方式：部分数据库支持执行计划提示，比如MySQL可以使用
STRAIGHT_JOIN强制连接顺序，避免优化器选择错误的执行计划。 - 增加合适的索引：在子查询的过滤字段、连接字段上增加索引，即使执行计划跳变，也能保证查询使用索引，减少性能下降的幅度。
需要注意的是，不同数据库的优化器逻辑和阈值设置不同，分析时需要结合对应数据库的官方文档，定位具体的阈值参数，才能更精准地解决问题。