Muti-Range Read（MRR）优化
zhaolengquan Lv3
  2022-10-09 20:53:18 2022-10-09 20:53    924 Words  3 Mins  

MySQL5.6版本开始支持MRR优化，目的是为了减少磁盘访问，并将磁盘随机访问转化为较为顺序的数据访问。这对IO-bound型的SQL查询语句可带来性能极大的提升。MRR优化可适用于range ref eq_ref类型的查询

IO bound：如果主要时间消耗在等待读写数据而CPU利用率很低，那么该任务就是I/O-bound，I/O束缚型。

先来一个范围查询

1
2
3
4
5
6
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 |
+----+-------------+-------+-------+----------------+------+------+-----------------------+

img

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

左边是字段age的二级索引，右边是存储完整行记录的地方（回表操作）。

先到左边的二级索引找，找到第一条符合条件的记录，接着到右边去读取这条数据的完整记录，读取完后回到左边继续找下一条符合条件的记录，然后到右边读取，这时发现这条数据和上条数据在物理存储位置上离得非常远！

为了读取记录只能让磁盘和磁头一起运动去读取这条记录。

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

MySQL是以页为单位读取数据的，这里假设这几条数据都恰好位于不同的页上，另外，页的思想来源于操作系统的非连续内存管理机制，类似的还有段。

开启MRR重新执行SQL语句

1
2
3
4
5
6
7
8
9
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 |
+----+-------------+-------+-------+------+---------+------+------+----------------+

img

对于innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

顺序读带来的好处

  1. 磁盘和磁头不再来回的做机械运动
  2. 可以充分使用磁盘预读。

比如客户端请求一页的数据时，可以吧后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不需要到磁盘读取，这样做的理论依据是计算机科学中著名的局部性原理

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

  1. 在一次查询中，每一页的数据只会从磁盘读取一次

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

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

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

索引本身就是为了减少磁盘IO，加快查询，MRR则是把索引减少磁盘IO的作用进一步放大。

 Comments