MySQL 8.0 参考手册——8.2优化 SQL 语句(一)
数据库应用程序的核心逻辑是通过 SQL 语句来执行的，无论是通过解释器直接发出还是通过 API 在后台提交。本节中的调整指南有助于提高各种 MySQL 应用程序的速度。指南涵盖读写数据的 SQL 操作、一般 SQL 操作的幕后开销，以及数据库监控等特定场景中使用的操作。
一、 优化 SELECT 语句
8.2.1.1 优化 WHERE 子句
本节讨论在处理 WHERE 子句时可以进行的优化。示例使用的是 SELECT 语句，但同样的优化也适用于 DELETE 和 UPDATE 语句中的 WHERE 子句。
你可能会受到诱惑，为了加快算术运算的速度，而重写查询，牺牲可读性。但由于MySQL会自动执行类似的优化，你通常可以避免这项工作，并让查询保持更易于理解和维护的形式。MySQL执行的一些优化包括：
删除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d)))) -> (a AND b AND c) OR (a AND b AND c AND d)
常数合并：
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
常数条件移除：
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) -> b=5 OR b=6
索引使用的常量表达式只评估一次。
从 MySQL 8.0.16 开始，数字类型列与常量值的比较会进行检查，删除无效或过时的值：
# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL); SELECT * FROM t WHERE c ≪ 256; -≫ SELECT * FROM t WHERE 1;
对于 MyISAM 表和 MEMORY 表，将直接从表信息中获取单个表不带 WHERE条件 的 COUNT(*)。如果只对一个表使用任何 NOT NULL 表达式，也会这样做。
早期检测无效常量表达式。MySQL 能快速检测到某些 SELECT 语句是不可能的，因此不返回任何记录。
如果不使用 GROUP BY 或聚合函数（COUNT()、MIN() 等），HAVING 将与 WHERE 合并。
对于连接中的每个表，都会构造一个更简单的 WHERE，以便快速评估表的 WHERE 值，并尽快跳过记录。
首先读取所有常量表，然后才读取查询中的其他表。常量表是指以下任何一种表：
1，空表或只有一行的表。
2，在 PRIMARY KEY 或 UNIQUE 索引上使用 WHERE 子句的表，所有索引部分都与常量表达式比较，并定义为 NOT NULL。
以下所有表格均用作常量表：
SELECT * FROM t WHERE primary_key=1; SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
通过尝试所有可能性，可以找到连接表的最佳连接组合。如果 ORDER BY 和 GROUP BY 子句中的所有列都来自同一张表，则在连接时优先选择该表。
如果存在 ORDER BY 子句和不同的 GROUP BY 子句，或者 ORDER BY 或 GROUP BY 包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。
如果使用 SQL_SMALL_RESULT 修改器，MySQL 将使用内存中的临时表。
每个表的索引都会被查询，除非优化器认为使用表扫描更有效，否则会使用最佳索引。曾经，使用扫描的依据是最佳索引是否跨越表的 30% 以上，但使用索引还是扫描不再由一个固定的百分比来决定。现在的优化器更加复杂，会根据表大小、行数和 I/O 块大小等其他因素进行估计。
在某些情况下，MySQL 可以从索引中读取记录，甚至无需查阅数据文件。如果从索引中使用的所有列都是数字列，则只使用索引树来解决查询。
在输出每一行之前，会跳过与 HAVING 子句不匹配的记录。
快速查询的一些示例：
SELECT COUNT(*) FROM tbl_name; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
假设索引列是数字列，MySQL 只使用索引树解决以下查询：
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; SELECT MAX(key_part2) FROM tbl_name GROUP BY key_part1;
以下查询使用索引检索按排序顺序排列的行，而无需单独的排序过程：
SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... ;
8.2.1.2 范围优化
范围访问方法使用单个索引来检索包含在一个或多个索引值区间内的表行子集。它可用于单部分或多字段索引。下文将介绍优化程序使用范围访问的条件。
- 单字段索引的范围访问方法
- 多字段索引的范围访问方法
- 多值比较的相等范围优化
- 跳过扫描范围访问方法
- 行构造函数表达式的范围优化
- 限制范围优化的内存使用
单字段索引的范围访问方法
对于单字段索引，索引值区间可以方便地用 WHERE 子句中的相应条件来表示，表示为范围条件而不是 "区间"。
单字段索引的范围条件定义如下：
- 对于 BTREE 和 HASH 索引，在使用 =、<=>、IN()、IS NULL 或 IS NOT NULL 操作符时，键部分与常量值的比较是范围条件。
- 此外，对于 BTREE 索引，在使用 >、<、>=、<=、BETWEEN、!= 或 <> 操作符或 LIKE 比较（如果 LIKE 的参数是不以通配符开头的常量字符串）时，键部分与常量值的比较是范围条件。
- 对于所有索引类型，多个范围条件与 OR 或 AND 结合在一起构成一个范围条件。
上述说明中的 "常量值 "是指以下其中之一：
- 查询字符串中的常数
- 来自同一连接的常量表或系统表的一列
- 不相关子查询的结果
- 完全由上述类型的子表达式组成的表达式
下面是一些在 WHERE 子句中使用范围条件的查询示例：
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10; SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20); SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';
在优化器常量传播阶段，一些非常量值可能会被转换为常量。
MySQL 会尝试从 WHERE 子句中为每个可能的索引提取范围条件。在提取过程中，不能用于构建范围的条件会被丢弃，产生重叠范围的条件会被合并，产生空范围的条件会被删除。
请看下面的语句，其中 key1 是有索引的列，而 nonkey 没有索引：
SELECT * FROM t1 WHERE (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z');
key1 的提取过程如下：
1，从原始 WHERE 子句开始：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')
2，删除 nonkey = 4 和 key1 LIKE '%b'，因为它们不能用于范围扫描。正确的删除方法是用 TRUE 替换它们，这样我们在进行范围扫描时就不会遗漏任何匹配的记录。用 "true "替换它们的结果是
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
3，折叠条件总是为真或为假：
-
(key1 LIKE 'abcde%' OR TRUE)is always true -
(key1 < 'uux' AND key1 > 'z')is always false
将这些条件替换为常数即可得到：
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
去掉不必要的 TRUE 和 FALSE 常量，结果是：
(key1 < 'abc') OR (key1 < 'bar')
4，将重叠的区间合并为一个区间，就得到了用于范围扫描的最终条件：
(key1 < 'bar')
一般来说（如前面的示例所示），范围扫描使用的条件比 WHERE 子句的限制性要小。MySQL 会执行额外检查，以过滤掉满足范围条件但不满足完整 WHERE 子句的记录。
范围条件提取算法可以处理任意深度的嵌套 AND/OR 结构，其输出与 WHERE 子句中条件出现的顺序无关。
对于空间索引的范围访问方法，MySQL 不支持合并多个范围。要绕过这一限制，可以使用 UNION 和相同的 SELECT 语句，只是将每个空间谓词放在不同的 SELECT 中。
多字段索引的范围访问方法
多字段索引的范围条件是单字段索引范围条件的扩展。多字段索引的范围条件限制索引行位于一个或多个关键元组区间内。关键元组区间是在一组关键元组上定义的，使用的是索引的排序。
例如，考虑一个定义为 key1（key_part1、key_part2、key_part3）的多字段索引，以及以下按 key的顺序排列的键元组：
key_part1 key_part2 key_part3 NULL 1 'abc' NULL 1 'xyz' NULL 2 'foo' 1 1 'abc' 1 1 'xyz' 1 2 'abc' 2 1 'aaa'
条件 key_part1 = 1 定义了这个区间：
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
该区间涵盖了前面数据集中的第 4、5 和 6 行，可用于范围访问方法。
相比之下，key_part3 = 'abc'条件没有定义单个区间，因此不能用于范围访问方法。
下面的描述将详细说明范围条件如何在多字段索引中起作用。
对于 HASH 索引，可以使用包含相同值的每个区间。这意味着只能为以下形式的条件创建区间：
key_part1 cmp const1 AND key_part2 cmp const2 AND ... AND key_partN cmp constN;
这里，const1、const2、...是常数，cmp 是 =、<=> 或 IS NULL 比较操作符之一，条件涵盖所有索引部分。(例如，下面是一个由三部分组成的 HASH 索引的范围条件：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
对于 BTREE 索引，区间可用于与 AND 结合使用的条件，其中每个条件使用 =、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE "模式"（其中 "模式 "不以通配符开头）比较关键部分和常量值。只要能确定一个包含符合条件的所有行的单键元组（如果使用 <> 或 != 则为两个区间），就可以使用区间。
只要比较操作符是 =、<=> 或 IS NULL，优化器就会尝试使用其他关键部分来确定区间。如果运算符为 >、<、>=、<=、!=、<>、BETWEEN 或 LIKE，优化器将使用它，但不再考虑其他关键部分。在下面的表达式中，优化器使用了第一次比较中的 =。它还使用了第二次比较中的 >=，但不再考虑其他关键部分，也不使用第三次比较来构建区间：
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
单一区间为：
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
创建的区间可能包含比初始条件更多的行。例如，前一个区间包含的值 ('foo', 11, 0) 并不满足初始条件。
如果将涵盖区间内所包含的行集的条件与 OR 结合在一起，它们就会形成一个条件，涵盖其区间结合处所包含的行集。如果将这些条件与 AND 结合在一起，则会形成一个条件，覆盖包含在其区间的交集内的行集。例如，对于这个由两部分组成的索引的条件：
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
这些区间是：
(1,-inf) < (key_part1,key_part2) < (1,2) (5,-inf) < (key_part1,key_part2)
在本例中，第一行的区间左边界使用一个关键部分，右边界使用两个关键部分。第二行的区间只使用了一个关键字部分。EXPLAIN 输出中的 key_len 列表示所使用的关键字前缀的最大长度。
在某些情况下，key_len 可能表示使用了一个关键字部分，但这可能与你的预期不同。假设 key_part1 和 key_part2 可以是 NULL。那么 key_len 列会显示以下条件下的两个键部分长度：
key_part1 >= 1 AND key_part2 < 2
但事实上，条件是这样转换的：
key_part1 >= 1 AND key_part2 IS NOT NULL
多值比较的相等范围优化
考虑这些表达式，其中 col_name 是索引列：
col_name IN(val1, ..., valN) col_name = val1 OR ... OR col_name = valN
如果 col_name 等于多个值中的任意一个，则每个表达式为真。这些比较是相等范围比较（其中 "范围 "是单个值）。优化器会按以下方式估算为相等范围比较读取合格记录的成本：
如果 col_name 上有唯一索引，则每个范围的行估计值为 1，因为最多只能有一条记录具有给定值。
否则，col_name 上的任何索引都是非唯一的，优化器可以通过深入索引或索引统计来估算每个范围的行数。
通过索引潜入，优化器会在范围的两端进行潜入，并使用范围内的行数作为估计值。例如，表达式 col_name IN (10, 20, 30) 有三个相等范围，优化器会在每个范围进行两次下潜，以生成行估计值。每一对下潜都会产生具有给定值的行数估计值。
索引潜入可提供精确的行估计，但随着表达式中比较值数量的增加，优化器生成行估计所需的时间也会延长。使用索引统计的准确性不如索引潜入，但可以更快地对大数值列表进行行估计。
通过 eq_range_index_dive_limit 系统变量，可以配置优化器从一种行估计策略切换到另一种行估计策略的值数。要允许在最多 N 个相等范围的比较中使用索引下潜，请将 eq_range_index_dive_limit 设置为 N + 1。如果要禁用统计信息并始终使用索引下潜，而不管 N 多 少，则应将 eq_range_index_dive_limit 设置为 0。
要更新表索引统计以获得最佳估计，请使用 ANALYZE TABLE。
在 MySQL 8.0 之前，除了使用 eq_range_index_dive_limit 系统变量外，无法跳过使用索引潜入来估计索引有用性。在 MySQL 8.0 中，满足所有这些条件的查询都可以跳过索引潜入：
- 查询针对的是单个表，而不是多个表的连接。
- 存在单索引 FORCE INDEX 索引提示。这样做的目的是，如果强制使用索引，则不会从执行索引潜入的额外开销中获得任何收益。
- 索引是非唯一索引，不是 FULLTEXT 索引。
- 不存在子查询。
- 没有 DISTINCT、GROUP BY 或 ORDER BY 子句。
对于 EXPLAIN FOR CONNECTION，如果跳过索引下潜，输出结果会发生如下变化：
- 对于传统输出，行数和过滤值均为 NULL。
- 对于 JSON 输出，不会出现 rows_examined_per_scan 和 rows_produced_per_join，skip_index_dive_due_to_force 为 true，成本计算也不准确。
在不使用 FOR CONNECTION 的情况下，当跳过索引潜入时，EXPLAIN 输出不会发生变化。
在执行了跳过索引潜入的查询后，信息模式 OPTIMIZER_TRACE 表中的相应行包含一个跳过索引潜入的索引_dives_for_range_access 值。
跳过扫描范围访问方法
请考虑以下情况：
CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (2,1), (2,2), (2,3), (2,4), (2,5); INSERT INTO t1 SELECT f1, f2 + 5 FROM t1; INSERT INTO t1 SELECT f1, f2 + 10 FROM t1; INSERT INTO t1 SELECT f1, f2 + 20 FROM t1; INSERT INTO t1 SELECT f1, f2 + 40 FROM t1; ANALYZE TABLE t1; EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
要执行此查询，MySQL 可以选择索引扫描来获取所有记录（索引包括要选择的所有列），然后应用 WHERE 子句中的 f2 > 40 条件来生成最终结果集。
范围扫描比全索引扫描更有效，但在本例中不能使用，因为在第一个索引列 f1 上没有条件。不过，从 MySQL 8.0.13 开始，优化器可以执行多次范围扫描，对 f1 的每个值都执行一次，使用的方法称为跳过扫描，与松散索引扫描类似
- 跳过第一个索引部分 f1（索引前缀）的不同值。
- 针对剩余索引部分中 f2 > 40 条件的每个不同前缀值执行子范围扫描。
对于前面显示的数据集，算法是这样运行的：
- 获取第一个关键字部分的第一个不同值 (f1 = 1)。
- 根据第一个和第二个关键字部分构建范围（f1 = 1 AND f2 > 40）。
- 执行范围扫描。
- 获取第一个关键字部分的下一个不同值 (f1 = 2)。
- 根据第一个和第二个关键字部分构建范围（f1 = 2 AND f2 > 40）。
- 执行范围扫描。
使用此策略可减少访问记录的数量，因为 MySQL 会跳过不符合每个构造范围的记录。这种跳过扫描访问方法适用于以下条件：
- 表 T 至少有一个复合索引，其关键部分的形式为（[A_1, ..., A_k,]B_1, ..., B_m,C[, D_1, ..., D_n]）。关键部分 A 和 D 可以为空，但 B 和 C 必须为非空。
- 查询只引用一个表。
- 查询未使用 GROUP BY 或 DISTINCT。
- 查询只引用索引中的列。
- 关于 A_1、...、A_k 的谓词必须是相等谓词，并且必须是常量。这包括 IN() 操作符。
- 查询必须是连接查询，即 OR 条件的 AND：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
- C 列上必须有一个范围条件。
- 允许在 D 列上设置条件。D 列上的条件必须与 C 列上的范围条件相结合。
跳转扫描的使用在 EXPLAIN 输出中显示如下：
- 在 Extra 列中使用索引进行跳过扫描表示使用了松散索引跳过扫描访问方法。
- 如果索引可用于跳过扫描，则索引应在 possible_keys 列中可见。
在优化器跟踪输出中，"skip scan"（跳过扫描）元素以这种形式表示使用了跳过扫描：
"skip_scan_range": { "type": "skip_scan", "index": index_used_for_skip_scan, "key_parts_used_for_access": [key_parts_used_for_access], "range": [range] }
您可能还会看到一个 "best_skip_scan_summary "元素。如果选择跳过扫描作为最佳范围访问变量，则会写入 "chosen_range_access_summary"。如果选择跳过扫描作为总体的最佳访问方法，则会出现一个 "best_access_path "元素。
跳过扫描的使用取决于 optimizer_switch 系统变量的 skip_scan 标志值。要禁用它，请将 skip_scan 设置为 off。
除了使用 optimizer_switch 系统变量控制优化器在整个会话范围内使用 Skip Scan 之外，MySQL 还支持优化器提示，以便在每条语句的基础上影响优化器。
行构造表达式的范围优化
优化器可以对这种形式的查询应用范围扫描访问方法：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
以前，要使用范围扫描，必须将查询写成这样：
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' ) OR ( col_1 = 'c' AND col_2 = 'd' );
要让优化器使用范围扫描，查询必须满足这些条件：
- 只使用 IN() 谓词，不使用 NOT IN()。
- 在 IN() 谓词的左侧，行构造函数只包含列引用。
- 在 IN() 谓词的右侧，行构造函数只包含运行时常量，这些常量要么是字面量，要么是在执行过程中绑定到常量的本地列引用。
- 在 IN() 谓词的右侧，有不止一个行构造函数。
限制内存使用以优化范围
要控制范围优化器的可用内存，请使用 range_optimizer_max_mem_size 系统变量：
- 值为 0 表示 "无限制"。
- 如果值大于 0，优化器在考虑范围访问方法时会跟踪消耗的内存。如果即将超过指定的限制，就会放弃范围访问方法，转而考虑其他方法，包括全表扫描。这可能不是最佳选择。如果发生这种情况，就会出现以下警告（其中 N 是当前 range_optimizer_max_mem_size 值）：
Warning 3170 Memory capacity of N bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
对于 UPDATE 和 DELETE 语句，如果优化器退回到全表扫描，并且启用了 sql_safe_updates 系统变量，则会出现错误而不是警告，因为实际上没有使用键来确定要修改哪些行。
如果个别查询超出了可用的范围优化内存，优化器会退回到较差的优化计划，那么增加 range_optimizer_max_mem_size 值可能会提高性能。
要估算处理范围表达式所需的内存量，请使用以下指南：
对于像下面这样的简单查询（其中有一个候选键用于范围访问方法），每个谓词与 OR 结合使用约为 230 字节：
SELECT COUNT(*) FROM t WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
同样，对于像下面这样的查询，每个谓词与 AND 结合使用大约 125 个字节：
SELECT COUNT(*) FROM t WHERE a=1 AND b=1 AND c=1 ... N;
对于包含 IN() 谓词的查询，可以使用"...... "查询：
SELECT COUNT(*) FROM t WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
IN() 列表中的每个字面值都算作一个与 OR 结合的谓词。如果有两个 IN() 列表，则与 OR 结合的谓词个数是每个列表中字面值个数的乘积。因此，上述情况中与 OR 结合的谓词个数为 M × N。
8.2.1.3 索引合并优化
索引合并访问方法通过多个范围扫描检索记录，并将其结果合并为一个结果。这种访问方法只合并单个表的索引扫描，不合并多个表的扫描。合并可以产生底层扫描的联合、交叉或交叉联合。
可使用索引合并的查询示例：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
索引合并优化算法有以下已知限制：
如果您的查询有一个复杂的 WHERE 子句，其中包含较深的 AND/OR 嵌套，而 MySQL 没有选择最佳计划，请尝试使用以下身份转换来分配子句：
(x AND y) OR z => (x OR z) AND (y OR z) (x OR y) AND z => (x AND z) OR (y AND z)
索引合并不适用于全文索引。
在 EXPLAIN 输出中，索引合并方法在类型列中显示为 index_merge。在这种情况下，key 列包含使用的索引列表，key_len 包含这些索引的最长键部分列表。
索引合并访问方法有多种算法，这些算法显示在 EXPLAIN 输出的 Extra 字段中：
- Using intersect(...)
- Using union(...)
- Using sort_union(...)
下文将详细介绍这些算法。优化器会根据各种可用选项的成本估算，在不同的索引合并算法和其他访问方法中做出选择。
- 索引合并交叉访问算法
- 索引合并联合访问算法
- 索引合并排序-联合访问算法
- 影响索引合并优化的因素
索引合并交叉访问算法
这种访问算法适用于将 WHERE 子句转换为不同键上的多个范围条件并结合 AND 的情况，且每个条件都是以下条件之一：
- 这种形式的 N 部分表达式，其中索引正好有 N 部分（即所有索引部分都包括在内）：
- key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
- InnoDB 表主键上的任何范围条件。
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1 = 20; SELECT * FROM tbl_name WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
索引合并交集算法会对所有使用的索引执行同步扫描，并生成从合并索引扫描中获得的行序列交集。
如果所使用的索引覆盖了查询中使用的所有列，则不会检索到完整的表行（在这种情况下，EXPLAIN 输出包含 Extra 字段中的 Using index）。下面是这样一个查询示例：
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
如果使用的索引没有覆盖查询中使用的所有列，则只有在满足所有使用键的范围条件时，才会检索到完整的行。
如果合并条件之一是 InnoDB 表主键的条件，则不会用于检索记录，而是用于过滤使用其他条件检索的记录。
索引合并联合访问算法
该算法的标准与索引合并交叉算法的标准类似。该算法适用于将表中的 WHERE 子句转换为不同键上的多个范围条件，并与 OR 结合使用，且每个条件都是以下条件之一时：
这种形式的 N 部分表达式，其中索引正好有 N 部分（即所有索引部分都包括在内）：
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
InnoDB 表主键上的任何范围条件。
适用索引合并交叉算法的条件。
SELECT * FROM t1 WHERE key1 = 1 OR key2 = 2 OR key3 = 3; SELECT * FROM innodb_table WHERE (key1 = 1 AND key2 = 2) OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
索引合并排序联合访问算法
这种访问算法适用于 WHERE 子句转换为由 OR 组合的多个范围条件时，但不适用索引合并联合算法。
SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
sort-union 算法与 union 算法的区别在于，sort-union 算法必须首先获取所有行的 ID 并对其进行排序，然后才能返回任何行。
影响索引合并优化
索引合并的使用受优化器开关系统变量 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 标志值的限制。默认情况下，所有这些标志都处于开启状态。要只启用某些算法，可将 index_merge 设置为关闭，并只启用其他应允许的算法。
除了使用 optimizer_switch 系统变量控制优化器在整个会话范围内使用索引合并算法外，MySQL 还支持优化器提示，以便在每条语句的基础上影响优化器
8.2.1.4 哈希连接优化
默认情况下，MySQL（8.0.18 及更高版本）会尽可能使用散列连接。可以使用 BNL 和 NO_BNL 优化器提示，或通过设置 block_nested_loop=on 或 block_nested_loop=off 作为优化器开关服务器系统变量设置的一部分，来控制是否采用散列连接。
#MySQL 8.0.18 支持在 optimizer_switch 中设置 hash_join 标志，以及优化器提示 HASH_JOIN 和 NO_HASH_JOIN。在 MySQL 8.0.19 及更高版本中，这些提示都不再有效。
从 MySQL 8.0.18 开始，MySQL 对每个联接都有等联接条件的任何查询都采用哈希联接，其中没有可应用于任何联接条件的索引，如查询：
SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1;
当有一个或多个索引可用于单表谓词时，也可以使用哈希连接。
哈希连接通常比以前版本的 MySQL 中使用的块嵌套循环算法（见块嵌套循环连接算法）更快，因此在这种情况下使用哈希连接。从 MySQL 8.0.20 开始，对块嵌套循环的支持被移除，服务器会在以前使用块嵌套循环的地方使用哈希连接。
在刚才的示例和本节的其他示例中，我们假设使用以下语句创建了三个表 t1、t2 和 t3：
CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT);
使用 EXPLAIN 可以看到哈希连接的使用情况，如下所示：
mysql> EXPLAIN -> SELECT * FROM t1 -> JOIN t2 ON t1.c1=t2.c1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using join buffer (hash join)
只要每对表中至少有一个连接条件是等连接，哈希连接也可用于涉及多重连接的查询，如下面所示的查询：
SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);
在如图所示的使用内连接的情况下，任何非等连接的附加条件都会在连接执行后作为筛选器应用。(对于外连接，如左连接、半连接和反连接，它们会作为连接的一部分打印出来）。在 EXPLAIN 的输出中可以看到这一点：
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) -> JOIN t3 -> ON (t2.c1 = t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (t3.c1 = t1.c1) (cost=1.05 rows=1) -> Table scan on t3 (cost=0.35 rows=1) -> Hash -> Filter: (t1.c2 < t2.c2) (cost=0.70 rows=1) -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1)
从刚刚显示的输出结果中也可以看出，多重哈希连接可以（而且已经）用于具有多个等连接条件的连接。
在 MySQL 8.0.20 之前，如果任何一对连接的表没有至少一个等连接条件，就不能使用散列连接，只能使用较慢的块嵌套循环算法。在 MySQL 8.0.20 及更高版本中，散列连接可用于这种情况，如下所示：
mysql> EXPLAIN FORMAT=TREE -> SELECT * FROM t1 -> JOIN t2 ON (t1.c1 = t2.c1) -> JOIN t3 ON (t2.c1 < t3.c1)\G *************************** 1. row *************************** EXPLAIN: -> Filter: (t1.c1 < t3.c1) (cost=1.05 rows=1) -> Inner hash join (no condition) (cost=1.05 rows=1) -> Table scan on t3 (cost=0.35 rows=1) -> Hash -> Inner hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1)
哈希连接也适用于笛卡尔乘积，即未指定连接条件时，如图所示：
mysql> EXPLAIN FORMAT=TREE -> SELECT * -> FROM t1 -> JOIN t2 -> WHERE t1.c2 > 50\G *************************** 1. row *************************** EXPLAIN: -> Inner hash join (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Filter: (t1.c2 > 50) (cost=0.35 rows=1) -> Table scan on t1 (cost=0.35 rows=1)
在 MySQL 8.0.20 及更高版本中，为了使用散列连接，连接不再需要至少包含一个等连接条件。这意味着可以使用散列连接优化的查询类型包括下面列表中的查询（附示例）：
内部非相等连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Filter: (t1.c1 < t2.c1) (cost=4.70 rows=12) -> Inner hash join (no condition) (cost=4.70 rows=12) -> Table scan on t2 (cost=0.08 rows=6) -> Hash -> Table scan on t1 (cost=0.85 rows=6)
半连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 -> WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G *************************** 1. row *************************** EXPLAIN: -> Hash semijoin (t2.c2 = t1.c1) (cost=0.70 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Hash -> Table scan on t2 (cost=0.35 rows=1)
反连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 -> WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G *************************** 1. row *************************** EXPLAIN: -> Hash antijoin (t1.c1 = t2.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1) 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1276 Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1
左外连接
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Left hash join (t2.c1 = t1.c1) (cost=0.70 rows=1) -> Table scan on t1 (cost=0.35 rows=1) -> Hash -> Table scan on t2 (cost=0.35 rows=1)
右外连接（注意，MySQL 会将所有右外连接改写为左外连接）：
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Left hash join (t1.c1 = t2.c1) (cost=0.70 rows=1) -> Table scan on t2 (cost=0.35 rows=1) -> Hash -> Table scan on t1 (cost=0.35 rows=1)
默认情况下，MySQL 8.0.18 及更高版本会尽可能使用哈希连接。可以使用 BNL 和 NO_BNL 优化器提示之一来控制是否使用哈希连接。
(MySQL 8.0.18 支持 hash_join=on 或 hash_join=off，作为 optimizer_switch 服务器系统变量以及优化器提示 HASH_JOIN 或 NO_HASH_JOIN 设置的一部分。在 MySQL 8.0.19 及更高版本中，这些提示不再有任何作用）。
可以使用 join_buffer_size 系统变量来控制哈希连接的内存使用量；哈希连接使用的内存不能超过此值。当哈希连接所需的内存超过可用容量时，MySQL 会使用磁盘上的文件来处理。如果出现这种情况，你应该注意，如果哈希连接无法容纳内存，并且创建的文件超过 open_files_limit 的设置，那么连接可能不会成功。为避免此类问题，请进行以下更改：
- 增大 join_buffer_size，以免散列连接溢出到磁盘。
- 增加 open_files_limit.
从 MySQL 8.0.18 开始，哈希连接的连接缓冲区以增量方式分配；因此，您可以将 join_buffer_size 设置得更高，而不会让小查询分配大量 RAM，但外连接会分配整个缓冲区。在 MySQL 8.0.20 及更高版本中，哈希连接也用于外部连接（包括反连接和半连接），因此这不再是一个问题。
8.2.1.5 引擎条件下推优化
这一优化提高了非索引列与常数之间直接比较的效率。在这种情况下，条件会被 "下推 "到存储引擎进行评估。该优化仅适用于 NDB 存储引擎。
对于 NDB 群集，这种优化可以消除在群集的数据节点和发出查询的 MySQL 服务器之间通过网络发送非匹配行的需要，与可以但未使用条件下推的情况相比，使用这种优化的查询速度可提高 5 到 10 倍。
假设 NDB 群集表定义如下：
CREATE TABLE t1 ( a INT, b INT, KEY(a) ) ENGINE=NDB;
引擎条件下推可用于查询，如图所示，其中包括非索引列和常数之间的比较：
SELECT a, b FROM t1 WHERE b = 10;
从 EXPLAIN 的输出中可以看到引擎状态下推的使用：
mysql> EXPLAIN SELECT a, b FROM t1 WHERE b = 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using where with pushed condition
但是，引擎条件下推不能与以下查询一起使用：
SELECT a,b FROM t1 WHERE a = 10;
由于 a 列上存在一个索引，因此这里不适用引擎条件下推（索引访问方法效率更高，因此会优先选择条件下推）。
当使用 > 或 < 操作符将索引列与常量进行比较时，也可以使用引擎条件下推：
mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 type: range possible_keys: a key: a key_len: 5 ref: NULL rows: 2 Extra: Using where with pushed condition
其他支持的引擎状态下推比较包括以下内容：
- column [NOT] LIKE pattern
- column IS [NOT] NULL
- column IN (value_list)
- column BETWEEN constant1 AND constant2
在前面列出的所有情况中，都可以将条件转换为列和常量之间的一个或多个直接比较的形式。
引擎条件下推默认为启用。要在服务器启动时禁用，请将 optimizer_switch 系统变量的 engine_condition_pushdown 标志设置为关闭。例如，在 my.cnf 文件中使用以下几行：
[mysqld] optimizer_switch=engine_condition_pushdown=off
在运行时，像这样禁用条件下推：
SET optimizer_switch='engine_condition_pushdown=off';
仅 NDB 存储引擎支持引擎条件推送。
在 NDB 8.0.18 之前，列只能与常量或只评估常量值的表达式进行比较。在 NDB 8.0.18 及更高版本中，只要列的类型完全相同，包括相同的符号性、长度、字符集、精度和比例（如果适用），列之间就可以相互比较。
用于比较的列不能是任何 BLOB 或 TEXT 类型。这种排除也适用于 JSON、BIT 和 ENUM 列。
要与列进行比较的字符串值必须使用与列相同的校对方式。
不直接支持连接；涉及多个表的条件尽可能单独推送。请使用扩展的 EXPLAIN 输出来确定哪些条件被实际推送
以前，引擎条件下推仅限于引用条件被下推到的同一表中的列值。从 NDB 8.0.16 开始，查询计划中较早的表中的列值也可以从推送条件中引用。这就减少了 SQL 节点在连接处理过程中必须处理的行数。过滤也可以在 LDM 线程中并行执行，而不是在单个 mysqld 进程中执行。这有可能显著提高查询性能。
从 NDB 8.0.20 开始，如果在同一连接巢中使用的任何表上，或在其所依赖的连接巢上的任何表上没有不可推送的条件，那么使用扫描的外部连接就可以被推送。如果使用的优化策略是 firstMatch，那么半连接也是如此。
在以下两种情况下，不能将连接算法与引用以前表中的列结合起来：
当引用的任何先前表都在连接缓冲区中时。在这种情况下，从扫描过滤表中检索的每一行都要与缓冲区中的每一行进行匹配。这意味着在生成扫描筛选器时，无法从单一特定行中获取列值。
当列来自推入连接中的子操作时。这是因为在生成扫描筛选器时，尚未检索从连接中的祖先操作引用的行。
从 NDB 8.0.27 开始，只要符合前面列出的要求，就可以向下推送连接中来自祖表的列。下面是使用之前创建的表 t1 进行此类查询的示例：
mysql> EXPLAIN -> SELECT * FROM t1 AS x -> LEFT JOIN t1 AS y -> ON x.a=0 AND y.b>=3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: x partitions: p0,p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: y partitions: p0,p1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra: Using where; Using pushed condition (`test`.`y`.`b` >= 3); Using join buffer (hash join) 2 rows in set, 2 warnings (0.00 sec)
索引条件下推优化
索引条件下推（ICP）是针对 MySQL 使用索引从表中检索记录的情况进行的优化。在没有 ICP 的情况下，存储引擎会遍历索引来查找基本表中的行，然后将它们返回到 MySQL 服务器，由服务器评估这些行的 WHERE 条件。启用 ICP 后，如果只使用索引中的列就能评估 WHERE 条件的部分内容，MySQL 服务器会将这部分 WHERE 条件推送给存储引擎。然后，存储引擎会使用索引条目评估推送的索引条件，只有在满足条件时才会从表中读取记录。ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。
指数条件下推优化的适用性取决于这些条件：
- 当需要访问完整的表行时，ICP 可用于 range、ref、eq_ref 和 ref_or_null 访问方法。
- ICP 可用于 InnoDB 和 MyISAM 表，包括分区 InnoDB 和 MyISAM 表。
- 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取的次数，从而减少 I/O 操作。对于 InnoDB 集群索引，完整记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。
- 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持在虚拟生成列上创建二级索引。
- 引用子查询的条件不能向下推送。
- 不能下推引用存储函数的条件。存储引擎无法调用存储函数。
- 触发条件不能下推。
- (MySQL 8.0.30 及更高版本：）不能将条件下推到包含系统变量引用的派生表。
要了解这种优化的工作原理，首先要考虑在不使用 "索引条件下推 "的情况下，索引扫描是如何进行的：
- 首先读取索引元组，然后使用索引元组定位并读取完整的表行，从而获取下一行。
- 测试 WHERE 条件中适用于该表的部分。根据测试结果接受或拒绝该行。
使用 "索引条件下推"，扫描过程就会变成这样：
- 获取下一行的索引元组（但不是整个表行）。
- 测试 WHERE 条件中适用于该表且只需使用索引列即可检查的部分。如果条件不满足，则继续查找下一行的索引元组。
- 如果满足条件，则使用索引元组定位并读取完整的表行。
- 测试 WHERE 条件中适用于该表的剩余部分。根据测试结果接受或拒绝该行。
EXPLAIN 输出在使用 Index Condition Pushdown 时会在 Extra 列中显示 Using index condition（使用索引条件）。不显示使用索引是因为必须读取整个表的行时，使用索引并不适用。
假设表中包含有关人员及其地址的信息，表中的索引定义为 INDEX（zipcode, lastname, firstname）。如果我们知道一个人的邮编值，但不确定姓氏，我们可以这样搜索：
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
MySQL 可以使用索引扫描邮编='95054'的人员。第二部分（lastname LIKE '%etrunia%'）不能用来限制必须扫描的行数，因此如果没有索引条件下推，该查询必须检索所有邮编为'95054'的人的完整表行。
如果使用了 "Index Condition Pushdown"，MySQL 会在读取完整表行之前检查姓氏 LIKE '%etrunia%' 部分。这样可以避免读取与符合邮编条件但不符合姓氏条件的索引元组相对应的完整行。
索引条件下推默认为启用。可以通过设置 index_condition_pushdown 标志，用 optimizer_switch 系统变量来控制：
SET optimizer_switch = 'index_condition_pushdown=off'; SET optimizer_switch = 'index_condition_pushdown=on';
8.2.1.7 嵌套循环连接算法
MySQL 使用嵌套循环算法或其变体执行表之间的连接。
- 嵌套循环连接算法
- 块嵌套循环连接算法
嵌套循环连接算法
简单的嵌套循环连接（NLJ）算法每次从循环中的第一个表读取一条记录，然后将每条记录传递给处理连接中下一个表的嵌套循环。这个过程重复的次数与需要连接的表的数量相同。
假设要使用以下连接类型执行三个表 t1、t2 和 t3 之间的连接：
Table Join Type t1 range t2 ref t3 ALL
如果使用简单的 NLJ 算法，连接处理过程如下：
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 } } }
由于 NLJ 算法从外层循环到内层循环每次传递一条记录，因此通常会多次读取内层循环中处理过的表。
区块嵌套循环连接算法
块嵌套循环 (BNL) 连接算法利用对外循环中读取的行进行缓冲，以减少内循环中表的读取次数。例如，如果在缓冲区中读取了 10 条记录，并将缓冲区传递给下一个内循环，那么内循环中读取的每条记录都可以与缓冲区中的所有 10 条记录进行比较。这就将必须读取内部表的次数减少了一个数量级。
在 MySQL 8.0.18 之前，当无法使用索引时，这种算法适用于等连接；在 MySQL 8.0.18 及以后版本中，在这种情况下使用哈希连接优化。从 MySQL 8.0.20 开始，MySQL 不再使用块嵌套循环，而是在以前使用块嵌套循环的所有情况下使用哈希连接算法
MySQL 连接缓冲具有这些特点：
- 当连接类型为 ALL 或索引（换句话说，当无法使用可能的键，并且分别对数据行或索引行进行全扫描时）或范围时，可以使用连接缓冲。缓冲也适用于外部连接，即使是 ALL 或索引类型的表，也不会为第一个非恒定表分配连接缓冲区。
- 连接缓冲区中只存储与连接相关的列，而不是整条记录。
- join_buffer_size 系统变量决定用于处理查询的每个连接缓冲区的大小。
- 每个可以缓冲的连接都会分配一个缓冲区，因此处理一个查询可能会使用多个连接缓冲区。
- 连接缓冲区在执行连接之前分配，查询完成后释放。
对于前面描述的 NLJ 算法的连接示例（无缓冲），使用连接缓冲的连接过程如下：
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 join 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 } } }
如果 S 是连接缓冲区中每个存储的 t1、t2 组合的大小，C 是缓冲区中组合的个数，则扫描表 t3 的次数为：
(S * C)/join_buffer_size + 1
随着 join_buffer_size 值的增加，t3 扫描的次数也会减少，直到 join_buffer_size 大到足以容纳所有以前的记录组合。此时，增大 join_buffer_size 不会提高速度。
8.2.1.8 嵌套连接优化
表达连接的语法允许嵌套连接。下文将讨论第 13.2.13.2 节 "连接语句 "中描述的连接语法。
与 SQL 标准相比，table_factor 的语法有所扩展。后者只接受 table_reference，而不是一对括号内的列表。如果我们把 table_reference 项目列表中的每个逗号都视为等同于内部连接，那么这是一种保守的扩展。例如
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
等同于
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
在 MySQL 中，CROSS JOIN 在语法上等同于 INNER JOIN；它们可以相互替代。在标准 SQL 中，它们并不等同。INNER JOIN 与 ON 子句一起使用；否则使用 CROSS JOIN。
一般来说，在只包含内部连接操作的连接表达式中，可以忽略括号。请看下面这个连接表达式
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a
去掉括号并向左分组操作后，连接表达式就变成了这个表达式：
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL
然而，这两个表达式并不等同。为了说明这一点，假设表 t1、t2 和 t3 的状态如下：
- 表 t1 包含行 (1)、(2)
- 表 t2 包含行 (1,101)
- 表 t3 包含行 (101)
在这种情况下，第一个表达式返回的结果集包括行 (1,1,101,101)、(2,NULL,NULL,NULL)，而第二个表达式返回的结果集包括行 (1,1,101,101)、(2,NULL,NULL,101)：
mysql> SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql> SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
在下面的示例中，外连接操作与内连接操作一起使用：
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
该表达式无法转化为下面的表达式：
t1 LEFT JOIN t2 ON t1.a=t2.a, t3
对于给定的表格状态，两个表达式会返回不同的行集：
mysql> SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+
因此，如果我们在使用外连接操作符的连接表达式中省略括号，就可能会改变原始表达式的结果集。
更确切地说，我们不能忽略左外部连接操作的右操作数和右连接操作的左操作数中的小括号。换句话说，我们不能忽略外连接操作的内表表达式中的括号。另一个操作数（外表操作数）的括号可以忽略。
下面的表达式
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
对于任何表 t1、t2、t3 以及属性 t2.b 和 t3.b 上的任何条件 P，都等同于此表达式：
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
只要连接表达式（joined_table）中连接操作的执行顺序不是从左到右，我们就会讨论嵌套连接。请看下面的查询：
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a WHERE t1.a > 1 SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1
这些查询被认为包含嵌套连接：
t2 LEFT JOIN t3 ON t2.b=t3.b t2, t3
在第一个查询中，嵌套连接是通过左连接操作形成的。在第二个查询中，嵌套连接是通过内连接操作形成的。
在第一个查询中，括号可以省略：连接表达式的语法结构决定了连接操作的执行顺序相同。对于第二个查询，括号不能省略，尽管这里的连接表达式在没有括号的情况下也能得到明确的解释。在我们的扩展语法中，第二个查询 (t2, t3) 中的括号是必须的，尽管理论上查询可以不加括号：由于 LEFT JOIN 和 ON 在表达式（t2,t3）中起到了左右分隔符的作用，因此我们仍然可以为查询提供明确的语法结构。
前面的示例证明了这些观点：
对于只涉及内连接（而不是外连接）的连接表达式，可以去掉括号，并从左到右对连接进行求值。事实上，表格可以以任何顺序进行求值。
一般来说，外连接或外连接与内连接混合的情况并非如此。删除括号可能会改变结果。
使用嵌套外部连接的查询与使用内部连接的查询以相同的流水线方式执行。更确切地说，是利用嵌套循环连接算法的一种变体。回顾一下嵌套循环连接执行查询的算法（参见第 8.2.1.7 节 "嵌套循环连接算法"）。假设对 3 张表 T1、T2、T3 的连接查询的形式如下
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3)
这里，P1(T1,T2) 和 P2(T3,T3) 是一些连接条件（关于表达式），而 P(T1,T2,T3) 是关于表 T1、T2、T3 的列的条件。
嵌套循环连接算法将按以下方式执行此查询：
FOR each row t1 in T1 { FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
符号 t1|||t2||t3 表示通过连接行 t1、t2 和 t3 的列来构造的行。在下面的一些示例中，表名中出现 NULL 表示该表的每一列都使用了 NULL。例如，t1||t2||NULL 表示通过连接行 t1 和 t2 的列以及对 t3 的每一列使用 NULL 构建的行。这样的行称为 NULL 补充行。
现在考虑一个嵌套外部连接的查询：
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2) WHERE P(T1,T2,T3)
对于该查询，修改嵌套循环模式即可得到：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF P(t1,t2,NULL) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
一般来说，对于外连接操作中第一个内表的任何嵌套循环，都会引入一个标志，该标志在循环前关闭，并在循环后检查。当发现外层表中的当前行与代表内层操作数的表匹配时，该标志就会打开。如果在循环周期结束时，标志仍处于关闭状态，则表示外层表的当前行没有找到匹配项。在这种情况下，内层表的列将以 NULL 值对该行进行补充。结果行将传递给输出的最终检查或下一个嵌套循环，但前提是该行满足所有嵌入式外部连接的连接条件。
在示例中，嵌入了由以下表达式表示的外连接表：
(T2 LEFT JOIN T3 ON P2(T2,T3))
对于带内连接的查询，优化器可以选择不同的嵌套循环顺序，比如下面这种：
FOR each row t3 in T3 { FOR each row t2 in T2 such that P2(t2,t3) { FOR each row t1 in T1 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
对于带外连接的查询，优化器只能选择这样一种顺序，即外层表的循环先于内层表的循环。因此，对于我们的外连接查询，只有一种嵌套顺序是可能的。对于下面的查询，优化器会评估两种不同的嵌套顺序。在这两种嵌套中，T1 必须在外循环中处理，因为它被用于外连接。T2 和 T3 用于内连接，因此必须在内循环中处理该连接。但是，由于连接是内连接，T2 和 T3 可以按任意顺序处理。
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) WHERE P(T1,T2,T3)
一个嵌套先评估 T2，然后评估 T3：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t1,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
其他嵌套先评估 T3，然后评估 T2：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t3 in T3 such that P2(t1,t3) { FOR each row t2 in T2 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } }
在讨论内连接的嵌套循环算法时，我们忽略了一些细节，而这些细节对查询执行性能的影响可能是巨大的。我们没有提到所谓的 "下推 "条件。假设我们的 WHERE 条件 P(T1,T2,T3) 可以用一个连接式来表示：
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
在这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带内连接的查询：
FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } }
我们可以看到，C1(T1)、C2(T2)、C3(T3) 中的每一个连接词都被从最内层的循环退到了最外层的循环，并在那里进行评估。如果 C1(T1) 是一个限制性很强的条件，这种条件下推可能会大大减少从表 T1 传递到内循环的行数。因此，查询的执行时间可能会大大缩短。
对于有外部连接的查询，只有在发现外部表中的当前记录在内部表中有匹配记录后，才会检查 WHERE 条件。因此，将条件退出内部嵌套循环的优化方法不能直接用于外部连接查询。在这里，我们必须引入有条件的下推谓词，这些谓词受到标志的保护，当遇到匹配时，标志就会被打开。
请回想一下这个外部连接的示例：
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
在这个例子中，使用有保护的下推条件的嵌套循环算法如下所示：
FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; } }
一般来说，下推谓词可以从连接条件中提取，例如 P1(T1,T2) 和 P(T2,T3)。在这种情况下，下推谓词也会受到一个标志的保护，该标志会阻止对相应外部连接操作产生的 NULL 补充行进行谓词检查。
在同一个嵌套连接中，如果由 WHERE 条件中的谓词引起从一个内表到另一个内表的键访问，则禁止这种访问。
8.2.1.9 外连接优化
外连接包括 LEFT JOIN 和 RIGHT JOIN。
MySQL 实现 A LEFT JOIN B join_specification 的方法如下：
- 表 B 设置为依赖于表 A 和 A 依赖的所有表。
- 表 A 被设置为依赖于 LEFT JOIN 条件中使用的所有表（表 B 除外）。
- LEFT JOIN 条件用于决定如何从表 B 中检索记录（换句话说，不使用 WHERE 子句中的任何条件）。
- 除了表总是在其依赖的所有表之后读取外，所有标准的连接优化都会执行。如果存在循环依赖关系，就会发生错误。
- 执行所有标准的 WHERE 优化。
- 如果 A 表中有符合 WHERE 子句的记录，但 B 表中没有符合 ON 条件的记录，则会生成一条额外的 B 表记录，并将所有列设置为 NULL。
- 如果使用 LEFT JOIN 查找某些表中不存在的记录，并且在 WHERE 部分有以下测试：col_name IS NULL，其中 col_name 是声明为 NOT NULL 的列，那么在找到一条符合 LEFT JOIN 条件的记录后，MySQL 将停止搜索更多记录（针对特定键组合）。
右连接的实现类似于左连接，但表的角色相反。如第 8.2.1.10 节 "外连接简化 "所述，右连接会转换为等效的左连接。
对于 LEFT JOIN，如果对于生成的 NULL 行，WHERE 条件始终为假，则 LEFT JOIN 变为内连接。例如，在下面的查询中，如果 t2.column1 为空，WHERE 子句将为假：
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
因此，将查询转换为内部连接是安全的：
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
在 MySQL 8.0.14 及更高版本中，常量字面表达式产生的琐碎 WHERE 条件会在准备过程中移除，而不是在优化的后期阶段移除，因为此时连接已被简化。提前删除琐碎条件可让优化器将外部连接转换为内部连接；这可改进在 WHERE 子句中包含琐碎条件的外部连接查询计划，如本查询：
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1
现在，优化器在准备过程中发现 0 = 1 总是假的，这使得 OR 0 = 1 成为多余，因此将其删除，只留下这个：
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2
现在，优化器可以将查询改写为内部连接，就像这样：
SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2
现在，如果在表 t1 之前使用表 t2 可以获得更好的查询计划，优化器就可以这样做。要提供有关表连接顺序的提示，请使用优化器提示；参见第 8.9.3 节 "优化器提示"。或者使用 STRAIGHT_JOIN；请参阅第 13.2.13 节 "SELECT 语句"。不过，STRAIGHT_JOIN 可能会阻止索引的使用，因为它会禁用半连接转换；请参阅第 8.2.2.1 节 "使用半连接转换优化 IN 和 EXISTS 子查询谓词"。
8.2.1.10 外连接简化
在很多情况下，查询 FROM 子句中的表，表达式会被简化。
在解析器阶段，包含右外部连接操作的查询会被转换为只包含左连接操作的等价查询。在一般情况下，右连接的转换是这样进行的：
(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)
成为相当于左侧的连接：
(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)
所有形式为 T1 INNER JOIN T2 ON P(T1,T2) 的内部连接表达式都会被列表 T1,T2 所替换，P(T1,T2) 作为连接项加入 WHERE 条件（或嵌入连接的连接条件，如果有的话）。
当优化器评估外部连接操作的计划时，它只考虑这样的计划：对于每个此类操作，先访问外部表，再访问内部表。优化器的选择是有限的，因为只有这样的计划才能使用嵌套循环算法执行外部连接。
考虑这种形式的查询，其中 R(T2) 大大缩小了表 T2 中匹配行的数量：
SELECT * T1 FROM T1 LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2)
如果按照所写的方式执行查询，优化器别无选择，只能先访问限制较少的表 T1，然后再访问限制较多的表 T2，这样可能会产生一个非常低效的执行计划。
相反，如果 WHERE 条件为空拒绝，MySQL 会将查询转换为无外连接操作的查询。(如果外部连接操作产生的任何 NULL 补充记录的条件返回值为 FALSE 或 UNKNOWN，那么该条件就是无效条件。
因此，对于这个外部连接
T1 LEFT JOIN T2 ON T1.A=T2.A
类似这样的条件会被空拒绝，因为对于任何 NULL 补充行（T2 列设置为 NULL）来说，这些条件都不可能为真：
T2.B IS NOT NULL T2.B > 3 T2.C <= T1.C T2.B < 2 OR T2.C > 1
类似这样的条件不会被空拒绝，因为对于 NULL 补充行来说，这些条件可能为真：
T2.B IS NULL T1.B < 3 OR T2.B IS NOT NULL T1.B < 3 OR T2.B > 3
检查外部连接操作的条件是否为空的一般规则很简单：
它的形式是 A IS NOT NULL，其中 A 是任意内层表的属性
它是一个谓词，包含对内表的引用，当其参数之一为 NULL 时，其值为 UNKNOWN
是一个连接词，包含一个拒绝为空的条件作为连接词
空拒绝条件的析取
一个条件可以在查询中的一个外部连接操作中空拒绝，而在另一个外部连接操作中不会空拒绝。在本查询中，WHERE 条件在第二个外部连接操作中被空拒绝，但在第一个外部连接操作中没有被空拒绝：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T1.B WHERE T3.C > 0
如果查询中的外连接操作的 WHERE 条件为空拒绝，则外连接操作将被内连接操作取代。
例如，在前面的查询中，第二个外连接操作被空拒绝，可以用内连接操作代替：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T1.B WHERE T3.C > 0
对于原始查询，优化器只评估与单一表访问顺序 T1,T2,T3 兼容的计划。对于改写后的查询，它会额外考虑 T3,T1,T2 的访问顺序。
一个外部连接操作的转换可能会触发另一个外部连接操作的转换。因此，查询
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0
首先转换为查询：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T2.B WHERE T3.C > 0
这相当于查询：
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B
由于 T3.B=T2.B 条件为空拒绝，因此剩余的外连接操作也可以用内连接操作代替。这样，查询就完全没有外连接了：
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B
有时，优化器能成功替换嵌入式外部连接操作，但无法转换嵌入式外部连接。下面的查询
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0
转换为
SELECT * FROM T1 LEFT JOIN (T2 INNER JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0
这只能改写成仍然包含嵌入式外部连接操作的形式：
SELECT * FROM T1 LEFT JOIN (T2,T3) ON (T2.A=T1.A AND T3.B=T2.B) WHERE T3.C > 0
在查询中转换嵌入式外部连接操作时，必须同时考虑嵌入式外部连接的连接条件和 WHERE 条件。在本查询中，对于嵌入式外部连接，WHERE 条件没有被拒绝为空，但嵌入式外部连接 T2.A=T1.A AND T3.C=T1.C 的连接条件被拒绝为空：
SELECT * FROM T1 LEFT JOIN (T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A AND T3.C=T1.C WHERE T3.D > 0 OR T1.D > 0
因此，该查询可转换为：
SELECT * FROM T1 LEFT JOIN (T2, T3) ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B WHERE T3.D > 0 OR T1.D > 0
8.2.1.11 多范围读取优化
在二级索引上使用范围扫描读取行，当表较大且未存储在存储引擎缓存中时，会导致对基表的多次随机磁盘访问。通过磁盘扫描多范围读取（MRR）优化，MySQL 会首先只扫描索引，然后收集相关行的键，从而减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后使用主键的顺序从基础表中检索记录。磁盘扫描 MRR 的动机是减少随机磁盘访问的次数，而不是对基表数据进行更有序的扫描。
多范围读取优化提供了这些优势：
- MRR 可根据索引元组按顺序而不是随机顺序访问数据行。服务器会获取一组满足查询条件的索引元组，根据数据行 ID 顺序对其进行排序，并使用排序后的元组按顺序检索数据行。这样可以提高数据访问效率，降低成本。
- 对于需要通过索引元组访问数据行的操作，如范围索引扫描和使用索引作为连接属性的等连接，MRR 可以批量处理键访问请求。MRR 对一系列索引范围进行迭代，以获得合格的索引元组。随着这些结果的累积，它们将被用于访问相应的数据行。在开始读取数据行之前，无需获取所有索引元组。
在虚拟生成列上创建的二级索引不支持 MRR 优化。InnoDB 支持在虚拟生成列上创建二级索引。
以下场景说明了什么情况下优化 MRR 更有利：
方案 A：MRR 可用于 InnoDB 和 MyISAM 表的索引范围扫描和等连接操作。
- 索引图元的一部分累积在缓冲区中。
- 缓冲区中的元组按数据行 ID 排序。
- 数据行根据排序后的索引元组序列进行访问。
方案 B：MRR 可用于 NDB 表的多范围索引扫描或按属性执行等连接时。
- 部分范围（可能是单键范围）会累积到提交查询的中央节点的缓冲区中。
- 这些范围会被发送到访问数据行的执行节点。
- 访问的数据行被打包成数据包，然后发送回中央节点。
- 接收到的包含数据行的数据包被放入缓冲区。
- 从缓冲区读取数据行。
使用 MRR 时，EXPLAIN 输出中的 Extra 栏会显示 Using MRR。
如果不需要访问完整的表行来生成查询结果，InnoDB 和 MyISAM 不会使用 MRR。如果可以完全根据索引元组中的信息（通过覆盖索引）生成结果，则属于这种情况；MRR 不会带来任何好处。
两个 optimizer_switch 系统变量标志提供了使用 MRR 优化的接口。mrr 标志控制是否启用 MRR。如果 mrr 启用（开启），则 mrr_cost_based 标志控制优化器是尝试在使用和不使用 MRR 之间做出基于成本的选择（开启），还是尽可能使用 MRR（关闭）。默认情况下，mrr 开启，mrr_cost_based 开启。请参见第 8.9.2 节 "可切换优化"。
对于 MRR，存储引擎使用 read_rnd_buffer_size 系统变量的值作为其缓冲区内存分配的指导。引擎最多使用 read_rnd_buffer_size 字节，并决定一次处理的范围数量。
8.2.1.12 块嵌套循环和批量key访问连接
在 MySQL 中，有一种批量键访问（BKA）连接算法，它同时使用对连接表的索引访问和连接缓冲区。BKA 算法支持内连接、外连接和半连接操作，包括嵌套外连接。BKA 算法的优点包括：由于表扫描效率更高，因此连接性能得到改善。此外，以前只用于内部连接的块嵌套循环（BNL）连接算法也得到了扩展，可用于外部连接和半连接操作，包括嵌套外部连接。
下文将讨论作为原始 BNL 算法、扩展 BNL 算法和 BKA 算法扩展基础的连接缓冲区管理。有关半连接策略的信息，请参见第 8.2.2.1 节 "使用半连接变换优化 IN 和 EXISTS 子查询谓词"。
- 区块嵌套循环和批量key访问算法的连接缓冲区管理
- 用于外连接和半连接的块嵌套循环算法
- 批量key访问连接
- 块嵌套循环算法和批量key访问算法的优化提示
区块嵌套循环和批量key访问算法的连接缓冲区管理
MySQL 不仅可以使用连接缓冲区来执行内表无索引访问的内连接，还可以执行子查询扁平化后出现的外连接和半连接。此外，当内表有索引访问时，也可以有效使用连接缓冲区。
在存储相关行列的值时，连接缓冲区管理代码能更有效地利用连接缓冲区空间：如果行列的值为 NULL，则不会在缓冲区中分配额外的字节，而对于任何 VARCHAR 类型的值，都会分配最少的字节数。
代码支持两种类型的缓冲区：常规缓冲区和增量缓冲区。假设使用连接缓冲区 B1 连接表 t1 和表 t2，并使用连接缓冲区 B2 将此操作的结果与表 t3 连接：
- 正则表达式连接缓冲区包含来自每个连接操作数的列。如果 B2 是正则表达式连接缓冲区，则放入 B2 的每一行 r 都由来自 B1 的行 r1 的列和来自表 t3 的匹配行 r2 的有趣列组成。
- 增量连接缓冲区只包含第二个连接操作产生的表中行的列。也就是说，它是来自第一个操作缓冲区的行的增量。如果 B2 是增量连接缓冲区，则它包含行 r2 的相关列以及指向来自 B1 的行 r1 的链接。
递增连接缓冲区总是相对于先前连接操作的连接缓冲区递增的，因此第一个连接操作的缓冲区总是常规缓冲区。在刚才的示例中，用于连接表 t1 和表 t2 的缓冲区 B1 必须是常规缓冲区。
用于连接操作的增量缓冲区的每一行只包含要连接的表中某一行的相关列。在这些列中增加了对第一个连接操作数生成的表中匹配行的相关列的引用。增量缓冲区中的多条记录可以引用存储在前面连接缓冲区中的同一行 r，只要所有这些记录都与 r 行匹配。
增量缓冲区可以减少从用于先前连接操作的缓冲区中复制列的频率。这可以节省缓冲区空间，因为在一般情况下，第一个连接操作产生的一条记录可以与第二个连接操作产生的多条记录相匹配。因此，没有必要将第一个操作数产生的一条记录复制数份。由于减少了复制时间，递增缓冲区还节省了处理时间。
在 MySQL 8.0 中，optimizer_switch 系统变量的 block_nested_loop 标志作用如下：
- 在 MySQL 8.0.20 之前，它控制优化程序如何使用块嵌套循环连接算法。
- 在 MySQL 8.0.18 及更高版本中，它还控制哈希连接的使用（参见第 8.2.1.4 节 "哈希连接优化"）。
- 从 MySQL 8.0.20 开始，该标志只控制哈希连接，不再支持块嵌套循环算法。
batched_key_access 标志控制优化器如何使用批量键访问连接（BKA join）算法。
默认情况下，block_nested_loop 开启，batched_key_access 关闭。请参见第 8.9.2 节 "可切换的优化"。也可以应用优化提示；请参阅块嵌套循环和批量密钥访问算法的优化提示。
有关半连接策略的信息，请参阅第 8.2.2.1 节 "使用半连接变换优化 IN 和 EXISTS 子查询条件"。
外连接和半连接的块嵌套循环算法
MySQL BNL 算法的最初实现已扩展到支持外连接和半连接操作（后来被哈希连接算法取代；参见第 8.2.1.4 节 "哈希连接优化"）。
在使用连接缓冲区执行这些操作时，放入缓冲区的每一行都有一个匹配标记。
如果使用连接缓冲区执行外连接操作，则会检查第二个操作数生成的表中的每一行与连接缓冲区中的每一行是否匹配。如果发现匹配，就会形成新的扩展行（原始行加上第二个操作数的列），并发送给其他连接操作以进一步扩展。此外，缓冲区中匹配行的匹配标志也会启用。检查完要连接的表中的所有行后，将扫描连接缓冲区。缓冲区中未启用匹配标志的每条记录都会通过 NULL 补充（第二个操作数中每列的 NULL 值）进行扩展，并发送给其他连接操作以进一步扩展。
在 EXPLAIN 输出中，如果 Extra 值包含 Using join buffer（块嵌套循环），且类型值为 ALL、索引或范围，则表示表使用了 BNL。
批量Key访问连接
MySQL 实现了一种连接表的方法，称为分批键访问（BKA）连接算法。当第二个连接操作数产生的表有索引访问时，就可以使用 BKA。与 BNL 连接算法一样，BKA 连接算法也使用连接缓冲区来累积连接操作的第一个操作数所产生的行中感兴趣的列。然后，BKA 算法为缓冲区中的所有行建立访问要连接的表的键，并将这些键批量提交给数据库引擎进行索引查找。这些键是通过多范围读取 (MRR) 接口提交给引擎的（参见第 8.2.1.11 节 "多范围读取优化"）。提交键值后，MRR 引擎功能会以最佳方式在索引中执行查找，获取由这些键值找到的连接表行，并开始向 BKA 连接算法提供匹配行。每条匹配行都与连接缓冲区中一条记录的引用相关联。
使用 BKA 时，join_buffer_size 的值定义了每次向存储引擎请求时键的批量大小。缓冲区越大，对连接操作右侧表的顺序访问就越多，这可以显著提高性能。
要使用 BKA，必须将 optimizer_switch 系统变量的 batched_key_access 标志设置为开启。BKA 使用 MRR，因此 mrr 标志也必须开启。目前，MRR 的成本估算过于悲观。因此，要使用 BKA，还必须关闭 mrr_cost_based。以下设置可启用 BKA：
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
执行 MRR 功能有两种情况：
- 第一种情况用于传统的基于磁盘的存储引擎，如 InnoDB 和 MyISAM。对于这些引擎，通常会将连接缓冲区中所有行的键一次性提交给 MRR 接口。特定引擎的 MRR 函数会对提交的键执行索引查找，从中获取行 ID（或主键），然后根据 BKA 算法的请求，逐一获取所有这些选定行 ID 的记录。每条记录都会返回一个关联引用，以便访问连接缓冲区中的匹配记录。MRR 函数以最佳方式获取行：它们按照行 ID（主键）顺序获取。这可以提高性能，因为读取是按照磁盘顺序而不是随机顺序进行的。
- 第二种情况用于远程存储引擎，如 NDB。MySQL 服务器（SQL 节点）将连接缓冲区中部分记录的一系列Key及其关联发送到 MySQL 群集数据节点。作为回报，SQL 节点会收到一个（或多个）相匹配的记录行。BKA 连接算法接收这些行，并建立新的连接行。然后，一组新的键被发送到数据节点，返回的数据行中的记录被用于建立新的连接行。这个过程一直持续到连接缓冲区中的最后一个键被发送到数据节点，并且 SQL 节点接收并连接了与这些键匹配的所有行。这可以提高性能，因为 SQL 节点发送到数据节点的含键数据包越少，就意味着它与数据节点之间执行连接操作的往返次数越少。
在第一种情况下，连接缓冲区的一部分被保留用于存储通过索引查找选择的行 ID（主键），并作为参数传递给 MRR 函数。
没有专门的缓冲区来存储为来自连接缓冲区的记录建立的键。相反，为缓冲区中下一条记录建立键的函数会作为参数传递给 MRR 函数。
在 EXPLAIN 输出中，如果 Extra 值包含使用连接缓冲区（分批键访问），且类型值为 ref 或 eq_ref，则表明表使用了 BKA。
