为什么你建了索引，查询还是很慢？常见失效原因汇总
一、先搞清楚一个基本认知
很多开发者以为：建了索引 = 查询变快。
但现实往往是：
索引建了，EXPLAIN 也走了索引，结果查询还是慢得像蜗牛。
这不是数据库的锅，是你的索引 没被正确使用 ，或者 根本就没生效。
今天我们系统梳理一下，索引失效的 12 个常见原因，以及背后的原理。
二、索引失效的核心原理：为什么优化器不走索引？
MySQL 优化器在选择执行计划时，会做一个 成本估算：
走索引的成本 vs 全表扫描的成本
如果估算走索引比全表扫描还贵，它就直接放弃索引
所以索引失效的本质就两种：
类别
说明
语法层面失效
SQL 写法导致索引无法被匹配
统计信息层面失效
优化器认为全表扫描更优，主动不走索引
下面逐一拆解。
三、语法层面：SQL 写法导致索引失效
1. ❌ 对索引列做函数/运算
sql
复制代码
sql
-- 失效 ❌
SELECT * FROM user WHERE YEAR(create_time) = 2024;
SELECT * FROM user WHERE age + 1 = 25;
-- 走索引 ✅
SELECT * FROM user WHERE create_time >= '2024-01-01'
AND create_time < '2025-01-01';
SELECT * FROM user WHERE age = 24;
原理：B+Tree 存储的是原始列值，对列做函数后，索引中找不到对应的值，只能全表扫描逐行计算。
📌 规则：索引列上不能有任何操作，包括函数、运算、类型转换。
2. ❌ 隐式类型转换
sql
复制代码
sql
-- phone 字段是 varchar 类型
-- 失效 ❌
SELECT * FROM user WHERE phone = 13800138000;
-- 走索引 ✅
SELECT * FROM user WHERE phone = '13800138000';
原理 ：当字符串列与数字比较时，MySQL 会把字符串列隐式转换成数字 ，相当于对列做了 CAST() 函数，索引失效。
📌 排查技巧：用 EXPLAIN 看 key_len，如果明显变短，很可能是类型转换导致只用了部分索引甚至没走索引。
3. ❌ LIKE 以通配符开头
sql
复制代码
sql
-- 失效 ❌（%在前，无法利用B+Tree有序性）
SELECT * FROM user WHERE name LIKE '%张';
-- 走索引 ✅（%在后，可以走范围扫描）
SELECT * FROM user WHERE name LIKE '张%';
-- 走索引 ✅（全模糊也能走，但只走到第一个%）
SELECT * FROM user WHERE name LIKE '张%三';
原理 ：B+Tree 是按从左到右 顺序组织的。%张 意味着要匹配所有以"张"结尾的值，无法利用树的有序性，只能全表扫描。
📌 如果必须支持 %xxx% 全模糊，考虑走 全文索引（FULLTEXT） 或 Elasticsearch。
4. ❌ 违反最左前缀原则（联合索引）
假设有联合索引 idx_a_b_c (a, b, c)：
ini
复制代码
sql
-- ✅ 走索引（最左匹配）
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
-- ❌ 不走索引（跳过最左列）
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3
-- ⚠️ 部分走索引（最左连续中断）
WHERE a = 1 AND c = 3 -- 只有 a 走索引，c 不走
原理 ：联合索引的 B+Tree 先按 a 排序，a 相同再按 b 排，再按 c 排。跳过 a 直接查 b，就像跳过目录直接翻书------树的结构用不上。
📌 建联合索引时，把最常作为查询条件、区分度最高的列放在最左边。
5. ❌ OR 条件中有一列没有索引
sql
复制代码
sql
-- 假设只有 name 有索引，age 没有索引
-- 失效 ❌（age 列无法走索引，优化器可能直接放弃）
SELECT * FROM user WHERE name = '张三' OR age = 25;
-- 走索引 ✅（两列都有索引）
SELECT * FROM user WHERE name = '张三' OR name = '李四';
原理 ：OR 两边只要有一列没有索引，优化器往往会选择全表扫描而不是"索引合并"（index merge 效率通常不高）。
📌 解决方案：
确保 OR 两边的列都有索引
或改用 UNION ALL：
sql
复制代码
sql
SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE age = 25 AND name != '张三';
6. ❌ 使用 != 或 <> 或 NOT IN
sql
复制代码
sql
-- 大概率失效 ❌
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE id NOT IN (1, 2, 3);
-- 走索引 ✅
SELECT * FROM user WHERE status = 0;
SELECT * FROM user WHERE id NOT IN (SELECT id FROM blacklist);
-- 子查询结果少时，优化器可能走索引
原理 ：!= 和 NOT IN 需要扫描大部分数据，优化器判断"反正要扫那么多，不如直接全表扫描"。
📌 但这不是绝对的------如果 != 的条件过滤后数据量很小，优化器仍可能走索引。关键看选择性。
7. ❌ IS NULL / IS NOT NULL
sql
复制代码
sql
-- 失效 ❌（取决于索引定义和数据分布）
SELECT * FROM user WHERE name IS NULL;
-- 如果 name 列允许 NULL，且建了普通索引，可能不走索引
-- 因为 NULL 值在索引中的存储方式特殊，优化器可能认为扫描代价大
原理 ：InnoDB 的普通索引不存储 NULL 值（只存非 NULL），所以 IS NULL 条件无法直接通过索引定位。
📌 解决方案：给列设置默认值（如空字符串），然后查 name = ''，或者用复合索引把 NULL 列放在后面。
四、统计信息层面：优化器主动不走索引
8. ❌ 数据区分度太低（选择性差）
sql
复制代码
sql
-- gender 只有 0/1 两个值，即使建了索引
SELECT * FROM user WHERE gender = 1;
原理 ：如果 gender = 1 的数据占表的 50%，走索引需要回表 50% 的行，代价比全表扫描还大。优化器会直接选择全表扫描。
📌 区分度公式 ：count(distinct col) / count(*)，结果越接近 1 索引越有效，越接近 0 越可能失效。一般认为 < 0.1 就不适合单独建索引。
9. ❌ 表数据量太小
sql
复制代码
sql
-- 表只有 100 行，建了索引也不走
SELECT * FROM user WHERE id = 50;
原理：表太小时，全表扫描的 I/O 代价极低，优化器认为"没必要走索引"。索引的优势在数据量大时才体现。
📌 这种情况不算"失效"，是优化器的正确选择。
10. ❌ 索引失效 + 优化器选错（统计信息过期）
sql
复制代码
sql
-- 以前走索引，现在不走了，数据没变但执行计划变了
原理 ：MySQL 依赖统计信息 来估算成本。如果统计信息过时（比如大量数据删除后没跑 ANALYZE TABLE），优化器可能做出错误判断。
📌 解决方案：
sql
复制代码
sql
ANALYZE TABLE user;
-- 或
OPTIMIZE TABLE user; -- 重建表+更新统计信息
五、索引设计层面：建了但不好用
11. ❌ 索引列太多 / 索引太宽
sql
复制代码
sql
-- 联合索引包含大量列
CREATE INDEX idx_wide ON user(a, b, c, d, e, f, g);
问题：
索引文件变大，占用更多磁盘和内存
B+Tree 层数增加，IO 次数变多
维护成本高（INSERT/UPDATE/DELETE 都要维护索引）
📌 建议联合索引不超过 3-5 列，优先把高区分度、常用查询条件放前面。
12. ❌ 用了覆盖索引的反面：SELECT *
sql
复制代码
sql
-- 即使走了索引，也要回表
SELECT * FROM user WHERE name = '张三'; -- 索引只有 name，需要回表取所有列
-- 走覆盖索引，不回表 ✅
SELECT id, name FROM user WHERE name = '张三';
原理 ：如果查询的列不在索引中，MySQL 需要回表（根据主键再查一次聚簇索引）。如果回表次数太多，优化器可能觉得不如全表扫描。
📌 覆盖索引优化：把常用查询的列都加到联合索引中，避免回表。
sql
复制代码
sql
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = '张三'; -- 纯索引扫描，不回表
六、一张表速查：索引失效全景图
序号
失效原因
关键词
解决方案
1
列上做函数/运算
YEAR(), +1, UPPER()
改写 SQL，把计算放到等号右边
2
隐式类型转换
varchar = int
统一类型，用引号包字符串
3
LIKE 左通配
%张
改成 张%，或用全文索引
4
违反最左前缀
跳过联合索引第一列
调整索引列顺序，或建多个索引
5
OR 一边无索引
a=1 OR b=2，b 无索引
两边都建索引，或改用 UNION
6
!= / NOT IN
负向查询
改用正向查询，或 UNION
7
IS NULL
空值判断
设默认值，或复合索引
8
区分度太低
性别、状态等枚举值
不单独建索引，用复合索引
9
表太小
< 几百行
不需要索引，优化器会处理
10
统计信息过期
执行计划突然变了
ANALYZE TABLE
11
索引太宽
联合索引 > 5 列
精简索引列
12
SELECT * 导致回表
查询列不在索引中
用覆盖索引，只查需要的列
七、实战排查流程
遇到慢查询，按这个顺序排查：
sql
复制代码
1. EXPLAIN 看执行计划
├── type 是 ALL？→ 全表扫描，检查索引
├── key 是 NULL？→ 没走索引，往下排查
├── key_len 异常短？→ 可能类型转换或最左前缀断了
└── Extra 有 Using index？→ 覆盖索引，很好
2. 检查 SQL 写法
├── 列上有函数吗？
├── 有隐式转换吗？
├── LIKE 是 %开头吗？
└── OR 两边都有索引吗？
3. 检查索引设计
├── 区分度够吗？
├── 联合索引顺序对吗？
└── 查询列在索引里吗？（覆盖索引）
4. 检查统计信息
└── 跑一下 ANALYZE TABLE
八、最后说句大实话
索引不是银弹，建索引是技术，用好索引是艺术。
很多时候慢查询的根因不是"没建索引"，而是：
建了但设计不合理（列顺序错、太宽）
建了但 SQL 写得太烂（函数、类型转换）
建了但数据量太小/区分度太低，优化器根本不想用
先 EXPLAIN，再改 SQL，最后调索引------这个顺序反了，就是浪费时间。