MySQL高级讲座篇之：MRR（Multi-Range Read）优化：批量随机读到顺序读的性能革命。

大家好，我是你们的老朋友，今天咱们聊聊MySQL里一个挺有意思的优化策略：MRR，也就是Multi-Range Read。 别看名字高大上，其实它干的事情很简单，就是把随机读变成顺序读，从而大幅提升性能。 想象一下，你平时在图书馆找书，一种情况是图书馆员给你一个书单，你按照书单上的顺序一本一本找，另一种情况是图书馆员随便给你指几本书，你找完这本，再去远处找下一本，再回来找另一本…… 哪种方式更有效率？ 显然是第一种，因为顺序查找可以最大化利用空间局部性，减少来回奔波。 MRR 的作用，就是把 MySQL 里的第二种情况，尽可能变成第一种。

一、 啥是 MRR？ 为什么要用它？

在深入细节之前，我们先来明确几个概念：

• 随机读 (Random Read): 数据库根据索引找到数据行的位置后，需要到磁盘上读取这些数据。如果这些数据行的物理位置是分散的，读取的顺序是随机的，这就是随机读。 随机读最大的问题就是磁盘寻道时间。 磁盘寻道，就好比唱片机要找到唱片上的特定位置，磁头需要在盘片上移动，这个动作是相当耗时的。

• 顺序读 (Sequential Read): 如果数据行的物理位置是连续的，读取的时候可以按照顺序连续读取，这就是顺序读。 顺序读避免了大量的磁盘寻道，速度自然快得多。

• MRR (Multi-Range Read): MRR 是一种优化策略，它的核心思想是：将根据二级索引查到的主键 ID 排序，然后按照排序后的主键 ID 顺序去访问实际的数据行。 这样就把原来随机的磁盘 I/O 变成了顺序的磁盘 I/O，从而提高性能。

为什么要用 MRR 呢？

原因很简单：磁盘 I/O 的效率是数据库性能的瓶颈之一。 尤其是对于机械硬盘 (HDD) 来说，随机 I/O 的性能非常差。 通过 MRR，我们可以显著减少随机 I/O 的次数，提高查询效率。

二、 MRR 的工作原理

MRR 的工作流程大致如下：

1. 索引扫描： MySQL 首先通过二级索引（也叫辅助索引）扫描，找到符合条件的数据行对应的主键 ID。

2. ID 排序： 将找到的主键 ID 放到一个缓冲区（read_rnd_buffer）中，并按照主键 ID 进行排序。

3. 范围读取： 根据排序后的主键 ID 顺序，去磁盘上读取实际的数据行。

举个例子：

假设我们有一个 orders 表，包含以下字段：

• order_id (主键，自增)
• customer_id (索引)
• order_date
• amount

现在我们要查询 customer_id 为 123 的所有订单：

SELECT * FROM orders WHERE customer_id = 123;

如果没有 MRR，MySQL 可能会按照二级索引 customer_id 找到的顺序，一条一条地去读取 orders 表中的数据。 如果 customer_id 为 123 的订单在磁盘上是分散存储的，那么就会产生大量的随机 I/O。

如果启用了 MRR，MySQL 会先通过 customer_id 索引找到所有符合条件的 order_id，例如：100, 20, 50, 80, 10。 然后将这些 order_id 排序：10, 20, 50, 80, 100。 最后，按照排序后的 order_id 顺序去读取 orders 表中的数据，这样就能最大程度地减少随机 I/O。

三、 MRR 的配置和使用

MRR 默认是启用的，但我们可以通过以下系统变量来控制 MRR 的行为：

• optimizer_switch: 控制 MRR 是否启用。 例如：SET optimizer_switch='mrr=on,mrr_cost_based=on,read_rnd_buffer_size=131072';。 mrr=on 表示启用 MRR，mrr_cost_based=on 表示让优化器根据成本来决定是否使用 MRR。
• read_rnd_buffer_size: MRR 使用的缓冲区大小，用于存放主键 ID。 默认值通常是 256KB。 增大这个值可以提高 MRR 的效率，但也会占用更多的内存。
• mrr_buffer_size: MySQL 5.6 之后引入，用于替代 read_rnd_buffer_size，含义相同。

如何查看 MRR 是否生效？

可以使用 EXPLAIN 命令来查看查询计划。 如果查询计划中出现了 Using MRR，就说明 MRR 生效了。

例如：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

如果 Extra 列显示 Using index condition; Using MRR，就表示使用了 MRR。

四、 MRR 的适用场景和限制

MRR 并非万能的，它只在特定的场景下才能发挥作用。

适用场景：

• 二级索引查询： MRR 主要用于优化通过二级索引进行的查询。
• 范围查询： MRR 在范围查询中效果更明显，因为范围查询通常会返回大量的数据行。
• 需要回表的查询： 如果查询需要根据索引找到主键 ID 后，再回表查询其他字段，MRR 才能发挥作用。

限制：

• 主键索引查询： MRR 对主键索引查询没有效果，因为主键本身就是有序的。
• 没有二级索引： 如果没有合适的二级索引，MRR 也无法使用。
• JOIN 操作： MRR 对 JOIN 操作的支持有限。
• 空间索引： MRR 不支持空间索引。
• InnoDB Fulltext索引： MRR不支持InnoDB Fulltext索引。

五、 MRR 的进阶用法：MRR 与 Index Condition Pushdown (ICP)

MRR 通常会和 Index Condition Pushdown (ICP) 一起使用，以进一步提高性能。

什么是 ICP？

ICP 是一种优化策略，它将部分 WHERE 条件下推到存储引擎层进行过滤，减少了不必要的数据传输。

MRR + ICP 的工作原理：

1. 索引扫描： MySQL 通过二级索引扫描，找到符合部分条件的数据行对应的主键 ID。 这部分条件是由 ICP 下推到存储引擎层进行过滤的。

2. ID 排序： 将找到的主键 ID 放到缓冲区中，并按照主键 ID 进行排序。

3. 范围读取： 根据排序后的主键 ID 顺序，去磁盘上读取实际的数据行。

4. 剩余条件过滤： 读取到数据行后，再在 MySQL Server 层应用剩余的 WHERE 条件进行过滤。

举个例子：

假设我们有一个 products 表，包含以下字段：

• product_id (主键，自增)
• category_id (索引)
• price
• name

现在我们要查询 category_id 为 10，并且 price 大于 100 的所有产品：

SELECT * FROM products WHERE category_id = 10 AND price > 100;

如果没有 ICP，MySQL 会先通过 category_id 索引找到所有 category_id 为 10 的产品，然后将所有这些产品的数据都读取到 MySQL Server 层，再在 Server 层过滤掉 price 小于等于 100 的产品。

如果启用了 ICP，MySQL 会将 price > 100 这个条件也下推到存储引擎层，在存储引擎层就过滤掉 price 小于等于 100 的产品，只将 category_id 为 10 且 price 大于 100 的产品的数据读取到 MySQL Server 层，从而减少了数据传输。

如何判断 ICP 是否生效？

同样可以使用 EXPLAIN 命令。 如果 Extra 列显示 Using index condition，就表示 ICP 生效了。 结合之前的 MRR，那么Extra 列显示 Using index condition; Using MRR， 就表示MRR和ICP都生效了。

六、 MRR 的参数调优

MRR 的性能受到 read_rnd_buffer_size (或者 mrr_buffer_size) 的影响。 一般来说，增大这个值可以提高 MRR 的效率，但也会占用更多的内存。

如何选择合适的 read_rnd_buffer_size？

没有一个固定的答案，需要根据实际情况进行测试和调整。 以下是一些建议：

• 从小到大尝试： 从默认值开始，逐渐增大 read_rnd_buffer_size，每次增大一点，然后测试查询性能。
• 监控内存使用： 确保增大的 read_rnd_buffer_size 不会导致内存不足。
• 关注 I/O 指标： 监控磁盘 I/O 的指标，例如 I/O 等待时间、I/O 请求数量等。 如果增大的 read_rnd_buffer_size 能够显著降低 I/O 指标，就说明效果比较好。
• 考虑并发： 在高并发的场景下，需要考虑多个查询同时使用 MRR 缓冲区，适当降低单个查询的 read_rnd_buffer_size，避免过度占用内存。

七、 总结

MRR 是一种非常有用的优化策略，它可以将随机读转换为顺序读，从而提高查询性能。 但是，MRR 并非万能的，它只在特定的场景下才能发挥作用。 在使用 MRR 之前，需要仔细评估其适用性，并根据实际情况进行配置和调优。

简单总结一下：

特性	描述
核心思想	将随机读转换为顺序读，减少磁盘寻道时间。
适用场景	二级索引查询，范围查询，需要回表的查询。
限制	主键索引查询，没有二级索引，JOIN 操作，空间索引，InnoDB Fulltext索引。
相关参数	optimizer_switch, read_rnd_buffer_size (或 mrr_buffer_size)。
配合使用的优化	Index Condition Pushdown (ICP)。
调优建议	从小到大尝试 read_rnd_buffer_size，监控内存使用和 I/O 指标，考虑并发情况。

希望今天的讲座能帮助大家更好地理解和使用 MRR。 记住，优化是一个持续的过程，需要不断学习和实践。 感谢大家的聆听！