MySQL中MRR怎样优化范围查询

目录

• 一、MRR优化概述
• 二、MRR优化的配景
• 三、MRR优化的原理
• 四、MRR优化的上风
• 五、磁盘预读机制
• 六、局部性原理
• 七、使用场景、条件与监控
• 1. 配置参数
• 2. 监控方法
• 八、SQL案例解读

一、MRR优化概述

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

二、MRR优化的配景

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

三、MRR优化的原理

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

1. 扫描辅助索引并收集主键值：

   • 当执行一个包罗范围条件的查询时，MySQL优化器起首会扫描辅助索引，找到满足条件的一系列索引元组。
   • 对于每个索引元组，MySQL会收集其对应的主键值（rowid）。

2. 对主键值举行排序：

   • 收集到的主键值会被放入一个内存缓冲区（read_rnd_buffer）中。
   • 当缓冲区满或查询竣事时，MySQL会对缓冲区中的主键值举行排序。排序的目的是为了将随机访问转换为序次访问。

3. 序次访问基表：

   • 排序后的主键值将按照序次被用来访问基表，检索出完整的数据行。
   • 由于主键值是有序的，因此访问基表时产生的磁盘I/O也变为序次I/O，从而提高了读取效率。

4. 使用磁盘预读和缓存机制：

   • MRR优化还充分使用了磁盘的预读机制。当请求读取某一页数据时，磁盘会预测并提前读取相邻的几页数据到内存中。
   • 由于MRR将随机访问转换为序次访问，磁盘预读机制可以大概更好地发挥作用，减少磁盘寻道时间和旋转耽误。
   • 同时，序次访问也提高了缓存的命中率，由于连续访问的数据页更有大概在缓存中找到。

5. 基于成本的决策：

   • MySQL优化器会根据查询的成本（如I/O成本、CPU成本等）来决定是否使用MRR优化。
   • 用户可以通过调整[code]optimizer_switch[/code]体系变量中的[code]mrr[/code]和[code]mrr_cost_based[/code]标志来控制MRR优化的使用。[code]mrr_cost_based[/code]设置为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优化。用户可以通过调整[code]optimizer_switch[/code]体系变量中的[code]mrr[/code]和[code]mrr_cost_based[/code]标志来控制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案例解读

一个为[code]orders[/code]的表结构如下：

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

1. 扫描辅助索引：

   • MySQL起首会使用辅助索引[code]idx_customer_date[/code]来定位满足[code]customer_id = 123[/code]和[code]order_date BETWEEN '2023-01-01' AND '2023-12-31'[/code]条件的索引元组。
   • 这些索引元组包罗了[code]customer_id[/code]、[code]order_date[/code]以及对应的主键值（[code]id[/code]）。

2. 收集并排序主键值：

   • MySQL会收集这些索引元组对应的主键值，并将它们放入一个内存缓冲区（read_rnd_buffer）中。
   • 当缓冲区满或查询竣事时，MySQL会对这些主键值举行排序。排序的目的是为了后续的序次访问基表。

3. 序次访问基表：

   • 使用排序后的主键值，MySQL将序次访问[code]orders[/code]表的基表部分，检索出完整的订单数据行。
   • 由于主键值是有序的，因此访问基表时产生的磁盘I/O变为序次I/O，提高了读取效率。

4. 使用磁盘预读和缓存机制：

   • 在序次访问基表的过程中，磁盘预读机制会预测并提前读取相邻的数据页到内存中。
   • 这有助于减少磁盘寻道时间和旋转耽误，并提高缓存命中率。

5. 查询性能提拔：

   • 相比没有MRR优化的情况，使用MRR可以显著减少随机磁盘I/O的次数，从而提高查询性能。
   • 特别是在处置惩罚大表时，MRR优化的效果更加明显。

以上就是MySQL中MRR怎样优化范围查询的详细内容，更多关于MySQL MRR优化范围查询的资料请关注脚本之家其它干系文章！