当前位置：首页 > 文章列表 > 数据库 > MySQL > 其实 MySQL 中的 like 关键字也能用索引！

其实 MySQL 中的 like 关键字也能用索引！

来源：SegmentFault 2023-01-22 21:41:32 0浏览收藏

在IT行业这个发展更新速度很快的行业，只有不停止的学习，才不会被行业所淘汰。如果你是数据库学习者，那么本文《其实 MySQL 中的 like 关键字也能用索引！》就很适合你！本篇内容主要包括其实 MySQL 中的 like 关键字也能用索引！，希望对大家的知识积累有所帮助，助力实战开发！

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

索引上不要使用函数运算。
使用覆盖索引避免回表。

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

今天，松哥在前文的基础上，再来和大家分享一条索引规则，一起来学习下。

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

我们一起来分析下。

1. 最左匹配原则

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

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 为
```
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 的
```
select * from user2 where age=99;
```
查询的是所有字段，那么此时就没有必要使用索引了，为啥？且听松哥细细道来。
我们来个反证：假设现在还是使用 username 复合索引，那么就需要把 username 索引整个读一遍，然后过滤出满足条件的数据，由于索引中没有保存 address 字段的值，所以还需要回表操作，再去主键索引中找到对应的记录。。。这一路操作下来太麻烦了，光 B+Tree 都读了两棵（而且第一颗 B+Tree 还是遍历），那我们还不如直接遍历主键索引呢！主键索引里要啥有啥，遍历完了想要的数据都有了，遍历主键索引其实就是我们常说的全表扫描。
小伙伴们仔细琢磨下松哥上面这段话。
上面是我们的分析，接下来我们来看看执行计划：
可以看到，如我们所想。
type 为 All 就是我们所熟悉的全表扫描（其实就是遍历主键索引），rows 是预估扫描的行数。最后的 Extra 为
```
Using where
```
表示 MySQL 首先从数据表（存储引擎）中读取记录，返回给 MySQL 的 server 层，然后在 server 层过滤掉不满足条件的记录。
3. 小结
好啦，通过这样两个小案例，松哥和大家分享了 MySQL 索引中的最左匹配原则，也希望小伙伴们能够藉此理解索引的存储结构。
以上就是《其实 MySQL 中的 like 关键字也能用索引！》的详细内容，更多关于mysql的资料请关注golang学习网公众号！