MySQL特性：MRR,Multi-Range Read，多范围读

MRR,Multi-Range Read，多范围读

孔个个
MRR在5.6版本开始支持，相关文章不少。但是读起来层次感差了一些，在这里我用自己的理解重新整理了一版。
这里参考了很多在网络上能找到的资料，才使我更全面的理解MRR，但时间有些久，并未记录出处，且多数文字并非原文，在此感谢所有默默分享知识的大佬们。

5.6以上版本开始支持MRR

基于辅助/第二索引的查询时，将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型（IO密集型）的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询。

MRR原理

查询辅助索引时，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找，避免频繁发生离散读操作导致缓冲区中的页被替换出缓冲区，然后又不断的被新的请求读入缓冲区，减少缓冲池中页被替换的次数。

• 将查询到的辅助索引结果放在一个buffer中（read_end_buffer_size)
• 将buffer中的辅助索引根据主键(rowid)进行排序
• 再根据上述排序后的主键(rowid)顺序，(回表)读取数据

MRR效果，MRR带来的好处

• 减少磁盘随机IO访问，将随机IO访问转变成顺序IO访问，提高IO读性能

• 减少buffer pool中页面被替换的次数

  如果存储引擎（不仅仅是InnoDB）的缓冲池不是足够大，即：不能存放下一张表中的所有数据，此时频繁的发生离散读操作会导致缓冲区中的页被替换出缓冲区，然后又不断的被新的请求读入缓冲区。

  若按照主键顺序进行访问，则可以将此重复行为降到最低。

• 可批量处理对索引的查询操作

MRR效果的讲解

• 在没有使用MRR特性时

  不使用MRR之前(MySQL5.6之前)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)，随机主键不在同一个page里时会导致多次IO和随机读。

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

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

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

     for each pk_column value in rest 
     do:	
     	select non_key_column from tb where pk_column=val
     

• 使用MRR特性时

  使用MRR优化(MySQL5.6之后)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。

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

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

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

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

     select non_key_column fromtb where pk_column in (rest_sort)
     

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

使用MRR时，SQL语句的执行过程是这样的：

1. 优化器将二级索引查询到的记录放到一块缓冲区中（read_end_buffer_size）
2. 如果二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键（rowid）进行排序
3. 用户线程调用MRR接口取cluster index，然后根据cluster index 取行数据
4. 当根据缓冲区中的 cluster index取完数据，则继续调用过程 2) 、3)，直至扫描结束

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

MRR还可以对某些范围查询进行批量的数据查询，提升性能。

在拆分过程中直接过滤掉不符合查询条件的数据。

将某些范围查询拆分为key对，以此来进行批量数据查询。

例如，表t中有(key_part1,key_part2)的联合索引，

对SQL：select * from t where key_part1 >= 1000 and key_part2 < 2000 and key_part2 = 10000;

索引根据key_part1,key_part2的位置关系进行排序。

• 如果没有MRR

  此时查询类型为range。SQL优化器会先将key_part1大于1000且小于2000的数据全都取出，即使这部分数据的key_part2并不等于10000。待取出这些数据后再根据key_part2的条件进行过滤。

  这就导致无用的数据被取出来了。

  如果符合key_part1大于1000且小于2000的数据中有相当的数据key_part2不符合条件，那么MRR优化可以使这部分不符合条件的数据不需要读取，使性能获得明显提升。

• 启用MRR优化

  优化器会先将查询条件拆分，然后再进行数据查询。

  如：将条件拆分为（1000，10000），（1001，10000），……，最后再根据这些拆分出来的条件进行数据查询。

一个关于MRR的简单例子

表salaries中salary列上有一个辅助索引 idx_s

对于SQL： select * from salaries where salary > 10000 and salary < 40000;而言，

• 未启用MRR时，查询首先要按照辅助索引进行范围查找，然后再通过辅助索引记录后的主键值回表查询整行数据。Extra只有Using index condition。
• 启用MRR时，查询首先将命中的辅助索引值放入read_end_buffer_size缓冲区，此时缓冲区中的数据是按照辅助索引的顺序排序的。然后将缓冲区中的数据根据rowid（或显式主键）进行排序。最后根据rowid排序的顺序去访问实际的数据文件，此时就是按照主键顺序去顺序的发生IO。Extra也会有Using MRR信息。

配置MRR的相关参数

• 优化器开关optimizer_switch 控制是否启用MRR，默认未启用MRR

  ...
  mrr={on|off}
  mrr_cost_based={on|off}
  ...

  e.g:

  set @@optimizer_switch='mrr=on,mrr_cost_based=off';

• 开启方式：mrr = on & mrr_cost_based = on/off

  mrr_cost_based用来告诉优化器，要不要基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR。

  很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR，这在有些情况下是很 stupid 的，所以建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。

  • mrr=on,mrr_cost_based=off时，强制开启MRR
  • mrr=on,mrr_cost_based=on时，优化器会通过CBO算法确定是否开启MRR特性

• 5.6.35中存在bug：由optimizer_switch引起诡异问题

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