InnoDB ICP、MRR、BAK特性

一、Index Condition Pushdown(ICP)

Index Condition Pushdown(ICP)是MySQL使用索引从表中索引行检索行数据的一种方式，从MySQL5.6开始支持。

1.1 解决了什么问题

在MySQL5.6之前，存储引擎通过编译索引定位基表中的行，然后返回给Server层，再去这些数据行进行WEHERE后的条件过滤。在MySQL5.6后支持ICP后，如果WHERE条件可以使用索引，MySQL会把这部分的过滤操作放在存储引擎层，把满足的行从表中读取出。ICP能够减少引擎层访问基表的次数和Server层访问存储引擎的次数。

1.2 使用场景举例

辅助索引INDEX(a, b, c)

SELECT * FROM peopleWHERE a='12345' AND b LIKE '%xx%'AND c LIKE '%yy%';

若不使用ICP:则通过二级索引中的a='12345’条件，去基表中取出所有a='12345’的数据，然后在Server层对b LIKE '%xx%'AND c LIKE '%yy%'进行过滤。
若使用ICP：则b LIKE '%xx%'AND c LIKE '%yy%'的过滤操作，在二级索引中就完成了过滤，然后再去取基表中的数据。

1.3 ICP特点

1）ICP的目标是减少从基表中读取操作的数量，从而降低IO操作
2）对于InnoDB表，ICP只适用于辅助索引
3）当使用ICP优化时，执行计划的Extra列显示Using indexcondition提示
4）数据库配置 optimizer_switch="index_condition_pushdown=on”;
5）ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法
6）不支持主建索引的ICP（对于Innodb的聚集索引，完整的记录已经被读取到Innodb Buffer，此时使用ICP并不能降低IO操作）
7）当 SQL 使用覆盖索引时但只检索部分数据时，ICP 无法使用
8）ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

二、Multi-Range Read(MRR)

MRR全称Multi-Range Read,是优化器将随机IO转化为顺序IO,以降低查询过程中IO开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用range ref eq_ref类型的查询。

2.1 MRR的好处

1）使数据访问从随机访问变成顺序访问，查询辅助索引时，首先先把查询结果按照主键进行排序，按照主键的顺序进行书签查找；
2）减少缓冲池页被替代的次数；
3）批量处理对键值的操作；

2.2 不使用MRR时

1）第一步，先根据WHERE条件中的辅助索引获取索引与主键的集合，结果为rest

select key_column, pk_column from tb where key_column=x order by key_column

2）第二步，根据第一步获取的主键来获取对应的值

for each pk_column value in rest do:
select non_key_column from tb where pk_column=val

2.3 使用MRR时

1）第一步，根据条件中WHERE中的辅助索引获取辅助索引与主键的集合，结果为rest

select key_column, pk_column from tb where key_column = x order by key_column

2）第二步，将结果集rest放在buffer里面（read_rnd_buffer_size大小直到buffer满了），然后对结果集rest按照pk_column排序，得到结果集是rst_sort

3）利用已经排过序的结果集，访问表的数据，此时是顺序IO

select non_key_column fromtb where pk_column in (rest_sort)

2.4 不使用MRR 和 使用MRR的区别

1）在不使用MRR时，优化器需要根据二级索引返回的记录进行“回表”，这个过程一般会有较多的随机IO；
2）使用MRR时：

• 优化器将二级索引查询到的数据放置在一块缓冲区；
• 如果二级索引查询完毕或者缓冲区已满，则使用快速排序对缓冲区的内容按照主键排序；
• 用户线程调用MRR接口取cluster index（聚簇索引）,然后根据cluster index取行数据；
• 当根据缓冲区的聚簇索引取完数据，则继续调用2）3）过程，直至扫描结束；
  通过上述过程，优化器将二级索引随机的IO进行排序，转化为主键的有序排列，从而实现了随机IO到顺序IO;

2.5 MRR范围查询，拆分键值对，进行批量查询

MRR还可以用于范围查询，拆分成键值对，来进行批量的数据查询，如下：

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 < 2000 AND key_part2 = 10000;

表t上有二级索引（key_part1, key_part2）,索引根据key_part1,key_part2的顺序排序。
1）若不使用MRR:此时查询的类型为Range,SQL优化器会将key_part1大于1000小于2000的数据全部取出来，即使key_part2不等于10000，取出之后再进行过滤，会取出很多无用的数据；

2）若使用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优化

SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

参数read_rnd_buffer_size 用来控制键值缓冲区的大小。二级索引扫描到文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序

三、Batched Key Access(BKA)和Block Nested-Loop(BNL)

Batched Key Access (BKA) 提高表join性能的算法。当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表，听起来和MRR类似，实际上MRR也可以想象成二级索引和 primary key的join
如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)

3.1 BKA原理

对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目的是较为顺序）MRR使得查询更有效率。
大致的过程如下:

• BKA使用join buffer保存由join的第一个操作产生的符合条件的数据
• 然后BKA算法构建key来访问被连接的表，并批量使用MRR接口提交keys到数据库存储引擎去查找查找。
• 提交keys之后，MRR使用最佳的方式来获取行并反馈给BKA
• BNL和BKA都是批量的提交一部分行给被join的表，从而减少访问的次数，那么它们有什么区别呢
• BNL比BKA出现的早，BKA直到5.6才出现，而NBL至少在5.1里面就存在。
• BNL主要用于当被join的表上无索引
• BKA主要是指在被join表上有索引可以利用，那么就在行提交给被join的表之前，对这些行按照索引字段进行排序，因此减少了随机IO，排序这才是两者最大的区别，但是如果被join的表没用索引呢？那就使用NBL

3.2 BKA和BNL标识

Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)
相关参数
BAK使用了MRR，要想使用BAK必须打开MRR功能，而MRR基于mrr_cost_based的成本估算并不能保证总是使用MRR，官方推荐设置mrr_cost_based=off来总是开启MRR功能。打开BAK功能(BAK默认OFF)：

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BKA使用join buffer size来确定buffer的大小，buffer越大，访问被join的表/内部表就越顺序。
BNL默认是开启的，设置BNL相关参数：

SET optimizer_switch=’block_nested_loop’

支持inner join, outer join, semi-join operations,including nested outer joins
BKA主要适用于join的表上有索引可利用，无索引只能使用BNL

四、总结

4.1 ICP（Index Condition Pushdown）

Index Condition Pushdown是用索引去表里取数据的一种优化，减少了引擎层访问基表的次数和Server层访问存储引擎的次数，在引擎层就能够过滤掉大量的数据，减少io次数，提高查询语句性能

4.2 MRR（Multi-Range Read）

是基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。

• 不使用MRR之前(MySQL5.6之前)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)，随机主键不在同一个page里时会导致多次IO和随机读。
• 使用MRR优化(MySQL5.6之后)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。

4.3 Nested Loop Join算法

将驱动表/外部表的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次，效率非常差。

4.4 Block Nested-Loop Join算法

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。主要用于当被join的表上无索引。

4.5 Batched Key Access算法

当被join的表能够使用索引时，就先好顺序，然后再去检索被join的表。对这些行按照索引字段进行排序，因此减少了随机IO。如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)。

参考:
https://www.cnblogs.com/chenpingzhao/p/6720531.html