mysql mrr_cost_based_MySQL MRR介绍

这个文章的原始出处找不到了。

什么是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 |

+----------------------+---------+