• 查询计划
• 查询计划输出格式
  • 解释输出列
    • 列格式
      • id
      • select_type
      • type
      • possible_keys
      • key
      • ref

查询计划

根据表、列、索引的详细信息以及子句中的 WHERE 条件不同，MySQL 优化器会考虑多种技术来有效地执行 SQL 查询中涉及的查找。

例如，

• 对大表进行查询可能无需读取所有行；

• 涉及多个表的联接查询可能不需要比较每个行组合。

优化器选择的执行最高效查询的操作集称为“查询执行计划”（query execution plan），也称为 EXPLAIN 计划。

通常情况下，EXPLAIN 返回的查询计划是被较好优化的执行计划，我们可以通过改进SQL 语句或者索引技术，来优化慢查询。

EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息，EXPLAIN 语句适用于 SELECT、 DELETE、 INSERT、 REPLACE 和 UPDATE语句。

查询计划输出格式

EXPLAIN 返回语句中使用的每个表的一行信息 SELECT。它按照 MySQL 在处理语句时读取表的顺序列出了输出中的表。这意味着MySQL从第一个表中读取一行，然后在第二个表中找到匹配的行，然后在第三个表中找到匹配的行，依此类推。当所有表都处理完毕后，MySQL 输出选定的列并回溯表列表，直到找到有更多匹配行的表。从此表中读取下一行，并继续处理下一个表。

解释输出列

EXPLAIN 语句输出列的格式：

列格式

id

标识符SELECT。SELECT这是查询中的序列号 。NULL如果该行引用其他行的并集结果，则该值可以是。在本例中，该 table列显示一个值 ，表示该行引用值为 和 的行的并集。 <unionM,N>idMN

select_type

SELECT 的类型，可以是下表中显示的任何类型。JSON 格式将类型EXPLAIN公开 SELECT为 a 的属性 query_block，除非它是 SIMPLE或PRIMARY。表中还显示了 JSON 名称（如果适用）。

type

EXPLAIN 输出的类型列描述了表的连接方式。下面的列表描述了连接类型，从最好的类型到最差的类型排序：

• system

  该表只有一行（通常是系统表），这是 const 连接类型的特例。。

• const

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

  当我们使用 PRIMARY KEY 或 UNIQUE 索引与常量值进行比较时，MySQL 会使用 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;
  

• eq_ref

  对于前一个表中的行的每个组合，都会从此表中读取一行。除了 system 和 const 类型之外，这是最好的连接类型。

  当连接使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，会使用 eq_ref。

  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;
  

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

• fulltext

  连接是使用 FULLTEXT 索引执行的。

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

• index_merge

  该连接类型表明使用了索引合并优化。在这种情况下，输出行中的 key 列会包含所使用的索引的列表，并且 key_len 包含所使用的索引的最长键部分的列表。

• unique_subquery

  此类型会替换以下形式的某些 IN 子查询的 eq_ref：

  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  

  unique_subquery 只是一个索引查找功能，完全替代了子查询，以获得更好的效率。

• index_subquery

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

  value IN (SELECT key_column FROM single_table WHERE some_expr)
  

• range

  使用索引来选择行，并且仅检索给定范围内的行。输出行中的 key 列会提示使用了哪个索引，key_len 列包含了所使用的最长的 key 部分。对于这种类型，ref 列为 NULL。

  当索引列与常量进行比较时，如果使用了 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN() 这些运算符，则 MySQL 会使用 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);
  

• index

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

  • 如果索引是查询的覆盖索引，并且可以用来满足表中所需的所有数据，则仅扫描索引树。

    这种情况下，Extra 列会显示 Using index，只进行索引扫描通常比 ALL 更快，因为索引的大小通常小于表数据。

  • 全表扫描是通过读取索引来按索引顺序查找数据行来执行的。 Uses index 不会出现在 Extra 列中。

  当查询仅使用属于单个索引的列时，MySQL 会使用 index 联接类型。

• ALL

  对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记为 const 的表，这通常不好，并且在所有其他情况下通常非常糟糕。

  通常，我们可以通过添加索引来避免 ALL，这些索引允许根据早期表中的常量值或列值从表中检索行。

possible_keys

该possible_keys列指示 MySQL 可以选择从中查找该表中的行的索引。请注意，此列完全独立于 的输出中显示的表顺序 EXPLAIN。这意味着在实践中，某些键possible_keys可能无法用于生成的表顺序。

如果此列是NULL（或在 JSON 格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查子句来WHERE 检查它是否引用某些适合建立索引的列，从而提高查询的性能。如果是这样，请创建适当的索引并再次检查查询 EXPLAIN。

key

该key列表示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用其中一个possible_keys 索引来查找行，则该索引将被列为键值。

可能key会指定值中不存在的索引 possible_keys。如果没有possible_keys索引适合查找行，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。

对于InnoDB，即使查询还选择主键，辅助索引也可能覆盖所选列，因为InnoDB每个辅助索引都存储主键值。如果 key是NULL，MySQL 找不到可用于更有效地执行查询的索引。

要强制 MySQL 使用或忽略列中列出的索引 possible_keys，请 在查询中使用FORCE INDEX、USE INDEX或。

ref

该ref列显示将哪些列或常量与列中指定的索引进行比较 key以从表中选择行。如果值为func，则使用的值是某个函数的结果。

参考：

• mysql explain type连接类型示例

• MySQL：连Explain的Type类型都没搞清楚，怎敢说精通SQL优化？