询问优化之,MySql联接算法

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算法供给扫描内层表一千次,但借使采纳BNL算法,则先抽取外层表结果集的100行存放到join
buffer,
然后用内层表的每一行数据去和那100行结果集做相比,能够三遍性与100行数据举办相比,那样内层表其实只供给循环1000/100=10次,收缩了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是经过M路虎极光Odyssey接口提交给引擎的,那样,MEvoque汉兰达使得查询更有作用。

设若外界表扫描的是主键,那么表中的记录拜见都以比较平稳的,不过只要连接的列是非主键索引,那么对于表中著录的拜见或然就是老大离散的。因而对此非主键索引的接入,Batched
Key Access
Join算法将能小幅拉长SQL的施行效用。BKA算法帮助内接连,外接连和半连接操作,包括嵌套外接连。

Batched Key Access Join算法的行事步骤如下:

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

  • 2) 批量的将Key(索引键值)发送到Multi-Range Read(MEscort安德拉)接口。

  • 3) Multi-Range
    Read(M宝马X3奥迪Q7)通过收到的Key,依据其对应的ROWID举行排序,然后再实行数量的读取操作。

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

Batched Key Access Join算法的实质上的话仍旧Simple Nested-Loops
Join算法,其发出的尺码为当中表上有索引,而且该索引为非主键,並且连接要求拜谒内部表主键上的目录。那时Batched
Key Access Join算法会调用Multi-Range
Read(M福睿斯昂科雷)接口,批量的进展索引键的合作和主键索引上获取数据的操作,以此来增加联接的实践作用,因为读取数据是以一一磁盘IO实际不是随机磁盘IO举办的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的每种诉求中批量密钥的深浅。缓冲区越大,对连年操作的侧边表的相继访谈就更加的多,那能够显着进步品质。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access表明设置为on。
BKA使用MRAV4R,由此mrr标志也不可能不张开。如今,MCR-V奥迪Q3的本金猜测过于悲观。因而,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上进行联网的列都不含有索引,算法的围观次数为:奥德赛+EscortxS,扫描开销为O(SportagexS)。

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提醒为外界联接的别的内部表启用联接缓冲,必得为外界联接的富有内部表启用联接缓冲。

图片 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中华V JOIN查询,而且每张表使用的对接类型如下:

二、Multi-Range Read (MRR)

M福睿斯Tiggo 的完备是 Multi-Range Read
Optimization,是优化器将随机 IO 转化为顺序 IO 以减低查询进程中 IO 开支的一种花招,那对IO-bound类型的SQL语句质量带来比十分大的晋升,适用于range
ref eq_ref类型的询问

MKoleosLAND优化的几个好处

使数据访问有私下变为顺序,查询辅助索引是,首先把询问结果遵照主键实行排序,依据主键的依次进行草签查找

收缩缓冲池中页被轮换的次数

批量拍卖对键值的操作

Table   Join Type
t1      range
t2      ref
t3      ALL

在并未有运用M揽胜极光CR-V天性时

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

在不行使 MQashqai奔驰M级 时,优化器需求基于二级索引重临的笔录来拓宽“回表”,这么些进度平时会有非常多的即兴IO, 使用MTiggoEnclave时,SQL语句的施行进程是这么的:

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

  • 假使二级索引围观到文件的终极或然缓冲区已满,则运用高效排序对缓冲区中的内容按执照主人键实行排序

  • 客户线程调用M福特Explorer帕杰罗接口取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
    }
  }
}

除此以外MWrangler揽胜仍可以将一些范围查询,拆分为键值对,来进行批量的多寡查询,如下:

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

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

若不使用MENCOREXC90:此时询问的花色为Range,sql优化器会先将key_part1大于一千低于2000的数据收取,纵然key_part2不等于一千0,带抽取之后再实行过滤,会招致无尽空头的数量被抽出

若使用MRR:假诺索引中key_part2不为一千0的元组越来越多,最终MLANDRAV4的意义越好。优化器会将查询条件拆分为(一千,一千),(1001,一千),… (1997,一千)最后会遵照这一个原则举办过滤

而是当在那之中表对所联网的列含有索引时,Simple Nested-Loops
Join算法能够使用索引的本性来张开飞速合作,此时的算法调解为如下:

连带参数

当mrr=on,mrr_cost_based=on,则代表cost
base的秘籍还挑拣启用MEvoqueGL450优化,当发掘优化后的代价过高时就能够不行使该项优化

当mrr=on,mrr_cost_based=off,则意味总是敞开M揽胜极光奇骏优化

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奥迪Q5奥迪R8类似,实际上M奥迪Q7卡宴也能够想象成二级索引和
primary key的join

万一被Join的表上未有索引,则选用老版本的BNL战术(BLOCK Nested-loop)

对于联接的列含有索引的意况,外界表的每条记下不再须求扫描整张内部表,只须要扫描内部表上的目录就可以获得联接的推断结果。

BKA原理

对此多表join语句,当MySQL使用索引访谈第二个join表的时候,使用贰个join
buffer来搜聚第贰个操作对象生成的有关列值。BKA创设好key后,批量传给引擎层做索引查找。key是通过MENVISIONCRUISER接口提交给引擎的(mrr目标是较为顺序)MHighlanderRAV4使得查询更有成效。 

大约的进度如下:

  • BKA使用join buffer保存由join的第八个操作发生的切合条件的数码

  • 接下来BKA算法塑造key来访谈被三番五次的表,并批量行使MLacrosse途观接口提交keys到数据仓库储存款和储蓄引擎去寻觅查找。

  • 付出keys之后,M逍客揽胜使用最好的主意来获取行并上报给BKA

BNL和BKA都以批量的交给一部分行给被join的表,进而降低访谈的次数,那么它们有什么样分别吧?

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

  • BNL首要用于当被join的表上无索引

  • BKA首要是指在被join表上有索引能够选取,那么就在行提交给被join的表在此以前,对那一个行根据索引字段张开排序,因而降低了随意IO,排序那才是两者最大的界别,然则只要被join的表没用索引呢?那就选拔NBL

在INNERAV4 JOIN中,两张联接表的次第是足以转变的,根据前面描述的Simple
Nested-Loops
Join算法,优化器在平常景色下延续选取将连接列含有索引的表作为内表。若是两张表Koleos和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使用了M福睿斯大切诺基,要想使用BAK必需打开MXC60LAND作用,而M普拉多Murano基于mrr_cost_based的本金估计并不能够担保总是采用M冠道福特Explorer,官方推荐设置mrr_cost_based=off来连接敞开MRubicon福特Explorer功效。张开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使用索引从表中检索行数据的一种优化…

图片 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施行布署如下:

图片 3

能够看见,SQL执行陈设的Extra列中晋升Using join
buffer,那就象征行使了Block Nested-Loops Join算法。MySql
5.6会在Extra列突显越发详细的新闻,如上边所示:

图片 4

注意点:在MySql 5.5本子中,Join Buffer只可以在INNE大切诺基 JOIN中运用,在OUTE陆风X8JOIN中则无法使用,即Block Nested-Loops Join算法不援助OUTE奥迪Q5JOIN。上边包车型地铁left join语句:

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

在MySql 5.第55中学的施行布署如下:

图片 5

能够见见并从未Using join buffer提醒,那就意味着未有选用Block
Nested-Loops Join算法,可是在MySql 5.6事后起头辅助,下边包车型大巴SQL语句在MySql
5.6中的实行布置如下:

图片 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(MRubiconSportage)接口。
  3. Multi-Range
    Read(M君越Enclave)通过收到的Key,根据其对应的ROWID实行排序,然后再实行多少的读取操作。
  4. 归来结果集给客商端。

Batched Key Access Join算法的本来面目上来说仍旧Simple Nested-Loops
Join算法,其产生的标准化为内部表上有索引,并且该索引为非主键,况且连接必要拜访内部表主键上的目录。那时Batched
Key Access Join算法会调用Multi-Range
Read(M奥德赛安德拉)接口,批量的实行索引键的至极和主键索引上获取数据的操作,以此来进步联接的进行功效。

对此Multi-Range
Read(M奥迪Q5LX570)的介绍属于MySql索引的内容,这里大致表达:MySQL
5.6的新天性MKoleosF12berlinetta。那天天性依照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
出处:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website