第8章优化

本节讨论可以为处理 WHERE 子句 进行的优化 。 这些示例使用 SELECT 语句，但相同的优化适用 WHERE 于 DELETE 和 UPDATE 语句中的 子句 。

您可能想要重写查询以更快地进行算术运算，同时牺牲可读性。 因为MySQL会自动执行类似的优化，所以通常可以避免这种工作，并使查询保持更易理解和可维护的形式。 MySQL执行的一些优化如下：

一些非常快的查询示例：

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_part1DESC，key_part2DESC，... LIMIT 10;

MySQL仅使用索引树解析以下查询，假设索引列是数字：

SELECT key_part1，key_part2FROM tbl_nameWHERE key_part1= val;

SELECT COUNT（*）FROM tbl_name
  WHERE key_part1= val1AND key_part2= val2;

SELECT key_part2FROM tbl_nameGROUP BY key_part1;

以下查询使用索引来按排序顺序检索行，而不使用单独的排序传递：

SELECT ... FROM tbl_name
  ORDER BY key_part1，key_part2...;

SELECT ... FROM tbl_name
  ORDER BY key_part1DESC，key_part2DESC，...;

的 range 访问方法使用单个索引来检索包含一个或若干个索引值的时间间隔内表行的子集。 它可用于单部分或多部分索引。 以下部分描述了优化程序使用范围访问的条件。

SELECT * FROM t1
  在哪里key_col> 1
  AND key_col<10;

SELECT * FROM t1
  WHERE key_col= 1
  或key_col（15,18,20）;

SELECT * FROM t1
  在哪里key_col'ab％'
  或key_col'BETWEEN'bar'和'foo';

SELECT * FROM t1 WHERE
  （key1 <'abc'AND（key1 LIKE'abcde％'或key1 LIKE'％b'））或者
  （key1 <'bar'和nonkey = 4）或
  （key1 <'uux'和key1>'z'）;

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'

（1，-INF，-INF）<=（ ，，key_part1 ）<（1，+ INF，+ INF）
key_part2key_part3

col_nameIN（val1，...，valN）
 col_name= val1OR ... OR col_name=valN

CREATE TABLE t1（f1 INT NOT NULL，f2 INT NOT NULL，PRIMARY KEY（f1，f2））;
插入t1值
  （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;
分析表t1;

EXPLAIN SELECT f1，f2 FROM t1 WHERE f2> 40;

“skip_scan_range”：{
  “type”：“skip_scan”，
  “指数”： index_used_for_skip_scan，
  “key_parts_used_for_access”：[ key_parts_used_for_access]，
  “范围”：[ range]
}

SELECT ... FROM t1 WHERE（col_1，col_2）IN（（'a'，'b'），（'c'，'d'））;

SELECT ... FROM t1 WHERE（col_1 ='a'和col_2 ='b'）
或（col_1 ='c'和col_2 ='d'）;

该 指数合并 访问方法检索与多行 range 扫描和他们的结果合并到一个。 此访问方法仅合并来自单个表的索引扫描，而不是跨多个表扫描。 合并可以生成其基础扫描的联合，交叉或交叉联合。

可以使用Index Merge的示例查询：

SELECT * FROM tbl_nameWHERE 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。IN key1（1,2）OR key2t1。LIKE' value％'）
  和t2。key1= t1。some_col;

SELECT * FROM t1，t2
  在哪里t1。key1= 1
  AND（t2。key1= t1。some_col或者t2。key2= t1。some_col2）;

在 EXPLAIN 输出中，索引合并方法显示 index_merge 在 type 列中。 在这种情况下，该 key 列包含使用的索引列表，并 key_len 包含这些索引的最长关键部分的列表。

Index Merge访问方法有几种算法，它们显示在 输出 Extra 字段中 EXPLAIN ：

以下部分更详细地描述了这些算法。 优化器根据各种可用选项的成本估算在不同的可能索引合并算法和其他访问方法之间进行选择。

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;

SELECT COUNT（*）FROM t1 WHERE key1 = 1 AND key2 = 1;

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;

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;

这种优化提高了非索引列和常量之间直接比较的效率。 在这种情况下，将条件 “ 下推 ” 到存储引擎以进行评估。 此优化只能由 NDB 存储引擎使用。

对于NDB Cluster，此优化可以消除在群集的数据节点和发出查询的MySQL服务器之间通过网络发送不匹配行的需要，并且可以将查询的使用速度提高5到10倍。条件下推可能但不使用。

假设NDB Cluster表定义如下：

CREATE TABLE t1（
    一个INT，
    b INT，
    KEY（一）
）ENGINE = NDB;

条件下推可以用于查询，例如此处显示的查询，其中包括非索引列和常量之间的比较：

SELECT a，b FROM t1 WHERE b = 10;

条件下推的使用可以在输出中看到 EXPLAIN ：

MySQL的> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1。排******************** *******
           id：1
  select_type：SIMPLE
        表：t1
         类型：全部
possible_keys：NULL
          key：NULL
      key_len：NULL
          ref：NULL
         行：10
        额外：使用推动条件的地方

但是，条件下推 不能 与这两个查询中的任何一个一起使用：

SELECT a，b FROM t1 WHERE a = 10;
SELECT a，b FROM t1 WHERE b + 1 = 10;

条件下推不适用于第一个查询，因为列上存在索引 a 。 （索引访问方法将更有效，因此将优先选择条件下推。）条件下推不能用于第二个查询，因为涉及非索引列的比较 b 是间接的。 （但是，如果您 b + 1 = 10 要 b = 9 在 WHERE 条款中 减少条件，则可以应用条件下推 。）

当索引列与使用 > 或 < 运算符 的常量进行比较时，也可以使用条件下推 ：

MySQL的> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1。排******************** *******
           id：1
  select_type：SIMPLE
        表：t1
         类型：范围
possible_keys：a
          关键：a
      key_len：5
          ref：NULL
         行：2
        额外：使用推动条件的地方

其他支持的条件下推比较包括以下内容：

在前面列表中的所有情况中，条件可以转换为列和常量之间的一个或多个直接比较的形式。

默认情况下启用发动机状态下推。 要在服务器启动时禁用它，请设置 optimizer_switch 系统变量。 例如，在 my.cnf 文件中，使用以下行：

的[mysqld]
optimizer_switch = engine_condition_pushdown =关

在运行时，禁用条件下推，如下所示：

SET optimizer_switch ='engine_condition_pushdown = off';

限制。  发动机状态下推受以下限制：

以前，条件下推仅限于引用条件被推送到的同一表中的列值的术语。 从NDB 8.0.16开始，查询计划中较早的表中的列值也可以从推送条件中引用。 这减少了在连接处理期间SQL节点必须处理的行数。 过滤也可以在LDM线程中并行执行，而不是在单个 mysqld 进程中执行。 这有可能大大提高查询性能。

在以下两种情况下，连接算法不能与先前表中的引用列组合：

索引条件下推（ICP）是对MySQL使用索引从表中检索行的情况的优化。 如果没有ICP，存储引擎会遍历索引以查找基表中的行，并将它们返回给MySQL服务器，该服务器会评估 WHERE 行 的 条件。 启用ICP后，如果 WHERE 只使用索引中的列来评估 部分 条件，MySQL服务器会推送这部分内容。 WHERE 条件下到存储引擎。 然后，存储引擎使用索引条目评估推送的索引条件，并且仅当满足该条件时才从表中读取行。 ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

指数条件下推优化的适用性受以下条件限制：

要了解此优化的工作原理，请首先考虑在不使用索引条件下推时索引扫描的进度：

使用索引条件下推，扫描会像这样进行：

EXPLAIN 使用“索引条件下推”时， 输出显示 Using index condition 在 Extra 列中。 它没有显示， Using index 因为当必须读取完整的表行时，这不适用。

假设一个表包含有关人员及其地址的信息，并且该表的索引定义为 INDEX (zipcode, lastname, firstname) 。 如果我们知道一个人的 zipcode 价值但不确定姓氏，我们可以这样搜索：

选择*来自人
  WHERE zipcode ='95054'
  AND lastname LIKE'％etrunia％'
  和地址LIKE'％Main Street％';

MySQL可以使用索引来扫描人 zipcode='95054' 。 第二部分（ lastname LIKE '%etrunia%' ）不能用于限制必须扫描的行数，因此如果没有Index Condition Pushdown，此查询必须为所有拥有的人检索完整的表行 zipcode='95054' 。

使用索引条件下推，MySQL lastname LIKE '%etrunia%' 在读取整个表行之前 检查该 部分。 这样可以避免读取与索引元组相对应的完整行，这些行匹配 zipcode 条件而不是 lastname 条件。

默认情况下启用索引条件下推。 可以 optimizer_switch 通过设置 index_condition_pushdown 标志 来控制 系统变量 ：

SET optimizer_switch ='index_condition_pushdown = off';
SET optimizer_switch ='index_condition_pushdown = on';

请参见 第8.9.2节“可切换的优化” 。

MySQL使用嵌套循环算法或其变体在表之间执行连接。

表连接类型
t1范围
t2 ref
t3 ALL

对于t1匹配范围中的每一行{
  对于t2中匹配引用键的每一行{
    对于t3中的每一行{
      如果行满足连接条件，则发送给客户端
    }
  }
}

对于t1匹配范围中的每一行{
  对于t2中匹配引用键的每一行{
    存储在连接缓冲区中使用t1，t2中的列
    如果缓冲区已满{
      对于t3中的每一行{
        对于连接缓冲区中的每个t1，t2组合{
          如果行满足连接条件，则发送给客户端
        }
      }
      空连接缓冲区
    }
  }
}

如果缓冲区不为空{
  对于t3中的每一行{
    对于连接缓冲区中的每个t1，t2组合{
      如果行满足连接条件，则发送给客户端
    }
  }
}

（S* C）/ join_buffer_size + 1

表达联接的语法允许嵌套联接。 以下讨论涉及 第13.2.10.2节“JOIN语法”中 描述的连接语法 。

table_factor 与SQL标准相比， 语法 扩展了。 后者只接受 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或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或t2.b IS NULL

然而，这两个表达并不相同。 看到这一点，假设表 t1 ， t2 以及 t3 具有以下状态：

在这种情况下，第一个表达式返回结果集包括行 (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 和任何条件 P 在属性 t2.b 和 t3.b ：

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
  在哪里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.6节“嵌套循环连接算法” ）。 假设3个表的连接查询 T1,T2,T3 具有以下形式：

SELECT * FROM T1 INNER JOIN T2 ON P1（T1，T2）
                 INNER JOIN T3 ON P2（T2，T3）
  P（T1，T2，T3）

这里， P1(T1,T2) 和 P2(T3,T3) 是一些连接条件（上表达），而 P(T1,T2,T3) 超过表中的列的条件 T1,T2,T3 。

嵌套循环连接算法将以下列方式执行此查询：

对于T1中的每一行t1 {
  对于T2中的每一行t2，使得P1（t1，t2）{
    对于T3中的每一行t3，使得P2（t2，t3）{
      IF P（t1，t2，t3）{
         T：= T 1 || || T2 T3; 输出t;
      }
    }
  }
}

符号 t1||t2||t3 表示通过连接的行的列构成的行 t1 ， t2 和 t3 。 在以下某些示例中， NULL 表名称的显示表示一行 NULL 用于该表的每一列。 例如， t1||t2||NULL 指示通过连接的行的列构成的行 t1 和 t2 ，以及 NULL 对于每一列 t3 。 据说这样一行是完成的 NULL 。

现在考虑使用嵌套外连接的查询：

SELECT * FROM T1 LEFT JOIN
              （T2 LEFT JOIN T3 ON P2（T2，T3））
              ON P1（T1，T2）
  P（T1，T2，T3）

对于此查询，修改嵌套循环模式以获取：

对于T1中的每一行t1 {
  BOOL f1：= FALSE;
  对于T2中的每一行t2，使得P1（t1，t2）{
    BOOL f2：= FALSE;
    对于T3中的每一行t3，使得P2（t2，t3）{
      IF P（t1，t2，t3）{
        T：= T 1 || || T2 T3; 输出t;
      }
      F2 = TRUE;
      F1 = TRUE;
    }
    IF（！f2）{
      IF P（t1，t2，NULL）{
        T：= T 1 || || T2 NULL; 输出t;
      }
      F1 = TRUE;
    }
  }
  IF（！f1）{
    IF P（t1，NULL，NULL）{
      T：= T 1 || || NULL NULL; 输出t;
    }
  }
}

通常，对于外连接操作中第一个内部表的任何嵌套循环，引入一个在循环之前关闭并在循环之后检查的标志。 当对于来自外部表的当前行，找到与表示内部操作数的表的匹配时，该标志被打开。 如果在循环周期结束时标志仍然关闭，则找不到外部表的当前行的匹配项。 在这种情况下，行由 NULL 内部表的列的值 补充 。 结果行将传递给输出的最终检查或下一个嵌套循环，但前提是该行满足所有嵌入外连接的连接条件。

在该示例中，嵌入了由以下表达式表示的外连接表：

（T2 LEFT JOIN T3 ON P2（T2，T3））

对于具有内部联接的查询，优化程序可以选择不同的嵌套循环顺序，例如：

对于T3中的每一行t3 {
  对于T2中的每一行t2，使得P2（t2，t3）{
    对于T1中的每一行t1，使得P1（t1，t2）{
      IF P（t1，t2，t3）{
         T：= T 1 || || T2 T3; 输出t;
      }
    }
  }
}

对于具有外连接的查询，优化器只能选择这样的顺序，其中外部表的循环位于内部表的循环之前。 因此，对于具有外连接的查询，只能有一个嵌套顺序。 对于以下查询，优化程序将评估两种不同的嵌套。 在两个嵌套中， T1 必须在外部循环中处理，因为它在外部联接中使用。 T2 并 T3 在内部联接中使用，因此必须在内部循环中处理联接。 但是，因为连接是内连接， T2 并且 T3 可以按任何顺序处理。

SELECT * T1 LEFT JOIN（T2，T3）ON P1（T1，T2）和P2（T1，T3）
  P（T1，T2，T3）

一个嵌套评估 T2 ，然后 T3 ：

对于T1中的每一行t1 {
  BOOL f1：= FALSE;
  对于T2中的每一行t2，使得P1（t1，t2）{
    对于T3中的每一行t3，使得P2（t1，t3）{
      IF P（t1，t2，t3）{
        T：= T 1 || || T2 T3; 输出t;
      }
      F1：= TRUE
    }
  }
  IF（！f1）{
    IF P（t1，NULL，NULL）{
      T：= T 1 || || NULL NULL; 输出t;
    }
  }
}

另一个嵌套评估 T3 ，然后 T2 ：

对于T1中的每一行t1 {
  BOOL f1：= FALSE;
  对于T3中的每一行t3，使得P2（t1，t3）{
    对于T2中的每一行t2，使得P1（t1，t2）{
      IF P（t1，t2，t3）{
        T：= T 1 || || T2 T3; 输出t;
      }
      F1：= TRUE
    }
  }
  IF（！f1）{
    IF P（t1，NULL，NULL）{
      T：= T 1 || || NULL NULL; 输出t;
    }
  }
}

在讨论内部联接的嵌套循环算法时，我们省略了一些细节，这些细节对查询执行性能的影响可能很大。 我们没有提到所谓的 “ 推倒 ” 条件。 假设我们的 WHERE 条件 P(T1,T2,T3) 可以用一个连接公式表示：

P（T1，T2，T2）= C1（T1）和C2（T2）和C3（T3）。

在这种情况下，MySQL实际上使用以下嵌套循环算法来执行带有内连接的查询：

对于T1中的每一行t1，使得C1（t1）{
  对于T2中的每一行t2，使得P1（t1，t2）和C2（t2）{
    对于T3中的每一行t3，使得P2（t2，t3）和C3（t3）{
      IF P（t1，t2，t3）{
         T：= T 1 || || T2 T3; 输出t;
      }
    }
  }
}

你看，每个合取的 C1(T1) ， C2(T2) ， C3(T3) 是最内环的推到最外环的地方进行评估。 如果 C1(T1) 是一个非常严格的条件，这种情况下推可能会大大减少 T1 传递给内部循环的 表中的行数 。 结果，查询的执行时间可能会大大改善。

对于具有外部联接的查询， WHERE 仅在发现外部表中的当前行与内部表中的匹配项之后才检查条件。 因此，从内嵌套循环中推出条件的优化不能直接应用于具有外连接的查询。 这里我们必须引入条件下推谓词，这些谓词由遇到匹配时打开的标志保护。

回想一下外连接的这个例子：

P（T1，T2，T3）= C1（T1）和C（T2）和C3（T3）

对于该示例，使用受保护的下推条件的嵌套循环算法如下所示：

对于T1中的每一行t1，使得C1（t1）{
  BOOL f1：= FALSE;
  对于T2中的每一行t2
      这样P1（t1，t2）AND（f1？C2（t2）：TRUE）{
    BOOL f2：= FALSE;
    对于T3中的每一行t3
        这样P2（t2，t3）AND（f1 && f2？C3（t3）：TRUE）{
      IF（f1 && f2？TRUE：（C2（t2）AND C3（t3）））{
        T：= T 1 || || T2 T3; 输出t;
      }
      F2 = TRUE;
      F1 = TRUE;
    }
    IF（！f2）{
      IF（f1？TRUE：C2（t2）&& P（t1，t2，NULL））{
        T：= T 1 || || T2 NULL; 输出t;
      }
      F1 = TRUE;
    }
  }
  IF（！f1 && P（t1，NULL，NULL））{
      T：= T 1 || || NULL NULL; 输出t;
  }
}

通常，可以从诸如 P1(T1,T2) 和的 连接条件中提取下推谓词 P(T2,T3) 。 在这种情况下，下推谓词也受到一个标志的保护，该标志阻止检查 NULL 由相应的外连接操作生成 的 - 实现行 的谓词 。

如果由 WHERE 条件中 的谓词引起，则禁止在同一嵌套连接中通过键从一个内部表访问另一个内部表 。

外连接包括 LEFT JOIN 和 RIGHT JOIN 。

MySQL实现 如下： A LEFT JOIN B join_specification

该 RIGHT JOIN 实现类似于 LEFT JOIN 表格角色颠倒的实现。 右连接转换为等效的左连接，如 第8.2.1.9节“外连接简化”中所述 。

对于a LEFT JOIN ，如果 WHERE 生成的 NULL 行 的 条件始终为false ，则将 LEFT JOIN 其更改为内部联接。 例如， WHERE 如果条款是在下面的查询错误的 t2.column1 是 NULL ：

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_1WHERE condition_2OR 0 = 1

优化器现在在准备期间看到0 = 1始终为false，使得 OR 0 = 1 冗余，并将其删除，留下：

SELECT * FROM t1 LEFT JOIN t2 ON condition_1在哪里condition_2

现在，优化器可以将查询重写为内部联接，如下所示：

SELECT * FROM t1 JOIN t2 WHERE condition_1ANDcondition_2

现在优化器可以在表 t2 之前 使用表 ， t1 如果这样做会导致更好的查询计划。 要提供有关表连接顺序的提示，请使用优化程序提示; 请参见 第8.9.3节“优化程序提示” 。 或者，使用 STRAIGHT_JOIN ; 请参见 第13.2.10节“SELECT语法” 。 但是， STRAIGHT_JOIN 可能会阻止使用索引，因为它会禁用半连接转换; 请参见 第8.2.2.1节“使用半连接转换优化子查询，派生表，视图引用和公用表表达式” 。

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 LEFT JOIN T2 ON P1（T1，T2）
  P（T1，T2）和R（T2）

如果查询是按写入的方式执行的，那么优化器别无选择，只能在限制 T1 较多的 表 之前 访问限制较少的 表 T2 ，这可能会产生效率非常低的执行计划。

相反，如果 WHERE 条件为空拒绝 ，MySQL会将查询转换为不带外连接操作的查询 。 （也就是说，它将外连接转换为内连接。）如果一个条件计算为 FALSE 或 UNKNOWN 为任何 NULL 为该操作生成的已完成行，则该 条件对于外连接操作被称为空拒绝 。

因此，对于此外连接：

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

诸如此类的条件被null拒绝，因为对于任何已完成的 NULL 行（ T2 列设置为 NULL ）， 它们不能为真 ：

T2.B不是空的
T2.B> 3
T2.C <= T1.C
T2.B <2或T2.C> 1

诸如此类的条件不会被拒绝，因为它们对于已完成的 NULL 行 可能是真的 ：

T2.B是空的
T1.B <3或T2.B不为空
T1.B <3或T2.B> 3

检查外部联接操作的条件是否为空拒绝的一般规则很简单：

对于查询中的一个外部联接操作，条件可以为null，而对于另一个条件，则不为null-rejected。 在此查询中， WHERE 对于第二个外部联接操作 ， 条件为空 - 拒绝，但对于第一个外部联接操作，条件不为空 - 拒绝：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A = T1.A
                 左接点T3接通T3.B = T1.B
  在哪里T3.C> 0

如果 WHERE 查询中的外连接操作 的 条件为空 - 拒绝，则外连接操作将由内连接操作替换。

例如，在前面的查询中，第二个外部联接是空的拒绝，可以由内部联接替换：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A = T1.A
                 INNER JOIN T3 ON T3.B = T1.B
  在哪里T3.C> 0

对于原始查询，优化程序仅评估与单个表访问顺序兼容的计划 T1,T2,T3 。 对于重写的查询，它还考虑访问顺序 T3,T1,T2 。

一个外连接操作的转换可以触发另一个外连接操作的转换。 因此，查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A = T1.A
                 LE3 JOIN T3 ON T3.B = T2.B
  在哪里T3.C> 0

首先转换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A = T1.A
                 INNER JOIN T3 ON T3.B = T2.B
  在哪里T3.C> 0

这相当于查询：

SELECT * FROM（T1 LEFT JOIN T2 ON T2.A = T1.A），T3
  T3.C> 0且T3.B = T2.B

剩余的外连接操作也可以由内连接替换，因为条件 T3.B=T2.B 为空拒绝。 这导致查询没有外部联接：

SELECT * FROM（T1 INNER JOIN T2 ON T2.A = T1.A），T3
  T3.C> 0且T3.B = T2.B

有时，优化器会成功替换嵌入式外连接操作，但无法转换嵌入外连接。 以下查询：

SELECT * FROM T1 LEFT JOIN
              （T2 LEFT JOIN T3 ON T3.B = T2.B）
              在T2.A = T1.A
  在哪里T3.C> 0

转换为：

SELECT * FROM T1 LEFT JOIN
              （T2 INNER JOIN T3 ON T3.B = T2.B）
              在T2.A = T1.A
  在哪里T3.C> 0

这只能重写为仍然包含嵌入外连接操作的表单：

SELECT * FROM T1 LEFT JOIN
              （T2，T3）
              ON（T2.A = T1.A和T3.B = T2.B）
  在哪里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）
              在T2.A = T1.A和T3.C = T1.C
  在哪里T3.D> 0或T1.D> 0

因此，查询可以转换为：

SELECT * FROM T1 LEFT JOIN
              （T2，T3）
              在T2.A = T1.A和T3.C = T1.C和T3.B = T2.B
  在哪里T3.D> 0或T1.D> 0

当表很大并且没有存储在存储引擎的缓存中时，使用辅助索引上的范围扫描读取行可能导致对基表的许多随机磁盘访问。 通过磁盘扫描多范围读取（MRR）优化，MySQL尝试通过首先扫描索引并收集相关行的密钥来减少范围扫描的随机磁盘访问次数。 然后对键进行排序，最后使用主键的顺序从基表中检索行。 磁盘扫描MRR的动机是减少随机磁盘访问的数量，而是实现对基表数据的更顺序扫描。

多范围读取优化提供以下好处：

在虚拟生成列上创建的二级索引不支持MRR优化。 InnoDB 支持虚拟生成列上的二级索引。

以下方案说明了MRR优化何时有利：

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

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

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

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

两个 optimizer_switch 系统变量标志提供了使用MRR优化的接口。 该 mrr 标志控制是否启用MRR。 如果 mrr 是enabled（ on ），则该 mrr_cost_based 标志控制优化器是否尝试在使用和不使用MRR（ on ） 之间进行基于成本的选择， 或者尽可能使用MRR（ off ）。 默认情况下， mrr 是 on 和 mrr_cost_based 是 on 。 请参见 第8.9.2节“可切换的优化” 。

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

在MySQL中，可以使用批量密钥访问（BKA）连接算法，该算法使用对连接表的索引访问和连接缓冲区。 BKA算法支持内连接，外连接和半连接操作，包括嵌套外连接。 BKA的优点包括提高连接性能，因为更高效的表扫描。 此外，先前仅用于内连接的块嵌套循环（BNL）连接算法已扩展，可用于外连接和半连接操作，包括嵌套外连接。

以下部分讨论了连接缓冲区管理，它是原始BNL算法扩展，扩展BNL算法和BKA算法的基础。 有关半连接策略的信息，请参见 第8.2.2.1节“使用半连接转换优化子查询，派生表，视图引用和公用表表达式”

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

在连接处理中，前缀行是从连接中的一个表传递到下一个表的那些行。 通常，优化器尝试在连接顺序的早期放置具有低前缀计数的表，以保持行组合的数量不会快速增加。 在某种程度上，优化程序可以使用有关从一个表中选择并传递给下一个表的行的条件的信息，它可以更准确地计算行估计并选择最佳执行计划。

如果没有条件筛选，则表的前缀行计数基于 WHERE 子句根据优化程序选择的访问方法选择 的估计行数 。 条件筛选使优化器能够使用 WHERE 访问方法未考虑 的 子句 中的其他相关条件 ，从而改进其前缀行计数估计。 例如，即使可能存在可用于从连接中的当前表中选择行的基于索引的访问方法，但在该表中可能还存在其他条件。 WHERE 可以过滤（进一步限制）传递给下一个表的合格行的估计的子句。

只有在以下情况下，条件才会影响过滤估算：

在 EXPLAIN 输出中， rows 列指示所选访问方法的行估计，该 filtered 列反映条件筛选的效果。 filtered 值以百分比表示。 最大值为100，这意味着不会对行进行过滤。 值从100开始减少表示过滤量增加。

前缀行计数（估计从连接中的当前表传递到下一个的行数）是 rows 和 filtered 值 的乘积 。 也就是说，前缀行计数是估计的行计数，通过估计的过滤效果减少。 例如，如果 rows 是1000并且 filtered 是20％，则条件过滤将估计的行计数1000减少到前缀行计数1000×20％= 1000×.2 = 200。

请考虑以下查询：

选择 *
  来自员工JOIN部门ON employee.dept_no = department.dept_no
  WHERE employee.first_name ='John'
  AND employee.hire_date BETWEEN'2018-01-01'EN'2018-06-01';

假设数据集具有以下特征：

没有条件过滤， EXPLAIN 产生如下输出：

+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +
| id | 表| 类型| possible_keys | 关键| ref | 行| 过滤|
+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +
| 1 | 员工| ref | name，h_date，dept | 名字| const | 8 | 100.00 |
| 1 | 部门| eq_ref | 主要| 主要| dept_no | 1 | 100.00 |
+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +

因为 employee ， name 索引 上的访问方法会 获取与名称匹配的8行 'John' 。 没有进行过滤（ filtered 是100％），因此所有行都是下一个表的前缀行：前缀行计数是 rows × filtered = 8×100％= 8。

通过条件过滤，优化器还考虑 WHERE 了访问方法未考虑 的 子句中的 条件 。 在这种情况下，优化器使用试探法来估计所述的16.31％的滤波效果 BETWEEN 的条件下 employee.hire_date 。 结果， EXPLAIN 产生如下输出：

+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +
| id | 表| 类型| possible_keys | 关键| ref | 行| 过滤|
+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +
| 1 | 员工| ref | name，h_date，dept | 名字| const | 8 | 16.31 |
| 1 | 部门| eq_ref | 主要| 主要| dept_no | 1 | 100.00 |
+ ---- + ------------ + -------- + ------------------ + --- ------ + --------- + ------ + ---------- +

现在前缀行计数为 rows × filtered = 8×16.31％= 1.3，这更接近地反映了实际数据集。

通常，优化器不会为最后一个连接表计算条件过滤效果（前缀行数减少），因为没有下一个表要传递行。 发生异常 EXPLAIN ：要提供更多信息，将为所有已连接的表（包括最后一个表）计算过滤效果。

要控制优化程序是否考虑其他过滤条件，请使用 系统变量 的 condition_fanout_filter 标志 optimizer_switch （请参见 第8.9.2节“可切换的优化” ）。 默认情况下启用此标志，但可以禁用此标志以禁止条件筛选（例如，如果发现特定查询在没有它的情况下产生更好的性能）。

如果优化器过高估计条件过滤的影响，则性能可能比不使用条件过滤时更差。 在这种情况下，这些技术可能有所帮助：

常量值和列值之间的比较，其中常量值超出范围或相对于列类型的错误类型现在在查询优化期间而不是逐行处理而不是在执行期间处理一次。 可以以这种方式处理的比较是 > ， >= ， < ， <= ， <> / != ， = 和 <=> 。

考虑以下语句创建的表：

CREATE TABLE t（c TINYINT UNSIGNED NOT NULL）;

所述 WHERE 查询条件 SELECT * FROM t WHERE c < 256 包含积分常数256，其超出范围为 TINYINT UNSIGNED 柱。 以前，这是通过将两个操作数视为较大的类型来处理的，但现在，由于任何允许的值 c 小于常量， WHERE 表达式可以折叠为 WHERE 1 ，以便将查询重写为 SELECT * FROM t WHERE 1 。

这使得优化器可以 WHERE 完全 删除 表达式。 如果列可以 c 为空（即，仅定义为 TINYINT UNSIGNED ），则将重写查询，如下所示：

SELECT * FROM t WHERE ti not NULL

与支持的MySQL列类型相比，对常量执行折叠，如下所示：