MySQL 索引失效的十大场景
一、什么是索引失效
索引失效是指 MySQL 查询时没有使用预期的索引，而是选择全表扫描或其他低效的执行计划。
核心问题：明明建了索引，查询却很慢
二、索引失效的十大场景
2.1 隐式类型转换
当查询条件的值类型与字段类型不一致时，MySQL 进行隐式转换导致索引失效。
-- 假设 phone 字段是 varchar 类型
-- ❌ 索引失效：传入数字
SELECT * FROM user WHERE phone = 13800138000;
-- ✅ 索引有效：传入字符串
SELECT * FROM user WHERE phone = '13800138000';
原因：MySQL 将 varchar 每行转为数字再比较，等同于 CAST(phone AS SIGNED)。
2.2 OR 条件导致失效
当 OR 连接的条件中有字段没有索引时，整个查询可能不走索引。
-- 假设 name 有索引，age 无索引
-- ❌ 索引可能失效
SELECT * FROM user WHERE name = '张三' OR age = 18;
-- ✅ 改用 UNION ALL
SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE age = 18 AND name != '张三';
原因：OR 要求两边条件都能用索引才走索引。
2.3 LIKE 左模糊查询
通配符 % 出现在开头时，索引失效。
-- ❌ 索引失效（左模糊）
SELECT * FROM user WHERE name LIKE '%三';
SELECT * FROM user WHERE name LIKE '%张三%';
-- ✅ 索引有效（右模糊）
SELECT * FROM user WHERE name LIKE '张%';
原因：B+ 树按从左到右顺序存储，左边不确定无法利用有序性。
2.4 联合索引不满足最左匹配
联合索引必须从最左列开始匹配，跳过左边的列会失效。
-- 假设有联合索引 idx_a_b_c (a, b, c)
-- ✅ 索引有效
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- ❌ 索引失效（跳过了 a）
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;
原因：联合索引先按 a 排序，a 相同按 b 排序。跳过 a，后面字段无序。
2.5 对索引列使用函数
对索引列使用内置函数导致索引失效。
-- ❌ 索引失效
SELECT * FROM order WHERE YEAR(create_time) = 2024;
SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';
-- ✅ 改写为范围查询
SELECT * FROM order WHERE create_time >= '2024-01-01'
AND create_time < '2025-01-01';
原因：使用函数后需要对每行计算，无法利用索引有序性。
2.6 对索引列进行运算
在索引列上进行算术运算导致索引失效。
-- ❌ 索引失效
SELECT * FROM user WHERE age + 1 = 19;
SELECT * FROM user WHERE age * 2 = 36;
-- ✅ 将运算移到右边
SELECT * FROM user WHERE age = 19 - 1;
SELECT * FROM user WHERE age = 36 / 2;
原因：与函数类似，列运算破坏索引有序性。
2.7 使用 != 或 <>
不等于操作可能导致索引失效。
-- ❌ 索引可能失效
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE status <> 1;
-- ✅ 尝试改写
SELECT * FROM user WHERE status IN (0, 2, 3);
原因：不等于通常匹配大量数据，优化器可能认为全表扫描更快。
2.8 IS NULL / IS NOT NULL
NULL 值判断可能导致索引失效。
-- 索引可能失效（取决于 NULL 值占比）
SELECT * FROM user WHERE name IS NULL;
SELECT * FROM user WHERE name IS NOT NULL;
原因：优化器根据 NULL 值分布决定是否走索引。
2.9 关联字段字符集不一致
JOIN 时关联字段的字符集或排序规则不一致导致索引失效。
-- 假设 t1.name 是 utf8，t2.name 是 utf8mb4
-- ❌ 索引失效
SELECT * FROM t1 LEFT JOIN t2 ON t1.name = t2.name;
-- ✅ 统一字符集
ALTER TABLE t1 MODIFY name VARCHAR(100) CHARACTER SET utf8mb4;
原因：字符集不同时 MySQL 需要转换，相当于使用了 CONVERT 函数。
2.10 优化器选错索引
MySQL 查询优化器负责为每条 SQL 选择最优执行计划，但有时会选错索引。
优化器选择索引的依据
┌─────────────────────────────────────────────────────────────┐
│ 优化器选择索引的依据 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 统计信息（ANALYZE TABLE 收集） │
│ - 表的总行数 │
│ - 索引的基数（Cardinality）—— 不重复值的数量 │
│ │
│ 2. 代价估算 │
│ - 预估扫描行数 │
│ - I/O 代价、CPU 代价 │
│ │
│ 3. 选择代价最低的方案 │
│ │
└─────────────────────────────────────────────────────────────┘
为什么会选错
| 原因 | 说明 |
|---|---|
| 统计信息过时 | 大量数据变更后未执行 ANALYZE TABLE |
| 数据分布不均 | 某些值特别多，但优化器不知道要查的是哪个值 |
| 预估误差 | 基于采样统计，可能与实际偏差较大 |
| 回表代价估算不准 | 低估了使用二级索引后回表的代价 |
实际例子
-- 假设表 user 有两个索引：idx_age 和 idx_status
-- user 表有 100 万行
-- age > 10 的有 90 万行
-- status = 1 的有 1000 行
SELECT * FROM user WHERE age > 10 AND status = 1;
-- 理论上应该选 idx_status（扫描 1000 行）
-- 但如果统计信息不准，可能误选 idx_age（扫描 90 万行）
如何发现选错了
EXPLAIN SELECT * FROM user WHERE age > 10 AND status = 1;
-- 结果示例：key = idx_age，rows = 900000 → 选错了！
+----+-------+-------+--------------------+---------+---------+--------+-------------+
| id | table | type | possible_keys | key | key_len | rows | Extra |
+----+-------+-------+--------------------+---------+---------+--------+-------------+
| 1 | user | range | idx_age,idx_status | idx_age | 4 | 900000 | Using where |
+----+-------+-------+--------------------+---------+---------+--------+-------------+
解决方案
-- 1. 更新统计信息
ANALYZE TABLE user;
-- 2. 强制使用索引
SELECT * FROM user FORCE INDEX(idx_status) WHERE age > 10 AND status = 1;
-- 3. 建议使用索引（优化器可忽略）
SELECT * FROM user USE INDEX(idx_status) WHERE age > 10 AND status = 1;
-- 4. 忽略某索引
SELECT * FROM user IGNORE INDEX(idx_age) WHERE age > 10 AND status = 1;
-- 5. 优化索引设计（创建联合索引）
CREATE INDEX idx_status_age ON user(status, age);
原因：统计信息不准确或数据分布不均匀，优化器基于错误信息做出次优选择。
三、场景总结
| 场景 | 根本原因 | 解决方案 |
|---|---|---|
| 隐式类型转换 | 触发函数转换 | 保持类型一致 |
| OR 条件 | 部分字段无索引 | 都加索引或 UNION |
| LIKE 左模糊 | 破坏索引有序性 | 右模糊或全文索引 |
| 不满足最左匹配 | 联合索引结构 | 调整查询或索引 |
| 使用函数 | 破坏索引有序性 | 改写 SQL |
| 列运算 | 破坏索引有序性 | 运算移到右边 |
| != 或 <> | 优化器判断 | 改写或覆盖索引 |
| IS NULL/NOT NULL | 优化器判断 | 避免 NULL |
| 字符集不一致 | 隐式转换 | 统一字符集 |
| 优化器选错 | 统计信息不准 | ANALYZE 或 FORCE INDEX |
四、排查方法
4.1 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM user WHERE name = '张三';
关注字段：
| 字段 | 说明 | 理想值 |
|---|---|---|
| type | 访问类型 | ref, eq_ref, const（避免 ALL） |
| key | 实际使用的索引 | 预期的索引名 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index（覆盖索引） |
4.2 EXPLAIN ANALYZE（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM user WHERE name = '张三';
可以看到实际执行时间和扫描行数。
4.3 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
4.4 查看索引信息
-- 查看表的索引
SHOW INDEX FROM table_name;
-- 更新统计信息
ANALYZE TABLE table_name;
五、最佳实践
5.1 索引设计原则
| 原则 | 说明 |
|---|---|
| 选择性高的字段 | 区分度高的字段更适合建索引 |
| 联合索引字段顺序 | 高选择性字段放前面 |
| 覆盖索引 | 尽量让查询只访问索引 |
| 避免过多索引 | 影响写入性能 |
5.2 SQL 编写规范
-- ✅ 类型一致
WHERE phone = '13800138000'
-- ✅ 避免函数
WHERE create_time >= '2024-01-01'
-- ✅ 运算放右边
WHERE age = 18
-- ✅ 遵循最左匹配
WHERE a = 1 AND b = 2
-- ✅ 右模糊
WHERE name LIKE '张%'
六、总结
索引失效的核心原因：
┌─────────────────────────────────────────────────────────────┐
│ 索引失效核心原因 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. 破坏索引有序性 │
│ - 使用函数、列运算、隐式转换 │
│ - B+ 树无法利用有序性快速定位 │
│ │
│ 2. 无法使用索引 │
│ - 不满足最左匹配、左模糊查询 │
│ - 索引结构限制 │
│ │
│ 3. 优化器判断全表扫描更快 │
│ - 使用 !=、IS NULL 等 │
│ - 基于代价估算 │
│ │
└─────────────────────────────────────────────────────────────┘
关键要点：
| 要点 | 说明 |
|---|---|
| 排查工具 | EXPLAIN / EXPLAIN ANALYZE |
| 核心原则 | 保持类型一致、避免破坏有序性 |
| 设计原则 | 高选择性、覆盖索引、最左匹配 |
| 兜底方案 | FORCE INDEX 强制指定索引 |
0 评论
评论