ICP、MRR 和 BKA

1、ICP

Index Condition Pushdown (ICP)是 MySQL使用索引从表中检索行数据的一种优化方式。

从MySOL5.6开始支持，5.6之前，存储引警会通过遍历索引定位基表中的行，然后返回给 Server层，再去为这些数据行进行 WHERE 后的条件的过滤。

MySQL5.6之后支持ICP，如果 WHERE条件可以使用索引，MySOL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。

ICP 能减少引层访问基表的次数和 Server 层访问存储引擎的次数。

MySOL通过optimizer_switch参数中的 index_condition_pushdow 选项来控制,默认是开启
的

show variables like  '%optimizer_switch%';

可以通过数据库命令行执行启动或关闭操作：

set optimizer_switch="index_condition_pushdown=on|off";

当使用ICP优化时，执行计划（explain）的extra列会显示 Using index condition 的关键字提示。

2、MRR

MRR 的全称是 Multi-Range Read Optimization，MySQL的MRR特性也是5.6之后才有的。

通过 optimizer_switch 参数中两个重要的选项来控制，一个是 mrr，另一个是 mrr_cost_based。

参数值默认也是开启状态的（mrr=on 和 mrr_cost_based=on）。

mrr_cost_based 选项：表示是否通过基于成本的算法来确定开启mrr 特性；设置为 on 表示自行判断，要是为 off 则表示强制开启 mrr。

可以通过数据库命令行执行启动或者关闭操作:

SET global optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

注：当mrr=on、mrr_cost_based=on 时，表示cost base 的方式还选择启用 MRR优化当发现优化后的代价过高时就会不使用该项优化。

当mrr=on、mrr_cost_based=off 时，表示总是开启MRR优化。

当使用MRR优化时，执行计划的 extra 列会显示 Using的关键字提示。

（1）MRR 的原理：

MySQL 普通索引获取数据的方式是：通过索引页的叶子结点找到对应的主键，再通过主键找到相对应的行数据记录。

如果在一张表中对某一个字段创建一个普通索引，但这个字段有一些重复的值，那么根据这个字段去做 where 条件时，每次取到的主键值可能不是按顺序的，那么随机I/0 的行为就会发生。

MRR 原理如图6-7 所示(图片来自MariaDB官方文档)。

（2）MRR 的作用：

把普通索引的叶子结点上找到的主键值的集合存储到 read_rnd_buffer 中然后在该 buffer 中对主键值进行排序，最后再利用已经排序好的主键值的集合，去访问表中的数据，这样就由原来的随机 I/O 变成了顺序 I/O，降低了查询过程中的 I/O 开销。

注：在生产环境中，read_rnd_buffer_size 的值可以在4~8MB 之间调整。

3、BKA

BKA的全称为 Batched Key Access。它是提高表 join 性能的算法,其作用是在读取被 join 表的记录的时候使用顺序 I/O。

BKA 的原理:

对于多表 join 语句，当 MySQL 使用索引访问第二个join 表时，使用一个join buffer 来收集第一个操作对象生成的相关列值。

BKA构建好 key 后，批量传给引擎层做索引查找。key 是通过MRR 接口提交给引擎的，这样一来MRR 使得查询更加高效。

BKA的原理如图(图片来自MariaDB 官方文档)。

MySQL中通过 optimizer_switch 参数中的 batched_key_access 选项来控制该选项默认是关闭的。

show variables like '%optimizer_switch%';

要想开启该参数，必须先要保证是在强制使用 MRR 的基础上才可以。

执行如下操作才算开启BKA功能:

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

当BKA被使用时，执行计划的extra 列会显示 Using join buffer(Batched Key Access)的关键字提示。