MySQL 索引：并非万能+失效场景+排查全解

---

一、为什么索引不一定有效？

1. 索引是“目录”，不是万能加速符

• 索引是B+树结构，只存索引列+主键，需要维护成本（增删改会更新索引）。
• 优化器会算两笔账：走索引的IO成本、全表扫描的IO成本。如果索引查询的数据占比太高（比如超过30%），优化器会直接选全表扫描。
• 索引列区分度太低（如性别只有男/女），索引几乎无效。

2. 两种“无效”情况

1. 完全没用到索引：key=NULL，走全表扫描（ALL）。
2. 用到索引但很慢：回表太多、随机IO高、索引选择不当。

---

二、7大索引失效场景（原理+案例+修复）

1. 联合索引违反最左前缀原则

• 原理：联合索引(a,b,c)按a→b→c排序，跳过最左列，索引无法匹配。
• 反例：索引(a,b,c)，查询where b=1 and c=2
• 正例：where a=1 and b=2、where a=1
• 补充：范围查询（>、<）会中断前缀，后面列无法用索引。

2. 索引列上使用函数

• 原理：索引存原始值，函数会破坏索引有序性，只能逐行计算。
• 反例：where date(create_time) = '2025-01-01'
• 修复：where create_time between '2025-01-01 00:00:00' and '2025-01-01 23:59:59'
• 进阶：MySQL 8.0支持函数索引，可建index((date(create_time)))。

3. 索引列上做运算

• 原理：对索引列加减乘除，无法直接匹配索引值。
• 反例：where age + 1 = 20
• 修复：where age = 19

4. LIKE左侧带通配符

• 原理：%开头破坏索引有序性，无法前缀匹配。
• 反例：where name like '%张'
• 修复：where name like '张%'；模糊匹配量大可用ES。

5. OR连接非索引字段

• 原理：OR一侧无索引，优化器会放弃索引走全表扫描。
• 反例：where a=1 or b=2（仅a有索引）
• 修复：两边都建索引；拆为UNION ALL。

6. 隐式类型转换

• 原理：字段类型与参数类型不匹配，MySQL自动转换，索引失效。
• 反例：phone是varchar，查询where phone=13800138000
• 修复：where phone='13800138000'

7. 优化器判定全表扫描更快

• 场景：小表、结果集占比高、统计信息过时。
• 原理：优化器按统计信息估算，索引成本>全表扫描时，直接弃用索引。
• 修复：更新统计信息analyze table 表名；调整索引设计。

---

三、索引效果排查：用EXPLAIN看懂执行计划

1. 基础用法

在SQL前加EXPLAIN，查看执行计划：

EXPLAIN SELECT * FROM user WHERE age=20;

2. 核心字段（必记）

字段	含义	索引判断
type	访问类型	ALL=全表扫描；index=索引全扫；range=范围索引；ref=普通索引；const=主键/唯一索引（最优）
key	实际使用索引	NULL=未用索引
rows	预估扫描行数	越小越好
Extra	额外信息	Using index=覆盖索引；Using filesort=文件排序（索引失效）；Using temporary=临时表

3. 三步排查法

1. 看key是否为NULL：NULL=索引未生效。
2. 看type：ALL最差，index次之，ref/range合格，const最优。
3. 看Extra：无Using index大概率回表；Using filesort需优化。

---

四、进阶排查与优化技巧

1. 查看索引使用频次

SHOW INDEX FROM 表名; -- 查看索引结构
SELECT * FROM sys.schema_unused_indexes; -- 查看未使用索引

2. 更新统计信息

ANALYZE TABLE 表名;

3. 强制使用索引（谨慎）

SELECT * FROM user FORCE INDEX(idx_age) WHERE age=20;

4. 设计高区分度索引：优先选唯一/高基数列，避免低基数列单独建索引。
5. 用覆盖索引消除回表：把查询列加入联合索引，Extra出现Using index。

---

五、总结（初学者记忆口诀）

建索引不代表生效，优化器按成本定输赢；
最左前缀要遵守，函数运算别碰索引列；
左模糊OR隐转，索引失效全白干；
EXPLAIN看key行，覆盖索引效率强。

---