MRR 顺序读取

总结

• 参数read_rnd_buffer_size用来控制键值的缓冲区大小，会先读区一页的数据，或者需要的数据在buffer中进行排序，然后，根据顺序进行读取，防止磁盘随机读取
• 是否启用MRR优化，可以通过参数optimizer_switch中的flag来控制。当MRR为on时，表示启用MRR优化。mrr_cost_based表示是否通过costbased的方式来选择是否启用mrr。若设置mrr=on,mrr_cost_based=off，则总是启用MRR优化。如下：
• 和 MRR 相关的配置 有两个：
• 更好的适用于范围查询
  mrr: onoff
  mrr_cost_based: onoff 是否每条都使用mrr off就是都使用，on由sql优化器决定

什么是MRR？

MRR：multi range read。不好解释，先来看个例子：

select * from tb where key_column = x

在没有MRR的情况下，它是这样得到结果的：

1. select key_column, pk_column from tb where key_column=x order by key_column —> 假设这个结果集是t

2. for each row in t ; select non_key_column from tb where pk_column = pk_column_value。(在Oracle里第2步叫回表？)

在有MRR的情况下，它是这样执行的：

1. select key_column, pk_column from tb where key_column = x order by key_column —> 假设这个结果集是t

2. 将结果集t放在buffer里面(直到buffer满了)，然后对结果集t按照pk_column排序 —> 假设排序好的结果集是t_sort

3. select non_key_column fromtb where pk_column in (select pk_column from t_sort)

两者的区别主要是两点：

1. 没有MRR的情况下，随机IO增加，因为从二级索引里面得到的索引元组是有序，但是他们在主键索引里面却是无序的，所以每次去主键索引里面得到non_key_column的时候都是随机IO。（如果索引覆盖，那也就没必要利用MRR的特性了，直接从索引里面得到所有数据）

2. 没有MRR的情况下，访问主键索引的次数也会增加。没有MRR的情况下，二级索引里面得到多少行，那么就要去访问多少次主键索引(也不能完全这样说，因为MySQL实现了BNL)，而有了MRR的时候，次数就大约减少为之前次数t/buffer_size。

所以说MRR主要解决的就是这两个问题。

此外，MRR还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据。

如：

官方文档：https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html

SELECT * FROM t WHERE key_part1 >=1000 AND key_part1 < 2000 AND key_part2 = 1000;

表t有(key_part1,key_part2)的联合索引，因此索引根据key_part1,key_part2的位置关系进行排序。若没有MRR，此时查询类型为Range，SQL优化器会先将key_part1大于1000且小于2000的数据都取出来，即便key_part2不等于1000。取出后再根据key_part2的条件进行过滤。这会导致无用的数据被取出。

如果启用MRR优化器会使性能有巨大的提升，优化器会先将查询条件拆分为(1000,1000),(1001,1000),(1002,1000)…(1999,1000) 最后再根据这些拆分出的条件进行数据的查询。

是否启用MRR优化，可以通过参数optimizer_switch中的flag来控制。当MRR为on时，表示启用MRR优化。mrr_cost_based表示是否通过costbased的方式来选择是否启用mrr。若设置mrr=on,mrr_cost_based=off，则总是启用MRR优化。如下：

SET GLOBAL optimizer_switch=‘mrr=on,mrr_cost_based=off’;

参数read_rnd_buffer_size用来控制键值的缓冲区大小。当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据，该值默认是256KB

show VARIABLES like ‘read_rnd_buffer_size’;

±---------------------±--------+

| Variable_name | Value |

|----------------------±--------|

| read_rnd_buffer_size | 262144 |

±---------------------±--------+

MySQL 里有个东西叫 MRR，全称「Multi-Range Read Optimization」。

简单说，MRR 通过：

把「随机磁盘读」，转化为「顺序磁盘读」
从而：

提高了磁盘读取的性能
至于：

为什么要把随机读转化为顺序读？

怎么转化的？

为什么顺序读就能提升读取性能？

咱们开始吧。

磁盘：苦逼的底层劳动人民
执行一个范围查询：

mysql > explain select * from stu where age between 10 and 20;
±—±------------±------±------±-----±--------±-----±-----±----------------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
±—±------------±------±------±---------------±-----±-----±----------------------+
| 1 | SIMPLE | stu | range | age | 5 | NULL | 960 | Using index condition |
±—±------------±------±------±---------------±-----±-----±----------------------+
当这个 sql 被执行时，MySQL 会按照下图的方式，去磁盘读取数据（假设数据不在数据缓冲池里）：

MySQL 的 MRR 到底是什么？

这张图是按照 Myisam 的索引结构画的，不过对于 Innodb 也同样适用。

对于 Myisam，左边就是字段 age 的二级索引，右边是存储完整行数据的地方。

先到左边的二级索引找，找到第一条符合条件的记录（实际上每个节点是一个页，一个页可以有很多条记录，这里我们假设每个页只有一条），接着到右边去读取这条数据的完整记录。

读取完后，回到左边，继续找下一条符合条件的记录，找到后，再到右边读取，这时发现这条数据跟上一条数据，在物理存储位置上，离的贼远！

咋办，没办法，只能让磁盘和磁头一起做机械运动，去给你读取这条数据。

第三条、第四条，都是一样，每次读取数据，磁盘和磁头都得跑好远一段路。

图中红色线就是整个的查询过程，蓝色线则是磁盘的运动路线。

磁盘的简化结构可以看成这样：

MySQL 的 MRR 到底是什么？

MySQL 的 MRR 到底是什么？

可以想象一下，为了执行你这条 sql 语句，磁盘要不停的旋转，磁头要不停的移动，这些机械运动，都是很费时的。

10,000 RPM 的机械硬盘，每秒大概可以执行 167 次磁盘读取，所以在极端情况下，MySQL 每秒只能给你返回 167 条数据，这还不算上 CPU 排队时间。

上面讲的都是机械硬盘，SSD 的土豪，请随意 - -

对于 Innodb，也是一样的。 Innodb 是聚簇索引（cluster index），所以只需要把右边也换成一颗叶子节点带有完整数据的 B+ tree 就可以了。

顺序读：一场狂风暴雨般的革命
到这里你知道了磁盘随机访问是多么奢侈的事了，所以，很明显，要把随机访问转化成顺序访问：

mysql > set optimizer_switch=‘mrr=on’;
Query OK, 0 rows affected (0.06 sec)

mysql > explain select * from stu where age between 10 and 20;
±—±------------±------±------±-----±--------±-----±-----±---------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
±—±------------±------±------±-----±--------±-----±-----±---------------+
| 1 | SIMPLE | tbl | range | age | 5 | NULL | 960 | …; Using MRR |
±—±------------±------±------±-----±--------±-----±-----±---------------+
我们开启了 MRR，重新执行 sql 语句，发现 Extra 里多了一个「Using MRR」。

这下 MySQL 的查询过程会变成这样：

MySQL 的 MRR 到底是什么？

在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。
顺序读带来了几个好处：

1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

3、在一次查询中，每一页的数据只会从磁盘读取一次
MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。

但是如果不排序，可能你在读取了第 1 页的数据后，会去读取第2、3、4页数据，接着你又要去读取第 1 页的数据，这时你发现第 1 页的数据，已经从缓存中被剔除了，于是又得再去磁盘读取第 1 页的数据。

而转化为顺序读后，你会连续的使用第 1 页的数据，这时候按照 MySQL 的缓存剔除机制，这一页的缓存是不会失效的，直到你利用完这一页的数据，由于是顺序读，在这次查询的余下过程中，你确信不会再用到这一页的数据，可以和这一页数据说告辞了。

顺序读就是通过这三个方面，最大的优化了索引的读取。
别忘了，索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。
尾声
和 MRR 相关的配置 有两个：

mrr: on off
mrr_cost_based: on off
第一个就是上面演示时用到的，用来打开 MRR 的开关：

mysql > set optimizer_switch=‘mrr=on’;
如果你不打开，是一定不会用到 MRR 的。

另一个，则是用来告诉优化器，要不要基于使用 MRR 的成本，考虑使用 MRR 是否值得（cost-based choice），来决定具体的 sql 语句里要不要使用 MRR。

很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR，这在有些情况下是很 stupid 的，所以建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。

另外还有一个配置 read_rnd_buffer_size ，是用来设置用于给 rowid 排序的内存的大小，显然， MRR 在本质上是一种用空间换时间的算法 ，MySQL 不可能给你无限的内存来进行排序，如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。