概叙

科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch】-CSDN博客

科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch--ICP索引下推】

MySQL :: MySQL 8.0 Reference Manual :: 10.2.1.11 Multi-Range Read Optimization

前面讲了MySQL查询优化器、以及ICP索引下推，今天接着说MRR。

启动MRR时，优化器会对索引进行排序，然后按照主键ID的顺序进行遍历。这样可以减少磁盘随机读取的次数，提高查询性能。

官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了优化器策略。

什么是MRR？

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

select * from tb where key_column = x

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

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

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

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

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

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

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

两者的区别主要是两点：

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

没有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 配置的上限，如此循环。

MRR 要解决的问题

MRR 是 MySQL 针对特定查询的一种优化手段。假设一个查询有二级索引可用，读完二级索引后要回表才能查到那些不在当前二级索引上的列值，由于二级索引上引用的主键值不一定是有序的，因此就有可能造成大量的随机 IO，如果回表前把主键值给它排一下序，那么在回表的时候就可以用顺序 IO 取代原本的随机 IO。

MRR 实战 

环境准备

为了实验我们要准备一下表结构和数据。

-- 创建表
mysql> show create table t;
+----------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT,
  `i0` int NOT NULL,
  `i1` int NOT NULL,
  `i2` int NOT NULL,
  `i3` int NOT NULL,
  `c0` varchar(128) NOT NULL,
  `c1` varchar(128) NOT NULL,
  `f0` float NOT NULL,
  `f1` float NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_i0` (`i0`)
) ENGINE=InnoDB
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 造数据
mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|  1120000 |
+----------+
1 row in set (0.77 sec)

--
update t set i0 = id % 100;

示例一：MRR 的优化效果 mrr=off

1. 有 MRR 优化（Using MRR）时 SQL 的耗时情况。

mysql> explain select i0,i3 from t where i0 between 1 and 2;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_i0        | idx_i0 | 4       | NULL | 43968 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select i0,i3 from t where i0 between 1 and 2;
22400 rows in set (0.80 sec)

2. 关闭 MRR 优化。

set optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=off,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on';

mysql> explain select i0,i3 from t where i0 between 1 and 2;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_i0        | idx_i0 | 4       | NULL | 43968 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select i0,i3 from t where i0 between 1 and 2;
22400 rows in set (2.56 sec)

示例一：MRR小结

上面实验可以得出结论：开启MRR时sql执行耗时0.8s，关闭时耗时2.56s，开启 MRR 优化可以得到 3 倍的性能提升。

示例二：MRR 的优化器参数调整mrr_cost_based

如果想关闭 MRR 优化的话，就要把优化器开关 mrr 设置为 off。

默认只有在优化器认为 MRR 可以带来优化的情况下才会走 MRR，如果你想不管什么时候能走 MRR 的都走 MRR 的话，你要把 mrr_cost_based 设置为 off，不过最好不要这么干，因为这确实是一个坑，MRR 不一定什么时候都好，全表扫描有时候会更加快，如果在这种场景下走 MRR 就完成了。

MRR 关闭基于开销的优化

-- mrr=on,mrr_cost_based=off
set optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on';

mysql> explain select i0,i3 from t where i0 between 1 and  10;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_i0        | idx_i0 | 4       | NULL | 218492 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

select i0,i3 from t where i0 between 1 and  10;
112000 rows in set (4.86 sec)

MRR 开启基于开销的优化

-- mrr=on,mrr_cost_based=on
set optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on';

mysql> explain select i0,i3 from t where i0 between 1 and  10;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_i0        | NULL | NULL    | NULL | 1121902 |    19.48 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select i0,i3 from t where i0 between 1 and  10;
112000 rows in set (1.52 sec)

示例二：mrr_cost_based小结

可以看到当 mrr_cost_based = OFF 的情况下用时 4.86s，mrr_cost_based = ON 的情况下用时 1.52s，总的来说 mrr_cost_based 是非常关键的建议始终打开。

示例三：MRR 的参数优化 read_rnd_buffer_size

MRR 要把主键排个序，这样之后对磁盘的操作就是由顺序读代替之前的随机读。从资源的使用情况上来看就是让 CPU 和内存多做点事，来换磁盘的顺序读。然而排序是需要内存的，这块内存的大小就由参数 read_rnd_buffer_size 来控制。

read_rnd_buffer_size 太小无法启用 MRR 功能

mysql> select @@read_rnd_buffer_size;
+------------------------+
| @@read_rnd_buffer_size |
+------------------------+
|                 262144 |
+------------------------+
1 row in set (0.00 sec)

mysql> explain select i0,i3 from t where i0 between 1 and  12;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_i0        | NULL | NULL    | NULL | 1121902 |    23.57 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

放大 read_rnd_buffer_size 让 MySQL 有足够的资源用于 MRR

mysql> set read_rnd_buffer_size = 32 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select i0,i3 from t where i0 between 1 and  12;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_i0        | idx_i0 | 4       | NULL | 264436 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

示例三：read_rnd_buffer_size小结

1. 参数read_rnd_buffer_size用来控制键值的缓冲区大小，会先读区一页的数据，或者需要的数据在buffer中进行排序，然后，根据顺序进行读取，防止磁盘随机读取。
2. read_rnd_buffer_size ，是用来设置用于给 rowid 排序的内存的大小，显然， MRR 在本质上是一种用空间换时间的算法 ，MySQL 不可能给你无限的内存来进行排序，如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。
3. 是否启用MRR优化，可以通过参数optimizer_switch中的flag来控制。当MRR为on时，表示启用MRR优化。mrr_cost_based表示是否通过costbased的方式来选择是否启用mrr。若设置mrr=on,mrr_cost_based=off，则总是启用MRR优化。如下：
4. 和 MRR 相关的配置 有两个：
   1. 更好的适用于范围查询
   2. mrr: onoff
   3. mrr_cost_based: onoff 是否每条都使用mrr off就是都使用，on由sql优化器决定