MySQL数据库索引优化之索引失效的几种情况（详解+代码展示）

一、索引是什么

    根据官方文档所定义：索引（Index）是帮助MySQL高效获取数据的数据结构，简单点理解，就是好比去图书馆在成千上万的书中找一本书，图书管理员告诉你在哪个楼层哪个房间的哪个柜子，这就是索引，为了让你更方便快捷的找到自己的数据，在计算机中，可以提高数据检索的效率，减低数据库的IO成本，降低数据排序的成本，降低CPU的消耗，也就是，排好序的快速查找数据结构。然而当我们在MySQL的日常使用索引中，有很多种情况会使索引失效，下面为大家详解，希望大家避免此类的索引。

二、MySQL索引分类

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 复合索引：即一个索引包含多个列

三、MySQL索引结构

1. BTree索引
2. Hash索引
3. full-text全文索引
4. R-Tree索引

四、MySQL中创建索引的方法

1. create index 索引名 on 表名(列名)
2. alter 表名 add index 索引名 on (列名)

五、MySQL中索引的几种级别（性能由好到坏）

1. NULL：不用访问表或者索引，直接就能得到结果。
2. coust、system：单表中最多有一个匹配行，查询速度很快。
3. eq_ref：唯一性索引扫描。
4. ref：非唯一性索引。
5. range：扫描部分索引，索引范围扫描。
6. index：扫描全部索引树
7. ALL：全表扫描

六、MySQL正确使用索引示例

1. 表数据
   
2. 索引列：id，deptName，locAdd
3. 由下图可以看出，explain分析后，我们使用到了我们我们创建的id_dept索引(红色部分)，索引长度为63(蓝色部分)，列与索引使用常数类型比较(黄色部分)，接下来逐一说明索引失效的情况。
   

七、MySQL索引失效的几种情况（应该避免）

1. 最佳左前缀法则，如果索引了多列， 就要从索引的最左端开始且不跳过索引列。如不遵守，则索引失效。
   如下图可以看到，在建立了多列索引的前提下(id，deptName，locAdd)，在查找时却跳过了之前的索引列，也就好比这个团队中没有大哥了，所以索引失效，使用的全表扫描（ALL）。
   
2. 不要在索引列上做任何操作（如计算，函数，类型转换），会导致索引失效。
   还是刚才的表，这次我查询id，但是做了加法运算，由于id为索引列，所以导致索引失效，使用了全表扫描（ALL），同样，不能在索引列上使用任何计算，函数，类型转换。
   
3. 存储引擎不能使用索引中范围条件右边的列，会导致索引失效。
   如下图所示，查询时在where语句后的locAdd列使用了大于号，也就是查询索引列中大于11的值，也就是索引中范围条件右边的列，导致索引失效，使用了全表扫描（ALL），所以要避免这种情况。
   
4. 在使用（!=或者<或者>）的时候会导致索引失效。
   如下图所示，在查询时，在索引列上使用了!=（不等于），导致索引失效，使用了全表扫描（ALL），同样要避免这种情况。
   
5. is null、is not null也无法使用索引。
   由下图可见，当我们在索引列LocAdd上使用了is not null，同样索引失效，使用了全表扫描（ALL），切记避免。
   
6. like以通配符开头（%xxx）同样导致索引失效。
   如下图所示，在查询中，索引列（deptName）使用like，但是使用了通配符%放在了前面，同样导致索引失效，使用了全表扫描（ALL），同样避免。
   
7. 当全表扫描或者主键扫描优于索引扫描时索引失效。
   可见下图，key字段中使用的primary，也就是我们的主键扫描，可见在这种情况下索引也失效。
   
8. 当使用or时前后没有同时使用索引导致索引失效。
   由下图可知，explain分析可能使用到我们的索引，但是实际并没有使用，还是使用的全表扫描（ALL），就是因为我们使用的or前后索引不一致，所以避免在使用索引的时候使用or。
   

八、MySQL使用索引的建议

1. 尽量使用全值匹配。
2. 尽量使用覆盖索引。
3. 对于单值索引，尽量选择针对当前查询过滤性更好的索引。
4. 在选择组合索引的时候，当前查询语句中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
5. 在选择组合索引的时候，尽量选择可以能够包含当前查询语句中的where字句中更多字段的索引。
6. 尽可能通过分析统计信息和调整查询语句的写法来达到选择合适索引的目的。

九、总结

    固然使用在MySQL中使用索引会带给我们更多的方便，但是如果我们的查询语句出现上述情况，我们的索引反而并没有起到作用，所以在日常工作和学习中要尽量避免这些种情况，提高我们的工作效率。