之前有次面试被问到了解MySQL中的MRR吗？当时仅仅只是听过这个名词，没答上来。最近在复习前面学过的MySQL知识，了解到这个知识点，写篇博客记录一下，加深印象。
什么是MRR？
MRR，全称「Multi-Range Read Optimization」。
先来了解下回表，回表是指，InnoDB在普通索引a上查到主键id的值后，再根据一个个主键id的值到主键索引上去查整行数据的过程。
我们知道二级索引是有回表的过程的，由于二级索引上引用的主键值不一定是有序的，因此就有可能造成大量的随机 IO，如果回表前把主键值给它排一下序，那么在回表的时候就可以用顺序 IO 取代原本的随机 IO。
简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。
顺序读带来了几个好处：
1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
MRR 在本质上是一种用空间换时间的算法。MySQL 不可能给你无限的内存来进行排序，这块内存的大小就由参数 read_rnd_buffer_size 来控制,如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。
假设，我执行这个语句：
select * from t1 where a>=1 and a<=100;
主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的，基本流程如图1所示。
如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。
因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。
这，就是MRR优化的设计思路。此时，语句的执行流程变成了这样：
1.根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
2.将read_rnd_buffer中的id进行递增排序；
3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回。
这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。如果步骤1中，read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引a的下个记录，并继续循环。
下面两幅图就是使用了MRR优化后的执行流程和explain结果。
从explain结果中，我们可以看到Extra字段多了Using MRR，表示的是用上了MRR优化。而且，由于我们在read_rnd_buffer中按照id做了排序，所以最后得到的结果集也是按照主键id递增顺序的，也就是与图1结果集中行的顺序相反。
MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
MRR如何使用？
//如果你不打开，是一定不会用到 MRR 的。 set optimizer_switch='mrr=on'; set optimizer_switch ='mrr_cost_based=off'; set read_rnd_buffer_size = 32 * 1024 * 1024;
mrr_cost_based: on/off，则是用来告诉优化器，要不要基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR。
很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR，这在有些情况下是很 stupid 的，所以建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。
实际使用
做个测试，我在mysql里预先插入了300w条假数据。
表的结构是这样的
age字段是300w递减
不建立索引查询
age字段不建立索引直接查询
EXPLAIN SELECT * FROM user where age>10 AND age<200000;
建立索引查询
age字段建立索引查询
create index age_index on `user`(age); EXPLAIN SELECT * FROM user where age>10 AND age<200000;
MRR优化查询
set optimizer_switch='mrr=on'; SELECT * FROM user where age>10 AND age<50;
使用上了MRR优化
