本文围绕 MySQL IN 与 NOT IN 条件的实战指南：用法、常见坑点与性能优化，带来系统性讲解与实战示例，帮助开发者在实际项目中正确使用这两种集合判断，提升查询效率并避免常见陷阱。
核心要点：理解 IN 与 NOT IN 的语义差异、留意 NULL 值的影响、并结合索引与 EXISTS/NOT EXISTS 的替代方案，以实现更稳定的查询性能。
1. IN 与 NOT IN 的基本用法与语义
1.1 IN 的基本用法
IN 运算符用于判断列值是否在给定的值集合中，语义上等同于多次使用相等比较的逻辑“或”。它适用于固定集合、较短的常量列表，以及需要一次性完成多值判断的场景。使用 IN 可以让 SQL 读起来更直观，并让优化器有机会使用索引或短路过滤。
在进行 IN 查询时，请确保集合中的值与列的数据类型一致，以避免隐式转换带来的性能下降或类型不匹配的问题。集合长度较短时，IN 通常性能良好；长度较长时，需关注执行计划的变化。
-- 示例：查找 ID 在指定集合中的用户
SELECT id, username
FROM users
WHERE id IN (1, 2, 3, 4, 5);
1.2 NOT IN 的基本用法
NOT IN 用于判断列值不在某集合中的记录，逻辑等价于对集合的“非包含”。然而在实际使用中，需要注意 NULL 值带来的特殊行为：集合中只要存在一个 NULL，NOT IN 的结果在某些场景下会返回空集合，导致意外返回空结果。这也是 NOT IN 的一个常见坑点。
为了避免 NULL 引发的不可预期，常见做法是明确排除 NULL，或使用 NOT EXISTS 来替代 NOT IN。
-- 示例：查找不在黑名单中的用户
SELECT id, username
FROM users
WHERE id NOT IN (SELECT user_id FROM blacklist);
2. 使用场景与常见坑点
2.1 常见坑点：NULL 值与空结果
在 NOT IN 的子查询包含 NULL 时，结果可能导致整张表返回空集合，给开发者带来困扰。这类情况在实际项目中较为常见，尤其 when 子查询来自外部数据源或动态表时，更应谨慎处理。
解决方案通常包括使用 NOT EXISTS 替代 NOT IN，或在 NOT IN 的子查询中显式排除 NULL 值，以确保逻辑正确性。
-- 使用 NOT EXISTS 作为替代
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = o.customer_id
);
2.2 子查询对性能的影响
将 NOT IN 与子查询结合时，数据库需要重复执行子查询的比较，可能造成执行计划膨胀和缓存失效。对于大数据量的子查询，IN/NOT IN 的性能可能不及使用 JOIN 或 EXISTS 的等价写法。
在此场景下，优先考虑将子查询转化为关联查询，利用索引列进行连接，并让优化器选择最优路径。
-- 优化示例：用 EXISTS/NOT EXISTS 替代 IN/NOT IN
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = o.customer_id
);
3. 性能优化策略
3.1 避免把大量常量放在 IN 列表中
当集合非常庞大时，IN 的常量列表会增加解析与排序成本，影响执行计划。此时可以将大集合改为基于子查询的方式，或将集合存入临时表/表变量再进行连接判断。
通过使用临时表或分区表等结构，可以让优化器更好地使用索引，从而提升查询吞吐。下面给出一个将大集合改为子查询的示例。
-- 大集合改为子查询的形式
SELECT o.order_id, o.order_date
FROM orders o
WHERE o.order_id IN (
SELECT id FROM large_id_list WHERE is_active = 1
);
3.2 EXISTS/NOT EXISTS 的替代与优势
在很多场景下，使用 EXISTS/NOT EXISTS 可以更好地被优化器处理，避免 NOT IN 对 NULL 的敏感性。 EXISTS 写法通常有更稳定的执行计划，且在使用覆盖索引时性能提升明显。
以下示例展示 NOT EXISTS 的常见写法，适用于排除存在于子查询结果中的行。
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = o.customer_id
);
3.3 使用覆盖索引与一致的数据类型
确保 where 子句的比较字段具有合适的索引，且参与比较的列类型尽量保持一致，以避免隐式类型转换导致的性能下降。对 IN/NOT IN 相关列建立覆盖索引，能够让查询尽可能只扫描索引树而非回表取数据。
结合统计信息与查询计划分析工具，可以判断是否需要添加联合索引来优化多列条件下的 IN/NOT IN。
-- 示例：为查询中经常出现的列创建覆盖索引
CREATE INDEX idx_users_id_username ON users (id, username);
4. IN/NOT IN 与 JOIN 的对比与最佳实践
4.1 何时选用 IN、何时选用 JOIN
当需要快速判断一个值集是否包含某列时，IN 是最直接的选择，且可读性强。但如果集合来自另一张表、或者集合很大，JOIN（或 EXISTS）往往具备更好的可扩展性和优化空间。
在设计查询时，优先评估数据规模、索引情况以及执行计划，再决定使用 IN/NOT IN 还是 JOIN。以下给出一个等价的 JOIN 写法示例，以帮助对比。
-- IN 的等价 JOIN 写法
SELECT u.id, u.username
FROM users u
JOIN (SELECT 1 AS dummy) AS t ON u.id IN (1,2,3,4,5);
-- NOT IN 的等价 NOT EXISTS 写法（如前所示）
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = o.customer_id
);
4.2 数据量较大时的走索引策略
在大数据量场景下，尽量避免在 IN/NOT IN 的集合上进行全表扫描，而应通过索引和分区策略来缩小扫描范围。分区表、分桶、以及将集合转化为可索引的结构，都是常见的优化路径。
结合分析执行计划（如 EXPLAIN）的结果，可以判断是否需要重构查询方式以利用现有的索引。
5. 实战案例：电商场景中的 IN/NOT IN 应用
5.1 案例：过滤黑名单中的订单记录
场景要点：需要快速排除黑名单中的客户订单，避免对整个订单表进行深度扫描。采用 NOT EXISTS 替代 NOT IN 的方案在数据量增大时更具稳定性。
案例中的核心是将黑名单表的 customer_id 与订单表进行连接排除，确保能使用订单表的索引。
SELECT o.order_id, o.customer_id, o.total_amount
FROM orders o
WHERE NOT EXISTS (
SELECT 1
FROM blacklist b
WHERE b.customer_id = o.customer_id
);
5.2 案例：排除特定商品的订单项
在商品下架、停售或已知不可售的场景下，需要对订单项进行筛选，NOT IN 常用于排除特定商品集合。但若集合较大或存在 NULL 值，需要考虑改用 NOT EXISTS/JOIN 的实现。
以下示例展示通过 NOT IN 排除停售商品的订单项的写法，以及一个替代的 EXISTS 方案。
-- NOT IN 示例
SELECT oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
WHERE oi.product_id NOT IN (SELECT id FROM discontinued_products);
-- 替代的 EXISTS 示例
SELECT oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
WHERE NOT EXISTS (
SELECT 1
FROM discontinued_products d
WHERE d.id = oi.product_id
);
