【mysql】关于ICP、MLacrossePAJERO、BKA等特点

作者:数据库

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

 

能够看来并未Using join buffer提醒,那就表示没有运用Block Nested-Loops Join算法,不过在MySql 5.6事后开首帮助,上面的SQL语句在MySql 5.6中的执行安插如下:

利用情况比如

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

二、Multi-Range Read (MRR)

M奔驰M级LAND 的全称是 Multi-Range Read Optimization,是优化器将随机 IO 转化为顺序 IO 以减少查询进程中 IO 费用的一种手腕,这对IO-bound类型的SQL语句质量带来巨大的进级,适用于range ref eq_ref类型的询问

M翼虎路虎极光优化的几个低价

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

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

批量管理对键值的操作

Block Nested-Loop算法

MySQL BNL算法原本只援助内连接,现在已援救外连接半连接操作,包括嵌套外连接

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

举个简易的例子:外层循环结果集有1000行数据,使用NLJ算法供给扫描内层表一千次,但如果应用BNL算法,则先收取外层表结果集的100行寄存到join buffer, 然后用内层表的每一行数据去和那100行结果集做比较,能够叁次性与100行数据开展相比,那样内层表其实只需求循环一千/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_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)

 

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

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

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

Batched Key Access 算法

对于多表join语句,当MySQL使用索引访谈第三个join表的时候,使用一个join buffer来收罗第三个操作对象生成的连锁列值。BKA创设好key后,批量传给引擎层做索引查找。key是因而M路虎极光GL450接口提交给引擎的,那样,M奥迪Q7PAJERO使得查询更有效率。

假设外界表扫描的是主键,那么表中的记录探望都以相比较平稳的,可是即使连接的列是非主键索引,那么对于表中记录的探问恐怕正是可怜离散的。由此对此非主键索引的连接,Batched Key Access Join算法将能极大加强SQL的实施功能。BKA算法帮助内连接,外接连和半连接操作,包蕴嵌套外接连。

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

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

  • 2) 批量的将Key(索引键值)发送到Multi-Range Read(MXC60奥迪Q5)接口。

  • 3) Multi-Range Read(M奇骏福睿斯)通过接受的Key,依据其对应的ROWID进行排序,然后再实行数据的读取操作。

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

Batched Key Access Join算法的面目上来讲依旧Simple Nested-Loops Join算法,其发出的条件为在这之中表上有索引,并且该索引为非主键,何况连接需求拜谒内部表主键上的目录。那时Batched Key Access Join算法会调用Multi-Range Read(M安德拉景逸SUV)接口,批量的进展索引键的相称和主键索引上获取数据的操作,以此来升高联接的实施功能,因为读取数据是以一一磁盘IO并非随机磁盘IO进行的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的每种供给中批量密钥的轻重缓急。缓冲区越大,对连日操作的左边表的逐个访谈就越多,那足以显着提升品质。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标注设置为on。 BKA使用M奔驰G级大切诺基,因而mrr标识也不能够不张开。近来,M陆风X8安德拉的本钱测度过于悲观。由此,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)

 

对此地方的SQL语句,使用Block Nested-Loops Join算法须要的小运3.84秒,而不行使的大运是11.93秒。能够观望Block Nested-Loops Join算法对质量指示广大。

BKA和BNL标识

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

在未曾使用M大切诺基Rubicon天性时

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

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

只顾:最后优化器分明联接表的依次只会根据切合的围观成本来明确,即:M(外表) M(外表)*N(内表);这里的表面和内表分别指的是外表和内表的围观次数,假诺含有索引,就是索引B 树的万丈,其余平常都以表的记录数。

连带参数

当mrr=on,mrr_cost_based=on,则表示cost base的法子还挑选启用M奥迪Q5GL450优化,当开采优化后的代价过高时就能不应用该项优化

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

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

参数read_rnd_buffer_size 用来决定键值缓冲区的轻重缓急。二级索引围观到文件的末段也许缓冲区已满,则利用高效排序对缓冲区中的内容依照主键举办排序

连带参数

当mrr=on,mrr_cost_based=on,则象征cost base的措施还采取启用M牧马人LX570优化,当开掘优化后的代价过高时就能不接纳该项优化

当mrr=on,mrr_cost_based=off,则象征总是敞开M哈弗奇骏优化

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

参数read_rnd_buffer_size 用来支配键值缓冲区的轻重缓急。二级索引围观到文件的末段可能缓冲区已满,则运用高效排序对缓冲区中的内容依据主键实行排序

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技艺内部情状:SQL编制程序》

使用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奥迪Q5瑞虎 时,优化器须要基于二级索引再次回到的笔录来实行“回表”,那一个进度相似会有相当多的妄动IO, 使用M福睿斯XC60时,SQL语句的实践进程是这样的:

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

  • 假定二级索引围观到文件的尾声也许缓冲区已满,则选用便捷排序对缓冲区中的内容按执照主人键实行排序

  • 客户线程调用M中华V中华V接口取cluster index,然后依照cluster index 取行数据

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

由此上述进度,优化器将二级索引随机的 IO 实行排序,转化为主键的稳步排列,从而实现了自由 IO 到各类 IO 的转速,升高品质

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

Batched Key Access (BKA)  进步表join质量的算法。当被join的表可以选取索引时,就先排好顺序,然后再去寻找被join的表,听上去和MLX570XC60类似,实际上M帕杰罗凯雷德也得以虚构成二级索引和 primary key的join

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

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

相关参数

BAK使用了M大切诺基Wrangler,要想采用BAK必须张开M奥迪Q3科雷傲作用,而M纳瓦拉ENCORE基于mrr_cost_based的开支预计并不可能保险总是采纳M哈弗奔驰M级,官方推荐设置mrr_cost_based=off来连接敞开MCR-V锐界功用。展开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

 

连锁参数

BAK使用了MLacrossePAJERO,要想行使BAK必得张开M揽胜极光揽胜作用,而MSportage凯雷德基于mrr_cost_based的工本估算并不可能确定保证总是利用M揽胜极光LAND,官方推荐设置mrr_cost_based=off来再三再四敞开M陆风X8Sportage成效。展开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

 

借使t1,t2和t3三张表实施INNE奥德赛 JOIN查询,何况每张表使用的衔接类型如下:

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

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

###Batched Key Access Joins算法 MySql 5.6最早支持Batched Key Access Joins算法(简称BKA),该算法的思维是整合索引和group前边三种格局来增加(search for match)查询相比较的操作,以此加速施行作用。

二、Multi-Range Read (MRR)

MRubicon奥迪Q7 的齐全都以 Multi-Range Read Optimization,是优化器将轻松 IO 转化为顺序 IO 以减低查询进程中 IO 开销的一种手腕,那对IO-bound类型的SQL语句品质带来巨大的晋升,适用于range ref eq_ref类型的查询

M标致RCZRAV4优化的多少个实惠

使数码访谈有专断变为顺序,查询援助索引是,首先把询问结果遵照主键实行排序,依据主键的相继举燕体签查找

调整和减弱缓冲池中页被交换的次数

批量甩卖对键值的操作

BKA原理

对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来搜聚第叁个操作对象生成的连锁列值。BKA创设好key后,批量传给引擎层做索引查找。key是通过MGL450牧马人接口提交给引擎的(mrr指标是比较顺序)M奇骏本田UR-V使得查询更有作用。 

轮廓的进度如下:

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

  • 然后BKA算法创设key来访谈被接连的表,并批量使用MLacrosse汉兰达接口提交keys到数据仓库储存储引擎去寻找查找。

  • 交由keys之后,M瑞虎Escort使用最棒的方法来获取行并反馈给BKA

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

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

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

  • BKA首若是指在被join表上有索引能够采取,那么就在行提交给被join的表此前,对这一个行依照索引字段进展排序,由此削减了随意IO,排序那才是两个最大的分别,可是假如被join的表没用索引呢?这就动用NBL

图片 2

BKA原理

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

粗粗的进程如下:

  • BKA使用join buffer保存由join的第二个操作爆发的切合条件的多少

  • 下一场BKA算法创设key来访谈被连接的表,并批量施用M奥迪Q3宝马X5接口提交keys到数据仓库储存款和储蓄引擎去搜索查找。

  • 付给keys之后,MEnclaveR使用最好的秘诀来获取行并报告给BKA

BNL和BKA都以批量的交由一部分行给被join的表,进而减少访谈的次数,那么它们有啥分别呢?

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

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

  • BKA主若是指在被join表上有索引能够应用,那么就在行提交给被join的表以前,对那几个行根据索引字段实行排序,因此削减了随机IO,排序那才是相互最大的区分,不过一旦被join的表没用索引呢?那就接纳NBL

使用情形例如

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

对于地方提到的多少个表打开联网操作,假如采取Join Buffer,则算法的伪代码如下:

其余M猎豹CS6汉兰达还足以将一些范围查询,拆分为键值对,来拓宽批量的数码查询,如下:

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

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

若不应用MLAND逍客:此时询问的体系为Range,sql优化器会先将key_part1大于一千低于3000的数码抽出,即便key_part2不对等10000,带抽出之后再举办过滤,会招致成千上万不算的数量被收取

若使用MRR:若是索引中key_part2不为一千0的元组越来越多,最终MLX570Wrangler的功用越好。优化器会将查询条件拆分为(一千,一千),(1001,1000),... (1996,1000)最后会基于这几个条件实行过滤

四、总结

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

 

参考:

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>

四、总结

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

BKA和BNL标识

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

图片 3

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

使用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揽胜极光景逸SUV 时,优化器要求基于二级索引再次来到的记录来拓宽“回表”,这么些进度平日会有非常多的随机IO, 使用M奥德赛凯雷德时,SQL语句的执行进程是这么的:

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

  • 如若二级索引围观到文件的最终或然缓冲区已满,则使用高效排序对缓冲区中的内容根据主键实行排序

  • 客户线程调用M福特Explorer奥迪Q3接口取cluster index,然后依照cluster index 取行数据

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

经过上述进度,优化器将二级索引随机的 IO 进行排序,转化为主键的不改变排列,进而达成了随机 IO 到各种 IO 的转会,升高性能

本文由ca88发布,转载请注明来源

关键词: ca88网址 MySQL yzc579亚洲城