IN和NOT IN在大数据量下慢因全量展开匹配或子查询临时表溢出；NOT IN遇NULL恒为UNKNOWN致结果为空；应改用关联的NOT EXISTS或LEFT JOIN+IS NULL，并建好索引。
IN 和 NOT IN 在大数据量下为什么慢
因为数据库通常会对 IN 列表做全量展开，再逐个匹配；如果右边是子查询（比如 IN (SELECT id FROM orders)），多数引擎会先执行子查询生成临时结果集，再对主表每行做哈希查找或嵌套循环——当子查询返回几万行、主表又很大时，IN 很容易触发全表扫描或临时表溢出。
更麻烦的是 NOT IN：只要子查询里任意一行是 NULL，整个条件就恒为 UNKNOWN，结果集直接变空。这是 SQL 三值逻辑的硬伤，不是 bug，但常被忽略。
- 子查询含
NULL时，NOT IN等价于FALSE，查不到任何数据 -
IN没这个问题，但性能差、内存占用高，尤其在 MySQL 5.7 或 PostgreSQL 低版本中 - Oracle 对
IN有隐式重写优化，但不可依赖；SQL Server 默认走哈希匹配，仍可能 spill 到 tempdb
用 NOT EXISTS 替换 NOT IN 的实操要点
核心是把“不在集合中”转成“找不到匹配行”，语义更清晰，且天然绕过 NULL 陷阱。
注意子查询必须关联外层，否则变成非相关子查询，效果等同于 NOT IN。
- 错误写法：
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE status = 'shipped')—— 没关联，永远返回空或全表 - 正确写法：
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id AND status = 'shipped') - MySQL 8.0+、PostgreSQL 12+、SQL Server 2019+ 都能对这类关联子查询自动转成半连接（semi-join）或反连接（anti-join），性能接近左连接 + IS NULL
- 记得给关联字段建索引，比如
orders(user_id, status)，否则NOT EXISTS也会慢
LEFT JOIN + IS NULL 和 NOT EXISTS 哪个更快
没有绝对答案，取决于数据分布、索引、统计信息准确度和优化器能力。但可以按场景判断：
- 当右表匹配率很低（比如用户中只有 5% 下过单），
NOT EXISTS通常更优——找到第一个匹配就停，不用扫完所有行 - 当右表匹配率高或需复用右表其他字段（如订单时间、金额），
LEFT JOIN更合适，避免多次执行子查询 - PostgreSQL 中两者计划常一致；MySQL 5.7 对
LEFT JOIN ... IS NULL有时误判为全表扫描，加STRAIGHT_JOIN或改写为NOT EXISTS反而稳定 - 别写
LEFT JOIN ... WHERE right_col IS NULL AND right_col != 'xxx'—— 条件放ON才生效，放WHERE会让左连接退化为内连接
IN 列表超过 1000 项时怎么安全处理
Oracle 限制 IN 最多 1000 项；MySQL 虽无硬限，但超大列表会导致解析慢、执行计划不准、甚至 OOM。
不要拼接超长字符串，也不要用临时表硬扛——优先走应用层分批或改用连接。
- 应用侧拆成每 500 项一批，用
UNION ALL合并结果（注意去重开销） - 把 ID 列表写入临时表（如
CREATE TEMPORARY TABLE tmp_ids (id BIGINT PRIMARY KEY)），然后JOIN—— MySQL 8.0+ 临时表支持索引，效果接近原生IN - 用
VALUES ROW构造行集合（PostgreSQL / SQL Server）：SELECT * FROM users WHERE id IN (SELECT id FROM (VALUES (1),(2),(3)) AS t(id)) - 千万别用
FIND_IN_SET或正则匹配替代IN，那是索引杀手
真正难的不是语法替换，是搞清「哪部分数据会变 NULL」「索引是否覆盖关联条件」「执行计划里有没有 Using temporary」——这些不看 EXPLAIN 或 pg_stat_statements，光改写 SQL 没用。
