(ICP)是mysql使用索引从表中检索行数据的一种优化方式金沙js娱乐场官方网站,(ICP)是mysql使用索引从表中检索行数据的一种优化方式

BKA算法支持,该算法使用对连接表的索引访问和连接缓冲区,如果使用了Simple Nested-Loops Join算法,Simple Nested-Loops Join算法,(ICP)是mysql使用索引从表中检索行数据的一种优化方式,存储引擎通过索引过滤,(ICP)是mysql使用索引从表中检索行数据的一种优化方式,存储引擎通过索引过滤,(ICP)是mysql使用索引从表中检索行数据的一种优化方式,存储引擎通过索引过滤

金沙js娱乐场官方网站 2

Block Nested-Loop算法

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

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

举个简易的例子:外层循环结果集有一千行数据,使用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)

 

金沙js娱乐场官方网站 1

在未有利用MEscortPAJERO本性时

第一步 先依照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中华VCR-V 时,优化器要求基于二级索引再次来到的笔录来扩充“回表”,那一个进度相似会有相当多的自由IO, 使用MPAJEROLacrosse时,SQL语句的奉行进程是那般的:

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

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

  • 客户线程调用MEvoque宝马X5接口取cluster index,然后遵照cluster index 取行数据

  • 当依照缓冲区中的 cluster index取完数据,则再三再四调用进程 2)
    3),直至扫描截止

经过上述进度,优化器将二级索引随机的 IO 进行排序,转化为主键的静止排列,进而实现了自便 IO 到各类 IO 的转化,提高质量

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

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

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

四、总结

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

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

采取情形比如

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

BNL和BKA算法的优化器Hint

除去采用optimizer_switch系统变量来支配优化程序在对话范围内使用BNL和BKA算法之外,MySQL还援救优化程序提醒,以便在每一个语句的基本功上海电影制片厂响优化程序。
请参见“优化程序Hint”。

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

金沙js娱乐场官方网站 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 ...)) ...

 

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

动用境况举个例子

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

有关参数

BAK使用了MEvoqueENVISION,要想利用BAK必得张开M奥迪Q5Kuga功用,而M宝马X5XC60基于mrr_cost_based的本金估摸并不可能担保总是利用MENCORE宝马X3,官方推荐设置mrr_cost_based=off来连接敞开MKoleos途睿欧效率。张开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

 

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

举三个事例,把driver表的_create_date列和user表的create_date列的目录删除,进行联网查询,实践下边包车型大巴SQL语句:

除此以外M凯雷德索罗德还能将一些范围查询,拆分为键值对,来举办批量的数目查询,如下:

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

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

若不使用MOdyssey君越:此时查询的花色为Range,sql优化器会先将key_part1大于1000稍低于3000的数据收取,即便key_part2不对等一千0,带抽出之后再拓宽过滤,会变成众多无效的数量被抽取

若使用MRR:即便索引中key_part2不为一千0的元组更多,最后MPAJEROHighlander的效劳越好。优化器会将查询条件拆分为(一千,一千),(1001,一千),… (一九九六,一千)最后会依靠这几个准则进行过滤

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

二、Multi-Range Read (MRR)

M奥德赛奇骏 的齐全都以 Multi-Range Read
Optimization,是优化器将轻巧 IO 转化为顺序 IO 以减低查询进程中 IO 开销的一种花招,这对IO-bound类型的SQL语句质量带来巨大的升迁,适用于range
ref eq_ref类型的查询

M悍马H2福特Explorer优化的多少个平价

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

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

批量管理对键值的操作

Batched Key Access 算法

对此多表join语句,当MySQL使用索引访谈第三个join表的时候,使用贰个join
buffer来采摘第三个操作对象生成的有关列值。BKA构建好key后,批量传给引擎层做索引查找。key是经过M奥德赛LAND接口提交给引擎的,那样,M凯雷德汉兰达使得查询更有功用。

设若外界表扫描的是主键,那么表中的记录会见都是比较平稳的,可是要是连接的列是非主键索引,那么对于表中记录的看望大概正是可怜离散的。由此对于非主键索引的交接,Batched
Key Access
Join算法将能不小提升SQL的实行功效。BKA算法协助内接连,外接连和半连接操作,包蕴嵌套外接连。

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

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

  • 2) 批量的将Key(索引键值)发送到Multi-Range Read(M奥德赛翼虎)接口。

  • 3) Multi-Range
    Read(MRubiconXC90)通过收到的Key,根据其相应的ROWID举办排序,然后再拓宽多少的读取操作。

  • 4) 重临结果集给客户端。

Batched Key Access Join算法的面目上的话依然Simple Nested-Loops
Join算法,其发生的原则为个中表上有索引,而且该索引为非主键,并且连接供给拜望内部表主键上的目录。那时Batched
Key Access Join算法会调用Multi-Range
Read(MRubiconEnclave)接口,批量的张开索引键的合营和主键索引上获取数据的操作,以此来加强联接的进行作用,因为读取数据是以一一磁盘IO实际不是自由磁盘IO举行的。

使用BKA时,join_buffer_size的值定义了对存款和储蓄引擎的每种伏乞中批量密钥的分寸。缓冲区越大,对再三再四操作的左边表的顺序访谈就更加的多,那足以显着升高质量。

要使用BKA,必须将optimizer_switch系统变量的batched_key_access标注设置为on。
BKA使用MPAJEROEnclave,因而mrr标识也非得展开。近来,MSportageLX570的财力测度过于悲观。因而,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)

 

对此联接的列含有索引的景色,外界表的每条记下不再必要扫描整张内部表,只须要扫描内部表上的目录就可以得到联接的决断结果。

二、Multi-Range Read (MRR)

M福特Explorer智跑 的完备是 Multi-Range Read
Optimization,是优化器将随机 IO 转化为各类 IO 以减低查询进程中 IO 开支的一种手段,这对IO-bound类型的SQL语句品质带来巨大的进级,适用于range
ref eq_ref类型的查询

MLacrosseMurano优化的多少个低价

使数码访问有私行变为顺序,查询协理索引是,首先把询问结果依据主键实行排序,按执照主人键的依次举黑体签查找

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

批量拍卖对键值的操作

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

Batched Key Access (BKA)
 升高表join品质的算法。当被join的表能够利用索引时,就先排好顺序,然后再去搜索被join的表,听上去和MLX570揽胜类似,实际上M大切诺基Enclave也得以设想成二级索引和
primary key的join

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

四、总结

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
    lookup r in S index
        If find s == r
           Then output the tuple <r, s>

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

BKA原理

对此多表join语句,当MySQL使用索引访谈第1个join表的时候,使用贰个join
buffer来搜聚第贰个操作对象生成的相干列值。BKA营造好key后,批量传给引擎层做索引查找。key是经过M锐界陆风X8接口提交给引擎的(mrr目标是相比较顺序)MWrangler卡宴使得查询更有成效。 

粗粗的长河如下:

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

  • 下一场BKA算法营造key来访谈被连接的表,并批量行使MENCORE途胜接口提交keys到数据仓库储存款和储蓄引擎去搜寻查找。

  • 提交keys之后,MLAND奥迪Q7使用最好的艺术来获取行并申报给BKA

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

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

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

  • BKA主就算指在被join表上有索引可以应用,那么就在行提交给被join的表在此之前,对那么些行根据索引字段进展排序,因而削减了随意IO,排序那才是两岸最大的界别,可是只要被join的表没用索引呢?这就应用NBL

除此以外MR中华V仍能将一些范围查询,拆分为键值对,来拓宽批量的多少查询,如下:

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

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

若不利用M逍客奥德赛:此时查询的连串为Range,sql优化器会先将key_part1大于一千紧跟于两千的数目抽出,尽管key_part2不等于一千0,带抽出之后再拓宽过滤,会造成数不尽不行的数码被抽取

若使用MRR:假若索引中key_part2不为一千0的元组越多,最终M福睿斯逍客的效果与利益越好。优化器会将查询条件拆分为(一千,1000),(1001,1000),… (一九九六,一千)最后会依照这一个标准举办过滤

在MySql 5.5中的实践布署如下:

连带参数

当mrr=on,mrr_cost_based=on,则意味着cost
base的秘技还选拔启用M普拉多ENVISION优化,当开掘优化后的代价过高时就能够不应用该项优化

当mrr=on,mrr_cost_based=off,则象征总是敞开M奥迪Q5Escort优化

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

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

连带参数

当mrr=on,mrr_cost_based=on,则代表cost
base的方法还选取启用M凯雷德安德拉优化,当开掘优化后的代价过高时就能够不应用该项优化

当mrr=on,mrr_cost_based=off,则代表总是敞开MOdysseyPRADO优化

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

参数read_rnd_buffer_size 用来调整键值缓冲区的尺寸。二级索引围观到文件的最后恐怕缓冲区已满,则动用高效排序对缓冲区中的内容依据主键举行排序

BKA和BNL标识

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

MySql数据库使用Join Buffer的尺度如下:

BKA原理

对此多表join语句,当MySQL使用索引访问第3个join表的时候,使用三个join
buffer来搜集第叁个操作对象生成的连锁列值。BKA创设好key后,批量传给引擎层做索引查找。key是因此MRubicon福睿斯接口提交给引擎的(mrr指标是相比较顺序)MPRADOLX570使得查询更有效用。 

大意的进程如下:

  • BKA使用join buffer保存由join的首先个操作产生的符合条件的多寡

  • 下一场BKA算法创设key来访问被三番五次的表,并批量施用MTucsonLX570接口提交keys到数据仓库储存款和储蓄引擎去搜索查找。

  • 交付keys之后,MLacrosse奔驰G级使用最棒的法子来获取行并汇报给BKA

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

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

  • BNL主要用来当被join的表上无索引

  • BKA首借使指在被join表上有索引可以行使,那么就在行提交给被join的表此前,对那些行根据索引字段进行排序,由此削减了自由IO,排序那才是双边最大的分别,可是若是被join的表没用索引呢?那就动用NBL

二、Multi-Range Read (MRR)

MQashqaiENVISION 的齐全都以 Multi-Range Read
Optimization,是优化器将轻便 IO 转化为种种 IO 以收缩查询进程中 IO 开销的一种手段,那对IO-bound类型的SQL语句品质带来非常大的进级,适用于range
ref eq_ref类型的询问

M途乐翼虎优化的多少个好处

使数据访谈有专擅变为顺序,查询支持索引是,首先把询问结果依照主键举行排序,遵照主键的顺序举草书签查找

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

批量管理对键值的操作

BKA原理

对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用七个join
buffer来收罗第二个操作对象生成的有关列值。BKA塑造好key后,批量传给引擎层做索引查找。key是因此M哈弗奥迪Q5接口提交给引擎的(mrr指标是较为顺序)MEvoque中华V使得查询更有功效。 

差十分少的进度如下:

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

  • 下一场BKA算法构建key来访谈被连接的表,并批量运用M兰德酷路泽奥迪Q5接口提交keys到数据仓库储存款和储蓄引擎去追寻查找。

  • 付给keys之后,M卡宴Enclave使用最好的主意来获取行并举报给BKA

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

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

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

  • BKA首假如指在被join表上有索引能够应用,那么就在行提交给被join的表此前,对这几个行依据索引字段进展排序,由此减少了自由IO,排序那才是两岸最大的分别,但是只要被join的表没用索引呢?那就利用NBL

###Simple Nested-Loops Join算法
从一张表中年天命之年是读取一条记下,然后将记录与嵌套表中的记录进行比较。算法如下:

BKA和BNL标识

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

在未有使用MKoleos福睿斯性情时

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

在不选拔 MTiggoLacrosse 时,优化器必要依据二级索引重返的笔录来实行“回表”,那些历程相似会有比较多的随机IO, 使用MGL450福睿斯时,SQL语句的实施进度是这么的:

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

  • 一经二级索引围观到文件的结尾大概缓冲区已满,则选择高效排序对缓冲区中的内容依照主键进行排序

  • 客户线程调用MEvoque瑞虎接口取cluster index,然后依据cluster index 取行数据

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

透过上述进程,优化器将二级索引随机的 IO 举办排序,转化为主键的不改变排列,进而实现了自由 IO 到各种 IO 的转载,进步质量

能够看到,SQL施行布署的Extra列中唤醒Using join
buffer,那就意味着接纳了Block Nested-Loops Join算法。MySql
5.6会在Extra列展现尤其详细的音信,如上面所示:

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

BKA和BNL标识

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

有关参数

当mrr=on,mrr_cost_based=on,则象征cost
base的办法还选拔启用MRubicon凯雷德优化,当开采优化后的代价过高时就能够不接纳该项优化

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

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

参数read_rnd_buffer_size 用来支配键值缓冲区的轻重。二级索引围观到文件的最后或然缓冲区已满,则应用高效排序对缓冲区中的内容依照主键进行排序