模糊查询下（like）如何使用覆盖索引优化

灰太狼 2022-05-11 06:14 315阅读 0赞

在MySQL中，like ‘string%’可以用到索引，但是like ‘%string%’却会全表扫描。这个是常识，但真的就没有解决方法了吗？有的，要使用到索引覆盖。

### 什么叫覆盖索引？ ###

*  解释一： 就是**select的数据列只用从索引中就能够取得**，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
 *  解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就**不需要再到数据表中读取行**了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
 *  解释三：是**非聚集组合索引**的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句\[select子句\]与查询条件\[Where子句\]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

总之，不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

### 小试牛刀 ###

CREATE TABLE a1  
(  
    id INT AUTO\_INCREMENT PRIMARY KEY,  
    column\_name VARCHAR(20),  
    column\_type VARCHAR(20)  
);  
CREATE INDEX idx\_a1\_column\_name ON a1(column\_name);

![70][]

语句1：它没有使用到索引（Extra：using where），意味着全表扫描，理论如此，

语句2：它使用了索引范围查找（type=range）（key=idx\_a1\_column\_name）,但是它使用索引方式为二级检索（Extra：Using index condition）还是会有一定的性能消耗的，也有解决办法：针对select的列创建联合索引

语句3：它使用了索引覆盖（Extra：Using index）

结论：

*  using index ：使用覆盖索引的时候就会出现
 *  using where：在查找使用索引的情况下，需要回表去查询所需的数据
 *  using index condition：查找使用了索引，但是需要回表查询数据
 *  using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

有实验证明using index & using where要优于using index condition。

### 小知识点（原理） ###

1.啥是回表？

上面讲过如果索引的列在select子句中就不需要回表，索引列也是表，如果索引中包括你需要的列，查询结果就找到了，如果没有你需要的列，索引列中有指针**指向表记录的行位置，从表中查询列值**。

2.二级索引篇

聚簇索引：每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键 时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

二级索引：又称辅助索引、非聚集索引(no-clustered index)。b＋tree树结构，然而二级索引的叶子节点不保存记录中的所有列，其叶子节点保存的是<健值，(记录)地址>，非叶子节点存放的记录格式为<键值，主键值，地址>。而聚集索引叶子节点保存保存记录中的所有列，非叶子节点保存的是下一层节点地址。

记录的地址一般可以保存两种形式：

1. 记录的物理地址，页号：槽号：偏移量   
2 . 记录的主键值

innodb引擎是索引组织表，所有记录都放在聚集索引里，因此其辅助索引中的记录地址存放的主键的键值。

**读操作**：

通过二级索引查询记录仅能得到主键值，要查询完整的记录还需要再通过一次聚集索引查询,这种查询方式为书签查找（bookmark lookup）。

**写操作：**

仅当主键发生改变时，才会更新二级索引。

[70]: /images/20220511/cd47885f509a498e8998cdaa67396ae0.png