金沙糖果派对网站app 2

Batched Key Access 算法

对于多表join语句,当MySQL使用索引访谈第三个join表的时候,使用一个join
buffer来搜集第多少个操作对象生成的相干列值。BKA构建好key后,批量传给引擎层做索引查找。key是因而MEnclave凯雷德接口提交给引擎的,那样,MKuga安德拉使得查询更有功效。

假若外部表扫描的是主键,那么表中的记录拜见都以相比较平稳的,不过就算连接的列是非主键索引,那么对于表中记录的拜见或者正是不行离散的。因而对此非主键索引的连片,Batched
Key Access
Join算法将能比很大增加SQL的试行成效。BKA算法协助内接连,外接连和半连接操作,包括嵌套外接连。

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

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

  • 2) 批量的将Key(索引键值)发送到Multi-Range Read(M卡宴Evoque)接口。

  • 3) Multi-Range
    Read(M奇骏LAND)通过收取的Key,依照其对应的ROWID举办排序,然后再开展多少的读取操作。

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

Batched Key Access Join算法的面目上的话仍然Simple Nested-Loops
Join算法,其产生的口径为内部表上有索引,况且该索引为非主键,况且连接要求拜谒内部表主键上的目录。那时Batched
Key Access Join算法会调用Multi-Range
Read(MSportage本田CR-V)接口,批量的进行索引键的非常和主键索引上获取数据的操作,以此来抓实联接的施行效用,因为读取数据是以一一磁盘IO并不是随便磁盘IO进行的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的各类央浼中批量密钥的大大小小。缓冲区越大,对连年操作的右边表的依次访问就更多,那能够显着进步质量。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标识设置为on。
BKA使用M福特Explorer奥迪Q5,因而mrr标识也非得展开。前段时间,M奥迪Q7本田UR-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)

 

要是t1,t2和t3三张表实施INNE普拉多 JOIN查询,並且每张表使用的交接类型如下:

连锁参数

当mrr=on,mrr_cost_based=on,则表示cost
base的点子还挑拣启用MXC60Highlander优化,当开采优化后的代价过高时就能够不行使该项优化

当mrr=on,mrr_cost_based=off,则表示总是敞开M瑞虎奇骏优化

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

参数read_rnd_buffer_size 用来调节键值缓冲区的轻重。二级索引围观到文件的最终只怕缓冲区已满,则接纳高效排序对缓冲区中的内容遵照主键举办排序

在未有使用M福睿斯奥迪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

BNL和BKA算法的优化器Hint

除却运用optimizer_switch系统变量来决定优化程序在对话范围内选择BNL和BKA算法之外,MySQL还扶助优化程序提醒,以便在各类语句的根底上海电影制片厂响优化程序。
请参见“优化程序Hint”。

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

金沙糖果派对网站app 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 ...)) ...

 

BKA和BNL标识

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

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筛选掉的数量的比重

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算法二遍只可以将一行数据传入内部存款和储蓄器循环,所以外层循环结果集有多少行,那么内部存储器循环将在实施多少次。

能够看来并不曾Using join buffer提示,那就意味着未有运用Block
Nested-Loops Join算法,可是在MySql 5.6事后开始帮忙,下边包车型大巴SQL语句在MySql
5.6中的实践安插如下:

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

别的MPRADOPRADO还是能够将某些范围查询,拆分为键值对,来打开批量的多寡查询,如下:

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

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

若不接纳MSportage奥迪Q5:此时询问的花色为Range,sql优化器会先将key_part1大于1000低于三千的多少抽取,尽管key_part2不等于10000,带收取之后再实行过滤,会导致数不胜数空头的数码被抽取

若使用MRR:要是索引中key_part2不为一千0的元组越来越多,最后MLX570猎豹CS6的职能越好。优化器会将查询条件拆分为(一千,一千),(1001,1000),… (一九九九,一千)最后会依据那几个规范举办过滤

Block Nested-Loop算法

MySQL
BNL算法原来只援助内连接,今后已帮衬外连接半连接操作,包括嵌套外连接

BNL算法原理:将外层循环的行/结果集存入join
buffer,内部存款和储蓄器循环的每一行数据与总体buffer中的记录做比较,可以减去内层循环的扫描次数

举个大约的例证:外层循环结果集有一千行数据,使用NLJ算法须求扫描内层表一千次,但只要利用BNL算法,则先抽出外层表结果集的100行存放到join
buffer,
然后用内层表的每一行数据去和那100行结果集做相比,能够三回性与100行数据开展比较,那样内层表其实只要求循环一千/100=14遍,减少了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)

 

在MySql 5.第55中学的实行安排如下:

连锁参数

BAK使用了M牧马人奥迪R8,要想利用BAK必得张开M昂科威Sportage成效,而M安德拉大切诺基基于mrr_cost_based的工本估算并不可能担保总是利用MLX570牧马人,官方推荐设置mrr_cost_based=off来延续敞开MRENVISION效能。展开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)

MPRADOCR-V 的全称是 Multi-Range Read
Optimization,是优化器将随便 IO 转化为顺序 IO 以减低查询进程中 IO 开销的一种手腕,那对IO-bound类型的SQL语句质量带来巨大的升迁,适用于range
ref eq_ref类型的询问

M奇骏Evoque优化的多少个实惠

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

调整和减少缓冲池中页被轮换的次数

批量管理对键值的操作

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

在MySQL中,能够采纳批量密钥访谈(BKA)连接算法,该算法使用对连接表的目录访谈和连接缓冲区。

BKA算法援助:内接二连三,外接连和半连接操作,包蕴嵌套外接连。

BKA的优点:特别急迅的表扫描升高了连接属性。

除此以外,先前仅用于内接连的块嵌套循环(BNL)连接算法现已扩张,可用来外连接半连接操作,包括嵌套外连接

以下部分商讨了连年缓冲区管理,它是原始BNL算法扩张,增加BNL算法和BKA算法的基本功。
有关半总是计策的消息,请参见“使用半连接转换优化子查询,派生表和视图引用”

  • Nested Loop Join
    算法

  • Block Nested-Loop
    算法

  • 金沙糖果派对网站app,Batched Key Access
    算法

  • BNL和BKA算法的优化器Hint

  1. 将表面表中相关的列归入Join Buffer中。
  2. 批量的将Key(索引键值)发送到Multi-Range Read(M途胜Sportage)接口。
  3. Multi-Range
    Read(M奥迪Q3帕杰罗)通过接收的Key,根据其相应的ROWID进行排序,然后再展开多少的读取操作。
  4. 回到结果集给顾客端。

使用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锐界福睿斯 时,优化器必要根据二级索引重返的记录来实行“回表”,那么些历程一般会有非常多的随便IO, 使用M本田UR-VENVISION时,SQL语句的实践进度是这么的:

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

  • 例如二级索引围观到文件的末尾只怕缓冲区已满,则使用高效排序对缓冲区中的内容依据主键进行排序

  • 客商线程调用M奥迪Q5ENCORE接口取cluster index,然后遵照cluster index 取行数据

  • 当根据缓冲区中的 cluster index取完数据,则一连调用进度 2)
    3),直至扫描结束

通过上述进度,优化器将二级索引随机的 IO 实行排序,转化为主键的有序排列,进而实现了随意 IO 到种种 IO 的中间转播,进步品质

有关参数

当mrr=on,mrr_cost_based=on,则意味cost
base的不二诀窍还选用启用M普拉多凯雷德优化,当开采优化后的代价过高时就会不选拔该项优化

金沙糖果派对2015cc,当mrr=on,mrr_cost_based=off,则意味着总是敞开MMurano君越优化

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

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

###Batched Key Access Joins算法 MySql 5.6发端协助Batched Key Access
Joins算法(简称BKA),该算法的思维是结合索引和group前边三种办法来增长(search
for match)查询相比较的操作,以此加速实行功效。

四、总结

ICP(Index Condition Pushdown)

Index Condition
Pushdown是用索引去表里取多少的一种优化,降低了引擎层访谈基表的次数和Server层访问存储引擎的次数,在引擎层就能够过滤掉多量的数额,收缩io次数,升高查询语句品质

MRR(Multi-Range Read)

是依靠协助/第二索引的查询,减弱自由IO,而且将随意IO转化为各类IO,进步查询效用。

  • 不使用MRR之前(MySQL5.6事先),先按照where条件中的帮助索引获取帮助索引与主键的集合,再经过主键来获取相应的值。辅助索引获取的主键来访问表中的数据会促成大肆的IO(扶助索引的蕴藏顺序并不是与主键的依次一致),随机主键不在同一个page里时会导致多次IO和Infiniti制读。

  • 使用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使用索引从表中检索行数据的一种优化…

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

Batched Key Access (BKA)
 进步表join品质的算法。当被join的表能够选择索引时,就先排好顺序,然后再去追寻被join的表,听上去和MLacrosse大切诺基类似,实际上M奔驰M级凯雷德也能够想象成二级索引和
primary key的join

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

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>

在未曾行使M冠道安德拉天性时

先是步 先遵照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

使用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哈弗哈弗 时,优化器供给基于二级索引重回的记录来扩充“回表”,那一个进度一般会有相当多的轻松IO, 使用M福睿斯纳瓦拉时,SQL语句的施行进度是那般的:

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

  • 设若二级索引围观到文件的结尾只怕缓冲区已满,则采取高效排序对缓冲区中的内容依据主键进行排序

  • 客商线程调用MRubiconXC60接口取cluster index,然后依照cluster index 取行数据

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

透过上述进度,优化器将二级索引随机的 IO 进行排序,转化为主键的静止排列,从而完成了任性 IO 到各样 IO 的转折,升高质量

留神:最后优化器明显联接表的逐贰只会遵照方便的围观开支来鲜明,即:M(外表)+M(外表)*N(内表);这里的表面和内表分别指的是外表和内表的围观次数,假若含有索引,正是索引B+树的惊人,其余一般都是表的记录数。

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

四、总结

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)。

 

参考:

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

二、Multi-Range Read (MRR)

MSportageKoleos 的全称是 Multi-Range Read
Optimization,是优化器将随机 IO 转化为顺序 IO 以减少查询进度中 IO 开支的一种手腕,那对IO-bound类型的SQL语句质量带来巨大的升高,适用于range
ref eq_ref类型的询问

M昂Cora景逸SUV优化的几个低价

使数码访谈有自由变为顺序,查询支持索引是,首先把询问结果根据主键进行排序,遵照主键的各样实燕体签查找

调整和减少缓冲池中页被轮换的次数

批量管理对键值的操作

运用情状举个例子

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

金沙糖果派对网站app 2

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筛选掉的多寡的比例

连带参数

BAK使用了MCRUISERLAND,要想利用BAK必须张开M奥迪Q7昂科雷功效,而M宝马X3帕杰罗基于mrr_cost_based的工本估摸并无法保障总是选取M本田CR-V奥迪Q5,官方推荐设置mrr_cost_based=off来连接敞开MEscortCRUISER作用。展开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

 

其推行安插如下:

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

Batched Key Access (BKA)
 升高表join质量的算法。当被join的表能够运用索引时,就先排好顺序,然后再去搜索被join的表,听上去和M奇骏安德拉类似,实际上MEnclaveLX570也足以想象成二级索引和
primary key的join

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

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

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

行使景况举个例子

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

BKA和BNL标识

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

admin

相关文章

发表评论

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