Explanation of MySQL execution plan

Explain syntax EXPLAINSELECT ...... Variant: 1. EXPLAINEXTENDEDSELECT ...... Decompile the execution plan into a SELECT statement and run SHOWWARNINGS to obtain the query statement optimized by the MySQL optimizer. 2. EXPLAINPARTITIONSSELECT ...... Information id contained in the explain execution plan for the partition table

Explain syntax explain select ...... Variant: 1. explain extended select ...... Decompile the execution plan into a SELECT statement and run show warnings to obtain the query statement optimized by the MySQL optimizer. 2. explain partitions select ...... Information id contained in the explain execution plan for the partition table

Explain syntax

Explain select ...... Variant:1. Explain extended select ...... Decompile the execution plan into a SELECT statement and run show warnings to obtain the query statement optimized by the MySQL optimizer.2. Explain partitions select ...... Used to EXPLAIN the Partition Table

Information contained in the execution plan

Id

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

Same id, execution sequence from top to bottom

In a subquery, the id sequence number increases. The higher the id value, the higher the priority.

If the IDs are the same, they can be considered as a group and executed sequentially from top to bottom. In all groups, the greater the id value, the higher the priority.

Select_type

Indicates the type of each select clause in the query (simple OR complex)

A. SIMPLE: the query does not contain subqueries or UNION

B. If the query contains any complicated sub-parts, the outermost query is marked as PRIMARY.

C. a subquery is included in the SELECT or WHERE list. The SUBQUERY is marked as SUBQUERY.

D. The subquery contained in the from list is marked as DERIVED (derivative)

E. If the second SELECT statement appears after UNION, it is marked as UNION. If UNION is included in the subquery of the from clause, the outer SELECT statement is marked as DERIVED.

F. SELECT for obtaining results from the UNION table is marked as: UNION RESULT

Type

Indicates how MySQL finds the required rows in the table, also known as "access type". The common types are as follows:

From left to right, from worst to best

A. ALL: Full Table Scan. MySQL will traverse the entire Table to find matched rows.

B. index: Full Index Scan. The difference between index and ALL is that index only traverses the index tree.

C. range: index range scan. The index scan starts at a certain point and returns rows that match the value range. It is common in between, <,>, and other queries.

Performance differences of different types of index access for range access

D. ref: Non-unique index scan. All rows matching a single value are returned. It is common in searches with a non-unique prefix of a non-unique index.

E. eq_ref: unique index scan. For each index key, there is only one record in the table that matches it. Common in primary key or unique index Scanning

F. const, system: these types of access are used when MySQL optimizes a part of the query and converts it to a constant. If the primary key is placed in the where list, MySQL can convert the query to a constant.

System is a special case of the const type. When the queried table has only one row, the system

G. NULL: MySQL breaks down statements during optimization, and does not even need to access tables or indexes during execution.

Possible_keys

Indicates which index MySQL can use to find rows in the table. If an index exists in the fields involved in the query, the index will be listed, but not necessarily used by the query.

Key

Displays the indexes actually used by MySQL in the query. If no index is used, it is NULL.

TIPS: If the overwrite index is used in the query, the index only appears in the key list.

Key_len

The number of bytes used in the index. You can use this column to calculate the length of the Index Used in the query.

The value displayed by key_len is the maximum possible length of the index field, rather than the actual length. That is, key_len is calculated based on the table definition and is not retrieved from the table.

Ref

Indicates the join matching condition of the above table, that is, which columns or constants are used to find the value of the index Column

In this example, we can see from key_len that idx_col1_col2 of table t1 is fully used. col1 matches col1 of table t2, and col2 matches a constant, that is, 'ac'

Rows

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

Extra

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

A. Using index

This value indicates that the Covering Index is used in the select Operation)

TIPS: Covering Index)

MySQL can use the index to return fields in the select list, instead of reading data files again based on the index.

The index that contains all the data that meets the query requirements is calledOverwrite Index(Covering Index)

Note:

If you want to use overwriting indexes, be sure to retrieve only the required columns from the select list and not select *. If you index all fields together, the index file will be too large and query performance will decrease.

B. Using where

Indicates that the MySQL server performs Post-filter after the storage engine is recorded ),

If the query fails to use the index, the function of Using where only reminds us that MySQL will use the where clause to filter the result set.

C. Using temporary

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

D. Using filesort

Sorting operations that cannot be completed by using indexes in MySQL are called "File Sorting"

Limitations of the MySQL execution plan

? EXPLAIN won't tell you about triggers, stored procedures, or the impact of user-defined functions on queries

? EXPLAIN does not consider various caches

? The EXPLAIN statement does not display the optimization work performed by MySQL during query execution.

? Some statistical information is estimated, not accurate.

? EXPALIN can only explain the SELECT Operation. Other operations must be rewritten to SELECT to view the execution plan.