关于mysql:其实-MySQL-中的-like-关键字也能用索引

上篇文章中，松哥和大家分享了索引的两个应用规定：

索引上不要应用函数运算。
应用笼罩索引防止回表。

当然，凡事有个度，用哪一种策略也要联合具体的我的项目来定，不能为了 SQL 优化而摈弃了业务。

明天，松哥在前文的根底上，再来和大家分享一条索引规定，一起来学习下。

咱们常说，MySQL 中的 like 要慎用，因为会全表扫描，这是一件可怕的事！不过呢，也看状况，有的 like 其实也能用索引：有的时候 like 用索引效率很高，有的时候 like 尽管用了索引效率却低的可怕。

咱们一起来剖析下。

我还是举个例子吧，假如我有如下一张表：

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

username 和 age 组成了复合索引，复合索引名为 username，下文提到的 username 索引都是指该复合索引。

依据上篇文章（是时候检查一下应用索引的姿态是否正确了！）的解说，咱们晓得，对于如下 SQL：

select username,age from user2 where username='javaboy' and age=99;

这个 SQL 在查问的过程中，会用到笼罩索引，防止回表，进步查问效率。

那么当初问题来了，如果我单纯的只是想通过 username 字段查问用户呢，是否须要为 username 字段独自建设一个索引？

咱们来看如下一条 SQL：

select username,age from user2 where username='javaboy';

因为我的表中没有为 username 字段建设的索引，那么它会不会应用已有的复合索引呢？咱们来看下执行打算：

能够看到，这里其实用到了 username 复合索引，通过 Extra 字段的值还能看到应用到了笼罩索引。

为啥会这样呢？在 B+Tree 这种索引构造中，能够利用索引的“最左匹配”来定位记录。最左匹配既能够是匹配复合索引中的前几个字段，也能够是匹配第一个字段的前几个字符，在下面的案例中，咱们匹配的是复合索引中的第一个字段。

当然咱们也能够匹配第一个字段的前几个字符，如下：

select username,age from user2 where username like 'j%';

执行打算如下：

从这执行打算中首先能够确认这个查问也用到了 username 复合索引。

不过这里的查问打算和后面的不太一样，两条 SQL 的区别在于一个是等于号一个是含糊匹配，查问打算的次要区别在于 type 和 Extra：

后面的 type 为 ref 示意通过索引查找数据，个别呈现等值匹配的时候，type 会为 ref；前面这个 type 为 range 示意这是一个索引的范畴扫描（因为是含糊匹配，而含糊匹配能够造成扫描区间）。
后面的 ref 为 const 示意与索引列进行等值匹配的是一个常量。
后面的 Extra 为 Using index 示意应用到了笼罩索引；前面的 Extra 为 Using where;Using index，示意用到了索引，然而还须要进行过滤。

对于第一点中加粗的字体，我再来和大家多说两句。为什么说含糊匹配就能造成扫描区间呢？因为咱们是依照 username 和 age 建设的复合索引，username 在前 age 在后，具体存的时候，是依照 username 排序存储，如果 username 雷同，则依照 age 排序存储，构造相似上面这样：

username	age
a	88
b	89
c	89
c	90
c	99
d	88
d	99

所以当想要搜寻以 j 结尾的 username 时，只须要定位到第一个以 j 结尾的 username，而后利用 B+Tree 叶子结点之间的双向链表持续向后读取，读到第一个不是以 j 结尾的 username 时截止，这就是扫描区间。

大家看到了，在下面的执行打算中，like 'j%' 其实也用到了索引，那么如果是 like '%j' 或者 like '%j%' 会用到索引吗？咱们来看一个例子：

咦！看执行打算仿佛也用上索引了！难道只有字段上有索引，like 就能用索引？

当然不是！

大家来看松哥上面这个辅助案例，看懂了就明确了。

为了让大家更好的了解下面所说的最左匹配，松哥再来举一个例子。

还是下面的表和数据结构，然而当初如果我想依照 age 来做查问，SQL 如下：

select username from user2 where age=99;
select username from user2 where age>99;

我举了两个查问的例子，大家一起来看下这两条 SQL 的执行打算，其实没啥差别：

这个查问打算咱们该如何解释呢？其实这两个查问打算没啥区别，我就解释一个吧。

首先大家想一下，username 和 age 建设的是复合索引，username 在前 age 在后，具体在 B+Tree 中存储的时候，首先是依照 username 排序的，当 username 雷同的时候，再依照 age 来排序，所以这个复合索引最终存储的后果就是，username 是有序的，而 age 是无序的，再来回顾下这个表格：

username	age
a	88
b	89
c	89
c	90
c	99
d	88
d	99

username 是有序的，而 age 是无序的。

了解了这个，咱们再来看这个执行打算就好懂了。

当咱们依照 age 去搜寻的时候，因为 age 在 username 索引中是无序的，所以只能遍历 username 索引，而执行打算中的 type 为 index，恰好就示意须要扫描全副的索引记录。以第一条查问 SQL 为例，扫描全副的索引记录，而后过滤出 age 等于 99 的记录（过滤这一步是在 server 层实现的），rows 示意预估的扫描行数，从最初的 Extra 的 Using where;Using index 也能看出这一点，即用到了索引，然而也对数据进行了过滤。

回顾第一大节的最初 like '%j' 和 like '%j%' 的执行打算，不就是下面这个吗！

在后面这两条 SQL 中，因为咱们查问的 username 字段曾经存在于 username 索引中了，所以能够通过笼罩索引机制间接从索引中获取想要的数据并返回，不须要回表操作了。

如果大家不懂 笼罩索引 戳这里：是时候检查一下应用索引的姿态是否正确了！。
如果大家不懂回表戳这里：什么是 MySQL 的“回表”？。

然而如果咱们查问的字段不仅仅是索引中的字段，例如如下 SQL：

select * from user2 where age=99;

查问的是所有字段，那么此时就没有必要应用索引了，为啥？且听松哥细细道来。

咱们来个反证：假如当初还是应用 username 复合索引，那么就须要把 username 索引整个读一遍，而后过滤出满足条件的数据，因为索引中没有保留 address 字段的值，所以还须要回表操作，再去主键索引中找到对应的记录。。。这一路操作下来太麻烦了，光 B+Tree 都读了两棵（而且第一颗 B+Tree 还是遍历），那咱们还不如间接遍历主键索引呢！主键索引里要啥有啥，遍历完了想要的数据都有了，遍历主键索引其实就是咱们常说的全表扫描。

小伙伴们认真推敲下松哥下面这段话。

下面是咱们的剖析，接下来咱们来看看执行打算：

能够看到，如咱们所想。

type 为 All 就是咱们所相熟的全表扫描（其实就是遍历主键索引），rows 是预估扫描的行数。最初的 Extra 为 Using where 示意 MySQL 首先从数据表（存储引擎）中读取记录，返回给 MySQL 的 server 层，而后在 server 层过滤掉不满足条件的记录。

好啦，通过这样两个小案例，松哥和大家分享了 MySQL 索引中的最左匹配准则，也心愿小伙伴们可能藉此了解索引的存储构造。

关于mysql:其实-MySQL-中的-like-关键字也能用索引

1. 最左匹配准则

2. 辅助案例

3. 小结