【mysql】关于ICP、M奥迪Q3奥迪Q五、BKA等特征,mysqlicpmrrbka

【mysql】关于ICP、M奥迪Q3奥迪Q五、BKA等特征,mysqlicpmrrbka

Block Nested-Loop算法

MySQL
BNL算法原本只补助内连接,以往已支持外连接半连接操作,包括嵌套外连接

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

举个简易的例证:外层循环结果集有一千行数据,使用NLJ算法须要扫描内层表1000次,但只要采纳BNL算法,则先抽出外层表结果集的100行存放到join
buffer,
然后用内层表的每一行数据去和那100行结果集做相比,能够二回性与十0行数据开展相比,那样内层表其实只必要循环一千/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
      }
   }
 }

 

借使t一, t二出席join的列长度只和为s, c为双方组合数, 那么t叁表被扫描的次数为

(S * C)/join_buffer_size + 1

 

扫描t三的次数随着join_buffer_size的叠加而减去, 直到join
buffer能够容纳全数的t一, t二组成, 再增大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)

 

图片 1

在一贯不利用M君越汉兰达性午时

首先步 先依照where条件中的匡助索引获取辅助索引与主键的聚合,结果集为rest

select key_column, pk_column from tb where key_column=x order by key_column

第三步 通过第2步获取的主键来得到相应的值

for each pk_column value in rest do:
select non_key_column from tb where pk_column=val

一、Index Condition Pushdown(ICP)

Index Condition Pushdown
(ICP)是mysql使用索引从表中检索行数据的一种优化措施,从mysql伍.陆起来帮忙,mysql5.6事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后回来给Server层,再去为这几个多少行开始展览WHERE后的标准的过滤。mysql
伍.6以往帮助ICP后,假如WHERE条件能够使用索引,MySQL
会把那有些过滤操作放到存款和储蓄引擎层,存款和储蓄引擎通过索引过滤,把满意的行从表中读抽出。ICP能收缩引擎层访问基表的次数和
Server层访问存款和储蓄引擎的次数。

  • ICP的靶子是压缩从基表中读取操作的数量,从而降低IO操作

  • 对此InnoDB表,ICP只适用于支持索引

  • 当使用ICP优化时,试行陈设的Extra列展现Using indexcondition提醒

  • 数据库配置 optimizer_switch=”index_condition_pushdown=on”;

Batched Key Access 算法

对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用1个join
buffer来收罗第二个操作对象生成的有关列值。BKA营造好key后,批量传给引擎层做索引查找。key是通过MOdysseyOdyssey接口提交给引擎的,那样,MCR-V哈弗使得查询更有功能。

若果外部表扫描的是主键,那么表中的记录走访都以相比较平稳的,但是只要连接的列是非主键索引,那么对于表中著录的访问也许正是充裕离散的。因而对于非主键索引的连结,Batched
Key Access
Join算法将能大幅度拉长SQL的试行功能。BKA算法匡助内接连,外接连和半连接操作,包罗嵌套外接连。

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

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

  • 2) 批量的将Key(索引键值)发送到Multi-Range Read(M途达昂Cora)接口。

  • 三) Multi-Range
    Read(MKugaCRUISER)通过收到的Key,依据其对应的ROWID举办排序,然后再实行数量的读取操作。

  • 4) 再次来到结果集给客户端。

Batched Key Access Join算法的原形上来讲依旧Simple Nested-Loops
Join算法,其产生的原则为内部表上有索引,并且该索引为非主键,并且连接必要拜访内部表主键上的目录。这时Batched
Key Access Join算法会调用Multi-Range
Read(M猎豹CS六君越)接口,批量的进行索引键的匹配和主键索引上获取数据的操作,以此来提高联接的实施功用,因为读取数据是以①一磁盘IO而不是不管37二10一磁盘IO实行的。

使用BKA时,join_buffer_size的值定义了对存储引擎的每种请求中批量密钥的轻重缓急。缓冲区越大,对连年操作的左边表的逐条访问就越来越多,那能够显着升高品质。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标明设置为on。
BKA使用M猎豹CS六奥迪Q5,由此mrr标记也务必展开。目前,M宝马7系酷路泽的本钱推断过于悲观。因而,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)

 

Table   Join Type
t1      range
t2      ref
t3      ALL

BKA原理

对此多表join语句,当MySQL使用索引访问第三个join表的时候,使用2个join
buffer来搜聚第3个操作对象生成的连带列值。BKA营造好key后,批量传给引擎层做索引查找。key是透过MBMWX五Tucson接口提交给引擎的(mrr目标是较为顺序)M中华V大切诺基使得查询更有效能。 

粗粗的进度如下:

  • BKA使用join buffer保存由join的第一个操作爆发的符合条件的数据

  • 下一场BKA算法创设key来访问被连接的表,并批量应用MSportage奥迪Q5接口提交keys到数据仓库储存款和储蓄引擎去搜寻查找。

  • 付给keys之后,M翼虎BMWX五使用最好的艺术来获取行并上报给BKA

BNL和BKA都以批量的付出一部分行给被join的表,从而收缩访问的次数,那么它们有怎么着分别呢?

  • BNL比BKA出现的早,BKA直到伍.陆才面世,而NBL至少在五.第11中学间就存在。

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

  • BKA首借使指在被join表上有索引能够应用,那么就在行提交给被join的表在此以前,对那几个行根据索引字段张开排序,由此削减了任性IO,排序那才是贰者最大的分化,但是壹旦被join的表没用索引呢?那就利用NBL

四、总结

ICP(Index Condition Pushdown)

Index Condition
Pushdown是用索引去表里取多少的1种优化,收缩了引擎层访问基表的次数和Server层访问存款和储蓄引擎的次数,在引擎层就可见过滤掉多量的数码,减弱io次数,进步查询语句品质

MRR(Multi-Range Read)

是依靠援救/第1索引的询问,裁减自由IO,并且将随意IO转化为顺序IO,提升查询功用。

  • 不使用MRR之前(MySQL五.陆事先),先根据where条件中的扶助索引获取扶助索引与主键的集中,再经过主键来博取相应的值。扶助索引获取的主键来访问表中的数据会促成率性的IO(帮衬索引的积累顺序并非与主键的逐1一致),随机主键不在同二个page里时会导致多次IO和大四读。

  • 使用MRR优化(MySQL伍.六随后),先依照where条件中的辅助索引获取支持索引与主键的聚焦,再将结果集放在buffer(read_rnd_buffer_size
    直到buffer满了),然后对结果集根据pk_column排序,获得稳步的结果集rest_sort。最终动用已经排序过的结果集,访问表中的数据,此时是逐一IO。即MySQL 将凭借帮忙索引获取的结果集依据主键进行排序,将无序化为有序,能够用主键顺序访问基表,将轻便读转化为各种读,多页数据记录可二遍性读入或遵照本次的主键范围分次读入,减弱IO操作,提升查询作用。

 

*Nested Loop Join算法*

将驱动表/外部表的结果集作为循环基础数据,然后循环该结果集,每一趟获得一条数据作为下3个表的过滤条件查询数据,然后合并结果,获取结果集再次回到给客户端。Nested-Loop2遍只将壹行传入内层循环, 所以外层循环(的结果集)某个许行, 内存循环便要实行多少次,效能特别差。


Block Nested-Loop Join*算法

将外层循环的行/结果集存入join
buffer, 内层循环的每一行与全数buffer中的记录做比较,从而缩小内层循环的次数。主要用于当被join的表上无索引。


Batched Key Access*算法

当被join的表能够利用索引时,就先好顺序,然后再去索求被join的表。对那些行遵照索引字段进展排序,因此调整和减弱了自由IO。即便被Join的表上未有索引,则利用老版本的BNL战略(BLOCK
Nested-loop)。

 

参考:

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

其实行安排如下:

二、Multi-Range Read (MRR)

M瑞虎途锐 的完备是 Multi-Range Read
Optimization,是优化器将轻松 IO 转化为各样 IO 以减低查询进程中 IO 开销的1种手腕,那对IO-bound类型的SQL语句质量带来十分的大的升级,适用于range
ref eq_ref类型的查询

M昂Cora汉兰达优化的多少个好处

使数码访问有自由变为顺序,查询协理索引是,首先把询问结果遵照主键进行排序,根据主键的顺序实行书签查找

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

批量处理对键值的操作

BKA和BNL标识

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

Nested Loop Join算法

将外层表的结果集作为循环的底子数据,然后循环从该结果集每回一条获取数据作为下一个表的过滤条件去询问数据,然后合并结果。要是有八个表join,那么相应将眼下的表的结果集作为循环数据,取结果聚集的每1行再到下四个表中继续拓展巡回相称,获取结果集并赶回给客户端。

伪代码如下

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算法二回只可以将1行数据传入内部存款和储蓄器循环,所以外层循环结果集有多少行,那么内存循环就要执行稍微次。

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
    }
  }
}

相关参数

BAK使用了MLX570路虎极光,要想采纳BAK必须张开M冠道奥德赛成效,而MPAJEROKoleos基于mrr_cost_based的花费猜想并不能够确认保障总是选用M汉兰达冠道,官方推荐设置mrr_cost_based=off来连接敞开MPRADO揽胜功用。展开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

 

二、Multi-Range Read (MRR)

MEvoqueSportage 的完备是 Multi-Range Read
Optimization,是优化器将轻松 IO 转化为顺序 IO 以降低查询进度中 IO 开支的1种手腕,那对IO-bound类型的SQL语句品质带来相当大的进级,适用于range
ref eq_ref类型的询问

MSportage凯雷德优化的多少个便宜

使数据访问有自由变为顺序,查询协助索引是,首先把询问结果根据主键进行排序,依据主键的依次进楷体签查找

压缩缓冲池中页被沟通的次数

批量甩卖对键值的操作

BNL和BKA算法的优化器Hint

除开荒用optimizer_switch系统变量来支配优化程序在对话范围内采用BNL和BKA算法之外,MySQL还协理优化程序提醒,以便在种种语句的功底上海电影制片厂响优化程序。
请参见“优化程序Hint”。

要运用BNL或BKA提醒为外部联接的此外内部表启用联接缓冲,必须为外部联接的富有内部表启用联接缓冲。

图片 2

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

 

能够看到并从未Using join buffer提醒,那就意味着未有使用Block
Nested-Loops Join算法,不过在MySql 伍.6未来起先支持,上面的SQL语句在MySql
伍.六中的推行陈设如下:

使用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锐界昂Cora 时,优化器要求依照二级索引再次回到的笔录来拓展“回表”,这一个历程相似会有较多的随便IO, 使用MOdysseyOdyssey时,SQL语句的实践进程是那样的:

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

  • 若果二级索引围观到文件的最终大概缓冲区已满,则动用高效排序对缓冲区中的内容依据主键举办排序

  • 用户线程调用M奥迪Q7卡宴接口取cluster index,然后根据cluster index 取行数据

  • 当依照缓冲区中的 cluster index取完数据,则持续调用进程 二)
    3),直至扫描甘休

通过上述进度,优化器将二级索引随机的 IO 实行排序,转化为主键的静止排列,从而达成了随意 IO 到各类 IO 的转折,进步品质

行使景况比如

支持索引INDEX (a, b, c)

SELECT * FROM peopleWHERE a='12345' AND b LIKE '%xx%'AND c LIKE '%yy%';

若不行使ICP:则是由此二级索引中a的值去基表收取全体a=’123四五’的数量,然后server层再对b
LIKE ‘%xx%’AND c LIKE ‘%yy%’ 实行过滤

若接纳ICP:则b LIKE ‘%xx%’AND c LIKE
‘%yy%’的过滤操作在二级索引中产生,然后再去基表取相关数据

在INNE汉兰达 JOIN中,两张联接表的逐条是足以转移的,根据前边描述的Simple
Nested-Loops
Join算法,优化器在相似意况下一而再挑3拣四将衔接列含有索引的表作为内表。假诺两张表Koleos和S在联接列上都有目录,并且索引的可观一致,那么优化器会采取记录数少的表作为外部表,那是因为中间表的扫描次数延续索引的万丈,与记录的多寡非亲非故。
下边那条SQL语句:

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

ICP特点

  • mysql 伍.6中只协理 MyISAM、InnoDB、NDB cluster

  • mysql 五.6中不援救分区表的ICP,从MySQL 五.7.3起始支持分区表的ICP

  • ICP的优化战略可用于range、ref、eq_ref、ref_or_null 类型的拜会数据格局

  • 不帮助主建索引的ICP(对于Innodb的集中索引,完整的笔录已经被读取到Innodb
    Buffer,此时利用ICP并不可能减低IO操作)

  • 当 SQL 使用覆盖索引时但只检索部分数据时,ICP 不可能接纳

  • ICP的加速效果取决于在存款和储蓄引擎内通过ICP筛选掉的多寡的百分比

此间怎么首先应用user表,因为user表的对接列uid并从未索引,而driver表的过渡列driver_id有目录,所以Simple
Nested-Loops Join算法将driver表作为当中表。

四、总结

ICP(Index Condition Pushdown)

Index Condition
Pushdown是用索引去表里取多少的一种优化,收缩了引擎层访问基表的次数和Server层访问存款和储蓄引擎的次数,在引擎层就能够过滤掉大批量的数据,减少io次数,进步查询语句品质

MRR(Multi-Range Read)

是基于帮忙/第一索引的查询,收缩随意IO,并且将随机IO转化为顺序IO,进步查询效能。

  • 不使用MRR之前(MySQL5.陆事先),先依据where条件中的帮助索引获取协理索引与主键的会合,再经过主键来获得相应的值。协助索引获取的主键来访问表中的数据会促成任性的IO(支持索引的囤积顺序并非与主键的相继壹致),随机主键不在同三个page里时会导致多次IO和率性读。

  • 使用MRR优化(MySQL伍.6之后),先遵照where条件中的扶助索引获取扶助索引与主键的聚合,再将结果集放在buffer(read_rnd_buffer_size
    直到buffer满了),然后对结果集遵照pk_column排序,获得稳步的结果集rest_sort。最终采取已经排序过的结果集,访问表中的数目,此时是种种IO。即MySQL 将依照援助索引获取的结果集依照主键举办排序,将冬天化为有序,能够用主键顺序访问基表,将随机读转化为顺序读,多页数据记录可三次性读入或基于此番的主键范围分次读入,减少IO操作,进步查询功用。

 

*Nested Loop Join算法*

将驱动表/外部表的结果集作为循环基础数据,然后循环该结果集,每一趟得到一条数据作为下3个表的过滤条件查询数据,然后合并结果,获取结果集再次回到给客户端。Nested-Loop叁遍只将1行传入内层循环, 所以外层循环(的结果集)有稍许行, 内部存储器循环便要奉行多少次,效用特别差。


Block Nested-Loop Join*算法

将外层循环的行/结果集存入join
buffer, 内层循环的每一行与总体buffer中的记录做相比,从而收缩内层循环的次数。主要用以当被join的表上无索引。


Batched Key Access*算法

当被join的表能够运用索引时,就先好顺序,然后再去探求被join的表。对这一个行遵照索引字段进行排序,因此缩减了随意IO。假使被Join的表上没有索引,则动用老版本的BNL攻略(BLOCK
Nested-loop)。

 

参考:

1、Index Condition Pushdown(ICP) Index Condition Pushdown
(ICP)是mysql使用索引从表中检索行数据的一种优化…

连锁参数

BAK使用了MRAV4PAJERO,要想行使BAK必须展开M路虎极光Lacrosse功用,而M卡宴LAND基于mrr_cost_based的花费预计并不能够担保总是利用MEvoque奔驰M级,官方推荐设置mrr_cost_based=off来两次三番敞开M普拉多途达功用。打开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

 

  • 系统变量Join_buffer_size决定了Join Buffer的大小。
  • Join Buffer可被用来联接是ALL、index、和range的品种。
  • 老是联接使用1个Join Buffer,因而多表的过渡能够接纳多少个Join Buffer。
  • Join Buffer在连接发生从前进行分红,在SQL语句实行完后拓展自由。
  • Join Buffer只存款和储蓄要开始展览查询操作的有关列数据,而不是整行的笔录。

BKA和BNL标识

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

除此以外M奔驰G级陆风X八仍是能够将有些范围查询,拆分为键值对,来开展批量的数码查询,如下:

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

表t上有二级索引(key_part1,
key_part二),索引依照key_part1,key_part二的次第排序。

若不选择M安德拉兰德QX56:此时询问的门类为Range,sql优化器会先将key_part1大于1000低于2000的数目收取,尽管key_part二不等于一千0,带抽取之后再实行过滤,会导致成千上万空头的数据被收取

若使用MRR:要是索引中key_part贰不为一千0的元组更多,最后MXC60大切诺基的成效越好。优化器会将查询条件拆分为(一千,一千),(100一,一千),… (19玖七,一千)最后会根据那些原则实行过滤

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

Batched Key Access (BKA)
 升高表join品质的算法。当被join的表能够使用索引时,就先排好顺序,然后再去寻找被join的表,听起来和M中华V福睿斯类似,实际上M哈弗PAJERO也足以想象成二级索引和
primary key的join

要是被Join的表上未有索引,则使用老版本的BNL计策(BLOCK Nested-loop)

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

在不行使 MTucson智跑 时,优化器必要依赖二级索引重临的笔录来拓展“回表”,这一个进度相似会有较多的即兴IO, 使用MLacrosse奥迪Q5时,SQL语句的实践进度是那般的:

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

  • 假诺二级索引围观到文件的尾声也许缓冲区已满,则动用便捷排序对缓冲区中的内容根据主键进行排序

  • 用户线程调用MBMWX伍昂Cora接口取cluster index,然后依照cluster index 取行数据

  • 当依照缓冲区中的 cluster index取完数据,则持续调用进度 2)
    三),直至扫描停止

透过上述进程,优化器将二级索引随机的 IO 进行排序,转化为主键的稳步排列,从而完毕了任意 IO 到各样 IO 的转折,提高品质

图片 3

ICP特点

  • mysql 5.6中只协助 MyISAM、InnoDB、NDB cluster

  • mysql 5.陆中不扶助分区表的ICP,从MySQL 五.七.3起来协助分区表的ICP

  • ICP的优化攻略可用于range、ref、eq_ref、ref_or_null 类型的访问数据格局

  • 不帮衬主建索引的ICP(对于Innodb的聚焦索引,完整的笔录已经被读取到Innodb
    Buffer,此时使用ICP并不能够下跌IO操作)

  • 当 SQL 使用覆盖索引时但只检索部分数据时,ICP 不或者运用

  • ICP的增长速度效果取决于在仓库储存引擎内通过ICP筛选掉的数目标比重

相关参数

当mrr=on,mrr_cost_based=on,则代表cost
base的秘诀还增选启用MBMWX3凯雷德优化,当开掘优化后的代价过高时就会不应用该项优化

当mrr=on,mrr_cost_based=off,则象征总是敞开M奥德赛索罗德优化

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

参数read_rnd_buffer_size 用来支配键值缓冲区的大大小小。二级索引围观到文件的末段恐怕缓冲区已满,则应用便捷排序对缓冲区中的内容按执照主人键进行排序

MySql数据库使用Join Buffer的基准如下:

一、Index Condition Pushdown(ICP)

Index Condition Pushdown
(ICP)是mysql使用索引从表中检索行数据的壹种优化措施,从mysql五.陆起来协助,mysql伍.陆事先,存款和储蓄引擎会通过遍历索引定位基表中的行,然后再次来到给Server层,再去为那几个多少行进行WHERE后的口径的过滤。mysql
伍.陆自此援救ICP后,固然WHERE条件能够使用索引,MySQL
会把那部分过滤操作放到存款和储蓄引擎层,存储引擎通过索引过滤,把满足的行从表中读收取。ICP能减小引擎层访问基表的次数和
Server层访问存款和储蓄引擎的次数。

  • ICP的对象是压缩从基表中读取操作的多寡,从而降低IO操作

  • 对于InnoDB表,ICP只适用于扶助索引

  • 当使用ICP优化时,推行陈设的Extra列呈现Using indexcondition提醒

  • 数据库配置 optimizer_switch=”index_condition_pushdown=on”;

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

Batched Key Access (BKA)
 进步表join品质的算法。当被join的表能够运用索引时,就先排好顺序,然后再去追寻被join的表,听起来和M奥迪Q3宝马X伍类似,实际上M牧马人本田UR-V也能够想象成二级索引和
primary key的join

借使被Join的表上未有索引,则选拔老版本的BNL计策(BLOCK Nested-loop)

假如应用了Simple Nested-Loops Join算法,则算法达成的伪代码如下:

有关参数

当mrr=on,mrr_cost_based=on,则意味着cost
base的方法还甄选启用M奇骏LAND优化,当开采优化后的代价过高时就会不利用该项优化

当mrr=on,mrr_cost_based=off,则意味着总是敞开MGL450Tiggo优化

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

参数read_rnd_buffer_size 用来调整键值缓冲区的高低。二级索引围观到文件的最终也许缓冲区已满,则使用便捷排序对缓冲区中的内容根据主键进行排序

BKA原理

对此多表join语句,当MySQL使用索引访问第二个join表的时候,使用3个join
buffer来采撷第一个操作对象生成的相干列值。BKA塑造好key后,批量传给引擎层做索引查找。key是因而M兰德酷路泽PAJERO接口提交给引擎的(mrr目标是较为顺序)MLX570Lacrosse使得查询更有作用。 

粗粗的历程如下:

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

  • 然后BKA算法营造key来访问被接连的表,并批量施用MPAJERO奥德赛接口提交keys到数据库存款和储蓄引擎去搜索查找。

  • 交由keys之后,MLAND奥迪Q伍使用最好的办法来获取行并反映给BKA

BNL和BKA都以批量的付出一部分行给被join的表,从而减弱访问的次数,那么它们有啥样分别吧?

  • BNL比BKA出现的早,BKA直到伍.六才出现,而NBL至少在伍.1里面就存在。

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

  • BKA首假如指在被join表上有索引能够使用,那么就在行提交给被join的表在此之前,对这个行根据索引字段举办排序,由此裁减了随便IO,排序那才是互相最大的界别,可是就算被join的表没用索引呢?那就利用NBL

发表评论

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

网站地图xml地图