mysql覆盖索引详解——like模糊全匹配中使用索引

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql覆盖索引详解——like模糊全匹配中使用索引

什么是覆盖索引?


解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。


解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引

(即select和where条件中的字段都出现在索引中,即为覆盖索引)


解释三:是非聚集组合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)。

(这里补充下,如果select中包含主键id,也是可以走覆盖索引的,因为非聚簇索引默认就包含主键id,不用回表查询)


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


这里还补充几个非常重要的概念:

要说回表查询,先要从InnoDB的索引实现说起。InnoDB有两大类索引,一类是聚集索引(Clustered Index),一类是普通索引(Secondary Index)。


InnoDB的聚集索引


InnoDB聚集索引的叶子节点存储行记录,因此InnoDB必须要有且只有一个聚集索引。

1.如果表定义了PK(Primary Key,主键),那么PK就是聚集索引。

2.如果表没有定义PK,则第一个NOT NULL UNIQUE的列就是聚集索引。

3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。

(一般聚集索引默认就是主键上面的索引)

这种机制使得基于PK的查询速度非常快,因为直接定位的行记录。


二级索引:又称普遍索引,辅助索引、非聚集索引(no-clustered index),非主键索引。

b+tree树结构,然而二级索引的叶子节点不保存记录中的所有列,其叶子节点保存的是<健值,(记录)地址>,非叶子节点存放的记录格式为<键值,主键值,地址>。而聚集索引叶子节点保存保存记录中的所有列,非叶子节点保存的是下一层节点地址。


例如:

有个t表(id PK, name KEY, sex, flag),这里的id是聚集索引,name则是普通索引。

10.png

聚集索引的B+树索引(id是PK,叶子节点存储行记录):

11.png


普通索引的B+树索引(name是KEY,叶子节点存储PK值,即id):

12.png


普通索引因为无法直接定位行记录,其查询过程在通常情况下是需要扫描两遍索引树的。


select * from t where name = 'lisi';


这里的执行过程是这样的:

13.png

粉红色的路径需要扫描两遍索引树,第一遍先通过普通索引定位到主键值id=5,然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询,即先定位主键值,再根据主键值定位行记录,性能相对于只扫描一遍聚集索引树的性能要低一些。


索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引(可以是单列索引,也可以一个索引语句定义所有要查询的列,即联合索引),这样的话就可以直接返回索引中的的数据,不需要再通过聚集索引去定位行记录,避免了回表的情况发生。


只扫描索引而无需回表的优点:

1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。

2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。

3.一些存储引擎如myisam在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用

4.innodb的聚簇索引,覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询)


覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B-tree索引做覆盖索引。


当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息

14.png


覆盖索引的坑:mysql查询优化器会在执行查询前判断是否有一个索引能进行覆盖,假设索引覆盖了where条件中的字段,但不是整个查询涉及的字段,mysql5.5和之前的版本也会回表获取数据行,尽管并不需要这一行且最终会被过滤掉。

15.png

如上图则无法使用覆盖查询,原因:

1.没有任何索引能够覆盖这个索引。因为查询从表中选择了所有的列,而没有任何索引覆盖了所有的列。

2.mysql不能在索引中执行LIke操作。mysql能在索引中做最左前缀匹配的like比较,但是如果是通配符开头的like查询,存储引擎就无法做比较匹配。这种情况下mysql只能提取数据行的值而不是索引值来做比较


优化后SQL:添加索引(artist,title,prod_id),使用了延迟关联(延迟了对列的访问)

16.png

说明:在查询的第一阶段可以使用覆盖索引,在from子句中的子查询找到匹配的prod_id,然后根据prod_id值在外层查询匹配获取需要的所有值。


5.5时API设计不允许mysql将过滤条件传到存储引擎层(是把数据从存储引擎拉到服务器层,在根据条件过滤),5.6之后由于ICP这个特性改善了查询执行方式


采用执行计划分析覆盖索引:

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


17.png


语句1:它没有使用到索引(Extra:using where),意味着全表扫描,理论如此。(因为索引没覆盖到select中的列)


语句2:它使用了索引范围查找(type=range)(key=idx_a1_column_name),但是它使用**索引方式为二级检索(Extra:Using index condition)**还是会有一定的性能消耗的,也有解决办法:针对select的列创建联合索引。


查询走了索引idx_a1_column_name,但是还需要根据二级索引检测出的结果中的主键id去进行回表查询。


语句3:虽然是全匹配模糊查询,但是使用了索引覆盖(Extra:Using index)

因为普通索引中已经包含了主键id的值,不需要再进行回表查询。

所以性能比(Extra:Using index condition)的快。


结论:

using index :使用覆盖索引的时候就会出现

using where:未使用索引,需要全表查询

using index condition:查找使用了索引,但是需要回表查询数据

using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据。


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


不得不说,这篇文章刷新了对mysql中索引使用的认知,特别是通过延迟关联(延迟了对列的访问)使用覆盖索引的方式,堪称经典,很值得学习。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
172 66
|
8天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
62 9
|
2月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
2月前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
196 0
案例剖析:MySQL唯一索引并发插入导致死锁!
|
12天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
52 18
|
5天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
31 8
|
11天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
18 7
|
10天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
40 5
|
14天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
74 7
|
1月前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
31 2

推荐镜像

更多