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

最新推荐文章于 2025-02-26 11:30:45 发布

布道

最新推荐文章于 2025-02-26 11:30:45 发布

阅读量8.3k

点赞数 5

分类专栏： oracle|sqlserver|mysql 文章标签： mysql InnoDB

本文链接：https://blog.csdn.net/alex_xfboy/article/details/82789942

版权

oracle|sqlserver|mysql 专栏收录该内容

29 篇文章

订阅专栏

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

语句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）。

写操作：

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