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 的每个输出行均提供有关一个 table 的信息。每行包含一下列：

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”的说明，如下：

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”属性的值。

以下列表描述了连接类型，从最佳类型到最差类型：

1. system：

   该表只有一行【即，系统表】。这是“const”连接类型的特例。

2. const：

   该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。【const表非常快，因为它们只能读取一次】

   • 当您将“PRIMARY KEY”或“UNIQUE”索引的所有部分与常量值进行比较时，将使用const。【！】

   在以下查询中，tbl_name 可用作 const 表：

   SELECT * FROM tbl_name WHERE primary_key=1;
   
   SELECT * FROM tbl_name
     WHERE primary_key_part1=1 AND primary_key_part2=2;
   

3. eq_ref：【！】

   对于前一个表中的每一行组合，从该表中读取一行。

   • 这是除 system 和 const 类型以外最好的联接类型。
   • 当连接使用索引的所有部分且索引为“PRIMARY KEY”或“UNIQUE NOT NULL”索引时使用。
   • eq_ref 可用于使用“=”运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。

   在以下示例中，MySQL 可以使用 eq_ref 连接来处理 ref_table ：

   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;
   

4. ref：【！】

   对于前一个表中的行的每种组合，将从该表中读取具有匹配索引值的所有行。

   • 如果联接仅使用键的最左前缀，或者键不是“PRIMARY KEY”或“UNIQUE”索引【换句话说，如果联接无法基于键值选择单个行】，则使用 ref。
   • 如果使用的键仅匹配几行，则这是一种很好的联接类型。
   • ref可用于使用“=”或“<=>”运算符进行比较的索引列。

   在以下示例中，MySQL 可以使用 ref 连接来处理 ref_table ：

   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;
   

5. ref_or_null：【！】

   这种连接类型类似于 ref，但是 MySQL 会额外搜索包含 NULL 值的行。

   • 此联接类型优化最常用于解析子查询。

   在以下示例中，MySQL 可以使用 ref_or_null 连接来处理 ref_table ：

   SELECT * FROM ref_table
     WHERE key_column=expr OR key_column IS NULL;
   

6. fulltext：

   使用 FULLTEXT 索引执行连接。

7. index_merge：

   此联接类型指示使用索引合并优化。

   在这种情况下，输出行中的“key”列包含使用的索引列表，而“key_len”包含使用的索引的最长键部分的列表。

8. unique_subquery：

   此类型将 eq_ref 替换为以下形式的某些“IN”子查询：

   value IN (SELECT primary_key FROM single_table WHERE some_expr)
   

   • unique_subquery 只是索引查找函数，可以完全替换子查询以提高效率。【！！！】
9. index_subquery：

   此连接类型类似于 unique_subquery。它代替了“IN”子查询，但适用于以下形式的子查询中的非唯一索引：

   value IN (SELECT key_column FROM single_table WHERE some_expr)
   

10. range：【！】

    使用索引选择行，仅检索给定范围内的行。

    输出行中的“key”列指示使用哪个索引。 “key_len”列包含已使用的最长键部分。此类型的“ref”列是 NULL。

    • 使用“=”，“<>”，“>”，“>=”，“<”，“<=”，“IS NULL”，“<=>”，“BETWEEN”，“LIKE”或“IN()”运算符将键列与常量进行比较时，可以使用 range：

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

11. index：【！】

    index 联接类型与 ALL 相同，只是扫描了索引树。这发生两种方式：

    1. 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra 列显示“Using index”。仅索引扫描通常比 ALL 快，因为索引的大小通常小于表数据。
    2. 使用对索引的读取执行全表扫描，以按索引顺序查找数据行。Extra 列不显示“Uses index”。
    • 当查询仅使用属于单个索引一部分的列时，MySQL 可以使用此联接类型。
12. ALL：【！】

    对来自前一个表的行的每个组合进行全表扫描。

    • 如果该表是第一个未标记 const 的表，则通常不好，并且在所有其他情况下通常“非常”糟糕。
    • 通常，您可以通过添加索引来避免 ALL，这些索引允许基于早期表中的常量值或列值从表中检索行。

解释输出解释

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

示例：
假设您在此处显示了 SELECT 语句，并且打算使用 EXPLAIN 进行检查：

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;

对于此示例，假设：

1. 表的索引即被比较列的定义如下：

  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(主键)

1. tt.ActualPC 值分布不均。

最初，在执行任何优化之前，EXPLAIN 语句会产生以下信息：

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)

因为每个表的 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 个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)。再次执行 EXPLAIN 语句将产生以下结果：

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

这并不完美，但是更好：rows值的乘积少了 74 倍。此版本在几秒钟内执行。

可以进行第二种更改以消除“tt.AssignedPC = et_1.EMPLOYID”和“tt.ClientID = do.CUSTNMBR”比较的列长度不匹配：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

修改之后，EXPLAIN 产生如下所示的输出：

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

在这一点上，查询尽可能地被优化。剩下的问题是，默认情况下，MySQL 假定 tt.ActualPC 列中的值是均匀分布的，而 tt 表不是这种情况。幸运的是，很容易告诉 MySQL 分析密钥分布：

mysql> ANALYZE TABLE tt;

使用其他索引信息，联接是完美的，并且 EXPLAIN 产生以下结果：

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

EXPLAIN 输出中的rows列是来自 MySQL 联接优化器的有根据的猜测。通过将 rows 乘积与查询返回的实际行数进行比较，检查数字是否接近真实值。

如果数字完全不同，则可以通过在“SELECT”语句中使用“STRAIGHT_JOIN”并尝试在“FROM”子句中以不同顺序列出表来获得更好的性能。（但是 STRAIGHT_JOIN 可能会禁止使用索引，因为它禁用了半联接转换）

• 在某些情况下，当“EXPLAIN SELECT”与子查询一起使用时，可能会执行修改数据的语句。