本文基于https://cloud.tencent.com/developer/article/2383928文章学习后个人自己再补充说明自己的理解。
DBA:like '%a%'肯定走不了索引的。。。(这句话不完全正确也不完全对)。
在MySQL数据库使用规范或优化建议中都明确说类似 like '%a%'的写法不走索引。那么,真的是在任何条件下这种写法都不能走索引么?
先举例代码:创建表并插入数据。Id为自增主键,C1字段上建辅助索引
CREATE TABLE test_tb1(
id INT PRIMARY KEY
c1 VARCHAR(10),
c2 VARCHAR(20),
KEY idx_c1(c1)
);
INSERT INTO test_tb1
VALUES
(1,'abc','dwdwdwd'),
(2,'cadw','kklll'),
(3,'rtyu','093jx'),
(4,'sfgh','pl;,efdsf'),
(5,'l,mi','45223sda'),
(6,'rty',',ngykmb'),
(7,'mju','wedffd'),
(8,'tyuo','yuxx'),
(9,'oiuyr','qwert'),
(10,'ytuion','wwwwww');
执行如下语句:由执行计划可知,该模糊查询走全表扫描未走任何索引
这里select * 包含字段id,c1,c2三个字段,id是主键聚簇索引,c1上有建辅助索引即b+tree索引(与主键聚簇索引不同,其叶子结点存储的是主键值,而非整行记录。Mysql索引原理可自己查找理解。)查找时先找到叶子节点主键值,然后再通过主键值查找整行记录,再从行记录中查找到c2字段。这种过程叫做索引回表。所以语句执行计划走索引会失效。未发生索引覆盖。
利用辅助索引拿到辅助索引对应字段的值,不需要从聚集索引中拿整行数据的操作叫覆盖索引
EXPLAIN SELECT * FROM test_tb1 WHERE c1 LIKE '%a%';
将表1语句修改为如下:由mysql执行计划可知,该语句查询走索引查询。如想让其走索引查询修改语句如下,这里查询列只拿了id,c1字段。发生索引覆盖,不需要去拿字段c3。
EXPLAIN SELECT id,c1 FROM test_tb1 WHERE c1 LIKE '%a%';
附注:这里索引key_len长度较大原因是由于表数据较小导致,表数据越大索引长度越低查找越快越好。
为方便理解:
创建一个新表并插入数据,id为自增长主键,c1字段建辅助索引。这个同上修改之后的语句。把表字段删除c2,避免索引回表。
# 创建表
CREATE TABLE test_tb7(
id INT PRIMARY KEY
c1 VARCHAR(10),
KEY idx_c1(c1)
);
# 插入数据
INSERT INTO test_tb7
VALUES
(1,'abc'),
(2,'cadw'),
(3,'rtyu'),
(4,'sfgh'),
(5,'l,mi'),
(6,'rty'),
(7,'mju'),
(8,'tyuo'),
(9,'oiuyr'),
(10,'ytuion');
执行如下语句: 由mysql执行计划可知,该语句走索引。
EXPLAIN SELECT * FROM test_tb7 WHERE c1 LIKE '%a%';
总结:所以说like '%a%'肯定走不了索引的,说这句话不完全正确也不完全对。当查询结果包含非索引字段时不会走索引,当查询结果只包含索引字段or主键时即覆盖索引时会走索引。