什么情况下索引会失效

一、索引创建的原则

1、针对数据量较大，且查询比较频繁的表建立索引。

单表超过10万数据，即可增加索引

2、使用经常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

3、尽量选择区分度高的字段作为索引，尽量建立唯一索引，区分度越高，使用的索引越高效。

例如下图中的address，区分度太小就不适合作为索引

4、尽量使用联合索引（多个字段），减少单列索引，查询的时候，联合索引很多时候可以用到“覆盖索引”，节省存储空间，避免“回表查询”，提高查询效率

5、索引也不是越多越好，尽量控制索引的数量

索引越多，维护索引结构的代价越大，在增删改的时候效率也越低

6、如果要建立索引的列不能存储null值，请在创建表时，使用not null约束，可以增加查询效率

二、什么情况下索引会失效

首先讲一个我碰到的真实案例：

我们公司系统有一张大表，有80多个字段（按照规范其实不应该有这么多字段，历史遗留问题，后面在数据库国产化的时候，准备优化），每天500万-2000万之间的订单数据；原来是使用日期作为分区键，再走主键索引查询的；

结果有一次代码改动，这张表又加了几个字段，而且在其中一条SQL的select 后加上了几个字段，oracle的索引就既不走分区键，也不走主键索引了，通过查看执行计划发现，是执行到了另外几个字段的普通联合唯一索引上，而我们除了select后加了字段以外，where条件没有改动。

出现的现象：应用更新上去后，立马开始告警，数据库连接超时，告警数据库连接池满。新进来的请求无法下单，大面积异常。决定立马执行回退操作；

回退完成（业务正常）后，开始分析原因：除了加了几个字段以外，select 后加了几个字段要查询出来以外，也没有增加改动索引，也没有where条件的任何改动；后面分析执行计划发现索引走偏了，走到另一个普通索引上了

原因分析为：

Oracle优化器会根据查询的代价（Cost）来选择执行计划。当查询中涉及的字段或数据量发生变化时，优化器可能会重新评估使用不同索引的代价。例如：

• 新添加的字段可能使得查询结果集的大小或数据分布发生了变化，导致优化器认为使用其他索引更高效。

• 如果查询涉及的字段在新索引中能够覆盖查询所需的所有列（即覆盖索引），优化器可能会优先选择该索引

当然，在我碰到的这次案例中，oracle优化器，显然是帮了倒忙，选择了错误的索引，导致查询时间超长，占用连接池

解决方案：联系DBA人员，强行绑定执行的索引，使用分区键+主键，问题解决

后续又碰到一次类似的问题，但是我们预料到可能出现这样的情况，提前联系了DBA人员支撑，一出现这个问题，就立马绑定索引；

索引失效的条件：

1、违反最左前缀原则

即：当使用一个联合索引（索引了多列）时，查询条件中，，要从索引的最左前列开始，并且不跳过索引中的列，匹配最左前缀法则，走索引

例如：tb_seller表有一个联合索引，是name、status、address三个字段的联合索引；

上述两条SQL执行，没有带上name字段，所以我们可以看到执行计划中，key和key_len都没有表现出这条SQL走了索引，索引失效

2、范围查询右边的列，不能使用索引

即，如果一个联合索引，某个字段使用了范围查询，那么这个字段后面的索引都不能生效

例如：还是上面这个表，第一条SQL使用了正确的联合索引，执行计划的key显示使用了索引tb_seller_index，索引长度key_len显示612，表示用到了name、status、address三个字段的索引，这三个字段的长度，加起来一共是612

而第二条SQL的条件中，status约束了查询范围“>1”，导致address这个字段的索引没有走到，key_len的长度就只有309了，只使用到了name和status

3、不要在索引列上进行运算操作，索引会失效

即：如果对有索引的列，使用运算操作，那么索引可能会失效

如图，还是这张表，组合索引是（name、status、address）

图中的SQL的执行计划中，key为null，说明没有走到索引，导致索引失效，这样查询会很慢

4、字符串不加单引号，造成索引失效

在查询时，如果没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效

上图中的SQL，第一条正确的SQL的执行计划，能够看到key_len是309，是使用了name、status两个字段的索引；

而第二条SQL的执行计划中，key_len是303，仅仅使用了name这个字段上的索引，造成了status上的索引失效；

原因是：第二条SQL条件中字符串类型的字段status后面的=跟的是0，没有带字符串，MySQL就认为是数字类型，而要与status相匹配，则要转换成字符串类型 ；而进行的类型转换，导致了索引失效

5、以%开头的like模糊查询，索引失效。

但是如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

如图中，第一条，第二条SQL中，组合索引统统失效，第三条中，%在末尾，并没有导致索引失效