MySQL EXPLAIN Detailed explanation of query plan

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

DESC with EXPLAIN the difference between

View table structure

DESC is DESCRIBE The abbreviation for, used to view field information in a table or view.

DESC employee;

DESCRIBE employee;

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

View SQL execution plan

Add before query statement EXPLAIN, you can 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 SQL is actually executed and estimates and actual execution data are returned. 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:

id: Query block number

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

• id The same rows usually belong to the same query block, and the order in which the tables are displayed reflects the order of access in the join plan.
• id Different usually indicates the presence of a subquery, derived table, or UNION Wait for multiple query blocks.
• Certain results are consistent id may be NULL, for example UNION RESULT。

The real execution process of complex SQL should also be combined FORMAT=TREE or EXPLAIN ANALYZE Judgment.

select_type: Query type

Common values are as follows:

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 number numbered N The derived table query block of.
• <unionM,N>: From the number numbered M、N Wait for query block UNION result.
• <subqueryN>: The materialized subquery result.

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.

Analysis usually requires focused attention ALL and index。However, scanning small and full tables is not necessarily a problem, and cannot be based on type Determine individually whether SQL needs optimization.

index dependent column

possible_keys

Displays the indexes that the optimizer believes are likely to use. value is NULL Indicates 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 possible_keys Multiple indexes are listed,key Usually only the index actually used is displayed; using index_merge Multiple indexes may be displayed when.

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 constants.
• 库名.表名.列名: Compare with columns in another table.
• func: Compare with an expression, function result, or value to which a type conversion occurred.

rows with filtered

rows

rows It is the optimizer that estimates 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 percentage.

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 execution rows and time spent, you should use the EXPLAIN ANALYZE。

Extra: Supplementary information

Extra Common information in:

Extra Multiple messages can appear 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 type Whether there is a large list ALL or unnecessary index Scan.

3. contrast possible_keys with key, confirm whether the index is selected.

4. binding key_len Determine the scope of use of the union index.

5. attention rows、filtered and Extra Sort, temporary table, and connection buffer information in.

6. Use of key SQL EXPLAIN ANALYZE, verify the estimation results with the actual number of rows and time consuming.