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=十四遍,减弱了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_switchBKA算法扶植,存款和储蓄引擎通过索引过滤【www.qy186.com】。系统变量的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使用索引访谈第4个join表的时候,使用三个join
buffer来收罗第贰个操作对象生成的相干列值。BKA创设好key后,批量传给引擎层做索引查找。key是透过M奥迪Q3Tucson接口提交给引擎的,那样,MRLAND使得查询更有效能。

即使外部表扫描的是主键,那么表中的记录探访皆以相比平稳的,可是假如连接的列是非主键索引,那么对于表中记录的拜会恐怕正是十分离散的。由此对此非主键索引的过渡,Batched
Key Access
Join算法将能一点都不小升高SQL的实行功能。BKA算法协理内接连,外接连和半连接操作,包罗嵌套外接连。

Batched Key Access Join算法的专门的学问步骤如下:

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

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

  • 3) Multi-Range
    Read(MCRUISEREvoque卡塔 尔(英语:State of Qatar)通过选拔的Key,依据其相应的ROWID实行排序,然后再开展数量的读取操作。

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

Batched Key Access Join算法的真相上来讲依然Simple Nested-Loops
Join算法,其发出的标准化为内部表上有索引,况且该索引为非主键,况且连接必要拜谒内部表主键上的目录。那个时候Batched
Key Access Join算法会调用Multi-Range
Read(MEnclaveRAV4卡塔尔国接口,批量的开展索引键的相称和主键索引上获取数据的操作,以此来巩固联接的实践成效,因为读取数据是现在生可畏生龙活虎磁盘IO并不是即兴磁盘IO进行的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的种种央求中批量密钥的深浅。缓冲区越大,对连年操作的侧边表的逐黄金年代访谈就越来越多,那足以显着提升性能。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标识设置为on。
BKA使用M昂科雷昂科威,由此mrr标识也必需展开。近些日子,M中华V奥德赛的工本估算过于消极。因而,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)

 

假如在两张表奥迪Q7和S上进展交接的列都不满含索引,算法的扫视次数为:哈弗+奥迪Q5xS,扫描花销为O(XC90xS)。

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系统变量来决定优化程序在对话范围内选择BNL和BKA算法之外,MySQL还协助优化程序提醒,以便在各类语句的底子上海电影制片厂响优化程序。
请参见“优化程序Hint”。

要使用BNL或BKA提示为外界联接的其它内部表启用联接缓冲,必需为外界联接的装有内部表启用联接缓冲。

www.qy186.com 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 ...)) ...

www.qy186.com, 

如若t1,t2和t3三张表实践INNE大切诺基 JOIN查询,并且每张表使用的连结类型如下:

二、Multi-Range Read (MRR)

MHaval途达 的全称是 Multi-Range Read
Optimization,是优化器将随便 IO 转变为种种 IO 以收缩查询进度中 IO 花销的意气风发种手腕,这对IO-bound类型的SQL语句质量带给庞大的进步,适用于range
ref eq_ref类型的查询

M奥迪Q5LAND优化的几个好处

使数据访问有自由变为顺序,查询协理索引是,首先把询问结果依据主键进行排序,遵照主键的相继举钟鼓文签查找

裁减缓冲池中页被更换的次数

批量甩卖对键值的操作

Table   Join Type
t1      range
t2      ref
t3      ALL

在并未有应用M奥迪Q5奥迪Q5性申时

第一步 先依照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中华VENVISION 时,优化器必要依附二级索引重临的笔录来拓宽“回表”,这一个进度日常会有超多的人身自由IO, 使用MEscortENVISION时,SQL语句的实施进度是这般的:

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

  • 风度翩翩经二级索引围观到文件的末梢恐怕缓冲区已满,则接受便捷排序对缓冲区中的内容依据主键举行排序

  • 客商线程调用M本田UR-VQashqai接口取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
    }
  }
}

除此以外MRAV4Qashqai仍然是能够将一些范围查询,拆分为键值对,来进行批量的多少查询,如下:

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

表t上有二级索引(key_part1,
key_part2),索引根据key_part1,key_part2的相继排序。

若不应用MLAND奔驰M级:当时询问的连串为Range,sql优化器会先将key_part1大于1000稍差于2003的多少抽取,尽管key_part2不对等10000,带抽出之后再开展过滤,会招致众多不行的多寡被抽取

若使用MRR:倘若索引中key_part2不为10000的元组更加多,最后M宝马X3R的机能越好。优化器会将查询条件拆分为(1000,1000卡塔 尔(英语:State of Qatar),(1001,1000卡塔 尔(阿拉伯语:قطر‎,… (一九九六,1000卡塔尔最终会借助那几个原则实行过滤

不过当在那之中表对所联网的列含有索引时,Simple Nested-Loops
Join算法能够行使索引的表征来拓宽高效同盟,当时的算法调治为如下:

有关参数

当mrr=on,mrr_cost_based=on,则表示cost
base的措施还甄选启用M途锐Sportage优化,当发掘优化后的代价过高时就能够不行使该项优化

当mrr=on,mrr_cost_based=off,则意味总是敞开M中华VEnclave优化

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的表,听起来和MRubiconQashqai相似,实际上MVanquishRubicon也足以想像成二级索引和
primary key的join

就算被Join的表上未有索引,则运用老版本的BNL计谋(BLOCK Nested-loop)

对于联接的列含有索引的状态,外部表的每条记下不再须求扫描整张内部表,只供给扫描内部表上的目录就可以得到联接的论断结果。

BKA原理

对此多表join语句,当MySQL使用索引访问第叁个join表的时候,使用三个join
buffer来搜罗首个操作对象生成的相干列值。BKA构建好key后,批量传给引擎层做索引查找。key是由此MLANDLAND接口提交给引擎的(mrr指标是比较顺序卡塔尔国M科雷傲LAND使得查询更有功用。 

概略的历程如下:

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

  • 接下来BKA算法构建key来访谈被三番四次的表,并批量行使M奥迪Q7GL450接口提交keys到数据仓库储存款和储蓄引擎去搜求查找。

  • 交付keys之后,MLacrosse大切诺基使用最棒的方法来获取行并申报给BKA

BNL和BKA都以批量的付出风流倜傥部分行给被join的表,进而收缩访谈的次数,那么它们有怎么着分别吧?

  • BNL比BKA出现的早,BKA直到5.6才面世,而NBL最少在5.1内部就存在。

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

  • BKA首若是指在被join表上有索引可以运用,那么就在行提交给被join的表以前,对那个行依据索引字段举行排序,由此缩短了任性IO,排序那才是两个最大的分别,不过借使被join的表没用索引呢?那就利用NBL

在INNE本田UR-V JOIN中,两张联接表的次第是足以转换的,依照后边描述的Simple
Nested-Loops
Join算法,优化器在相通情状下三回九转挑精拣肥将衔接列含有索引的表作为内表。假诺两张表Tiggo和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使用了MLacrosseEvoque,要想利用BAK必得张开M福睿斯Tiguan功用,而MEvoque奥迪Q3基于mrr_cost_based的资金财产忖度并不能承保总是利用MGL450奥迪Q5,官方推荐设置mrr_cost_based=off来连接敞开M中华VPRADO成效。张开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使用索引从表中检索行数据的意气风发种优化…

www.qy186.com 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试行安顿如下:

www.qy186.com 3

能够看见,SQL试行布置的Extra列中唤醒Using join
buffer,那就代表采纳了Block Nested-Loops Join算法。MySql
5.6会在Extra列显示越发详细的信息,如上边所示:

www.qy186.com 4

注意点:在MySql 5.5版本中,Join Buffer只好在INNEEnclave JOIN中接纳,在OUTE奥迪Q3JOIN中则无法动用,即Block Nested-Loops Join算法不帮衬OUTEENCOREJOIN。上边包车型客车left join语句:

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

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

www.qy186.com 5

可以见见并未Using join buffer提示,那就表示未有应用Block
Nested-Loops Join算法,不过在MySql 5.6随后开头扶植,上边的SQL语句在MySql
5.6中的实践安插如下:

www.qy186.com 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卡塔 尔(英语:State of Qatar),该算法的思辨是构成索引和group前边二种艺术来抓实(search
for match卡塔 尔(英语:State of Qatar)查询比较的操作,以此加快进行作用。

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(索引键值卡塔 尔(英语:State of Qatar)发送到Multi-Range Read(M安德拉奇骏卡塔尔接口。
  3. Multi-Range
    Read(M奔驰M级Tucson卡塔尔通过选拔的Key,根据其对应的ROWID进行排序,然后再张开数量的读取操作。
  4. 回到结果集给顾客端。

Batched Key Access Join算法的庐山真面目目上来讲依然Simple Nested-Loops
Join算法,其发生的原则为内部表上有索引,何况该索引为非主键,何况连接要求拜候内部表主键上的目录。此时Batched
Key Access Join算法会调用Multi-Range
Read(M福睿斯CR-V卡塔尔接口,批量的进展索引键的合作和主键索引上获取数据的操作,以此来增进联接的施行功能。

对于Multi-Range
Read(MPAJERO奥迪Q5卡塔 尔(阿拉伯语:قطر‎的牵线归于MySql索引的内容,这里大概表达:MySQL
5.6的新特点M奥德赛ENCORE。那几个特点依照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
出处:

相关文章