先交代一个我实际碰到过的场景某在线商城做活动运营要拉一份“非会员订单明细”开发顺手写了一版NOT IN子查询当时测试环境也就几千条数据跑得飞快谁也没当回事。结果上线第二天这条 SQL 在高峰期直接把主库 CPU 拉满慢查询日志里满满当当全是它单次执行时长三十多秒。等我把执行计划捞出来一看DEPENDENT SUBQUERY外层订单表一百多万行子查询全表扫按这个执行方式算下来理论扫描行数是百万乘以百万的级别——这已经不是调参能救的了。后来改了一版NOT EXISTS同样的表、同样的索引、同样的业务逻辑跑到几百毫秒。从那以后我就把NOT IN当成了高危语法来对待。这篇文章就把这个坑彻底拆开聊NOT IN为什么会慢、慢在哪、什么时候会踩 NULL 陷阱、怎么改写才能让优化器走高效的 anti join。适合正在写业务 SQL 的后端开发也适合负责数据库优化的 DBA看到就是赚到。1. NOT IN 语句先看看它到底慢在哪很多人对NOT IN的印象停留在“能用偶尔慢”但其实这个语法在 MySQL 里有三个非常隐蔽的坑NULL 值语义、子查询执行模型、索引匹配方式。这三个坑叠加在一起才是线上性能事故的真正根源。1.1 三个隐藏的“坑”NULL、逐行执行与索引空转先说 NULL 陷阱这是最容易被忽略的一个。SQL 里的判断逻辑是三值逻辑除了 TRUE 和 FALSE还有 UNKNOWN。NOT IN展开后的判断是“不等于列表里任何一项”翻译成逻辑就是多个AND条件的叠加。但凡其中一项和 NULL 比较结果就是 UNKNOWN整体条件不成立这一行就被过滤掉。直接看例子SELECT 命中 AS result WHERE 1 NOT IN (1, 2, NULL); -- 返回结果空没有“命中”1 NOT IN (1, 2, NULL)等价于1 ! 1 AND 1 ! 2 AND 1 ! NULL前两个条件都是 FALSE 或 TRUE但第三个1 ! NULL是 UNKNOWN整个 AND 链的结果就是 UNKNOWN所以查询返回空。放到实际业务里更可怕SELECT * FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM vip_customers);只要vip_customers.customer_id这一列里存在任何一个 NULL 值整个查询结果就是空的。你查“非会员订单”结果一行都不返回运营那边看到的数据就是空白。而且这个结果不是报错是静默出错线上排查很难想到是 NULL 的问题。再说执行模型。MySQL 对NOT IN子查询的经典执行方式是DEPENDENT SUBQUERY也就是相关子查询。什么叫相关外层查询每取出一行都要拿这一行的值去执行一次子查询。外层订单表有 100 万行子查询就被执行 100 万次。如果子查询走的还是全表扫描哪怕另一张表只有 20 万行实际对比次数也是 100 万乘以 20 万这个量级放在任何机器上都是灾难。最后是索引空转问题。很多开发者以为内外表关联列都有索引子查询就一定会走索引。但实际上NOT IN转成DEPENDENT SUBQUERY之后优化器对子查询的优化非常有限尤其在 5.7 版本里经常出现内层查询type ALL的情况。就算索引存在也可能因为字符集不一致、排序规则不一致、数据类型隐式转换而无法使用。索引建了等于白建全表扫描一次比一次实打实。1.2 执行计划怎么读从 EXPLAIN 看 NOT IN 的真实代价不要猜直接把 SQL 丢进EXPLAIN看执行计划。以下是一个典型场景EXPLAIN SELECT * FROM orders WHERE customer_id NOT IN ( SELECT customer_id FROM vip_customers );这是 MySQL 5.7 和部分 8.0 场景下的典型输出-------------------------------------------------------------------------- | id | select_type | table | type | key | rows | -------------------------------------------------------------------------- | 1 | PRIMARY | orders | ALL | NULL | 1200000 | | 2 | DEPENDENT SUBQUERY | vip_customers | ALL | NULL | 200000 | --------------------------------------------------------------------------看到两个关键信息DEPENDENT SUBQUERY和type ALL。前者说明子查询是相关子查询外层每行执行一次后者说明子查询没有走索引每次执行都是全表扫描。120 万行订单表每条都触发一次 20 万行的全表扫描理论上最大比较次数是 2400 亿次。没有索引的情况下这个 SQL 存在慢查询日志里一点都不冤枉。这里补充一个实操小技巧5.7 里看EXPLAIN就知道有没有救但如果你用的是 MySQL 8.0.18 及以后建议直接上EXPLAIN ANALYZE它能输出每个节点真实执行的时间、行数和循环次数一眼就能看到“子查询执行了 120 万次”这样的现实。没有它很多优化效果只能靠猜。1.3 版本差异5.7 与 8.0 的优化器行为差异MySQL 8.0 对子查询的优化能力比 5.7 强很多但这不代表NOT IN就完全安全了。8.0 优化器会把部分非关联子查询物化处理8.0.18 之后还引入了 hash join能够把某些NOT IN改写成 anti join 执行。但要注意优化器的这些转换是有前提条件的聚合函数、NULL 值可能性、关联条件复杂度过高都可能让优化器放弃转换退回逐行执行的老路。版本NOT IN 子查询的典型行为风险等级MySQL 5.6基本只支持 SUBQUERY/DEPENDENT SUBQUERY逐行执行高MySQL 5.7增加 semi-join 物化优化但 NOT IN 仍常走 DEPENDENT SUBQUERY高MySQL 8.0.17 及之前优化器更智能部分场景可物化子查询中MySQL 8.0.18支持 hash join有机会转换成 anti join中MySQL 8.0 含聚合/复杂子查询仍可能退化为逐行执行高所以我的建议很直接在生产环境不要赌优化器直接写能明确触发 anti join 的语法从源头避免不确定性。MySQL 5.7 到 8.0 的升级不是万能药SQL 写法本身才是根本。2. 性能瓶颈背后的原理剖析只看表面现象不够想要真正掌握优化方法得理解NOT IN和NOT EXISTS在数据库引擎层面到底是怎么执行的。这一节把原理拆开讲透。2.1 相关子查询的“逐行执行”模式DEPENDENT SUBQUERY是被动式执行模型的典型代表。数据库的嵌套循环连接Nested Loop Join理论上就是内外两层循环外层表出驱动行内层表逐行匹配。这种模式本身不可怕正常的索引连接也能跑得很快。可怕的是NOT IN子查询在 5.7 版本中经常拿不到索引导致内层查询从“索引查找”变成了“全表扫描”。用个生活化的类比帮你理解NOT IN的逐行执行方式好比超市收银员每扫一件商品都要跑到仓库里把所有货物清点一遍确认这件商品不在库存清单里。一件商品清点一次一千件商品就清点一千次每次都是全套动作。而正常带索引的关联查询相当于收货的时候已经把货物按货架位置编好了号收银员直接根据编号查位置一次定位。这就是为什么同样的逻辑写法不同性能能差出几万倍。代价估算公式也很简单外层表 m 行内层表 n 行无索引全表扫描时内层每次代价约 n 次比较总代价 O(m*n)。加入索引后内层代价降到 O(log n)总代价 O(m log n)。m 和 n 都在百万量级时这两个公式算出来的差异是天文数字。2.2 NOT IN 与 NOT EXISTS 的本质区别NOT IN和NOT EXISTS在语义上有本质差别它们的执行路径也因此完全不同。NOT EXISTS基于二值逻辑它关心的是“是否存在满足条件的行”一旦找到一行能匹配立即短路返回而NOT IN需要判断“是否不等于集合中的每一个值”遇到 NULL 就卡住没有短路机制。-- NOT EXISTS 写法 SELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM vip_customers v WHERE v.customer_id o.customer_id );当子查询结果集中存在 NULL 时NOT EXISTS依然能正确返回“不在集合中”的行因为它的判断不涉及 NULL 比较。而NOT IN直接短路返回整个查询为空。这也是为什么官方文档和社区都在强调子查询结果列有 NULL 可能时把NOT IN改成NOT EXISTS不仅是为了性能更是为了结果正确性。从优化器角度看NOT EXISTS是 anti join 的标准表达形式。MySQL 8.0 的优化器识别出这种反连接模式后会优先选择 hash anti join 或 batched key access 等方式避免逐行执行。NOT IN在某些情况下也能被识别为 anti join但优化器会额外做 NULL 安全检查一旦发现潜在 NULL 风险就会退化为保守的逐行执行。2.3 驱动表与被驱动表谁驱动谁影响有多大SQL 优化中有一个经典原则小表驱动大表。这个原则放在NOT IN和NOT EXISTS身上尤其明显。NOT IN的语义结构决定外层表是驱动表内层子查询是被驱动表在执行计划里几乎无法互换。如果外层表是大表那么相当于大表逐行驱动小表查询每次都重新执行一遍子查询性能自然差。而NOT EXISTS改写后优化器有更大的自由度选择驱动表。哪个表更适合做驱动、哪个表更适合做被驱动优化器会根据统计信息和索引情况做成本估算。只要关联列存在索引优化器通常会选择小表驱动大表让带索引的大表做被驱动侧查找。来看一个真实估算场景。订单表 120 万行会员表 20 万行NOT IN执行计划往往固定为订单表驱动、子查询全表扫描对比次数约 2400 亿次NOT EXISTS优化器选择会员表驱动20 万次循环订单表走主键索引查找每次 O(log n) 级别总代价可能低于百万次同样的表结构、同样的业务逻辑仅仅是语法的不同执行计划完全不同。这就是为什么说“SQL 写法决定性能下限”你写成什么样数据库就只能尽量在这个框架内优化。3. 优化方案对照与实战操作理论铺垫够了进入实战环节。这一节给三类改写方案NOT EXISTS、LEFT JOIN IS NULL、临时表物化 JOIN并附上实际执行计划和耗时对比可以直接抄作业。3.1 方案一换成 NOT EXISTS 改写最推荐的改写方案语义最接近原版改动最小SELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM vip_customers v WHERE v.customer_id o.customer_id );注意点子查询的 SELECT 列表写1就行不关心具体查什么列只关心是否存在匹配行。另外关联条件v.customer_id o.customer_id两边的列类型和字符集需要完全一致否则索引照样失效。为什么推荐它因为NOT EXISTS的语义是“找不到匹配就返回”优化器可以放心地把它转换成 anti join。5.7 里虽然不会生成 hash anti join但至少关联查询能走ref类型索引访问内层扫描量大大降低。8.0.18 之后如果满足 hash join 条件性能还会再上一个台阶。对比EXPLAIN------------------------------------------------------------------------------------ | id | select_type | table | type | key | rows | Extra | ------------------------------------------------------------------------------------ | 1 | SIMPLE | vip_customers | index | PRIMARY | 200000 | Using index | | 1 | SIMPLE | orders | ref | idx_customer | 6 | Using where | ------------------------------------------------------------------------------------注意执行计划的顺序vip_customers在前面orders在后面。这意味着优化器选择了会员表做驱动表订单表通过idx_customer索引做被驱动查找扫描行数从“百万级乘以百万级”降到了“二十万次索引命中”差距一目了然。3.2 方案二LEFT JOIN IS NULL 改写反连接的另一种标准写法用外连接配合空值判断SELECT o.* FROM orders o LEFT JOIN vip_customers v ON o.customer_id v.customer_id WHERE v.customer_id IS NULL;逻辑上很好理解先左连接能连上说明是会员连不上说明不是会员最后把连不上的行过滤出来。这种写法非常直观也是很多 SQL 规范文档里推荐的“避免子查询”方案。从优化器角度LEFT JOIN ... WHERE ... IS NULL是一种明确的 anti join 模式执行计划会显示Anti或类似标记。在 MySQL 8.0.18 中它会优先使用 hash anti join性能非常稳定。和NOT EXISTS相比两者在多数场景下性能相当但LEFT JOIN写法更适合“从结果集里剔除某些行”的思维模式可读性更强。不过有一个细节要提醒如果连接列允许 NULLLEFT JOIN写法本身是安全的因为IS NULL判断的是连接后的主表列不会受到子查询 NULL 影响。但如果你写的是RIGHT JOIN且判断方向搞反结果就容易出错。建议统一用LEFT JOIN方向固定好记不易错。3.3 方案三临时表物化 索引 JOIN有些复杂场景子查询里带了聚合、GROUP BY、多表关联直接改NOT EXISTS可能因为关联条件太复杂而难以优化。这时候可以先物化结果再走 JOIN-- 第一步物化子查询结果 CREATE TEMPORARY TABLE tmp_vip AS SELECT DISTINCT customer_id FROM vip_customers WHERE status 1; -- 第二步给临时表加索引 ALTER TABLE tmp_vip ADD PRIMARY KEY (customer_id); -- 第三步反连接查询 SELECT o.* FROM orders o LEFT JOIN tmp_vip v ON o.customer_id v.customer_id WHERE v.customer_id IS NULL;主键索引必须加否则临时表全表扫描性能还是上不去。这个方案的适用场景很明确子查询本身计算复杂度高、无法直接走索引关联、或者子查询结果需要复用。它的优点是把“逐行执行子查询”变成了“子查询只算一次之后纯索引关联”计算量大幅下降。临时表方式还有一个额外好处物化出来的结果集如果很小比如只有几百行那么全量加载到内存里做 hash 关联非常快即使结果集有几万行配合主键索引做 nested loop 也比重复执行子查询快得多。但它也有代价——临时表占用内存或磁盘空间所以不适合无限大的结果集实践经验是物化结果在千万行以内都还能接受。3.4 各方案执行计划与耗时对比实测为了让你直观看到差异我放一组测试环境的实测结果。环境MySQL 8.0.28订单表 120 万行customer_id 有普通索引会员表 20 万行customer_id 有唯一索引。业务需求统计非会员订单量。写法执行计划关键特征扫描行数估算实测耗时NOT INDEPENDENT SUBQUERY 逐行执行120万 × 20万32 秒NOT EXISTS会员表驱动订单表 ref 查找20万次单行查找0.8 秒LEFT JOIN IS NULL8.0 hash anti join120万 20万全量读一遍0.5 秒临时表 JOIN物化一次索引关联20万 索引查找0.7 秒这个数据不是极端场景就是很普通的业务量。120 万行的订单表在电商行业只能算小表20 万行的会员表更是常态。即便如此NOT IN的耗时是其他写法的几十倍。如果数据量翻十倍这个差距会扩大到不可接受的程度。有一个细节我说一下LEFT JOIN IS NULL在 8.0.18 版本里的耗时经常比NOT EXISTS更短因为它用 hash anti join 时只需要把两张表各自完整读一遍在内存里做哈希匹配不需要反复回表。但低版本 MySQL 没有这个算子LEFT JOIN有时候反而比NOT EXISTS慢。所以版本也要考虑进去。4. 优化过程中容易踩的坑与排查技巧改写方案写了半天你会发现一个残酷的现实有时候写完NOT EXISTS一跑执行计划还是全表扫描。优化并没有一步到位。这一节把我在实际排查中遇到的高频问题整理成清单按图索骥能省不少力气。4.1 索引明明存在却没用上的排查思路最常见的情况关联列明明建了索引但EXPLAIN显示type ALLkey NULL。这时候不要怀疑人生按顺序查下面几个点。第一检查数据类型是否一致。orders.customer_id是bigintvip_customers.customer_id是varchar这种情况 MySQL 会对列做隐式类型转换索引大概率失效。看两张表的SHOW CREATE TABLE类型不一致就改类型或者显式 CAST。第二检查字符集和排序规则。一张表是utf8mb4另一张是utf8关联时索引也会失效。用下面的 SQL 快速确认SELECT t1.table_name, t1.column_name, t1.character_set_name, t1.collation_name, t2.table_name, t2.column_name, t2.character_set_name, t2.collation_name FROM information_schema.columns t1 JOIN information_schema.columns t2 ON t1.column_name t2.column_name WHERE t1.table_name orders AND t2.table_name vip_customers;第三检查函数包裹。如果你在关联列上写了函数比如DATE_FORMAT(create_time)或者在关联条件里对列做函数运算索引必然失效。WHERE v.customer_id o.customer_id没问题但WHERE LOWER(v.customer_id) LOWER(o.customer_id)就是灾难。第四检查 NULL 值占比。如果某列的 NULL 值行数占比特别高优化器可能认为“走索引回表还不如全表扫描”主动放弃索引。这种场景下可以考虑给列加NOT NULL约束或者拆分查询条件。4.2 优化器选错执行计划的干预手段有时候索引没问题、类型没问题但优化器就是选了错的执行计划。常见的原因是统计信息过期优化器手里的数据失真了。先执行ANALYZE TABLE orders;和ANALYZE TABLE vip_customers;刷新统计信息再看执行计划是否变化。如果统计信息没问题还是选错可以在 SQL 层面用STRAIGHT_JOIN强制连接顺序。注意STRAIGHT_JOIN会强制优化器按表在 SQL 中出现的顺序连接不会自动选择最优驱动表所以使用前要自己明确哪张表更适合做驱动表。SELECT o.* FROM vip_customers v STRAIGHT_JOIN orders o ON v.customer_id o.customer_id WHERE v.customer_id IS NULL;这里的顺序是vip_customers在左、orders在右配合STRAIGHT_JOIN会强制以会员表为驱动表。不过它是把双刃剑一旦后续表数据量变化这个固定连接顺序可能变成性能瓶颈。谨慎使用上线前用不同数据量压一遍。更精细的干预是调整optimizer_switch。比如确认子查询物化是否开启SELECT optimizer_switch LIKE %materializationon%;如果物化被关闭某些场景下子查询执行计划会很差。但全局改optimizer_switch影响面太大不建议线上直接动还是从 SQL 层面改写更安全。4.3 常见问题速查表现象可能原因排查与解决办法NOT IN 返回空结果子查询结果列存在 NULL改用 NOT EXISTS 或 LEFT JOIN IS NULLNOT IN 执行计划为 DEPENDENT SUBQUERY子查询关联列无索引给关联列加索引或改写为 NOT EXISTS改写后仍全表扫描两张表关联列类型/字符集不一致检查信息架构表统一列定义8.0 仍不使用 hash anti join关联列含 NULL 或条件过于复杂使用临时表物化 索引 JOIN参考数据量膨胀后执行计划突变统计信息过期执行 ANALYZE TABLE 刷新统计信息明明有索引但 type 为 ALLNULL 值占比过高隐式转换调整列约束修正类型或使用 FORCE INDEX如果你遇到某个慢查询但列不在表里优先用EXPLAIN ANALYZE看最耗时的节点精确到算子级别。这个工具比EXPLAIN信息丰富得多能输出“每个节点的循环次数、单次耗时、实际返回行数”排查效率提升一个档次。这里再分享一个我自己的排查经验。有一回线上NOT EXISTS改写后还是慢查来查去最后发现是vip_customers.customer_id和orders.customer_id一个int一个varchar隐式转换导致索引失效。那两条 SQL 之前分别是从不同业务模块建的表结构根本没有人统一设计。后来把varchar改成bigint问题立刻消失。类型统一这件事在建表阶段就搞定后面能省太多事。根据我个人的体会NOT IN这个语法在 MySQL 里属于“能用但不要随便用”的类型数据量小、受控环境、字段绝对不会有 NULL那没什么问题一旦上了生产、数据量过百万、子查询来源复杂强烈建议直接禁用这种写法通过规范把风险扼杀在代码评审阶段。最后给你一个小技巧建一个团队内部的 SQL 规范文档明确列出“子查询一律用 EXISTS / JOIN 表达禁止写 NOT IN 和 IN 大表子查询”并在发布会流水线里加一条EXPLAIN检查。这件事听起来简单但真能帮你挡掉不少线上事故。反正我自己在经历过几次深夜慢查询报警之后已经把这条规则当成铁律了。