浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
来源:互联网 发布:什么是金蝶软件 编辑:程序博客网 时间:2024/06/06 07:22
另一篇文章介绍了index condition pushdown(ICP) 这篇讲叙的是MRR和与之相关的BKA
什么是MRR?
MRR:multi range read。不好解释,先来看个例子:
select * from tb where key_column = x
在没有MRR的情况下,它是这样得到结果的:
1. select key_column, pk_column from tb where key_column=x order by key_column ---> 假设这个结果集是t
2. for each row in t ; select non_key_column from tb where pk_column = pk_column_value。(在oracle里第2步叫回表?)
在有MRR的情况下,它是这样执行的:
1. select key_column, pk_column from tb where key_column = x order by key_column ---> 假设这个结果集是t
2. 将结果集t放在buffer里面(直到buffer满了),然后对结果集t按照pk_column排序 ---> 假设排序好的结果集是t_sort
3. select non_key_column fromtb where pk_column in (select pk_column from t_sort)
两者的区别主要是两点:
1. 没有MRR的情况下,随机IO增加,因为从二级索引里面得到的索引元组是有序,但是他们在主键索引里面却是无序的,所以每次去主键索引里面得到non_key_column的时候
都是随机IO。(如果索引覆盖,那也就没必要利用MRR的特性了,直接从索引里面得到所有数据)
2. 没有MRR的情况下,访问主键索引的次数也会增加。没有MRR的情况下,二级索引里面得到多少行,那么就要去访问多少次主键索引(也不能完全这样说,因为mysql实现了BNL),而有了MRR的时候,次数就大约减少为之前次数t/buffer_size。
所以说MRR主要解决的就是这两个问题。
那么看看使用了MRR的explain:
mysql> explain select c from test1 wherek in(25054, 24781, 23054, 25207, 25020);
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+
| 1| SIMPLE | test1 | range | k | k | 4 | NULL | 329 | Using where; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------+
没有使用MRR的explain:
mysql> explain select c from test1 wherek in(25054, 24781, 23054, 25207, 25020);
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1| SIMPLE | test1 | range | k | k | 4 | NULL | 124 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
那么提到了MRR,还有一个概念BKA(batch key access) 就不得不提了。
BKA就是为解决上面所说的:在没有MRR的情况下,被join表访问的次数大多(实际上上面的例子中可以把主键索引看成一个被join的表)。BKA就是每次批量的提交一批行给被join的表。实际上在BKA实现的过程中就是通过传递一个参数给MRR接口,本质上还是在MRR里面实现,下面这幅图则展示了他们之间的关系。
NBL和BKA都是批量的提交一部分行给被join的表,从而减少访问的次数,那么它们有什么区别呢?NBL和BKA的思想是类似的,详情见:
http://www.mysqlab.net/docs/refman/en-5.1/page/nested-loop-joins.html。
而BKA(batch key access)主要是指在被join表上有索引可以利用,那么就在行提交给被join的表之前,对这些行按照索引字段进行排序,因此减少了随机IO,排序这才是两者最大的区别
你可能还会问,为什么有了BKA还要NBL?
第一、NBL比BKA出现的早,BKA直到5.6才出现,而NBL至少在5.1里面就存在
第二、BKA是针对被join的表上面有index可以使用(也就是有key),但是如果用户没有在上面建index呢?那么就用NBL了咯
总结:
MRR主要是对于减少join中的随机IO和被join的表的访问次数,没有MRR之前mysql中最好的join算法都是NBL,虽然可以减少被join表的次数,但是随机IO这个还是解决不了,而现在两个问题都解决了。而性能对比测试则由于现在mysql 5.6还只是开发版本所以没测,以后可以试试。
NBL和BKA的都是面向被驱动表较大的场景。NBL是通过减少被驱动表扫描次数来提升效率。BKA和MRR则是在被驱动表jion的是key的场景下,减少随机IO。同时也会减少扫描次数。
参考资料:
1. http://www.mysqlab.net/docs/refman/en-5.1/page/nested-loop-joins.html
2. http://www.mysqlperformanceblog.com/2012/04/04/join-optimizations-in-mysql-5-6-and-mariadb-5-5/
3. http://www.mysqlperformanceblog.com/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/
4. http://kb.askmonty.org/en/multi-range-read-optimization#case-3-key-sorting-for-batched-key-access
- 浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
- 浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
- 浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
- 浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
- 【MySQL】MySQL性能优化之Block Nested-Loop Join(BNL)
- 【MySQL】MySQL性能优化之Block Nested-Loop Join(BNL)
- Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
- BKA MRR
- 5.6新特性之NL,BNL,MRR和BKA(转)
- 5.6新特性之NL,BNL,MRR和BKA(转)
- 浅析MySQL中的Index Condition Pushdown (ICP 索引条件下推)和Multi-Range Read(MRR 索引多范围查找)查询优化
- 优化器 MRR & BKA
- Mysql Multi-Range Read
- Mysql Block Nested-Loop 与 ICP
- 学习MYSQL之ICP、MRR、BKA
- 【mysql】关于ICP、MRR、BKA等特性
- hexo YAMLException: cannot read a block mapping entry; a multi line key may not be an implicit key a
- hexo YAMLException: cannot read a block mapping entry; a multi line key may not be an implicit key a
- Android导航菜单横向左右滑动并和下方的控件实现联动
- 業務システムの開発ドキュメント標準化 第5回:詳細設計書(後半)
- ios之应用程序设置
- 判断一个类型是否为可空类型(System.Nullable)
- elasticsearch常用的一些功能api
- 浅析multi range read(MRR) & batch key access(BKA) & block nested loop(BNL)
- hdu-1242
- 计算圆周率的最短C程序
- jQuery 对象访问:each(callback)
- Android访问通讯录和添加联系人
- Gathering Statistics for the Cost Based Optimizer (Pre 10g) (Doc ID 114671.1)
- 记录下
- 有关2014工作室学生负责人
- PHP消息发布时间距当前时间差距函数