MySQL索引失效详解

被索引字段，发生隐式类型转换

例如以下表结构

CREATE TABLE consumers(
    `consumerId` varchar(11) NOT NULL PRIMARY KEY COMMENT '用户Id',
    `consumerAge` int(10) NOT NULL COMMENT '用户年龄',
    `consumerName` varchar(120) NOT NULL COMMENT '用户名'
     KEY `idx_age` (`consumerAge`),
)ENGINE = InnoDB default charset = utf8mb4 comment '用户表';

可以看出consumerId是一个主键索引是一个字符类型的数据如果在查询是给consumerId字段赋值为一个数值类型，这时MySQL会将SQL语句中与原数据类型不匹配的值隐式转换成匹配的数据类型（如果可以的话）。

select * from consumers where consumerId = 18521436711;

这时这条SQL查询语句就不会使用索引，可以用EXPLAIN来进行分析一下。

EXPLAIN select * from consumers where consumerId = 18521436711\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where

EXPLAIN 字段详解
- id:选择标识符
- select_type:表示查询的类型
- table:输出结果集的表
- partitions:匹配的分区
- type:表示表的连接类型
- possible_keys:表示查询时，可能使用的索引
- key:表示实际使用的索引
- key_len:索引字段的长度
- ref:列与索引的比较
- rows:扫描出的行数(估算的行数)
- filtered:按表条件过滤的行百分比
- Extra:执行情况的描述和说明

由上面的EXPLAIN的分析结果可知，在执行SQL时MySQL的SQL分析器分析时预测会使用主键索引，但是实际执行时并没有使用，而使用了FULL SCAN全表扫描。

正确使用索引

select * from consumers where consumerId = '18521436711';

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 46
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

被索引字段使用了表达式计算

还是上面那个表结构这时我们要查询年龄大于18又刚好满2年的人使用到的SQL如下

select * from consumers where consumerAge - 2 = 18;

语句是可以正常执行的可以查询出结果但是EXPLAIN分析后的结果如下

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where

由以上分析可以看出这个查询也是没有使用到索引，而是使用了FULL SCAN全表扫描。这时应该对SQL语句进行调整如下

select * from consumers where consumerAge = 18 + 2;

select * from consumers where consumerAge = 20;

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ref
possible_keys: idx_age
          key: idx_age
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

被索引字段使用了函数

还是上面那个表结构查询consumerId为185开头时

select * from consumers where left(consumerId,3) = '185';

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where

由上面分析可以看出key为NULL说明在实际查询时并没有使用到主键索引。这时将SQL语句调整为

select * from consumers where consumerId LIKE '185%';

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 46
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where

这时在观察EXPLAIN分析Key值是PRIMARY说明在查询过程中使用到了主键索引。

小结

被索引字段，发生隐式类型转换
被索引字段使用了表达式计算
被索引字段使用了函数

以上这三种情况都会导致索引的失效。

为什么

索引的使用依赖于BTree索引树的遍历而索引树的遍历依赖于BTree底层叶子结点的有序性当发生以上这三种情况后，有可能这个字段新的排列顺序和原来索引树叶子结点层的排序顺序就不一样了。索引树叶子结点的有序性就会被破坏，当执行SQL时MySQL的执行器无法判断原来的索引树是否还能被检索使用，就会导致执行器不去使用索引而使用全表扫描。

最左匹配原则

还是上面的那个表结构，现在有一个需求是查询以6711结尾的consumerId那么你可能会毫不犹豫的写出下面这条SQL语句

select * from consumers where consumerId LIKE '%6711'

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where

你会发现这条语句没有使用索引然后你又试了试

select * from consumers where consumerId LIKE '%6711%'

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where

发现还是没有使用索引然后你又进行了修改

select * from consumers where consumerId LIKE '6711%'

EXPLAIN分析

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 46
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

你会发现这样居然可以使用上索引，这是因为MySQL中的索引树搜索也是遵循最左匹配原则，BTree索引树的叶子结点的有序性也是建立在最左匹配的基础上的，如果使用索引键的中部或者后部进行SQL查询由于违背了最左匹配原则，所以MySQL在执行SQL时无法利用索引树进行搜索所以索引会失效。当然还有以下情况。当一个表中有联合索引**(a,b,c)时使用的索引时应使用(a),(a,b),(a,b,c)如果使用(b),(c),(b,c)时是不会使用到索引这可能是所有网上的说法**但是官方文档 8.3.6 Multiple-Column Indexes中确实也是这么举例的。于是我建了一张测试表

表结构

create table test(
    `id` int(10) NOT NULL PRIMARY KEY ,
    `a` int(10) NOT NULL ,
    `b` int(10) NOT NULL ,
    `c` int(10) not null ,
    key idx_a_b_c(a,b,c)
)ENGINE = InnoDB

表数据

+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | 2 | 3 | 4 |
|  2 | 3 | 4 | 5 |
|  3 | 4 | 5 | 6 |
|  4 | 5 | 6 | 7 |
+----+---+---+---+

开始分析

explain select * from test where a = 2\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_a_b_c
          key: idx_a_b_c
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

explain select * from test where a = 2 and b = 3\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_a_b_c
          key: idx_a_b_c
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index

explain select * from test where a = 2 and b = 3 and c = 4\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_a_b_c
          key: idx_a_b_c
      key_len: 12
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index

以上这三条可以看到key都是idx_a_b_c说明执行时使用了索引。接下来看看不会执行索引的。

explain select * from test where c = 6\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: idx_a_b_c
          key: idx_a_b_c
      key_len: 12
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using index

😺 这怎么也走索引了

但是查询的explain结果中显示用到索引的情况类型是不一样的。可观察explain结果中的type字段。你的查询中分别是：

type: index
type: ref

解释

index:这种类型表示是MySQL会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，MySQL都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，MySQL会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

type :显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

关于联合索引最左匹配原则其实网上和官方文档一样，但是当你实验的时候却是另一种结果关于最左匹配原则请参考：

Growth Log

MySQL索引失效详解

被索引字段，发生隐式类型转换

被索引字段使用了表达式计算

被索引字段使用了函数

小结

最左匹配原则

解释

二分查找算法

MySQL的InnoDB引擎