MySQL / 系统课时

判断子查询改写与半连接

判断子查询改写与半连接

面对 IN、EXISTS 或相关子查询,真正要判断的不是“有没有子查询”,而是优化器能否把它改造成一次连接式执行,以及是否仍会按外层行重复运行。本课用可复现实验和执行计划,建立判断 semijoin、手工改写与保留子查询的顺序。

学习目标

  • 判断 IN、EXISTS 是否具备 semijoin 改写条件
  • 从执行计划识别去重、物化或提前命中的执行思路
  • 识别相关子查询重复执行与 NOT IN 的空值风险

先分清语义再谈改写

半连接只回答一个问题:外层行在内层是否存在匹配。即使内层有多条匹配记录,外层行也只能返回一次。因此,IN 或 EXISTS 不能机械地改成普通 JOIN;普通连接可能把外层行复制多次。

MySQL 8.x 会对符合条件的 IN、EXISTS 等子查询尝试 semijoin 优化。优化器仍要根据基数、索引和成本选择具体策略,例如提前停止匹配、先把内层结果物化并去重,或对重复匹配做 weedout。计划最后长得像普通连接并不代表语义变成了普通连接。

IN 与 EXISTS 转换为 semijoin 的优化路径

用执行计划验证 semijoin

下面建立一个允许同一客户出现重复 vip 标签的表。这样能直接观察半连接为何必须保留“只返回一次外层行”的语义。

sql
DROP TABLE IF EXISTS customer_tags;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY idx_customer_id (customer_id)
);

CREATE TABLE customer_tags (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  tag VARCHAR(20) NOT NULL,
  KEY idx_tag_customer (tag, customer_id)
);

INSERT INTO orders VALUES
  (1, 101, 120.00),
  (2, 102, 80.00),
  (3, 103, 200.00);

INSERT INTO customer_tags VALUES
  (1, 101, 'vip'),
  (2, 101, 'vip'),
  (3, 103, 'vip');

EXPLAIN FORMAT=TREE
SELECT o.id, o.customer_id
FROM orders AS o
WHERE o.customer_id IN (
  SELECT ct.customer_id
  FROM customer_tags AS ct
  WHERE ct.tag = 'vip'
);

SELECT o.id, o.customer_id
FROM orders AS o
WHERE o.customer_id IN (
  SELECT ct.customer_id
  FROM customer_tags AS ct
  WHERE ct.tag = 'vip'
);

预期结果

text
EXPLAIN 的具体节点、成本和行数会随 MySQL 版本与统计信息变化,但应体现“只判断是否存在匹配”的连接式执行,而不是把 customer_tags 的两条 101 记录复制成两条订单结果。

查询结果:
id  customer_id
1   101
3   103

如果把最后一条查询直接改成 JOIN customer_tags,订单 1 会因为两条 vip 记录出现两次。只有在内层连接键本身唯一,或你显式先去重时,普通 JOIN 才与半连接语义等价。

判断相关子查询是否重复执行

“相关”并不自动等于“每行执行一次”,因为优化器可能把部分相关 EXISTS 去相关并转成 semijoin。判断时不要只读 SQL 形状,应使用 EXPLAIN ANALYZE 查看真实循环次数。

sql
EXPLAIN ANALYZE
SELECT o.id
FROM orders AS o
WHERE EXISTS (
  SELECT 1
  FROM customer_tags AS ct
  WHERE ct.customer_id = o.customer_id
    AND ct.tag = 'vip'
);

预期结果

text
关注实际计划中的 loops、rows 与连接节点。若 EXISTS 已被改造成 semijoin,通常会看到内外表处于同一连接执行路径;若计划保留独立相关子查询节点,并且该节点 loops 接近外层输入行数,就说明存在重复执行成本。具体节点名称和数值可能不同。

真正需要警惕的是无法半连接化的相关标量子查询,例如外层每行都计算一次聚合值。此时应重点比较“重复探测内层索引”的总代价,与“先聚合成派生结果再连接”的代价,而不是看到相关子查询就立即重写。

处理边界与反例

NOT IN 最容易因为 NULL 产生语义差异:只要内层结果可能含 NULL,判断就可能变成 UNKNOWN,和直觉中的“没有匹配就返回”不同。需要反连接语义时,优先确认列是否 NOT NULL,或者改写为能明确表达空值规则的 NOT EXISTS。

手工改写前按这个顺序判断:先确认存在性语义,再看 EXPLAIN 是否已经 semijoin 化;只有计划仍存在高循环次数、内层代价显著,或索引无法支撑时,再考虑去重后连接、预聚合或补索引。这样避免为了“消灭子查询”反而制造重复行和更大的中间结果。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

判断子查询改写与半连接

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通