[MYSQL] EXPLAIN command

EXPLAIN command

The EXPLAIN command is the main way to see how the optimizer decides to execute the query, but it does not necessarily follow this in the end.

EXPLAIN can act on SELECT, DELETE, INSERT, UPDATE, and REPLACE statements.

You can use FORMAT=JSON to output detailed execution plan costs

EXPLAIN FORMAT=JSON SELECT id FROM zc_order;

It is roughly in the following format

{
    "query_block": {
        "select_id": 1,
        "cost_info": {
            "query_cost": "399.75"
        },
        "table": {
            "table_name": "zc_order",
            "access_type": "index",
            "key": "IDX_ORDER_NO",
            "used_key_parts": [
                "ORDER_NO"
            ],
            "key_length": "98",
            "rows_examined_per_scan": 3755,
            "rows_produced_per_join": 3755,
            "filtered": "100.00",
            "using_index": true,
            "cost_info": {
                "read_cost": "24.25",
                "eval_cost": "375.50",
                "prefix_cost": "399.75",
                "data_read_per_join": "14M"
            },
            "used_columns": [
                "ID"
            ]
        }
    }
}

Information contained in the execution plan

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

For details, please refer to: MySQL official document-explain-output

Let’s take a look at the meaning of each item

id

Contains a set of numbers, indicating the order in which the select clause or operation table is executed in the query

The id is the same, which means the same group, and the execution order is from top to bottom

If the id is different, the larger the value, the higher the priority, and the first to execute

If it is a subquery, the id's serial number will increase, the greater the id value, the higher the priority, and the earlier it will be executed

The id of the temporary table is NULL

select_type

The type of each select clause in the query (simple OR complex)
select_type For non-SELECT statements, the type is displayed, for example, UPDATE displays UPDATE

SIMPLE : simple SELECT, the query does not contain subqueries or UNION

mysql> EXPLAIN SELECT * FROM zc_order;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3781 |      100 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

PRIMARY : the outermost SELECT, if the query contains any complex sub-parts, the outermost query will be marked

SUBQUERY : A subquery is included in the SELECT or WHERE list, and the subquery is marked

mysql> EXPLAIN SELECT * from zc_order_goods where order_no = (select MAX(order_no) from zc_order);
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | zc_order_goods | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4696 |       10 | Using where                  |
|  2 | SUBQUERY    | NULL           | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

UNION : mark the SELECT that appears after UNION

UNION RESULT : the result of UNION

mysql> EXPLAIN SELECT * from zc_order o where o.order_no = 'UNO200418000000008' UNION SELECT * from zc_order o2 where o2.order_no = 'UNO200418000000009';
+------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY      | o          | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | const |    1 |      100 | NULL            |
|    2 | UNION        | o2         | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | const |    1 |      100 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+

DERIVED : used to indicate the subquery contained in the FROM clause, mysql will recursively execute and put the results in a temporary table. The server is internally called a "derived table" because the temporary table is derived from the subquery. (The performance of the derived table is not good)

--  MySQL 5.7开始优化器引入derived_merge,
-- 当子查询中存在 UNION、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作时会禁止该优化
mysql> EXPLAIN SELECT * FROM ( SELECT * FROM zc_order LIMIT 1) temp;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |      100 | NULL  |
|  2 | DERIVED     | zc_order   | NULL       | ALL    | NULL          | NULL | NULL    | NULL | 3781 |      100 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+

DEPENDENT DERIVED : Indicates that the derived table depends on another table

SUBQUERY and UNION can also be marked as DEPENDENT and UNCACHEABLE . DEPENDENT means that the SELECT is in the subquery and depends on the outer query. UNCACHEABLE means that the result of the subquery cannot be cached, and the result must be recalculated for each row of the outer query.

mysql> EXPLAIN SELECT * from zc_order where order_no in 
(SELECT order_no from zc_order o where o.order_no = 'UNO200418000000008' UNION SELECT order_no from zc_order o2 where o2.order_no = 'UNO200418000000009');
+------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra           |
+------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+
|    1 | PRIMARY            | zc_order   | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  | 3781 |      100 | Using where     |
|    2 | DEPENDENT SUBQUERY | o          | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | const |    1 |      100 | Using index     |
|    3 | DEPENDENT UNION    | o2         | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | const |    1 |      100 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+-------+---------------+--------------+---------+-------+------+----------+-----------------+

MATERIALIZED materialized subquery is used to optimize the subquery, corresponding to FORMAT=JSON output. materialized_from_subquerySee: MySQL official document-subquery-materialization

table

Indicates the table accessed by the row

<derivedN>Indicates that the current query depends on the query with id=N

<unionM,N> Indicates that the query with id=M and N participated in UNION

<subqueryN> The result of a materialized subquery with id=N

partitions

If the query is based on a partition table, the partition that the query will access will be displayed

type

Represents the way that MySQL finds the required row in the table, also known as the "access type". Common types are as follows:

Existing index
ALTER TABLE zc_order ADD PRIMARY KEY (ID);
ALTER TABLE zc_order ADD UNIQUE INDEX IDX_ORDER_NO (ORDER_NO);
ALTER TABLE zc_order ADD INDEX IDX_USER_NO_ORDER_STATUS (USER_NO, ORDER_STATUS);

all : Full Table Scan, MySQL will traverse the entire table to find matching rows

mysql> EXPLAIN SELECT * FROM zc_order;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3781 |      100 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

index : Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree

mysql> EXPLAIN SELECT id, order_no FROM zc_order;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | zc_order | NULL       | index | NULL          | IDX_ORDER_NO | 130     | NULL | 3781 |      100 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

range : Index range scan, the obvious index range scan is between、!=、<>、in、not in、orthe query with (or both sides must be indexes)

mysql> EXPLAIN SELECT * FROM zc_order WHERE order_no = 'UNO200418000000008' or order_no = 'UNO200418000000009';
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | zc_order | NULL       | range | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | NULL |    2 |      100 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

ref : Use a non-unique index scan or a prefix scan of a unique index (combined index) to return a record row that matches a single value

-- 非唯一索引,走ref
-- 唯一索引,如果是单列索引走的是const, 如果是组合索引的前缀匹配走ref
mysql> EXPLAIN SELECT * FROM zc_order WHERE USER_NO = 'US0000000001';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | ref  | IDX_USER_NO_ORDER_STATUS | IDX_USER_NO_ORDER_STATUS | 258     | const |    4 |      100 | NULL  |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+

eq_ref : Similar to ref, the difference is that the index used is the primary key index or the unique index. For each index key value, there is only one record in the table that matches

-- 单表没测试出来,通常是const
-- 连表查询
mysql> EXPLAIN SELECT * FROM zc_order o, zc_order_goods g WHERE o.ORDER_NO = g.ORDER_NO;
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key          | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | g     | NULL       | ALL    | NULL          | NULL         | NULL    | NULL                 | 4696 |      100 | NULL  |
|  1 | SIMPLE      | o     | NULL       | eq_ref | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | test-db.g.ORDER_NO   |    1 |      100 | NULL  |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+

const : When MySQL optimizes a certain part of the query and converts it to a constant, use these types of access. For example, use primary key or unique index for query

mysql> EXPLAIN SELECT * FROM zc_order WHERE order_no = 'UNO200418000000008';
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | const |    1 |      100 | NULL  |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+

system : system is a special case of const type. When the query table has only one row, use system

-- 测不出来,即使表中只存在一条记录,还是const

NULL : MySQL decomposes the statement during the optimization process, and does not even need to access the table or index during execution. For example, selecting the minimum value from an index column can be done through a separate index search.

mysql> EXPLAIN SELECT MIN(order_no) FROM zc_order;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

From top to bottom, performance is getting better and better

possible_keys

Indicate which index MySQL can use to find records in the table. If there is an index on the field involved in the query, the index will be listed, but it may not be used by the query

It is possible that it is possible_keysnot null, but keyis null. Generally, there is a matching index on the query condition, but the mysql optimizer thinks that the full table scan is more efficient

mysql> EXPLAIN SELECT * FROM zc_order where user_no > 'US0000000013';
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | zc_order | NULL       | ALL  | IDX_USER_NO_ORDER_STATUS | NULL | NULL    | NULL | 3781 |    90.27 | Using where |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+

The other general situation is just the opposite, possible_keys is null, but the key is not null, usually caused by the use of a covering index

-- 这里比较有趣的一点是,覆盖的索引为id,任意索引中都存在主键 mysql根据某种规则选择了其中一个而不是直接拿主键索引
-- 如果查询的是user_no,那么必定是使用IDX_USER_NO_ORDER_STATUS索引
mysql> EXPLAIN SELECT id FROM zc_order;
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | zc_order | NULL       | index | NULL          | IDX_ORDER_NO | 130     | NULL | 3781 |      100 | Using index |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+

key

Actually used index

key_len

Indicates the number of bytes used in the index. This column can be used to calculate the length of the index used in the query (key_len shows the maximum possible length of the index field, not the actual length used, that is, key_len is calculated according to the table definition, not Retrieved from the table)

  • For all index fields, if not null is not set, one byte needs to be added to record whether it is NULL
  • Fixed length field, int occupies four bytes, date occupies three bytes, char(n) occupies n characters , tinyint occupies one byte
  • For the variable length field varchar(n), there are n characters + two bytes.
  • Different character sets, the number of bytes occupied by a character is different. A character encoded by latin1 occupies one byte, a character encoded by gbk occupies two bytes, a character encoded by utf8 occupies three bytes, and a character encoded by utf8mb4 occupies four bytes
-- `ORDER_NO` varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- 32 * 3 + 2 = 98
mysql> EXPLAIN SELECT * FROM zc_order where order_no = 'UNO200515000000060';
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | const | IDX_ORDER_NO  | IDX_ORDER_NO | 98      | const |    1 |      100 | NULL  |
+----+-------------+----------+------------+-------+---------------+--------------+---------+-------+------+----------+-------+

-- `USER_NO` varchar(64) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- `ORDER_STATUS` char(2) CHARACTER SET utf8 NOT NULL DEFAULT ''
-- 64 * 3 + 2 + 2 * 3 = 200
mysql> EXPLAIN SELECT * FROM zc_order where user_no = 'US0000000013' and order_status = '01';
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | zc_order | NULL       | ref  | IDX_USER_NO_ORDER_STATUS | IDX_USER_NO_ORDER_STATUS | 200     | const,const |    1 |      100 | NULL  |
+----+-------------+----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

ref

Indicates which columns of the index are used, which may be a constant

Common ones are: const, field name, NULL

-- const
mysql> EXPLAIN SELECT * FROM zc_order WHERE id = 20;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | zc_order | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |      100 | Directly search via Primary Index |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------------------+

-- 字段名
mysql> EXPLAIN SELECT * FROM zc_order o, zc_order_goods g WHERE o.ORDER_NO = g.ORDER_NO;
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key          | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | g     | NULL       | ALL    | NULL          | NULL         | NULL    | NULL                 | 4696 |      100 | NULL  |
|  1 | SIMPLE      | o     | NULL       | eq_ref | IDX_ORDER_NO  | IDX_ORDER_NO | 130     | test-db.g.ORDER_NO   |    1 |      100 | NULL  |
+----+-------------+-------+------------+--------+---------------+--------------+---------+----------------------+------+----------+-------+

rows

Indicates that MySQL estimates the number of rows that need to be read to find the required records based on table statistics and index selection

filtered

This field indicates the percentage of the number of records that satisfy the query remaining after the data returned by the storage engine is filtered at the server layer (estimated)

This field is almost useless, usually showing 100%, indexand allit only takes effect when the index type is and (there is an index on the column that appears in the where condition, but the execution plan does not take the range or ref scan of the index, but the whole table Scan or cover index scan)

mysql> EXPLAIN SELECT * FROM zc_order where user_no > 'US0000000013';
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | zc_order | NULL       | ALL  | IDX_USER_NO_ORDER_STATUS | NULL | NULL    | NULL | 3755 |    49.99 | Using where |
+----+-------------+----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
1 row in set

Extra

Contains additional information that is not suitable for display in other columns but is very important

Using where

Indicates that the mysql server will perform filtering (service layer filtering) after retrieval by the storage engine. Many where conditions involve columns in the index. When it reads the index, it can directly filter in the index, so not all queries with where will display "Using where"

Using index

This value indicates that a covering index is used in the corresponding select operation (Covering Index)

Using index confition

Indicates that index pushdown is used

Insert picture description here

Using temporary

Indicates that MySQL needs to use a temporary table to store the result set, which is common in sorting and grouping queries

Using filesort

MySQL must do an extra step to figure out how to retrieve rows in sorted order

Using join buffer

This value emphasizes that the index is not used when obtaining the connection conditions, and the connection buffer is required to store intermediate results. If this value appears, it should be noted that depending on the specific situation of the query, you may need to add an index to improve performance

Impossible where

This value emphasizes that the where statement will result in no eligible rows

Select tables optimized away

This value means that only by using the index, the optimizer may only return one row from the aggregate function result.

EXPLAIN SELECT MIN(id) FROM zc_order;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

Index merges

When MySQL decides to use more than one index on a given table, one of the following formats will appear, detailing the index used and the type of merge

Using sort_union(…)
Using union(…)
Using intersect(…)