mysql like走索引吗 mysql like什么时候走索引

转载

网络安全专家 2023-10-23 14:15:46

文章标签 mysql like走索引吗数据搜索 mysql 文章分类 MySQL 数据库

文章目录

1. 问题的引入

1.1 验证

1.1.1 案例1 like ‘%测试%’
1.1.2 案例2 like ‘测试%’
1.1.3 案例3 like ‘测试1%’

1.2 总结

2. 离散性对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，说明全表扫描没有使用索引：

mysql like走索引吗 mysql like什么时候走索引_搜索

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检索出所有数据，说明全表扫描没有使用索引：

mysql like走索引吗 mysql like什么时候走索引_搜索_02

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检索出一条数据，说明使用了索引：

mysql like走索引吗 mysql like什么时候走索引_mysql like走索引吗_03

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 查询其实很多人也是有误解的！

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