为什么LIKE以%开头索引会失效?
首先看看B+树是如何查找数据的:
查找数据时,MySQL会从根节点开始,按照从左到右的顺序比较查询条件和节点中的键值。如果查询条件小于节点中的键值,则跳到该节点的左子节点继续查找;如果查询条件大于节点中的键值,则跳到该节点的右子节点继续查找;如果查询条件等于节点中的键值,则继续查找该节点的下一个节点。
比如说我有下面这条SQL:
select * from `user` where nickname like '%冥';
如果数据库中存在南冥
北冥
西冥
东冥
,那么在B+树中搜索的效率和全表扫描还有什么区别呢?
我走聚簇索引全表扫描还不用回表。
最后在扩展讲一个点,其实不一定会导致索引失效。举个例子:
create table `user`(
id int primary key auto_increment,
name varchar(20),
index idx_name(name),
);
// 那么这种情况是会走索引的。
select id,name from `user` where name like '%冥';
为什么说上面的例子会走索引呢?
首先我们需要查询的id
name
这两个字段是不是都在我们的辅助索引中,叶子节点是不是存的索引值和主键值,所以我们只要查辅助索引就可以直接拿到我们的需要的结果了,那么这个叫做索引覆盖。我们观察执行计划会发现它的查询级别是index
,其实也是全表遍历了辅助索引。
第二个问题来了,那为什么就要走辅助索引而不是走全表扫描呢?
因为辅助索引中记录的东西比主键索引少了很多,只有索引值和主键值,但是主键索引中就包含了,其他值、事物ID、MVCC的回流指针等等。再加上索引覆盖不用回表,优化器就认为直接遍历辅助索引的效率高于主键索引。
![](/images/780-200-2.jpg)