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

欢迎关注作者微信公众号：编程大椰子

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版) 》。