MySql5.6新特性之ICP、MRR、BKA

以你之姓@ 2022-05-26 06:14 204阅读 0赞

[原文地址点击此处][Link 1]

# MySql5.6新增了一些提升查询排序和关联查询的性能的内容 #

## 一、先说结论 ##

### 1.1 ICP（Index Condition Pushdown） ###

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

### 1.2 MRR（Multi-Range Read） ###

是基于辅助/第二索引的查询，减少随机IO，并且将随机IO转化为顺序IO，提高查询效率。将原先需要以IO获取数据的方式，转变为尽量从buffer(read\_rnd\_buffer\_size设置buffer大小)中获取数据。然后排序操作是在buffer中进行，提高了效率。

### 1.3 BKA （Batched Key Access） ###

也是引入了一个叫“join buffer”的缓存，来加速表连接的查询速度

## 二、逐个说明    ##

### 2.1 Index Condition Pushdown(ICP)    ###

Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式，从mysql5.6开始支持，mysql5.6之前，存储引擎会通过遍历索引定位基表中的行，然后返回给Server层，再去为这些数据行进行WHERE后的条件的过滤。mysql 5.6之后支持ICP后，如果WHERE条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和 Server层访问存储引擎的次数。

ICP的目标是减少从基表中读取操作的数量，从而降低IO操作

对于InnoDB表，ICP只适用于辅助索引

当使用ICP优化时，执行计划的Extra列显示Using indexcondition提示

数据库配置 optimizer\_switch="index\_condition\_pushdown=on”;

使用场景举例

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

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

若不使用ICP：则是通过二级索引中a的值去基表取出所有a='12345'的数据，然后server层再对b LIKE '%xx%'AND c LIKE '%yy%' 进行过滤

若使用ICP：则b LIKE '%xx%'AND c LIKE '%yy%'的过滤操作在二级索引中完成，然后再去基表取相关数据

ICP特点

mysql 5.6中只支持MyISAM、InnoDB、NDB cluster

mysql5.6中不支持分区表的ICP，从MySQL 5.7.3开始支持分区表的ICP

ICP的优化策略可用于range、ref、eq\_ref、ref\_or\_null类型的访问数据方法

不支持主建索引的ICP（对于Innodb的聚集索引，完整的记录已经被读取到Innodb Buffer，此时使用ICP并不能降低IO操作）

当SQL使用覆盖索引时但只检索部分数据时，ICP无法使用

ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

### 2.2 Multi-Range Read (MRR)    ###

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

MRR优化的几个好处

使数据访问有随机变为顺序，查询辅助索引是，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找

减少缓冲池中页被替换的次数

批量处理对键值的操作

在没有使用MRR特性时

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

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

第二步 通过第一步获取的主键来获取对应的值

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

使用MRR特性时

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

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

第二步 将结果集rest放在buffer里面(read\_rnd\_buffer\_size大小直到buffer满了)，然后对结果集rest按照pk\_column排序，得到结果集是rest\_sort

第三步 利用已经排序过的结果集，访问表中的数据，此时是顺序IO.

select non_key_column fromtb where pk_column in (rest_sort)

在不使用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）最终会根据这些条件进行过滤

相关参数

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

## 三、总结 ##

ICP（Index Condition Pushdown）

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

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操作，提高查询效率。

*Nested Loop Join算法*

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

*Block Nested-Loop Join算法*

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

*Batched Key Access算法*

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

参考：

http://dev.mysql.com/doc/refman/5.7/en/select-optimization.html

http://www.kancloud.cn/taobaomysql/monthly/117959

http://www.kancloud.cn/taobaomysql/monthly/67181

http://www.cnblogs.com/zhoujinyi/p/4746483.html

[Link 1]: http://www.zhimengzhe.com/shujuku/MySQL/274115.html