MYSQL索引失效的几种情况

  MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提MySQL的检索速度。但是，有时候常常会明明建了索引，查询结果却没有使用到索引的情况。那么就说明索引失效。以下就列举出几种索引失效的情况：
  在举例之前，先简单的介绍一下MySQL中的explain，explain这个命令可以查看SQL语句的执行计划，结果如图所示：

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息
   因此，要查看索引是否失效，只需要看possible_keys以及key属性就可以了，其他属性暂不赘述。

索引失效的几种情况：

本文以此表gift_exchange_record作为例子，以下是当前表的索引：

1、索引列中使用了运算或者函数

错误情况1，使用了数学运算：

错误情况2，使用了函数运算：

正确情况：

2、or语句中前后字段没有同时都为索引

错误情况：

正确情况：

3、数据类型出现隐式转化， 例如字符串比较没有使用单引号

错误情况：

正确情况：

4、like语句以%开头

错误情况：
正确情况：

5、索引字段中使用is null 、is not null 、！=、<>

6、复合索引中没有遵循最佳左前缀原则

最佳左前缀原则即要使复合索引生效，必须要先有左边的字段，再使用右边的字段时才会生效，直接使用右边的字段索引不会生效。

正确情况1：只使用左边字段
正确情况2：左右字段都使用
错误情况：只使用了右边字段

7、查询结果大于全表的30%