MRR优化：MySQL多范围读的IO性能提升

可视化演示

通过随机IO问题、MRR工作流程、ICP配合三个模式，展示MySQL如何将随机回表IO转为顺序IO提升范围查询性能。

IO随机之痛

图书馆管理员老李每天都要按书单取书。单子上的书分布在 A 区、B 区、C 区，他每取完一本，就要回到柜台看下一本在哪儿，然后再次出发。结果一上午都在爬楼梯、绕走廊，真正取书的时间反而没多少。数据库里的机械硬盘做范围扫描时也面临同样的问题：数据页不连续，磁头就需要反复移动。

在传统机械硬盘上，一次随机 IO 的开销主要是磁头寻道和盘片旋转等待；而顺序 IO 因为磁头沿着相邻磁道一路读过去，开销几乎只来自数据传输本身。通常一次随机读需要约 10ms，而顺序读一个扇区可能不到 0.1ms。如果范围查询命中几千行，每一行都要这样随机访问，慢查询就被无限放大了。

老李很快发现，问题的根源不是“书太多”，而是“路线太乱”。只要把路线理顺，哪怕书还是那么多，也能轻松很多。这就是 MRR 要解决的第一个痛点。

回表机制解析

为什么范围查询会产生这么多随机 IO？关键在 InnoDB 的索引结构。主键索引的叶子节点直接保存完整行，而二级索引的叶子节点只保存“索引字段 + 主键值”。你想通过 user_id 查用户的手机号，就得先用 user_id 索引找到对应主键值，再拿着主键值到主键索引里找完整行。这个过程叫回表，就像你查到一张“索引卡”，上面只有书名和编号，真正取书还得按编号跑到对应书架。

大范围扫描时，二级索引给出的主键值通常不是连续的。比如 user_id 在 5000 到 6000 之间的记录，主键 id 可能是 1、5、9、17、23……它们散落在表的不同数据页里。每回表一次，就是一次随机访问。命中 N 行，就至少发生 N 次回表。

老李意识到，真正浪费时间的不是取书本身，而是“每次只看一个编号就跑一趟”的习惯。如果先把所有编号抄下来排好序，问题就解决了一半。

MRR执行流程

MRR 的做法和老李的改进一模一样。当优化器决定使用 MRR 时，MySQL 会先把二级索引范围内命中的主键值（rowid）收集到一个内存缓冲区，这个缓冲区由 read_rnd_buffer_size 控制。缓冲区里的主键值收集够一批后，从小到大排序；然后按排序后的顺序，一次性到主键索引批量取行。如果一次装不下，就分批执行。

排序之后，主键值在物理页上的位置更接近，磁头移动距离大幅缩短；当一批主键落在相邻页时，随机 IO 就近似变成了顺序 IO。这也是 MRR 名字里“多范围读”的含义——多个零散的单行读取被合并成有规律的多段顺序读取。

老李推着小车出发了：他不再因为下一个编号在楼上就立刻跑上去，而是等这一批编号都排好，沿路一次取完。如果小车装不下，就分两车，但每车都按书架顺序走。

触发条件精讲

不是所有书单都值得排序。如果读者只借两本书，直接跑两趟反而更快；如果借了几百本分散的书，排序后再取才划算。MySQL 优化器同样要做成本决策，只有满足条件时才会启用 MRR。

触发 MRR 通常需要几个前提：查询使用二级索引，且访问类型是 range 或 ref；查询需要回表获取非索引列；全局开关 optimizer_switch 中 mrr=on；优化器估算 MRR 成本低于直接随机回表。如果查询走主键索引、覆盖索引，或者结果集非常小，MRR 一般不会出现。

老李的经验是“看着办”：单子小就直接跑，单子大就排好序再跑。MySQL 的 mrr_cost_based 就是让优化器拥有同样的判断力。

ICP下推协同

索引下推（ICP）是 MRR 的好搭档。它解决的是另一个问题：回表之前能不能先过滤掉一部分行？联合索引 (user_id, status) 上执行 WHERE user_id BETWEEN 100 AND 2000 AND status=1 时，ICP 允许存储引擎在扫描二级索引时就直接用 status=1 过滤，而不是每行都回表后才发现不满足。

这和 MRR 并不冲突。ICP 帮助的是“减少回表行数”，MRR 帮助的是“让必然发生的回表更顺序”。MySQL 5.6 之后两者可以同时工作，EXPLAIN 中往往同时出现 Using index condition 和 Using MRR。先由 ICP 在索引层把明显不符合条件的行划掉，剩下的 rowid 再交给 MRR 排序批量回表。

老李整理书单时发现，有些书虽然编号在单子上，但借阅规则显示“不在服务范围”。他直接在索引卡阶段就把这些书划掉，而不是跑到书架旁才发现取错了。

参数开关调优

在实际运维中，MRR 是否需要干预，取决于业务的 IO 特征。MySQL 提供几个关键参数：optimizer_switch 中的 mrr 和 mrr_cost_based 控制开关；read_rnd_buffer_size 控制 MRR 缓冲区大小；engine_condition_pushdown 控制 ICP 是否下推。

调优时建议先观察 EXPLAIN。如果大范围回表场景没有出现 Using MRR，可以临时把 mrr_cost_based=off 强制开启，对比执行时间；如果耗时下降明显，说明成本模型低估了 MRR 的收益。read_rnd_buffer_size 不宜设得太小，否则会分成很多批次，导致反复扫描二级索引；也不宜设得过大，毕竟这是会话级内存。

没有一种参数组合适合所有业务。老李需要根据书单的“数量”和“分散程度”来调整自己的策略，DBA 也要根据慢查询和 IO 特征做对照实验。

EXPLAIN诊断实战

要确认 MySQL 到底有没有走 MRR，最直接的方法是看执行计划。EXPLAIN 输出的 Extra 列如果出现 Using MRR，说明优化器选择了多范围读优化。例如下面这条典型的大范围回表查询：

如果 user_id 有二级索引且查询需要取所有列，执行计划里就可能出现 type: range 和 Extra: Using MRR。如果没有看到，可以检查 optimizer_switch、read_rnd_buffer_size，以及查询是否真的需要回表；也可以对比开启和关闭 MRR 后的实际耗时。

老李每次优化完都会在记录单上画一笔，确认到底走了哪条路。DBA 也一样，EXPLAIN 是衡量优化是否落地的第一证据。

EXPLAIN SELECT * FROM ordersWHERE user_id BETWEEN 100 AND 2000\G

AI 应用场景

Python 演示

import random  class DiskSim:    def __init__(self, positions):        self.positions = positions[:]        self.head = 0        self.seek_distance = 0        self.time_ms = 0.0     def read(self, cell):        self.seek_distance += abs(self.head - cell)        self.time_ms += 0.1 + 5.0 * abs(self.head - cell)        self.head = cell     def run_direct(self):        self.head = 0        self.seek_distance = 0        self.time_ms = 0.0        for cell in self.positions:            self.read(cell)        return self.time_ms, self.seek_distance     def run_mrr(self):        self.head = 0        self.seek_distance = 0        self.time_ms = 0.0        for cell in sorted(self.positions):            self.read(cell)        return self.time_ms, self.seek_distance  def main():    random.seed(42)    positions = [random.randint(0, 99) for _ in range(20)]    sim = DiskSim(positions)    direct_time, direct_dist = sim.run_direct()    mrr_time, mrr_dist = sim.run_mrr()    print('随机位置:', positions)    print('直接回表耗时: %.1f ms, 寻道: %d' % (direct_time, direct_dist))    print('MRR回表耗时: %.1f ms, 寻道: %d' % (mrr_time, mrr_dist))    print('耗时下降: %.1f%%' % ((1 - mrr_time / direct_time) * 100))  if __name__ == '__main__':    main()

1. self.positions = positions[:]复制一份随机生成的书号位置，避免影响原始数据。
2. self.time_ms += 0.1 + 5.0 * abs(self.head - cell)模拟顺序读基础耗时加寻道成本，距离越远耗时越长。
3. for cell in self.positions:直接按随机顺序访问，对应不使用 MRR 的离散回表。
4. for cell in sorted(self.positions):排好序后访问，对应 MRR 的按主键顺序回表。
5. print('耗时下降: %.1f%%' % ...)输出 MRR 相比直接回表的收益百分比。

常见误区

面试题