MySQL执行查询时，如果需要通过二级索引回表获取聚簇索引的数据，当二级索引匹配到的主键值分布离散时，会产生大量随机磁盘读操作。磁盘随机读需要频繁寻道，IO效率远低于顺序读，会显著提升查询的响应时间，增加磁盘负载。针对这个问题，MySQL引入了MRR（Multi-Range Read）机制，专门用于将离散的随机读转换为连续的顺序读，从而优化磁盘IO性能。
随机读带来的性能问题
在InnoDB存储引擎中，数据按照聚簇索引的顺序存储在磁盘上，二级索引的叶子节点存储的是对应的主键值。当使用二级索引查询数据时，如果查询需要返回聚簇索引中的其他列，就需要根据二级索引得到的主键值去聚簇索引中查找对应的行数据，这个过程称为回表。
如果二级索引匹配出的主键值在聚簇索引中分布非常分散，那么回表时就会产生大量的随机读操作。比如用户表有100万行数据，通过状态索引查询出1万个状态为1的用户，这1万个用户的主键可能是1、1000、5000、10000这样离散的值，回表时磁盘需要不断移动磁头到不同的位置读取数据，性能会非常差。
MRR机制的核心原理
MRR机制的核心思路是，在回表之前先对二级索引获取到的主键值进行排序，让主键值按照聚簇索引的顺序排列，这样再去聚簇索引中读取数据时，原本离散的随机读就会变成连续的顺序读，减少磁盘寻道次数。
MRR机制的工作流程可以分为以下几个步骤：
- 第一步，存储引擎通过二级索引扫描，获取到所有需要回表的主键值，将这些主键值缓存到内存的MRR缓冲区中。
- 第二步，对MRR缓冲区中的主键值按照聚簇索引的顺序进行排序，排序后的主键值在聚簇索引中是连续或者接近连续的。
- 第三步，按照排序后的主键顺序，批量去聚簇索引中读取对应的行数据，此时磁盘读操作就变成了顺序读，IO效率大幅提升。
- 第四步，将读取到的行数据返回给MySQL Server层，由Server层进行后续的过滤、排序等操作。
MRR机制的配置方式
MySQL中通过optimizer_switch系统变量来控制MRR机制是否开启，相关的配置参数有两个：
mrr：控制是否启用MRR优化，默认值为on，表示开启。mrr_cost_based：控制是否基于成本决定是否使用MRR，默认值为on。如果开启，优化器会评估使用MRR的成本，只有当MRR的成本更低时才会使用；如果设置为off，只要查询符合MRR的使用条件就会强制使用MRR。
可以通过以下SQL语句查看和修改MRR的配置：
-- 查看当前MRR相关配置 SELECT @@optimizer_switch; -- 开启MRR并关闭基于成本的判断，强制使用MRR SET optimizer_switch = 'mrr=on,mrr_cost_based=off'; -- 仅针对当前会话生效，全局生效需要加GLOBAL关键字 SET SESSION optimizer_switch = 'mrr=on,mrr_cost_based=off';
另外，MRR缓冲区的大小由read_rnd_buffer_size参数控制，默认值为256KB，如果查询需要回表的主键数量很多，可以适当调大这个参数，避免缓冲区溢出导致无法使用MRR。
-- 查看read_rnd_buffer_size大小 SELECT @@read_rnd_buffer_size; -- 调整缓冲区大小为1MB SET SESSION read_rnd_buffer_size = 1024 * 1024;
MRR机制的适用场景
MRR机制并不是所有查询场景都适用，它主要适合以下场景：
- 使用二级索引进行范围查询，并且需要回表获取聚簇索引数据的场景，比如
WHERE条件中使用二级索引的范围查询，同时查询的列不在二级索引中。 - 二级索引匹配出的主键值离散度较高的场景，这类场景原本的随机读开销大，MRR的顺序读优化效果明显。
- 查询需要返回大量行数据的场景，因为MRR需要缓存主键值并排序，行数太少的话优化收益不明显，甚至可能因为额外的排序开销导致性能下降。
以下场景不适合使用MRR：
- 查询可以通过覆盖索引完成，不需要回表的场景，此时没有随机读问题，MRR不会生效。
- 二级索引匹配出的主键值本身已经是顺序的，比如按照主键顺序插入的数据，通过二级索引查询时主键值本身就是连续的，不需要额外排序。
- 查询返回的行数非常少，排序和缓存的开销可能超过随机读优化的收益。
MRR优化效果验证
可以通过EXPLAIN语句查看查询是否使用了MRR优化，如果Extra列中出现Using MRR的提示，就表示当前查询使用了MRR机制。
比如有一张用户表user，在status字段上建立了二级索引，执行以下查询：
-- 查看查询执行计划 EXPLAIN SELECT * FROM user WHERE status = 1 LIMIT 1000;
如果配置正确且符合MRR的使用条件，执行计划的Extra列会显示Using MRR。可以通过对比开启和关闭MRR时的查询响应时间，来验证MRR的优化效果，通常在大批量随机回表的场景下，MRR可以让查询性能提升30%以上。
注意事项
使用MRR机制时需要注意以下几点：
- MRR排序需要消耗CPU资源，如果CPU资源已经非常紧张，开启MRR可能会导致CPU成为新的瓶颈，此时需要评估整体收益。
read_rnd_buffer_size的大小需要根据实际查询情况调整，过大会占用过多内存，过小则无法缓存所有需要回表的主键，导致MRR无法生效。- MRR机制只对InnoDB和MyISAM存储引擎有效，其他存储引擎不支持该优化。
通过合理使用MRR机制，可以有效降低MySQL查询过程中的随机读磁盘压力，提升查询性能，尤其是在大批量回表的查询场景中，优化效果非常显著。