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

其实，早期关注我的朋友都知道，我写了一个 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 条查询的执行计划都是如下所示：

根据我前面的这篇文章《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 查询最左匹配，%在前的情况是否走索引的问题，要分情况回答，不要直接说走索引，也不要直接说不走索引！

： » Like 查询，%百分号在前 ‘%xttblog’ 到底走不走索引？