金三银四精选java面试题-为什么LIKE以%开头索引会失效？

为什么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的回流指针等等。再加上索引覆盖不用回表，优化器就认为直接遍历辅助索引的效率高于主键索引。