IN子查询常不走索引，因优化器对长列表（超eq_range_index_dive_limit）改用粗略统计而误判全表扫描更优，且隐式类型转换、NULL值、子查询字段无索引或复合索引跳过最左列均导致索引失效。
直接改写为 JOIN 或 EXISTS，绝大多数情况下能绕过索引失效问题；硬拼长 IN 列表或依赖子查询本身走索引，基本不可靠。
为什么 IN 子查询经常不走索引
MySQL 不是“看到 IN 就用索引”，而是对每个值做 index dive 估算成本。一旦 IN 列表长度超过 eq_range_index_dive_limit（5.7 默认 200，8.0 默认 10），优化器就放弃逐个探测，改用粗略统计——常误判为全表扫描更便宜，于是 type=ALL。
更常见的是隐式转换：比如 user_id 是 BIGINT，但子查询里传了字符串 '123'，触发类型转换，索引直接失效。
其他干扰项包括：
-
IN中混入NULL值（如WHERE id IN (1, 2, NULL)），优化器会跳过索引路径 - 子查询字段没索引（如
orders.user_id无索引），IN再怎么写也白搭 - 复合索引下跳过最左列（如索引是
(a,b,c)，却写WHERE b IN (1,2)）
用 JOIN 替代 IN 子查询的实操要点
核心不是语法替换，而是把“判断存在性”转为“物理连接后过滤”。原语句：
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
应改为：
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active';
关键动作：
- 确保
orders.customer_id和customers.id都有索引（主键自动有，但别假设） - 用
INNER JOIN对应IN，用LEFT JOIN ... WHERE xxx IS NULL对应NOT IN - 如果子查询本身复杂（含
GROUP BY、多表），先抽成派生表：FROM (SELECT DISTINCT user_id FROM orders WHERE status = 'paid') AS tmp，再JOIN - 一对多时结果可能重复，业务若只需 ID 去重，加
DISTINCT或换EXISTS
什么时候该选 EXISTS 而不是 JOIN
当外层表小、内层表大，且你只关心“是否存在匹配”，EXISTS 往往更快——它找到第一条就停，不生成中间结果集。
例如：
SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');
优势场景：
- 子查询结果集极大（上万行），
JOIN可能放大临时结果 - 关联字段已有索引，
EXISTS执行计划通常是type=eq_ref或ref - 原
IN语义本就是“存在性检查”，而非“取子查询字段值”
注意：EXISTS 无法替代 IN 返回子查询中非关联字段的场景（比如要取 SELECT name FROM customers WHERE id IN (...) ）。
长 IN 列表（>500 项）的落地处理方式
硬拼 SQL 不仅容易超 max_allowed_packet，还会让优化器彻底放弃索引。真实项目中推荐：
- 拆成每批 200–500 项的多个查询，应用层合并结果（适合读多写少、ID 来源可控）
- 写入临时表并建索引：
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY); INSERT INTO tmp_ids VALUES (...); SELECT * FROM t JOIN tmp_ids USING(id); - 若 ID 列表长期复用（如活跃用户池），存进普通表 +
ON DUPLICATE KEY UPDATE维护，避免每次重建 - 绝不调高
eq_range_index_dive_limit全局值——会拖慢所有小IN查询；如真需局部调整，仅限会话级：SET SESSION eq_range_index_dive_limit = 500;，且必须配合ANALYZE TABLE
真正稳住索引命中的根基，永远是三件事：字段类型严格匹配、关联列有索引、数据分布不过于倾斜。参数和写法只是在这些前提成立后才起作用。
