性能优化之--BKA

一 介绍 MySQL 5.6版本提供了很多性能优化的特性,其中之一是关于提高表join性能的算法:Batched Key Access (BKA) ,本文将结合之前写过MRR,BNL优化特性一起来详细介绍该算法。 二 原理 对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口 提交给引擎的. 这样,MRR使得查询更有效率。 大致的过程如下:

1 BKA使用join buffer保存由join的第一个操作产生的符合条件的数据。 2 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎去查找查找。 3 提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA .

BKA使用join buffer size来确定buffer的大小,buffer越大,访问被join的表/内部表就越顺序。MRR接口有2个应用场景: 场景1:应用于传统的基于磁盘的存储引擎(innodb,myisam),对于这些引擎join buffer中keys是一次性提交到MRR,MRR通过key找到rowid,通过rowid来获取数据。 场景2:应用于远程存储引擎(NDB),来自join buffer上的部分key,从SQL NODE发送到DATA NODE,然后SQL NODE会收到通过相关关系匹配的行组合。然后使用这些行组合匹配出新行。然后在发送新key,直到发完为止。 三 BNL和BKA,MRR的关系 BNL和BKA都是批量的提交一部分结果集给下一个被join的表(标记为T),从而减少访问表T的次数,那么它们有什么区别呢?NBL和BKA的思想是类似的,详情见:《nest-loop-join官方手册》 第一 NBL比BKA出现的早,BKA直到5.6才出现,而NBL至少在5.1里面就存在。 第二 NBL主要用于当被join的表上无索引,Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively) 第三BKA主要是指在被join表上有索引可以利用,那么就在行提交给被join的表之前,对这些行按照索引字段进行排序,因此减少了随机IO,排序这才是两者最大的区别,但是如果被join的表没用索引呢?那就使用NBL了。

上面原理环境提到讲了在BKA实现的过程中就是通过传递keys给MRR接口,本质上还是在MRR里面实现,下面这幅图则展示了它们之间的关系:

四 如何使用 要使用BKA,必须调整系统参数optimizer_switch的值,batched_key_access设置为on,因为BKA使用了MRR,因此也要打开MRR,但是基于成本优化MRR算法不是特别准确官方文档推荐关闭 mrr_cost_based,将其设置为off。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

另外多表join语句 ,被join的表/非驱动表必须索引可用。 五 参考资料 [1] https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html [2] Block-Based Join Algorithms [3] https://www.percona.com/blog/2012/03/21/multi-range-read-mrr-in-mysql-5-6-and-mariadb-5-5/ [4] bacthed-key-access-speeds-up-disk-bound [5] https://mariadb.com/kb/en/library/multi-range-read-optimization

[6] On the multi range read&batch key access&block nested loop

原文发表时间:2018-01-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏做全栈攻城狮

C#(Net)软件开发常用工具汇总,提高你的开发效率

作为C#语言官方的开发工具,VS的强大只有在多种语言开发工具使用之后,你才会明白VS的强大之处。可谓神器。其中,开发工具尽量选择版本高的。数据库尽量选择版本低的...

22720
来自专栏重庆的技术分享区

如何在Debian 8上安装MySQL

MySQL是一种流行的数据库管理系统,用于Web和服务器应用程序。本指南将介绍如何在运行Debian 8(Jessie)的Linode上安装,配置和管理MySQ...

1K20
来自专栏PHP在线

数据优化

数据优化 读写分离,在X2的版本中,我们引入了多SQL服务器的支持,在主从服务器中,你可以配置写服务器跟读服务器,这样对于负载高的站点中可以使用这个 功能达到读...

30480
来自专栏王大锤

iOS中UITableView和UICollectionView的默认空态页

34970
来自专栏流柯技术学院

Kerberos简介

Kerberos协议主要用于计算机网络的身份鉴别(Authentication), 其特点是用户只需输入一次身份验证信息就可以凭借此验证获得的票据(ticket...

2.2K20
来自专栏zhangdd.com

mysql proxysql+mgr集群 centos7系统安装配置

wget https://codeload.github.com/sysown/proxysql/tar.gz/v1.4.4

30330
来自专栏PHP在线

总结

1.安装完成后备份快照 2.不插网线使用虚拟机,查看vmware的IP网段,设置linux系统相同的网段。 3.rpm -qa 软件名字 //查询软件是...

33350
来自专栏CSDN技术头条

MongoDB开发版本3.1.8发布

MongoDB 3.1.8版本已发布。值得注意的是此次3.1.8作为开发版本,并不适用于生产环境中使用。接来下的3.2系列版本将供广大用户作为生产环境中使用,敬...

23560
来自专栏文渊之博

SQL Server 中的逻辑读与物理读

首先要理解逻辑读和物理读:   预读:用估计信息,去硬盘读取数据到缓存。预读100次,也就是估计将要从硬盘中读取了100页数据到缓存。   物理读:查询计划生成...

21990
来自专栏菩提树下的杨过

mac 下卸载mysql的方法

今天在mac上瞎折腾时,把mysql玩坏了,想卸载重装,却发现找不到卸载程序,百度了下,将操作步骤备份于此: cd ~/ sudo rm /usr/local...

30970

扫码关注云+社区

领取腾讯云代金券