MySQL的优化利器⭐️Multi Range Read与Covering Index是如何优化回表的?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 本文以小白的视角使用通俗易懂的流程图深入浅出分析Multi Range Read与Covering Index是如何优化回表

前言

上篇文章MySQL的优化利器:索引条件下推,千万数据下性能提升273%🚀,我们说到MySQL中server层与存储引擎层的交互、索引、回表、ICP等知识(有不理解的概念可以看上篇文章哈~)

上篇文章末尾我们提出一个问题:有没有什么办法可以尽量避免回表或让回表的开销变小呢?

本篇文章围绕这个问题提出解决方案,一起来看看MySQL是如何优化的

回表

为什么会发生回表?

因为使用的索引并没有整条记录的所有信息,因此使用索引后不满足查询列表需要的列,就要回表查询聚簇索引

image.png

回表查询聚簇索引时,由于主键值是乱序的这样就会导致随机IO

什么是随机IO呢?

MySQL查询时,需要将磁盘的数据加载到缓冲池中,与磁盘交互的单位是页,页中存在多条记录

由于获取的是聚簇索引的页,那么该页中的主键值是有序的,但在二级索引上的记录主键值可能并不是有序的

image.png

比如图中第一条记录主键值为24记录在页A中,第二条记录主键值为82546记录在页C中

当遍历到第一条记录时需要去加载页A,当遍历下一条记录时需要去加载页C

当这种随机IO过多时,可能每查一条记录相当于要去加载一个页,成本非常大

不要小瞧回表的开销,当查询数据量大,使用二级索引都要回表的话,性能还不如全表扫描(扫描聚簇索引),这通常也是索引失效的一大场景(后续文章再来聊聊这块)

Multi Range Read 多范围读取

那有没有什么办法降低成本呢?

回表成本大的原因主要是产生随机IO,那能不能先在索引上查出多条记录,要回表时对主键值进行排序,让随机IO变成顺序IO呢

对主键值排序后每个加载的页,页中可能存在多条需要回表查询的记录就减少回表随机IO的开销

MySQL中另一个优化回表的手段是:Multi Range Read 多范围读取 MRR

MRR使用缓冲区对需要回表的记录根据主键值进行排序,将随机IO优化为顺序IO

image.png

使用MRR优化后图中第二条记录id为25回表时就可以直接在缓冲池的页A中获取完整记录

查看MRR缓冲池大小show variables like '%read_rnd_buffer_size%';

可以使用查看相关优化器的参数SHOW VARIABLES LIKE 'optimizer_switch';

有关MRR的优化器开关参数:mrrmrr_cost_based

mrr 表示是否开启MRR

MRR还需要在缓冲池中排序的开销,因此并不是所有场景都用MRR,默认情况下启动mrr_cost_based基于成本判断是否要使用MRR

SET optimizer_switch='mrr=on,mrr_cost_based=off';关闭根据成本判断是否用MRR

附加信息携带Using MRR说明使用MRR

image.png

除了将随机IO优化为顺序IO,还有没有什么方式可以降低回表的开销呢?

我们从另一个角度分析,如果减少查询的数据量,是不是也可以减少回表次数,降低回表开销

那如何减少数据量呢?实际上上篇文章说过的ICP就可以减低回表次数

Covering Index 覆盖索引

回表无论如何优化都会存在一定的开销,那有没有可能避免回表呢?

要避免回表问题,那就要知道为什么会回表?

由于使用的二级索引不包含查询需要的字段,因此需要回表查询聚簇索引获取需要的字段

那如果使用的二级索引包含需要的查询字段是不是就避免回表的呢!

因此可以通过修改查询需要的字段select xx1,xx2或 增加二级索引包含的列(变成联合索引)来避免出现回表

注意:如果你想通过增加二级索引的列来避免回表时,需要评估二级索引存在列太多的维护成本

MySQL中的覆盖索引指的是使用二级索引时不需要回表,在执行计划中的附加信息显示Using index

image.png

将查询列表从 * 改为 age,student_name ,使用二级索引时不需要回表

总结

当使用的二级索引不满足查询需要的列时,会进行回表查询聚簇索引获取完整记录

回表不仅需要再查一次聚簇索引,而且在二级索引中主键值可能是乱序的,因此查询聚簇索引会出现随机IO

查询随机IO时可能每条记录都在不同的页中,这会导致每查询一条记录就需要将磁盘中的页加载到缓冲池,随机IO开销很大

优化回表有两种思路:一种是降低回表的开销,另一种是避免回表

Index Condition Push 索引条件下推(上篇文章说的)可以减少回表次数,降低回表的开销

Multi Range Read 多范围读取在某些场景下使用缓冲池排序主机,将读取的随机IO转换为顺序IO,降低回表开销

修改查询需要的字段或者给二级索引上增加列,使用覆盖索引的方式来避免回表

最后(不要白嫖,一键三连求求拉~)

本篇文章被收入专栏 由点到线,由线到面,构建MySQL知识体系,感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJavagithub-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~

关注菜菜,分享更多干货,公众号:菜菜的后端私房菜

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL Java 关系型数据库
MYSQL--JDBC优化
MYSQL--JDBC优化
|
1天前
|
关系型数据库 MySQL 数据库
mysql索引优化
【6月更文挑战第16天】mysql索引优化
5 2
|
3天前
|
缓存 关系型数据库 MySQL
如何优化MySQL 8.0的性能?
【6月更文挑战第14天】如何优化MySQL 8.0的性能?
22 5
|
5天前
|
缓存 监控 关系型数据库
mysql优化
【6月更文挑战第12天】mysql优化
16 3
|
10天前
|
SQL 关系型数据库 MySQL
MySQL大数据量分页查询方法及其优化
MySQL大数据量分页查询方法及其优化
|
10天前
|
缓存 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
27 3
|
11天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结
16 0
|
11天前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
12 0
|
11天前
|
存储 SQL 关系型数据库
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
MySQL数据库——SQL优化(1/3)-介绍、插入数据、主键优化
21 1
|
12天前
|
存储 SQL 关系型数据库
MySQL调优之索引:索引的失效与优化
MySQL调优之索引:索引的失效与优化
14 0

相关产品

  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多