索引
目录
索引
索引失效的情况
第一类：对索引列做了“手脚”（破坏原始值）
这是最隐蔽也最常见的情况。只要对索引列进行了函数运算、类型转换或数学计算，索引就会失效。
- 使用函数包裹：
WHERE DATE(create_time) = '2026-07-30'（失效） 应改为WHERE create_time >= '2026-07-30' AND create_time < '2026-07-31'（生效）。 - 隐式类型转换：
WHERE phone = 13800138000（假设phone是varchar类型，失效）。数据库会将索引列phone转为数字进行比较，破坏了原始值。应改为WHERE phone = '13800138000'。 - 进行数学运算：
WHERE age + 1 = 30（失效）。应改为WHERE age = 29。
第二类：违反最左前缀原则（针对联合索引）
联合索引 (a, b, c) 相当于按照 a 排序，a相同再按b排序，b相同再按c排序。如果跳过了左边的列，右边的列就是无序的，无法走索引。
- 跳过第一列：
WHERE b = 1 AND c = 2（失效，因为b和c在全局是无序的）。 - 范围查询阻断：
WHERE a = 1 AND b > 2 AND c = 3（失效，c失效）。在联合索引中，范围查询（>、<、between）会停止匹配，c无法使用索引。建议将c的条件放在前面，或者修改业务逻辑。 - 模糊匹配左模糊：
WHERE name LIKE '%三'（失效）。因为B+树从左往右匹配，不知道开头是什么就无法比较。WHERE name LIKE '张%'（生效）。
第三类：优化器“嫌弃”索引（全表扫描更快）
当数据库优化器（CBO）计算后发现，需要回表查出的数据量太大，此时走索引需要频繁随机I/O，还不如直接扫全表顺序I/O来得快。
- 查询返回数据量过大：如果表中
status字段90%都是1，查询WHERE status = 1，优化器大概率会走全表扫描（失效）。 - 使用
!=、<>或NOT IN：这些操作符意味着要扫描几乎所有的数据，优化器通常认为不走索引更划算（失效）。 IS NOT NULL：同理，非空值太多时失效。但IS NULL通常有效（除非空值极少）。
第四类：索引列本身的问题
- 索引列参与
OR条件：SELECT * FROM t WHERE a = 1 OR b = 2。如果a和b不是同一个索引，且没有同时覆盖，数据库为了合并结果集可能放弃索引。解决办法是分别建两个索引，或者用UNION代替OR。 - 查询
\*且索引无法覆盖：如果SELECT *需要的字段不在索引中，回表成本太高，优化器可能不走索引而走全表扫描。解决办法是使用覆盖索引（只查询索引中包含的字段）。
怎么验证是否失效？
不要靠猜，要看执行计划。
- MySQL：在SQL前加
EXPLAIN，重点看key列（用了哪个索引）和Extra列（如果出现Using filesort或Using temporary，说明索引失效严重）。- 基本语法：
EXPLAIN SELECT * FROM table_name WHERE condition;
- 基本语法：
- 验证技巧：可以用
FORCE INDEX(index_name)强制走索引，然后对比执行时间。如果强制走索引比默认的全表扫描还慢，就印证了优化器的选择是正确的（第三条情况）。- 基本语法：
SELECT * FROM table_name FORCE INDEX (index_name) WHERE condition;
- 基本语法：
为什么失效
第一类：对索引列做“手脚”（为什么失效？）
场景：WHERE DATE(create_time) = '2026-07-30' 或 WHERE age + 1 = 30
- 失效原理（破坏有序性）：
B+树磁盘上存的是原始值，比如2026-07-30 15:30:00。当你用函数DATE()包裹后，数据库拿到索引里的原始值，必须先把原始值计算成日期，才能去和右边的'2026-07-30'比较。
这就导致比较的顺序变了。原本索引是按2026-07-30 15:30:00排序的，但你要按2026-07-30（只取日期部分）去查找。这两个顺序完全不同。数据库没办法在有序的索引树上直接二分查找，只能把整棵索引树的所有叶子节点都遍历一遍，计算每一个值的函数结果。这就叫全索引扫描，等价于失效。- 总结：索引是年月日 时分秒排序的，现在用
DATE()把排序依据变成了年月日，排序规则变了，所以用不了索引。
- 总结：索引是年月日 时分秒排序的，现在用
- 隐式类型转换（为什么失效？）：
WHERE phone = 13800138000（phone是varchar）。
MySQL的规则是：在比较中，如果一边是数字，一边是字符串，会把字符串隐式转为数字。
这相当于执行了WHERE CAST(phone AS SIGNED) = 13800138000。虽然你没写函数，但数据库偷偷在索引列上套了CAST函数，再次破坏原始值，导致失效。
第二类：违反最左前缀原则（为什么失效？）
场景：联合索引 (a, b, c)，查询 WHERE b = 1 AND c = 2（跳过a）。
- 失效原理（破坏有序性）：
联合索引的排序规则是：先按a排，a相同再按b排，b相同再按c排。
这就好比一本电话簿，先按“姓氏”排，同姓再按“名字”排。- 如果查询
WHERE a='张' AND b='三'，你可以直接翻到“张”姓下的“三”字，很快。 - 如果查询
WHERE b='三'（不带姓），请问怎么找？因为在电话簿里，所有叫“三”的人（王三、李三、张三）是散落在全书的各个角落的，完全没有按“三”字排序。此时数据库只能从头到尾翻遍整本电话簿，索引完全失效。
- 如果查询
- 范围查询阻断（为什么阻断？）：
查询WHERE a=1 AND b>2 AND c=3。a=1：能精确定位到a=1的这一小段数据。- 在这一小段里，数据是按
b排好序的，所以b>2也能利用顺序，快速定位到b=2的边界。 - 关键点来了：当你找到
b>2的范围后，这个范围内的数据，虽然a都是1，但c是乱序的！因为排序优先级是b优先于c，只要b不同，c的大小就毫无规律。所以c=3无法利用索引，只能在b>2的结果集里一个个遍历。这就是“范围查询右侧的列失效”。
第三类：优化器“嫌弃”索引（为什么失效？）
场景：WHERE status = 1（表中90%的数据status都是1）。
- 失效原理（破坏树形查找的效率）：
这里不是“不能查”，而是“查了比不查更慢”。
走二级索引（辅助索引）查数据，需要回表（先查索引树得到主键ID，再拿着ID去查聚簇索引树得到整行数据）。这是两次B+树查找，属于随机I/O。
如果命中的数据量太大（比如90万行），就意味着要做90万次随机I/O回表。而全表扫描是沿着聚簇索引的叶子节点，从头到尾顺序读磁盘，属于顺序I/O（磁盘顺序读极快）。
优化器（CBO）会计算成本。当它发现要读的数据占总行数的比例超过大约20%~30%（临界值）时，就会认为回表成本远高于全表扫描，于是毅然放弃索引，选择全表扫描。这不是索引坏了，而是“走索引不划算”。 !=和NOT IN为什么失效？：
这两个条件意味着要查找“除了某些值以外的几乎所有数据”。既然要查几乎所有数据，优化器算账后发现：反正你几乎要把整张表都翻出来，那我直接顺序读全表，比跳来跳去回表快得多。
第四类：索引列本身的问题（为什么失效？）
场景：SELECT * FROM t WHERE a = 1 OR b = 2（a和b分别有单列索引，但不是同一个联合索引）。
- 失效原理（破坏树形查找的路径）：
索引是一棵独立的树。a=1需要走a这棵树，b=2需要走b这棵树。
如果是AND（与），数据库可以走a索引筛出一部分，再回表过滤，没问题。
但如果是OR（或），意味着最终结果集 = 走a树的结果 ∪ 走b树的结果。数据库为了合并这两个结果集，需要分别从两棵树取数据，还要做去重排序。
在MySQL老版本中，优化器觉得这太复杂了，不如直接全表扫描，然后一行行判断a=1 OR b=2。这就导致了索引失效。（注：MySQL 5.6后引入了Index Merge优化，某些情况下可以用索引，但局限性很大且不稳定，一般不建议依赖）。 - 查询
\*且索引无法覆盖（为什么？）：
假设有一个联合索引(a, b)，查询是SELECT * FROM t WHERE a=1 AND b=2。- 虽然
a和b能走索引快速定位到叶子节点。 - 但叶子节点里只存了
a和b的值，以及主键ID。并不包含*（其他字段如c、d）。 - 所以，即使通过索引找到了100个主键ID，依然要回表100次去拿
c、d。 - 如果表很大，这100次回表是随机I/O。如果表很小，优化器可能想：我直接扫全表（顺序I/O）也就读200行，何必多此一举回表100次？于是放弃索引，直接全表扫描。
- 虽然
总结
索引失效的本质，不是索引文件坏了，而是因为“输入的条件”无法利用B+树“排好序”的特性进行二分查找。要么是条件把原始值改了（函数/类型），导致无法使用索引；要么是条件查的数据太多（OR/>/大量数据），导致随机回表比顺序扫全表更慢。
- “输入的条件”无法利用B+树“排好序”的特性进行二分查找
- 条件把原始值改了（函数/类型），导致无法使用索引
- 条件查的数据太多（
OR/>/大量数据），导致随机回表比顺序扫全表更慢
浙公网安备 33010602011771号