Detailed explanation of MySQL EXPLAIN query plan

EXPLAIN is used to view the execution plan generated by MySQL for a SQL statement. Through execution plans, information such as access order, index selection, estimated scan rows, sorting, and temporary tables can be analyzed.

Differences between DESC and EXPLAIN

View table structure

DESC is the abbreviation of DESCRIBE and is used to view field information of a table or view.

DESC employee;

DESCRIBE employee;

These two statements usually display the field name, data type, whether NULL is allowed, index type, default values, and additional information.

View SQL execution plan

Add EXPLAIN before the query statement to view the execution plan selected by the optimizer.

EXPLAIN
SELECT *
FROM employee
WHERE first_name = 'tom';

MySQL also supports other output formats. For example, the tree format makes it easier to observe the execution order:

EXPLAIN FORMAT=TREE
SELECT *
FROM employee
WHERE first_name = 'tom';

EXPLAIN ANALYZE will actually execute SQL and return estimates and actual execution data. Update or delete statements should be used with caution to avoid accidentally modifying data.

EXPLAIN ANALYZE
SELECT *
FROM employee
WHERE first_name = 'tom';

EXPLAIN Common Column

Traditional table formats usually contain the following columns:

listed	role
id	Query block number, which can help determine the execution relationship between query blocks
select_type	The type of query block, such as simple query, main query, subquery, or derived table
table	Table, alias, or internal temporary result currently accessed
partitions	Partition expected to be accessed; usually NULL when partition table is not used
type	Table access method is an important indicator to determine index usage
possible_keys	Index that the optimizer thinks is likely to use
key	Actual selected index
key_len	Expected index key length in bytes
ref	Constant or column to compare to indexed column
rows	The optimizer estimates the number of rows to check
filtered	Percentage expected to retain after filtering through current table conditions
Extra	Supplementary information such as sorting, temporary tables, and override indexes

id: Query block number

id is used to identify different query blocks, but it cannot simply be regarded as an absolute execution order.

• Multiple rows of the same id usually belong to the same query block, and the order in which the tables are displayed reflects the order of access in the connection plan.
• Differences between id usually mean that there are multiple query blocks such as sub-queries, derived tables, or UNION.
• id for some result rows may be NULL, for example UNION RESULT.

The actual execution process of complex SQL should also be judged in conjunction with FORMAT=TREE or EXPLAIN ANALYZE.

select_type: Query Type

Common values are as follows:

value	meaning
SIMPLE	Simple query that does not contain UNION or subqueries
PRIMARY	outermost query
UNION	Second and subsequent query blocks in UNION
DEPENDENT UNION	A UNION query block that relies on outer query results
UNION RESULT	UNION result set
SUBQUERY	Subqueries that do not rely on outer queries
DEPENDENT SUBQUERY	Related subqueries that rely on outer query results
DERIVED	Derived table in the FROM clause
MATERIALIZED	Subquery results that are materialized and reused

table: Access objects

table generally displays table names or table aliases. For the internal results generated by the optimizer, the following forms may occur:

• <derivedN>: From the derived table query block numbered N.
• <unionM,N>: UNION results from query blocks numbered M, N, etc.
• <subqueryN>: Materialized sub-query results.

type: Access method

type describes how MySQL reads data from tables. Common types are listed below in order from precise to broad, but actual performance is also affected by factors such as return rows, data distribution, and caching.

Type	Description
system	A table is treated as having only one row and is a special case of const
const	Returns up to one row through equivalent matching of the primary key or unique index
eq_ref	When joining, match is made through the primary key or non-empty unique index, and each previous table combination hits at most one row
ref	Equivalent lookup through incomplete prefixes of a common index or unique index may return multiple rows
fulltext	Use full-text indexing
ref_or_null	Similar to ref, while additionally looking for NULL
index_merge	Merge scan results from multiple indexes
unique_subquery	Some IN subqueries look up through a unique index
index_subquery	Some IN subqueries look up through non-unique indexes
range	Performs a range scan on the index
index	Scan the entire index
ALL	Scan the entire table

ALL and index are usually focused on when analyzing. However, scanning small and full tables is not necessarily a problem. You cannot judge whether SQL needs optimization based on type alone.

index dependent column

possible_keys

Displays the indexes that the optimizer believes are likely to use. A value of NULL means that there is no obvious index available for the current query, but this does not mean that SQL must have a performance problem.

key

Displays the index that the optimizer finally selected. Even if possible_keys lists multiple indexes, key usually only displays the actual index; multiple indexes may be displayed when using index_merge.

key_len

Displays the length of the index key that the execution plan is expected to use. For federated indexes, this value can be used to help determine which index columns are used.

key_len is the estimated maximum length and does not represent the actual data length read. Character sets, nullable columns, variable-length fields, and indexed column types all affect this value.

ref

Displays the values used for comparison during index lookup. For example:

• const: Compare with constant.
• 库名.表名.列名: Comparison with columns of another table.
• func: Compare with an expression, function result, or value that has undergone type conversion.

rows and filtered

rows

rows is the optimizer’s estimate of the number of rows to be checked, not the actual number of rows scanned. When statistical information is inaccurate, the estimation results may also deviate.

filtered

filtered is the proportion of data expected to be retained after filtering through the current table conditions, in percentages.

You can roughly estimate the number of rows passed to the next step in the following way:

rows × filtered ÷ 100

If you need to observe the actual number of execution rows and time consuming, use EXPLAIN ANALYZE.

Extra: Supplementary information

Common information in Extra is as follows:

Information	Description
Using index	You can get the required columns by using the override index, and usually you don’t need to return to the table
Using where	After reading the data, it also needs to be filtered according to conditions
Using index condition	uses index conditions to push down and filter part of the record
Using filesort	Sorting cannot directly utilize the appropriate index and requires additional sorting; you may not actually write the disk file
Using temporary	uses an internal temporary table to store intermediate results, commonly used in partial grouping, deduplication, or sorting operations
Using join buffer	connection process uses connection buffers and usually requires checking connection conditions and indexing
FirstMatch	One of the semi-connection optimization strategies, stop looking for
LooseScan	One of the semi-connection optimization strategies, reducing duplicate matches through index skip scanning
Impossible WHERE	optimizer determines that the query condition cannot be established

Extra can appear multiple pieces of information at the same time. For example:

Using index condition; Using where

Basic analytical ideas

When analyzing the execution plan, you can check it in the following order:

1. Verify that the order of access to the tables is reasonable.

2. Check whether type has large ALL or unnecessary index scans.

3. Compare possible_keys with key to confirm whether the index is selected.

4. Determine the scope of use of the joint index based on key_len.

5. Pay attention to sorting, temporary tables, and connection buffer information in rows, filtered, and Extra.

6. Use EXPLAIN ANALYZE on critical SQL to verify the estimation results with actual rows and time consumption.