mysql5.6icpmrrbak_ICP、MRR、BKA等特性

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

对于InnoDB表&＃xff0c;ICP只适用于辅助索引

当使用ICP优化时&＃xff0c;执行计划的Extra列显示Using indexcondition提示

数据库配置 optimizer_switch&＃61;"index_condition_pushdown&＃61;on”;

使用场景举例

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

SELECT * FROM peopleWHERE a&＃61;&＃39;12345&＃39; AND b LIKE &＃39;%xx%&＃39;AND c LIKE &＃39;%yy%&＃39;;

若不使用ICP&＃xff1a;则是通过二级索引中a的值去基表取出所有a&＃61;&＃39;12345&＃39;的数据&＃xff0c;然后server层再对b LIKE &＃39;%xx%&＃39;AND c LIKE &＃39;%yy%&＃39; 进行过滤

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

ICP特点

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

mysql 5.6中不支持分区表的ICP&＃xff0c;从MySQL 5.7.3开始支持分区表的ICP

ICP的优化策略可用于range、ref、eq_ref、ref_or_null 类型的访问数据方法

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

当 SQL 使用覆盖索引时但只检索部分数据时&＃xff0c;ICP 无法使用

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

二、Multi-Range Read (MRR)

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

MRR优化的几个好处

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

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

批量处理对键值的操作

在没有使用MRR特性时

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

select key_column, pk_column from tb where key_column&＃61;x order by key_column

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

for each pk_column value in rest do:

select non_key_column from tb where pk_column&＃61;val

使用MRR特性时

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

select key_column, pk_column from tb where key_column &＃61; x order by key_column

第二步 将结果集rest放在buffer里面(read_rnd_buffer_size 大小直到buffer满了)&＃xff0c;然后对结果集rest按照pk_column排序&＃xff0c;得到结果集是rest_sort

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

select non_key_column fromtb where pk_column in (rest_sort)

在不使用 MRR 时&＃xff0c;优化器需要根据二级索引返回的记录来进行“回表”&＃xff0c;这个过程一般会有较多的随机IO, 使用MRR时&＃xff0c;SQL语句的执行过程是这样的&＃xff1a;

优化器将二级索引查询到的记录放到一块缓冲区中

如果二级索引扫描到文件的末尾或者缓冲区已满&＃xff0c;则使用快速排序对缓冲区中的内容按照主键进行排序

用户线程调用MRR接口取cluster index&＃xff0c;然后根据cluster index 取行数据

当根据缓冲区中的 cluster index取完数据&＃xff0c;则继续调用过程 2) 3)&＃xff0c;直至扫描结束

通过上述过程&＃xff0c;优化器将二级索引随机的 IO 进行排序&＃xff0c;转化为主键的有序排列&＃xff0c;从而实现了随机 IO 到顺序 IO 的转化&＃xff0c;提升性能

此外MRR还可以将某些范围查询&＃xff0c;拆分为键值对&＃xff0c;来进行批量的数据查询&＃xff0c;如下&＃xff1a;

SELECT * FROM t WHERE key_part1>&＃61; 1000 AND key_part1<2000AND key_part2&＃61; 10000;

表t上有二级索引(key_part1, key_part2)&＃xff0c;索引根据key_part1,key_part2的顺序排序。

若不使用MRR&＃xff1a;此时查询的类型为Range&＃xff0c;sql优化器会先将key_part1大于1000小于2000的数据取出&＃xff0c;即使key_part2不等于10000&＃xff0c;带取出之后再进行过滤&＃xff0c;会导致很多无用的数据被取出

若使用MRR&＃xff1a;如果索引中key_part2不为10000的元组越多&＃xff0c;最终MRR的效果越好。优化器会将查询条件拆分为(1000,1000)&＃xff0c;(1001,1000)&＃xff0c;... (1999,1000)最终会根据这些条件进行过滤

相关参数

当mrr&＃61;on,mrr_cost_based&＃61;on&＃xff0c;则表示cost base的方式还选择启用MRR优化,当发现优化后的代价过高时就会不使用该项优化

当mrr&＃61;on,mrr_cost_based&＃61;off&＃xff0c;则表示总是开启MRR优化

SET &＃64;&＃64;optimizer_switch&＃61;&＃39;mrr&＃61;on,mrr_cost_based&＃61;on&＃39;;

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

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

Batched Key Access (BKA)  提高表 join 性能的算法。当被join的表能够使用索引时&＃xff0c;就先排好顺序&＃xff0c;然后再去检索被join的表&＃xff0c;听起来和MRR类似&＃xff0c;实际上MRR也可以想象成二级索引和 primary key的join

如果被Join的表上没有索引&＃xff0c;则使用老版本的BNL策略(BLOCK Nested-loop)

BKA原理

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

大致的过程如下:

BKA使用join buffer保存由join的第一个操作产生的符合条件的数据

然后BKA算法构建key来访问被连接的表&＃xff0c;并批量使用MRR接口提交keys到数据库存储引擎去查找查找。

提交keys之后&＃xff0c;MRR使用最佳的方式来获取行并反馈给BKA

BNL和BKA都是批量的提交一部分行给被join的表&＃xff0c;从而减少访问的次数&＃xff0c;那么它们有什么区别呢&＃xff1f;

BNL比BKA出现的早&＃xff0c;BKA直到5.6才出现&＃xff0c;而NBL至少在5.1里面就存在。

BNL主要用于当被join的表上无索引

BKA主要是指在被join表上有索引可以利用&＃xff0c;那么就在行提交给被join的表之前&＃xff0c;对这些行按照索引字段进行排序&＃xff0c;因此减少了随机IO&＃xff0c;排序这才是两者最大的区别&＃xff0c;但是如果被join的表没用索引呢&＃xff1f;那就使用NBL

BKA和BNL标识

Using join buffer (Batched Key Access)和Using join buffer (Block Nested Loop)

相关参数

BAK使用了MRR&＃xff0c;要想使用BAK必须打开MRR功能&＃xff0c;而MRR基于mrr_cost_based的成本估算并不能保证总是使用MRR&＃xff0c;官方推荐设置mrr_cost_based&＃61;off来总是开启MRR功能。打开BAK功能(BAK默认OFF)&＃xff1a;

SET optimizer_switch&＃61;&＃39;mrr&＃61;on,mrr_cost_based&＃61;off,batched_key_access&＃61;on&＃39;;

BKA使用join buffer size来确定buffer的大小&＃xff0c;buffer越大&＃xff0c;访问被join的表/内部表就越顺序。

BNL默认是开启的&＃xff0c;设置BNL相关参数&＃xff1a;

SET optimizer_switch&＃61;’block_nested_loop’

支持inner join, outer join, semi-join operations,including nested outer joins

BKA主要适用于join的表上有索引可利用&＃xff0c;无索引只能使用BNL

四、总结

ICP(Index Condition Pushdown)

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

MRR(Multi-Range Read)

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

不使用MRR之前(MySQL5.6之前)&＃xff0c;先根据where条件中的辅助索引获取辅助索引与主键的集合&＃xff0c;再通过主键来获取对应的值。辅助索引获取的主键来访问表中的数据会导致随机的IO(辅助索引的存储顺序并非与主键的顺序一致)&＃xff0c;随机主键不在同一个page里时会导致多次IO和随机读。

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