mysql索引失效

为什么索引会失效

MySQL 索引是提升查询性能的关键，但很多时候我们建了索引却发现并没有被使用，查询依旧很慢。这就是典型的”索引失效”。下面梳理几种最常见的失效原因，并给出对应的解决方法。

一、违反最左前缀原则

对于联合索引 (a, b, c)，查询必须从最左边的列开始使用，否则部分索引会失效。

-- 假设有联合索引 idx_abc (a, b, c)

-- ✅ 能用到索引
SELECT * FROM t WHERE a = 1;                     -- 使用 a
SELECT * FROM t WHERE a = 1 AND b = 2;           -- 使用 a, b
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- 使用 a, b, c

-- ❌ 无法使用索引（跳过了 a）
SELECT * FROM t WHERE b = 2 AND c = 3;

-- ❌ 部分失效（跳过了 b，c 用不上）
SELECT * FROM t WHERE a = 1 AND c = 3;

解决方法：根据查询模式合理设计联合索引的字段顺序，把最常用作过滤条件的列放在最左边。

二、在索引列上使用函数或运算

对索引列做运算或调用函数，会让优化器无法直接使用索引。

-- 假设 create_time 上有索引

-- ❌ 使用了函数，索引失效
SELECT * FROM t WHERE YEAR(create_time) = 2024;

-- ✅ 改为范围查询
SELECT * FROM t
WHERE create_time >= '2024-01-01'
  AND create_time <  '2025-01-01';

-- ❌ 列上做运算
SELECT * FROM t WHERE id + 1 = 100;

-- ✅ 把运算移到右侧
SELECT * FROM t WHERE id = 99;

解决方法：避免在索引列上使用函数或表达式，改为等价的常量条件。

三、使用 OR 导致全表扫描

当 OR 两端有一个条件列没有索引时，整个查询会放弃索引走全表扫描。

-- 假设 a 有索引，b 没有索引

-- ❌ b 没有索引，整个查询全表扫描
SELECT * FROM t WHERE a = 1 OR b = 2;

-- ✅ 方案1：给 b 也建索引
CREATE INDEX idx_b ON t(b);

-- ✅ 方案2：改写为 UNION ALL
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2;

解决方法：确保 OR 关联的列都有索引，或改写为 UNION。

四、LIKE 以 % 开头

LIKE '%abc' 这种以通配符开头的模糊匹配无法利用 B+Tree 索引。

-- ❌ 以 % 开头，索引失效
SELECT * FROM t WHERE name LIKE '%张';

-- ❌ 两边都加 %，索引失效
SELECT * FROM t WHERE name LIKE '%张%';

-- ✅ 前缀匹配可以使用索引
SELECT * FROM t WHERE name LIKE '张%';

解决方法： - 尽量使用前缀匹配 LIKE 'abc%' - 真的需要中间匹配时，考虑全文索引 FULLTEXT 或外置搜索引擎（如 Elasticsearch）

五、隐式类型转换

当查询条件的类型与列类型不一致时，MySQL 会做隐式转换，通常会导致索引失效。

-- 假设 phone 字段是 VARCHAR 类型

-- ❌ 传入数字，发生隐式转换，索引失效
SELECT * FROM t WHERE phone = 13800138000;

-- ✅ 传入字符串，类型一致
SELECT * FROM t WHERE phone = '13800138000';

解决方法：查询条件的类型要和列定义保持一致，字符串字段就用引号包裹。

六、NOT IN / NOT EXISTS

NOT IN 和 NOT EXISTS 在很多场景下优化器会选择全表扫描。

-- ❌ NOT IN 容易走全表扫描
SELECT * FROM t WHERE id NOT IN (1, 2, 3);

-- ✅ 改写为 LEFT JOIN ... IS NULL
SELECT t.* FROM t
LEFT JOIN exclude_t e ON t.id = e.id
WHERE e.id IS NULL;

解决方法：尽量用 LEFT JOIN ... IS NULL 等方式替代 NOT IN。

七、索引选择性差

如果一列的取值很少（比如性别只有”男/女”），即使建了索引，优化器也可能认为走全表扫描更划算，从而放弃索引。

-- ❌ 性别列基数太低，索引可能不被使用
SELECT * FROM t WHERE gender = '男';

-- ✅ 选择性高的列更适合建索引
CREATE INDEX idx_create_time ON t(create_time);

解决方法： - 选择基数高（区分度大）的列建索引 - 低基数列可以考虑与其他列组成联合索引

如何用 EXPLAIN 分析索引使用

EXPLAIN 是排查索引失效最有力的工具。在 SQL 前加上 EXPLAIN 即可查看执行计划。

EXPLAIN SELECT * FROM t WHERE name = '张三';

重点关注以下字段：

-- 示例：分析一条查询
EXPLAIN SELECT id, name FROM users WHERE age > 18 AND city = '北京';
-- 如果 type 为 ALL、key 为 NULL，说明索引没生效，需要调整索引或查询写法

总结

索引失效的常见原因可以归纳为”优化器认为走索引不划算”或”写法让索引无法被利用”。排查时记住三步： 1. 用 EXPLAIN 看执行计划 2. 检查查询是否满足索引使用规则 3. 调整 SQL 写法或重建合适的索引

养成用 EXPLAIN 的习惯，索引问题就能事半功倍。