横向对比:
特性 / 版本 | MySQL 5.5 | MySQL 5.6 | MySQL 5.7 | MySQL 8.0 |
---|---|---|---|---|
子查询重写能力 | 极弱,几乎不做 semi‑join 重写 | 引入部分 semi‑join 优化,但仅限于部分场景 | semi‑join 优化更完备,可将 EXISTS 重写成 anti‑semi‑join |
semi‑join / anti‑semi‑join 优化更成熟 |
NOT IN + DISTINCT | – 一定 materialize 子查询结果; – 若含 NULL ,外层结果全空;– 临时表倾向落磁盘 |
– 仍 materialize; – 优化器没把它当 anti‑semi‑join; – 大结果集时临时表/IO 开销高 |
– 行为同 5.6; – 虽然改进了 subquery 架构,但 NOT IN 路径没本质变化 |
– NOT IN 路径仍留存;– 仍需谨慎避免 NULL ;– 但临时表多用内存,性能略优于旧版 |
NOT EXISTS | – 对每个外层行都走独立子查询; – 无 semi‑join 改写,仅短路定位; – 但在小集合上通常胜过 NOT IN |
– 部分场景可转 anti‑semi‑join; – 短路机制更有效; – 对大外层集合仍保持快速剔除 |
– 更多场景下可做 anti‑semi‑join 重写; – 避免 materialize,性能稳定 |
– optimizer 能智能选择 anti‑semi‑join; – 短路+索引全命中,最优 |
NULL 处理 | NOT IN 被 NULL “毒死”;NOT EXISTS 无此问题 |
同 5.5 | 同 5.5 | 同 5.5 |
临时表开销 | NOT IN 一定落磁盘;EXISTS 每行短路,无大临时表 |
NOT IN 大多内存,但仍可能落磁盘;EXISTS 子查询短路 |
NOT IN 更多走内存;EXISTS anti‑semi‑join 路径避临时表 |
NOT IN 几乎全内存;EXISTS 完全走 anti‑semi‑join |
实际推荐 | 优先用 LEFT JOIN … IS NULL 反连接; 再用 NOT EXISTS |
对小外层集合可放心 NOT EXISTS ;大集合仍首选反连接 |
NOT EXISTS 更稳;NOT IN 只用在可控、无 NULL 场景 |
NOT EXISTS / anti‑semi‑join 最优;NOT IN 谨慎且加 IS NOT NULL |
NOT IN + DISTINCT 陷阱
-
NULL
毒性(Null “Poisoning”)-
如果子查询结果集中出现了任何
NULL
,那么整个x NOT IN (…)
判断都会变成UNKNOWN
,最终行会被 全部过滤掉。 -
你必须在子查询里额外写上
AND customer_id IS NOT NULL
,才能避免这个问题;否则哪怕只有一行customer_id IS NULL
,外层也得不到任何结果。
-
-
临时表(Materialization)开销
-
MySQL(尤其 5.5/5.6/5.7)通常会把子查询结果
DISTINCT
后 materialize 到临时表(内存或磁盘), -
如果子查询返回大量行,不但要占用额外存储,还要在外层对每行做一次哈希/排序比对,IO 和内存压力都很大。
-
-
索引失效
-
子查询结果被 materialize 后,外层每条记录对临时表的查找不走表的原始索引,而是走临时表上的内存哈希或临时索引。
-
导致即使主表和子查询表上有合适的复合索引,也无法充分利用,性能不如直接用
NOT EXISTS
或者反连接(LEFT JOIN … IS NULL
)。
-
-
不可短路
-
NOT IN
必须先把子查询的“全量结果”都读出来并去重,才能开始跟外层比对; -
与
NOT EXISTS
不同,它没法“遇到第一条就停”,无法利用 anti‑semi‑join 的短路优化。
-
-
语义冗余与可读性
-
往往会看到
SELECT DISTINCT customer_id … GROUP BY customer_id
这样的双重去重, -
其实写
GROUP BY customer_id
就足够,DISTINCT
在这里是语义上的冗余,容易误导读者去认为两者要配合使用才能正确去重。
-
-
不易维护
-
为防止
NULL
和性能问题,你还得在子查询里不断加上过滤条件(IS NOT NULL
、时间范围等), -
查询逻辑变得臃肿、可读性差,也更容易在后续迭代中出错。
-
替代方案
NOT EXISTS
-
无
NULL
毒性; -
能利用 anti‑semi‑join 短路;
-
更容易走索引,性能稳定。
反连接(Anti‑Join)
-
对 MySQL 5.5+ 都友好;
-
读写分离、索引利用率高;
-
逻辑清晰,可读性好。