企业官网建设流程全解析

接手过一个让人头疼的线上问题：某一张订单记录表，数据量大概在 200 万行，业务里要查“最近 30 天内有订单、但不在这份黑名单里的用户”，SQL 写得很直白，NOT IN (SELECT user_id FROM blacklist)。上线第一天，这条查询直接干了 5.8 秒，数据库 CPU 飙到 80%，整个服务差点被打挂。当时第一反应是“加索引”，但索引加上之后效果微乎其微，执行计划里依然是全表扫描加一个个子查询的逐行校验。

那是我第一次认真研究 MySQL 中NOT IN语句的性能陷阱。后来翻文档、看执行计划、做对照实验，才把这件事从头到尾理清楚。今天就把这些经验完整写出来，包括它为什么慢、何时该换写法、如何根据实际数据量做决策，以及踩过的那些坑。

1. NOT IN 慢的根因不止一个：NULL 语义、物化与关联失效

很多开发习惯性地以为NOT IN慢只是因为“没走索引”，但实际情况要复杂得多。MySQL 优化器对NOT IN的态度和对JOIN、EXISTS完全不同，而且NOT IN还自带一个非常隐蔽的 NULL 语义问题，这两个因素叠加起来，才导致它在很多场景下变成性能炸弹。

1.1 第一条性能陷阱：NULL 值让整条查询直接失效

先看一个最简单的例子：

SELECT id FROM users WHERE id NOT IN (1, 2, NULL);

这条语句的返回结果是什么？很多人会说是“除了 id 为 1 和 2 以外的所有用户”，实际上 MySQL 返回的是空集。因为NOT IN的语义是“不等于列表中任何一个值”，但NULL代表“未知”，任何值与 NULL 做比较的结果都是“未知”，而不是“真”。所以id NOT IN (1, 2, NULL)等价于id != 1 AND id != 2 AND id != NULL，最后的id != NULL结果是未知，整个 AND 条件也就是未知，最终一行都查不出来。

这听起来像一个“知识题”，但它真真切切会发生在业务中。比如刚才那个黑名单例子，如果blacklist.user_id列里存在 NULL 值，那么整个查询结果会直接变成空集，业务上表现为“所有人都被拉黑”。这种错误极其隐蔽，因为子查询结果集一多，你根本不会去想里面有没有 NULL，只有做数据对比时才会发现结果对不上。

而且，被查询的主表列上有 NULL 值也会踩坑，但这里有个很多人不知道的细节——如果主表列上有 NULL，MySQL 并不一定会直接返回空集，而是可能通过常规执行计划扫描并过滤掉这些行。真正恶性的问题是：优化器遇到可能含 NULL 的子查询时，生成的执行计划往往会带上“物化”特性，它会预先处理这个子查询，把结果输出成一个临时表再比对，而这种物化过程本身往往很慢。

提示：无论主表还是子查询表，只要关联字段允许为 NULL，就必须先去 NULL 再使用NOT IN。最简单的做法是在子查询里显式加WHERE xxx IS NOT NULL，或把主表查询里对应的列也过滤掉 NULL。

1.2 第二条性能陷阱：子查询被物化成临时表，索引关联失效

这才是NOT IN性能问题的重头戏。

MySQL 优化器在遇到NOT IN (SELECT ...)这类子查询时，并不会像处理JOIN那样把子查询转换为简单的关联查找，而是倾向于走“物化”或“exist 扫描”两条路。什么叫物化？就是先把子查询的结果算出来，写入一个内部的临时表（可能基于内存或磁盘），然后再拿外部查询的每一行到这个临时表里去比对。

问题出在哪里？对比的过程不是走哈希索引，也不是走 B+ 树的等值查找，而是对物化后的临时表做逐行扫描。外部表 200 万行，子查询结果 1000 行，那就是理论上的 200 万 × 1000 次比较。如果子查询结果集稍大，或者临时表被落到磁盘上走不了内存，这条查询就是一场灾难。

用生活化类比来说，JOIN相当于快递员拿着收件人名单按片区配送，每到一个片区直接看门牌号；而NOT IN的最大可能执行方式是快递员每送一个包裹，就重新翻阅一遍全城所有地址簿。

MySQL 官方文档里也明确说过，对于NOT IN，子查询可以转换为 NOT EXISTS 的形式去优化，但在很多情况下（尤其带 NULL、带排序、带聚合的子查询），优化器无法完成这种等价转换。这也是为什么我们经常看到一条NOT IN写起来很优雅，执行计划却稀烂的原因。

1.3 第三条性能陷阱：子查询结果集未去重，重复比对放大成本

NOT IN的语义要求子查询返回的列表是一个“集合”，但 MySQL 并不会主动为你去重。比如子查询返回[1, 1, 1, 2, 2, 3]，实际上它会拿外部表的每一行去跟这 6 个值逐一比较，而不是先压缩成[1, 2, 3]再比较。子查询一旦因为JOIN或者业务逻辑产生重复值，比对次数就会成倍膨胀。

举例来说，查询“最近 30 天下过单但不在黑名单里的用户”，子查询可能是从订单表里捞用户 ID，但订单表一个用户会有多笔订单，如果不加DISTINCT，子查询返回的 ID 会重复很多次。MySQL 优化器在多数情况下能感知到子查询的代价，但它感知到之后的选择并不是“自动去重”，而是“物化成表再扫描”，这个物化表里依然是带重复的。

所以我自己写NOT IN时有一条铁律：子查询先SELECT DISTINCT再套NOT IN。不能依赖优化器帮你收拾残局，它没这个自觉。

2. 什么样的情况该换写法：三方案全量实测对比

讲原理永远是“纸上谈兵”，真正有价值的是一组可复现的对照实验。我构造了一个模拟订单系统的数据环境，专门对比NOT IN、NOT EXISTS、LEFT JOIN三种写法的真实表现。下面是我这次实验的完整设计、测试脚本、结果数据以及一些结论。

2.1 测试环境与数据准备

测试环境和真实业务尽可能接近：

• MySQL 版本：8.0.32，InnoDB，默认配置，未调优。
• 主表orders：模拟订单表，记录 id、user_id、order_time，共 200 万行。user_id 在 1 到 100 万之间随机分布。
• 子查询来源表blacklist：模拟黑名单表，共 1 万行。user_id 从 orders 表里随机抽取，保证两者存在交集。
• 关联字段：两个表的 user_id 列均设立了非唯一索引（因为业务中一个用户可以有多个订单，所以不建唯一索引更符合实际）。
• 查询目标：找出“在 orders 表中有订单记录，但 user_id 不在 blacklist 中的用户数量”。

建表和填充数据的核心脚本如下：

-- 订单表 CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_amount DECIMAL(10,2) DEFAULT 0, order_time DATETIME NOT NULL, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- 黑名单表 CREATE TABLE blacklist ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, add_time DATETIME NOT NULL, INDEX idx_user_id (user_id) ) ENGINE=InnoDB; -- 模拟插入：orders 200万行，user_id 1~100万 INSERT INTO orders (user_id, order_amount, order_time) SELECT (FLOOR(RAND() * 1000000) + 1), RAND() * 1000, NOW() - INTERVAL FLOOR(RAND() * 30) DAY FROM information_schema.columns LIMIT 2000000; -- 模拟插入：blacklist 1万行，user_id 随机从 orders 里抽 INSERT INTO blacklist (user_id, add_time) SELECT DISTINCT user_id, NOW() FROM orders ORDER BY RAND() LIMIT 10000;

这里有个细节：blacklist的插入用了SELECT DISTINCT保证子查询结果本身无重复或尽量少重复，这一点很重要，否则对比结果会被重复值干扰。

2.2 三组查询实测结果：耗时、扫描行数、执行计划解读

核心测试的三种写法如下：

-- 写法 A：NOT IN SELECT COUNT(*) FROM orders WHERE user_id NOT IN (SELECT user_id FROM blacklist); -- 写法 B：NOT EXISTS SELECT COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id); -- 写法 C：LEFT JOIN SELECT COUNT(*) FROM orders o LEFT JOIN blacklist b ON b.user_id = o.user_id WHERE b.user_id IS NULL;

三次执行，清空缓存、固定同一个种子数据，结果如下：

注意：NOT EXISTS 并不是完全不扫描、直接通过索引过滤，它是对orders每一行都去做一次子查询。但因为是WHERE NOT EXISTS，子查询只关心“是否存在”，一旦在blacklist里通过索引找到同 user_id 的第一行就立刻返回，不再继续找。所以它的代价近似于 orders 行数 × 一次索引点查的开销，200 万 × 索引点查，在 MySQL 里大约是秒级以内。

而 NOT IN 的 4.82 秒属于“子查询物化 + 外层逐行比对”的典型表现。在执行计划里，能看到 MATERIALIZED 这个关键字，说明子查询结果已经实体化成临时表，外层再对这个临时表做扫描判断。

LEFT JOIN 的 0.76 秒里还有个非常关键的信息：实际的关联查询把blacklist当成了驱动侧的反连接，执行计划里能看到Using where; Using index，说明它是以索引查找的方式去匹配黑名单，而不是全表扫描。

2.3 为什么数据规模不同结论会变

如果你以为“只要把 NOT IN 换成 NOT EXISTS 或 LEFT JOIN 就一定更快”，那就掉进另一个坑了。这个结论有重要的适用前提。

在子查询结果集非常小的情况下（比如黑名单只有 10 条），NOT IN的表现其实并不差。外部表虽然大，但物化出来的临时表只有 10 行，逐行比对成本极低。反而NOT EXISTS需要对每一行都发起一次索引点查，调用次数多，建立临时数据结构和存储过程调用的开销占比高，有时候反而比 NOT IN 略慢。

反过来，如果子查询结果集非常庞大（比如上百万元素），NOT IN基本就是灾难：临时表大到落盘，每一行外部记录都要在磁盘临时表上做查找。而LEFT JOIN不关心子查询数量多少，只要连接列上有索引，它就是标准的高效关联查询。

所以我的结论是：

• 子查询结果集很小（几百条以内）：三种写法差异不大，NOT IN 可接受。
• 子查询结果集大（几千条以上）且外部表也大（几十万行以上）：优先 NOT EXISTS 或 LEFT JOIN。
• 子查询结果集非常大（几十万条以上）：基本别用 NOT IN，LEFT JOIN 是最稳的选择。
• 两个表都不大（几千行以内）：写啥都无所谓，业务可读性优先。

另外一个总被忽略的因素是NULL。如果你的数据里存在 NULL，NOT IN 不只是性能问题，是结果正确性问题。换 NOT EXISTS 之后，NULL 语义就变了——NULL 会正常参与比较判断，结果不会因一个 NULL 变成空集，这也是我建议在大多数场景下放弃 NOT IN 的核心理由之一。

3. 优化实战：如何把 NOT IN 改写成一等一的好查询

前面把原理和实验讲透了，这一章直接给结论。我根据实际项目中总结出来的经验，把 NOT IN 的改写策略分成三档，分别应对“直接改 SQL”“索引和数据治理”和“业务层根本性解决”三种情况。每一档都有可以直接抄作业的 SQL 和配置。

3.1 常规替换：NOT EXISTS 与 LEFT JOIN 怎么选

假设原始 SQL 是：

SELECT * FROM orders o WHERE o.user_id NOT IN ( SELECT b.user_id FROM blacklist b );

第一步要做的替换就是 NOT EXISTS：

SELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id );

这个改写的正确性是有保障的：只要orders.user_id不为 NULL，NOT EXISTS与NOT IN在语义上是等价的。注意这个前提，如果orders.user_id可能为 NULL，NOT EXISTS的行为会保留这些行（因为 NULL 不参与关联匹配），而NOT IN在子查询无 NULL 时会过滤掉这些行。现实中我一般建议把 NULL 行单独处理，不要在 SQL 里隐式依赖“未知”的结果。

如果希望语义更精确、可控，用 LEFT JOIN 会更直白：

SELECT o.* FROM orders o LEFT JOIN blacklist b ON b.user_id = o.user_id WHERE b.id IS NULL;

LEFT JOIN 的优点是：能通过ON条件显式控制关联逻辑，且WHERE b.id IS NULL这个条件会让优化器执行 “反连接”（anti-join）逻辑，在 MySQL 8.0 里这是效率极高的一种执行形态。

那么 NOT EXISTS 和 LEFT JOIN 怎么选？我的标准是：

• 只需要判断“是否存在”时，用 NOT EXISTS，语义最贴近需求，且不产生额外列。
• 查询里本身就要用到主表的多个字段，且不关心关联表的字段，也用 NOT EXISTS。
• 需要在结果里保留主表的所有列，或将来可能要改成带关联字段输出的，用 LEFT JOIN。
• 在 MySQL 8.0.16 以后的版本里，两者会生成很接近的执行计划，实际差异主要在看索引使用情况和数据量。

3.2 更稳的方案：给连接列加索引并规避 NULL

改写 SQL 是一方面，数据层的底子也得打好。我见过太多场景：SQL 已经按最佳实践改成了 NOT EXISTS，执行计划却照样全表扫描，因为子查询关联的列根本没索引。

在这个案例中，核心索引就是blacklist(user_id)和orders(user_id)。如果主表是 orders、子查询表是 blacklist，那么子查询表上的索引尤其重要：

ALTER TABLE blacklist ADD INDEX idx_user_id (user_id); ALTER TABLE orders ADD INDEX idx_user_id (user_id);

这里的优化原理：NOT EXISTS 对 orders 每一行执行子查询时，子查询内部要迅速判断 blacklist 中是否有相同 user_id，如果没有索引，每次判断都是一次 blacklist 全表扫描，200 万行订单 × 1 万行黑名单 = 200 亿次比较，再多优化器也救不回来。

关于 NULL 的处理，这里给出两条可直接执行的策略：

策略一：把 NOT NULL 约束加上，如果业务逻辑本来就要求 user_id 不能为空，那就在建表语句里直接写user_id INT NOT NULL，从源头上断掉 NULL 分支的可能。

策略二：查询时显式过滤。如果历史数据已经存在 NULL，又实在没办法改表，那就在子查询里强制去 NULL，比如：

SELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id AND b.user_id IS NOT NULL );

别看多了个IS NOT NULL，它能让优化器更自信地走索引关联，同时也把你的意图写明白了，后来维护代码的人不会被 NULL 语义坑到。

3.3 业务层兜底：拆批、限流和控制结果集

有时候 SQL 本身已经最优了，但数据量实在太大，比如 orders 表有几千万行，黑名单也有几十万行，就算用 LEFT JOIN + 索引，一次查询也得扫千万行。这种场景下，SQL 层的优化已经到天花板，得从业务层动刀。

我通常用的手段有三个：

第一，拆批处理。比如业务要分析全量用户，没必要一次跑完。按用户的创建时间或者 ID 范围分批跑，每批只处理 50 万用户，SQL 加一个范围条件：

SELECT * FROM orders WHERE user_id BETWEEN 1 AND 500000 AND NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.user_id = orders.user_id );

拆批之后，每一批查询的耗时都能控制在毫秒级到百毫秒级，资源占用也稳定。这个策略在数据仓库 ETL 任务、定时统计任务里尤其好用。

第二，把黑名单结果预计算成一张小表。比如黑名单其实只有 1 万条，那只需要在业务启动时一次性加载到应用内存的 Set 里，然后在应用层做过滤，根本不用每次都让数据库跑 200 万行的全表扫描。很多“SQL 性能问题”到最后用缓存或者预计算就能彻底绕开。

第三，控制结果集大小。有些业务场景根本不需要返回全量数据，只需要“前 N 个不在黑名单里的用户”，此时可以直接反连接 + LIMIT，但要注意 LIMIT 的语义。比如我们只查前 100 个，写成：

SELECT * FROM orders o LEFT JOIN blacklist b ON b.user_id = o.user_id WHERE b.id IS NULL LIMIT 100;

这样 MySQL 一旦找到 100 条满足条件的记录就会停下来，避免了全表扫描完再排序返回的浪费。注意这个写法依赖反连接的驱动顺序，也就是优化器是以 orders 表逐行去匹配 blacklist，而不是反过来全表扫 blacklist。用 EXPLAIN 确认驱动顺序后，就可以放心用。

4. 常见问题与排查技巧实录（避坑指南）

最后这部分是实战中高频踩坑的汇总。很多问题不是我通过阅读文档解决的，而是线上故障之后一步一步挖出来的。

4.1 先看执行计划，别猜

遇到 NOT IN 性能问题，第一件事绝对不是改 SQL，而是执行EXPLAIN看执行计划。重点看这几个字段：

• type：是否为ALL（全表扫描），是的话要警惕。
• key：有没有用到索引。
• Extra：是否出现Using where; Using temporary，出现意味着有物化临时表。
• rows：优化器的行数估算，注意它会严重低估，但低估本身就是信号。
• MATERIALIZED：出现这个词基本就能实锤子查询物化。

我把第一次分析时拿到的执行计划贴出来：

id | select_type | table | type | key | rows | Extra 1 | PRIMARY | orders | ALL | NULL | 1946000| Using where 2 | SUBQUERY | blacklist | index| idx_user_id | 10000 | Using index

在这个计划里，主查询是全表扫描，子查询走了索引物化。看起来还行，但实际执行时两个阶段串行，物化表产生后再做外部逐行比对，问题就出现在这里。如果改成 NOT EXISTS，计划会变成：

id | select_type | table | type | key | rows | Extra 1 | PRIMARY | orders | ALL | NULL | 1946000| Using where 2 | DEPENDENT SUBQUERY | blacklist | ref | idx_user_id | 4 | Using index

DEPENDENT SUBQUERY+ref意味着对 orders 的每一行，子查询在 blacklist 上做了一个索引点查，200 万次点查的开销明显小于物化全表比对。

注意：DEPENDENT SUBQUERY的字面意思让人害怕，很多人看到这个词就以为“关联子查询很慢”，但实际在 NOT EXISTS 场景里，它是高效的索引点查，真正要警惕的是MATERIALIZED和全表ALL。

4.2 五个高频坑

我把这几年遇到的 NOT IN 相关坑整理成一个速查表，碰到问题直接对照排查：

4.3 如果必须用 NOT IN：三条保命规则

有些场景受限于代码风格或团队规范，确实只能保留 NOT IN 写法，比如遗留系统里存储过程已经写死、或者业务方明确要求“只改索引不准改 SQL”。这时我建议至少做到下面三条：

规则一：子查询必须去重且排除 NULL。用SELECT DISTINCT user_id FROM blacklist WHERE user_id IS NOT NULL的形式包一层，从源头保障子查询结果集是干净集合。

规则二：被驱动表必须有索引。子查询表上的关联列如果没有索引，NOT IN 完全没法救，必须补索引。虽然 NOT IN 的物化执行不一定能直接受益于索引，但在优化器选择“exists 转换”时索引就派上用场了，这也是 MySQL 8.0 优化器的行为之一。

规则三：控制返回量。能加 LIMIT 就加 LIMIT，能让业务分批就分批。NOT IN 写法的复杂度与外部表行数成正比，把外部表的扫描范围缩小，才是真正的对症下药。

4.4 从一次线上故障看完整排查过程

最后分享一下真实项目里的一次排查全过程，完全匿名化处理，但思路可以复现。

某天晚上收到监控告警：某查询接口 P99 延迟从 80ms 涨到 6s。打开慢查询日志，发现是一条带 NOT IN 的统计 SQL。当时的业务场景是查“最近 7 天有支付记录、但没有触发风控策略的用户 ID 列表”，子查询是风控表里的用户集合，约 3.5 万条。

我的排查步骤：

1. 先看执行计划，发现 NOT IN 触发了物化，物化临时表 3.5 万行，外层全表扫描 500 万行。
2. 验证 NULL：查了一下风控表里 user_id 是否有 NULL，结果没有，排除语义陷阱。
3. 试改 NOT EXISTS，执行时间从 6s 降到 1.2s，效果显著。
4. 进一步看执行计划，确认 NOT EXISTS 走的是索引点查，又给风控表的 user_id 建了复合索引（user_id + create_time），把 1.2s 压到 0.3s。

这个案例很好的说明了问题：SQL 层面不仅要把 NOT IN 改成 NOT EXISTS，还要确保索引覆盖关联列。两者叠加，才能真正解决性能问题。

最后分享一点点个人经验

写了好几千字，其实核心思想可以浓缩成一句话：NOT IN 在 MySQL 里是个“语法上很舒服、性能上很危险”的写法，能不碰就别碰，尤其是子查询结果集大、数据量大的时候。

我个人现在写 SQL 的习惯是：默认用 NOT EXISTS 表达“不存在”的语义，除非子查询结果集小到不行才考虑 NOT IN。代码审查的时候看到 NOT IN 也会多追问一句：子查询有没有 NULL？结果集多大？走了执行计划没有？这三个问题问完，问题基本就暴露了。

另外，优化 SQL 时不要只盯着 SQL 本身，索引、NULL 约束、数据分布、业务拆分，这几个层面往往比改写 SQL 本身更关键。有时一条 SQL 改得再漂亮，数据量上来了照样撑不住，提前从业务层规划好数据规模和查询频率，远比事后调优更重要。