Multi-Range Read (MRR)
MRR 的全称是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段,这对IO-bound类型的SQL语句性能带来极大的提升,适用于range ref eq_ref类型的查询
MRR优化的几个好处
使数据访问有随机变为顺序,查询辅助索引是,首先把查询结果按照主键进行排序,按照主键的顺序进行书签查找
减少缓冲池中页被替换的次数
批量处理对键值的操作
在没有使用MRR特性时
第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合,结果集为rest
1 |
|
第二步 通过第一步获取的主键来获取对应的值
1 2 |
|
使用MRR特性时
第一步 先根据where条件中的辅助索引获取辅助索引与主键的集合,结果集为rest
1 |
|
第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了),然后对结果集rest按照pk_column排序,得到结果集是rest_sort
第三步 利用已经排序过的结果集,访问表中的数据,此时是顺序IO.
1 |
|
在不使用 MRR 时,优化器需要根据二级索引返回的记录来进行“回表”,这个过程一般会有较多的随机IO, 使用MRR时,SQL语句的执行过程是这样的:
-
优化器将二级索引查询到的记录放到一块缓冲区中
-
如果二级索引扫描到文件的末尾或者缓冲区已满,则使用快速排序对缓冲区中的内容按照主键进行排序
-
用户线程调用MRR接口取cluster index,然后根据cluster index 取行数据
-
当根据缓冲区中的 cluster index取完数据,则继续调用过程 2) 3),直至扫描结束
通过上述过程,优化器将二级索引随机的 IO 进行排序,转化为主键的有序排列,从而实现了随机 IO 到顺序 IO 的转化,提升性能
此外MRR还可以将某些范围查询,拆分为键值对,来进行批量的数据查询,如下:
SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000AND key_part2 = 10000;
表t上有二级索引(key_part1, key_part2),索引根据key_part1,key_part2的顺序排序。
若不使用MRR:此时查询的类型为Range,sql优化器会先将key_part1大于1000小于2000的数据取出,即使key_part2不等于10000,带取出之后再进行过滤,会导致很多无用的数据被取出
若使用MRR:如果索引中key_part2不为10000的元组越多,最终MRR的效果越好。优化器会将查询条件拆分为(1000,1000),(1001,1000),... (1999,1000)最终会根据这些条件进行过滤
相关参数
当mrr=on,mrr_cost_based=on,则表示cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化
当mrr=on,mrr_cost_based=off,则表示总是开启MRR优化
1 |
|
参数read_rnd_buffer_size 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满,则使用快速排序对缓冲区中的内容按照主键进行排序