MySQL机制介绍之MRR

发表于 | 分类于 |

什么是MRR

Multi-Range Read Optimization，是优化器将随机IO转换成顺序IO以降低查询过程中IO开销的一种手段。
它的好处有：

  • 使数据访问由随机变为顺序
  • 减少缓冲池中页被替换的次数
  • 批量处理查询操作

可以通过set optimizer_switch='mrr=on';命令进行开启。

不使用MRR

1
2
3
4
5
6
7
8
9
10
mysql> set optimizer_switch='mrr=off';
Query OK, 0 rows affected (0.00 sec)

mysql>  explain select * from test.t1 where (a between 1 and 10) and (c between 9 and 10) ;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
|  1 | SIMPLE      | t1    | range | mrrx,xx       | xx   | 5       | NULL |    2 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
1 row in set (0.00 sec)

在不使用MRR时，优化器需要根据二级索引返回的记录来进行回表，这个过程一般会有较多的随机IO操作。

使用MRR

1
2
3
4
5
6
7
8
9
10
mysql> set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.00 sec)

mysql>  explain select * from test.t1 where (a between 1 and 10) and (c between 9 and 10) ;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                         |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
|  1 | SIMPLE      | t1    | range | mrrx,xx       | xx   | 5       | NULL |    2 | Using index condition; Using where; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
1 row in set (0.00 sec)

在使用了MRR时，SQL语句的执行过程为：

  1. 优化器将二级索引查询到的记录放到一块缓冲区中(read_rnd_buffer_size)
  2. 如果二级索引扫描到文件末尾或者缓冲区已满，则使用快排对缓冲区中的内容按照主键进行排序
  3. 用户线程调用MRR接口获取cluster index，然后根据cluster index获取行数据
  4. 当缓冲区中的cluster index取完数据，则继续调用过程2、3，直到扫描结束

总结

MRR特性就是在查询过程中，先将满足条件的id查询出来并进行排序后，再进行批量查询操作。从而实现随机IO到顺序IO的转换，提升性能。

https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html
https://zhuanlan.zhihu.com/p/110154066