MySQL 中 IN 和 EXISTS 的性能选择

sql -- 使用 IN 子查询 SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE vip = 1);

`EXISTS` 用于判断子查询是否至少返回一行数据，它不关心子查询的具体内容，只关心是否存在结果。

```sql
-- 使用 EXISTS 子查询
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.vip = 1);

两者在语义上可以相互转换，但 MySQL 优化器对它们的处理方式往往不同，从而导致性能差异。

执行原理对比

IN 的工作方式

MySQL 5.6 及之后的版本对 IN 子查询进行了大量优化，主要手段是将子查询转换为 半连接（Semi-join） 或 物化（Materialization）。

• 物化：先把子查询的结果集存到一张临时表（内存或磁盘），然后让外层查询与这张临时表做连接（通常使用哈希索引）。当子查询结果集较小时，这种方式非常高效。
• 半连接：把子查询“拉平”到外层，直接利用连接算法（如 Nested Loop Join）执行，但保证每个外层行最多只匹配一次。优化器会根据统计信息选择 Table Pullout、FirstMatch 等策略。

也就是说，现代 MySQL 中的 IN (子查询) 不再是简单的“遍历外层每一行，去执行子查询”，而会更像一次智能的连接操作。

EXISTS 的工作方式

EXISTS 子查询通常是 关联子查询（Correlated Subquery），即子查询中引用了外层表的列。对于外层查询的每一行，MySQL 都会执行一次内层子查询。但由于 EXISTS 采用短路机制——一旦找到第一条匹配记录就立即停止扫描，因此在内层表有合适索引的情况下，即便外层行数很多，实际的开销也可能很小。

-- 执行逻辑类似于：
-- 对于 orders 表中的每一行 o：
--   在 customers 表中搜索 c.id = o.customer_id AND c.vip = 1 的记录，
--   找到第一条就返回 true，停止继续搜索。

性能选择的核心原则

没有一种写法在所有场景下都是最快的，选择需要依据两表的大小关系和索引情况来决定。有一条通用的经验法则：以小表驱动大表。

示例说明

假设 orders 表有 100 万行，customers 表只有 100 行 VIP 客户。

-- 推荐 IN：子查询结果小，物化后驱动大表
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE vip = 1);

反过来，如果 orders 表只有 100 行，customers 表有 100 万行，并且 customers.id 上有主键索引：

-- 推荐 EXISTS：外层只有 100 行，每行通过索引速查 customers
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.vip = 1);

索引的影响

索引对两者的影响都非常大，但对于 EXISTS 尤为关键。

• IN：如果子查询被物化，内层查询本身可能受益于索引，物化结果集会存放在搜索效率较高的临时表中。外层与临时表的连接也可能使用到外层表上的索引（例如 customer_id 上的索引）。
• EXISTS：子查询中用于关联的列（如 c.id = o.customer_id）必须加上索引。没有索引时，对外层每一行，内层都会做全表扫描，性能会极速下降。

特别注意的问题：NULL 值和 NOT IN

使用 IN 和 NOT IN 时，如果子查询结果中包含 NULL 值，会得到意想不到的结果，甚至是严重的逻辑错误。

• value IN (1, 2, NULL) 在 value = 2 时返回 TRUE，结果正常。
• value NOT IN (1, 2, NULL) 永远返回 FALSE 或 NULL，因为 MySQL 无法确定 value 是否不等于 NULL，整个表达式的真值变成了 UNKNOWN，被当作 FALSE 处理。

-- 危险示例：如果 customers 表的某个 name 为 NULL，
-- 则以下查询可能一条数据都查不出。
SELECT * FROM orders
WHERE customer_name NOT IN (SELECT name FROM customers WHERE ...);

安全的替代方案是使用 NOT EXISTS：

SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c
    WHERE c.name = o.customer_name AND ...
);

NOT EXISTS 没有 NULL 的歧义问题，而且通常可以利用索引提供良好的性能。

使用 EXPLAIN 进行验证

理论只是指导，最终决策应基于 EXPLAIN 分析。执行类似下面的命令观察执行计划：

EXPLAIN SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.vip = 1);