MySQL中IN和NOT IN会走索引吗？
|
zhenglin
2025年12月4日 10:29
本文热度 111
|
MySQL中IN和NOT IN会走索引吗？
在 MySQL 中，IN 和 NOT IN 是常见的 SQL 查询条件，用于匹配多个值。然而，它们是否会使用索引并不是绝对的，而是取决于查询优化器、索引类型、数据分布等因素。
本文将深入探讨 IN 和 NOT IN 是否走索引的原因，以及它们在什么情况下可能用不到索引。
一、IN 会走索引吗？为什么？
1. 基本原理
IN 表示一个等值匹配的集合（例如 WHERE id IN (1, 2, 3)），MySQL 优化器会尝试将其转化为多个等值条件（类似于 id = 1 OR id = 2 OR id = 3）。如果查询的列上有索引，优化器通常会选择使用索引来加速查找。
2. 走索引的条件
列上有索引：无论是主键、唯一索引还是普通索引，IN 都可以利用索引。
值数量适中：当 IN 中的值数量较少时，优化器倾向于使用索引逐个查找。
选择性较高：如果索引列的选择性好（即重复值少），走索引的概率更高。
3. 示例
假设表 users 有索引 idx_id 在 id 列上：
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);
- 输出：
type 可能是 ref 或 range，key 为 idx_id。 - 原因：优化器使用索引
idx_id 查找匹配的行。
4. 什么情况下 IN 用不到索引？
尽管 IN 通常会走索引，但以下情况可能导致索引失效：
值数量过多：如果 IN 中的值非常多（例如上千个），优化器可能认为全表扫描比逐个索引查找更高效，从而放弃索引。
示例：WHERE id IN (1, 2, 3, ..., 10000)。
- 选择性低：如果索引列重复值过多（例如大部分行都满足条件），优化器可能选择全表扫描。
- 统计信息不准确：MySQL 依赖表统计信息判断走索引的成本。如果统计信息过时（未执行
ANALYZE TABLE），可能误判。
二、NOT IN 会走索引吗？为什么？
1. 基本原理
NOT IN 表示排除一组值（例如 WHERE id NOT IN (1, 2, 3)），逻辑上等价于 id != 1 AND id != 2 AND id != 3。然而，MySQL 对 NOT IN 的优化能力较弱，通常难以直接利用索引。
2. 为什么通常不走索引？
- 范围排除逻辑：
NOT IN 是一个否定条件，优化器很难将其转化为高效的索引查找。索引更适合正向匹配，而非排除。 - 全表扫描成本低：对于否定条件，MySQL 倾向于扫描全表并逐行检查是否满足条件，尤其是当排除的值较少时。
- NULL 值的影响：如果
NOT IN 的子查询或值列表中包含 NULL，整个条件的结果会变为 UNKNOWN，导致优化器无法有效利用索引。
3. 示例
EXPLAIN SELECT * FROM users WHERE id NOT IN (1, 2, 3);
4. NOT IN 一定不会用到索引吗？
并非绝对。在特定情况下，NOT IN 可以走索引：
- 值数量极少且索引选择性高：如果排除的值很少，且索引列重复值少，优化器可能选择索引。
- 子查询优化：如果
NOT IN 后跟的是一个子查询，且子查询结果集较小，MySQL 可能通过索引优化。 - 转换为
LEFT JOIN：某些情况下，优化器会将 NOT IN 重写为 LEFT JOIN ... WHERE IS NULL，从而利用索引。
示例：
EXPLAIN SELECT * FROM users u LEFT JOIN (SELECT 1 AS id UNION SELECT 2) t ON u.id = t.id WHERE t.id IS NULL;
三、总结与优化建议
1. IN 和 NOT IN 的对比
2. 优化建议
3. 结论
IN 在大多数情况下会走索引，但值过多或选择性低时可能失效；NOT IN 通常不走索引，但并非绝对不可用。通过理解优化器的行为并调整查询结构，可以显著提升查询性能。
参考文章：原文链接
该文章在 2025/12/4 10:29:57 编辑过
400 186 1886
