MySQL中MRR如何优化范围查询_Mysql

一、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优化的效果更加明显。