MySQL索引失效完全指南：从原理到10大场景，避坑必看

前言索引是MySQL性能优化的核心武器但很多开发者都遇到过“明明加了索引SQL还是慢”的问题——这大概率是索引失效了。索引失效的本质是MySQL优化器认为“走索引的成本比全表扫描更高”或者“索引的有序性被破坏无法利用B树的特性”。本文将从B树索引的核心原理出发拆解10大最常见的索引失效场景每个场景配错误示例、正确写法、EXPLAIN对比和原理分析最后给出一套可复用的避坑指南帮你彻底搞懂索引失效的底层逻辑。文章目录前言一、前置核心认知索引生效的两个前提1.1 B树索引的核心特性1.2 优化器的选择逻辑二、10大索引失效场景详解场景1违反联合索引的「最左前缀原则」最常见占比60%核心原理回顾失效示例错误SQL与EXPLAIN对比错误SQL跳过最左列正确SQL完整匹配最左前缀避坑指南场景2在索引列上使用函数或表达式第二常见核心原理失效示例错误SQL与正确写法对比错误SQL用YEAR()函数正确写法1把函数移到等号右边用范围查询正确写法2MySQL 8.0创建函数索引避坑指南场景3隐式类型转换容易忽略后果严重核心原理失效示例错误SQL与正确写法对比错误SQL隐式类型转换正确写法保持类型一致额外风险隐式类型转换可能查错数据避坑指南场景4LIKE通配符在开头经典错误核心原理失效示例错误SQL与正确写法对比错误SQL通配符在开头正确写法1业务允许的话用前缀匹配正确写法2必须用中间/后缀匹配用全文索引或Elasticsearch避坑指南场景5使用OR但OR两边的列不都是索引列核心原理失效示例错误SQL与正确写法对比错误SQLOR两边不都是索引列正确写法1给OR两边的列都加索引正确写法2用UNION ALL替代OR避坑指南场景6使用NOT IN、NOT EXISTS、等负向查询核心原理失效示例错误SQL与正确写法对比错误SQL用NOT IN正确写法1业务允许的话用正向查询替代正确写法2必须用负向查询用覆盖索引优化避坑指南场景7索引列的区分度太低核心原理失效示例错误SQL与EXPLAIN对比错误SQL用区分度低的列查询避坑指南场景8数据量太小优化器选择全表扫描核心原理失效示例避坑指南场景9MySQL 8.0的函数索引没正确创建失效示例避坑指南场景10统计信息过期优化器选错执行计划核心原理失效示例解决方法避坑指南三、如何验证索引是否生效四、避坑指南避免索引失效的5个核心习惯五、总结一、前置核心认知索引生效的两个前提在讲失效场景之前我们先明确两个决定索引是否生效的核心前提这是所有分析的基础1.1 B树索引的核心特性MySQL的InnoDB索引是B树结构它的核心特性是有序性索引键值在B树中严格有序排列最左前缀匹配联合索引a,b,c中查询必须从最左边的列开始且不能跳过中间列才能利用索引的有序性等值匹配优先范围匹配次之等值匹配、IN能精准定位到B树的节点范围匹配、、BETWEEN只能定位到一个区间范围匹配后的列无法再利用索引。1.2 优化器的选择逻辑MySQL优化器不是“只要有索引就一定会用”而是会计算走索引和全表扫描的成本选择成本更低的方案走索引的成本读取索引页 回表读取数据页如果是聚簇索引则不需要回表全表扫描的成本直接读取所有数据页。如果优化器认为“走索引的成本更高”比如数据量太小、索引区分度太低就会放弃索引选择全表扫描。二、10大索引失效场景详解我们从最常见、最基础的场景开始逐一拆解场景1违反联合索引的「最左前缀原则」最常见占比60%这是新手最容易犯的错误也是索引失效的第一大原因。核心原理回顾联合索引a,b,c的B树结构是先按a排序a相同的情况下再按b排序a和b都相同的情况下再按c排序。因此查询必须从最左边的列开始且不能跳过中间列才能利用索引的有序性。失效示例假设我们有联合索引idx_a_b_c (a, b, c)查询条件索引是否生效原因WHERE a 1✅ 完全生效从最左列a开始WHERE a 1 AND b 2✅ 完全生效从a开始不跳过中间列WHERE a 1 AND b 2 AND c 3✅ 完全生效完整匹配最左前缀WHERE b 2❌ 完全失效跳过了最左列aWHERE c 3❌ 完全失效跳过了a和bWHERE a 1 AND c 3⚠️ 部分失效只有a生效跳过了中间列bc无法利用索引错误SQL与EXPLAIN对比错误SQL跳过最左列-- 错误写法跳过最左列a索引完全失效EXPLAINSELECT*FROMtest_tableWHEREb2;EXPLAIN结果type: ALL -- 全表扫描 key: NULL -- 没用到索引 rows: 1000000 -- 扫描了100万行 Extra: Using where正确SQL完整匹配最左前缀-- 正确写法从最左列a开始完整匹配EXPLAINSELECT*FROMtest_tableWHEREa1ANDb2ANDc3;EXPLAIN结果type: ref -- 非唯一索引等值查询 key: idx_a_b_c -- 用到了联合索引 rows: 10 -- 只扫描了10行 Extra: NULL避坑指南联合索引的列顺序必须匹配业务的高频查询顺序把最常用的查询条件放在最左边不要跳过中间列如果业务需要查询a和c考虑把联合索引调整为a,c,b或者单独给a和c加索引用EXPLAIN验证重点看key列是否用到了预期的索引type列是否至少到range。场景2在索引列上使用函数或表达式第二常见在索引列上使用函数YEAR()、DATE()、UPPER()或表达式id 1、id * 2会直接破坏索引的有序性导致索引失效。核心原理MySQL的索引是对列的原始值建立的B树。如果在列上用了函数或表达式索引的有序性就被破坏了——优化器无法通过原始值的B树定位到函数计算后的值只能全表扫描。失效示例假设我们有索引idx_create_time (create_time)查询条件索引是否生效原因WHERE YEAR(create_time) 2026❌ 失效在索引列上用了YEAR()函数WHERE id 1 2❌ 失效在索引列上用了表达式WHERE UPPER(username) ZHANGSAN❌ 失效在索引列上用了UPPER()函数错误SQL与正确写法对比错误SQL用YEAR()函数-- 错误写法在索引列create_time上用YEAR()函数EXPLAINSELECT*FROMuser_infoWHEREYEAR(create_time)2026;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where正确写法1把函数移到等号右边用范围查询-- 正确写法create_time保持干净用范围查询替代YEAR()EXPLAINSELECT*FROMuser_infoWHEREcreate_time2026-01-01ANDcreate_time2027-01-01;EXPLAIN结果type: range -- 范围查询用到了索引 key: idx_create_time rows: 50000 -- 只扫描了5万行 Extra: Using where正确写法2MySQL 8.0创建函数索引如果必须在列上用函数可以创建函数索引MySQL 8.0.13支持-- 第一步创建函数索引CREATEINDEXidx_year_create_timeONuser_info((YEAR(create_time)));-- 第二步用YEAR()查询能用到函数索引EXPLAINSELECT*FROMuser_infoWHEREYEAR(create_time)2026;EXPLAIN结果type: ref key: idx_year_create_time rows: 50000 Extra: Using where避坑指南永远保持索引列“干净”不要在索引列上用函数或表达式把计算移到等号右边范围查询替代函数比如用和替代YEAR()、MONTH()MySQL 8.0可以用函数索引但函数索引只能用于特定的函数查询通用性不如普通索引。场景3隐式类型转换容易忽略后果严重查询时字段类型和参数类型不一致MySQL会自动做隐式类型转换这会直接破坏索引的有序性导致索引失效甚至可能查错数据。核心原理隐式类型转换的规则比较复杂但核心是如果索引列是字符串类型参数是数字类型MySQL会把字符串转换为数字再比较——这会破坏字符串索引的有序性导致索引失效。失效示例假设我们有索引idx_phone (phone)phone字段是varchar类型查询条件索引是否生效原因WHERE phone 13800138000✅ 生效字段类型和参数类型一致都是varcharWHERE phone 13800138000❌ 失效字段是varchar参数是数字隐式类型转换WHERE id 1⚠️ 通常生效id是bigint数字类型的索引列参数是字符串MySQL会把字符串转数字索引通常能生效但不推荐错误SQL与正确写法对比错误SQL隐式类型转换-- 错误写法phone是varchar参数是数字13800138000隐式类型转换EXPLAINSELECT*FROMuser_infoWHEREphone13800138000;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where正确写法保持类型一致-- 正确写法phone是varchar参数用字符串13800138000EXPLAINSELECT*FROMuser_infoWHEREphone13800138000;EXPLAIN结果type: ref key: idx_phone rows: 1 Extra: NULL额外风险隐式类型转换可能查错数据隐式类型转换不仅会导致索引失效还可能查错数据-- phone是varchar有数据13800138000和13800138000aSELECT*FROMuser_infoWHEREphone13800138000;-- 结果会把13800138000和13800138000a都查出来-- 因为MySQL把字符串13800138000a转换为数字时会忽略后面的字母变成13800138000避坑指南永远保持字段类型和参数类型一致varchar字段用字符串参数bigint字段用数字参数建表时选择合适的数据类型手机号、身份证号用varchar不要用bigint用EXPLAIN验证如果key是NULL且字段类型和参数类型不一致大概率是隐式类型转换导致的。场景4LIKE通配符在开头经典错误用LIKE模糊查询时把通配符%或_放在开头会直接破坏索引的有序性导致索引失效。核心原理B树的索引是按前缀有序排列的LIKE 张三%✅ 能用索引前缀匹配利用索引的有序性LIKE %张三❌ 索引失效后缀匹配无法利用索引的有序性LIKE %张三%❌ 索引失效中间匹配无法利用索引的有序性。失效示例假设我们有索引idx_username (username)查询条件索引是否生效原因WHERE username LIKE 张三%✅ 生效通配符在结尾前缀匹配WHERE username LIKE %张三❌ 失效通配符在开头WHERE username LIKE %张三%❌ 失效通配符在中间和结尾错误SQL与正确写法对比错误SQL通配符在开头-- 错误写法通配符在开头索引失效EXPLAINSELECT*FROMuser_infoWHEREusernameLIKE%张三%;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where正确写法1业务允许的话用前缀匹配-- 正确写法通配符在结尾前缀匹配能用索引EXPLAINSELECT*FROMuser_infoWHEREusernameLIKE张三%;EXPLAIN结果type: range key: idx_username rows: 1000 Extra: Using where正确写法2必须用中间/后缀匹配用全文索引或Elasticsearch如果业务必须用%张三%这样的模糊查询不要用LIKE改用MySQL全文索引FULLTEXT INDEX-- 第一步创建全文索引CREATEFULLTEXTINDEXidx_fulltext_usernameONuser_info(username);-- 第二步用MATCH AGAINST查询EXPLAINSELECT*FROMuser_infoWHEREMATCH(username)AGAINST(张三INBOOLEANMODE);Elasticsearch把数据同步到ES用ES做模糊查询性能更好功能更强大。避坑指南LIKE查询尽量用前缀匹配通配符只放结尾必须用中间/后缀匹配时用全文索引或ES不要用LIKE用EXPLAIN验证如果type是ALL且查询条件是LIKE通配符在开头就是索引失效了。场景5使用OR但OR两边的列不都是索引列使用OR连接查询条件时只有当OR两边的列都有索引优化器才会考虑用索引如果有一边的列没有索引整个查询的索引都会失效。核心原理OR的逻辑是“满足任意一个条件即可”如果有一边的列没有索引优化器无法通过索引定位到所有满足条件的行只能全表扫描。失效示例假设我们有索引idx_a (a)但b列没有索引查询条件索引是否生效原因WHERE a 1 OR a 2✅ 生效OR两边都是a列都有索引WHERE a 1 OR b 2❌ 失效b列没有索引整个查询索引失效错误SQL与正确写法对比错误SQLOR两边不都是索引列-- 错误写法OR两边的b列没有索引整个查询索引失效EXPLAINSELECT*FROMtest_tableWHEREa1ORb2;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where正确写法1给OR两边的列都加索引-- 第一步给b列加索引ALTERTABLEtest_tableADDINDEXidx_b(b);-- 第二步用OR查询优化器会用索引合并Index MergeEXPLAINSELECT*FROMtest_tableWHEREa1ORb2;EXPLAIN结果type: index_merge -- 索引合并用到了idx_a和idx_b key: idx_a, idx_b rows: 20 Extra: Using union(idx_a, idx_b); Using where正确写法2用UNION ALL替代OR如果不想给b列加索引或者索引合并的性能不好可以用UNION ALL替代OR-- 正确写法用UNION ALL替代OR两个子查询都能用索引EXPLAINSELECT*FROMtest_tableWHEREa1UNIONALLSELECT*FROMtest_tableWHEREb2;EXPLAIN结果-- 第一个子查询 type: ref key: idx_a rows: 10 -- 第二个子查询 type: ALL -- b列没有索引全表扫描但至少a列的查询用到了索引 key: NULL rows: 1000000注如果b列也有索引两个子查询都会用到索引避坑指南OR两边的列都要有索引否则整个查询的索引都会失效优先用UNION ALL替代OR性能更稳定且更容易优化用EXPLAIN验证如果type是ALL且查询条件是OR检查OR两边的列是否都有索引。场景6使用NOT IN、NOT EXISTS、等负向查询使用NOT IN、NOT EXISTS、不等于等负向查询时优化器通常不会选择索引而是全表扫描。核心原理负向查询的逻辑是“排除满足条件的行”B树的索引是为“正向匹配”设计的负向查询无法高效利用索引的有序性优化器通常认为全表扫描的成本更低。失效示例假设我们有索引idx_city (city)查询条件索引是否生效原因WHERE city IN (武汉, 北京)✅ 生效正向查询IN能用索引WHERE city NOT IN (武汉, 北京)❌ 通常失效负向查询NOT IN通常不用索引WHERE city 武汉❌ 通常失效负向查询通常不用索引WHERE NOT EXISTS (SELECT 1 FROM ...)❌ 通常失效负向查询NOT EXISTS通常不用索引错误SQL与正确写法对比错误SQL用NOT IN-- 错误写法用NOT IN索引通常失效EXPLAINSELECT*FROMuser_infoWHEREcityNOTIN(武汉,北京);EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where正确写法1业务允许的话用正向查询替代如果业务允许尽量用正向查询IN、EXISTS、替代负向查询-- 假设业务需要查询“除了武汉和北京的其他城市”可以用IN列出所有其他城市如果城市数量少EXPLAINSELECT*FROMuser_infoWHEREcityIN(上海,广州,深圳);正确写法2必须用负向查询用覆盖索引优化如果必须用负向查询可以创建覆盖索引让优化器认为“走索引的成本更低”-- 第一步创建覆盖索引包含查询需要的所有字段CREATEINDEXidx_city_coverONuser_info(city,id,username,phone);-- 第二步用负向查询优化器可能会选择覆盖索引因为不需要回表EXPLAINSELECTid,username,phoneFROMuser_infoWHEREcityNOTIN(武汉,北京);EXPLAIN结果type: range -- 范围查询用到了覆盖索引 key: idx_city_cover rows: 500000 Extra: Using where; Using index -- Using index说明用到了覆盖索引无需回表避坑指南尽量避免负向查询优先用正向查询替代必须用负向查询时用覆盖索引优化让优化器认为走索引的成本更低用EXPLAIN验证如果type是ALL且查询条件是负向查询考虑用覆盖索引优化。场景7索引列的区分度太低索引列的区分度Cardinality太低优化器会认为“走索引的成本比全表扫描更高”从而放弃索引选择全表扫描。核心原理区分度的计算公式是区分度 唯一值数量 / 总行数取值范围是0-1越接近1区分度越高。区分度高的列用户ID、订单ID、手机号唯一值多区分度接近1区分度低的列性别只有男/女区分度0.000001、状态只有0/1/2区分度0.00001、地区如果只有几个地区区分度也很低。如果索引列的区分度太低走索引需要读取大量的索引页然后回表读取大量的数据页成本比全表扫描更高优化器会直接放弃索引。失效示例假设我们有索引idx_gender (gender)gender列只有“男”和“女”两个值表有100万行数据查询条件索引是否生效原因WHERE id 1✅ 生效id是主键区分度1WHERE gender 男❌ 失效gender区分度太低优化器认为全表扫描更快错误SQL与EXPLAIN对比错误SQL用区分度低的列查询-- 错误写法gender区分度太低索引失效EXPLAINSELECT*FROMuser_infoWHEREgender男;EXPLAIN结果type: ALL key: NULL rows: 1000000 Extra: Using where避坑指南不要给区分度太低的列加索引比如性别、状态、只有几个值的地区如果必须给区分度低的列加索引用联合索引把区分度低的列和区分度高的列组合成联合索引比如idx_gender_city (gender, city)或者idx_city_gender (city, gender)把区分度高的列放在前面用EXPLAIN验证如果key是NULL且索引列的区分度很低就是优化器认为走索引成本太高。场景8数据量太小优化器选择全表扫描如果表的数据量太小比如只有几百行、几千行优化器会认为“全表扫描的成本比走索引更低”从而放弃索引选择全表扫描。核心原理全表扫描是顺序IO走索引是随机IO需要读取索引页回表数据页。对于小表来说顺序IO读取所有数据页的成本比随机IO读取索引页回表的成本更低优化器会直接选择全表扫描。失效示例假设表只有100行数据有索引idx_a (a)查询条件索引是否生效原因WHERE a 1❌ 通常失效数据量太小优化器认为全表扫描更快避坑指南小表不需要加索引数据量小于5000行的表通常不需要加索引全表扫描足够快不要纠结小表的索引失效这是优化器的正常选择不影响性能用大表测试索引如果要验证索引是否生效用数据量大的表测试。场景9MySQL 8.0的函数索引没正确创建MySQL 8.0.13支持函数索引但如果函数索引没正确创建或者查询条件和函数索引不匹配索引也会失效。失效示例假设我们创建了函数索引idx_year_create_time ((YEAR(create_time)))查询条件索引是否生效原因WHERE YEAR(create_time) 2026✅ 生效查询条件和函数索引匹配WHERE create_time 2026-01-01❌ 失效查询条件是范围查询和函数索引不匹配WHERE MONTH(create_time) 3❌ 失效查询条件是MONTH()和函数索引的YEAR()不匹配避坑指南函数索引只能用于特定的函数查询创建的是YEAR()的函数索引就只能用YEAR()查询优先用普通索引范围查询函数索引的通用性不如普通索引用EXPLAIN验证如果key是NULL且查询条件和函数索引不匹配就是索引失效了。场景10统计信息过期优化器选错执行计划MySQL的优化器是基于统计信息来选择执行计划的。如果表的数据变化很大比如大量插入、删除但统计信息没更新优化器会基于错误的统计信息选错执行计划导致索引失效。核心原理MySQL的统计信息包括表的行数、索引的区分度、页的数量等。如果统计信息过期优化器会错误地估计走索引和全表扫描的成本从而选错执行计划。失效示例假设表原本有100行数据后来插入了100万行数据但统计信息没更新优化器认为表只有100行选择全表扫描但实际上表有100万行全表扫描极慢应该走索引。解决方法手动更新表的统计信息-- 更新表的统计信息ANALYZETABLEtest_table;-- 或者用OPTIMIZE TABLE同时会整理碎片OPTIMIZETABLEtest_table;避坑指南定期更新统计信息如果表的数据变化很大比如每天插入/删除超过10%的数据定期执行ANALYZE TABLE大促前更新统计信息大促前给核心表更新统计信息避免优化器选错执行计划用EXPLAIN验证如果执行计划明显不对比如大表全表扫描考虑更新统计信息。三、如何验证索引是否生效验证索引是否生效唯一的方法是用EXPLAIN查看执行计划重点看这4个字段EXPLAIN字段含义优化目标type访问类型至少要到range最好到ref/eq_ref/const绝对避免ALLkey实际用到的索引必须有值且是预期的索引rows预计扫描的行数越少越好最好和实际返回行数接近Extra额外信息尽量有Using index覆盖索引避免Using filesort、Using temporary四、避坑指南避免索引失效的5个核心习惯严格遵守最左前缀原则联合索引的列顺序匹配业务高频查询顺序不要跳过中间列永远保持索引列“干净”不要在索引列上用函数、表达式、隐式类型转换LIKE查询尽量用前缀匹配必须用中间/后缀匹配时用全文索引或ESOR两边的列都要有索引否则整个查询的索引都会失效优先用UNION ALL替代OR不要给区分度太低的列加索引小表也不需要加索引定期更新统计信息。五、总结MySQL索引失效的本质要么是索引的有序性被破坏违反最左前缀、用函数/表达式、隐式类型转换、LIKE通配符在开头要么是优化器认为走索引的成本更高负向查询、区分度太低、数据量太小、统计信息过期。记住索引不是银弹加了索引不代表一定会用必须结合业务场景设计合理的索引并用EXPLAIN验证索引是否生效才能真正发挥索引的价值。