开发者日记

外观

半连接实测对比：EXISTS、IN 改写 JOIN 避坑实录 ​

问题背景：慢 SQL 不一定是 EXISTS 写法的锅 ​

业务中常见这样的需求：查询“下过有效订单的用户”。SQL 往往先被写成 IN 子查询，也有人坚持 EXISTS 性能更好，还有团队会统一改写成 JOIN。但在 MySQL 8.0 中，这三种写法并不存在绝对的快慢排序。优化器是否能识别为半连接（Semi-join）、驱动表选择是否合理、索引是否命中、是否误写成产生重复行的 Inner Join，才是决定执行效率的关键。

半连接的核心语义是：外层表只需要判断“在内层集合中是否存在匹配”，匹配到一条即可停止，不关心匹配多少条，也不应该因为一对多关系而复制外层行。本文用一组可直接运行的表和查询，对比 EXISTS、IN、JOIN 的执行计划与改写边界。

方案对比：三种写法的语义与优化器处理 ​

先看三种常见形式：

  • IN (SELECT ...)：表达“外层值是否属于子查询结果集”，天然具备去重语义；
  • EXISTS (SELECT 1 ... WHERE 关联条件)：表达“是否存在至少一条匹配”，找到即可短路；
  • INNER JOIN：表达两张表按条件匹配，如果右表一对多，结果可能重复，必须配合 DISTINCT 或先聚合去重；
  • LEFT JOIN ... WHERE right.id IS NOT NULL：表面是外连接反查，实际常被优化器转成连接，但同样有重复风险。

在 MySQL 8.0 中，很多 IN 和 EXISTS 子查询会被优化器转成 Semi-join，执行策略可能包括 Table Pullout、Duplicate Weedout、FirstMatch、LooseScan、Materialization 等。因此，只靠 SQL 外形判断性能并不可靠，必须看 EXPLAIN 或 EXPLAIN ANALYZE。

原始子查询IN / EXISTS查看 EXPLAIN是否转 Semi-join已命中：补关联索引验证驱动表与行数未命中：JOIN 派生表或强制相关子查询用实际数据复测耗时与行数

实操准备：建立可复现的用户与订单表 ​

下面示例使用 MySQL 8.0，先建两张 InnoDB 表。用户表约 10 万行，订单表约 50 万行，用来模拟一个用户对应多张订单的一对多场景。

sql
-- 用户表
CREATE TABLE t_user (
  id BIGINT PRIMARY KEY,
  user_name VARCHAR(64) NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表：一个用户可有多张订单
CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  order_status TINYINT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_status_user (order_status, user_id),
  KEY idx_user_status (user_id, order_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

写入简化测试数据。如果使用真实库，也可以跳过造数，直接替换表名和过滤条件。

sql
-- 生成 100000 个用户
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
INSERT INTO t_user(id, user_name, status, created_at)
SELECT
  n,
  CONCAT('user_', n),
  CASE WHEN n % 20 = 0 THEN 0 ELSE 1 END,
  DATE_ADD('2025-01-01', INTERVAL n MINUTE)
FROM seq;

-- 每个用户生成 5 张订单，约 500000 行
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 500000
)
INSERT INTO t_order(id, user_id, order_status, amount, created_at)
SELECT
  n,
  ((n - 1) MOD 100000) + 1,
  CASE WHEN n % 5 = 0 THEN 20 ELSE 10 END, -- 20 表示已支付
  ROUND(50 + RAND() * 200, 2),
  DATE_ADD('2025-01-01', INTERVAL n MINUTE)
FROM seq;

需求是：查询最近 30 天内有已支付订单的活跃用户，分页返回前 20 个。

IN 写法：关注子查询是否被 Semi-join 优化 ​

sql
SELECT u.id, u.user_name
FROM t_user u
WHERE u.status = 1
  AND u.id IN (
    SELECT o.user_id
    FROM t_order o
    WHERE o.order_status = 20
      AND o.created_at >= '2025-01-01'
      AND o.created_at < '2025-02-01'
  )
ORDER BY u.id
LIMIT 20;

查看执行计划：

sql
EXPLAIN FORMAT=TREE
SELECT u.id, u.user_name
FROM t_user u
WHERE u.status = 1
  AND u.id IN (
    SELECT o.user_id
    FROM t_order o
    WHERE o.order_status = 20
      AND o.created_at >= '2025-01-01'
      AND o.created_at < '2025-02-01'
  )
ORDER BY u.id
LIMIT 20;

理想情况下，执行计划中可能出现类似以下信息：

text
-> Limit: 20 row(s)
    -> Nested loop semijoin
        -> Index range scan on u ...
        -> Index lookup on o using idx_user_status ...

看到 semijoin，说明优化器已经理解“只判断存在性”。如果订单表使用 idx_user_status(user_id, order_status)，当外层用户作为驱动表时，可以快速判断某个用户是否存在已支付订单；但 created_at 不在这个联合索引中，仍需回表过滤时间。若时间条件选择性很高，更合适的索引是：

sql
ALTER TABLE t_order
ADD INDEX idx_status_created_user (order_status, created_at, user_id);

这个索引同时覆盖状态、时间范围和关联字段，子查询可在索引上完成过滤与去重，减少回表。

EXISTS 写法：相关子查询并非天然更慢 ​

sql
SELECT u.id, u.user_name
FROM t_user u
WHERE u.status = 1
  AND EXISTS (
    SELECT 1
    FROM t_order o
    WHERE o.user_id = u.id
      AND o.order_status = 20
      AND o.created_at >= '2025-01-01'
      AND o.created_at < '2025-02-01'
  )
ORDER BY u.id
LIMIT 20;

很多人以为 EXISTS 一定是“对外层每行执行一次子查询”，所以必然差。事实上，优化器可能把它改写成 Semi-join，也可能保留 FirstMatch 策略：一旦为当前外层用户找到第一条匹配订单，就停止继续查找。对于“外层结果集小、内层可通过索引快速探活”的场景，这种方式很高效。

这里优先使用下面的索引：

sql
ALTER TABLE t_order
ADD INDEX idx_user_status_created (user_id, order_status, created_at);

选择哪个索引取决于驱动方向：

  • 用户表经过 status = 1 和 LIMIT 20 后行数很少，订单表按 user_id 探活，适合 idx_user_status_created；
  • 订单表时间范围很窄、先从订单侧筛出少量用户，再回连用户表，适合 idx_status_created_user。

不要只凭经验固定一个索引，应结合过滤条件的基数和 EXPLAIN ANALYZE 的实际行数判断。

JOIN 改写：必须防止一对多重复 ​

最容易出错的是直接改成 Inner Join：

sql
-- 风险写法：一个用户有多张符合条件的订单时会返回多行
SELECT u.id, u.user_name
FROM t_user u
JOIN t_order o
  ON o.user_id = u.id
WHERE u.status = 1
  AND o.order_status = 20
  AND o.created_at >= '2025-01-01'
  AND o.created_at < '2025-02-01'
ORDER BY u.id
LIMIT 20;

这条 SQL 的结果不再等价于半连接，因为匹配 5 张订单的用户可能出现 5 次。即使加了 LIMIT 20，也可能导致前 20 行被少数重复用户占满。正确方式之一是先在订单侧按用户去重：

sql
SELECT u.id, u.user_name
FROM t_user u
JOIN (
    SELECT DISTINCT o.user_id
    FROM t_order o
    WHERE o.order_status = 20
      AND o.created_at >= '2025-01-01'
      AND o.created_at < '2025-02-01'
) d ON d.user_id = u.id
WHERE u.status = 1
ORDER BY u.id
LIMIT 20;

也可以使用 GROUP BY：

sql
SELECT u.id, u.user_name
FROM t_user u
JOIN t_order o ON o.user_id = u.id
WHERE u.status = 1
  AND o.order_status = 20
  AND o.created_at >= '2025-01-01'
  AND o.created_at < '2025-02-01'
GROUP BY u.id, u.user_name
ORDER BY u.id
LIMIT 20;

但 GROUP BY 可能触发临时表和文件排序，通常不如派生表配合覆盖索引稳定。若只是为了去重，推荐在子查询中只保留关联键，让语义更清晰。

执行计划对比：重点看这 5 个信号 ​

使用传统 EXPLAIN 时，不要只看 type 和 rows，还要结合以下字段：

检查项理想信号异常表现
连接类型range、ref、eq_ref大表出现 ALL 全表扫描
ExtraUsing index、FirstMatch、LooseScanUsing temporary; Using filesort 且数据量很大
行数估算预估行数接近实际rows 极小但实际慢，统计信息可能失真
索引列顺序等值列在前，范围列在后，覆盖返回列对范围列后面的列无法有效利用
结果正确性每个外层对象最多一行Join 后出现重复用户

建议在测试环境执行：

sql
-- 查看实际执行耗时和各迭代器行数
EXPLAIN ANALYZE
SELECT u.id, u.user_name
FROM t_user u
WHERE u.status = 1
  AND EXISTS (
    SELECT 1
    FROM t_order o
    USE INDEX (idx_user_status_created)
    WHERE o.user_id = u.id
      AND o.order_status = 20
      AND o.created_at >= '2025-01-01'
      AND o.created_at < '2025-02-01'
  )
ORDER BY u.id
LIMIT 20;

EXPLAIN ANALYZE 会显示实际行数、循环次数和耗时。如果某个索引查找被执行了接近外层全量行数，而每次都要回表，说明驱动选择或索引顺序不理想；如果实际行数远低于预估行数，可考虑更新统计信息：

sql
ANALYZE TABLE t_user;
ANALYZE TABLE t_order;

避坑总结：子查询改写的实战注意事项 ​

1. 不要把 Inner Join 当成等价改写 ​

常见错误现象是：原 IN 查询返回 20 个用户，改成 JOIN 后接口出现重复用户，分页总数也变大。原因是订单表与用户表是一对多关系。修正方法是使用 JOIN (SELECT DISTINCT user_id ...) 派生表，或确认执行计划已经是 Semi-join，而不是普通 Inner Join。

2. 不要迷信“EXISTS 一定比 IN 快” ​

在 MySQL 8.0 中，两者都可能被改写为相同的 Semi-join 执行计划。真正高频的问题是关联字段无索引、过滤字段没有进入联合索引、统计信息过旧。应分别用 EXPLAIN FORMAT=TREE 和 EXPLAIN ANALYZE 验证，而不是通过写法口诀选 SQL。

3. 联合索引顺序不能只按 WHERE 字段堆列 ​

错误现象是建立了 (created_at, order_status, user_id)，但执行计划仍扫描大量记录；或者建立 (user_id, created_at, order_status)，等值状态列放在范围列之后导致过滤效果变差。一般规则是：高频等值条件在前，范围条件随后，返回或去重字段作为覆盖列放最后；同时根据驱动方向设计另一套索引，而不是无节制添加重复索引。

4. 分页排序可能让“存在性查询”额外排序 ​

即使半连接探活很快，ORDER BY u.id LIMIT 20 也可能因为优化器选择从订单侧驱动而产生临时排序。若业务允许，应让外层表按主键或已索引字段排序，并确认用户表是否能按序输出。必要时用 STRAIGHT_JOIN 只能作为短期验证手段，不建议在代码中长期固化，因为数据分布变化后可能反效果。

5. 空值与类型转换会破坏半连接效率 ​

IN 子查询结果中如果包含 NULL，语义容易让人困惑；关联字段发生字符串与数字隐式转换时，还可能导致索引失效。建表时应保证关联字段均为 NOT NULL，外层和子查询字段类型、字符集、排序规则一致。上线前用 EXPLAIN 检查 key、key_len 和警告信息，避免出现 Range checked for each record 这类逐行检查。

子查询改写的目标不是把所有 SQL 统一成某种语法，而是保留“只判断存在”的语义，让优化器稳定选择半连接策略，并用索引和执行计划验证收益。先保证结果等价，再比较执行计划，最后做真实数据压测，才是可靠的 SQL 调优路径。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。