8章优化

本节讨论的优化，可以处理WHERE条款。考试使用SELECT报表，但相同的优化应用哪里条款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_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 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, ... ;

这个range访问方法使用一个单一的指标来检索，都包含在一个或几个指标值区间表的行的子集。它可以用一个单一的部分或多部分指标。以下各节描述的条件下，优化器使用范围的访问。

SELECT * FROM T1的地方key_col&#62; 1key_col< 10;SELECT * FROM t1  WHEREkey_col= 1  ORkey_col在（15,18,20）；选择*从T1的地方key_col像“AB %”或key_col“酒吧”和“foo”；

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');

key_part1key_part2key_part3ABC是空1空1 XYZ Foo是空2 1 1 1 1 XYZ的ABC ABC 1 2 2 1是AAA

(1,-inf,-inf) <= (key_part1，key_part2，key_part3) < (1,+inf,+inf)

col_name在（val1，…，valN）col_name=val1或或col_name=valN

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;

“跳过扫描“{”：“跳跃式”_扫描”，“指标”：index_used_for_skip_scan，“key_parts_used_for_access”：[key_parts_used_for_access]，”前缀范围”：[prefix_ranges“范围”：[ ]，range] }

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' );

这个索引进行合并访问方法检索行多range扫描和合并成一个。这种访问方法将索引扫描只从一个单一的表扫描，不能跨多个表。合并产生的工会，工会的路口，或其基本扫描的十字路口。

示例查询的索引合并可以使用：

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

进入EXPLAIN输出指标的合并方法的出现index_merge在类型专栏在这种情况下，的key列包含一系列指标，并该_懒惰contains a list of the最长密钥份额for those指标。

索引合并访问方法有几种算法，这是显示在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存储引擎

MySQL集群，这种优化可以消除需要发送的数据不匹配的行在集群节点发出查询MySQL服务器之间的网络，可以加快查询速度，它是由五因子用于十倍的情况下，条件下推可以但不使用。

假设一个MySQL簇表的定义如下：

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。行***************************编号：1 select_type：简单表：T1型：allpossible_keys：空键：空key_len：零号：空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. 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

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

在所有的情况下，上述列表中，可以转换成一个或多个列和一个常数之间的直接比较是可能的形成条件。

发动机状态下默认是启用。禁用它在服务器启动时，设置optimizer_switch可变系统。For example，in amy.cnf文件，使用这些线：

[mysqld]
optimizer_switch=engine_condition_pushdown=off

在运行时，禁止条件下推这样：

SET optimizer_switch='engine_condition_pushdown=off';

局限性发动机状态下推是受以下限制：

指数条件下推（ICP）是一种优化的情况下，MySQL检索行从表使用索引。没有ICP，存储引擎遍历索引查找列在基表中返回到MySQL服务器的评估WHERE行条件。用ICP启用，如果部分的哪里条件可以通过指数仅列评估，MySQL服务器将这部分的WHERE条件下的存储引擎。存储引擎进行推指数条件下利用索引项，如果这是满意的是从表中读取的行。ICP可以减少次存储引擎的数量必须访问基表和时代的MySQL服务器必须访问存储引擎数。

该指数的条件下推优化的适用性是受这些条件：

要了解如何优化，首先考虑的是如何索引扫描时的所得指标条件下推是没有用的：

使用索引扫描所得的条件下推，这样反而：

EXPLAIN输出显示索引的使用条件在Extra当指数条件下推用柱。它不显示使用索引因为这并不适用于当全表的行必须读。

假设一个表包含关于人和他们的地址信息，表定义的索引INDEX (zipcode, lastname, firstname)。如果我们知道一个人的邮政编码价值但不确定的名字，我们可以搜索这样的：

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

MySQL可以使用索引扫描的人zipcode='95054'. 第二部分（姓是“etrunia %）不能用于限行必须扫描的数量，所以没有索引的条件下推，这个查询要检索所有人有充分的表行zipcode='95054'

指数条件下推，MySQL检查lastname LIKE '%etrunia%'在阅读完整的表行。这避免了阅读整行对应元组匹配指数邮政编码条件但不是lastname条件

指数条件下推是默认启用。它可以控制的optimizer_switch系统变量的设置_ _ pushdown条件指数国旗：

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

看到第3，“开关的优化”

MySQL执行连接上使用嵌套循环算法或变化表之间。

表连接typet1 ranget2 reft3所有

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
    }
  }
}

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 * C)/join_buffer_size + 1

表达和语法允许嵌套连接。下面的讨论是指在加入语法描述第13.2.10.2，加入“语法”

语法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在语法上是等价的内部联接他们可以相互替换。在标准的SQL，他们是不等价的。INNER JOIN使用一个打开（放）Clause；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有以下状态：

在这种情况下，第一个表达式返回的结果集的行(1,1,101,101)，（2，零，零，零），而第二表达式返回的行(1,1,101,101)，（2，零，零，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)

当执行命令的连接操作在连接表达式（join_table）不是从左到右，我们谈论嵌套连接。考虑以下查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a  WHERE t1.a > 1SELECT * 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)第二查询是必要的，虽然理论上查询可以解析没有他们：我们仍会有明确的语法结构的查询，因为左连接和ON对表达的左和右的分隔符的作用（T2，T3）

前面的例子说明这一点：

嵌套的外连接查询的执行在同一管道内连接的查询方式。更确切地说，一种变异的嵌套循环连接算法开发。记得算法的嵌套循环连接执行查询（见第8.2.1.6，“嵌套循环连接算法）。假设一个加入3多表查询T1,T2,T3这种形式：

SELECT * FROM T1内P1（T1、T2和T2）内加入T3（T2，T3），P2 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。在some examples of the following，NULL其中一个表的名字出现在这一行无效的用于每个列的表。例如,t1||t2||NULL显示一行行柱连接构造T1和t2，和无效的每一列t3。这一行是无效的-是的

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

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（T2、T3）左连接在P1（T1，T2）和P2（T1，T3），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条件

外部连接：LEFT JOIN和右连接

MySQL实现A LEFT JOIN B join_condition如下：

这个RIGHT JOIN实现是类似的左连接与表的角色逆转。正确的连接转换为等效的左连接，所述第8.2.1.9，“外连接简化”

对于一个LEFT JOIN，如果哪里条件总是错误的产生NULL行的左连接改为内连接。例如，在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;

现在，优化器可以使用表t2在表T1如果这样做会导致一个更好的查询计划。提供一个关于表提示连接顺序，使用优化器提示；看第8.9.2，“优化器提示”。另外，使用STRAIGHT_JOIN它；第13.2.10，选择“语法”。然而,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)作为一个连接的连接哪里条件（或连接条件的嵌入连接，如果有的话）。

当优化器评估外加入行动计划，它只考虑计划里，对于每一次这样的操作，外表内表访问之前。优化器的选择是因为只有这样的计划，使外部连接是使用嵌套循环算法执行有限公司。

考虑一个查询这个形式，在R(T2)大大缩小了从表匹配的行数T2：

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

如果执行查询的书面，优化器没有选择只能访问限制较少的表T1在更严格的表T2，这可能会产生一个非常低效的执行计划。

相反，MySQL将查询到没有外连接操作查询如果WHERE条件是零拒绝。（即，将外部连接到内部联接。）的条件是零拒绝外部联接操作，如果它对错误的或UNKNOWN对于任何无效的补行生成操作

因此，此外连接：

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

如这些空拒绝了因为他们不能为任何真实情况NULL补排（带T2栏目设置NULL）：

T2.B IS NOT NULLT2.B > 3T2.C <= T1.CT2.B < 2 OR T2.C > 1

如这些都不是零拒绝，因为他们可以为真的条件NULL补排：

T2.B IS NULLT1.B < 3 OR T2.B IS NOT NULLT1.B < 3 OR T2.B > 3

一般规则检查是否为null拒绝外部联接操作简单：

一个条件可以是零拒绝一个外连接操作查询中不空拒绝另一个。在这个查询的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条件在这个查询的哪里条件不是零拒绝嵌入式外加入，但加入嵌入外部联接条件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

通过一系列阅读排在第二索引扫描可以导致许多随机磁盘访问基表当表大而不存储在存储引擎的缓存。与磁盘扫描多方位阅读（MRR）优化，MySQL试图减少范围扫描首先通过扫描指数仅收集钥匙相关行的随机磁盘访问次数。然后按键排序，最后行用主键顺序从基表中检索。磁盘扫描MRR的动机是为了减少随机磁盘访问次数而实现对基表中的数据，更连续扫描。

多方位阅读优化提供这些好处：

MRR优化不次要指标对虚拟生成的列创建的支持。InnoDB支持二级指标虚拟生成的列。

以下情况说明当MRR优化是有利的：

场景一：材料可用于InnoDB和MyISAM表索引范围扫描和等值连接操作。

方案B：MRR可用于NDB多方位索引表扫描或表演等时的属性连接。

当使用的材料，Extra柱EXPLAIN输出显示使用MRR

InnoDB和MyISAM如果不充分的表行不需要访问生成查询结果使用MRR。这种情况如果结果可以产生完全基于信息索引中的元组（通过覆盖指标MRR）提供任何好处。

二optimizer_switch系统变量标志提供了一个接口，MRR的优化使用。这个MRR控制是否启用MRR是旗帜。如果mrr启用（打开（放）页：1mrr_cost_based标志控制是否优化器尝试基于成本的选择使用和不使用MRR（打开（放）（b）无论是否有可能（或）off页：1By default，MRR是on和mrr_cost_based是on。看到第3，“开关的优化”

MRR，存储引擎使用的价值read_rnd_buffer_size系统变量作为一个指南，它能为缓冲区分配多少内存。该发动机采用了read_rnd_buffer_size字节数和确定范围，通过在一个单一的过程。

在MySQL中，一批重点访问（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可过滤条款（限制）的排位赛传递给下一个表行的估计。

条件有利于滤波估计只有：

进入EXPLAIN输出的排列指示所选择的接入方法行估计，和filtered柱反映状态的滤波作用。过滤值表示为百分比。最大值是100，这意味着没有过滤的行发生。减少100值表明越来越多的过滤。

前缀的行数（行估计是通过电流表连接到下一个数）的产品rows和过滤价值观。即前缀行数的行数，以估计的滤波效果降低。例如，如果rows1000，过滤is 20%, condition filtering reduces the estimated row count of 1000 to a prefix row count of 1000 × 20% = 1000 × .2 = 200.

考虑以下查询：

SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';

假设数据集具有这些特点：

无状态过滤，EXPLAIN这样产生的输出：

---- ------------ -------- ------------------ --------- --------- ------ ---------- | ID |表|型| possible_keys |关键| REF |行|过滤| ---- ------------ -------- ------------------ --------- --------- ------ ---------- | 1 |员工| REF |名字，h_date，系|名字| const | 8 | 100 | | 1 |部| eq_ref |初级|小学| dept_no | 1 | 100 | ---- ------------ -------- ------------------ --------- --------- ------ ----------

为employee，在访问方法姓名指数回升8行名称匹配'John'。没有过滤了（过滤100%），所以所有的行的下一行：表前缀前缀的行数rows×过滤= 8 × 100% = 8.

有条件的过滤，优化器还考虑到条件从WHERE条款不考虑访问方法。在这种情况下，优化器使用启发式方法的16.31%个滤波效果评价BETWEEN条件employee.hire_date。因此,EXPLAIN这样产生的输出：

---- ------------ -------- ------------------ --------- --------- ------ ---------- | ID |表|型| possible_keys |关键| REF |行|过滤| ---- ------------ -------- ------------------ --------- --------- ------ ---------- | 1 |员工| REF |名字，h_date，系|名字| const | 8 | 16.31 | | 1 |部| eq_ref |初级|小学| dept_no | 1 | 100 | ---- ------------ -------- ------------------ --------- --------- ------ ----------

现在的前缀行计数rows×过滤= 8 × 16.31% = 1.3, which more closely reflects actual data set.

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

控制是否优化器考虑额外的过滤条件，使用condition_fanout_filter旗的optimizer_switch系统变量（参见第3，“开关的优化”）。这个标志是默认启用，但可以禁用抑制条件过滤（例如，如果一个特定的查询发现，产生更好的性能没有）。

如果优化器高估状态滤波的效果，性能可能比如果条件过滤不差。在这种情况下，这些技术可以帮助：

MySQL可以执行相同的优化col_nameIS NULL它可以使用col_name=constant_value。例如，MySQL可以使用指标和范围搜索无效的与IS 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条款包括col_nameIS NULL一个声明为柱条件不为空，表现为优化掉。这种优化不发生案件时可能产生的柱NULL无论如何；例如，如果是一对一的右侧表左连接

MySQL也可以优化组合col_name = expr OR col_name IS NULL，一是解决子查询的常见形式。EXPLAIN演出ref_or_null当这种优化方法

这种优化处理IS NULL对其中的关键部分

有些是优化查询的例子，假设有一个索引列a和B表t2：

SELECT * FROM t1 WHERE t1.a=exprOR 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首先对参考答案一读的作品，然后用一个单独的搜索排无效的关键值

优化只能处理一IS NULL水平。在下面的查询，MySQL使用键查询仅在表达(t1.a=t2.a AND t2.a IS NULL)和无法使用的关键部分b：

SELECT * FROM t1, t2  WHERE (t1.a=t2.a AND t2.a IS NULL)  OR (t1.b=t2.b AND t2.b IS NULL);

本节描述了当MySQL可以使用索引来满足ORDER BY的条款，filesort使用索引时，不能使用，和执行计划的信息可以从优化器ORDER BY

一个ORDER BY与无极限可以将不同的命令行，讨论第8.2.1.17，“限制查询优化”

SELECT a FROM t1 ORDER BY a;

选择ABS（一）作为一个从T1的命令；

选择ABS（一）B从T1的命令；

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

为了满足一个最普通的方式GROUP BY条款是扫描整个表，创建一个新的临时表，从每一组中的所有行是连续的，然后使用这个临时表发现组和应用聚合函数（如有）。在一些情况下，MySQL能够做的更好，通过使用索引访问避免创建临时表。

使用指标的最重要的前提GROUP BY是所有组列参考属性相同的指数，而指数存储键为（是真实的，例如，一个BTREE指数，但不一搞砸指数）。是否使用临时表可以通过索引访问取代也取决于这一指标的部分是在查询中使用的，这部分规定的条件，并选择聚合函数。

有两种方法来执行GROUP BY通过索引访问查询，在下面的章节中详细。第一种方法适用于所有的分组操作范围谓词（如果有的话）。第二方法首先执行范围扫描，然后组产生的元组。

松散索引扫描也可以用来在没有GROUP BY在一定的条件下。看到跳跃扫描范围的访问方法

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

DISTINCT结合顺序需要在许多情况下，一个临时表。

因为DISTINCT可以使用组，学会工作列在MySQLORDER BY或有条款不属于所选列的一部分。看到第12.19.3，MySQL处理组”

在大多数情况下，一个DISTINCT条款可以被视为一种特殊情况组。例如，下面的两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;

由于这种等价性，适用于优化GROUP BY查询也可用于查询和不同的条款.因此，在优化的可能性的更多细节DISTINCT查询，看第8.2.1.15，“优化”组

当结合LIMIT row_count与不同的MySQL停止，尽快找到row_count独特的行

如果你不使用所有的表在查询中指定的列，MySQL停止扫描任何未使用的表，只要它找到第一个匹配。在下列情况下，假设t1之前使用T2（你可以检查EXPLAINMySQL），从阅读。T2（对于任何特定的行t1）当它发现第一排T2：

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

如果你需要从结果集只有一个指定的行数，使用LIMIT查询中的条款，而不是取整个结果集和扔掉的额外数据。

MySQL查询优化，有时有LIMIT row_count条款和无有条款:

如果多个列中具有相同值的ORDER BY列，服务器是免费的任何命令返回的行，并可以根据不同的整体执行计划。换句话说，这些行的排序顺序是不确定的关于nonordered柱。

其中一个因素，影响执行计划LIMIT，所以顺序查询有无LIMIT可以将不同的命令行。考虑这个查询，这是排序的多类别但不确定柱相对于id和评级专栏

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

包括LIMIT可能会影响在每个命令行多类别价值。例如，这是一个有效的查询结果：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

在每一种情况下，行进行排序的ORDER BY柱，这是由SQL标准要求。

如果它是保证同一排，重要的不LIMIT，包括额外的列顺序条款使阶确定性。例如，如果id价值观是独特的，你可以为一个给定的成行多类别值出现在id这样的顺序进行排序：

MySQL的>SELECT * FROM ratings ORDER BY category, id;---- ---------- -------- | ID |范畴|评级| ---- ---------- -------- | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | | 2 | 3 | 5 | | 7 | 3 | 2.7 | ---- ---------- -------- MySQL >SELECT * FROM ratings ORDER BY category, id LIMIT 5;---- ---------- -------- | ID |范畴|评级| ---- ---------- -------- | 1 | 1 | 4.5 | | 5 | 1 | 3.2 | | 3 | 2 | 3.7 | | 4 | 2 | 3.5 | | 6 | 2 | 3.5 | ---- ---------- --------

MySQL的功能标记内部确定的或不确定的。一个函数是不确定的如果，给出其参数固定的值，它可以返回不同的调用不同的结果。非确定性函数的例子：RAND()，UUID()

如果一个函数被标记具有不确定性，在一个对它的引用WHERE条款对每行（当选择一个表中的行）或组合（当选择从多个表的连接）。

MySQL也决定当评估基于类型的参数的功能，无论是参数表列或常量值。一个确定性的函数，以表列作为参数必须进行评估时，柱的变化值。

不确定性函数可能会影响查询性能。例如，一些优化可能不可用，或者可能需要更多的锁定。以下的讨论使用RAND()但适用于其他不确定性函数以及。

假设一个表t这个定义：

创建表（ID int不空主键，col_a varchar（100））；

考虑到这两个查询：

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

查询出现使用主键查找因为平等比较关键，但这是真的只是其中的第一：

不确定性的影响不仅限于SELECT声明.这UPDATE语句使用非确定性函数，选择要修改的行：

UPDATE t SET col_a =some_exprWHERE id = FLOOR(1 + RAND() * 49);

大概意图是更新在一个单一的行的主键相匹配的表达。然而，它可能会更新为零，一个，或多个行，取决于id列的值在值RAND()序列

在所描述的行为对性能和复制的影响：

困难在于RAND()函数求值一次表的每一行。避免多功能评估，使用这些技术：

如前所述，在一个不确定的表达WHERE条款可能防止优化和结果在表扫描。然而，它可能会部分优化哪里如果其他的表达是确定的条款。例如:

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

如果优化器可以使用partial_key为了减少行选择设定，RAND()被执行的次数减少，从而减少不确定性对优化的影响。

窗函数的影响策略的优化研究：

聚合函数不作为窗口的功能是聚集在最可能的查询。例如，在这个查询，MySQL看到COUNT(t1.b)是不存在于外部查询由于其位置哪里条款:

SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);

因此，MySQL的聚集体内部的子查询的处理t1.b作为一个常数和返回的行数T2

更换WHERE与有在一个错误的结果：

mysql> SELECT * FROM t1 HAVING t1.a = (SELECT COUNT(t1.b) FROM t2);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1
of SELECT list contains nonaggregated column 'test.t1.a'; this is
incompatible with sql_mode=only_full_group_by

错误发生的原因COUNT(t1.b)可以存在于有因此，外部查询汇总

窗函数（包括骨料作为窗口函数）没有前面的复杂性。他们总是聚集在子查询的地方都写，从不在外查询。

窗函数的评价可以通过值的影响windowing_use_high_precision系统变量，它决定着计算窗口操作而不损失精度。默认情况下，windowing_use_high_precision启用

对于一些移动框架的聚集体，逆聚合函数可以应用于从集合删除值。这可以提高性能，但可能会以损失精度。例如，添加一个非常小的浮点值的一个非常大的价值使很小的值是“隐藏“通过大量的价值。当反相大的价值后，对小值的效果了。

由于逆聚集的精度损失是浮点只有操作因素（近似值）的数据类型。对于其他类型，反聚集是安全的；这包括DECIMAL它允许一个小数部分，但一个确切的值类型。

执行速度更快，MySQL总是用反聚集时它是安全的：

对于方差的功能评价STDDEV_POP()，STDDEV_SAMP()，VAR_POP()，VAR_SAMP()，和它们的同义词，评价可以发生在优化模式或默认模式。优化模式可能会在最后一位有效数字产生稍微不同的结果。如果这种差异是允许的，windowing_use_high_precision可以禁用允许优化模式。

为EXPLAIN，窗口执行计划信息太大，传统的输出格式显示。看到窗口的信息，使用EXPLAIN FORMAT=JSON寻找开窗元素

行构造函数允许多个值同时比较。例如，这两个语句在语义上是等价的：

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

此外，优化器处理表达式相同的方式。

优化器是不太可能如果行构造函数列不包括索引的前缀使用索引。考虑下面的表格，其中有一个主键(c1, c2, c3)：

create table T1（int，int，C1，C2，C3，C4 int，char（100），primary key（C1，C2，C3））；

在这个查询的WHERE子句中索引使用的所有列。然而，行构造函数本身不包括索引，优化器使用前缀，只有结果C1（key_len=4，大小C1）：

mysql> EXPLAIN SELECT * FROM t1
       WHERE c1=1 AND (c2,c3) > (1,1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using where

在这种情况下，重写构造函数表达行采用等效nonconstructor表达可能会导致更完整的索引使用。对于给定的查询，该行构造函数和等效nonconstructor表达式：

(c2,c3) > (1,1)
c2 > 1 OR ((c2 = 1) AND (c3 > 1))

重写查询优化器使用所有三列在索引中使用nonconstructor表达结果（key_len=12）：

MySQL的>EXPLAIN SELECT * FROM t1WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G*************************** 1。行***************************编号：1 select_type：简单表：T1分区：null类型：rangepossible_keys：主键：主要key_len：12编号：零排：3：100：使用额外的过滤

因此，为了更好的效果，避免混行构造函数AND/OR表达.使用一个或另一个。

在一定的条件下，优化器可以适用范围的访问方法IN()有行构造函数的参数表达式。看到行构造函数的表达范围优化

从输出EXPLAIN演出ALL在类型柱时，MySQL的使用全表扫描解决一个查询。这通常发生在下列条件下：

小表，表扫描通常是适当的，对性能的影响可以忽略不计。对于大型的表，试试下面的方法来避免错误的优化器选择一个表扫描：

优化器使用半连接策略来提高查询的执行，如本节所述。

两表内连接，连接返回一行从一个表多次有在其他表中匹配。但对于一些问题，唯一一个重要的信息是否匹配，不匹配的数目。假设有个名为class和花名册单班在课程和班级花名册（登记每个班的学生，分别）。列出有学生就读的课程，你可以使用这个连接：

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

然而，结果列出每个类每一次招收的学生。被问的问题，这是不必要的重复信息。

假设class_num在一个主键类表，通过使用重复的抑制是可能的SELECT DISTINCT，但它是产生所有匹配行的第一只消除重复后无效。

同样的复制自由的结果可以通过使用子查询获得：

SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);

在这里，优化器可以承认IN条款要求的子查询的返回唯一的每一类数的实例花名册表在这种情况下，查询可以使用半连接；即返回的每一行中只有一个实例运行class这是由行匹配花名册

外部连接和内部连接语法是在外部查询规范允许，和表可参考基表，导出表、视图的引用，或公用表表达式。

在MySQL数据库中，查询必须满足这些标准作为半办理加入：

子查询可能相关或不相关。DISTINCT是允许的，如极限除非ORDER BY也用了

如果子查询满足以上要求，MySQL将其转换为半连接，使得基于成本的选择，这些策略：

这些策略可以启用或禁用使用以下optimizer_switch系统变量的旗帜：

这些旗子是默认启用。看到第3，“开关的优化”

优化器将处理意见分歧和派生表。这会影响查询使用STRAIGHT_JOIN改性剂和与进入子查询可以转换为半连接。下面的查询说明因为在处理变化导致的转型中的变化，因而不同的执行策略：

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

优化器首先查看和转换IN子查询的半连接，然后检查是否合并视图到外部查询。因为straight_join外部查询中的改性剂防止半连接，优化器不合并，导致派生表评价使用物化表。

EXPLAIN输出指示使用的半连接策略如下：

优化器使用物化以实现更高效的查询处理。物化加快查询执行生成查询结果作为一个临时表，通常在内存中。MySQL需要子查询结果的第一次，它体现了，结果到一个临时表。以后的任何时间的结果是必要的，MySQL又指临时表。优化器可能指数与哈希索引进行查找，快速和廉价的桌子。索引是唯一的，从而消除了重复，使表更小。

子查询物化使用内存中的临时表，如果可能的话，回落到磁盘上的存储如果表变得太大。看到第8.4.4，“MySQL”使用内部临时表

如果物化是不使用优化器有时改写相关子查询作为一个相关子查询。例如，下面的INsubquery是noncorrelated（where_condition只涉及列T2而不是t1）：

SELECT * FROM t1where T1，T2在（选择B从T2的地方。where_condition）；

优化器可能会改写这一EXISTS相关子查询：

SELECT * FROM t1where存在（选择B从T2，T2。where_conditionAND t1.a=t2.b);

使用临时表避免了这样的重写查询物化，使得它可以执行查询只有一次，而不是每行外查询一次。

子查询物化为可用于MySQL的optimizer_switch系统变量物化国旗必须启用。（见第3，“开关的优化”用。）materialization标志启用，实现适用于子查询谓词出现在选择列表中的任何地方（，哪里，ON，组，HAVING，或顺序），谓词，落入任何这些用例：

下面的示例说明如何为对等的要求UNKNOWN和错误的谓词评估是否可以使用子查询物化的影响。假设where_condition仅从涉及列T2而不是t1我知道that is the noncorrelated子查询。

这是受物化查询：

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

在这里，不管的IN谓词返回未知或FALSE。无论哪种方式，该行从T1不包括在查询结果

一个例子，查询物化是不是使用以下查询，在那里t2.b是一个空列：

SELECT * FROM t1where（T1，T1，T2（B）没有选择，B从T2，T2。where_condition）；

以下限制适用于使用子查询的实现：

使用EXPLAIN一个查询提供了一些指示优化器使用子查询是否实现。相比不使用物化查询执行，select_type可以改变DEPENDENT SUBQUERY到子查询。这表明，为子查询，将每行执行一次外，物化使子查询被执行一次。此外，扩展EXPLAIN输出，由下面显示的文本SHOW WARNINGS包括实现和materialized-subquery

优化器可以使用派生表的参考策略：

优化器使用相同的策略来处理查看参考和公用表表达式。

例1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

合并，查询执行类似：

SELECT * FROM t1;

例2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

合并，查询执行类似：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

与物化，derived_t1和derived_t2作为在各自的查询一个单独的表。

优化器处理源表和视图引用了相同的方式：尽可能避免不必要的物化条件，使压下从外部查询派生表和生产更高效的执行计划。（例如，见第8.2.2.2，“物化”优化子查询。）

如果合并会导致外部查询块引用61多个基表，优化器选择物化代替。

优化传播ORDER BY条款在派生表或视图外查询块参考如果这些条件都是真的：

否则，优化器ignores the theORDER BY条款.

以下可用的手段是否影响优化器尝试合并源表和视图引用到外查询块：

如果优化器选择物化策略而不是派生表的合并，它处理查询如下：

考虑以下EXPLAIN声明，为子查询中出现的从条款一SELECT查询：

解释查询（SELECT * FROM T1）作为derived_t1；

物化查询优化器可以通过延迟直至结果需要在SELECT执行.在这种情况下，查询不执行（因为它发生在一个EXPLAIN声明），因此不需要

即使是对执行的查询，查询物化延迟可能使优化器实现完全避免。当这一切发生的时候，查询的执行需要进行物化的时间更快。考虑下面的查询，将在子查询的结果FROM条款的另一个表：

SELECT *  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2          ON t1.f2=derived_t2.f1  WHERE t1.f1 > 0;

如果优化过程t1第一、哪里条款产生一个空的结果，加入一定是空的，不需要物化查询。

的情况下，派生表的要求具体化，优化器可以添加索引化表来加快访问它。如果这样的索引使ref访问表，可以大大减少查询执行过程中读取的数据量。考虑以下查询：

SELECT * FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2         ON t1.f1=derived_t2.f1;

优化器在列建立索引f1从derived_t2如果这样做会使使用ref以最低的成本获取执行计划。添加索引后，优化器可以把物化的派生表作为一个指标的常规表一样，它的好处同样从生成的索引。索引创建的开销相比，没有索引的查询执行成本可以忽略不计。如果ref访问会导致比其他接入方式成本较高，优化器创建没有索引和失去什么。

优化的跟踪输出，合并的源表或视图引用不显示为一个节点。只有底层表出现在顶部的查询计划。

为派生表化什么是同样适用于公用表表达式（CTE）。此外，以下专属于CTEs。

如果一个CTE的查询物化，它是物化的一次查询，即使查询引用了好几次。

递归CTE总是物化

如果一个CTE是实，优化器会自动添加相关的指标，如果估计的索引将加快对CTE的顶级声明访问。这是类似于派生表的自动标引，但如果CTE引用多次，优化器可能会创建多个索引，加快以最适当的方式由各参考访问。

这个MERGE和no_merge优化器提示可以应用CTEs。每个CTE参考在顶层声明可以有它自己的暗示，允许热膨胀系数参考被选择性合并或实。下面的语句使用的暗示表明cte1应合并cte2应物化：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

这个ALGORITHM条款CREATE VIEW不影响任何物化WITH前面的条款SELECT在视图的定义语句。考虑这一说法：

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

这个ALGORITHM价值实现的影响SELECT，不WITH条款.

CTEs，存储引擎用于磁盘内部临时表不能MyISAM。如果internal_tmp_disk_storage_engine=MYISAM一个错误发生时，任何试图实现一个CTE使用磁盘上的临时表。

如前所述，CTE，如果物化，物化一次，即使多次引用。表示一次性的物化，优化跟踪输出中包含一个发生creating_tmp_table加上一个或多个事件reusing_tmp_table

热膨胀系数与派生表的materialized_from_subquery结如下参考。这对于一个CTE是多次引用的是真实的，所以不会有重复materialized_from_subquery节点（这会给人的印象是，子查询被执行多次，并产生不必要的冗长的输出）。只有一个引用CTE拥有完整materialized_from_subquery随着它的查询计划的说明结。其他参考资料了materialized_from_subquery结。同样的想法适用于EXPLAIN输出传统格式：其他参考子查询不显示。

某些优化适用于比较使用IN（或=ANY）运算符测试子查询结果。本节讨论这些问题，特别是面临的挑战NULL价值观现状。讨论的最后一部分提出如何可以帮助优化器。

考虑下面的子查询比较：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL评估查询“从外到内“即，首先得到外部的表达式的值outer_expr，然后运行查询并捕捉它产生的行。

一个非常有用的优化是“通知“子查询，唯一感兴趣的是那些表达内在的行inner_expr等于outer_expr。这是被推下一个适当的平等为子查询的完成哪里条款更严格。转换比较看起来像这样：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

转换后，MySQL可以使用推下平等的限行必须要检查评估查询的数量。

更一般的，比较N子查询返回的值N值行是受同样的转换。如果oe_i和ie_i代表相应的外在和内在的表达值，该子查询比较：

（oe_1，…，oe_N在（选择）ie_1，…，ie_N从…哪里subquery_where）

成为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

为简单起见，以下的讨论假定一个单一的对外部和内部的表达式的值。

刚才描述的转换有其局限性。如果我们无视可能是有效的NULL价值观。那是的“下推“战略工程，只要这些条件都是真实的：

当这些条件或不持有，优化更为复杂。

假设outer_expr被称为是一个非—无效的价值但查询不产生排成一排，outer_expr=inner_expr。然后outer_expr（选择）评价如下：

在这种情况下，寻找行的方法outer_expr = inner_expr不再有效。要寻找这样的行，但如果没有被发现，也找行inner_expr是无效的。大致说来，子查询可以被转换成这样的：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

需要进行额外的IS NULL条件就是为什么MySQL的ref_or_null存取方法：

MySQL的>EXPLAINSELECT outer_expr IN (SELECT t2.maybe_null_keyFROM t2, t3 WHERE ...)FROM t1;*************************** 1。行*************************** ID：1 select_type：主表：T1…*************************** 2。行***************************编号：2 select_type：相关子查询表：T2型：ref_or_nullpossible_keys：maybe_null_key关键：maybe_null_key key_len：5号：功能：2行：使用额外使用指数…

这个unique_subquery和index_subquery子查询具体的接入方式也有“或NULL“变种

额外的OR ... IS NULL条件使查询执行更复杂的（和在子查询优化成为不适用），但通常这是可以容忍的。

情况更糟的时候outer_expr可以是无效的。根据SQL的解释NULL作为“未知的价值，“NULL IN (SELECT inner_expr ...)这样的评价：

适当的评价是必要的，它能够检查是否SELECT产生了所有的行，所以outer_expr=inner_expr不能下推到子查询。这是一个问题，因为许多现实世界的子查询变得很慢，除非平等可以推下去。

从本质上讲，必须有不同的方法来执行子查询取决于价值outer_expr

优化器选择SQL符合以上的速度，所以它占的可能性outer_expr可能是无效的：

为了解决是否下推到子查询条件的困境，条件是包裹在“触发“功能.因此，该形式的表达：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转换成：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

更一般地，如果子查询的比较是基于内部和外部表情几双，转换需要比较：

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

并将其转换为这种表达：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

每个trigcond(X)是一种特殊的函数值为下列值：

平等，是包裹在trigcond()功能并非第一类谓词的查询优化器。大多数优化不能谓词，可以打开和关闭的查询执行时间的交易，所以他们承担任何trigcond（X）是一个未知函数，忽略它。触发的等式可以通过这些优化的应用：

当优化器使用触发条件来创建基于访问索引查找一些种（如上述列表中的前两个项目），它必须对案件回退策略时关闭。此回退策略都是一样的：做一个全表扫描。进入EXPLAIN输出回退显示为对空键全扫描在Extra专栏

MySQL的>EXPLAIN SELECT t1.col1,t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G*************************** 1。行***************************编号：1 select_type：主表：2 *************************** T1。行***************************编号：2 select_type：相关子查询表：T2型：index_subquerypossible_keys：KEY1键：key1 key_len：5号：功能：2行：使用额外的地方；在空键全扫描

如果你运行EXPLAIN然后SHOW WARNINGS，你可以看到触发条件：

*************************** 1. row ***************************  Level: Note   Code: 1003Message: select `test`.`t1`.`col1` AS `col1`,         <in_optimizer>(`test`.`t1`.`col1`,         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2         on key1 checking NULL         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`         from `test`.`t1`

触发条件的使用对性能的影响。一NULL IN (SELECT ...)表达现在可能会导致全表扫描（这是慢）当它以前没有。这是正确的结果付出的代价（的触发条件，战略的目标是提高依从性，而不是速度）。

多表查询，执行NULL IN (SELECT ...)特别慢因为加入优化器不会的情况下，外在表现为优化无效的。它假定查询的评价NULL左边是非常罕见的，即使有统计表明，否则。另一方面，如果外表达可能无效的不过，没有性能损失

帮助查询优化器更好地执行你的查询，使用这些建议：

这个subquery_materialization_cost_based旗的optimizer_switch系统变量可以在子查询的物化和之间的选择控制进入-EXISTS辅助转换见第3，“开关的优化”