IN与EXISTS怎么选：语义、执行计划与NULL陷阱

IN 和 EXISTS 这两个关键字我见过太多人用哪个快用哪个的方式来做选择也见过有人在技术评审里张口就是EXISTS 一定比 IN 快然后被追问在什么条件下就答不上来了。真实情况是这两者在语义上并不完全等价在执行层面谁快谁慢完全取决于优化器怎么改写、子查询结果集多大、外层表有多少行、索引长什么样、统计信息准不准。脱离这些条件谈优劣跟问锤子和扳手哪个好用没什么区别。我印象最深的一次是几年前接手一个报表任务一条带IN (子查询)的 SQL 跑了将近四十分钟还没出结果把它改成EXISTS之后五分钟内出数。当时我也以为找到了万能钥匙结果同一个项目里另一条 SQL 反着改才变快这就很打脸了。后来我把执行计划一条条拉出来对比才把这件事的边界摸清楚。这篇就把我这些年积累下来的理解、实测数据和踩过的坑完整写出来不管你是在写业务查询、做慢 SQL 优化还是在准备技术面试应该都能拿到能直接用的东西。顺带提一句标题里那个 EXSIST 是个常见手滑正确拼写是EXISTS少一个 I 都不行我在命令行里打错过不止一次。1. 把语义掰开看一个在比值一个在探行很多性能问题追到根上其实是语义理解不到位。在讨论谁快之前得先把这两者到底在做什么讲清楚否则后面的优化全是碰运气。1.1 IN 是一次集合成员测试IN的本质是集合成员判断。写成col IN (子查询)数据库要做的事情是把子查询的结果当成一个集合然后判断外层这一行的col值是否落在这个集合里。从标准 SQL 的角度它等价于col ANY (子查询)。这个集合两个字很关键它带来了两个隐含约束。第一子查询返回的必须是一列你写IN (SELECT a, b FROM t)会直接报错因为集合的元素得是同一种类型。第二集合是去重的概念——虽然结果集里有没有重复不影响成员判断的真假但优化器在做物化的时候往往需要建唯一索引来去重这个额外开销后面会详细讲。还有一个更隐蔽的点因为是值比较就必然要面对 NULL。如果外层col是 NULL或者子查询结果集里混进了 NULL整个判断就会进入三值逻辑的 UNKNOWN 状态。这是后面第四节那个经典事故的根源。1.2 EXISTS 只关心有没有一行EXISTS完全换了个思路。它接的是一个相关子查询数据库要做的是对外层每一行把相关条件代入子查询跑一遍只要子查询能返回至少一行结果就是 TRUE一行都返回不了就是 FALSE。注意这里根本没发生值比较。EXISTS (SELECT 1 FROM t WHERE t.k o.k)里面t.k和o.k相等与否是通过 WHERE 条件判断的EXISTS本身只看有没有行。所以它天然免疫 NULL 问题——子查询里的 NULL 只是让 WHERE 条件不成立不会污染最终的真假结果。这也解释了一个新手经常问的问题EXISTS (SELECT 1 ...)和EXISTS (SELECT * ...)有区别吗没有。SELECT 列表在 EXISTS 里是被完全忽略的写1、写*、写NULL都一样优化器压根不会去展开那些列。习惯写SELECT 1更多是给人看的表达我不关心具体值这个意图。1.3 一个容易忽略的行为差异重复行假设外层表orders里有一行对应子查询表order_items里有五条明细。用IN和用EXISTS都不会让这行orders输出五次——两者都只输出一次。这一点上它们是一致的都表现为半连接语义。真正的差异出现在NOT IN和NOT EXISTS上。NOT EXISTS是标准的反连接语义清晰子查询没匹配到就保留。而NOT IN在子查询结果集为空时的行为和NOT EXISTS也不完全一样前者返回空集后者返回全部外层行这个细节在业务上经常导致没数据时报表全空这种诡异现象。我一般跟团队里的新人这么总结IN 是在问这个值在不在这个名单里EXISTS 是在问这个条件下有没有记录。前者是值域问题后者是存在性问题。想清楚你要问的是哪一种选型就有依据了。2. 优化器动手脚的地方半连接改写与物化开销理解语义只是第一步。真正决定性能的是数据库优化器拿到这条 SQL 之后怎么改写它。这块不搞清楚你永远在靠猜。2.1 子查询物化把结果搬进临时表以 MySQL 8.0 为例当它遇到col IN (SELECT ...)且子查询是非相关的子查询里不引用外层表的列一个很自然的处理方式是物化先把整个子查询跑完结果写进一张临时表然后在外层表上逐行去这张临时表里查。物化本身不一定是坏事如果子查询结果集不大、外层表很大一次物化的成本远低于反复执行子查询。但代价也很明确临时表需要建索引而IN语义要求去重所以建的往往是唯一索引插入时每行都要做一次唯一性检查。临时表默认可能落在磁盘上结果集一大会触发落盘性能断崖式下跌。物化之后外层表还是得逐行去临时表里探如果外层表本身没有合适的过滤条件那就是一次变相的全表扫。所以那种子查询结果几百万行、外层表也几百万行的场景IN往往是最差的写法。2.2 半连接改写把子查询拉平成 JOIN现代优化器还有另一条路半连接Semi-Join改写。简单说就是把IN或EXISTS子查询转成一种特殊的 JOIN匹配到就输出一次外层行匹配到多行也不重复输出。MySQL 从 5.6 开始支持这批策略到 8.0 已经相当成熟了常见的有策略大致做法适合的场景FirstMatch外层每行去子查询里找找到第一条就停子查询匹配率高能快速短路LooseScan在子查询表的索引上做松散扫描跳过重复键子查询表上有高选择性的索引Materialization物化成带唯一索引的临时表子查询结果集小、外层表大DuplicateWeedout用临时表记录已输出的外层行防止重复关联条件非唯一这张表我建议记住一个直觉优化器是在短路和批量之间做权衡。FirstMatch 靠的是尽早停止Materialization 靠的是一次算完。到底选哪个取决于它对数据分布的估算。这里有个很多人不知道的点EXISTS 在 8.0 里同样会被尝试改写成半连接。也就是说IN 会被改写、EXISTS 不会被改写这个说法在旧版本上还有点道理在新版本上已经站不住了。如果你在 5.7 或更早的版本上测出 EXISTS 快很多升级到 8.0 之后很可能两条 SQL 的执行计划一模一样。2.3 相关子查询上的路径依赖如果子查询里引用了外层表的列相关子查询情况就复杂了。优化器如果没能把它改写成半连接就会退化成依赖子查询——外层每来一行子查询就完整执行一次。这个代价是 O(外层行数 × 子查询单次成本)外层表一百万行的话基本就是灾难。EXISTS因为语法上通常就是相关的在这种退化场景里特别容易踩雷。我见过一条 SQL外层表一百万行EXISTS子查询里没走索引执行计划里赫然写着每行都要扫一遍子查询表跑了七个小时。而把它改写成IN之后因为子查询是非相关的优化器直接物化几十秒出结果。所以你看谁快谁慢的答案跟语法没有绑定关系绑定的是优化器能不能把它转成半连接以及转成半连接之后选了哪种策略。3. NULL 值这个坑NOT IN 返回空结果的经典事故如果要把 IN 和 EXISTS 的差异排个重要性顺序NULL 处理绝对是第一位的。性能问题顶多让你多等一会儿NULL 问题会让你拿到错误的业务结果还悄无声息。3.1 NOT IN 遇到 NULL 会返回空集这是最经典的坑。x NOT IN (1, 2, NULL)从逻辑上等价于x 1 AND x 2 AND x NULL前两个比较结果可能是 TRUE 或 FALSE但x NULL永远是UNKNOWN。而AND运算里只要有一个操作数是 UNKNOWN整个表达式的结果就不可能是 TRUETRUE AND UNKNOWN UNKNOWNFALSE AND UNKNOWN FALSE。也就是说带 NULL 的那一项会把整个表达式的结果压成 UNKNOWN 或 FALSE永远等不到 TRUE。最终查询返回空结果集。更狠的地方在于子查询里只要混进一行 NULL整个NOT IN就废了。比如你要查没有下过单的客户写成WHERE id NOT IN (SELECT customer_id FROM orders)只要orders.customer_id里有一条 NULL结果就是空——但业务方看到的是所有客户都下过单这个错误极其隐蔽。3.2 NOT EXISTS 为什么天然安全同样的需求改成NOT EXISTSSELECT c.id FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id c.id );EXISTS只看子查询有没有行。即使o.customer_id里有 NULL那行也只是在 WHERE 条件o.customer_id c.id处被判为 UNKNOWN不满足条件不参与匹配不会污染外层结果。语义干净利落。所以我的经验规则是只要是为了表达不存在这个语义优先用 NOT EXISTS除非你能百分之百确认子查询那一列有 NOT NULL 约束。3.3 反连接改写的前提条件这里还有一层技术细节。优化器想把NOT EXISTS改写成反连接Anti-Join也是有前提的子查询的连接列必须是 NOT NULL 的或者优化器能证明它在语义上非空。PostgreSQL 在这方面比较保守——它的NOT IN子查询如果无法确认子查询列非空会生成一种带 NULL 检查的哈希子计划每次匹配都要额外判断 NULL。而一旦子查询列声明了NOT NULL它就能放心改写成标准的反连接性能差异可能有数倍。MySQL 这边的处理更粗糙一些NOT IN在遇到可能含 NULL 的子查询时改写选项会大幅收窄很多时候干脆退化成逐行探测。这又是一个加个 NOT NULL 约束就能提速的真实案例。写法子查询含 NULL 时的结果能否改写成反连接NOT IN返回空结果集仅在子查询列确定非空时可以NOT EXISTS正常返回语义正确大多数情况下可以这半张表我在团队内部文档里贴了三年每次 code review 看到NOT IN都会翻出来对一遍。4. 反直觉的部分有些场景 IN 就是比 EXISTS 快前面说的这些容易让人形成EXISTS 更优的偏见。但实际上我自己实测下来在相当多的场景里IN反而更快。这些场景有共同特征值得单独拎出来讲。4.1 常量列表加索引IN 直接把等值比较吃满这条最容易被忽视。WHERE id IN (1, 2, 3, 100, 250)这种写法里括号里是一串常量根本没有子查询。优化器在解析阶段就会把它展开成若干等值条件的 OR如果id上有索引直接走多次索引查找或者一次 range 扫描。这种场景下写EXISTS反而是给自己找麻烦——你得构造一个临时表或者写一堆 OR没有任何收益。即便列表长一点比如两百个 IDMySQL 依然能走 range 优化。有个参数叫eq_range_index_dive_limit默认 200当 IN 列表的等值条件数量超过它时优化器会放弃逐值做索引潜水改用统计信息估算这时候执行计划可能突然从 range 变成全表扫。这个过了某个数量就变慢的悬崖现象我在批量删除、批量查询的场景里遇到过好几次。解决办法一般不是换语法而是把大批量拆成小批或者改成 JOIN 临时表。4.2 非相关子查询可以做全局去重EXISTS 做不到IN的子查询如果不是相关的优化器有能力先算一次、全局复用。而EXISTS天然是相关的如果优化器没能把它转成半连接就必须逐行执行。举个具体例子。假设有一张blacklist表只有几百行被拉黑的 ID外层是几百万行的users表。写成SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM blacklist);子查询几百行物化成带唯一索引的临时表成本可以忽略。然后优化器可能选择在users上全表扫每行探一次临时表或者反过来用临时表驱动、回表查users。两种都挺快。换成EXISTS版本如果优化器保守可能就变成了对users每行执行一次SELECT 1 FROM blacklist WHERE user_id ?。虽然blacklist.user_id上有索引的话单次很快但几百万次函数调用和索引查找累加起来时间差距是可以到几倍的。4.3 统计信息失真别急着怪语法还有一种情况特别气人两条 SQL 逻辑完全等价执行计划却天差地别。这时候八成不是 IN 和 EXISTS 的问题是统计信息不准。优化器选择物化还是半连接、选择哪个表做驱动表全靠它对行数的估算。如果表刚批量导入过数据没跑ANALYZE或者某个字段的数据分布极度倾斜估算值可能差好几个数量级选出来的计划自然离谱。我在一个日志表上就踩过实际只有 300 行匹配优化器估算成了 30 万行果断选了物化 全表扫结果比逐行探测还慢。场景我更倾向的写法理由常量列表长度可控IN直接展开成等值/range无需额外结构子查询小、非相关、外层大IN物化后全局复用成本极低子查询大、外层小EXISTS外层行数少逐行探测成本可控可短路判断不存在NOT EXISTS规避 NULL 语义陷阱匹配列可空且数据量大先补 NOT NULL 约束再谈约束到位才能走反连接判断顺序我一般是先看语义要不要处理 NULL再看两张表的行数比例最后才看索引。语法本身排在最后一位。5. 自己造数据跑一遍一套可复现的对比实验纸上谈兵没意义。这套实验我在不同项目里复用过很多次成本很低一小时能跑完但能让你对自己环境里的优化器行为建立准确直觉。5.1 建表与数据生成用一张客户表和一张订单表规模刻意做成不对称CREATE TABLE customers ( id BIGINT PRIMARY KEY, name VARCHAR(64) NOT NULL, city VARCHAR(32) NOT NULL ) ENGINEInnoDB; CREATE TABLE orders ( id BIGINT PRIMARY KEY, customer_id BIGINT, amount DECIMAL(10,2) NOT NULL, KEY idx_customer (customer_id) ) ENGINEInnoDB;注意我故意让orders.customer_id可空这样后面能直接演示 NULL 陷阱。数据量用递归 CTE 或者脚本刷客户表 10 万行订单表 200 万行其中故意插入 100 行customer_id IS NULL的记录。插完记得跑一次ANALYZE TABLE否则统计信息不准实验结论会失真。5.2 三条等价 SQL 的执行计划对比-- A: IN 子查询 EXPLAIN ANALYZE SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE amount 500); -- B: EXISTS 相关子查询 EXPLAIN ANALYZE SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id c.id AND o.amount 500); -- C: 显式 JOIN DISTINCT对照组 EXPLAIN ANALYZE SELECT DISTINCT c.* FROM customers c JOIN orders o ON o.customer_id c.id WHERE o.amount 500;EXPLAIN ANALYZE比EXPLAIN有价值得多因为它会真的执行并给出实际行数和耗时。看执行计划的时候重点盯三处驱动表是谁、用了哪种半连接策略、估算行数和实际行数差多少。第三点尤其重要差一个数量级以上就说明统计信息有问题这时候优化语法是白费力气。5.3 换个数据分布再跑一次同一个实验把amount 500换成amount 0几乎全表匹配再跑一遍。你会发现执行计划变了——短路策略失效因为几乎找不到第一条就命中的便宜可占。再把订单表缩到 1000 行客户表放到 100 万行结论又会翻转。我建议至少跑四个组合大表驱动小表、小表驱动大表、高匹配率、低匹配率。跑完之后你会得到一个非常具体的感受优化器的选择是有规律的但规律跟数据分布强相关不是跟关键字强相关。这个直觉比背十条最佳实践管用得多。提示在共享的测试环境里跑EXPLAIN ANALYZE要小心它会真实执行 SQL。带写操作的语句千万别这么干只对 SELECT 用。6. 落地时的判断顺序和几条硬规矩讲了这么多原理和实验最后落到怎么写。我不太喜欢给永远用 EXISTS这种一刀切的规则但确实有几条我一直在用的判断顺序和硬约束。6.1 我的实际判断顺序第一步永远是确认语义。你要的是值在不在名单里还是存不存在匹配记录前者用 IN 更自然后者用 EXISTS 更自然。如果涉及否定语义不包含、没下过单、没有记录默认走 NOT EXISTS除非子查询列明确 NOT NULL。第二步看子查询是不是相关的。如果子查询里引用了外层列先看执行计划有没有被改写成半连接如果退化成依赖子查询优先考虑能不能通过改写把它变成非相关的。这一步的收益往往比换关键字大得多。第三步才看行数比例。子查询结果小、外层表大IN 物化通常占优子查询大、外层表小EXISTS 短路通常占优。但这个结论必须用EXPLAIN ANALYZE验证别靠估。第四步是检查基础设施索引有没有、统计信息新不新、字段该不该加 NOT NULL 约束。我见过太多调了半天语法最后发现是索引建错了列顺序的案例。6.2 几条我基本不破的规矩绝不写NOT IN加可空子查询列。这条没有例外加约束或者换写法二选一。常量 IN 列表超过几百个就分批。不是因为语法有问题是因为优化器在长列表上的处理会退化行为不可预测。改完 SQL 一定对比执行计划不看计划就宣称优化了是自欺欺人。同样的 SQL 在不同数据量下可能选不同计划上线前用生产规模的样本数据验一遍。别迷信SELECT 1比SELECT *快。在 EXISTS 里两者等价在 IN 子查询里返回多列会直接报错这跟性能无关。注意IN列表里混入 NULL 的影响。x IN (1, 2, NULL)在 x 等于 1 或 2 时返回 TRUE没问题但NOT IN版本直接废掉。这个不对称性坑过很多人。6.3 一个容易被误判的细节还有一种情况值得单独说IN子查询里如果带了DISTINCT很多人以为这是必须的。其实不是——IN的语义本身就是集合成员判断重复值不影响结果。写DISTINCT反而可能阻止优化器选择某些半连接策略因为它会影响对子查询结果基数的估算。反过来如果你用的是JOIN写法DISTINCT才是必须的因为 JOIN 会放大行数。这个差异经常被搞混IN 和 EXISTS 天然不放大行数JOIN 会。理解这一点你就明白为什么很多JOIN 改 IN的优化能生效——不是因为 IN 更快是因为省掉了去重那一步的开销。我个人在实际操作中的体会是这类问题的答案从来不在语法书里。同一个写法在数据量小的时候、在数据倾斜的时候、在索引刚建好的时候表现可能完全不同。真正有价值的不是记住用哪个而是养成拿到一条慢 SQL 先看执行计划、先看估算与实际行数偏差、再看数据分布的习惯。这套动作走熟了IN 还是 EXISTS 这个选择本身反而变成了最不需要纠结的一步。