MySQL8.0官方文档第八章优化（十三）——块嵌套循环和批量键访问连接

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.12 块嵌套循环和批量键访问连接

在MySQL中&＃xff0c;可以使用一种称为批量键访问&＃xff08;Batched Key Access --> BKA&＃xff09;的连接算法&＃xff0c;该算法在连接表时同时使用索引访问和连接缓冲区。BKA算法支持内连接、外连接和半连接操作&＃xff0c;包括嵌套的外连接。BKA的好处包括由于采用更高效的表扫描而提高了连接性能。此外&＃xff0c;以前只能用于内连接的块嵌套循环&＃xff08;Block Nested-Loop --> BNL&＃xff09;连接算法也得到了扩展&＃xff0c;现可以用于外连接和半连接操作&＃xff0c;包括嵌套的外连接。

下面的章节将讨论连接缓冲区管理&＃xff0c;它是原始BNL算法、扩展BNL算法和BKA算法的扩展基础。有关半连接策略的信息&＃xff0c;请参见8.2.2.1节“用半连接转换优化IN和EXISTS子查询谓词”。

块嵌套循环和批量键访问算法使用到的连接缓冲区管理

MySQL不仅可以使用连接缓冲区来执行没有索引访问的内部表的内连接&＃xff0c;还可以执行子查询完成后出现的外连接和半连接。此外&＃xff0c;当可以利用索引访问内部表时&＃xff0c;可以有效地使用连接缓冲区。

在存储感兴趣行中的列数值时&＃xff0c;连接缓冲区管理代码能更有效地利用连接缓冲区空间&＃xff1a;如果行中的列数值为NULL&＃xff0c;则不会在缓冲区中为其分配额外的字节&＃xff0c;并且为VARCHAR类型的任何值分配最小的字节数。

该代码支持两种类型的缓冲区&＃xff1a;常规缓冲区和增量缓冲区。假设使用连接缓冲区B1来连接表 t1 和表 t2&＃xff0c;并使用连接缓冲区B2将B1中已连接的行与表 t3 进行连接&＃xff1a;

• 常规连接缓冲区保存来自每个连接操作数中的列。如果B2是一个常规连接缓冲区&＃xff0c;那么放入 B2 中的每一行 r 都是由 B1中一行 r1 中的列和表 t3 中能匹配的行 r2 中的感兴趣的列组合而成的。

• 增量连接缓冲区只包含由第二个连接操作数产生的表中行中的列。也就是说&＃xff0c;它是从第一个操作数缓冲区基础上递增的一行。如果B2是一个增量连接缓冲区&＃xff0c;那么它包含了行 r2 中感兴趣的列以及对B1中行 r1 的链接。

增量连接缓冲区总是相对于前一个连接操作的连接缓冲区增量的&＃xff0c;因此第一个连接操作的缓冲区总是常规缓冲区。在刚刚给出的示例中&＃xff0c;用于连接表 t1 和 t2 的缓冲区B1必须是常规缓冲区。

用于连接操作的增量缓冲区中的每一行只包含要连接的表中一行感兴趣的列。这些列是通过引用第一个连接操作数生成的表中匹配行的感兴趣列来递增的。增量缓冲区中的多行可以引用同一行 r&＃xff0c;只要所有这些行与行 r 匹配&＃xff0c;而行 r 的列存储在前面的连接缓冲区中。

增量缓冲区使从前一个连接操作所用的缓冲区中复制列的频次降低。这一方面节省了缓冲区空间&＃xff0c;因为&＃xff0c;通常情况下&＃xff0c;第一个连接操作数产生的一行可能与第二个连接操作数产生的多行匹配。另一方面由于不必对第一个操作数产生的行进行多次复制&＃xff0c;从而减少了复制时间&＃xff0c;所以使用增量缓冲区还节省了处理时间。

在MySQL 8.0中&＃xff0c;系统变量optimizer_switch中的block_nested_loop&＃xff08;块嵌套循环&＃xff09;标记工作如下&＃xff1a;

• 在MySQL 8.0.20之前&＃xff0c;它控制优化器如何使用块嵌套循环连接算法。
• 在MySQL8.0.18及更高版本中&＃xff0c;它还控制哈希连接的使用&＃xff08;请参阅第8.2.1.4节“哈希连接优化”&＃xff09;。
• 从MySQL 8.0.20开始&＃xff0c;这个标志只控制哈希连接&＃xff0c;不再支持块嵌套循环算法。

batched_key_access&＃xff08;批量键访问&＃xff09;标志控制优化器如何使用批量键访问连接算法。

默认情况下&＃xff0c;block_nested_loop是开启的&＃xff0c;batched_key_access是关闭的。请参见8.9.2节&＃xff0c;“可切换优化”。也可以应用优化器提示&＃xff1b;请参阅块嵌套循环和批量键访问算法的优化提示。

有关半连接策略的信息&＃xff0c;请参阅第8.2.2.1节“使用半连接转换优化IN和EXISTS子查询谓词”

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

MySQL BNL算法的最初实现已被扩展为支持外连接和半连接操作&＃xff08;后来被哈希算法取代&＃xff1b;参见第8.2.1.4节“哈希连接优化”&＃xff09;。

当使用连接缓冲区执行这些操作时&＃xff0c;将为放入缓冲区的每一行都提供一个匹配标志。

如果使用连接缓冲区执行外连接操作&＃xff0c;则会检查第二个操作数生成的表中的每一行是否与连接缓冲区中的每一行相匹配。当找到匹配项时&＃xff0c;将形成新的扩展行&＃xff08;原始行加上第二个操作数相应的列&＃xff09;&＃xff0c;并发送至其余的连接操作执行进一步的扩展。此外&＃xff0c;缓冲区中匹配行的匹配标志设置为启用。在检查了要连接表中所有行之后&＃xff0c;执行扫描连接缓冲区。对缓冲区中匹配标志的未设置为启用的每一行都扩展为空补行&＃xff08;第二个操作数中的每列的值都设置为NULL&＃xff09;&＃xff0c;并发送至其余的连接操作执行进一步的扩展。

在MySQL 8.0中&＃xff0c;系统变量optimizer_switch的block_nested_loop&＃xff08;块嵌套循环&＃xff09;标记工作如下&＃xff1a;

• 在MySQL 8.0.20之前&＃xff0c;它控制优化器如何使用块嵌套循环连接算法。

• 在MySQL8.0.18及更高版本中&＃xff0c;它还控制哈希连接的是否使用&＃xff08;请参阅第8.2.1.4节“哈希连接优化”&＃xff09;。

• 从MySQL 8.0.20开始&＃xff0c;这个标志只控制哈希连接&＃xff0c;不再支持块嵌套循环算法。

有关更多信息&＃xff0c;请参阅第8.9.2节“可切换优化”。优化器提示也可以应用&＃xff1b;有关块嵌套循环和批量键访问算法&＃xff0c;请参见优化器提示。

在EXPLAIN输出中&＃xff0c;当Extra列的值包含Using join buffer&＃xff08;块嵌套循环&＃xff09;&＃xff0c;且type列的值为ALL、index或range时&＃xff0c;表示对表使用了 BNL。

有关半连接策略的信息&＃xff0c;请参阅第8.2.2.1节“使用半连接转换优化IN和EXISTS子查询谓词”

批量键范围连接

MySQL实现了一种称为批量键访问&＃xff08;BKA&＃xff09;连接算法的连接表方法。当第二个连接操作数生成的表有访问索引时&＃xff0c;就可以应用BKA。与BNL连接算法一样&＃xff0c;BKA连接算法使用连接缓冲区来累积在连接操作时第一个操作数生成的行中的感兴趣的列。然后&＃xff0c;在缓冲区中BKA算法构建要被访问连接表中的所有行的键&＃xff0c;并将这些键批量提交给数据库引擎用以索引查找。这些键通过多范围读&＃xff08;MRR&＃xff09;接口提交到引擎&＃xff08;见第8.2.1.11节“多范围读优化”&＃xff09;。在提交这些键后&＃xff0c;MRR引擎函数以最佳方式在索引中执行查找&＃xff0c;获取利用这些键找到连接表中的行&＃xff0c;并开始向BKA连接算法提供匹配行。每个匹配行都与连接缓冲区中的行进行引用耦合。

当使用BKA时&＃xff0c;join_buffer_size&＃xff08;连接缓冲区大小&＃xff09;的值定义了每次请求存储引擎时&＃xff08;译者&＃xff1a;要发送的&＃xff09;批量键的大小。该缓冲区越大&＃xff0c;一次对连接操作的右表进行的顺序访问就越多&＃xff0c;这可以显著提高性能。

要使用BKA&＃xff0c;必须把optimizer_switch系统变量的batched_key_access&＃xff08;批量键访问&＃xff09;标志设置为on。由于BKA需要使用MRR&＃xff0c;所以MRR标志也必须是打开的。目前&＃xff0c;MRR的成本估计过于悲观。因此&＃xff0c;要使用BKA&＃xff0c;也有必要关闭mrr_cost_based标志。以下设置启用BKA&＃xff1a;

mysql> SET optimizer_switch&＃61;&＃39;mrr&＃61;on,mrr_cost_based&＃61;off,batched_key_access&＃61;on&＃39;;


MRR函数执行有两种场景&＃xff1a;

• 第一个场景用于传统的基于磁盘的存储引擎&＃xff0c;如InnoDB和MyISAM。对于这些引擎&＃xff0c;连接缓冲区中所有行的键通常都会立即提交到MRR接口。特定于引擎的MRR函数对提交的键执行索引查找&＃xff0c;先从中得到行ID&＃xff08;或主键&＃xff09;&＃xff0c;然后根据BKA算法的请求逐个获取所有这些已选定行ID的行。每一行都会返回一个关联引用&＃xff0c;该引用允许访问连接缓冲区中匹配的行。MRR函数以最佳方式获取这些行&＃xff1a;它们以行ID&＃xff08;主键&＃xff09;顺序获取。因为数据是按磁盘顺序读取的&＃xff0c;而不是按照随机顺序&＃xff0c;所以提高了性能。

• 第二种场景用于NDB等远程存储引擎。MySQL服务器&＃xff08;SQL节点&＃xff09;将连接缓冲区中部分行的键和其链接一起打包发送到MySQL集群数据节点。作为回报&＃xff0c;SQL节点接收一个&＃xff08;或几个&＃xff09;匹配行和相应链接的包。BKA连接算法获取这些行并构建新连接的行。然后向数据节点发送一组新的键&＃xff0c;并使用返回包中的行来构建新的连接行。该过程将继续&＃xff0c;直到连接缓冲区中的最后一个键被发送到数据节点&＃xff0c;并且SQL节点已接收并连接与这些键匹配的所有行。这提高了性能&＃xff0c;因为SQL节点发送给数据节点的承载键的包更少&＃xff0c;这意味着它与数据节点之间执行连接操作的往返次数更少。

在第一种情况下&＃xff0c;会保留部分连接缓冲区来存储用于索引查找所选择的行ID&＃xff08;主键&＃xff09;&＃xff0c;并作为参数传递给MRR函数。

没有特殊的缓冲区来存储由连接缓冲区中的行构建的键。相反&＃xff0c;为缓冲区中的下一行生成键的函数作为参数传递给MRR函数。

在EXPLAIN输出中&＃xff0c;当Extra值包含Using join buffer (batch Key Access)&＃xff0c;且类型值为 ref 或 eq_ref 时&＃xff0c;表示对表使用BKA。

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

除了使用optimizer_switch系统变量来控制会话范围内BNL和BKA算法的优化器使用之外&＃xff0c;MySQL还支持优化器提示来影响每条语句的优化器。参见8.9.3节&＃xff0c;“优化提示”。

想要使用BNL或BKA提示来启用外连接中的任何内部表的连接缓冲&＃xff0c;必须为该外连接中的所有内部表都启用连接缓冲。

上一集 MySQL 8.0 官方文档 第八章 优化&＃xff08;十二&＃xff09;—— 多范围读优化

下一集 MySQL 8.0 官方文档 第八章 优化&＃xff08;十四&＃xff09;—— 条件过滤