查看“MySQL 优化：了解查询执行计划”的源代码

[[category:MySQL]]

== 关于 ==
根据 表，列，索引的详细信息以及“WHERE”子句中的条件，MySQL 优化器考虑了许多技术来有效执行 SQL 查询中涉及的查找。无需读取所有行即可执行对巨大表的查询；可以在不比较行的每个组合的情况下执行涉及多个表的联接。'''优化器选择执行最有效查询的一组操作'''称为“'''查询执行计划'''”，也称为'''EXPLAIN'''计划。


您的目标是认识到“EXPLAIN”计划的各个方面，这些方面表明查询已得到优化，并且如果发现操作效率低下，则需要学习 SQL '''语法'''和'''索引'''技术来改进计划。

== 使用 EXPLAIN 优化查询 ==
EXPLAIN 语句提供有关 MySQL 如何执行语句的信息：
* EXPLAIN 与 '''SELECT'''，'''DELETE'''，'''INSERT'''，'''REPLACE''' 和 '''UPDATE''' 语句一起使用。
* 当 EXPLAIN 与可解释的语句一起使用时，MySQL 将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。
* 当 EXPLAIN 与“FOR CONNECTION connection_id”而不是可解释的语句一起使用时，它将显示在命名连接执行的语句的执行计划。【？】
* 对于 SELECT 语句，EXPLAIN 生成可以使用“'''SHOW WARNINGS'''”显示的其他执行计划信息。
* EXPLAIN 对于检查涉及'''分区表'''的查询很有用。【？】
* “'''FORMAT'''”选项可用于选择输出格式。“TRADITIONAL”以表格格式显示输出（如果不提供 FORMAT 选项，则为默认设置）。 JSON 格式以 JSON 格式显示信息。


借助 EXPLAIN，您可以看到应该向表中添加索引的位置，以便通过使用索引查找行来使语句更快地执行。您还可以使用 EXPLAIN 来检查优化器是否以最佳顺序连接表。为了提示优化器使用与 SELECT 语句中表的命名顺序相对应的连接顺序，请以“'''SELECT STRAIGHT_JOIN'''”而不是“SELECT”开头该语句。但是，'''“STRAIGHT_JOIN”可能会禁止使用索引，因为它禁用了半联接转换'''。

优化器跟踪有时可能提供与 EXPLAIN 互补的信息。但是，优化程序的跟踪格式和内容在版本之间可能会发生变化。

如果您在认为应该使用索引时遇到问题，请运行“ANALYZE TABLE”以更新表统计信息（例如，键的基数），这可能会影响优化器的选择。


EXPLAIN 也可用于获取有关表中列的信息。 “EXPLAIN tbl_name”与“DESCRIBE tbl_name”和“SHOW COLUMNS FROM tbl_name”同义。【！！！】

== EXPLAIN 输出格式 ==
EXPLAIN 为 SELECT 语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取它们的顺序列出了输出中的表。 MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL 通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。
* EXPLAIN 语句提供有关 MySQL 如何执行语句的信息。 EXPLAIN 与“SELECT”，“DELETE”，“INSERT”，“REPLACE”和“UPDATE”语句一起使用。
* EXPLAIN 输出包括分区信息。同样，对于 SELECT 语句，EXPLAIN 生成扩展信息，该信息可以在 EXPLAIN 之后用“SHOW WARNINGS”显示。
** 在较早的 MySQL 版本中，分区和扩展信息是使用“EXPLAIN PARTITIONS”和“EXPLAIN EXTENDED”生成的。
* '''MySQL Workbench''' 具有视觉解释功能，该视觉解释提供了 EXPLAIN 输出的视觉表示。【！还有这好玩意？】

=== EXPLAIN 输出列 ===
EXPLAIN 的每个输出行均提供有关一个 table 的信息。每行包含一下列：
{| class="wikitable"
|+ EXPLAIN 输出列
|-
! Column 
! JSON Name 
! Meaning
|-
| id || select_id || SELECT 标识符
|-
| select_type || None || SELECT 类型
|-
| table || table_name || 输出行表
|-
| partitions || partitions || 匹配的分区
|-
| '''type''' || access_type || 联接类型
|-
| possible_keys || possible_keys || 可能的索引选择
|-
| '''key''' || key || 实际选择的索引
|-
| key_len || key_length || 所选键的长度
|-
| '''ref''' || ref || 与索引比较的列
|-
| rows || rows || 估计要检查的行
|-
| '''filtered''' || filtered || 按表条件过滤的行百分比
|-
| '''Extra''' || None || 附加信息
|}
* 上表中“JSON Name”：使用“FORMAT=JSON”时在输出中显示的等效属性名称。【NULL 的 JSON 属性不会显示在 JSON 格式的 EXPLAIN 输出中】

=== 查询类型（“select_type”） ===
其中“select_type”的说明，如下：
{| class="wikitable"
! select_type值 !! JSON Name !! Meaning
|-
| SIMPLE || None || 简单的“SELECT”（不使用“UNION”或 子查询）
|-
| PRIMARY || None || 最外层的“SELECT”
|-
| UNION || None || “UNION”中的第二个或之后的“SELECT”语句
|-
| DEPENDENT UNION || dependent ( true ) || “UNION”中的第二个或之后的“SELECT”语句，具体取决于外部查询
|-
| UNION RESULT || union_result || “UNION”的结果
|-
| SUBQUERY || None || 子查询中的第一个“SELECT”
|-
| DEPENDENT SUBQUERY || dependent ( true ) || 子查询中的第一个“SELECT”，取决于外部查询
|-
| DERIVED || None || 派生表
|-
| MATERIALIZED || materialized_from_subquery || 实体化子查询
|-
| UNCACHEABLE SUBQUERY || cacheable ( false ) || 不可缓存子查询：其结果无法缓存，必须针对外部查询的每一行重新进行评估
|-
| UNCACHEABLE UNION || cacheable ( false ) || 不可缓存子查询的“UNION”中的第二个或之后的“SELECT”
|}
* 非“SELECT”语句的“select_type”值显示受影响表的语句类型。例如，对于“DELETE”条语句的“select_type”是“DELETE”。

=== 说明联接类型（“type”） ===
EXPLAIN 输出的“type”列描述如何联接表。
* 在 JSON 格式的输出中，这些是“access_type”属性的值。


以下列表描述了连接类型，从最佳类型到最差类型：
# '''system'''：
#: 该表只有一行【即，系统表】。这是“const”连接类型的特例。
# '''const'''：
#: 该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。【const表非常快，因为它们只能读取一次】
#* 当您将“'''PRIMARY KEY'''”或“'''UNIQUE'''”索引的所有部分与常量值进行比较时，将使用const。【！】
#: 在以下查询中，tbl_name 可用作 const 表：
#:<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;
</syntaxhighlight>
# '''eq_ref'''：【！】
#: 对于前一个表中的每一行组合，从该表中读取一行。
#* 这是除 system 和 const 类型以外最好的联接类型。
#* 当连接使用索引的所有部分且索引为“'''PRIMARY KEY'''”或“'''UNIQUE NOT NULL'''”索引时使用。
#* eq_ref 可用于使用“'''='''”运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。
#: 在以下示例中，MySQL 可以使用 eq_ref 连接来处理 ref_table ：
#:<syntaxhighlight lang="mysql">
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
</syntaxhighlight>
# '''ref'''：【！】
#: 对于前一个表中的行的每种组合，将从该表中读取具有匹配索引值的所有行。
#* 如果联接仅使用键的最左前缀，或者键不是“PRIMARY KEY”或“UNIQUE”索引【换句话说，如果联接无法基于键值选择单个行】，则使用 ref。
#* 如果使用的键'''仅匹配几行'''，则这是一种很好的联接类型。
#* ref可用于使用“'''='''”或“'''<=>'''”运算符进行比较的索引列。
#: 在以下示例中，MySQL 可以使用 ref 连接来处理 ref_table ：
#:<syntaxhighlight lang="mysql">
SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;
</syntaxhighlight>
# '''ref_or_null'''：【！】
#: 这种连接类型类似于 ref，但是 MySQL 会'''额外搜索包含 NULL 值的行'''。
#* 此联接类型优化最常用于'''解析子查询'''。
#: 在以下示例中，MySQL 可以使用 ref_or_null 连接来处理 ref_table ：
#:<syntaxhighlight lang="mysql">
SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;
</syntaxhighlight>
# '''fulltext'''：
#: 使用 FULLTEXT 索引执行连接。
# '''index_merge'''：
#: 此联接类型指示使用'''索引合并优化'''。
#: 在这种情况下，输出行中的“key”列包含使用的索引列表，而“key_len”包含使用的索引的最长键部分的列表。
# '''unique_subquery'''：
#: 此类型将 eq_ref 替换为以下形式的某些“IN”子查询：
#:<syntaxhighlight lang="mysql">
value IN (SELECT primary_key FROM single_table WHERE some_expr)
</syntaxhighlight>
#* unique_subquery 只是'''索引查找函数'''，可以完全替换子查询以提高效率。【！！！】
# '''index_subquery'''：
#: 此连接类型类似于 unique_subquery。它代替了“IN”子查询，但适用于以下形式的子查询中的'''非唯一'''索引：
#:<syntaxhighlight lang="mysql">
value IN (SELECT key_column FROM single_table WHERE some_expr)
</syntaxhighlight>
# '''range'''：【！】
#: 使用'''索引'''选择行，仅检索'''给定范围'''内的行。
#: 输出行中的“key”列指示使用哪个索引。 “key_len”列包含已使用的最长键部分。此类型的“ref”列是 NULL。
#* 使用“'''='''”，“'''<>'''”，“'''>'''”，“'''>='''”，“'''<'''”，“'''<='''”，“'''IS NULL'''”，“'''<=>'''”，“'''BETWEEN'''”，“'''LIKE'''”或“'''IN()'''”运算符将键列与常量进行比较时，可以使用 range：
#:<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
</syntaxhighlight>
# '''index'''：【！】
#: index 联接类型与 ALL 相同，只是'''扫描了索引树'''。这发生两种方式：
## 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra 列显示“'''Using index'''”。仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据。
## 使用对索引的读取执行全表扫描，以按索引顺序查找数据行。Extra 列不显示“Uses index”。
#* 当查询仅使用'''属于单个索引一部分的列'''时，MySQL 可以使用此联接类型。
# '''ALL'''：【！】
#: 对来自前一个表的行的每个组合进行'''全表扫描'''。
#* 如果该表是第一个未标记 const 的表，则通常不好，并且在所有其他情况下通常“非常”糟糕。
#* 通常，您可以通过'''添加索引来避免 ALL'''，这些索引允许基于早期表中的常量值或列值从表中检索行。

=== 说明额外信息（“Extra”） ===

=== 理解：EXPLAIN 输出（“rows”） ===
通过获取 EXPLAIN 输出的“'''rows'''”列中值的乘积，可以很好地表明联接的良好程度。这应该大致告诉您 MySQL 必须检查多少行才能执行查询。如果使用“max_join_size”系统变量限制查询，则此行乘积还用于确定要执行和终止哪些多表“SELECT”语句。


'''示例：'''<br/>
假设您在此处显示了 SELECT 语句，并且打算使用 EXPLAIN 进行检查：
<syntaxhighlight lang="mysql">
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;
</syntaxhighlight>
对于此示例，假设：
# 表的索引即被比较列的定义如下：
<syntaxhighlight lang="mysql">
  Table    Column    Data Type 
---------------------------------------
   tt	  ActualPC  	CHAR(10)
   tt	  AssignedPC  	CHAR(10)
   tt	  ClientID  	CHAR(10)
   et	  EMPLOYID  	CHAR(15)
   do	  CUSTNMBR  	CHAR(15)

  Table    Index 
---------------------------------------
   tt	  ActualPC
   tt	  AssignedPC
   tt	  ClientID
   et	  EMPLOYID(主键)
   do	  CUSTNMBR(主键)
</syntaxhighlight>
# tt.ActualPC 值分布不均。
最初，在执行任何优化之前，EXPLAIN 语句会产生以下信息：
<syntaxhighlight lang="mysql">
table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)
</syntaxhighlight>
: 因为每个表的 type 是 ALL，所以此输出表明 MySQL 正在为所有表生成笛卡尔乘积。也就是说，每行的组合。这需要相当长的时间，因为必须检查每个表中的行数的乘积。对于手头的情况，此乘积为“74×2135×74×3872 = 45,268,558,720”行。如果表更大，时间将会更长。
这里的一个问题是，如果将索引声明为相同的类型和大小，MySQL 可以更有效地在列上使用索引。在这种情况下，'''如果“VARCHAR”和“CHAR”声明为相同大小，则认为它们相同'''。 tt.ActualPC 被声明为 CHAR(10)，而 et.EMPLOYID 被声明为 CHAR(15)，因此长度不匹配。
: 若要解决此列长度之间的差异，请使用“ALTER TABLE”将 ActualPC 从 10 个字符延长到 15 个字符：
: <syntaxhighlight lang="mysql">
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
</syntaxhighlight>
现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)。再次执行 EXPLAIN 语句将产生以下结果：
<syntaxhighlight lang="mysql">
table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1
</syntaxhighlight>
: 这并不完美，但是更好：rows值的乘积少了 74 倍。此版本在几秒钟内执行。

可以进行第二种更改以消除“tt.AssignedPC = et_1.EMPLOYID”和“tt.ClientID = do.CUSTNMBR”比较的列长度不匹配：
:<syntaxhighlight lang="mysql">
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);
</syntaxhighlight>
修改之后，EXPLAIN 产生如下所示的输出：
<syntaxhighlight lang="mysql">
table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1
</syntaxhighlight>

在这一点上，查询尽可能地被优化。剩下的问题是，默认情况下，MySQL 假定 tt.ActualPC 列中的值是均匀分布的，而 tt 表不是这种情况。幸运的是，很容易告诉 MySQL 分析密钥分布：
:<syntaxhighlight lang="mysql">
mysql> ANALYZE TABLE tt;
</syntaxhighlight>
使用其他索引信息，联接是完美的，并且 EXPLAIN 产生以下结果：
<syntaxhighlight lang="mysql">
table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1
</syntaxhighlight>


EXPLAIN 输出中的'''rows'''列是来自 MySQL 联接优化器的有根据的猜测。通过将 rows 乘积与查询返回的实际行数进行比较，检查数字是否接近真实值。
: 如果数字完全不同，则可以通过在“SELECT”语句中使用“STRAIGHT_JOIN”并尝试在“FROM”子句中以不同顺序列出表来获得更好的性能。（但是 STRAIGHT_JOIN 可能会禁止使用索引，因为它禁用了半联接转换）
* 在某些情况下，当“EXPLAIN SELECT”与子查询一起使用时，可能会执行修改数据的语句。

== 扩展 EXPLAIN 输出格式 ==





== 获取命名连接的执行计划信息 ==






== 估算查询性能 ==