MySQL索引失效是性能优化的关键问题，以下列举15+个典型场景，结合原理和示例分析，帮你全面避坑：
一、基础失效场景对索引列进行计算或函数操作
sql
SELECT*FROMusersWHEREYEAR(create_time)=2023;--索引失效
分析：对create_time索引列使用YEAR()函数，需逐行计算后再比较。
优化：改用范围查询：
sql
SELECT*FROMusersWHEREcreate_timeBETWEEN'2023-01-01'AND'2023-12-31';
隐式类型转换
sql
SELECT*FROMusersWHEREphone=;--phone是varchar类型
分析：数字被转为字符串，相当于对索引列做CAST(phoneASsigned)操作。
优化：保持类型一致：
sql
SELECT*FROMusersWHEREphone='';
最左前缀原则缺失
索引：(name,age)
sql
SELECT*FROMusersWHEREage=30;--失效（未用name）SELECT*FROMusersWHEREnameLIKE'A%'ANDage=30;--有效
使用OR连接非索引列
sql
SELECT*FROMusersWHEREid=100ORemail='test@';--若email无索引，全表扫描
优化：拆分查询或用UNION：
sql
SELECT*FROMusersWHEREid=100UNIONSELECT*FROMusersWHEREemail='test@';
二、复杂查询失效场景范围查询阻断后续索引
索引：(age,salary)
sql
SELECT*FROMusersWHEREage25ANDsalary=8000;--salary无法用索引
原理：age25返回大量无序数据，无法再用索引过滤salary。
!=或操作符
sql
SELECT*FROMusersWHEREstatus!=1;--全表扫描（需排除大部分数据）
例外：覆盖索引可能被使用（只需查索引）。
NOTIN/NOTEXISTS
sql
SELECT*FROMusersWHEREidNOTIN(1,2,3);--通常全表扫描
优化：小数据集用LEFTJOIN过滤：
sql
SELECTu.*FROMusersuLEFTJOIN(SELECT1ASidUNIONSELECT2)=;
LIKE以通配符开头
sql
SELECT*FROMusersWHEREnameLIKE'%abc';--索引失效
优化：
后缀匹配：LIKE'abc%'（有效）
全文索引：MATCH(name)AGAINST('abc')
三、优化器行为导致失效索引区分度过低
例如ger列（值仅M/F），优化器可能弃用索引。
解决方案：复合索引（如(ger,age)）。
表数据量过小
当行数全表扫描成本阈值时（如1000行），优化器直接全表扫描。
统计信息不准确
现象：EXPLAIN显示错误执行计划。
修复：ANALYZETABLEtable_name;更新统计信息。
强制索引合并成本高
sql
SELECT*FROMusersWHEREname='John'ORage=30;--可能全表扫描
优化器逻辑：当OR条件涉及不同索引，合并成本高时弃用索引。
四、隐式陷阱字符集/排序规则不匹配
sql
SELECT*_col=_col;--隐式转换失效
解决方案：统一字符集或显式转换：
sql
ONCONVERT(_colUSINGlatin1)=_col
JOIN列类型不一致
sql
SELECT*_id=;--若_id为varchar，为int，失效
原理：类型不匹配触发隐式转换。
索引列使用子查询
sql
SELECT*FROMproductsWHEREcategory_idIN(SELECTidFROMcategoriesWHEREname='Electronics');--可能失效
优化：改用JOIN：
sql
SELECTp.*_id=='Electronics';
五、特殊场景使用ORDERBY非索引列
sql
SELECT*FROMusersORDERBYcreate_timeDESC;--无索引时filesort
优化：为排序字段添加索引。
覆盖索引未命中
sql
SELECT*FROMtableWHEREindexed_col=1;--需回表
对比：若只查索引列则无需回表（Usingindex）。
索引损坏
现象：索引存在但查询极慢。
修复：REPAIRTABLEtable_name;或重建索引。
场景
优化策略
函数操作
重写查询避免计算
类型不匹配
统一数据类型
最左前缀缺失
调整索引顺序或新增复合索引
OR条件失效
改UNION或确保所有列有索引
LIKE通配符开头
倒排索引/ES分词
优化器选错索引
FORCEINDEX（慎用）
统计信息过期
ANALYZETABLE
通过理解这些场景，结合EXPLAIN分析，能有效规避索引失效问题，提升查询性能！
