MySQL Batched Key Access (BKA)原理和设置使用方法举例

MySQL 5.6版本开始增加了提高表join性能的算法:Batched Key Access (BKA)的新特性。

BKA算法原理:将外层循环的行/结果集存入join buffer,内存循环的每一行数据与整个buffer中的记录做比较,

可以减少内层循环的扫描次数.

对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成

的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的,

这样,MRR使得查询更有效率。

如果外部表扫描的是主键,那么表中的记录访问都是比较有序的,但是如果联接的列是非主键索引,那么对于表中记录

的访问可能就是非常离散的。因此对于非主键索引的联接,Batched Key Access Join算法

将能极大提高SQL的执行效率。BKA算法支持内连接,外连接和半连接操作,包括嵌套外连接。

Batched Key Access Join算法的工作步骤如下:

1) 将外部表中相关的列放入Join Buffer中。

2) 批量的将Key(索引键值)发送到Multi-Range Read(MRR)接口。

3) Multi-Range Read(MRR)通过收到的Key,根据其对应的ROWID进行排序,然后再进行数据的读取操作。

4) 返回结果集给客户端。

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

列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎 的(mrr目的是较为顺序).

这样,MRR使得查询更有效率。

大致的过程如下:

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

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

3 提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA

BNL(Block Nested Loop)和BKA(MySQL Batched Key Access)都是批量的提交一部分行给被join的表,从而减少访问的

次数,那么它们有什么区别呢?

 第一 BNL比BKA出现的早,BKA直到5.6才出现,而BNL至少在5.1里面就存在。

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

 第三 BKA主要是指在被join表上有索引可以利用,那么就在行提交给被join的表之前,对这些行按照索引字段进行排序,

因此减少了随机IO,排序这才是两者最大的区别,但是如果被join的表没用索引呢?

 那就使用BNL了。

以下设置启用BKA:

要使用BKA,必须调整系统参数optimizer_switch的值,官方推荐关闭mrr_cost_based,应将其设置为off。

mysql> SET global optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;

备注:

BKA主要适用于join的表上有索引可利用,无索引只能使用BNL。

多表join语句 ,被join的表/非驱动表必须有索引可用。

在EXPLAIN输出中,当Extra值包含Using join buffer(Batched Key Access),表示使用BKA。

+——————————————————–+

| Extra                                                                           |

+——————————————————–+

| NULL                                                                           |

| Using where; Using join buffer (Batched Key Access)|

+———————————————————+

使用hint,强制走BKA的方法:

例如:

Image placeholder
无名花火
未设置
  79人点赞

没有讨论,发表一下自己的看法吧

推荐文章
pymysql fetchone () , fetchall () , fetchmany ()

最近在用python操作mysql数据库时,碰到了下面这两个函数,标记一下: 1.定义 1.1fetchone(): 返回单个的元组,也就是一条记录(row),如果没有结果则返回None 1.2fet

【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

聊一聊 MySQL 中的事务及其实现原理

说到数据库,那就一定会聊到事务,事务也是面试中常问的问题,我们先来一个面试场景:面试官:"事务的四大特性是什么?" 我:"ACID,即原子性(Atomicity)、隔离性(Isolation)、持久性

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

mysql5.7 General tablespace使用说明

GeneraltablespaceGeneraltablespace 是一种共享的 innodb 表空间,有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表,即使这些表来自不同的 sch

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适

《MySQL主从不一致情形与解决方法》

一、MySQL主从不同步情况1.1网络的延迟由于mysql主从复制是基于binlog的一种异步复制通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现

Github一天标星1k+,程序员需要知道的那些定理和法则

大数据文摘出品编译:蒋宝尚、曹培信摩尔定律知道么?帕金森定律讲的又是啥?作为一名合格的开发人员,除了本身码力超强外,或多或少要知道几条“”潜规则”,例如依赖倒置原则、鲁棒性原则……关于开发人员必须要知

对话OceanBase资深总监韩鸿源:数据库是技术能力,云是使用方式,两者不应是竞争关系

5月10日,在第十届中国数据库技术大会(DTCC2019)上,蚂蚁金服的金融级分布式关系数据库OceanBase2.0,在经过200名数据库领域三年以上的从业者投票和专业评委的评选下,高分荣获了“年度

【python测试开发栈】帮你总结python random模块高频使用方法

随机数据在平时写python脚本时会经常被用到,比如随机生成0和1来控制逻辑、或者从列表中随机选择一个元素(其实抽奖程序也类似,就是从公司所有人中随机选择中奖用户)等等。这篇文章,就帮大家整理在pyt

【python测试开发栈】帮你总结python time模块高频使用方法

在平时写python脚本时,时间是我们经常用到的数据,比如:时间戳、前端展示的对应格式的时间等,在python中主要有三个和时间处理相关的模块:time、datetime、calendar,这篇文章主

static使用方法

(1)在非静态的成员方法中技能访问非静态的成员也能访问静态的成员;(静态成员被所有对象共享)(2)在静态的成员方法中只能访问静态的成员不能访问非静态的成员(调用静态方法时,可能还没有创建对象)(3)只

日均5亿查询量的京东订单中心,为什么舍MySQL用ES?

京东到家订单中心系统业务中,无论是外部商家的订单生产,或是内部上下游系统的依赖,订单查询的调用量都非常大,造成了订单数据读多写少的情况。我们把订单数据存储在MySQL中,但显然只通过DB来支撑大量的查

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

为什么不建议在 MySQL 中使用 UTF-8?

最近我遇到了一个bug,我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串,然后出现了一个离奇的错误:Incorrect string value: ‘😃 

MySQL优化之覆盖索引的使用

查看测试表结构:mysql>showcreatetableim_message\G ***************************1.row**************************

为什么mysql索引要使用B+树,而不是B树,红黑树

我们在MySQL中的数据一般是放在磁盘中的,读取数据的时候肯定会有访问磁盘的操作,磁盘中有两个机械运动的部分,分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟,而磁盘移动则是在盘片

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS