用join替代in子查询能显著提速，因其避免嵌套循环和临时表物化；需确保关联字段均有索引、类型严格一致，并优先以小结果集为驱动表。
子查询为什么在亿级表里特别慢
因为大多数子查询会触发嵌套循环（Nested Loop）或临时表物化，尤其当外层结果集大、内层无索引时，数据库可能对每个外层行都执行一次全表扫描。比如 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE region = 'CN')，若 users.id 无索引，且 orders 有 1 亿行，实际执行可能扫描 1 亿 × 用户数次磁盘。
用 JOIN 替代 IN/EXISTS 子查询的实操要点
绝大多数 IN 和相关 EXISTS 子查询可重写为 JOIN，前提是关联字段已建索引且类型严格一致：
- 确保
JOIN字段两边数据类型相同——user_id INT不能和'123'字符串比较，否则触发隐式转换，索引失效 - 优先让小结果集做驱动表：先查出
region = 'CN'的用户 ID（假设几千行），再用这些 ID 去orders表走索引查找，而非反向操作 - 避免
DISTINCT或GROUP BY在JOIN后补救——应在子查询阶段就去重，或用LEFT JOIN + IS NOT NULL模拟EXISTS
示例：SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.region = 'CN';
无法改写时，必须加索引的三个位置
如果子查询逻辑复杂（如含聚合、窗口函数），无法简单转 JOIN，至少保证以下三处有索引：
- 子查询
WHERE条件字段（如users.region） - 子查询与外层关联的字段（如
users.id和orders.user_id），两者都要单独建索引，或组成复合索引 - 外层查询的过滤字段（如
orders.status），否则即使子查询快，外层仍要扫全表
常见错误是只给 users.id 加主键索引，却忽略 users.region ——这会导致子查询本身变成全表扫描。
深度分页 + 子查询的致命组合
像 SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE x=1) ORDER BY create_time LIMIT 100000, 20 这类写法，在亿级数据下基本不可行：子查询结果可能上百万，LIMIT 又要求跳过前 10 万行，MySQL 仍需生成完整中间结果集再截断。
- 拆成两步：先用子查询拿到满足条件的 ID 列表（加
LIMIT控制数量），再用这些 ID 主键IN查询详情 - 更优解是放弃
OFFSET，改用游标分页——基于上次查询的create_time和id做范围过滤 - 如果子查询结果集过大（>10 万行），考虑用临时表存 ID，并对临时表建索引
真正难的不是语法改写，而是判断子查询是否真的需要实时执行——很多场景下，把子查询结果缓存到物化视图或应用层预计算，比硬扛 SQL 优化更有效。
