MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。 _生活百科

索引失效

介绍

索引失效就是我们明明在查询时的条件为索引列（包括自己新建的索引），但是索引不能起效，走的是全表扫描。explain 后可查看type=ALL 。
这是为什么呢？
首先介绍有以下几种情况索引会出现失效：

当我们使用了左模糊匹配和左右模糊匹配的时候，像like ‘%str’或者‘%str%’ 。
当我们使用联合索引没有遵守最左匹配原则的时候。
当我们使用索引时对其索引字段进行计算、函数、类型转换的操作。
当我们在where条件子句中使用了OR运算，同时OR前为索引列，OR后的条件不是索引列。以上几种情况均会引起索引失效。

下面我们来谈谈具体的原因和其中的细节

1、第一种情况：左模糊匹配和左右模糊匹配我们都知道在mysql中innodb存储引擎会对我们的索引以B+树存储。
InnoDB 存储引擎根据索引类型不同，分为聚簇索引（主键索引）和二级索引。它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。
主键索引一般来说叶子结点存储的都是数据本身。二级索引一般来说叶子结点存储的都是数据的物理地址。
因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
下面看看转载：
来自：[小林Code][https://mp.weixin.qq.com/s/lEx6iRRP3MbwJ82Xwp675w]

文章插图
索引存储结构长什么样？我们先来看看索引存储结构长什么样？因为只有知道索引的存储结构，才能更好的理解索引失效的问题。
索引的存储结构跟 MySQL 使用哪种存储引擎有关，因为存储引擎就是负责将数据持久化在磁盘中，而不同的存储引擎采用的索引数据结构也会不相同。
MySQL 默认的存储引擎是 InnoDB，它采用 B+Tree 作为索引的数据结构，至于为什么选择B+ 树作为索引的数据结构，详细的分析可以看我这篇文章：为什么 MySQL 喜欢 B+ 树？
在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。
MySQL 的 MyISAM 存储引擎支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时，创建的主键索引默认使用的是 B+ 树索引。
虽然，InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

接下来，我举个例子，给大家展示下这两种存储引擎的索引存储结构的区别。
这里有一张 t_user 表，其中 id 字段为主键索引，其他都是普通字段。

文章插图
如果使用的是 MyISAM 存储引擎，B+ 树索引的叶子节点保存数据的物理地址，即用户数据的指针，如下图：

文章插图
如果使用的是 InnoDB 存储引擎， B+ 树索引的叶子节点保存数据本身，如下图所示：

文章插图
InnoDB 存储引擎根据索引类型不同，分为聚簇索引（上图就是聚簇索引）和二级索引。它们区别在于，聚簇索引的叶子节点存放的是实际数据，所有完整的用户数据都存放在聚簇索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。
如果将 name 字段设置为普通索引，那么这个二级索引长下图这样，叶子节点仅存放主键值。

文章插图
知道了 InnoDB 存储引擎的聚簇索引和二级索引的存储结构后，接下来举几个查询语句，说下查询过程是怎么选择用哪个索引类型的。
在我们使用「主键索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么就会在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后直接读取要查询的数据。如下面这条语句：
// id 字段为主键索引select * from t_user where id=1;在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么需要检索两颗B+树：