SQL子查询优化技巧

可视化演示

展示正态分布、均匀分布和指数分布的概率密度函数曲线，动态演示参数变化对分布形状的影响，含随机采样散点动画。

子查询基础：助理的两种汇报模式

老板要一份华东大客户的订单清单，助理小林有两种办事方式：一种是先跑去档案室，把华东客户名单一次性抄回来，再对照订单逐条审批——跑腿只花一趟；另一种是老板手里攥着订单，每看一行就问一句“这客户是华东的吗？”，小林就得跑去档案室查一次，再跑回来汇报——订单有多少行，他就要跑多少趟。同样是子查询，执行方式的天壤之别从此开始。

SQL里这两种方式对应两类子查询。独立子查询（Uncorrelated Subquery）不引用外层任何列，像“抄名单”，数据库先执行它一次，把结果缓存起来供外层使用；相关子查询（Correlated Subquery）引用外层表的列，外层每一行都要触发一次内层执行，跑腿次数被外层行数成倍放大。理解两者的执行顺序差异，是判断一条查询“快不快”的直觉起点。

假设外层订单表有 M 行，内层客户表有 N 行。独立子查询的成本约等于“一次物化 + M 次哈希探测”；相关子查询在最坏情况下要执行 M 次内层扫描，每次扫描 N 行，总成本接近 M×N 行的读取量。这就是为什么大表上的相关子查询会突然拖垮数据库——它不是慢了一点，而是直接把执行次数放大了一个数量级。

$$\displaystyle C_{indep} = C_{once} + M \times C_{probe}, \qquad C_{corr} = M \times C_{scan}$$

1. C_once：独立子查询只执行一次内层查询的固定成本，对应“抄一次名单”

$$\displaystyle C_{once}$$

2. M × C_probe：外层 M 行每行在子查询结果集合中做索引或哈希探测，单次探测很快

$$\displaystyle M \times C_{probe}$$

3. M × C_scan：相关子查询对外层 M 行各执行一次完整内层扫描，单次成本远高于探测

$$\displaystyle M \times C_{scan}$$

4. 两式对比：相关子查询的劣势来自“执行次数”而非单次速度，当 M 远大于 N 时差距更悬殊

$$C_{indep} \ll C_{corr} \ (M \gg N)$$

聪明的老板自然会让小林“一次抄全名单”。但现实中有些需求天然需要逐行确认，这时就要靠后面的办公室主任（优化器）和同桌办公（JOIN）来拯救了。理解两种子查询，是走上查询优化之路的第一步。

IN与EXISTS：清单汇报与点头确认

还是那份华东客户名单，这次小林换了两套汇报方案。IN 方案是把整个华东客户档案全部复印一份放在老板桌上，老板自己拿订单逐一比对；EXISTS 方案是老板每念一个客户名，小林只回答“在”或“不在”，不用复印任何文件。前者是搬运整箱资料，后者是逐条口头确认，体力消耗完全不同。

SQL中 IN 子查询先把内层结果集整体取出，与外层值做集合成员判断；EXISTS 则对外层每一行做存在性检测，只要内层找到一条匹配就立刻返回真。很多老资料说“EXISTS 比 IN 快”，其实在 MySQL 5.7 之后，优化器会把大多数等值 IN 也改写成半连接，两者的执行计划常常一模一样。真正的性能差异，来自数据量的分布和索引的利用。

真正影响性能的是谁的数据量小：当内层结果集很小、外层很大时，EXISTS 的“找到就停”能显著减少扫描；当内层结果集很大时，IN 一次性物化后建哈希表反而更高效。核心口诀是“小表驱动大表”——让数据量小的表先执行、当驱动，再让大表的每一行去探测小表的结果，从而把对比次数压到最低。

$$\displaystyle \exists s \in S,\ s = R.A \quad \Longleftrightarrow \quad R.A \in S$$

1. 集合 S：子查询返回的结果集，例如华东客户 id 的集合

$$\displaystyle S = \{id \mid region = \text{'华东'}\}$$

2. ∃ 存在量词：表示“至少能找到一条匹配”，正好对应 EXISTS 的语义

$$\exists s \in S$$

3. s = R.A：匹配条件为 s 等于外层行字段 A，即关联列的等值比较

$$s = R.A$$

4. R.A ∈ S：字段值属于集合 S，正是 IN 操作符的集合成员语义

$$R.A \in S$$

5. ⟺ 等价符号：说明 IN 与 EXISTS 可以互相改写，差异只在优化器如何执行

$$\Longleftrightarrow$$

老板问“在不在”（EXISTS）和翻名单（IN）得到的结果完全一样，但消耗的体力不同。如果名单就三页纸，翻一眼更快；如果老板要核对一万个客户，直接把名单做成速查卡（物化 IN）更省力。关键看哪边数据量小，谁就驱动。

半连接优化：主任的去重卡片魔法

办公室主任看不下去小林的跑腿方式了。他心想：与其让老板每问一个客户，小林就去档案室翻半天，不如提前把华东客户名单抄成一张去重卡片——同一个客户只记一次——放在老板手边。老板随时问起，翻卡片秒回。这就是半连接优化的雏形卡片，也是 MySQL 优化器最拿手的魔法之一。

半连接（Semi-Join）是优化器处理 IN/EXISTS 子查询的三大策略之一。它先把子查询结果物化（Materialize）成一张去重的临时表，然后让外层表的每一行去临时表里探测：有匹配就保留外层行，没有就丢弃，并且绝不返回内层表的任何列。这正好对应子查询“只关心是否存在”的语义，多返回一列都是浪费。

关系代数用符号 ⋉ 定义半连接：R ⋉ S 等于 R 与 S 做等值连接后，只保留 R 的列。因为普通连接时内层重复值会让 R 的同一行出现多次，所以“物化阶段去重”是半连接高效的关键。MySQL 通过 optimizer_switch 中的 semijoin=on、materialization=on 开关控制这类转换，触发前提通常是等值关联且内层无聚合、无 LIMIT。

$$\displaystyle R \ltimes_{R.k = S.k} S = \pi_{R.*}\left(R \bowtie_{R.k = S.k} S\right)$$

1. R ⋉ S：半连接运算符号，结果只包含 R 中能与 S 匹配的行

$$\displaystyle R \ltimes_{R.k = S.k} S$$

2. π_{R.*}：投影运算，保留 R 表的全部列，丢弃 S 表的全部列

$$\pi_{R.*}$$

3. R ⋈ S：等值连接，按 k 列值相等把两表行拼接在一起

$$R \bowtie_{R.k = S.k} S$$

4. 整体含义：半连接 = 连接后再投影，天然没有重复行

$$\pi_{R.*}\left(R \bowtie_{R.k = S.k} S\right)$$

主任的去重卡片一石二鸟：物化只跑一次档案室，不用老板反复问；卡片上去重后每个客户只出现一次，老板绝不会因为同一客户被重复审批而困惑。这正是半连接在数据库内核里做的事——用一张小卡片，化解千万次跑腿。

JOIN改写：让助理坐上办公桌

主任又有了新主意：与其老板问、小林答，不如让小林直接坐到老板对面，两人各拿一份表，同步念出客户编号，编号相同的订单和客户就配对。这就是把子查询改写成 JOIN——从“来回跑腿”变成“同桌办公”。办公效率瞬间提升，但办公规则也要重新约定。

示例中，SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region='华东') 可以改写为 SELECT DISTINCT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.region='华东'。JOIN 让优化器直接选择嵌套循环或哈希连接，省去子查询逐行启动的开销。IN 表达“属于集合”，JOIN 表达“按条件配对”，两种思维方式在等值场景下可以互换。

改写有个关键坑：如果内层表在关联列上存在重复值，或者一对多关系导致同一订单被匹配多次，JOIN 会产生重复结果。这违背了子查询“只看是否存在”的语义。因此改写时通常需要保留 DISTINCT 或 GROUP BY。只有内表键唯一时，JOIN 才与 IN 完全等价；否则必须手动去重。

$$\displaystyle \text{DISTINCT}\left(R \bowtie_{R.k = S.k} S\right) \equiv R \ltimes_{R.k = S.k} S$$

1. R ⋈ S 连接：普通连接会因一个 R 行匹配多个 S 行而放大结果行数

$$\displaystyle R \bowtie_{R.k = S.k} S$$

2. DISTINCT(……)：SQL 的去重操作符，消除连接产生的重复左表行

$$\text{DISTINCT}\left(\cdots\right)$$

3. ≡ 等价符号：说明“去重后的连接”与“半连接”在任意数据集上结果相同

$$\equiv$$

4. R ⋉ S 半连接：天然去重、不返回内层列，是衡量 JOIN 改写正确性的标准

$$R \ltimes_{R.k = S.k} S$$

同桌办公高效，但也要防“同一笔订单被两个同名客户档案各配一次”的乌龙——DISTINCT 就是主任在流程单上盖的“仅保留一项”章。办公方式变了，审批结论必须和原来完全一致，这是所有改写的底线。

执行计划：看懂主任的工作报告

每次查询开工前，主任都会提交一份工作安排表（EXPLAIN），写明：谁先跑腿（驱动表）、用什么目录（索引）、预计翻多少本档案（rows）、子查询是独立完成还是合并办公（select_type）。老板不用猜，一行行读下来就能看出问题出在哪。

EXPLAIN 输出的关键字段包括：select_type（SIMPLE / SUBQUERY / MATERIALIZED 等）、type（ALL 全表扫描 / range 范围 / ref 非唯一索引 / eq_ref 主键等值）、key（实际使用的索引）、rows（预计扫描行数）。当 select_type 出现 SUBQUERY 或 MATERIALIZED，说明子查询被单独物化了一次；当 type 为 ALL，说明存在全表扫描。

rows 是优化器对扫描行数的估算，它决定了嵌套循环的放大倍数。当 rows 异常放大——例如 1000 万 ≈ 外层 1 万 × 内层 1000——说明优化器选择了逐行驱动的计划，子查询未能被半连接化。此时应检查关联列类型是否一致、统计信息是否过期、optimizer_switch 的 semijoin 是否开启。优化的目标，就是让 rows 的总乘积尽量贴近实际返回行数。

$$\displaystyle rows_{est} = N_{table} \times \sigma$$

1. N_table：优化器统计信息中的表总行数（基数）

$$\displaystyle N_{table}$$

2. σ 选择性：过滤条件保留的数据比例，取值在 0 到 1 之间

$$\sigma = \frac{N_{filtered}}{N_{table}}$$

3. rows_est：预计扫描行数，数值越小代表该步骤的代价越低、计划越优

$$rows_{est}$$

看到 rows 从千万级骤降到十万级，老板就知道主任重新排了班——助理不用再为每个订单跑档案室了。EXPLAIN 是数据库写给程序员的“述职报告”，读懂它，才能和优化器好好配合而不是互相猜忌。

索引与性能：给档案室装目录

档案室给每个客户编号做了目录卡（索引）。小林接到“查客户 57”的任务，先翻目录卡直接定位到第 57 号档案，而不是从 1 号柜子开始一本本翻。索引把“逐柜查找”变成“翻目录定位”，速度天差地别。在子查询的世界里，索引同样是加速探测的关键道具。

在子查询中，索引的作用体现在两个关联列上：内层子查询里的 id（通常是主键，天然有索引）和外层表用于探测的 customer_id（建议人工建索引）。有了索引，相关子查询每次探测从全表扫描 O(N) 降为 B+ 树查找 O(log N)，EXISTS 和半连接的探测成本都大幅下降，整个查询的响应时间可能缩小几个数量级。

但索引不是万能的。如果一列只有两种取值（如性别），选择性极低，优化器宁可全表扫描也不走索引；如果关联列做了函数运算或隐式类型转换（如字符串列与数字比较），索引会直接失效。更高阶的覆盖索引（查询所需列全部在索引树中）还能省去回表，是子查询提速的进阶手段。

$$\displaystyle Sel = \frac{|D|}{N}$$

1. |D|：索引列去重后的不同键值个数，代表“目录里有多少个词条”

$$\displaystyle |D|$$

2. N：表的总行数，代表“档案室里总共有多少份档案”

$$N$$

3. Sel 选择性：越接近 1 区分度越高，索引高效；越接近 0 则索引几乎无用

$$Sel = \frac{|D|}{N}$$

目录卡能加速，但要是全档案室都叫“华东客户”，目录再精细也没意义。索引解决的是“找到匹配后还要翻很多本”的问题，而子查询真正的大敌是“每行都让助理跑一趟”——所以先改写、再建索引，顺序千万别反。

实战调优：后厨流程大改造

大促当天，数据库 CPU 告警，订单接口超时一片。DBA 翻出慢查询日志，发现元凶是一条订单查询：每读一行订单，就去客户表确认一次“是不是华东客户”。十万订单，等于让小林十万次往返档案室。问题一目了然，改造方案也呼之欲出。

定位慢查询的常规流程是：先用 mysqldumpslow 或 performance_schema 找出总耗时最高的 SQL；再用 EXPLAIN 观察 select_type、rows、type 等字段；若发现 SUBQUERY 且 rows 被放大，就尝试等价改写成 JOIN 或半连接友好写法，最后对比改写前后的执行计划。

实战改写对照：

改写后，EXPLAIN 中 select_type 从 SUBQUERY 变为 SIMPLE（或出现半连接标记），rows 从千万降到十万量级。衡量改进幅度，可以用改善率公式直观计算。

$$\displaystyle \text{Improvement} = \left(1 - \frac{rows_{after}}{rows_{before}}\right) \times 100\%$$

1. rows_before：改写前 EXPLAIN 中预计扫描的总行数，例如 1000 万

$$\displaystyle rows_{before}$$

2. rows_after：改写后 EXPLAIN 中预计扫描的总行数，例如 10 万

$$rows_{after}$$

3. 1 减比例：计算减少量占原来的百分比，乘 100% 化为直观的百分点

$$\left(1 - \frac{rows_{after}}{rows_{before}}\right) \times 100\%$$

改完之后，小林终于坐到了老板对面（JOIN），十万订单 30 毫秒跑完。主任在季度总结里写道：最大的浪费不是档案多，而是让助理重复跑同一条路。优化子查询，本质上就是消灭重复跑腿。

-- 优化前：相关子查询，外层每行触发一次内层扫描SELECT o.id, o.amountFROM orders oWHERE EXISTS (  SELECT 1 FROM customers c  WHERE c.id = o.customer_id AND c.region = '华东'); -- 优化后：JOIN 改写（customer_id 关联），用 DISTINCT 防重复SELECT DISTINCT o.id, o.amountFROM orders oJOIN customers c ON c.id = o.customer_idWHERE c.region = '华东';

最佳实践：高效团队的办事守则

经历几轮爆单磨炼，老板把主任和小林的经验总结成一本《办事守则》：能批量取回的资料绝不让助理逐行确认；能同桌办公就同桌办公；改了办公流程，必须用 EXPLAIN 报告验收效果。守则简洁，但每条都来自真实的事故与复盘。

工程上落地为三条原则。一是可读性优先：IN 表达 “属于集合” 的语义更直观，SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE region='华东') 一眼就能看懂业务意图；二是热点查询优先 JOIN：在高频路径上用可预期的执行计划换取性能稳定；三是改写后必须对比 EXPLAIN，用 rows 和 type 说话，不能凭感觉判断快慢。

改写保留的唯一底线是逻辑等价：新语句与原语句在任意数据集上返回相同的结果集。此外，不要频繁改动 optimizer_switch 的 semijoin / materialization 开关，让优化器保留自动改写的能力；MySQL 8.0+ 可以用 EXPLAIN ANALYZE 实测各阶段耗时，让每个优化决策都有数据支撑。

$$\displaystyle Q_{rewritten} \equiv Q_{original}$$

1. Q_original：改写前的原始子查询语句（IN / EXISTS 写法）

$$\displaystyle Q_{original}$$

2. Q_rewritten：优化改写后的 JOIN 语句

$$Q_{rewritten}$$

3. ≡ 逻辑等价：两条语句在任意数据集上返回相同结果，是安全改写的数学前提

$$\equiv$$

守则不是教条：如果老板今天只查一个订单，让小林跑一趟档案室（相关子查询）完全没问题，总成本反而最低。优化的本质是“根据场景选最省力的办事方式”，而不是把所有子查询都套上 JOIN 的模板。数据结构、数据量、执行频率，三者共同决定最优解。

AI 应用场景

Python 演示

"""模拟SQL子查询优化：相关子查询 vs 半连接物化"""  class SubqueryDemo:    """用Python列表模拟orders与customers两张表，统计内层扫描行数"""     def __init__(self, orders, customers):        self.orders = orders        # 订单表: [(order_id, customer_id), ...]        self.customers = customers  # 客户表: [(customer_id, region), ...]        self.gc_count = 0           # 累计扫描customers表的行数     def correlated_in(self, region='华东'):        """写法一：相关子查询，外层每行触发一次内层扫描"""        result = []        for oid, cid in self.orders:            for pcid, pregion in self.customers:                self.gc_count += 1                if pcid == cid and pregion == region:                    result.append(oid)                    break        return result     def semi_join(self, region='华东'):        """写法二：半连接物化，内层先执行一次并去重"""        east_ids = set()        for pcid, pregion in self.customers:            self.gc_count += 1            if pregion == region:                east_ids.add(pcid)        result = []        for oid, cid in self.orders:            if cid in east_ids:                result.append(oid)        return result     def run_compare(self, region='华东'):        """执行两种写法并打印性能对比"""        self.gc_count = 0        r1 = self.correlated_in(region)        cost1 = self.gc_count         self.gc_count = 0        r2 = self.semi_join(region)        cost2 = self.gc_count         print('相关子查询结果:', r1[:8], '...')        print('半连接物化结果:', r2[:8], '...')        print('结果一致:', r1 == r2)        print('customers扫描行数: 相关子查询=%d, 半连接=%d' % (cost1, cost2))        print('加速比: %.1fx' % (cost1 / cost2))  def main():    """生成1000条订单和100位客户，其中约1/4是华东客户"""    orders = [(i, i % 100) for i in range(1000)]    customers = [(i, '华东' if i % 4 == 0 else '华北') for i in range(100)]    demo = SubqueryDemo(orders, customers)    demo.run_compare()  if __name__ == '__main__':    main()

1. self.orders` 与 `self.customers模拟两张数据库表，外层订单、内层客户，对应正文中的 orders 与 customers。
2. self.correlated_in()模拟相关子查询：外层每行都完整遍历一次内层表，对应 WHERE EXISTS (...)。
3. for oid, cid in self.orders:外层订单循环，相关子查询中每一行都会触发内层扫描。
4. if pcid == cid and pregion == region:内层匹配条件，对应 SQL 的 c.id = o.customer_id AND region='华东'。
5. east_ids.add(pcid)半连接物化的核心：先执行内层并把结果放入集合自动去重。
6. if cid in east_ids:哈希探测 O(1) 判断，对应优化器把子查询改为临时表探测后的匹配方式。
7. r1 == r2验证改写前后结果集一致，呼应“逻辑等价”的改写底线。
8. cost1 / cost2计算加速比，让优化收益一目了然。

常见误区

面试题