【MYSQL系列-优化4】——阻止嵌套循环和批量密钥访问连接

在MYSQL中，可以使用批量秘钥访问连接算法（BKA），它使用对连接的表和连接缓冲区的索引访问。批量秘钥访问连接算法支持内连接，外联结，和半连接操作，包括嵌套外连接。BKA的优点包括通过更有效的表扫描来提高连接性能。

此外，先前仅用于内部连接的块嵌套循环（BNL）连接算法被扩展，并且可以用于外连接和半连接操作，包括嵌套的外连接。
以下部分讨论了原始BNL算法，扩展BNL算法和BKA算法扩展的连接缓冲区管理。有关半连接策略的信息，第8.2.2.1节“使用半连接变换优化子查询，派生表，视图引用和公用表表达式。

1. 加入块嵌套循环和批量密钥访问算法的缓冲区管理
2. 对于外连接和半连接的块嵌套循环算法
3. 批量秘钥访问连接
4. 块嵌套循环和批量密钥访问算法的优化器提示

加入块嵌套循环和批量密钥访问算法的缓冲区管理

MySQL可以使用连接缓冲区来执行内部连接，而不需要对内部表进行索引访问，而且还可以在子查询平坦化之后出现外部连接和半连接。此外，当存在对内表的索引访问时，可以有效地使用连接缓冲区。

当存储感兴趣的行列的值时，连接缓冲区管理代码稍微更有效地利用连接缓冲区空间：如果其值为NULL，则在行列的缓冲区中不分配其他字节，并且为任何值分配最小字节数的VARCHAR类型。

该代码支持两种类型的缓冲区，即常规和增量。假设使用连接缓冲器B1来连接表t1和t2，并且使用连接缓冲器B2将该操作的结果与表t3相连接：

1. 常规连接缓冲区包含每个连接操作数的列。如果B2是常规连接缓冲区，则放入B2的每行r由B1的行r1的列和来自表t3的匹配行r2的感兴趣的列组成。

2. 增量连接缓冲区仅包含由第二个连接操作数生成的表的行。也就是说，它是从第一个操作数缓冲区的一行增量。如果B2是增量连接缓冲区，则它包含行r2的有趣列，以及来自B1的行r1的链接。

增量连接缓冲区总是相对于早期连接操作的连接缓冲区而言是增量的，因此来自第一个连接操作的缓冲区始终是常规缓冲区。在刚刚给出的示例中，用于连接表t1和t2的缓冲区B1必须是常规缓冲区。

用于连接操作的增量缓冲区的每一行仅包含要连接的表中的行的有趣列。对这些列进行扩充，引用从第一个连接操作数生成的表中的匹配行的有趣列。增量缓冲区中的几行可以引用其列存储在先前连接缓冲区中的相同行r，只要所有这些行与行r相匹配即可。

增量缓冲区可以较少地频繁地复制用于先前连接操作的缓冲区中的列。这提供了缓冲空间的节省，因为在一般情况下，由第一个连接操作数产生的行可以由第二个连接操作数产生的多个行匹配。没有必要从第一个操作数生成一行的几个副本。由于复制时间的减少，增量缓冲区还可以节省处理时间。

optimizer_switch系统变量的block_nested_loop和batched_key_access标志控制优化器如何使用块嵌套循环和批量密钥访问连接算法。默认情况下，block_nested_loop已打开，batched_key_access关闭。请参见第8.9.3节“可切换优化”。也可以应用优化器提示;请参阅块嵌套循环和批次密钥访问算法的优化器提示。

有关半连接策略的信息，请参见第8.2.2.1节“使用半连接转换优化子查询，派生表，视图引用和公用表表达式”

对于外连接和半连接的块嵌套循环算法

扩展了MySQL BNL算法的原始实现，以支持外连接和半连接操作。
当使用连接缓冲区执行这些操作时，放入缓冲区的每一行都将提供一个匹配标志。

如果使用连接缓冲区执行外连接操作，则会检查由第二个操作数生成的表的每一行，以针对连接缓冲区中的每一行进行匹配。当找到一个匹配时，形成一个新的扩展行（原始行加上来自第二个操作数的列），并通过其余的连接操作发送进一步扩展。此外，缓冲区中匹配行的匹配标志被使能。在要连接的表的所有行已经被检查之后，扫描连接缓冲区。来自缓冲区的每个不启用其匹配标志的行都被扩展为NULL补码（第二个操作数中每列的NULL值），并通过剩余的连接操作发送进一步扩展。

optimizer_switch系统变量的block_nested_loop标志控制优化器如何使用块嵌套循环算法。默认情况下，block_nested_loop为on。请参见第8.9.3节“可切换优化”。也可以应用优化器提示;请参阅块嵌套循环和批次密钥访问算法的优化器提示。

在EXPLAIN输出中，当Extra值包含使用连接缓冲区（块嵌套循环），类型值为ALL，索引或范围时，表示使用BNL表。

有关半连接策略的信息，请参见第8.2.2.1节“使用半连接转换优化子查询，派生表，视图引用和公用表表达式”

批量秘钥访问连接

MySQL实现了一种加入称为批量密钥访问（Batch）密钥访问（BKA）连接算法的方法。当存在对第二个连接操作数产生的表的索引访问时，可以应用BKA。像BNL连接算法一样，BKA连接算法使用连接缓冲器来累加由连接操作的第一个操作数产生的行的感兴趣的列。然后，BKA算法构建密钥以访问缓冲区中所有行的要连接的表，并将这些密钥批量提交到数据库引擎进行索引查找。密钥通过多范围读取（MRR）接口提交给引擎（参见第8.2.1.10节“多范围读取优化”）。提交密钥后，MRR引擎功能以最佳方式执行索引中的查找，获取由这些密钥找到的连接表的行，并开始使用匹配的行来提供BKA连接算法。每个匹配行与对连接缓冲区中的一行的引用相结合。

当使用BKA时，join_buffer_size的值定义了在存储引擎的每个请求中的批次键的大小。缓冲区越大，连接操作的右侧表格的顺序访问越多，这可以显着提高性能。

对于要使用的BKA，必须将optimizer_switch系统变量的batched_key_access标志设置为on。 BKA使用MRR，所以mrr标志也必须打开。目前，MRR的成本估算太悲观了。因此，为了使用BKA，也需要关闭mrr_cost_based。以下设置启用BKA：

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

MRR功能有两种情况执行：

1. 第一种情况用于常规的基于磁盘的存储引擎，如InnoDB和MyISAM。对于这些引擎，通常来自连接缓冲区的所有行的密钥一次提交到MRR接口。引擎特定的MRR功能对提交的键执行索引查找，从中获取行ID（或主键），然后通过BKA算法的请求逐个提取所有这些所选行ID的行。每行都返回一个关联引用，可以访问连接缓冲区中匹配的行。这些行以最佳方式由MRR函数获取：它们以行ID（主键）顺序获取。这可以提高性能，因为读取是以磁盘顺序而不是随机顺序。

2. 第二种情况用于远程存储引擎，如NDB。来自连接缓冲区的一部分行的一组密钥及其关联由MySQL服务器（SQL节点）发送到MySQL群集数据节点。作为回报，SQL节点接收与相应关联相匹配的匹配行的包（或多个包）。 BKA连接算法采用这些行并构建新的连接行。然后将一组新的密钥发送到数据节点，并使用返回的包中的行来构建新的连接行。该过程继续，直到来自连接缓冲区的最后一个密钥发送到数据节点，并且SQL节点已经接收并加入了与这些密钥相匹配的所有行。这提高了性能，因为SQL节点发送到数据节点的较少的承载包意味着它和数据节点之间的往返次数更少，以执行连接操作。

使用第一种情况，保留连接缓冲区的一部分来存储由索引查找选择的行ID（主键），并作为参数传递给MRR功能。

没有特殊的缓冲区来存储从连接缓冲区为行建立的键。相反，构建缓冲区中下一行的键的函数作为参数传递给MRR函数。

在EXPLAIN输出中，当额外值包含使用连接缓冲区（批量密钥访问），类型值为ref或eq_ref时，表示使用BKA表。

块嵌套循环和批量密钥访问算法的优化器提示

除了使用optimizer_switch系统变量来控制优化器在会话范围内使用BNL和BKA算法外，MySQL还支持优化器提示，以便在每个语句的基础上影​​响优化器。请参见第8.9.2节“优化程序提示”。

要使用BNL或BKA提示为外部连接的任何内部表启用连接缓冲，必须为外部连接的所有内部表启用连接缓冲。