MySQL execution plan interpreting to others articles

explain Syntax

EXPLAIN  SELECT ... Variant:12. EXPLAIN Partitions SELECT ... Explain for partitioned tables

Information contained in the execution plan

ID

Contains a set of numbers that indicate the order in which a SELECT clause or action table is executed in a query

Same ID, execution order from top to bottom

In the case of a subquery, the sequence number of the ID is incremented, the higher the ID value, the higher the priority, and the first execution

If the ID is the same, it can be thought of as a group, executed from the top down; in all groups, the higher the ID value, the higher the priority, the first

Select_typeRepresents the type of each SELECT clause in a query (simple or complex)

A. Simple: The query does not contain subqueries or UNIONB. If the query contains any complex sub-parts, the outermost query is marked as: PRIMARYC. The subquery is included in the Select or where list, and the subquery is marked as: Subqueryd. The subquery contained in the From list is marked as: DERIVED (derivative) e. If the second select appears after the union, it is marked as union, and if the Union is contained in a subquery in the FROM clause, The outer select is marked as: DERIVEDF. The select that gets the result from the Union table is marked: Union result typeIndicates how MySQL finds the desired row in the table, also known as the "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 full table to find a matching row

B.index:full index Scan,index is different from all for index type only traversal index tree

C.range: Index range Scan, scan of index starts at a point, return rows matching the value of the domain, common in between, <, > and other queries

Different forms of index access performance differences for range access types

D.ref: A non-unique index scan that returns all rows that match a single value. Lookups that are common to non-unique prefixes that use non-unique indexes that are unique indexes

E.eq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common to primary key or unique index scans

F.const, System: Use these types of access when MySQL optimizes a part of the query and converts it to a constant. If you put the primary key in the where list, MySQL can convert the query to a constant

System is a special case of a const type that, when queried with only one row, uses system G.null:mysql to decompose statements during optimization without even accessing tables or indexes

Possible_keysIndicates which index MySQL can use to find rows in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query. KeyDisplays the index that MySQL actually uses in the query, and if the index is not used, it is displayed as Nulltips: If an overwrite index is used in the query, the index appears only in the key list

Key_lenRepresents the number of bytes used in the index, which evaluates the length of the index used in the query

The value displayed by Key_len is the maximum possible length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition, not by the table-retrieved refRepresents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index column

In this example, the idx_col1_col2 of the T1 table is fully used by Key_len, and the T2 of the col1 matching col1,col2 table matches a constant, that is, ' AC ' rows indicate MySQL based on table statistics and index selection, Estimated number of rows to be read for finding required records

ExtraContains additional information that is not suitable for display in other columns but is important a.using index This value indicates that the overwrite index is used in the corresponding select operation (covering index)

TIPS: Overlay index (covering index) MySQL can use the index to return the fields in the select list without having to read the data file again according to the index, which contains all the indexes that satisfy the query's needs are called Overwrite Index(covering index) Note: If you want to use an overlay index, be aware that only the required columns are taken out of the select list, not select *, because if all fields are indexed together, the index file is too large and the query performance is degraded b.using where Indicates that the MySQL server "Post-filter" after the storage engine has been recorded, and if the query fails to use the index, the role of using where is only a reminder that MySQL will filter the result set with a WHERE clause

C.using temporary means that MySQL needs to use temporary tables to store result sets, which are common in sorting and grouping queries

D.using Filesort A sort operation that cannot be done with an index in MySQL is called "file sort"

limitations of MySQL execution planexplain will not tell you about triggers, stored procedures, or user-defined functions that affect the query explain do not consider various cache explain do not display the optimization work that MySQL does when executing the query • Some of the statistics are estimates, not exact values · Expalin can only interpret select operations, other actions to override as Select to view execution plans

MySQL execution plan interpreting to others articles