孔个个
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之前(MySQL5.6之前)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)，随机主键不在同一个page里时会导致多次IO和随机读。
- 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest
select key_column, pk_column from tb where key_column=x order by key_column
- 通过第一步获取的主键来获取对应的值
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操作，提高查询效率。
- 先根据where条件中的辅助索引获取辅助索引与主键的集合，结果集为rest
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.
select non_key_column fromtb where pk_column in (rest_sort)
在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行”回表”， 这个过程一般会有较多的随机IO；
使用MRR时，SQL语句的执行过程是这样的：
通过上述过程，优化器将二级索引随机的 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 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序。Original: https://www.cnblogs.com/konggg/p/14695323.html
Author: 孔个个
Title: MySQL特性：MRR,Multi-Range Read，多范围读
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/621102/
转载文章受原作者版权保护。转载请注明原作者出处！