MySQL索引失效是性能优化的关键问题，以下列举15+个典型场景，结合原理和示例分析，帮你全面避坑：
一、基础失效场景
- 对索引列进行计算或函数操作
- sql
- SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效
- 分析：对create_time索引列使用YEAR()函数，需逐行计算后再比较。
优化：改用范围查询： - sql
- SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
- 隐式类型转换
- sql
- SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型
- 分析：数字13800138000被转为字符串，相当于对索引列做CAST(phone AS signed)操作。
优化：保持类型一致： - sql
- SELECT * FROM users WHERE phone = '13800138000';
- 最左前缀原则缺失
索引：(name, age) - sql
- SELECT * FROM users WHERE age = 30; -- 失效（未用name） SELECT * FROM users WHERE name LIKE 'A%' AND age = 30; -- 有效
- 使用OR连接非索引列
- sql
- SELECT * FROM users WHERE id = 100 OR email = 'test@example.com'; -- 若email无索引，全表扫描
- 优化：拆分查询或用UNION：
- sql
- SELECT * FROM users WHERE id = 100 UNION SELECT * FROM users WHERE email = 'test@example.com';
二、复杂查询失效场景
- 范围查询阻断后续索引
索引：(age, salary) - sql
- SELECT * FROM users WHERE age > 25 AND salary = 8000; -- salary无法用索引
- 原理：age>25返回大量无序数据，无法再用索引过滤salary。
- !=或<>操作符
- sql
- SELECT * FROM users WHERE status != 1; -- 全表扫描（需排除大部分数据）
- 例外：覆盖索引可能被使用（只需查索引）。
- NOT IN / NOT EXISTS
- sql
- SELECT * FROM users WHERE id NOT IN (1,2,3); -- 通常全表扫描
- 优化：小数据集用LEFT JOIN过滤：
- sql
- SELECT u.* FROM users u LEFT JOIN (SELECT 1 AS id UNION SELECT 2 ...) t ON u.id = t.id WHERE t.id IS NULL;
- LIKE以通配符开头
- sql
- SELECT * FROM users WHERE name LIKE '%abc'; -- 索引失效
- 优化：
- 后缀匹配：LIKE 'abc%'（有效）
- 全文索引：MATCH(name) AGAINST('abc')
三、优化器行为导致失效
- 索引区分度过低
例如gender列（值仅M/F），优化器可能弃用索引。
解决方案：复合索引（如(gender, age)）。 - 表数据量过小
当行数 < 全表扫描成本阈值时（如<1000行），优化器直接全表扫描。 - 统计信息不准确
现象：EXPLAIN显示错误执行计划。
修复：ANALYZE TABLE table_name; 更新统计信息。 - 强制索引合并成本高
- sql
- SELECT * FROM users WHERE name = 'John' OR age = 30; -- 可能全表扫描
- 优化器逻辑：当OR条件涉及不同索引，合并成本高时弃用索引。
四、隐式陷阱
- 字符集/排序规则不匹配
- sql
- SELECT * FROM t1 JOIN t2 ON t1.utf8_col = t2.latin1_col; -- 隐式转换失效
- 解决方案：统一字符集或显式转换：
- sql
- ON CONVERT(t1.utf8_col USING latin1) = t2.latin1_col
- JOIN列类型不一致
- sql
- SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- 若o.user_id为varchar，u.id为int，失效
- 原理：类型不匹配触发隐式转换。
- 索引列使用子查询
- sql
- SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics'); -- 可能失效
- 优化：改用JOIN：
- sql
- SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics';
五、特殊场景
- 使用ORDER BY非索引列
- sql
- SELECT * FROM users ORDER BY create_time DESC; -- 无索引时filesort
- 优化：为排序字段添加索引。
- 覆盖索引未命中
- sql
- SELECT * FROM table WHERE indexed_col = 1; -- 需回表
- 对比：若只查索引列则无需回表（Using index）。
- 索引损坏
现象：索引存在但查询极慢。
修复：REPAIR TABLE table_name; 或重建索引。
解决方案总结
场景 | 优化策略 |
函数操作 | 重写查询避免计算 |
类型不匹配 | 统一数据类型 |
最左前缀缺失 | 调整索引顺序或新增复合索引 |
OR条件失效 | 改UNION或确保所有列有索引 |
LIKE通配符开头 | 倒排索引/ES分词 |
优化器选错索引 | FORCE INDEX（慎用） |
统计信息过期 | ANALYZE TABLE |
终极诊断工具：
使用EXPLAIN查看执行计划，关注：type列：ALL（全表扫描）、index（全索引扫描）需警惕
key列：实际使用的索引
Extra列：Using filesort、Using temporary表示性能瓶颈
通过理解这些场景，结合EXPLAIN分析，能有效规避索引失效问题，提升查询性能！