写点什么

一文搞懂 MySQL 中 like 的索引情况

作者:止水
  • 2022 年 6 月 22 日
  • 本文字数:1292 字

    阅读完需:约 4 分钟

一文搞懂 MySQL 中 like 的索引情况

准备数据

表结构:

CREATE TABLE `user` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `create_time` datetime DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
复制代码


表数据:

like %在前面

先看下面两条 SQL 语句

select * from user where name like '%张%';
select * from user where name like '%张';
复制代码


这两条 SQL 语句是同一类型,大家应该都知道,它们是不走索引的。

为什么不走索引

最左匹配原则。B+树底层存储是有顺序的,%在左侧说明我们查找的内容前缀不确定,不确定的话就只能一个一个的查找比较,这就等于扫描了整个辅助索引树,这还不算完,因为要查询所有的字段,所以中间还要回表去主键索引上查询。这么复杂还不如直接全表扫描呢。


再来看一条 SQL 语句

select id, name from user where name like '%张'; 
复制代码



我们只查找了id,name字段发现可以走索引了,Why?

为什么又走索引了

这里走的是覆盖索引,虽然还是扫描了整个辅助索引,但是辅助索引中包含了所有查询字段,不需要再次回表查询了。(扫描辅助索引比全表扫描效率高。)

like %在后面

针对 like的优化通常是将%放在后面,这样就可以通过索引查找数据提高查询效率。


问题来了,这样就肯定可以用上索引吗?

select * from user where name like '张%';select * from user where name like '周%'; 
复制代码


思考一下🤔 🤔 🤔



很明显like 张%没有走索引,而 like 周%走了索引,同样的%都放后面为什么有的走了索引,有的没走呢。

为什么不走索引

首先说%like后面大部分场景下是走索引的,但是也有例外,在这个例子中,以开头的数据离散度较低,优化器判断通过辅助索引idx_name查询成本可能大于全表扫描,就会直接走全表扫描。


所以我们创建的索引字段离散度要尽可能高,否则可能会出现不走索引的情况。

强制走索引

如果感觉优化器判断错误,可以强制使用索引:


select * from user force index(idx_name) where name like '张%';
复制代码


后话

写完 SQL 语句,一定要看看它的执行计划,看看是否和我们预想的一样。


在某些情况下,当执行 EXPLAIN 命令进行 SQL 语句的分析时,会发现优化器并没有选择索引去查找数据,而是通过扫描聚集索引,也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN 链接操作等情况下。

例如:SELECT * FROM orderdetails WHERE orderid>10000 and orderid<102000;

这是为什么呢?原因在于用户要选取的数据是整行信息,而 OrderID 索引不能覆盖到我们要查询的信息,因此在对 OrderID 索引查询到指定数据后,还需要一次书签访问来查找整行数据的信息。虽然 OrderID 索引中数据是顺序存放的,但是再一次进行书签查找的数据则是无序的,因此变为了磁盘上的离散读操作。如果要求访问的数据量很小,则优化器还是会选择辅助索引,但是当访问的数据占整个表中数据的蛮大一部分时(一般是 20%左右),优化器会选择通过聚集索引来查找数据。因为之前已经提到过,顺序读要远远快于离散读。

摘录来自: 姜承尧. 《MySQL 技术内幕:InnoDB 存储引擎(第 2 版) 》。

发布于: 刚刚阅读数: 3
用户头像

止水

关注

博观而约取,厚积而薄发 2018.11.06 加入

记录工作技术。如果对文章有异议,或者有不同观点,请留言告知呦 ❣️

评论

发布
暂无评论
一文搞懂 MySQL 中 like 的索引情况_MySQL 数据库_止水_InfoQ写作社区