横向对比:

特性 / 版本 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 INNULL “毒死”;
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+ 都友好;

  • 读写分离、索引利用率高;

  • 逻辑清晰,可读性好。

 

作者 admin

百度广告效果展示