IN和NOT IN常触发全表扫描，因优化器将其转为OR展开或遇NULL/隐式转换/高命中率时弃用索引；EXISTS通过半连接+提前终止更高效；UNION ALL仅适用于小规模静态列表；最终以EXPLAIN的type和rows为准。
IN 和 NOT IN 为什么常触发全表扫描
MySQL 对 IN 的处理本质上是“多个等值条件的 OR 展开”，比如 WHERE id IN (1,2,3) 在优化器眼里 ≈ WHERE id = 1 OR id = 2 OR id = 3。一旦其中任意一个分支无法走索引（比如类型不匹配、字段被函数包裹），或优化器预估命中行数太多（超过全表 30%），就可能直接放弃索引，降级为全表扫描。
- 当
IN子查询返回结果较多（如SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status=1)），且子查询没走索引或返回上万行时，外层几乎必然全表扫描 -
NOT IN更危险：只要子查询含NULL，整个条件结果恒为UNKNOWN，MySQL 会放弃使用索引，强制全表过滤 - 即使
IN列有索引，若传入的是非常规类型（如字符串字段传数字：WHERE code IN (123)），触发隐式转换，索引也会失效
用 EXISTS 替代 IN 的真实效果
EXISTS 是半连接（semi-join）语义，只关心“是否存在匹配行”，不关心具体值或数量。MySQL 通常能将其转为 index lookup + early stop，只要找到第一个匹配就跳出，天然规避大量数据扫描。
- 适用前提：子查询中必须有明确的关联条件，例如
EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') - 注意：
EXISTS不要求子查询 SELECT 列，写SELECT 1或SELECT NULL即可，避免无谓列解析开销 - 对比
IN，EXISTS对空结果集更友好——子查询返回 0 行时，它快速判定为 false；而IN (empty)会被视作IN ()，语法错误
SELECT u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.created_at > '2025-01-01' );
UNION ALL 拆分 IN 的适用边界
当 IN 列表固定且较短（≤ 10 个值），且每个值都能精准命中索引时，手动拆成多个 UNION ALL 查询，可让优化器对每个分支单独走索引，再合并结果。这比单条 IN 更可控，但仅限于静态、小规模列表。
- 不能用于动态参数（如应用拼接的长列表），否则 SQL 预编译失效，还可能触发连接池 SQL 注入防护拦截
- 每个子句必须结构一致（字段数、类型、顺序），否则
UNION ALL报错 - MySQL 8.0+ 对
IN做了更多优化（如常量折叠、范围下推），这种拆分在新版中收益变小，需实测EXPLAIN对比
SELECT id, name FROM users WHERE id = 1001 UNION ALL SELECT id, name FROM users WHERE id = 1002 UNION ALL SELECT id, name FROM users WHERE id = 1003;
真正决定是否全表扫描的，是执行计划里的 type 和 rows
别猜，直接看 EXPLAIN。集合操作是否走索引，最终由优化器根据统计信息判断——不是语法决定的，而是成本模型算出来的。
-
type字段为ALL就是全表扫描；range或ref才算走了索引 -
rows显示预估扫描行数，如果远大于实际返回行数（比如查 10 行却扫 10 万行），说明索引选择不当或统计过期 - 执行前务必
ANALYZE TABLE users;更新统计信息，尤其在大批量导入/删除后，否则优化器基于过时数据做决策，EXISTS也可能被误判为低效
真正容易被忽略的是：集合操作的性能拐点不在语法本身，而在数据分布和统计准确性。哪怕写了 EXISTS，如果关联字段没有索引、子查询条件未覆盖索引最左列、或 ANALYZE TABLE 长期没跑过，照样全表扫。先看 EXPLAIN，再动索引，最后才改写逻辑。
