ToB企服应用市场:ToB评测及商务社交产业平台

标题: 详解MySQL中MRR(多范围读取)如何优化范围查询 [打印本页]

作者: 星球的眼睛 时间: 2024-10-14 14:06
标题: 详解MySQL中MRR(多范围读取)如何优化范围查询
❃博主首页：   「码到三十五」   ，同名公众号 :「码到三十五」，wx号 : 「liwu0213」
  ☠博主专栏：   <mysql高手>    <elasticsearch高手>    <源码解读>    <java核心>    <面试攻关>
  ♝博主的话：  搬的每块砖，皆为峰峦之基；公众号搜索「码到三十五」关注这个爱发技术干货的coder，一起筑基

MySQL提供了多种优化技术以提高查询性能。其中，MRR（Multi-Range Read）优化是一种紧张的查询优化技术，尤其在处置惩罚包含多个范围条件的查询时，能够显著提升查询服从。

一、MRR优化概述

MRR，全称Multi-Range Read Optimization，直译为多范围读取优化，是MySQL中一种用于提高索引查询性能的技术。MRR通过淘汰随机磁盘访问次数，将随机IO转换为顺序IO，从而提高数据读取的服从。它特别适用于包含范围条件（如BETWEEN、<、>等）的查询，以及须要通过辅助索引访问表数据的场景。
二、MRR优化的背景

在InnoDB中表数据是通过聚集索引组织的。当基于辅助索引的范围查询时，须要先通过辅助索引找到对应的主键值，再通过主键值回表查询完整的行数据。这种回表会产生大量的随机磁盘I/O，尤其是在处置惩罚大表时，随机I/O的性能瓶颈尤为显着。MRR优化正是为了解决这一问题提出。
三、MRR优化的原理

MRR优化的核心思想是将多个范围查询中的随机磁盘I/O转换为顺序磁盘I/O，从而提高查询性能。

扫描辅助索引并收集主键值：
- 当实验一个包含范围条件的查询时，MySQL优化器首先会扫描辅助索引，找到满意条件的一系列索引元组。
- 对于每个索引元组，MySQL会收集其对应的主键值（rowid）。
对主键值进行排序：
- 收集到的主键值会被放入一个内存缓冲区（read_rnd_buffer）中。
- 当缓冲区满或查询竣事时，MySQL会对缓冲区中的主键值进行排序。排序的目的是为了将随机访问转换为顺序访问。
顺序访问基表：
- 排序后的主键值将按照顺序被用来访问基表，检索出完整的数据行。
- 由于主键值是有序的，因此访问基表时产生的磁盘I/O也变为顺序I/O，从而提高了读取服从。
使用磁盘预读和缓存机制：
- MRR优化还充实使用了磁盘的预读机制。当哀求读取某一页数据时，磁盘会预测并提前读取相邻的几页数据到内存中。
- 由于MRR将随机访问转换为顺序访问，磁盘预读机制能够更好地发挥作用，淘汰磁盘寻道时间和旋转延迟。
- 同时，顺序访问也提高了缓存的掷中率，由于连续访问的数据页更有可能在缓存中找到。
基于本钱的决策：
- MySQL优化器会根据查询的本钱（如I/O本钱、CPU本钱等）来决定是否使用MRR优化。
- 用户可以通过调整optimizer_switch系统变量中的mrr和mrr_cost_based标志来控制MRR优化的使用。mrr_cost_based设置为ON时，优化器会根据本钱来决定是否使用MRR；设置为OFF时，则逼迫使用MRR（但通常不建议如许做，由于优化器在大多数环境下都是精确的）。

四、MRR优化的优势

提高查询性能：通过淘汰随机磁盘I/O次数和提高缓存掷中率，MRR优化能够显著提高查询性能。
淘汰I/O本钱：顺序I/O比随机I/O具有更低的本钱，由于顺序I/O可以更有效地使用磁盘带宽和缓存资源。
适用于多种查询类型：MRR优化不仅适用于范围查询（如BETWEEN、<、>等），还适用于等值毗连（equi-join）等须要回表访问的场景。

五、磁盘预读机制

MRR优化充实使用了磁盘预读机制。当客户端哀求读取某一页数据时，磁盘预读功能会预测并提前读取相邻的几页数据到内存缓冲区中。由于MRR将随机访问转换为顺序访问，磁盘预读机制能够更好地发挥作用，淘汰磁盘寻道时间和旋转延迟，进一步提升读取服从。
六、局部性原理

局部性原理是MRR优化的另一个理论基础。时间局部性表明，如果某个数据项被访问，那么在不久的将来它可能再次被访问；空间局部性表明，一旦某个数据项被访问，那么其附近的数据项也可能很快被访问。MRR通过顺序访问数据，使得数据访问更加符合局部性原理，从而提高了缓存掷中率，淘汰了磁盘访问次数。
七、使用场景、条件与监控

MRR优化适用于基于范围扫描和等值毗连的使用中尤为有效。但是，并非全部查询都能从MRR优化中受益。如，当查询完全基于索引元组中的信息（纵然用覆盖索引）时，MRR优化就没有须要，由于此时无需回表访问基表数据。
别的，MySQL默认开启MRR优化，但是否真正使用MRR由优化器决定。优化器会根据查询的本钱（如IO本钱、CPU本钱等）来决定是否接纳MRR优化。用户可以通过调整optimizer_switch系统变量中的mrr和mrr_cost_based标志来控制MRR优化的使用。
1. 配置参数

optimizer_switch：包含mrr和mrr_cost_based两个选项，分别用于控制是否启用MRR优化以及是否基于本钱决定是否使用MRR。
read_rnd_buffer_size：设置用于给rowid排序的内存缓冲区的大小。这个参数的大小会影响MRR优化的效果，须要根据现实环境进行调整。

2. 监控方法

使用EXPLAIN语句查看查询的实验筹划。如果查询使用了MRR优化，EXPLAIN的输出会在Extra列中显示Using MRR。
监控查询的响应时间和I/O开销。通过比力开启和关闭MRR优化时的查询性能，可以评估MRR优化的效果。

八、SQL案例解读

一个为orders的表结构如下：

CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
INDEX idx_customer_date (customer_id, order_date)
) ENGINE=InnoDB;

复制代码

表中，customer_id和order_date上有一个团结索引idx_customer_date。想要查询某个特定客户在指定日期范围内的全部订单，SQL语句：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

复制代码

扫描辅助索引：
- MySQL首先会使用辅助索引idx_customer_date来定位满意customer_id = 123和order_date BETWEEN '2023-01-01' AND '2023-12-31'条件的索引元组。
- 这些索引元组包含了customer_id、order_date以及对应的主键值（id）。
收集并排序主键值：
- MySQL会收集这些索引元组对应的主键值，并将它们放入一个内存缓冲区（read_rnd_buffer）中。
- 当缓冲区满或查询竣事时，MySQL会对这些主键值进行排序。排序的目的是为了后续的顺序访问基表。
顺序访问基表：
- 使用排序后的主键值，MySQL将顺序访问orders表的基表部分，检索出完整的订单数据行。
- 由于主键值是有序的，因此访问基表时产生的磁盘I/O变为顺序I/O，提高了读取服从。
使用磁盘预读和缓存机制：
- 在顺序访问基表的过程中，磁盘预读机制会预测并提前读取相邻的数据页到内存中。
- 这有助于淘汰磁盘寻道时间和旋转延迟，并提高缓存掷中率。
查询性能提升：
- 相比没有MRR优化的环境，使用MRR可以显著淘汰随机磁盘I/O的次数，从而提高查询性能。
- 特别是在处置惩罚大表时，MRR优化的效果更加显着。

关注公众号[码到三十五]获取更多技术干货 !

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)