mysql 索引失效及其解决办法

一、索引失效的 12 种典型场景

1、违反最左前缀原则

场景：复合索引 (a,b,c)，但查询条件未包含 a

SELECT * FROM table WHERE b=1 AND c=2; -- 索引失效

原理：B+树按索引列顺序构建，跳过左列无法定位区间

解决：调整查询条件顺序或重建索引（如 (b,c,a)）

2、在索引列上使用函数或计算

场景：对索引列进行运算或函数调用

SELECT * FROM table WHERE YEAR(create_time)=2023; -- 索引失效

原理：索引存储原始值，无法匹配计算后的结果

解决：改写为范围查询

WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'

3、隐式类型转换

场景：索引列是字符串类型，但用数字查询

SELECT * FROM table WHERE phone = 13800138000; -- phone为varchar类型

原理：MySQL 执行隐式转换，等效于 CAST(phone AS SIGNED)

解决：保持类型一致

WHERE phone = '13800138000'

4、使用 OR 连接非索引列

场景：OR 条件中存在非索引字段

SELECT * FROM table WHERE a=1 OR d=2; -- 若d无索引，全表扫描 

原理：OR 要求任意条件满足，无法同时走索引

解决：为 d 添加索引或拆分为 UNION 查询

SELECT * FROM table WHERE a=1 
UNION 
SELECT * FROM table WHERE d=2;

5、LIKE 以通配符开头

场景：模糊查询前缀不固定

SELECT * FROM table WHERE name LIKE '%John%'; -- 全表扫描

原理：B+树无法定位前缀不确定的字符串

解决：

使用覆盖索引：SELECT id FROM table WHERE name LIKE '%John%'

使用全文索引（FULLTEXT）或 Elasticsearch

6、范围查询后的索引列失效

场景：复合索引 (a,b,c)，范围查询后列无法使用索引

SELECT * FROM table WHERE a>1 AND b=2; -- b列索引失效

原理：范围查询后索引列的有序性被破坏

解决：调整索引顺序或使用覆盖索引

7、使用 != 或 <> 运算符

场景：非等值查询

SELECT * FROM table WHERE status != 'active'; -- 全表扫描 

原理：无法利用索引快速定位边界

解决：改写为 IN 或范围查询

WHERE status IN ('inactive', 'deleted')

8、索引列参与 IS NULL/IS NOT NULL

场景：索引列存在大量 NULL 值

SELECT * FROM table WHERE col IS NULL; -- 可能全表扫描

原理：索引不存储 NULL 值（除非显式声明允许 NULL） 

解决：设置默认值替代 NULL 或使用覆盖索引

9、 数据倾斜导致优化器弃用索引 

场景：索引列值分布不均（如 90% 数据为同一个值） 

SELECT * FROM table WHERE gender='F'; -- 假设 gender 有索引但数据倾斜

原理：优化器认为全表扫描成本更低

解决：强制使用索引（慎用）

SELECT * FROM table FORCE INDEX(idx_gender) WHERE gender='F';

10、JOIN 字段字符集/排序规则不一致 

场景：跨表 JOIN 时字段字符集不同

-- table1.utf8_col (utf8mb4) JOIN table2.latin1_col (latin1)　　 

原理：隐式转换导致索引失效

解决：统一字符集或显式转换

ON CONVERT(utf8_col USING latin1) = latin1_col

11、使用 ORDER BY + LIMIT 但排序字段无索引

场景：排序字段无索引且数据量大

SELECT * FROM table ORDER BY create_time LIMIT 100000,10; -- 全表排序　　

原理：无法利用索引有序性

解决：为排序字段添加索引或使用延迟关联

SELECT t.* FROM table t 
JOIN (SELECT id FROM table ORDER BY create_time LIMIT 100000,10) tmp 
ON t.id=tmp.id;

12、索引统计信息过期

场景：表数据变化频繁但未更新统计信息

ANALYZE TABLE table; -- 手动更新统计信息

原理：优化器基于过时统计信息选择低效执行计划

解决：定期执行 ANALYZE TABLE 或设置自动统计更新

二、索引失效排查工具

1、EXPLAIN 分析执行计划

关注 

type 列：index/range 优于 ALL（全表扫描）

key 列：实际使用的索引名称

Extra 列：Using where; Using index 表示覆盖索引

2、开启慢查询

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询