Like 查询,%百分号在前 ‘%xttblog’ 到底走不走索引?

SQL herman 507浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

其实,早期关注我的朋友都知道,我写了一个 MySQL 的 in 查询到底走不走索引。今天我们再来看看 MySQL 的 Like 查询 % 百分号在前的情况是否会走索引。

在面试过程中,我们一般都会说 like 走索引有一个原则,那就是最左匹配原则。那么当 % 号放在最前面的情况,就一定不走索引吗?

我们通过一个例子来看看是否是这样的情况。示例 SQL 我一次性贴出来,如下所示:

select version(); -- 5.6.41-log

create table xttblog_like (
	ID int primary key,
	test_like VARCHAR(20) NOT NULL DEFAULT 0,
	index test_like(test_like)
)engine=InnoDB;

insert into xttblog_like values(1,'公众号:业余草'),
	(2,'微信号:xttblog'),
	(3,'感谢转发!'),
	(4,'MySQL like'),
	(5,'MySQL in');

select * from xttblog_like;

EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE 'y'; -- 1
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE 'y%'; -- 2
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE '%y'; -- 3
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE '%y%'; -- 4
EXPLAIN SELECT * FROM xttblog_like WHERE test_like not LIKE '%y%'; -- 5

注意,我的版本是 MySQL 5.6 。上面 1 到 5 条查询的执行计划都是如下所示:

Like 查询走不走索引

根据我前面的这篇文章《MySQL 性能优化神器 Explain 使用教程》我们知道,上面的查询都是用到了索引。但是注意第 3 条查询中,possible_keys 为 null。possible_keys 代表的是此次查询中可能选用的索引,那就说明将 % 放在前面的 like 查询虽然 type 和 extra 显示的都好像用了索引,但其实 possible_keys 显示的结果告诉我们并没有使用索引。为什么会这样呢?

那么我们现在就可以下一个结论,在 MySQL 5.6 版本中,Like 查询,在覆盖索引的情况下,不管有没有%,以及%在前还是在后,还是前后都有,都会走索引。 not like 同理。

但是你也别高兴的太早。“用索引” 和 “用索引快速定位记录”是有区别的。“用索引”有一种用法是 “顺序扫描索引”。

Like ‘y’ 或 ‘y%’ 可以使用索引,并且快速定位记录。like ‘%y’ 或 ‘%y%’,只是在二级索引树上遍历查找记录,并不能快速定位(扫描了整棵索引树)。只有 id 和 test_like 字段时,上述 4 种 like 查询,test_like 索引能满足 id 和 test_like 的查询情况,不需要回表,所以选择了使用 test_like 的索引树解决问题。如果新添加了 xtt 字段,但无联合索引 (test_like, xtt) 的情况,如果使用 test_like 索引树,需要回表。在 like ‘%y’ 或 ‘%y%’ 直接扫描主键索引树,现象就是没有使用 test_like 索引。添加了 xtt 字段,也添加了 (test_like, xtt) 索引,使用覆盖索引就能满足 select * 的字段查询,不需要回表,因此使用了 (test_like, xtt) 索引树。但是只有 like ‘y’ 和 ‘y%’ 能快速定位记录,而 like ‘%y’ 和 ‘%y%’ 也能使用该索引树,但是不能快速定位,需要顺序遍历。

其次,index 类型表示”和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。index 表示性能并不是特别优越,system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说,得保证查询至少达到 range 级别,最好能达到 range 级别。

最后,我们在来看一下非覆盖索引的情况下。至于什么是非覆盖索引?就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。为了演示这种情况,我们改进一下测试SQL。

select version(); -- 5.6.41-log

drop TABLE xttblog_like;

create table xttblog_like (
	ID int primary key,
	test_like VARCHAR(20) NOT NULL DEFAULT 0,
	mysql_like VARCHAR(20) NOT NULL DEFAULT 0,
	other_like VARCHAR(20) NOT NULL DEFAULT 0,
	index test_like(test_like)
)engine=InnoDB;

insert into xttblog_like values(1,'公众号:业余草','极客时间返现','极客时间返现'),
	(2,'微信号:xttblog','微信号:xttblog','极客时间返现'),
	(3,'感谢转发!','感谢转发!','感谢转发!'),
	(4,'MySQL like','极客时间返现','极客时间返现'),
	(5,'MySQL in','极客时间返现','极客时间返现');

select * from xttblog_like;

EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE 'y'; -- 1
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE 'y%'; -- 2
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE '%y'; -- 3
EXPLAIN SELECT * FROM xttblog_like WHERE test_like LIKE '%y%'; -- 4
EXPLAIN SELECT * FROM xttblog_like WHERE test_like not LIKE '%y%'; -- 5
EXPLAIN SELECT id,test_like FROM xttblog_like WHERE test_like LIKE 'y%'; -- 6

测试结果,大家可以自己运行一下看看。我直接说结论:在非覆盖索引的情况下,like 查询中,%只要在前,就不会走索引!所以,大家以后在遇到 MySQL like 查询最左匹配,%在前的情况是否走索引的问题,要分情况回答,不要直接说走索引,也不要直接说不走索引!

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847(已满),QQ3群:187424846(已满)。QQ群进群密码:xttblog,想加微信群的朋友,之前的微信号好友已满,请加博主新的微信号:xttblog,备注:“xttblog”,添加博主微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » Like 查询,%百分号在前 ‘%xttblog’ 到底走不走索引?