本篇文章主要介绍了"MySQL 优化之 MRR Multi-Range Read:二级索引合并回表"，主要涉及到require,索引方面的内容，对于MySql感兴趣的同学可以参考一下：
MySQL5.6中引入了MRR，专门来优化：二级索引的范围扫描并且需要回表的情况。它的原理是，将多个需要回表的二级索引根据主键进行排序，然后一起回表，将原来的回...
With MRR, the scan is broken up into multiple ranges, each for a single value of key_part1 (1000, 1001, ... , 1999). Each of these scans need look only for tuples with key_part2 = 10000. If the index contains many tuples for which key_part2 is not 10000, MRR results in many fewer index tuples being read.
To express this using interval notation, the non-MRR scan must examine the index range [{1000, 10000}, {2000, MIN_INT}), which may include many tuples other than those for which key_part2 = 10000. The MRR scan examines multiple single-point intervals [{1000, 10000}], ..., [{1999, 10000}], which includes only tuples with key_part2 = 10000.
Two optimizer_switch system variable flags provide an interface to the use of MRR optimization. The mrr flag controls whether MRR is enabled. If mrr is enabled (on), the mrr_cost_based flag controls whether the optimizer attempts to make a cost-based choice between using and not using MRR (on) or uses MRR whenever possible (off). By default, mrr is on and mrr_cost_based is on. See Section 8.9.2, “Controlling Switchable Optimizations”.
For MRR, a storage engine uses the value of the read_rnd_buffer_size system variable as a guideline for how much memory it can allocate for its buffer. The engine uses up to read_rnd_buffer_size bytes and determines the number of ranges to process in a single pass.
MySQL的MRR一次扫描多少个二级索引，然后进行回表，其使用到的内存是参考 read_rnd_buffer_size 的值来决定的。
总结：
MRR 仅仅针对 二级索引 的范围扫描 和 使用二级索引进行 join 的情况。
MRR 的优势是将多个随机IO转换成较少数量的顺序IO。所以对于 SSD 来说价值还是有的，但是相比机械磁盘来说意义小一些。
以上就介绍了MySQL 优化之 MRR Multi-Range Read:二级索引合并回表，包括了require,索引方面的内容，希望对MySql有兴趣的朋友有所帮助。
本文网址链接：http://www.codes51.com/article/detail_228012_3.html
