Introduction to the EXPLAIN interpretation command in MySQL (with examples)-Mysql Tutorial-php.cn

This article brings you an introduction to the EXPLAIN interpretation command in MySQL (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1 EXPLAIN concept

EXPLAIN will provide us with some information about MySQL executing sql:

EXPLAIN can explain SELECT, DELETE, INSERT, REPLACE, and UPDATE and other statements.
When EXPLAIN is used with interpretable statements, mysql will display some information about the SQL execution plan from the optimizer. That is, MySQL explains how it processes these statements and how the tables are connected. Want to get more information about how EXPLAIN obtains the execution plan.
When EXPLAIN is followed by a session connection_id instead of an executable statement, it will display session information.
For SELECT statements, EXPLAIN will generate additional execution plan information, which can be displayed using SHOW WARNINGS.
EXPLAIN is useful for checking queries that design partitioned tables.
The FORMAT option can be used to select the output format. If the FORMAT option is not configured, it will be output in tabular form by default. The JSON option lets the information be displayed in json format.

2 EXPLAIN output column information

EXPLAIN output field information
First column: column name, second column: equivalent attributes displayed in the output when FORMAT = JSON Name, third column: Field meaning

##type access_type Connection type used, whether to use index ##possible_keys key key_len ref rows ##filtered filtered Indicates the percentage of data filtered by this query condition Extra None Additional information 2.1 id

Column	JSON Name	Meaning
id	select_id	select identification number
select_type	None	select type
table	table_name	Which table does this row of data refer to
partitions	partitions	Matching partitions, this value is empty for unpartitioned tables

possible_keys	Which index can MySQL use to find rows in this table
key	The key (index) that MySQL actually decides to use
key_length	MySQL determines the key length to use. If the key is NULL, the length is NULL
ref	Columns associated with the index
rows	mysql thinks the number of rows that must be verified when executing sql

SELECT identifier. SELECT The sequence number in the query, which can be empty.

2.2 select_type

SELECT type, all types are shown in the following table, EXPLAIN in JSON format exposes the SELECT type as an attribute of query_block, unless it is SIMPLE or PRIMARY. The JSON name (None if applicable) is also shown in the table.

Table information (for demonstration later):

mysql> show create table t_a; ------+ | t_a | CREATE TABLE `t_a` ( `id` bigint(20) NOT NULL DEFAULT '0', `age` int(20) DEFAULT NULL, `code` int(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_code` (`code`), KEY `age_key` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+----------------------------------- ------+ 1 row in set (0.03 sec)

Copy after login

SIMPLE: Simple SELECT (without using UNION or subquery, etc.)

mysql> explain select * from t_a where id =1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec)

Copy after login

PRIMARY: The outermost query when nesting queries

mysql> explain select * from t_a where num >(select num from t_a where id = 3); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index | | 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.03 sec)

Copy after login

UNION: The second or subsequent SELECT statement in UNION

mysql> explain select * from t_a where id =9 union all select * from t_a; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 2 | UNION | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.04 sec)

Copy after login

DEPENDENT UNION: The second or subsequent SELECT statement in UNION, depending on external QUERY

mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5); +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.08 sec)

Copy after login

UNION RESULT: RESULTS OF UNION

mysql> explain select num from t_a where id = 3 union select num from t_a where id =4; +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 2 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ 3 rows in set, 1 warning (0.03 sec)

Copy after login

SUBQUERY: FIRST SELECT IN SUBQUERY

mysql> explain select * from t_a where num >(select num from t_a where id = 3); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index | | 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.03 sec)

Copy after login

DEPENDENT SUBQUERY: The first selection in the subquery, depending on the outer query

mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4); +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.12 sec)

Copy after login

DERIVED: Derived table (temporary table generated in the subquery)

mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY |  | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | | 2 | DERIVED | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index | | 3 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.12 sec)

Copy after login

2.3 table

Shows which table this row of data refers to, sometimes it is the real table name, sometimes it may be the following results

: refers to the id The union of M and N row results
: This row refers to the derived table result of the row with id value n. A derived table may come from a subquery in a from clause, for example.
: This row refers to the result of the materialized subquery of the row with id value n.

2.4 partitions

The partition to which the queried record belongs. For unpartitioned tables, this value is NULL.

2.5 type

Which category is used for the connection, and whether an index is used. Commonly used types are: system, const, eq_ref, ref, range, index, ALL (from left to right, Performance is getting worse), for details, see EXPLAIN Join Types

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

system: This table (it may also be the temporary table queried) has only one row of data (= system table). It is a special case of const

const : The table has at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be treated as constants by the rest of the optimizer. const tables are fast because they are read only once! const is used for all parts of the query when the condition is a PRIMARY KEY or UNIQUE index and compared with a constant value.
In the following query, tbl_name can be used for const tables:

SELECT * from tbl_name WHERE primary_key=1； SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2； --例子 mysql> explain select * from t_a where id =1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.07 sec)

Copy after login

eq_ref: For each combination of rows in the previous tables, read one row from this table. Besides system and const, this is the best connection type. It is used when the join uses all parts of the index, and the index is a primary key or a unique non-null index. eq_ref can be used on indexed columns compared using the = operator. The comparison value can be a constant or an expression that uses a column from a table that was read before this table.
In the following example, MySQL can use the eq_ref join to process ref_tables:

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; --例子（t_b为t_a的复制表，表结构相同） mysql> explain select * from t_a,t_b where t_a.code=t_b.code; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | ALL | uk_code | NULL | NULL | NULL | 9 | 100.00 | NULL | | 1 | SIMPLE | t_b | NULL | eq_ref | uk_code | uk_code | 4 | test.t_a.code | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ 2 rows in set, 1 warning (0.03 sec)

Copy after login

ref For each combination of rows from the previous table, all rows with matching index values will be read from this table . Use ref if the join only uses the leftmost prefix of the key, or if the key is not a UNIQUE or PRIMARY KEY (in other words, if the join cannot result in a single row based on the key query). This join type is good if you are using keys that only match a small number of rows. ref can be used on indexed columns using the = or <=> operators.
In the following example, MySQL can use ref joins to handle ref_tables:

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; --例子（t_b为t_a的复制表，表结构相同） mysql> explain select * from t_a,t_b where t_a.age=t_b.age; +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ | 1 | SIMPLE | t_a | NULL | ALL | age_key | NULL | NULL | NULL | 9 | 100.00 | Using where | | 1 | SIMPLE | t_b | NULL | ref | age_key | age_key | 5 | test.t_a.age | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ 2 rows in set, 1 warning (0.03 sec)

Copy after login

fulltext: Use the FULLTEXT index to perform the join

ref_or_null: The join type ref is similar, but Added MySQL ability to specifically search for rows containing NULL values. This join type of optimization is often used in solving subqueries.
In the following example, MySQL can use ref_or_null joins to process ref_tables:

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; --例子 mysql> explain select * from t_a where t_a.age =3 or t_a.age is null; +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t_a | NULL | ref_or_null | age_key | age_key | 5 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.03 sec)

Copy after login

index_merge: This join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.

SELECT * FROM ref_table WHERE idx1=expr1 OR idx2 =expr2; --例子 mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3; +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ | 1 | SIMPLE | t_a | NULL | index_merge | uk_code,age_key | uk_code,age_key | 4,5 | NULL | 2 | 100.00 | Using union(uk_code,age_key); Using where | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ 1 row in set, 1 warning (0.03 sec)

Copy after login

unique_subquery: This type replaces the ref of the IN subquery of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery: This join type is similar to unique_subquery. IN subqueries can be replaced, but only for non-unique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range: retrieve only given A range of rows, using an index to select rows. The key column shows which index was used. key_len contains the longest key element of the index used. The ref column is NULL in this type. When using =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN or IN operators, you can use range# when comparing key columns with constants. ##

mysql> explain select * from t_a where id > 8; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.03 sec)

Copy after login

ALL: Perform a complete table scan for each combination of rows from the previous table. This is usually not good if the table is the first one not marked const, and is usually bad in that case. It is usually possible to add more indexes without using ALL so that rows can be retrieved based on constant values or column values in the previous table.

2.6 possible_keys

The possible_keys column indicates which index MySQL can use to find rows in the table. Note that this column is completely independent of the order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot actually be used in the generated table order.

If the column is NULL, there is no related index. In this case, you can improve the performance of your query by checking the WHERE clause to see if it refers to certain columns or columns that are suitable for indexing. If so, create an appropriate index and check query

2.7 key

# with EXPLAIN again. The key column shows the key (index) that MySQL actually decided to use. If no index is selected, the key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.

2.8 key_len

The key_len column displays the key length that MySQL decides to use. If the key is NULL, the length is NULL.
The length of the index used. Without losing accuracy, the shorter the length, the better

2.9 ref

The ref column shows which column or constant is used with the key to select rows from the table.

2.10 rows

The rows column displays the number of rows that MySQL thinks it must examine when executing the query.

2.11 Extra

This column contains the details of how MySQL solved the query, as detailed below.

Distinct: Once MYSQL finds a row that matches the row union, No more searching
Not exists: MYSQL optimizes LEFT JOIN. Once it finds a row matching the LEFT JOIN criteria, it no longer searches
Range checked for each: No ideal found index, so for each combination of rows from the previous table, MYSQL checks which index was used and uses it to return the rows from the table. This is one of the slowest connections using an index
Using filesort: When you see this, the query needs to be optimized. MYSQL requires an extra step to discover how to sort the returned rows. It sorts all rows based on the connection type and the row pointers that store the sort key value and all rows matching the condition
Using index: Column data is obtained from just using the information in the index without reading the actual action The table is returned. This occurs when all requested columns of the table are part of the same index.
Using temporary: When you see this, the query needs to be optimized. Here, MYSQL needs to create a temporary table to store the results. This usually occurs when ORDER BY is performed on different column sets instead of GROUP BY.
Using where: A WHERE clause is used to limit which rows will be compared with the next A table is matched or returned to the user. This will happen if you do not want to return all rows in the table and the connection type is ALL or index, or there is a problem with the query

[Related recommendations:MySQL Video Tutorial]

select_type Value JSON Name Meaning SIMPLE None Simple SELECT (does not use UNION or subquery, etc.) PRIMARY None The outermost query when nesting queries UNION None The second or subsequent SELECT statement in UNION DEPENDENT UNION dependent (true) The second or subsequent SELECT statement in UNION, depends on the external query UNION RESULT union_result UNION's result SUBQUERY None 子The first selection in the query DEPENDENT SUBQUERY dependent (true) The first selection in the subquery, depends on the outer query DERIVED None Derived table (temporary table generated in subquery) MATERIALIZED materialized_from_subquery Materialized subquery UNCACHEABLE SUBQUERY cacheable (false) The result cannot be cached Subquery must be recalculated for each row of the outer query UNCACHEABLE UNION cacheable (false) UNCACHEABLE subquery in UNION The second or subsequent selection (please refer to UNCACHEABLE SUBQUERY) unique_subquery is a The index lookup function can completely replace the subquery and is more efficient.index: This join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. MySQL can use this join type when the query uses only columns that are part of a single index.

The above is the detailed content of Introduction to the EXPLAIN interpretation command in MySQL (with examples). For more information, please follow other related articles on the PHP Chinese website!