目录

  • 第八章 优化（十二）—— 多范围读优化
  • 8.2 优化SQL语句
  • 8.2.1 优化 SELECT 语句
  • 8.2.1.11 多范围读优化


第八章 优化（十二）—— 多范围读优化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.11 多范围读优化

当基表很大且没有存储在存储引擎的缓存中时，通过使用辅助索引进行范围扫描，从而读取行可能会导致对基表多次随机磁盘访问（译者：通过辅助索引可以快速定位整行数据在基表中的位置，如果是多范围读，可能会导致多次回表进行随机磁盘访问）。通过磁盘扫描的多范围读（Multi-Range Read --> MRR）优化，MySQL在进行范围扫描时试图减少对随机磁盘访问的次数。其方法是，首先仅扫描索引和收集相关行的键，然后对键集合进行排序，最后使用主键的顺序从基表中检索行。磁盘扫描多范围读优化的初衷是减少随机磁盘访问的次数，而实际上实现了对基表数据更顺序地扫描。

多范围读优化具有以下优点：

  • MRR允许基于索引元组按顺序访问数据行，而不是按随机顺序进行操作。服务器先获取一组满足查询条件的索引元组，然后按照数据行ID的顺序对它们进行排序，并使用排序后的元组按顺序检索数据行。这使得数据访问更高效，成本更低。
  • MRR支持通过索引元组对需要访问数据行的操作所需的键访问的请求进行批量处理，例如范围索引扫描和使用索引作为连接属性的等值连接。MRR对一系列索引范围进行迭代以获得符合条件的索引元组。随着这些结果的积累，这些元组被用来访问相应的数据行。在开始读取数据行之前，不必获取所有的索引元组。

在虚拟生成的列上创建辅助索引不支持MRR优化。InnoDB支持在虚拟生成的列上创建辅助索引。

以下场景说明了MRR优化的优势：

场景A：MRR可用于InnoDB和MyISAM表，用于索引范围扫描和等值连接操作。

  1. 索引元组的一部分累积在缓冲区中。
  2. 缓冲区中的索引元组按其数据行ID排序。
  3. 根据排序后的索引元组顺序访问数据行。

场景B：MRR可用于NDB表，用于多范围索引扫描或通过属性执行等值联接。

  1. 部分范围（可能是单键范围）累积在提交查询的中心节点中的缓冲区中。
  2. 这些范围被发送到访问数据行的执行节点。
  3. 访问到的行被打成包并发送回中心节点。
  4. 接收到的带有数据行的包被放置在缓冲区中。
  5. 从缓冲区读取数据行。

如果使用了MRR，则在EXPLAIN输出的Extra列上提示 Using MRR。

如果不需要访问表中完整的行来生成查询结果，InnoDB和MyISAM就不会使用MRR。如果结果可以完全基于索引元组中的信息（通过覆盖索引）生成，则属于这种情况，此时，MRR没有任何优势。

有两个optimizer_switch系统变量标志为如何使用MRR优化提供了接口。其中一个标志mrr用来控制是否启用MRR。如果启用了mrr（设置为on），另一个标志 mrr_cost_based（基于成本开销决定是否使用mrr）控制优化器是否尝试在mrr处于开的情况下使用还是不使用MRR，或在mrr处于关的情况下尽可能使用MRR，做出基于成本的选择。缺省情况下，mrr和 mrr_cost_based两个标志都是开的（on）。请参见 8.9.2 节，“可切换优化”。

对于MRR，存储引擎使用read_rnd_buffer_size（译者：随机读缓冲区大小）系统变量的值作为它可以为其缓冲区分配多少内存的指导原则。引擎最多使用read_rnd_buffer_size字节，并确定在一次传递中要处理的范围的数量。