MySQL索引失效是性能优化的关键问题，以下列举15+个典型场景，结合原理和示例分析，帮你全面避坑：

一、基础失效场景

1. 对索引列进行计算或函数操作
2. sql
3. SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 索引失效
4. 分析：对create_time索引列使用YEAR()函数，需逐行计算后再比较。
   优化：改用范围查询：
5. sql
6. SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
7. 隐式类型转换
8. sql
9. SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar类型
10. 分析：数字13800138000被转为字符串，相当于对索引列做CAST(phone AS signed)操作。
    优化：保持类型一致：
11. sql
12. SELECT * FROM users WHERE phone = '13800138000';
13. 最左前缀原则缺失
    索引：(name, age)
14. sql
15. SELECT * FROM users WHERE age = 30; -- 失效（未用name） SELECT * FROM users WHERE name LIKE 'A%' AND age = 30; -- 有效
16. 使用OR连接非索引列
17. sql
18. SELECT * FROM users WHERE id = 100 OR email = 'test@example.com'; -- 若email无索引，全表扫描
19. 优化：拆分查询或用UNION：
20. sql
21. SELECT * FROM users WHERE id = 100 UNION SELECT * FROM users WHERE email = 'test@example.com';

二、复杂查询失效场景

5. 范围查询阻断后续索引
   索引：(age, salary)
6. sql
7. SELECT * FROM users WHERE age > 25 AND salary = 8000; -- salary无法用索引
8. 原理：age>25返回大量无序数据，无法再用索引过滤salary。
9. !=或<>操作符
10. sql
11. SELECT * FROM users WHERE status != 1; -- 全表扫描（需排除大部分数据）
12. 例外：覆盖索引可能被使用（只需查索引）。
13. NOT IN / NOT EXISTS
14. sql
15. SELECT * FROM users WHERE id NOT IN (1,2,3); -- 通常全表扫描
16. 优化：小数据集用LEFT JOIN过滤：
17. sql
18. 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;
19. LIKE以通配符开头
20. sql
21. SELECT * FROM users WHERE name LIKE '%abc'; -- 索引失效
22. 优化：
23. 后缀匹配：LIKE 'abc%'（有效）
24. 全文索引：MATCH(name) AGAINST('abc')

三、优化器行为导致失效

9. 索引区分度过低
   例如gender列（值仅M/F），优化器可能弃用索引。
   解决方案：复合索引（如(gender, age)）。
10. 表数据量过小
    当行数 < 全表扫描成本阈值时（如<1000行），优化器直接全表扫描。
11. 统计信息不准确
    现象：EXPLAIN显示错误执行计划。
    修复：ANALYZE TABLE table_name; 更新统计信息。
12. 强制索引合并成本高
13. sql
14. SELECT * FROM users WHERE name = 'John' OR age = 30; -- 可能全表扫描
15. 优化器逻辑：当OR条件涉及不同索引，合并成本高时弃用索引。

四、隐式陷阱

13. 字符集/排序规则不匹配
14. sql
15. SELECT * FROM t1 JOIN t2 ON t1.utf8_col = t2.latin1_col; -- 隐式转换失效
16. 解决方案：统一字符集或显式转换：
17. sql
18. ON CONVERT(t1.utf8_col USING latin1) = t2.latin1_col
19. JOIN列类型不一致
20. sql
21. SELECT * FROM orders o JOIN users u ON o.user_id = u.id; -- 若o.user_id为varchar，u.id为int，失效
22. 原理：类型不匹配触发隐式转换。
23. 索引列使用子查询
24. sql
25. SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics'); -- 可能失效
26. 优化：改用JOIN：
27. sql
28. SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics';

五、特殊场景

16. 使用ORDER BY非索引列
17. sql
18. SELECT * FROM users ORDER BY create_time DESC; -- 无索引时filesort
19. 优化：为排序字段添加索引。
20. 覆盖索引未命中
21. sql
22. SELECT * FROM table WHERE indexed_col = 1; -- 需回表
23. 对比：若只查索引列则无需回表（Using index）。
24. 索引损坏
    现象：索引存在但查询极慢。
    修复：REPAIR TABLE table_name; 或重建索引。

解决方案总结

终极诊断工具：
使用EXPLAIN查看执行计划，关注：

type列：ALL（全表扫描）、index（全索引扫描）需警惕

key列：实际使用的索引

Extra列：Using filesort、Using temporary表示性能瓶颈

通过理解这些场景，结合EXPLAIN分析，能有效规避索引失效问题，提升查询性能！