首页 > 编程语言 > 详解 MySQL 执行计划
2020
12-24

详解 MySQL 执行计划

EXPLAIN语句提供有关MySQL如何执行语句的信息。EXPLAIN与SELECT，DELETE，INSERT，REPLACE和UPDATE语句一起使用。

EXPLAIN为SELECT语句中使用的每个表返回一行信息。它按照MySQL在处理语句时读取它们的顺序列出了输出中的表。 MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL输出所选列，并通过表列表回溯，直到找到一个表，其中有更多匹配的行。从这个表中读取下一行，然后继续处理下一个表。

1.  EXPLAIN 输出列

说下几个关键的列：

  • type ：连接类型
  • possible_keys ：可选的索引
  • key ：实际执行时使用的索引
  • ref ：ref列显示将哪些列或常量与前面key列中显示的命名的索引进行比较以从表中选择行
  • rows ：rows列表示MySQL认为执行查询必须检查的行数

2.  连接类型

连接类型，顺序从最好到最差，依次是： 

system

表只有一行。这是const join类型的特例。

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;

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;

ref

对于前表中的行的每种组合，将从该表中读取具有匹配索引值的所有行。如果联接仅使用key的最左前缀，或者如果key不是PRIMARY KEY或UNIQUE索引（换句话说，如果联接无法基于key值选择单个行），则使用ref。如果使用的key仅匹配几行，则这是一种很好的联接类型。

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包含所使用索引的最长key部分列表。

unique_subquery

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

value IN (SELECT primary_key FROM single_table WHERE some_expr)

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()运算符将key列与常量进行比较时，可以使用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是一样的，区别在于index连接类型扫描的时候索引树。通常，只发生在以下两种情况：

  • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，“Extra”列显示“Using index”。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
  • 使用从索引读取数据以按索引顺序查找数据行来执行全表扫描。“Uses index”不会出现在Extra列中。

ALL

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

3.  Extra列

关于Extra列的输出，只说几个常见的：

Using filesort

MySQL必须做一次额外操作，以找出如何按排序顺序检索行。排序是通过根据联接类型遍历所有行并存储与WHERE子句匹配的所有行的排序key和指向该行的指针来完成的。然后对key进行排序，并按排序顺序检索行。

Using index

仅使用索引树中的信息从表中检索列信息，而不需要执行额外的查找来读取实际行。当查询只使用属于单个索引的列时，可以使用此策略。

Using temporary

为了解析查询，MySQL需要创建一个临时表来保存结果。通常，如果查询包含以不同方式展示列的GROUP BY和ORDER BY子句，则会发生这种情况。

Using where 

WHERE子句用于限制哪些行匹配下一个表或发送给客户端。除非你打算从表中获取或检查所有行，否则如果额外的值没有使用where，并且表连接类型是all或index，则查询中可能出现错误。 

4.  优化ORDER BY

在某些情况下，MySQL可能会使用一个索引来满足ORDER BY子句，从而避免执行filesort操作所涉及的额外排序。

假设在(key_part1, key_part2)上有一个索引，下面的查询可以使用索引来解析ORDER BY部分。优化器是否真的这样做，取决于如果还必须读取索引之外的时，读取索引是否比表扫描更有效。

SELECT * FROM t1 ORDER BY key_part1, key_part2;

上面的语句，查询使用SELECT *，这可能会选择比key_part1和key_part2更多的列。在这种情况下，扫描整个索引并查找表行以查找索引中未包含的列可能比扫描表并排序结果要昂贵。如果是这样，则优化器不太可能使用索引。如果SELECT *仅选择索引列，则使用索引并避免排序。

下面这个查询中，key_part1是常量，因此通过索引访问的所有行都按key_part2顺序排列，并且如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则在（key_part1，key_part2）上的索引可以避免排序：

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2; 

以上就是详解 MySQL 执行计划的详细内容，更多关于MySQL 执行计划的资料请关注自学编程网其它相关文章！

编程技巧