EXPLAIN

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

EXPLAIN Output Columns

EXPLAIN 解释

id

列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。

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.

select_type

The type of SELECT, which can be any of those shown in the following table. A JSON-formatted EXPLAIN exposes the SELECT type as a property of a query_block, unless it is SIMPLE or PRIMARY. The JSON names (where applicable) are also shown in the table.

table (JSON name: table_name)

table：显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，

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.
• <derivedN>: 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 - 如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生
• <subqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N. See

partitions

The partitions from which records would be matched by the query. The value is NULL for nonpartitioned tables.

type - EXPLAIN Join Types

The type column of EXPLAIN output describes how tables are joined. In JSON-formatted output, these are found as values of the access_type property. The following list describes the join types, ordered from the best type to the worst:

type：依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了 ALL 之外，其他的type都可以使用到索引，除了 index_merge 之外，其他的type只可以用到了一个索引。

system

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

表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index。

const - 使用唯一索引（unique index）或者主键索引（primary index）

使用唯一索引（unique index）或者主键索引（primary index）。当执行返回的执行结果最多只包含1行记录时，通常type是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 is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:

Example

Table Schema

mysql> show create table task2.core_user;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| core_user | CREATE TABLE `core_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `core_user_name_2b52f76e_uniq` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，对 id 和 name 都有索引。

type = const - where name = “1”

mysql> EXPLAIN select * from task2.core_user where name = "1";
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys                | key                          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | core_user | NULL       | const | core_user_name_2b52f76e_uniq | core_user_name_2b52f76e_uniq | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# or
mysql> EXPLAIN select * from user limit 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

• type 为 const 说明使用了唯一索引或者主键索引，而且当前执行结果一定是最多包含1行记录的where查询语句

eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. 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.

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. In the following examples, MySQL can use an eq_ref join to process

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

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.

ref can be used for indexed columns that are compared using the = or <=> operator. In the following examples, MySQL can use a ref join to process 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 - 全文索引

The join is performed using a FULLTEXT index.

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

Example

Table Schema

mysql> show create table task2.core_user;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| core_user | CREATE TABLE `core_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...
  `words` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `core_user_name_2b52f76e_uniq` (`name`),
  FULLTEXT KEY `core_user_words_index` (`words`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

type = fulltext - EXPLAIN select * from task2.core_user where MATCH(words) against(“hello”);

mysql> EXPLAIN select * from task2.core_user where MATCH(words) against("hello");
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table     | partitions | type     | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | core_user | NULL       | fulltext | core_user_words_index | core_user_words_index | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-----------+------------+----------+-----------------------+-----------------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.02 sec)

• key 为 core_user_words_index 说明这个索引在这次查询中被用到了
• type 为 fulltext 说明使用了全文索引

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. In the following examples, MySQL can use a ref_or_null join to process ref_table:

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.

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.

index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range - 索引表范围查找

Only rows that are in a given range are retrieved, using an index （非唯一索引（unique index）、非主键索引（primary 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.

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

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

Example

Table Schema

mysql> show create table task2.core_user;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| core_user | CREATE TABLE `core_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `core_user_name_2b52f76e_uniq` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到，对 id 和 name 都有索引。

type = range - where name LIKE “1%”

mysql> EXPLAIN select * from task2.core_user where name LIKE "1%";
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys                | key                          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | core_user | NULL       | range | core_user_name_2b52f76e_uniq | core_user_name_2b52f76e_uniq | 82      | NULL |   12 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+------------------------------+------------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

• key 为 core_user_name_2b52f76e_uniq 说明这个索引在这次查询中被用到了
• type 为 range 说明使用索引范围扫描索引表

index - 索引表全表扫描

索引表全表扫描，把索引表从头到尾扫一遍（会比 ALL 快，因为索引表的体积通常小于数据表）。

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

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

MySQL can use this join type when the query uses only columns that are part of a single index.

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.

possible_keys - 查询可能使用到的索引

The possible_keys column indicates the indexes from which MySQL can choose to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from EXPLAIN. That means that some of the keys in possible_keys might not be usable in practice with the generated table order.

If this column is NULL (or undefined in JSON-formatted output), 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.

key：查询真正使用到的索引

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

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.

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.

To force MySQL to use or ignore an index listed in the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in your query.

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.

Due to the key storage format, the key length is one greater for a column that can be NULL than for a NOT NULL column.

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。

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.

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.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

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.

表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。

值越大，说明查询效率越高

extra

This column contains additional information about how MySQL resolves the query. For descriptions of the different values, see EXPLAIN Extra Information.

There is no single JSON property corresponding to the Extra column; however, values that can occur in this column are exposed as JSON properties, or as the text of the message property.

See more in https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information.

Using filesort

If an index cannot be used to satisfy an ORDER BY clause, MySQL performs a filesort operation that reads table rows and sorts them. A filesort constitutes an extra sorting phase in query execution.

To obtain memory for filesort operations, the optimizer allocates a fixed amount of sort_buffer_size bytes up front. Individual sessions can change the session value of this variable as desired to avoid excessive memory use, or to allocate more memory as necessary.

A filesort operation uses temporary disk files as necessary if the result set is too large to fit in memory. Some types of queries are particularly suited to completely in-memory filesort operations. For example, the optimizer can use filesort to efficiently handle in memory, without temporary files, the ORDER BY operation for queries (and subqueries) of the following form:

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

Ref

• https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

filesort 的排序方式

filesort 有两种排序方式

1. 对需要排序的记录生成 <sort_key,rowid> 的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表操作获取所需要的列的值，可能会导致大量的随机IO读消耗；
2. 对需要排序的记录生成 <sort_key,additional_fields> 的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，需要更多的空间用于排序。

Reference

• https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
• https://zhuanlan.zhihu.com/p/101571164

• ← Previous Post
• Next Post →