Explanation of MySQL execution plan, mysql execution plan

Explanation of MySQL execution plan, mysql execution plan
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_typeIndicates the type of each select clause in the query (simple OR complex)

A. SIMPLE: the query does not contain a subquery or UNIONb. if the query contains any complicated child parts, the outermost query is marked as PRIMARYc. the SELECT or WHERE list contains a subquery marked as SUBQUERYd. the subquery contained in the from list is marked as DERIVED (derivative) e. if the second SELECT clause appears after UNION, it is marked as UNION. If UNION is included in the subquery of the from clause, the outer SELECT is marked as DERIVEDf. the SELECT statement for obtaining results from the UNION table is marked as: UNION RESULT TypeIndicates 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 the worst to the 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 is used: MySQL breaks down the statement during the optimization process and does not even need to access the table or index during execution.

 

Possible_keysIndicates 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. KeyDisplays the indexes actually used by MySQL in the query. If no index is used, it is shown as NULLTIPS: If the index is overwritten in the query, the index only appears in the key list.

 

Key_lenThe 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. RefIndicates 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 and col2 of table t2 to match a constant, that is, 'ac' rows indicates that MySQL estimates the number of rows to be read Based on the table statistics and index selection.

 

ExtraContains additional information that is not suitable for display in other columns but is very important. a. Using index this value indicates that overwrite Index (Covering index) is used in the corresponding select operation)

 

TIPS: overwrite Index MySQL can use the Index to return fields in the select list, instead of reading data files again based on the Index. Overwrite Index(Covering Index) Note: If you want to use the overwrite Index, you must note that only the required columns are retrieved from the select list. Do not select *, if you index all fields together, the index file will be too large and the query performance will decrease. B. using where indicates that the MySQL server performs Post-filter after the storage engine receives a record. 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 indicates that MySQL uses 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 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 doesn't consider various caches • EXPLAIN won't show MySQL optimization during query execution work • some statistical information is estimated, not the exact value • EXPALIN can only explain the SELECT Operation, and other operations should be rewritten to SELECT to view the execution plan