⚡explain 查询字段

列名	解释
id	The SELECT identifier:select 标识符,表示执行顺序
select_type	The SELECT type:查询类型
table	The table for the output row: 表名
partitions	The matching partitions:使用的哪些分区(对于非分区表值为null)
type	The join type: join 类型
possible_keys	The possible indexes to choose:可能用到的索引
key	The index actually chosen:实际选择的索引
key_length	The length of the chosen key:所选择索引的长度
ref	The columns compared to the index :显示索引的哪一列被使用了,可以是一个常数
rows	Estimate of rows to be examined:预计检查行数
filtered	Percentage of rows filtered by table condition:通过过滤条件之后对比总数的百分比
Extra	Additional information:其他信息,如 using file sort,using index,using index condition,using join buffer,using where

⚡id

The SELECT identifier. This is the sequential number of the SELECT within the query. The value can be NULL if the row refers to the union result of other rows. In this case, the table column shows a value like <unionM,N> to indicate that the row refers to the union of the rows with id values of M and N.

翻译:
id 是 SELECT 的标识符.它是查询中 SELECT 的顺序号

如果该行引用其他行的并集结果,则该值可以为 NULL,在这种情况下,table 字段会显示类似<unionM，N>的值，以表示该行引用 ID 值为 M 和 N 的行的并集。

也就是说,id 数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,还有一种为 null 的情况,是由于这一行会 union 其他行的结果(其对应 select_type 为 union result)

⚡select_type

select_type	解释
SIMPLE	Simple SELECT (not using UNION or subqueries): 简单查询,没有用到 union 或者子查询
PRIMARY	Outermost SELECT:最外层查询,即有 union 或者子查询的最外层查询,一个 SQL 整体语句有且仅有一个最外层查询
UNION	Second or later SELECT statement in a UNION: union 语句中,除了第一个表之外,第二和之后的查询都是 union
DEPENDENT	Second or later SELECT statement in a UNION, dependent on outer query: 与union一样，出现在union 或union all 语句中，但是这个查询要受到外部查询的影响
UNION RESULT	Result of a UNION: 包含 union 的结果集,在 union 和 union all 语句中,因为它不需要参与查询,所以id字段为null
SUBQUERY	First SELECT in subquery: 第一个出现的子查询. 即如果在 select 或者 where 里包含了子查询,那么子查询就是 subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query: 和 subquery 一样,是第一个出现的子查询,并且它依靠外部查询.这就是**(相关子查询)**
DERIVED	Derived table SELECT (subquery in FROM clause): 派生表,from 子句中出现的子查询
MATERIALIZED	Materialized subquery
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

DEPENDENT typically signifies the use of a correlated subquery: dependent 一般代表用到相关子查询

DEPENDENT SUBQUERY evaluation differs from UNCACHEABLE SUBQUERY evaluation. For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context. For UNCACHEABLE SUBQUERY, the subquery is re-evaluated for each row of the outer context.
DEPENDENT SUBQUERY 评估方式与 UNCACHEABLE SUBQUERY 不同.对于 DEPENDENT SUBQUERY,子查询仅针对其外部上下文中变量的每组不同值重新评估一次.对于UNCACHEABLE SUBQUERY,将为外部上下文的每一行重新评估子查询

The select_type value for non-SELECT statements displays the statement type for affected tables. For example, select_type is DELETE for DELETE statements
非 SELECT 语句的 select_type 值显示受影响表的语句类型.例如,对于 DELETE 语句,select_type 是 DELETE

⚡table

The name of the table to which the row of output refers. This can also be one of the following values:

<unionM,N> : The row refers to the union of the rows with id values of M and N.

: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.

: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 8.2.2.2, “Optimizing Subqueries with Materialization”.

即: 通常是所查询的表名,或者表的别名,或者一个为查询产生临时表(如派生表、子查询、union 集合)的标示符

⚡type

⚡system

The table has only one row (= system table). This is a special case of the const join type.

只有一行数据,const 的特殊情况

(如果是 Innodb，type 通常都是 all 或者 index)

⚡const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

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

const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values

当您将 PRIMARY KEY 或 UNIQUE 索引的所有部分与常量值进行比较时,将使用 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

One row is read from this table for each combination of rows from the previous tables.It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

对于先前表中的每行组合,从这一张表中读取一行.当 join 使用索引的所有部分并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引时,显示 eq_ref

eq_ref can be used for indexed columns that are compared using the = operator. The comparison value can be a constant or an expression that uses columns from tables that are read before this table.

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;

即: 唯一性索引扫描,对于每个来自于前面的表的记录,从该表中匹配到唯一一行,并且一般是等值匹配.驱动表只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为 not null.
也就是 t1 join t2,其关联条件都是主键或唯一索引,t1的一行,对应 t2 也只有一行(一般用到最左原则)

⚡ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

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

即: 非唯一性索引扫描,类似 eq_ref,只是关联条件只是普通索引,不是唯一或主键索引,t1 对应的 t2 会有多个匹配行

⚡ref_or_null

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries

这种 join 类型类似于 ref,但是 MySQL 需要额外搜索包含 NULL 值的行.此联接类型优化最常用于解决子查询

例如:

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

⚡index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used

此 join 类型表示使用了索引合并优化.在这种情况下,输出行中的键列包含使用的索引列表,而 key_len 包含使用的索引的最长键部分的列表

即: 使用同一张表的多个索引,将多个索引合并取交集或者并集,常见 and,or 的条件使用了不同的索引

⚡unique_subquery

This type replaces eq_ref for some IN subqueries of the following form

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

unique_subquery 只是一个索引查找函数,可以完全替换子查询以提高效率

⚡range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

使用索引选择行,仅检索给定范围内的行.输出行中的 key 指示使用哪个索引.key_len包含使用的最长的键部分。此类型的ref列为NULL。

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:

即: 只检索给定范围的行，使用一个索引来选择行,而且可以是范围常量比较

⚡index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

index 的类型除了扫描的是索引树之外和 ALL 差不多，而扫描索引树只在下面两种情况下发生

• If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

  如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra 列显示 using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。

• A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

  使用对索引的读取来执行全表扫描，是按照索引顺序查找数据行。using index不会出现在 Extra 列中。

即：如果是覆盖索引，那么在 extra 列中会显示 using index，而如果没有用到覆盖索引，那么就不会在 extra列中显示，查询方式为全索引扫描。

⚡fulltext

The join is performed using a FULLTEXT index.

用到全文索引时显示

要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

⚡ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

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

⚡possible_keys

可能用到的索引

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing. If so, create an appropriate index and check the query with EXPLAIN again

翻译: 如果此列为 NULL,则没有相关的索引.在这种情况下,你可以通过检查 WHERE 子句来检查它是否引用了某些适合索引的列,从而可以提高查询性能.如果是这样,请创建一个适当的索引,然后再次使用 EXPLAIN 检查查询

⚡keys

实际用到的索引

It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

可能会显示一个 possible_keys 中不存在的索引.如果没有一个 possible_keys 索引适合查找行,但是查询选择的所有列都是其他索引的列,则可能发生这种情况.也就是说,这个索引覆盖了选定的列,因此尽管不使用索引来确定要检索的行,但索引扫描比数据行扫描更有效(索引覆盖的情况?)

For InnoDB, a secondary index might cover the selected columns even if the query also selects the primary key because InnoDB stores the primary key value with each secondary index. If key is NULL, MySQL found no index to use for executing the query more efficiently.

对于 InnoDB,即使查询也选择了主键,辅助索引也可能覆盖选定的列,因为 InnoDB 将主键值与每个辅助索引一起存储

(可以用 force index,use index 或者 ignore index,选择:使用/建议/不使用索引)

⚡key_len

The key_len column indicates the length of the key that MySQL decided to use. The value of key_len enables you to determine how many parts of a multiple-part key MySQL actually uses. If the key column says NULL, the key_len column also says NULL.

key_len 指示 MySQL 决定使用的索引的长度.key_len 的值使您能够确定 MySQL 实际使用的联合索引的多少部分

⚡ref

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

ref 显示将哪些列或常量与 explain 中 key 列中列出的索引进行比较来从表中选择行

即显示关联的字段.如果使用常数等值查询,则显示 const,如果是多个表连接查询,则会显示关联表的字段

If the value is func, the value used is the result of some function. To see which function, use SHOW WARNINGS following EXPLAIN to see the extended EXPLAIN output. The function might actually be an operator such as an arithmetic operator.

如果该值为 func,则使用的值是某些函数的结果.要查看哪个函数,在 EXPLAIN 之后使用 SHOW WARNINGS 来查看扩展的 EXPLAIN 输出.该函数实际上可能是算术运算符之类的运算符

即如果是 func,说明条件判断时进行了数据类型转换或者使用了表达式或函数

⚡rows

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.
For InnoDB tables, this number is an estimate, and may not always be exact.

rows列指示MySQL认为执行查询必须检查的行数。
对于 InnoDB,此数字是估计值

⚡filtered

表示此查询条件所过滤的数据的百分比，将 rows 除以 filtered 可以估算出整个表数据行数。

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100 indicate increasing amounts of filtering. rows shows the estimated number of rows examined and rows × filtered shows the number of rows that will be joined with the following table. For example, if rows is 1000 and filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.

表示被条件过滤的行数的估计百分比.最大值为100,这表示未过滤行.值从100减小表示过滤量增加.rows 显示检查的估计行数，filtered 显示将与下表连接的行数.例如,如果 rows 是1000,filtered 是 50.00(50％),则与下表连接的行数是 1000×50％= 500。

⚡extra

This column contains additional information about how MySQL resolves the query

⚡附录

⚡extra 列的几种情况

1. using index:

   The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

   仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。

   For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

   对于具有用户定义的聚集索引的InnoDB表，即使 Extra 列中没有 using index，也可以使用索引。如果 type 列是 index 并且 key 列是PRIMARY，就是这种情况。

   即：在索引列上进行查找，使用到了索引覆盖；或者是 type 为 index 且 key 为 primary 的 InnoDB 表的情况。

   如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表面索引用来读取数据而非执行查找动作。

2. using index condition

   Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.

   通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息将用于延迟（“下推”）读取整个表行。

   即：索引下推，当取出记录的时候就进行过滤，过滤掉不必要的记录。

3. using filesort

   MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order.

   MySQL必须额外进行一遍，以按排序顺序检索行。通过根据联接类型遍历所有行并存储与WHERE子句匹配的所有行的排序键和指向该行的指针，可以完成排序。然后对键进行排序，并按排序顺序检索行。

   使用文件排序，常出现在 order by 或者 group by 语句中，且排序成分没有用到索引，此时需要在 内存中 进行排序，由于使用索引排序会好于文件排序，一般这种情况为了减小 CPU 资源消耗都可以考虑添加索引进行优化。

   出现条件：在 order by 或者在 group by 排序的过程中，order by 的字段不是索引字段，或者 select 查询字段存在不是索引字段，或者 select 查询字段都是索引字段，但是 order by 字段和 select 索引字段的顺序不一致，都会导致 fileSort

4. using join buffer (Block Nested Loop 或者 Batched Key Access 或者 hash join)

   Tables from earlier joins are read in portions into the join buffer, and then their rows are used from the buffer to perform the join with the current table. (Block Nested Loop) indicates use of the Block Nested-Loop algorithm, (Batched Key Access) indicates use of the Batched Key Access algorithm, and (hash join) indicates use of a hash join. That is, the keys from the table on the preceding line of the EXPLAIN output are buffered, and the matching rows are fetched in batches from the table represented by the line in which Using join buffer appears.

   来自较早联接的表被部分读取到 join 缓冲区中，然后从缓冲区中使用它们的行来执行与当前表的联接。使用 Blocked Nested Loop 或者 Batched Key Access 或 hash join 算法进行具体连接。即，将缓冲EXPLAIN输出的前一行中的表中的键，并从出现“使用连接缓冲区”的行所代表的表中批量提取匹配的行。

   Hash joins are available beginning with MySQL 8.0.18; the Block Nested-Loop algorithm is not used in MySQL 8.0.20 or later MySQL releases.

   从MySQL 8.0.18开始，可以使用哈希联接。MySQL 8.0.20或更高版本的MySQL中未使用“块嵌套循环”算法。

5. using temporary

   To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

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

6. using where

   MySQL 服务器会在存储引擎检索行后再根据 where 条件进行过滤。

   A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

   WHERE子句用于限制要与下一个表匹配或发送给客户端的行。除非你专门打算从表中获取或检查所有行，否则，如果 Extra 值不是 using where 并且表 type 是 ALL 或 index，则查询中可能存在一些问题。

   即：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。

   查询条件中分为限制条件和检查条件。5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra 列显示 using index condition

7. using MRR

   Tables are read using the Multi-Range Read optimization strategy.

   使用 Multi-Range Read 优化策略读取表。