Multi-Range Read (MRR)原理

Multi-Range Read (MRR)

MRR 的全称是 Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询

MRR优化的几个好处

使数据访问有随机变为顺序，查询辅助索引是，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找

减少缓冲池中页被替换的次数

批量处理对键值的操作

在没有使用MRR特性时

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest

1	select key_column, pk_column from tb where key_column=x order by key_column

第二步 通过第一步获取的主键来获取对应的值

1 2	for each pk_column valuein restdo: select non_key_column from tb where pk_column=val

使用MRR特性时

第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest

1	select key_column, pk_column from tb where key_column = x order by key_column

第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)，然后对结果集rest按照pk_column排序，得到结果集是rest_sort

第三步 利用已经排序过的结果集，访问表中的数据，此时是顺序IO.

1	select non_key_column fromtb where pk_columnin (rest_sort)

在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行“回表”，这个过程一般会有较多的随机IO, 使用MRR时，SQL语句的执行过程是这样的：

• 优化器将二级索引查询到的记录放到一块缓冲区中

• 如果二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序

• 用户线程调用MRR接口取cluster index，然后根据cluster index 取行数据

• 当根据缓冲区中的 cluster index取完数据，则继续调用过程 2) 3)，直至扫描结束

通过上述过程，优化器将二级索引随机的 IO 进行排序，转化为主键的有序排列，从而实现了随机 IO 到顺序 IO 的转化，提升性能

此外MRR还可以将某些范围查询，拆分为键值对，来进行批量的数据查询，如下：

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000AND key_part2 = 10000;

表t上有二级索引(key_part1, key_part2)，索引根据key_part1,key_part2的顺序排序。

若不使用MRR：此时查询的类型为Range，sql优化器会先将key_part1大于1000小于2000的数据取出，即使key_part2不等于10000，带取出之后再进行过滤，会导致很多无用的数据被取出

若使用MRR：如果索引中key_part2不为10000的元组越多，最终MRR的效果越好。优化器会将查询条件拆分为（1000,1000），（1001,1000），... （1999,1000）最终会根据这些条件进行过滤

相关参数

当mrr=on,mrr_cost_based=on，则表示cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化

当mrr=on,mrr_cost_based=off，则表示总是开启MRR优化

1	SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

参数read_rnd_buffer_size 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序