008.mysql之MRR
1.MRR是什么
MRR是 Multi Range Read的缩写。
优化器会先扫描辅助索引,然后收集每行的主键（rowid ）,并对主键进行排序（排序结果存储到read_rnd_buffer),此时就可以用主键顺序访问基表,即用顺序IO代替随机IO。
2.MRR为了解决什么问题
MRR针对于辅助索引上的范围查询进行优化,收集辅助索引对应主键rowid。进行排序后回表查询，随机IO转顺序IO。
MRR优化可用于range，ref，eq_ref类型的查询。
简单说：使用辅助索引进行范围查询时，MRR会收集并排序好符合范围查询条件的rowid。然后通过顺序的rowid去回表查询数据记录。 MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。
3.使用场景
mrr的使用，相关的参数有3个
- mrr 值有
on|off - mrr_cost_based 值有
on|off - read_rnd_buffer_size 具体buffer值
3.1实验
mysql默认开启MRR优化。但是由优化器决定是否真正使用MRR(mrr=on,mrr_cost_based=on)，因为有些时候优化器认为不使用MRR性能会更好！查询MRR的开启状态如下：
SHOW VARIABLES LIKE '%optimizer_switch%'
index_merge=on,index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on==,
block_nested_loop=off,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
关闭MRR优化，不强制使用MRR
set optimizer_switch='mrr=off,mrr_cost_based=on';
k_1是sbtest1 表的一个辅助索引，执行计划如下：
EXPLAIN SELECT * from sbtest1 force index(k_1) where k BETWEEN 1000 and 55555
再来看开启MRR，并强制使用。可以看到执行计划的Extra列多了Using MRR
mrr=on会开启MRR优化功能，mrr_cost_based 则是用来告诉优化器，要不要基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR。很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR。建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。另外还有一个配置 read_rnd_buffer_size ，是用来设置用于给 rowid 排序的内存的大小。显然，MRR 在本质上是一种用空间换时间的算法。MySQL 不可能给你无限的内存来进行排序，如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。
VARIABLES LIKE '%read_rnd_buffer%' -- 262144 字节 0.25M
注意：MRR 只是针对优化回表查询的速度，当不需要回表访问的时候，MRR就失去意义了（比如覆盖索引）
optimizer_switch可以是全局的，也可以是会话级的。当然，除了调整参数外，数据库也提供了语句级别的开启或关闭MRR，使用方法如下：
EXPLAIN SELECT /*+ MRR(sbtest1)*/ * from sbtest1 force index(k_1) where k BETWEEN 1000 and 55555
