索引是MySQL性能优化的核心武器，但错误的使用场景可能让索引完全失效，导致查询性能断崖式下降。本文通过实际案例，深入剖析索引失效的典型场景及其底层原理，并提供可落地的解决方案。
一、索引失效的核心原理
1.索引数据结构特性
- B+树索引：有序存储，适合范围查询和前缀匹配
- 回表代价：非覆盖索引需二次查询主键索引
2.优化器决策机制
- 成本估算：基于索引选择性（Cardinality）和IO成本
- 强制索引（FORCE INDEX）可能破坏优化器决策平衡
二、索引失效的10大典型场景
1.对索引列进行运算或函数操作
sql
-- 失效案例：对索引列做运算
SELECT * FROM users WHERE YEAR(create_time) = 2023;
-- 优化方案：使用范围查询
SELECT * FROM users
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';2.隐式类型转换
sql
-- user_id是VARCHAR类型，但用数字查询（触发类型转换）
SELECT * FROM users WHERE user_id = 10086;
-- 执行计划显示：type=ALL（全表扫描）3.前导通配符模糊查询
sql
-- 无法使用name索引
SELECT * FROM users WHERE name LIKE '%张%';
-- 优化方案：后缀匹配 + 倒序索引
ALTER TABLE users ADD INDEX idx_name_reverse (reverse(name));
SELECT * FROM users WHERE reverse(name) LIKE reverse('%张');4.OR连接非索引列条件
sql
-- age未建索引，导致全表扫描
SELECT * FROM users WHERE id = 100 OR age > 30;5.联合索引最左前缀缺失
sql
-- 联合索引 (a,b,c)
SELECT * FROM table WHERE b = 2 AND c = 3; -- 未使用索引
SELECT * FROM table WHERE a = 1 AND c = 3; -- 仅使用a列索引6.数据倾斜导致索引选择性过低
sql
-- 性别字段（cardinality极低）
SELECT * FROM users WHERE gender = 'F'; -- 可能全表扫描7.使用NOT或负向条件
sql
-- 无法使用索引
SELECT * FROM orders WHERE status NOT IN ('paid', 'completed');8.索引列参与JOIN的隐式转换
sql
-- 两表user_id类型不同（INT vs VARCHAR）
SELECT * FROM t1
JOIN t2 ON t1.user_id = t2.user_id; -- 全表扫描9.强制类型转换破坏索引
sql
-- CAST导致索引失效
SELECT * FROM products
WHERE CAST(price AS DECIMAL) > 100.00;10.全文索引的误用
sql
-- MATCH...AGAINST未遵循最左匹配原则
SELECT * FROM articles
WHERE MATCH(title,content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);三、诊断与优化工具箱
1.执行计划分析（EXPLAIN）
- type列：
- const/system > ref > range > index > ALL
- key_len：确认索引使用长度
- Extra：
- Using index：覆盖索引
- Using filesort：需优化排序
2.索引选择性计算
sql
SELECT
COUNT(DISTINCT column)/COUNT(*) AS selectivity
FROM table;
-- 选择性 > 10% 适合建索引3.强制索引的风险提示
sql
-- 需对比有无FORCE INDEX的查询成本
SELECT * FROM table FORCE INDEX(idx_column) WHERE ...;4.优化器提示（Optimizer Hints）
sql
/*+ INDEX(table idx_name) */
SELECT * FROM table WHERE ...;四、高级优化策略
1.索引跳跃扫描（Index Skip Scan）
- MySQL 8.0+ 特性，缓解最左前缀缺失问题
- 适用条件：前导列基数低，后续列基数高
2.虚拟列索引（Generated Columns）
sql
-- 对函数结果建索引
ALTER TABLE users
ADD COLUMN name_reverse VARCHAR(255) AS (REVERSE(name)),
ADD INDEX idx_name_reverse (name_reverse);3.索引合并优化
sql
-- index_merge场景（需权衡成本）
SET optimizer_switch='index_merge=on';五、总结：索引优化的黄金法则
- 写查询比建索引更重要：80%的性能问题源于错误SQL
- 理解B+树特性：有序性决定前缀匹配的有效性
- 关注基数与成本：优化器的选择可能不符合直觉
- 监控索引使用率：定期执行 SHOW INDEX_STATISTICS
通过精准识别索引失效场景，结合执行计划分析与版本特性，可系统性提升MySQL的索引命中率，让查询性能飞驰在正确的轨道上。