MySQL 索引失效常见场景：开发优化必记要点
索引建了，SQL 也能跑，但就是慢——这是 DBA 和开发同学日常对话中最常见的场景之一。明明字段上有索引，WHERE 条件也用了这个字段，但
EXPLAIN一看，type=ALL全表扫描。问题出在哪？答案只有一个：你的索引失效了。
一、先搞懂索引为什么快
MySQL 的索引底层是 B+ 树。想象一本 1000 页的字典，没有目录你得从第 1 页翻到第 500 页才能找到"程序员"；有目录，直接翻到"程"字开头在第 300 页，瞬间定位。
| 场景 | 无索引 | 有索引 | 性能提升 |
|---|---|---|---|
| 100万条数据查询 | 扫描100万行 | 扫描3-4行 | 25万倍+ |
| 用户登录验证 | 50ms | 1ms | 50倍 |
| 订单查询 | 200ms | 5ms | 40倍 |
B+ 树的查找只需 3 次磁盘 IO，而全表扫描可能需要几万次。索引的价值，不言而喻。
但如果你的写法踩了下面这些坑，索引就等于白建。
二、索引失效的 12 大常见场景
❌ 场景1：违反最左前缀原则（最高频！）
联合索引 (name, age, city) 的 B+ 树是按 name → age → city 的顺序构建的，有序性只体现在最左列上。
sql
-- ✅ 有效：从最左列开始匹配
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
-- ❌ 失效：跳过最左列
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE city = '北京';
口诀：联合索引像目录，必须从第一级开始翻，不能直接翻到第三级。
❌ 场景2：在索引列上使用函数或表达式
sql
-- ❌ 失效：对索引列套用函数
SELECT * FROM users WHERE YEAR(birthday) = 2000;
SELECT * FROM users WHERE UPPER(name) = 'ZHANGSAN';
SELECT * FROM users WHERE LEFT(name, 3) = 'Geo';
-- ✅ 改写：把计算移到等号右边
SELECT * FROM users WHERE birthday >= '2000-01-01' AND birthday < '2001-01-01';
原理：索引树按字段原始值构建，函数会改变原始值，B+ 树的有序性被破坏，只能逐行扫描。
❌ 场景3：隐式类型转换（最容易踩的暗坑）
sql
-- 假设 phone 是 VARCHAR 类型，有索引
-- ❌ 失效：数字传给字符串列，MySQL 自动转换
SELECT * FROM users WHERE phone = 13800000000;
-- ✅ 正确：加引号，类型匹配
SELECT * FROM users WHERE phone = '13800000000';
本质：隐式转换等价于在索引列上执行了 CAST() 函数，索引直接报废。
❌ 场景4：LIKE 以 % 开头
sql
-- ❌ 失效：前缀不固定，B+ 树无法定位
SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';
-- ✅ 有效：前缀固定，可以利用有序性
SELECT * FROM users WHERE name LIKE '张三%';
SELECT * FROM users WHERE name LIKE '张%三'; -- 只用到 '张%' 部分
❌ 场景5：不等于操作（!=、<>、NOT IN）
sql
-- ❌ 大概率全表扫描
SELECT * FROM users WHERE status != 'active';
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
-- ✅ 优化：改写为正向查询
SELECT * FROM users WHERE status IN ('pending', 'closed');
原因：不等于操作的结果集通常占表的大部分，优化器认为全表扫描比索引查找更划算。
❌ 场景6：OR 条件中有一列没索引
sql
-- 假设 name 有索引，age 无索引
-- ❌ 全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 35;
-- ✅ 方案：两边都建索引，或改用 UNION ALL
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 35;
❌ 场景7：IS NOT NULL / IS NULL 处理不当
| 条件 | 索引是否生效 |
|---|---|
WHERE email IS NULL | ✅ 通常生效（B+ 树存了 NULL 位置） |
WHERE email IS NOT NULL | ❌ 通常失效（结果集太大） |
字段定义为 NOT NULL | IS NOT NULL 无意义，不会触发失效 |
❌ 场景8：联合索引中，范围列之后的列全部失效
这是很多人忽略的关键规则：
sql
-- 联合索引 (emp_no, title, from_date)
-- ❌ from_date 失效
SELECT * FROM employees WHERE emp_no > 10010 AND title = 'Senior Engineer' AND from_date = '1986-01-01';
原因：范围查询（>、<、BETWEEN）会"截断"索引的有序性，范围列之后的列无法再利用索引。索引最多只有一个范围列能生效。
优化策略：把等值条件的列放前面，范围条件的列放最后。
❌ 场景9：ORDER BY 不符合索引顺序
sql
-- 联合索引 (a, b)
-- ❌ 失效：ORDER BY 的列不在索引最左
SELECT * FROM users ORDER BY b;
-- ✅ 有效
SELECT * FROM users ORDER BY a, b;
❌ 场景10：数据量太少 / 区分度太低
sql
-- 表只有 100 行，优化器认为全表扫描更快
SELECT * FROM tiny_table WHERE status = '1';
-- 90% 的数据 status='active'，索引形同虚设
SELECT * FROM users WHERE status = 'active';
区分度计算：COUNT(DISTINCT col) / COUNT(*)，低于 80% 建索引意义不大。
❌ 场景11：深度分页
sql
-- ❌ 即使有索引也很慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 25;
-- ✅ 优化：记录上次最后一条 ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 25;
❌ 场景12：SELECT * 导致无法使用覆盖索引
sql
-- ❌ 需要回表：先查索引拿主键，再查主键取整行数据
SELECT * FROM users WHERE name = '张三';
-- ✅ 覆盖索引：所需字段全在索引中，无需回表
SELECT name, email FROM users WHERE name = '张三';
-- 如果建了 (name, email) 联合索引，EXPLAIN 的 Extra 会显示 Using index
三、一张图记住所有失效场景
| 场景 | 关键词 | 一句话记忆 |
|---|---|---|
| 最左前缀 | 联合索引 | 跳过第一列，索引白建 |
| 函数运算 | YEAR()、+、- | 索引列上做计算 = 索引报废 |
| 隐式转换 | 字符串 vs 数字 | 不加引号，索引失效 |
| LIKE 前导 % | '%abc' | % 在前，全表扫描 |
| 不等于 | !=、NOT IN | 负向条件，优化器放弃索引 |
| OR 混合 | 一边有索引一边没有 | 木桶效应，短板决定速度 |
| IS NOT NULL | IS NOT NULL | 结果集太大，不如全扫 |
| 范围截断 | 联合索引 + 范围查询 | 范围之后，索引归零 |
| ORDER BY 错位 | 排序列不在索引首位 | 顺序不对，文件排序 |
| 数据太少/区分度低 | 小表、性别字段 | 优化器比你聪明 |
| 深度分页 | LIMIT 1000000, 25 | 记住上次 ID，别用 OFFSET |
| SELECT * | 回表开销 | 能用覆盖索引就别回表 |
四、诊断工具：EXPLAIN
所有猜测都不如一条命令来得直接：
sql
EXPLAIN SELECT * FROM users WHERE name LIKE '%张三%';
重点关注这几个字段：
| 字段 | 含义 | 期望值 |
|---|---|---|
type | 访问类型 | ref/range 优于 ALL |
key | 实际使用的索引 | 不能为 NULL |
rows | 估算扫描行数 | 越少越好 |
Extra | 额外信息 | Using index（覆盖索引）最优；Using filesort 需警惕 |
性能排序：system > const > eq_ref > ref > range > index > ALL，尽量避免 ALL。
五、开发优化的 6 条黄金法则
- 为 WHERE 条件建索引，为 ORDER BY 字段建索引，为 JOIN 关联字段建索引
- 联合索引把等值列放前面，范围列放最后，遵循最左前缀
- 字符类型务必加引号，杜绝隐式类型转换
- 能用
=别用!=，能用IN别用OR，能用UNION ALL别用OR - 优先考虑覆盖索引，减少
SELECT *，让查询只扫描索引树 - 定期
ANALYZE TABLE更新统计信息，让优化器做对决策
写在最后
索引失效的本质只有一个：你的 SQL 写法破坏了 B+ 树的有序性，让 MySQL 无法利用索引快速定位数据。
记住一句话就能避开 90% 的坑：
不计算、不函数、不隐式转换、最左前缀不乱写。
剩下那 10%，交给 EXPLAIN。
