8.2 mysql 优化1

2020-05-13 10:07:37

8. IS NULL 优化

MySQL可以对col_name = constant_value使用col_name IS NULL来执行相同的优化。例如，MySQL可以使用索引和范围来搜索带有IS NULL的NULL。

例子：

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name

WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果WHERE子句包含声明为NOT NULL的列的col_name IS NULL条件，则会优化该表达式。这种优化不会发生在列可能产生NULL的情况下; 例如，如果它来自LEFT JOIN右侧的表

MySQL还可以优化组合col_name = expr或col_name IS NULL，这是解析子查询中常见的一种形式。当使用这个优化时，EXPLAIN显示ref_or_null。

--如果左侧列明确是 is not null 就不会进行优化

这个优化可以处理任何key part的一个 IS NULL。

假设在表t2的列a和b上有一个索引，优化查询的一些示例：

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2

WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2

WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2

WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)

OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null首先对引用键进行读取，然后单独搜索具有NULL键值的行。

优化只能处理一个IS NULL级别。在下面的查询中，MySQL仅在表达式（t1.a = t2.a AND t2.a IS NULL）上使用key 查找，并且没办法在b上使用key 部分：

SELECT * FROM t1, t2

WHERE (t1.a=t2.a AND t2.a IS NULL)

OR (t1.b=t2.b AND t2.b IS NULL);

9.LEFT JION 和 RIGHT JOIN 优化

MySQL按如下方式实现 A LEFT JOIN B join_condition：

表B被设置为依赖于表A和A所依赖的所有表。
表A被设置为依赖于在LEFT JOIN条件中使用的所有表（除了B）。
LEFT JOIN条件用于决定如何从表B中检索行（换句话说，不使用WHERE子句中的任何条件）。
所有标准连接优化都执行，不同之处在于一个表总是在它所依赖的所有表之后被读取。如果有循环依赖，MySQL会发出错误。
所有标准的 WHERE 优化都被执行
如果A中存在与WHERE子句匹配的行，但B中没有与ON条件相匹配的行，则会生成一个额外的B行，并将所有列设置为NULL。
如果使用LEFT JOIN查找某些表中不存在的行，并且您有以下测试：在WHERE部分中col_name是NULL，其中col_name是一个声明为NOT NULL的列，MySQL将停止搜索更多行（for 找到一行符合LEFT JOIN条件的特定组合键）。

RIGHT JOIN的实现与LEFT JOIN的实现类似，反转了表中的角色。

连接优化器计算应该连接表的顺序。由LEFT JOIN或STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地完成工作，因为检查的表排列更少。请注意，这意味着如果您执行以下类型的查询，MySQL将对b执行全面扫描，因为LEFT JOIN强制在d之前读取它：

SELECT *

FROM a JOIN b LEFT JOIN c ON (c.key=a.key)

LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

在这种情况下，修复的方式是与在FROM子句中列出a和b的顺序相反：

SELECT *

FROM b JOIN a LEFT JOIN c ON (c.key=a.key)

LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

对于LEFT JOIN，如果WHERE条件对于生成的NULL行始终为false，则将LEFT JOIN更改为正常连接。例如，如果t2.column1为NULL，则WHERE子句在以下查询中将为false：

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可以在表t1之前使用表t2，如果这样做会导致更好的查询计划。要提供有关表连接顺序的提示，请使用STRAIGHT_JOIN。

10 嵌套循环连接算法

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算法从外循环向内循环一次传递一行，因此它通常会多次读取在内循环中处理的表。

Block Nested-Loop Join 算法

块嵌套循环（BNL）连接算法使用在外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。例如，如果将10行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。内表必须被读取的次数减少一个数量级。

MySQL在这些条件下使用连接缓冲：

join_buffer_size系统变量确定每个连接缓冲区的大小。
当连接的类型为ALL或者index（换句话说，当不可能使用任何key，并且完成对数据或索引行的完全扫描）或range时，可以使用连接缓冲。
为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定的查询。
连接缓冲区永远不会分配给个非常量表，即使它是ALL或index类型。

连接缓冲区在执行连接之前被分配，并在查询完成后被释放。

只有连接感兴趣的列存储在连接缓冲区中，而不是整行。

对于前面介绍的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 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足够大以容纳所有先前的行组合。到这时，增大已经没有速度提升。

11.嵌套连接优化

表达连接的语法允许嵌套连接。

与SQL标准相比，table_factor的语法得到了扩展。后者只接受table_reference，而不是在一对圆括号内的列表。如果我们将table_reference项目列表中的每个逗号视为等同于inner join，这是一个保守的扩展。例如：

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）：

--表达式看起来逻辑相同，但是在连接的过程中，结果可能不同

在以下示例中，外连接操作与内连接操作一起使用：

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 |

+------+------+------+------+

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 LEFT JOIN t3 ON P(t2.b,t3.b)

对于任何表t1，t2，t3以及任何属性t2.b和t3.b的条件P.

每当连接表达式（join_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）的左右分隔符。）

前面的例子证明了这些观点：

对于仅涉及内部联接（而不是外部联接）的联接表达式，可以删除括号。您可以删除括号并从左到右评估（或者，实际上，您可以按任何顺序评估表）。
一般来说，对于外部连接或外部连接与内部连接混合，情况也是如此。删除括号可能会改变结果。

具有嵌套外连接的查询,以与具有内连接的查询相同的管道方式执行。更确切地说，嵌套循环连接算法的一个变体被利用。回想一下嵌套循环连接执行查询的算法模式。假设我们有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”。

现在让我们考虑一个嵌套的外连接的查询：

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值的情况。

通常，对于外部联接操作中的个内部表的任何嵌套循环，都会引入一个在循环之前关闭并在循环之后进行检查的标志。当外部表中的当前行与表示内部操作数的表相匹配时，该标志被打开。如果在循环结束时标志仍然关闭，那么外表的当前行没有找到匹配。在这种情况下，该行由内部表的列的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;

}

对于outer join，优化器只能选择外部表循环在内部循环之前的一个顺序。因此，对于使用外连接的查询，只有一个嵌套顺序是可能的。对于以下查询，优化器将评估两个不同的嵌套：

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)

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) {

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;

}

并且：

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;

}

在两个嵌套中，T1都必须在外部循环中处理，因为它在外部连接中使用。 T2和T3在内部连接中使用，所以必须在内部循环中处理连接。但是，因为联接是内部联接，所以T2和T3可以按任意顺序处理。

--这就是所谓的outer table 驱动inner table。

在讨论内连接的嵌套循环算法时，我们省略了一些对查询执行性能影响巨大的细节。我们没有提到所谓的“push-down”的条件。假设我们的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)

使用guarded 下推条件的嵌套循环模式如下所示：

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））中提取下推谓词。在这种情况下，push-down谓词也被一个标志保护，该标志防止检查由相应的外连接操作生成的NULL补充行的谓词

如果由WHERE条件中的谓词引发，则禁止在同一个嵌套连接中通过键从一个内部表访问另一个内部表。（在这种情况下，我们可以使用条件键访问，但是这种技术在MySQL 5.7中还没有被使用。）

12 外联接简化

在很多情况下，查询的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条件（或者联结条件嵌入连接，如果有的话）。

当优化器使用外部连接操作评估连接查询的计划时，它只考虑这样的计划，对于每个这样的操作，外部表在内部表之前被访问。优化程序选项是有限的，因为只有这样的计划才能使我们能够通过嵌套循环模式执行具有外部连接操作的查询。

假设我们有一个查询的形式：

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)

WHERE P(T1,T2) AND R(T2)

其中R（T2）大大地缩小了来自表T2的匹配行的数量。如果我们按照原样执行查询，那么除了在表T2之前访问表T1之外，优化器将没有别的选择，这可能导致非常低效的执行计划。

幸运的是，如果WHERE条件为空，则MySQL将这样的查询转换为无需外连接操作的查询。如果外部连接操作的计算结果为FALSE，或者对于为操作构建的任何NULL补充行，则该条件被称为null。

因此，对于外连接：

T1 LEFT JOIN T2 ON T1.A=T2.A

这些条件是null-rejected：

T2.B IS NOT NULL,

T2.B > 3,

T2.C <= T1.C,

T2.B < 2 OR T2.C > 1

这些条件不是null-rejected的：

T2.B IS NULL,

T1.B < 3 OR T2.B IS NOT NULL,

T1.B < 3 OR T2.B > 3

检查外部联接操作的条件是否为空的一般规则很简单。在下列情况下，条件为null-rejected ：

如果它的形式是A IS NOT NULL，其中A是任何内部表的属性
如果它是一个包含对内部表的引用的谓词，当其中一个参数为NULL时，该内部表的计算结果为UNKNOWN
如果它是包含作为合并的null-rejected条件的联合
如果它是一个分离的null-rejected条件

对于查询中的一个外连接操作，条件可以为null，否则为空。在查询中：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

LEFT JOIN T3 ON T3.B=T1.B

WHERE T3.C > 0

对于第二个外部联接操作，WHERE条件为null-rejected，但对于个外部联接操作，它不是null-rejected。

如果查询中的外部联接操作的WHERE条件为null-rejected，则外部联接操作将由内部联接操作替代。

例如，前面的查询被替换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

INNER JOIN T3 ON T3.B=T1.B

WHERE T3.C > 0

--此处 left join就没有效果，会自动转换成inner join

对于原始查询，优化器将评估仅与一个访问顺序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是null-rejected

，我们得到一个没有外部联接的查询：

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条件。在查询中：

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

对于嵌入式外部联接，WHERE条件不为null-rejected，但嵌入外部联接T2.A = T1.A AND T3.C = T1.C的联接条件为null - rejected。所以查询可以转换为：

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

----这里关键是对于外连接是否是null 的一个判断，会影响查询连接的转换。

13.多范围读优化

使用范围扫描在辅助索引中读取行可能会导致许多随机磁盘访问基表，当表很大并且不存储在存储引擎的缓存中时。通过磁盘扫描多范围读取Multi-Range Read（MRR）优化，MySQL会尝试减少范围扫描的随机磁盘访问次数，方法是先扫描索引并收集相关行的键。然后对键进行排序，后使用主键的顺序从基表中检索行。磁盘扫描MRR的动机是减少随机磁盘访问的次数，替代实现基本表数据的更多顺序扫描。

Multi-Range Read 优化提供下列优势：

MRR使得数据行能够按照索引元组的顺序而不是按照随机顺序被访问。服务器获得一组符合查询条件的索引元组，并根据数据行ID顺序进行排序，并使用排序后的元组来检索数据行按顺序排列。这使得数据访问更高效，更便宜。
MRR允许批量处理需要通过索引元组访问数据行的操作的key访问请求，例如使用连接属性的范围索引扫描和等值连接。 MRR迭代一系列索引范围以获得合格的索引元组。随着这些结果的积累，它们被用来访问相应的数据行。在开始读取数据行之前，不需要获取所有索引元组。

在虚拟生成的列上创建的二级索引不支持MRR优化。从MySQL 5.7.8开始，InDB支持虚拟生成的列上的二级索引。

以下情况说明MRR优化何时可能是有利的：

场景A：MRR可用于InnoDB和MyISAM表的索引范围扫描和等值连接。

一部分索引元组被累积在一个缓冲区中。
缓冲区中的元组按其数据行ID排序。
数据行根据排序的索引元组序列被访问

场景B：MRR可以用于多范围索引扫描的NDB表，或者通过属性执行等值连接。

部分范围（可能是单键范围）在提交查询的中央节点的缓冲区中累积。
范围被发送到访问数据行的执行节点。
被访问的行被打包并被发送回中央节点。
收到的含数据行的包放在缓冲区中。
从buffer中读取数据行

使用MRR时，EXPLAIN输出中的Extra列显示Using MRR。

如果不需要访问全表行来生成查询结果，则InnoDB和MyISAM不使用MRR。如果结果可以完全基于索引元组中的信息（通过覆盖索引）来产生，则是这种情况; MRR没有任何好处。

假设在（key_part1，key_part2）上有索引，可以使用MRR的示例查询：

SELECT * FROM t

WHERE key_part1 >= 1000 AND key_part1 < 2000

AND key_part2 = 10000;

索引由（key_part1，key_part2）值组成，首先由key_part1排序，然后由key_part2排序。

如果没有MRR，索引扫描将覆盖key_part1范围从1000到2000的所有索引元组，而不管这些元组中的key_part2值如何。扫描做了额外的工作，范围内的元组包含10000以外的key_part2值。

使用MRR，扫描被分成多个范围，每个范围为key_part1（1000,1001，...，1999）的单个值。每个扫描只需要查看key_part2 = 10000的元组。如果索引包含许多key_part2不是10000的元组，则MRR会导致读取的索引元组更少。

--ORACLE 优化器也类似，否则组合索引如何体现出优势呢？

为了使用区间表示法来表示，非MRR扫描必须检查索引范围[{1000,10000}，{2000,MIN_INT}），其中可能包含除key_part2 = 10000之外的许多元组.MRR扫描检查多个单点间隔[{1000，10000}]，...，[{1999,10000}]，其中只包含key_part2 = 10000的元组。

两个optimizer_switch系统变量标志为使用MRR优化提供了一个接口。mrr标志控制MRR是否启用。如果启用了mrr（on），则mrr_cost_based标志控制优化器是否尝试在使用和不使用MRR（on）之间进行基于开销的选择，或者尽可能（off）使用MRR。默认情况下，mrr处于on，mrr_cost_based处于on。

对于MRR，存储引擎使用read_rnd_buffer_size系统变量的值作为它可以为其缓冲区分配多少内存的指导。引擎多使用read_rnd_buffer_size个字节，并确定一次处理的范围数。

14.Block Nested-Loop 和 批量键访问联接

在MySQL 5.7中，Batched Key Access连接算法可以用于索引访问连接表和join 缓冲区。 BKA算法支持内连接，外连接和半连接操作，包括嵌套的外连接。 BKA的优点包括由于更高效的表扫描而提高了连接性能。此外，以前仅用于内连接的块嵌套循环（BNL）连接算法已扩展，可用于外连接和半连接操作（包括嵌套外连接）。

以下部分讨论了原始BNL算法，扩展BNL算法和BKA算法的扩展基础之上的join 缓冲区管理。

join buffer 管理 Block Nested-Loop 和批处理key访问算法

在MySQL 5.7中，MySQL服务器可以使用连接缓冲区来执行inner join，而不是对inner

表进行索引访问，也可以执行子查询展平后出现的外部连接和半连接。而且，当有inner表的索引访问时，可以有效地使用连接缓冲区。

连接缓冲区管理代码在存储感兴趣的行列的值时稍微更高效地利用连接缓冲区空间：如果行列的缓冲区中未分配额外的字节（如果它的值为NULL），并且将小字节数分配给任何值的VARCHAR类型。

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

常规联接缓冲区包含每个联接操作数的列。如果B2是常规连接缓冲区，则每个放入B2的行r由来自B1的行r1的列和来自表t2的匹配行r2的相关列组成。
增量连接缓冲区仅包含第二个连接操作生成的表的行中的列。也就是说，它是从个操作数缓冲区增加一行。如果B2是一个增量连接缓冲区，它包含了行r2中感兴趣的列以及从B1到行r1的链接。

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

用于连接操作的增量缓冲区的每一行仅包含要连接的表中某行的有趣列。这些列是通过引用个连接操作数生成的表中匹配行的有趣列来扩充的。增量缓冲区中的多行可以引用同一行r，其列存储在前面的连接缓冲区中，只要所有这些行与行r匹配。

增量缓冲区使从以前的连接操作所使用的缓冲区中列的复制更少。这节省了缓冲区空间，因为在一般情况下，由个连接操作数生成的行可以由第二个连接操作数生成的若干行匹配。从个操作数开始不需要对一行进行多个复制。由于减少了复制时间，增量缓冲区还节省了处理时间。

optimizer_switch系统变量的block_nested_loop和batched_key_access标志控制优化器如何使用块嵌套循环和批处理键访问连接算法。默认情况下，block_nested_loop是 on，batched_key_access是off

外连接和半连接的 BNL 算法

在MySQL 5.7中，BNL算法的原始实现被扩展为支持外连接和半连接操作。

当使用连接缓冲区执行这些操作时，放入缓冲区的每一行都会提供一个匹配标志。

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

optimizer_switch系统变量的block_nested_loop标志控制优化器如何使用Block嵌套循环算法。默认情况下，block_nested_loop为on。

在EXPLAIN输出中，当Extra值包含Using join buffer (Block Nested Loop)并且type 是ALL，index或range时，对表使用BNL。

Batched Key Access Joins

MySQL服务器实现了一种称为批处理密钥访问（BKA）连接算法的表连接方法。当对第二个连接操作数产生的表存在索引访问时，可以应用BKA。与BNL连接算法一样，BKA连接算法使用一个连接缓冲区，用于累加由连接操作的个操作数产生的行的有趣列。然后，BKA算法在buffer中构建被join表的所有行的访问key，并将这些key批量提交给数据库引擎进行索引查找。key通过多范围读取（MRR）接口提交给引擎。在提交密钥之后，MRR引擎功能以佳的方式在索引中执行查找，获取由这些密钥找到的连接表的行，并开始馈送具有匹配行的BKA连接算法。每个匹配的行都与对加入缓冲区中的行的引用相关联。

当使用BKA时，join_buffer_size的值定义了对存储引擎的每个请求中key batch的大小。缓冲区越大，连接操作的右边的顺序访问越多，这可以显著提高性能。

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

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

MRR功能有两种执行方式：

种情况是用于传统的基于磁盘的存储引擎，例如InnoDB和MyISAM。对于这些引擎，通常将来自连接缓冲区的所有行的key一次提交给MRR接口。特定于引擎的MRR函数对已提交的键执行索引查找，从中获取行ID（或主键），然后根据BKA算法的请求逐一提取所有这些所选行ID的行。每行都返回一个关联引用，该引用允许访问连接缓冲区中的匹配行。这些行由MRR函数以佳方式获取：它们是以行ID（主键）顺序获取的。这可以提高性能，因为读取顺序是磁盘顺序而不是随机顺序。
第二个场景用于远程存储引擎，如NDB。连接缓冲区中一部分行的键的包和其关联由MySQL服务器（SQL节点）发送到MySQL簇数据节点。作为回报，SQL节点接收匹配行的包（或多个包）以及相应的关联。 BKA连接算法采用这些行并构建新的连接行。然后将一组新的键发送到数据节点，并使用返回的包中的行构建新的连接行。该过程继续进行，直到来自连接缓冲区的后一个键被发送到数据节点，并且SQL节点已经接收并且加入了与这些键匹配的所有行。这样可以提高性能，因为SQL节点向数据节点发送的包含更少的key包意味着与数据节点之间执行连接操作的往返次数减少。

在种情况下，连接缓冲区的一部分被保留以存储通过索引查找选择的行ID（主键），并作为参数传递给MRR功能。

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

--只传递函数

在EXPLAIN输出中，当Extra值包含Using join buffer (Batched Key Access并且类型值为ref或eq_ref时，对表使用BKA。