sql性能分析之EXPLAIN

EXPLAIN提供了有关如何执行SQL语句的信息，用于select，delete，insert，update，replace等语句中。在select语句中使用EXPLAIN将返回一行信息（每个表对应一行，包含临时表），按照处理语句时的顺序显示。存在表连接时，使用循环嵌套的方式解析所有连接，也就是说从第一个表中读取一行然后在第二个表中匹配所有符合条件的行，然后再在第三个表……。当所有的表处理完后，输出选中的列并且返回表清单直到找到一个有更多匹配行的表。从该表读入一行并继续处理下一个表。EXPLAIN的输出包含分区信息，对于select语句和DESCRIBE相似（通常情况下DESCRIBE用于获取表结构的信息，而EXPLAIN用于语句的执行计划，即解释如何执行查询）。

EXPLAIN输出列

每行输出代表一个表的相关信息，包含以下字段（JSON 名称为使用FORMAT=JSON时的key）

• id
  SELECT识别符，SELECT的查询序列号。
• select_type
  select类型可以是以下值：

注：在查询缓存中子查询的缓存行和查询结果的缓存不同，子查询缓存发生在查询期间，而查询缓存在查询完成后才进行存储结果。具体查询如何缓存，请参照：https://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html

• table
  引用表的名称。如果使用别名，这里将显示别名。也可以是以下几种情况：
  <unionM,N>，<derivedN>，subqueryN>。M和N为返回行的id

• partitions
  记录由查询匹配的分区。该值为NULL表示用于非分区表。（关于表分区可用show create table查看）

• type
  连接类型。依次从好到差：

• system
  表中只有一行（系统表）。是const连接类型的特殊情况 。

• const
  使用唯一索引或者主键，返回记录一定只有一行，通常是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
  对于每个来自于前表的行组合，从该表中读取一行。它使用一个索引连接并且索引是UNIQUE或PRIMARY KEY，这可能是除了system和const类型外最好的连接类型。eq_ref可以是使用 = 操作符比较索引列，比较值可以是常量或一个列的表达式
  在下面的查询中，使用eq_ref连接进行处理
  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
  对于每个来自于前表的行组合，读取所有匹配索引值的行。若索引不是UNIQUE或PRIMARY KEY（也就是说连接不能返回单行的话）则类型是ref。如果匹配少量的行，该连接类型还是不错的。
  在下面的查询中，使用ref连接进行处理
  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
  全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，优先选择使用全文索引。

• ref_or_null
  该联接类型同ref，只是添加了专门包含NULL值的行。
  在下面的查询中，使用ref_or_null联接来处理ref_tables：
  SELECT * FROM ref_table WHERE key_column = expr OR key_column IS NULL

• index_merge
  使用两个及以上的索引合并查询，常用or或and
  在下面的查询中，使用ref_or_null联接来处理ref_tables：
  SELECT * FROM ref_table WHERE key_column1 = expr1 and key_column2 = expr2

• unique_subquery
  这个类型使用in的子查询替换eq_ref
  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高

• index_subquery
  该联接类型类似于unique_subquery。适用于子查询包含非唯一索引的情况。
  value IN (SELECT key_column FROM single_table WHERE some_expr)

• range
  索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

• index
  该连接类型类似于ALL，除了索引树被扫描。通常有两种方式：
  1：如果索引是用于满足表中所需的所有数据时的覆盖索引，则仅扫描索引树。这种情况下，Extra中包含 Using index。因为索 引比表数据更小，所以这种情况通常比All更快。
  2：使用索引读取执行全表扫描，按索引顺序查找数据行。Extra中不包含 Using index。
  当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

• ALL
  全表扫描然后过滤，通常性能很差。

• possible_keys
  表示可以选择哪些索引来查找此表中的行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些适合使用索引的列来提高你的查询性能。这样，创建一个合适的索引并且再次用EXPLAIN检查查询

• key
  实际使用的索引，如果没有选择索引则为NULL

• key_len
  索引长度。如果索引是NULL，则长度为NULL

• ref
  ref列显示使用哪个列或常数与索引匹配行。

• rows
  执行查询时必须检查的行数。

• filtered
  该filtered列指示将由表条件过滤的表行的估计百分比。也就是说，rows 显示估计的行数， rows× filtered/ 100显示与先前表相连接的行数。

• Extra
  包含有关如何解析查询的其他信息。参照https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_extra