MRR和ICP

MRR和ICP

• Multi-Range Read优化
• ICP索引下推优化

Multi-Range Read优化

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

在这里插入图片描述

根据二级索引定位到的一批记录，对应的主键集合是无序的，根据这个无序主键集合回到聚簇索引进行查询，显然会产生大量的随机IO,为了降低随机IO带来的损耗，可以考虑先对主键集合进行排序，这样再根据主键集合进行回表查询，就能尽可能产生的是顺序IO了。

MRR优化有以下几个好处：

• MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
• 减少缓冲池中页被替换的次数。
• 批量处理对键值的查询操作。

对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：

• 将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
• 将缓存中的键值根据RowID进行排序。
• 根据RowID的排序顺序来访问实际的数据文件。

此外，若InnoDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降到最低。

如下面这条sql语句:

在这里插入图片描述

若启用了Mulit-Range Read特性，则除了会在列Extra看到Using index condition外，还会看见Using MRR选项。

此外，Multi_Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。

这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如：

在这里插入图片描述

倘若启用了Multi_Range Read优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（2，1）， （3， 1）, . . . ， （19， 1），最后再根据这些拆分出的条件进行数据的查询。

通过将查询条件进行拆分，可以避免取出大量无用的数据。

是否启用Multi_Range Read优化可以通过参数optimizer_switch中的标记(flag)来控制。当mrr为on时，表示启用Multi_Range Read优化。mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。若将mrr设为on， mrr_cost_based设为off，则总是启用Multi_Range Read优化。例如，下述语句可以将Multi_Range Read优化总是设为开启状态：

set optimizer_switch='mrr=on,mrr_cost_based=off';

参数 read＿rnd＿buffer＿size用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256K:

在这里插入图片描述

ICP索引下推优化

和Multi_Range Read—样， Index Condition Pushdown 同样是 MySQL 5.6 开始支持的种根据索引进行查询的优化方式。

之前的MySQL数据库版本不支持Index. ConditionPushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。

在支持Index Condition Pushdown后， MySQL数据库会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。

Index Condition Pushdown 优化支持 range, ref, eq_ref, ref_or_null 类型的查询,当前支持MyISAM和InnoDB存储引擎。当优化器选择Index Condition Pushdown优化时，可在执行计划的列Extra看到Using index condition提示，

当然，Where可以过滤的条件是要该索引可以覆盖到的范围。