模糊搜索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. 简述原因
上述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：成本计算当然不止这些内容，还有一套公式，本次不赘述。
通过上面的对比，如果想例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. 小结
在数据库学习的过程中，不可只记结论而忽视其原理。另外还有很多所谓的规范都是需要区别对待的，你知道的还有什么需要区别处理的数据库规范么？
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
扫码关注