模糊搜索c1 like '%a%'真的都不能走索引么

某DBA：like '%a%'肯定走不了索引的。。。

在MySQL数据库使用规范或优化建议中都明确说类似 like '%a%'的写法不走索引。那么，真的是在任何条件下这种写法都不能走索引么？

1、不走索引的情况

创建一个测试表并插入测试数据

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');

进行测试 c1 LIKE '%a%'的写法是否走索引。

EXPLAIN SELECT * FROM test_tb1 WHERE c1 LIKE '%a%';

结果如下：

从执行计划来看，符合我们一贯的认知。

2、走索引的情况

重新创建一个表，并插入数据

# 创建表
CREATE TABLE test_tb2(
id INT PRIMARY KEY ,
c1 VARCHAR(10),
KEY idx_c1(c1)
);
# 插入数据
INSERT INTO  test_tb2
VALUES
(1,'abc'),
(2,'cadw'),
(3,'rtyu'),
(4,'sfgh'),
(5,'l,mi'),
(6,'rty'),
(7,'mju'),
(8,'tyuo'),
(9,'oiuyr'),
(10,'ytuion');

此时使用上述相同的SQL来看一下执行计划。

EXPLAIN SELECT * FROM test_tb2 WHERE c1 LIKE '%a%';

此时结果与之前不同了，可以走索引了。

3、简述原因

（1）索引内容

上述2例中的差别在于test_tb1比test_tb2多了一个c2字段，这导致在进行c1 like '%a%'查询时，一级索引（主键索引）primary key 及二级索引（辅助索引）idx_c1的执行代价不同。

在MySQL中，主键索引存储的是主键字段及对应的整条记录的数据，即所有的数据都是按照主键进行排序组织在主键索引上的。而二级索引存储的数据是按照对应的字段排序后的数据，包含索引字段+主键字段。

以上两例中，一级索引与二级索引的内容如下：

例1

例2：

如果例1中使用c1索引，则过程是，先在c1索引上进行整个索引的扫描，然后找到主键字段，因为找到的内容还缺少c2的值，因此需要再回到主键索引上进行检索，拿到所有字段的内容，这个代价相对较高

而例2中，扫描c1索引后，便得到了所有需要返回的值，而不需要再回主键索引上取其他内容（因为c1索引上已经有主键字段），因此可以选择走c1索引。

PS：成本计算当然不止这些内容，还有一套公式，本次不赘述。

（2）例1的改写

通过上面的对比，如果想例1中也走索引，可以只查询c1字段或c1及主键id字段，此时也是可以走索引的，例如：

EXPLAIN SELECT id,c1 FROM test_tb1 WHERE c1 LIKE '%a%';

EXPLAIN SELECT c1 FROM test_tb1 WHERE c1 LIKE '%a%';

EXPLAIN SELECT id FROM test_tb1 WHERE c1 LIKE '%a%';

PS：全模糊搜索还有其他的方式解决，另外也可以使用ES等来解决。

4、小结

在数据库学习的过程中，不可只记结论而忽视其原理。另外还有很多所谓的规范都是需要区别对待的，你知道的还有什么需要区别处理的数据库规范么？

原文地址：https://mp.weixin.qq.com/s/LZrJjIFq7Wbxc8DaTvzjug