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”语句。