SQL 查询优化是每个数据库开发者的关键技能。虽然许多开发者记住了“永远不要用 IN，永远用 EXISTS”这样的规则，但现实更为复杂。本文深入探讨常见的 SQL 反模式——IN、NOT IN 和缺失的索引，解释它们何时会损害性能，NULL 值如何静默破坏结果，以及如何重写查询以保证正确性和速度。
何时 IN 是安全的，何时不安全
IN 运算符对于小型静态集合完全没问题——例如，按少量状态值过滤：
SELECT * FROM orders
WHERE status IN ('PAID', 'SHIPPED', 'FINISHED');
这里，数据库可以高效地评估列表。无需将其重写为 EXISTS。
问题出现在 IN 包含一个返回大量行的子查询时：
SELECT *
FROM customer
WHERE id IN (
SELECT customer_id
FROM orders
WHERE amount > 1000
);
如果 orders 表很大，数据库必须将每个 customer.id 与可能数百万的 customer_id 进行匹配。现代优化器（如 MySQL）可以将其转换为半连接（semijoin），但这仅在统计信息、索引和查询结构都理想的情况下才有效。你不能依赖优化器来修复写得不好的查询。
EXISTS 的优势
对于存在性检查——“这个客户有没有高价订单？”——EXISTS 在语义上更清晰，通常也更快：
SELECT c.*
FROM customer c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.amount > 1000
);
EXISTS 一旦找到一条匹配记录就会停止扫描。有了合适的索引，对于大型子查询，这比 IN 高效得多。
为 EXISTS 创建索引
创建一个覆盖复合索引，同时支持连接和过滤：
CREATE INDEX idx_orders_customer_amount
ON orders(customer_id, amount);
没有索引，无论是 IN 还是 EXISTS 都无法拯救你免于全表扫描。
NOT IN 的 NULL 陷阱
NOT IN 危险不是因为慢，而是因为当子查询包含一个 NULL 值时，它可能返回零行。
考虑：查找从未下过订单的客户。
-- 错误：如果 orders.customer_id 中有任何 NULL，则返回空结果
SELECT *
FROM customer
WHERE id NOT IN (
SELECT customer_id
FROM orders
);
为什么？在 SQL 中，NULL 表示“未知”。表达式 1 NOT IN (2, 3, NULL) 的结果是 UNKNOWN（而不是 TRUE），因为数据库无法保证 1 不等于那个未知值。WHERE 子句只返回条件为 TRUE 的行，因此整个查询什么也不返回。
这是一个静默数据错误——没有报错，只是结果错误。
更安全的替代方案：NOT EXISTS
SELECT c.*
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
NOT EXISTS 不受子查询中 NULL 值的影响。它逐个检查每个客户，并返回那些没有匹配订单的客户。
JOIN 替代方案与重复数据处理
当你需要关联表的字段时，JOIN 可能更合适：
-- 有高价订单的客户（可能产生重复）
SELECT DISTINCT c.*
FROM customer c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 1000;
注意 DISTINCT——一个客户如果有多个符合条件的订单，否则会多次出现。
对于反连接（查找不匹配的记录），LEFT JOIN / IS NULL 是一种常见模式：
SELECT c.*
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
这在逻辑上等同于 NOT EXISTS。哪个更快？始终用 EXPLAIN 检查。
使用 EXPLAIN 诊断性能
永远不要猜测性能。使用 EXPLAIN（或 EXPLAIN ANALYZE）查看执行计划：
EXPLAIN
SELECT c.*
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
关注：
- 索引扫描 vs. 全表扫描
- 预估行数 vs. 实际行数
- Using temporary 或 Using filesort（危险信号）
- DEPENDENT SUBQUERY（通常很慢）
如果你看到大表上的全表扫描，在连接列上添加索引。
常见陷阱
- 对可能包含 NULL 的子查询使用 NOT IN——结果静默为空。
- 假设优化器会修复糟糕的 IN 子查询——它可能不会，尤其是在数据倾斜时。
- 使用 JOIN 进行存在性检查时忘记 DISTINCT——重复数据悄悄出现。
- 添加过多索引——它们加快读取但减慢写入。
- 跳过 EXPLAIN——没有数据的优化是猜测。
常见问题
IN 和 EXISTS 有什么区别？
IN 计算一个值列表并检查外层值是否存在于列表中。EXISTS 检查子查询中是否存在至少一行。对于大型子查询，EXISTS 通常更快，因为它可以短路。
为什么 NOT IN 在有 NULL 时返回空行？
因为 NULL 不是一个值——它是未知的。x NOT IN (list) 如果列表中有任何元素是 NULL，则返回 UNKNOWN，而 WHERE 子句只保留条件为 TRUE 的行。
我应该总是用 EXISTS 替换 IN 吗？
不。对于小型静态列表（例如 IN ('A', 'B', 'C')），IN 清晰且高效。将 EXISTS 保留给可能返回大量行的子查询。
如何在 NOT EXISTS 和 LEFT JOIN / IS NULL 之间选择？
两者在逻辑上等效。用你的数据和 EXPLAIN 测试。它们通常产生相同的计划，但根据索引和统计信息，其中一个可能更快。
SQL 性能最重要的是什么？
连接列和过滤列上的索引。 没有索引，任何将 IN 重写为 EXISTS 的操作都无法拯救你免于全表扫描。始终检查 EXPLAIN 以确认你的查询使用了索引。
在分析之前，试试我们的 SQL 格式化工具 来清理你的查询。