索引是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';

五、总结：索引优化的黄金法则

1. 写查询比建索引更重要：80%的性能问题源于错误SQL
2. 理解B+树特性：有序性决定前缀匹配的有效性
3. 关注基数与成本：优化器的选择可能不符合直觉
4. 监控索引使用率：定期执行 SHOW INDEX_STATISTICS

通过精准识别索引失效场景，结合执行计划分析与版本特性，可系统性提升MySQL的索引命中率，让查询性能飞驰在正确的轨道上。