为什么Mysql 在覆盖索引下的like ‘%xxx%’查询能使用到索引，原理是什么？

Question

为什么Mysql 在覆盖索引下的like ‘%xxx%’查询能使用到索引，原理是什么？

下面是在mysql5.7的一个示例：

第一步准备数据

CREATE TABLE `Student` (
  `ID` int(11) NOT NULL primary key AUTO_INCREMENT,
  `Name` varchar(4) NOT NULL DEFAULT '' COMMENT '姓名',
  `Age` int(5) NOT NULL DEFAULT '0' COMMENT '年龄',
  `Email` varchar(10) NOT NULL DEFAULT '' COMMENT '邮箱'
  
) ENGINE=InnoDB ;

insert into Student(name,age,email) values ("aa",11,"[e-mail]"),("bb",22,"[e-mail]"),("cc",22,"[e-mail]")

第二步查询分析,未加索引。

explain select name,age from Student where name like "%b%"

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1    SIMPLE    Student    NULL    ALL    NULL    NULL    NULL    NULL    3    33.33    Using where; Using filesort

文件排序，全局扫描。

第三步创建复合索引（name，age）

alter table Student add INDEX index_nameAge (name,age)

第四步查询分析，覆盖索引。

explain select name,age from Student where name like "%b%"

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1    SIMPLE    Student    NULL    index    NULL    index_nameAge    18    NULL    3    33.33    Using where; Using index

使用到了index_nameAge索引，且使用到了覆盖索引。

第五步查询分析，非覆盖索引。

explain select name,age,email from Student where name like "%b%"

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  Student NULL    ALL    NULL    NULL    NULL    NULL    3    33.33    Using where

没有使用到索引，全局扫描。

Answer 1

MySQL 默认索引的结构是 B+ 树。

比如你创建了 name,age 的覆盖索引，那么 MySQL 会为你创建一棵 B+ 树。可以认为每一个节点都是一个 key-value 结构，key 是覆盖索引 (name,age) 的哈希值，并且这棵树是以 name,age 进行排序的，value 是主键的值，也就是 id。

select name,age from Student where name like "%b%"

这条 SQL 为什么可以走索引呢？一般我们认为like "b%"这种形式才能走索引，而like "%b%"是不会走索引的。

但是请注意你查询的是 name,age 这两个字段，在覆盖索引的这棵 B+ 数上只需要进行 like 的匹配，或者是基于覆盖索引再进行 where 的查询就可以获得结果。

正如解释的结果中 Extra 字段为 using where。

而为什么查询的列再加上 email 字段就不走索引了？

select name,age,email from Student where name like "%b%"

这是因为要回表。回表就是使用普通索引获得主键，再使用主键回到主键索引树搜索的过程。

假设上面这条 SQL 走了覆盖索引，那么首先在覆盖索引树上根据 name 和 age 查找到 id， 然后根据 id 到主键索引上找到 email 的值，然后再进行 like 的匹配。这与直接进行全表扫描使用 like 匹配相比更耗时，所以在这里是扫描全表，也就是没有走索引。

以上。

Answer 2

能写下具体例子吗？

Answer 3

我猜的：你的索引包含了like的那个字段，所以xxx like xxx可以只需要索引就能完成，而查询引擎认为先扫描索引把符合条件的行号筛选出来再慢慢去取完整的行数据会更快一点，因为读索引比读所有的完整数据的I/O少。

Answer 4

能explain 一下你的sql语句看看嘛，记得%xxx应该不会走索引， xxx是不是就是刚好是索引中的字段而不是字段的一部分？