模糊搜索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数据备份及还原（二）

扫码关注