模糊搜索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.  简述原因

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：成本计算当然不止这些内容，还有一套公式，本次不赘述。

3.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.  小结

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

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

3.  比hive快10倍的大数据查询利器-- presto

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注