MySQL 5.6新特性 MRR（Multi-Range Read Optimization）

官方网站：https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html

多范围读取优化（MRR）#

explain语句#

• 当表很大且未存储在存储引擎的缓存中时，使用二级索引上的范围扫描读取行可能会导致对基表的许多随机磁盘访问。
• 通过磁盘扫描多范围读取 (MRR) 优化，MySQL 通过首先仅扫描索引并收集相关行的键来尝试减少范围扫描的随机磁盘访问次数。
• 然后对键进行排序，最后使用主键的顺序从基表中检索行。

Disk-sweep MRR 的动机是减少随机磁盘访问的次数，而是实现对基表数据的更顺序扫描。

MRR 允许基于索引元组按顺序访问数据行，而不是按随机顺序访问。

• 服务器获取一组满足查询条件的索引元组，按照数据行ID的顺序进行排序，并使用排序后的元组依次检索数据行，使得数据访问更加高效且成本更低。
• MRR 支持对需要通过索引元组访问数据行的操作的键访问请求进行批处理，例如范围索引扫描和对连接属性使用索引的等连接。
• MRR 迭代一系列索引范围以获得合格的索引元组，随着这些结果的积累，它们被用于访问相应的数据行，在开始读取数据行之前不需要获取所有索引元组。

MRR 可用于 InnoDB 和 MyISAM 表的索引范围扫描和等连接操作#

• 索引元组的一部分累积在缓冲区中
• 缓冲区中的元组按其数据行 ID 排序
• 根据排序的索引元组序列访问数据行

图片来自知乎

 

将行id排好序后，再去磁盘顺序访问数据，不会涉及到随机访问的各种磁盘调度，大大降低了访问成本

MRR的好处#

不涉及磁盘调度#

磁盘和磁头不再需要来回做机械运动

局部性原理#

在一次查询中，每一页的数据只会从磁盘读取一次，可以充分利用磁盘预读，大大减少磁盘IO

MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。

MRR配置#

• mrr: on/off（开关）
• mrr_cost_based: on/off（基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR）

如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR，即使只有一条数据

Mariadb 对 MySQL 的 MRR做了很多优化

• 一部分范围（可能是单键范围）在提交查询的中央节点上的缓冲区中累积
• 范围被发送到访问数据行的执行节点
• 访问的行被打包成包并发送回中央节点，接收到的带有数据行的包被放置在一个缓冲区中
• 从缓冲区读取数据行

如果使用了覆盖索引，MRR 没有任何意义

对于 MRR，存储引擎使用 read_rnd_buffer_size 系统变量的值作为它可以为其缓冲区分配多少内存的指南

引擎最多使用 read_rnd_buffer_size 字节并确定要在单次传递中处理的范围数