mysql5.7官网直译SQL语句优化--块式嵌套循环和批量key访问连接

71 阅读 0 评论 47 点赞

我是靠谱客的博主爱撒娇草莓，最近开发中收集的这篇文章主要介绍mysql5.7官网直译SQL语句优化--块式嵌套循环和批量key访问连接，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.11 Block Nested-Loop and Batched Key Access Joins(块式嵌套循环和批量key访问连接)。
在mysql,一个批量key访问(BKA)连接算法可以使用在通过索引扫描而连接的表和一个连接缓存中。BKA算法支持内连接，外连接和semi-join操作，包括嵌套外连接。而BKA的好处包括通过提高表扫描效率来提高连接效率。当然循环嵌套块(BNL)算法原来被用来扩展内连接，并且能够用于外连接和semi-join操作，包括嵌套外连接。
下面的部分讨论了连接缓存管理，基于原BNL算法的扩展，扩展后的BNL算法和BKA算法。关于semi-join策略的信息，请看8.2.2.1的优化子查询，派生表，和试图引用通过semi-join 转变。
>对BNL(Block Nested-Loop)和BKA(Batched Key Access)算法的连接缓存的管理
>外连接和Semi-join连接中的BNL算法
>BKA连接
>优化器关于BNL和BKA算法的提示。
关于BNL算法和BKA算法的连接缓存管理
mysql能够使用连接缓存去执行查询，不仅仅是内连接中没有索引扫描的内部表，而且出现在子查询扁平化之后的外连接和semi-join也可以。同时，内部连接表中通过索引扫描查询，也同样可以有效的使用连接缓存。
连接缓存管理使得存储的都是感兴趣的行的列值在缓存中，从而提高缓存的使用效率：没有额外的字节被允许加入缓存，如果值为NULL，并且最小数目的字节数被分配给任何大小的varchar类型。
编码支持两种类型的缓存，规则的和递增的。假设缓存B1被用于表t1和t2的连接，并且其结果和t3表连接使用了缓存B2:
>一个规则的缓存包含的列来自每一个连接操作数。如果B2是一个规则的缓存，每一行放入缓存的数据行r都是由B1中的行r1中的列和从t3中匹配到的行r2中感兴趣的列值组成的。
>一个递增的缓存中包含的只有第二个连接操作产生的行中的列。也就是说，它是递增的从第一个操作缓存中得到的行。如果B2是一个递增的缓存，它包含了感兴趣的列从行r2中获取的，而行r2得到一个与B1中行r1的连接结合产生。
递增的缓存总是递增一个相关的缓存从一个较早的连接操作，所以来自第一个连接操作的缓存总是规则缓存。就刚刚给出的例子中，使用在t1和t2连接中的缓存B1就是一定是规则缓存。
递增缓存中的每一行数据都是为了一个连接操作，只包含所关心的行的列来自要连接的表中。这些列增强一个引用到感兴趣的列值，列值来自匹配到第一连接操作产生的表中的行。一些行在缓存中能够引用相同的行r,r中的列被存储在之前的连接缓存中，在这个范围内，所有这些行都和行r匹配。
递增缓存能够减少列数据复制从用于提前连接操作的缓存中。这能够减少缓存空间占用，因为大多数情况下在第一个缓存中的列都可以匹配多行在第二个连接中的数据。从第一个操作中的行数据并不需要多次复制。递增缓存因为减少了复制时间从而节省了查询处理时间。
block_nested_loop和batched_key_access标志，optimizer_switch系统变量中的两个标志能够控制优化器怎么用BNL和BKA连接算法。默认情况，block_nested_loop的值是on,batched_key_access的值是off。请看8.9.3的开关控制优化。优化器的提示也可以被使用。请看优化器的提示关于BNL和BKA算法。关于semi-join 策略信息，请看8.2.2.1优化子查询，派生表，试图引用通过semi-join转变。
BBL算法关于外连接和Semi-joins
MYSQLBNL算法最初的目的是用来扩展支持外连接和Semi-join操作的。
当这些操作在一个连接缓存中被执行，每一行放入缓存的数据都有一个匹配的标志。
如果一个外连接被执行通过一个连接缓存，表中的每一行通过第二个操作产生的都要去去检查是否有一个匹配与缓存中的行。如果存在匹配，那么一个新的扩展行就形成了(原有行数据+第二个操作中产生的列)并且通过接下来的连接操作进一步扩展。另外，在缓存中匹配的行的匹配标志是可以用的。当所有表中的行通过连接扫描后，连接缓存被扫描完。来自缓存的每一行,如果扩展完成通过null值补充(第二个操作中的每一列的值都是null)，则没有可用的匹配标志,并且进一步与接下来的连接操作扩展。
optimizer_switch系统变量中的block_nested_loop标志控制了优化器怎么样使用BNL算法。默认情况下，block_nested_loop是on,请看8.9.3的开关控制优化。优化器提示也可以用；请看优化器提示关于BNL和BKA算法。
在EXPLAIN的输出中，使用BNL的表意味着Extra值有Using join buffer(Block Nested Loop)且type的值是All,index或者是range。关于semi-join 策略信息，请看8.2.2.1优化子查询，派生表，试图引用通过semi-join转变。
BKA连接
mysql提供了一个连接表的方法被叫做Batched Key Access(BKA)连接算法。BKA能够被用当第二个丽娜姐操作中生成的表是通过索引查找而来的。和BNL算法一样，BKA算法使用一个连接缓存去计算在第一个连接操作中产生的感兴趣的列值。然后，BKA算法构造keys用于访问表与缓存中的所有行连接，然后批量提交这些keys到数据库引擎用于索引查找。keys提交通过多范围读取(MRR)接口(8.2.1.10的多范围读取优化)。提交keys之后，MRR引擎函数以优化的方法完成索引查找。通过这些keys找到连接表中的每一行数据，然后开始执行BKA连接算法通过匹配到行。每一个匹配到的行再加上一个引用被作为一行放在了连接缓存中。
当BKA被使用，值join_buffer_size定义多大一批的keys每次请求给存储引擎。缓存越大，顺序访问右边连接操作表中的行数越多，也就越能提高效率。
对于BKA的使用，batched_key_access标志的值必须是on。BKA使用MRR,所以mrr标志必须也是on。当前对MRR的估价太悲观，所以设置mrr_cost_based值为off是必要的为了使用BKA算法。下面的设置可以使用BKA算法：
mysql>
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

两种场景MRR函数执行：
>第一种场景是传统的基于硬盘的存储引擎例如InnoDB和MyISAM。对于这些引擎，连接缓存中的keys集合中的行被一次性被提交给MRR接口。引擎相关的MRR函数完成索引查找通过提交的keys值，得到行IDs(或者是主键集合)，然后一个一个的请求BKA算法对选择出的行IDs。被返回的每行数据都是能和缓存中的行匹配的一个关联引用。数据被查找通过MRR函数用一个高效的方法：他们根据行id顺序查找。因为顺序查找而不是随机查找，从而提高查询执行性能。
>第二种场景是用于远端存储引擎例如NDB。来自连接缓存的一部分行的keys包，和他们关联的一起被发送到NDB集群数据节点中通过一个mysql服务(sql节点)。反过来，sql节点接受一个包关于匹配的数据行和相关数据。BKA连接算法使用这些行构建一个新的连接行集合。然后把新的keys集合发送到到数据节点，并且从返回包中的数据用于构建新的连接行。这个过程会一直持续直到连接缓存中的最后一个key数据被发送到数据节点中，并且sql节点已经接受并且连接了所有这些匹配上的行数据。这会提高性能因为少量的key集合包被发送到sql节点，意味着少量的交互在数据节点和sql节点之间完成连接操作。
在第一场景，连接缓存的部分被用于存储行IDs，IDs集合是通过索引查询或者是MRR函数选择的参数的集合。
没有专门的缓存用于存储keys,keys来自连接缓存。然而，MRR函数可以将key构建到下一行的缓存中去。
在EXPLAIN输出中，表中使用了BKA，则Extra列的值中包含了Using join buffer(Batched Key Access) 并且type列的值是ref或者eq_ref。
优化提示关于BNL和BKA算法。
额外通过使用系统变量optimizer_switch去控制优化器在session范围内使用BNL和BKA算法，mysql支持优化器提示去影响有哈气在报表的每一部分。具体请看8.9.2的优化器的提示。
为了使用BNL或者BKA提示到可用的连接缓存对于任何一个外连接的内部表中，连接缓存必须能够缓存所有者个外连接的内部表。
到此，关于这两种算法的优化就已经结束了，那么接下来就是1.12IS NULL Optimization。关于IS NULL 的优化。