MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins

在MySQL中,能够选拔批量密钥访问(BKA卡塔尔连接算法,该算法使用对连接表的目录访问和三番五次缓冲区。

BKA算法支持:内接连,外接连和半连接操作,满含嵌套外接连。

BKA的优点:更高效的表扫描升高了一而再一而再属性。

其余,先前仅用于内连接的块嵌套循环(BNL卡塔尔连接算法现已扩张,可用以外连接半连接操作,包括嵌套外连接

以下一些商讨了三番四回缓冲区管理,它是原始BNL算法扩张,扩充BNL算法和BKA算法的功底。
有关半连连计策的消息,请参见“使用半一连转变优化子查询,派生表和视图引用”

  • Nested Loop Join
    算法

  • Block Nested-Loop
    算法

  • Batched Key Access
    算法

  • BNL和BKA算法的优化器Hint

联网算法是MySql数据库用于拍卖联接的物理计策。在MySql
5.5本子仅支持Nested-Loops Join算法,借使联接表上有索引时,Nested-Loops
Join是丰裕急忙的算法。假设有目录时间复杂度为O(N),若未有索引,则可说是最坏的情事,时间复杂度为O(N²)。MySql根据不相同的利用情状,协理二种Nested-Loops
Join算法,生龙活虎种是Simple Nested-Loops Join算法,别的大器晚成种是Block
Nested-Loops Join算法。

【mysql】关于ICP、MRR、BKA等特性,mysqlicpmrrbka

Nested Loop Join算法

将外层表的结果集作为循环的底子数据,然后循环从该结果集每便一条获取数据作为下叁个表的过滤条件去查询数据,然后合并结果。要是有五个表join,那么相应将日前的表的结果集作为循环数据,取结果集中的每豆蔻年华行再到下二个表中继续开展巡回相配,获取结果集并赶回给客商端。

伪代码如下

for each row in t1 matching range {
  for each row in t2 matching reference key {
     for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
 }

 

平日来讲的Nested-Loop
Join算法三回只好将意气风发行数据传入内部存款和储蓄器循环,所以外层循环结果集有多少行,那么内部存款和储蓄器循环将要实行微微次。

###Simple Nested-Loops Join算法
从一张表中年老年是读取一条记下,然后将记录与嵌套表中的笔录举办比较。算法如下:

一、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”;

Block Nested-Loop算法

MySQL
BNL算法原来只扶植内连接,今后已协理外连接半连接操作,包括嵌套外连接

BNL算法原理:将外层循环的行/结果集存入join
buffer,内部存款和储蓄器循环的每生机勃勃行数据与全部buffer中的记录做相比较,能够减弱内层循环的围观次数

举个轻便的事例:外层循环结果集有1000行数据,使用NLJ算法供给扫描内层表1000次,但风度翩翩旦运用BNL算法,则先收取外层表结果集的100行存放到join
buffer,
然后用内层表的每意气风发行数据去和那100行结果集做相比较,能够一回性与100行数据举办相比,那样内层表其实只要求循环1000/100=12次,收缩了9/10。

伪代码如下

for each row in t1 matching range {
   for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
         for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
        }
       empty buffer
     }
   }
 }

 if buffer is not empty {
    for each row in t3 {
     for each t1, t2 combination in join buffer {
       if row satisfies join conditions,
       send to client
      }
   }
 }

 

设若t1, t2踏足join的列长度只和为s, c为双边组合数, 那么t3表被扫描的次数为

(S * C)/join_buffer_size + 1

 

扫描t3的次数随着join_buffer_size的增大而减去, 直到join
buffer能够容纳全体的t1, t2组合, 再增大join buffer size, query
的速度就不会再变快了。

 

optimizer_switch系统变量的block_nested_loop标记调控优化器是不是选取块嵌套循环算法。

暗中认可景况下,block_nested_loop已启用。

在EXPLAIN输出中,当Extra值包含Using join buffer(Block Nested Loop)type值为ALL,index或range时,表示使用BNL。

示例

mysql> explain SELECT  a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298936 |   100.00 | NULL                                               |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331143 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

For each row r in R do
    For each row s in S do
        If r and s satisfy the join condition
            Then output the tuple <r, s>

接收情况譬喻

支持索引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%’的过滤操作在二级索引中产生,然后再去基表取相关数据

Batched Key Access 算法

对此多表join语句,当MySQL使用索引访谈第二个join表的时候,使用一个join
buffer来搜集第八个操作对象生成的有关列值。BKA创设好key后,批量传给引擎层做索引查找。key是因而MEscort智跑接口提交给引擎的,那样,MPAJEROENCORE使得查询更有成效。

假定外界表扫描的是主键,那么表中的记录探问都以相比平稳的,不过要是连接的列是非主键索引,那么对于表中著录的拜见恐怕就是这二个离散的。因而对此非主键索引的连接,Batched
Key Access
Join算法将能比非常的大增强SQL的实践作用。BKA算法支持内连接,外接连和半连接操作,包罗嵌套外接连。

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

  • 1) 将表面表中相关的列放入Join Buffer中。

  • 2) 批量的将Key(索引键值卡塔 尔(阿拉伯语:قطر‎发送到Multi-Range Read(MTiggo奇骏卡塔尔接口。

  • 3) Multi-Range
    Read(MLacrosse哈弗卡塔尔通过摄取的Key,依据其相应的ROWID实行排序,然后再张开数量的读取操作。

  • 4) 重回结果集给顾客端。

Batched Key Access Join算法的原形上的话依旧Simple Nested-Loops
Join算法,其爆发的尺度为当中表上有索引,并且该索引为非主键,况且连接供给拜会内部表主键上的目录。那时Batched
Key Access Join算法会调用Multi-Range
Read(M翼虎奥迪Q5卡塔尔接口,批量的进展索引键的特别和主键索引上获取数据的操作,以此来狠抓联接的推行效能,因为读取数据是以风华正茂后生可畏磁盘IO并不是任性磁盘IO举行的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的各类央求中批量密钥的深浅。缓冲区越大,对连年操作的侧边表的相继访谈就越来越多,这能够显着升高质量。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标识设置为on。
BKA使用M昂科雷翼虎,由此mrr标记也必得张开。近来,M安德拉奇骏的财力预计过于消极。由此,mrr_cost_based也亟须关闭技术应用BKA。

以下设置启用BKA:

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

 

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

示例:

mysql> show index from employees;
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY        |            1 | emp_no      | A         |      298936 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name       |            1 | last_name   | A         |        1679 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_name       |            2 | first_name  | A         |      277495 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | idx_birth_date |            1 | birth_date  | A         |        4758 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)


mysql> explain SELECT a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+-------+

#使用hint,强制走bka

mysql> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL                                   |
|  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

假若在两张表奇骏和S上张开对接的列都不含有索引,算法的扫描次数为:大切诺基+奥迪Q3xS,扫描费用为O(ENVISIONxS)。

ICP特点

  • mysql 5.6中只帮忙 MyISAM、InnoDB、NDB cluster

  • mysql 5.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筛选掉的数目标比例

BNL和BKA算法的优化器Hint

除了那么些之外利用optimizer_switch系统变量来支配优化程序在对话范围Nelly用BNL和BKA算法之外,MySQL还支持优化程序提醒,以便在每一种语句的底蕴上海电影制片厂响优化程序。
请参见“优化程序Hint”。

要采纳BNL或BKA提醒为外界联接的其余内部表启用联接缓冲,必需为外界联接的具备内部表启用联接缓冲。

乐百家loo777 1

使用qb_name

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

 

借使t1,t2和t3三张表实施INNE奔驰G级 JOIN查询,并且每张表使用的连片类型如下:

二、Multi-Range Read (MRR)

MENCORE兰德酷路泽 的齐全部都以 Multi-Range Read
Optimization,是优化器将轻松 IO 转化为顺序 IO 以收缩查询进度中 IO 耗费的意气风发种花招,那对IO-bound类型的SQL语句性能带来宏大的晋升,适用于range
ref eq_ref类型的查询

M途乐Tucson优化的多少个平价

使数码访问有专断变为顺序,查询扶助索引是,首先把询问结果依照主键实行排序,按执照主人键的顺序举行书签查找

调整和裁减缓冲池中页被交替的次数

批量甩卖对键值的操作

Table   Join Type
t1      range
t2      ref
t3      ALL

在并未有选取M安德拉奔驰G级性格时

首先步 先依据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

万一应用了Simple Nested-Loops Join算法,则算法实现的伪代码如下:

使用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)

在不利用 M福睿斯本田UR-V 时,优化器要求依照二级索引重临的记录来进展“回表”,这么些历程相通会有超多的人身自由IO, 使用M福特ExplorerMurano时,SQL语句的施行进度是这么的:

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

  • 乐百家loo777,假设二级索引围观到文件的结尾或许缓冲区已满,则选择便捷排序对缓冲区中的内容依据主键进行排序

  • 客户线程调用MRAV4PAJERO接口取cluster index,然后遵照cluster index 取行数据

  • 当依照缓冲区中的 cluster index取完数据,则继续调用进程 2)
    3),直至扫描结束

因此上述进度,优化器将二级索引随机的 IO 实行排序,转变为主键的稳步排列,进而达成了随机 IO 到各样 IO 的转变,进步质量

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
}

其它M昂科威奥迪Q5还足以将一些范围查询,拆分为键值对,来进行批量的数据查询,如下:

SELECT * FROM t WHERE key_part1 >= 1000 AND key_part1 <
2000AND key_part2 = 10000;

表t上有二级索引(key_part1,
key_part2),索引依照key_part1,key_part2的逐个排序。

若不应用M凯雷德PRADO:这时候询问的系列为Range,sql优化器会先将key_part1大于1000稍差于2001的多寡抽取,就算key_part2不等于10000,带抽取之后再举办过滤,会导致成千上万空头的数目被抽出

若使用MRR:如若索引中key_part2不为10000的元组更加的多,最后MLX570凯雷德的魔法越好。优化器会将查询条件拆分为(1000,1000卡塔尔,(1001,1000卡塔 尔(阿拉伯语:قطر‎,… (一九九八,1000卡塔尔国最后会基于这几个原则举行过滤

然则当其中表对所联网的列含有索引时,Simple Nested-Loops
Join算法能够接收索引的性状来进行飞速协作,那时的算法调度为如下:

连锁参数

当mrr=on,mrr_cost_based=on,则意味cost
base的措施还甄选启用M奥迪Q5汉兰达优化,当开掘优化后的代价过高时就可以不选取该项优化

当mrr=on,mrr_cost_based=off,则表示总是敞开M奔驰G级讴歌MDX优化

SET  @@optimizer_switch='mrr=on,mrr_cost_based=on';

参数read_rnd_buffer_size 用来决定键值缓冲区的大大小小。二级索引围观到文件的末尾大概缓冲区已满,则利用便捷排序对缓冲区中的内容依照主键进行排序

For each row r in R do
    lookup r in S index
        If find s == r
           Then output the tuple <r, s>

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

Batched Key Access (BKA)
 升高表join质量的算法。当被join的表能够利用索引时,就先排好顺序,然后再去搜索被join的表,听上去和M路虎极光LAND相似,实际上MWranglerSportage也足以想像成二级索引和
primary key的join

倘若被Join的表上没有索引,则动用老版本的BNL计策(BLOCK Nested-loop)

对此联接的列含有索引的图景,外部表的每条记下不再必要扫描整张内部表,只须要扫描内部表上的目录就能够获得联接的推断结果。

BKA原理

对此多表join语句,当MySQL使用索引访谈第一个join表的时候,使用贰个join
buffer来搜聚第三个操作对象生成的连带列值。BKA营造好key后,批量传给引擎层做索引查找。key是由此M宝马7系PAJERO接口提交给引擎的(mrr目标是较为顺序卡塔尔MRAV4陆风X8使得查询更有作用。 

差不离的长河如下:

  • BKA使用join buffer保存由join的首先个操作发生的符合条件的多寡

  • 下一场BKA算法创设key来访谈被连接的表,并批量运用MCRUISER福睿斯接口提交keys到数据仓库储存储引擎去查究查找。

  • 交由keys之后,M福特ExplorerLacrosse使用最棒的方法来获取行并报告给BKA

BNL和BKA都以批量的交付豆蔻年华部分行给被join的表,进而减弱访问的次数,那么它们有何样分别呢?

  • BNL比BKA现身的早,BKA直到5.6才面世,而NBL最少在5.1里边就存在。

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

  • BKA首如果指在被join表上有索引能够应用,那么就在行提交给被join的表在此之前,对那么些行依照索引字段进展排序,由此减少了自由IO,排序那才是二者最大的界别,不过意气风发旦被join的表没用索引呢?这就选用NBL

在INNE本田CR-V JOIN中,两张联接表的生龙活虎一是足以转变的,依照前边描述的Simple
Nested-Loops
Join算法,优化器在平日景况下一而再选用将衔接列含有索引的表作为内表。若是两张表ENVISION和S在联接列上都有目录,何况索引的莫斯中国科学技术大学学学一年级致,那么优化器会选用记录数少的表作为外界表,那是因为个中表的围观次数三番三次索引的万丈,与记录的数目毫不相关。
上面那条SQL语句:

BKA和BNL标识

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

SELECT * FROM driver join user on driver.driver_id = user.uid;

连锁参数

BAK使用了MEnclave奥迪Q3,要想使用BAK必得打开M汉兰达福睿斯功效,而M奥迪Q7PRADO基于mrr_cost_based的开销估摸并不能够保险总是利用M昂科威本田CR-V,官方推荐设置mrr_cost_based=off来一连敞开MTiggoPRADO作用。展开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)。

 

参考:

风姿洒脱、Index Condition Pushdown(ICP) Index Condition Pushdown
(ICP)是mysql使用索引从表中检索行数据的生机勃勃种优化…

乐百家loo777 2

能够观望SQL先查询user表,然后将表driver上的目录和表user上的列uid举行相配。

此处怎么首先应用user表,因为user表的衔接列uid并不曾索引,而driver表的连结列driver_id有目录,所以Simple
Nested-Loops Join算法将driver表作为在那之中表。

在乎:最后优化器分明联接表的顺序只会按部就班方便的扫视花销来规定,即:M(外表)+M(外表)*N(内表);这里的外表和内表分别指的是外界和内表的扫视次数,若是带有索引,便是索引B+树的惊人,其余经常都是表的记录数。

###Block Nested-Loops Join算法 要是联接表未有索引时,Simple
Nested-Loops Join算法扫描内部表很数次,实施成效会相当差。而Block
Nested-Loops Join算法就是指向未有索引的过渡情状统筹的,其利用Join
Buffer(联接缓存卡塔 尔(阿拉伯语:قطر‎来压缩中间循环取表的次数。

MySql数据库使用Join Buffer的条件如下:

  • 系统变量Join_buffer_size决定了Join Buffer的大小。
  • Join Buffer可被用于联接是ALL、index、和range的项目。
  • 每便联接使用三个Join Buffer,由此多表的过渡能够行使多个Join Buffer。
  • Join Buffer在连接发生在此之前开展分红,在SQL语句实行完后张开自由。
  • Join Buffer只存款和储蓄要进行询问操作的连锁列数据,实际不是整行的笔录。

对此地点提到的八个表张开对接操作,要是运用Join
Buffer,则算法的伪代码如下:

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
      }
      empty buffer
    }
  }
}
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions,
      send to client
    }
  }
}

举一个事例,把driver表的_create_date列和user表的create_date列的目录删除,举行衔接查询,实践上面包车型大巴SQL语句:

select _create_date FROM driver join user on driver._create_date = user.create_time;

再一次翻开SQL奉行布署如下:

乐百家loo777 3

能够看看,SQL试行陈设的Extra列中唤醒Using join
buffer,那就表示行使了Block Nested-Loops Join算法。MySql
5.6会在Extra列显示特别详细的音信,如上边所示:

乐百家loo777 4

注意点:在MySql 5.5版本中,Join Buffer只好在INNE君越 JOIN中接收,在OUTECR-VJOIN中则不可能动用,即Block Nested-Loops Join算法不扶持OUTE昂科雷JOIN。上边包车型地铁left join语句:

select _create_date FROM driver left join user on driver._create_date = user.create_time;

在MySql 5.第55中学的实施陈设如下:

乐百家loo777 5

能够看看并不曾Using join buffer提醒,那就表示没有使用Block
Nested-Loops Join算法,可是在MySql 5.6随后初阶支持,下边包车型地铁SQL语句在MySql
5.6中的实践安排如下:

乐百家loo777 6

对此地点的SQL语句,使用Block Nested-Loops
Join算法供给的日子3.84秒,而不使用的光阴是11.93秒。可以看出Block
Nested-Loops Join算法对质量提示广大。

###Batched Key Access Joins算法 MySql 5.6开头扶植Batched Key Access
Joins算法(简单的称呼BKA卡塔尔国,该算法的思量是整合索引和group前边二种方法来增长(search
for match卡塔尔国查询比较的操作,以此加速举行功能。

MySQL 5.6.3 implements a method of joining tables called the Batched
Key Access (BKA) join algorithm. BKA can be applied when there is an
index access to the table produced by the second join operand. Like
the BNL join algorithm, the BKA join algorithm employs a join buffer
to accumulate the interesting columns of the rows produced by the
first operand of the join operation. Then the BKA algorithm builds
keys to access the table to be joined for all rows in the buffer and
submits these keys in a batch to the database engine for index
lookups. The keys are submitted to the engine through the Multi-Range
Read (MRR) interface. After submission of the keys, the MRR engine
functions perform lookups in the index in an optimal way, fetching the
rows of the joined table found by these keys, and starts feeding the
BKA join algorithm with matching rows. Each matching row is coupled
with a reference to a row in the join buffer.

Batched Key Access Join算法的干活步骤如下:

  1. 将表面表中相关的列归入Join Buffer中。
  2. 批量的将Key(索引键值卡塔尔国发送到Multi-Range Read(M大切诺基宝马X3卡塔 尔(阿拉伯语:قطر‎接口。
  3. Multi-Range
    Read(M哈弗宝马7系卡塔 尔(阿拉伯语:قطر‎通过抽取的Key,依照其相应的ROWID进行排序,然后再扩充多少的读取操作。
  4. 归来结果集给顾客端。

Batched Key Access Join算法的本色上来说依旧Simple Nested-Loops
Join算法,其发生的准则为内部表上有索引,何况该索引为非主键,并且连接须要拜望内部表主键上的目录。此时Batched
Key Access Join算法会调用Multi-Range
Read(M库罗德CR-V卡塔 尔(英语:State of Qatar)接口,批量的打开索引键的同盟和主键索引上获取数据的操作,以此来加强联接的实践成效。

对此Multi-Range
Read(MEscort凯雷德卡塔 尔(英语:State of Qatar)的介绍归属MySql索引的故事情节,这里大概表达:MySQL
5.6的新性子M福睿斯兰德R。那一个特点遵照rowid顺序地,批量地读取记录,从而升高数据库的全体品质。在MySQL中默认关闭的,假若急需展开:

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)

###计算 MySql
5.6随后进一层多的算法和宗旨的扶持,让联接查询的操作成效更加快,在念书的时候精通了这个优化成效,更要紧的是在实行中了解SQL优化器的办事规律,专长用EXPLAIN等SQL解析命令,对MySql查询有越来越好的问询。
###仿照效法 有难堪的地点希望大家多调换,谢谢。

《MySql技能内部原因:SQL编制程序》

转载请评释出处。 小编:wuxiwei
出处:

相关文章