【mysql 5.7】like ‘XX%‘一定会使用索引吗 & 离散性对like的影响(索引选择性)

1. 问题的引入

在非覆盖索引场景下，大家知道Mysql索引有最左原则，所以通过 like '%XX%'查询的时候一定会造成索引失效（5.7版本覆盖索引可以走索引），一般采用like 'XX%'右边匹配的方式来索引。

但是这样一定会使用索引吗？
答案是否定的，不一定会用。

附：在覆盖索引下， like '%XX%'和like 'XX%'均走索引

1.1 验证

创建实验用的SQL：

CREATE TABLE `user` (
  `ID_` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `AGE_` INT(11) DEFAULT NULL,
  `NAME_` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  UNIQUE KEY `NAME_` (`NAME_`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

#插入三条数据
INSERT INTO `user` VALUES ('1', '1', '测试1.1'), ('2', '2', '测试2.2'), ('3', '3', '测试3');

注意：下面3个例子是基于非覆盖索引场景下

1.1.1 案例1 like ‘%测试%’

EXPLAIN select * from user where NAME_ like '%测试';

EXPLAIN结果如下：type=ALL，key=null，说明全表扫描没有使用索引：

    id  select_type  table   partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  ------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       user    (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       3     33.33  Using where  

1.1.2 案例2 like ‘测试%’

EXPLAIN select * from user where NAME_ like '测试%';

EXPLAIN结果如下：type=ALL，key=null，rows=3检索出所有数据，说明全表扫描没有使用索引：

    id  select_type  table   partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  ------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       user    (NULL)      ALL     NAME_          (NULL)  (NULL)   (NULL)       3    100.00  Using where  

1.1.3 案例3 like ‘测试1%’

EXPLAIN select * from user where NAME_ like '测试1%';

EXPLAIN结果如下：type=range，key=NAME_，rows=1检索出一条数据，说明使用了索引：

    id  select_type  table   partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra                  
------  -----------  ------  ----------  ------  -------------  ------  -------  ------  ------  --------  -----------------------
     1  SIMPLE       user    (NULL)      range   NAME_          NAME_   1023     (NULL)       1    100.00  Using index condition  

1.2 总结

从上面三个案例可以看出，like ‘xxxx%’ 时不一定100%使用索引，是否使用索引与该条件数量和数据总量比例有关

那么这是为什么呢？其实就是离散性的问题

2. 离散性对like的影响

非覆盖索引场景下，WHERE 条件中，like ‘xttblog%’， like ‘%xttblog%’， like ‘%xttblog’，三种方式查询方式，后两种方式对于索引是无效的，有索引也不会走索引。第一种 ‘xttblog%’ 是不确定的，决定于列的离散型，理论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。所以，关于 like 查询其实很多人也是有误解的！

离散型的好坏，决定着优化器是否走索引。

《mysql高性能5》5.3.2前缀索引和索引选择性章节里面提到的索引选择性其实就是离散性的不同表述，离散性越高，选择性就越高，可以让mysql在查找时过滤掉更多的行

你看我这个例子，status 字段的索引离散型非常的差，如果此时搜索 status = 1 的数据，根节点判断的时候，结果是查询左子树，但是当在左子树第二层再进行判断的时候，因为左右分支都满足条件，所以很难抉择选择哪一个分支继续搜索，或者是把两个分支同时进行搜索。

如果是范围查询还好一点，因为所有的叶子节点都是有顺序的。我从最左边开始，一直遍历到不符合条件的第一条数据为止，把数据返回。这是 B+ 树的一个特点，有序性更强！
但是呢？由于离散型非常的差，优化器可能直接就选择不走索引了，因为优化器可能认为，走索引和全表扫描差不多。

所以，我们再创建索引时，一定要选择重复值较低的字段。

离散型有一个计算公式：count(distinct col):count(col)，离散型越高，选择型越好。

参考：

《面试问题之Mysql like索引失效》 问题的引出

《从根上理解为什么说索引的离散型越高越好？》 问题的根因