MySql索引优化中索引失效和如何避免索引失效

一：索引

MySql官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构，可以得到索引的本质：索引是数据结构。

你可以简单理解为"排好序的快速查找的数据结构"，因此，当我们建立了索引之后，我们应该如何避免索引失效呢？

二：如何避免索引失效

1：最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引的列

表的结构大致是这个样子

这是建立的索引(索引字段顺序，name，age，pos)：

A：当想要通过年龄和位置去查询时：

发现type是ALL，查询效果最差的，并且没有用到索引

B：当想要通过位置去查询时：

发现type是ALL，查询效果最差的，并且没有用到索引，和上述的结果一样

C：当想要通过姓名去查询时

发现了type是ref，并且用到了索引，key_len是74，这是为什么呢？

正如我们上面所介绍的，索引建立以后，应该从最左开始，建立的字段不能丢失。比如火车不能没有火车头

D：再举一个例子

我们查询了索引字段中的第一个和第三个，发现key_len是74，按理来说查询的精度越高应该key_len就越长，并没有将索引全部用到。因此不能跳过索引中的列。

2：不在索引列上做任何操作(计算，函数，(自动or手动)类型转换)，会导致索引失效而转向全表扫描

从上面可以看出下面用到了函数查询，从左开始取四位，两次的查询结果一致，但是用了Explain之后发现一个type是ref，一个是ALL，上面的用到了索引，下面用到了Mysql函数后取截取name，导致索引失效。也印证了上面的话。

3：存储引擎不能使用索引中范围条件右边的列

如果索引都用到了的话，key_len应该是140，而此时是78，表示只用到了两个字段，范围查找导致后面的索引失效

4：like以通配符开头('%abc/...')mysql索引失效会变成全表扫描的操作

5：尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*

6：mysql在使用不等式(!=或者<>)的时候无法使用索引而导致全表扫描

可以看出用到了不等式，从表中可以看出key是null，并没有用到索引

7：is,null，is not null也无法使用索引

理论上用到索引，实际上并没有用到

8：like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

但在右边写%的话

如果想要使用%%的话如何建立索引呢？

我们建立覆盖索引在name，age字段上，id是主键

当我们查询的字段在我们的索引的字段里的话，就会用到索引

当查找全部的时候，查找的字段超过了索引的范围就会让索引失效，所以如何建立索引要从实际情况考虑。

9：字符串不加单引号索引失效

10：少用or，用它连接时会索引失效