MySQL MRR 索引检索优化之 key/rowid 排序

DS-MRR

• mrr function calls

• Key-Ordered Retrieval
  关键思想：如果 MRR 扫描类似于：

  tbl.key=value1 OR tbl.key=value2 OR ... OR tbl.key=valueN  
  

  那么将要查询的条件进行收集并且排序，例如：

   sort(value1, value2, .. valueN)  
  

  然后按照索引顺序进行索引检索，就会减少的索引页 page 的 fetch 操作。

• Rowid-Ordered Retrieval
  如果做一个索引的常规扫描或者一系列索引的扫描，那么检索到记录将会是一个随机 IO。基于磁盘的引擎，这将远远慢于按磁盘顺序检索读取记录。假设记录在磁盘上的顺序是以 rowid 的顺序进行组织的（handler::cmp_ref()提供比较）。

  如果要按照不同的顺序进行检索记录，必须区分索引扫描和记录读取，为了达到这个目录，MRR 扫描有以下步骤原则：
  1 扫描索引（只是索引，HA_EXTRA_KEYREAD），然后用记录的{rowid，range_id}来填充一个 buffer。
  2 按照 rowid 对 buffer 进行排序。
  3 对于 buffer 中的每一对{rowid, range_id}，根据 rowid 获取记录并且返回{record, range_id}记录对。
  4 重复以上步骤，直到扫描完了一个 range。

• 缓冲区空间管理注意事项
  关于缓冲区/内存管理，MRR接口指定:

  • SQL 层提供 multi_range_read_init 和一块指定大小的缓存区。
  • MRR 实现可能会使用（即直到 MRR 扫描结束）所有缓存区，或返回未使用的缓存区回给 SQL 层。

  DS-MRR 需要缓存区以排序 rowid 或者索引键。至于什么情况下需要排序 rowid 或索引键，条件是相当琐碎的。

  当我们需要对键和rowid进行排序时，使用缓存将会变得比较复杂。需要：

  • 首先，收集 keys 然后对其进行排序；
  • 然后使用 keys（较小的值排在最前面）获取行rowid。在获得完匹配的 rowid 之后， keys 就不再需要了。
  • 确保使得 rowid 在缓冲区的前面，以便我们
    可以将缓冲区的结尾部分返回到SQL层，以使很少的 rowid 值占用缓冲区。

  通过使用以下方案可以实现所有这些目标：

  
   |                    |   从SQL层获取一块空缓存；   
  
   |                  *-|    
   |               *----|   首先，用 keys 值填充 buffer，Key_buffer 部分
   |            *-------|   从 buffer 尾部向前涨
   |         *----------|   (这个示例图中，buffer 大的足够放下所有 key，
   |      *-------------|    放完了所有key值后，还有剩余空间)
  
   |      *=============|   我们想要做 key-ordered index 扫描, 所以进行排序
  
   |-x      *===========|   然后使用 keys 来获取 rowids. Rowids 以
   |----x      *========|   buffer space 的头部向尾部增长的方式存储。
   |--------x     *=====|   buffer 中的部分 keys 内容逐渐可以进行释放，
   |------------x   *===|   以释放空间给 rowids 存储. 在这个示例图中，
   |--------------x   *=|   我们所有的 keys 都释放完后，
   |----------------x   |   buffer 空间还有剩余。
  
   |================x   |   然后对 rowids 进行排序.
                   
   |                |~~~|   buffer 未使用的部分在尾部, 所以
                            我们可以将其释放回给 SQL 层.
  
   |================*       排序后的 rowids 被用来按磁盘顺序来读取 table records