MySQL execution plan explain detailed

Tag: Represents the + + table operation retrieval distinct additional buffer 2.4 through

Explain is primarily used to obtain a query execution plan that describes how MySQL performs query operations, the order of execution, the indexes used, and the number of rows that MySQL will need to execute to successfully return a result set. can help us to analyze the SELECT statement, let us know why the query is inefficient, so as to improve our query, so that the query optimizer can work better.

Explain syntax and description

From the above we can see that the syntax of explain is to add the Explain keyword before the SELECT statement. The following column information is then included after the explain is executed:

• ID: An identifier that represents the order of execution.
• Select_type: Query type.
• Table: The tables that are queried.
• partitions: Which partition to use, to be seen in conjunction with table partitioning (since 5.7)
• Type: Query types, such as the index index
• Possible_key: The index that may be used, if multiple indexes are separated by commas
• Key: The index is actually used, the name of the index is saved, if multiple indexes are separated by commas
• Key_len: Length of Use to index
• Ref: Which rows in the table that reference the index correspond to
• Rows: Shows the number of rows that MySQL has to return when executing a query
• Filtered: Percent of total compared by filter criteria
• Extra: Additional information, using file sort,using where

1. ID

The ID represents the select identifier, indicating the SQL execution order, which means that he is a serial number. There are three cases:

1.1 ID Same – Sequential execution

For the above three-table associated query, we can see that the ID column has the same value, which is 1. In this case, the order in which they are executed is executed sequentially, and is performed from top to bottom by column name.

1.2 IDs are all different – the larger the number, the more the first execution

Here we can see that in this subquery, the ID is completely different. In this case, the execution mechanism of MySQL is the larger the number, the more the first execution. Because you need to get the information in the subquery as a condition before you can query the information outside.

1.3 ID section is the same – first large execution, small order execution

For this ID part of the same situation, in fact, is the first 2 cases of synthesis. The order of execution is first executed according to the number, and then the same number is executed in the order of the top down.

2. select_type– Query Type

Select_type includes the following types:

• Simple: Represents an easy select query that does not require a union operation or does not contain subqueries. When there is a connection query, the outer query is simple, and there is only one
• Primary: A select that requires a union operation or contains a subquery, and the select_type of the outermost unit query is primary. and only one
• Union:union connected Two select queries, the first query is a dervied derived table, except for the first table, the second table Select_type is the union
• Dependent Union: As with union, it appears in the Union or UNION ALL statement, but the query is affected by external queries
• Union result: The result set containing the union, in the Union and UNION all statements, because it does not need to participate in the query, so the ID field is NULL
• Subquery: In addition to subqueries contained in the FROM clause, subqueries that appear elsewhere may be subquery
• Dependent subquery: Similar to dependent union, indicates that this subquery query is affected by external table queries
• A subquery, also called a derived table, that appears in a derived:from sentence may be called an inline view or a nested select in another database

2.1 primary/subquery

select * from student s where s. classid = (select id from classes where classno=‘2017001‘);

2.2 union/union Result

select * from student where id = 1 union select * from student where id = 2;

2.3 Dependent union/dependent subquery

select * from student s where s.classid in (select id from classes where classno=‘2017001‘ union select id from classes where classno=‘2017002‘);

2.4 Derived

select * from (select * from student) s;

In mysql5.5, mysql5.6.x:

However, the display will be different in 5.7:

3. Table

The query table name is displayed, if the query uses an alias, then the alias is shown here, if the operation of the data table is not involved, then this is displayed as NULL, if it is shown as a temporary table surrounded by angle brackets <derived N> , the following n is the ID in the execution plan, indicating that the result is generated from this query. If it is enclosed in angle brackets, <union M,N> and <derived N> similar, it is also a temporary table that indicates that the result is from the result set of the union query with the ID m,n.

4. partitions– partition

Partitions This column is built on your table is the partition table before the row.

select * from test_partition where id > 7;

Before 5.7, the partition information is not displayed by default and needs to be specified manually.

select * from emp;

5. type– Query Result type

The expression is queried by some type, for example, by index type lookup, by scope, mainly by the following types:

• Const: Indicates that there is a maximum of one matching row in the table
• Eq_ref: A unique row is read from the table for each record from the preceding table
• Ref: For each record from the preceding table, all matching rows are taken out of this table
• Ref_or_null: Similar to ref, but can search for rows that contain null values
• Index_merge: When you use multiple indexes in a single table, MySQL will say that multiple indexes are merged together
• Range: Retrieved by the specified range, very common
• Index: Find from the request tree
• All: Full table scan

As we can see from the classification described above, query efficiency is based on the top-down arrangement.

5.1 Const

When a unique index or primary key is used, the return record must be an equivalent where condition of 1 rows of records, usually the type is const. Other databases are also called unique index scans

select * from student where id = 1;

5.2 Eq_ref

For each record from the preceding table, a unique row is read from the table.

In a query plan that is now connected to a table, the driver table returns only one row of data, and the row data is the primary key or unique index of the second table, and must be not NULL, when the unique index and primary key are multiple columns, only eq_ref will appear when all columns are used as comparisons.

select * from student s, student ss where s.id = ss.id;

5.3 Ref

For each record from the preceding table, all matching rows are taken out of this table

Unlike Eq_ref, which requires a connection order, there is no requirement for a primary key and a unique index, as long as an equality condition is used to retrieve it, a common lookup for equivalence with the secondary index is found. Or a multi-column primary key, a unique index, the use of columns other than the first column as an equivalent lookup also appears, in short, the return data is not unique to the equivalent of a lookup can occur.

select * from student s, student_detail sd where s.id = sd.id;

5.4 Ref_or_null

Similar to ref, but you can search for rows that contain null values, but you don't actually use much.

select * from student_detail where address = ‘xxx‘ or address is null;

5.5 Index_merge

When you use multiple indexes in a single table, MySQL will say that multiple indexes are merged together.

Indicates that the query uses more than two indexes, and finally takes the intersection or the set, common and, or conditions use different indexes, the official sort after ref_or_null, but in fact because to read the index, performance may not be as large as the range.

5.6 Range

Index range scanning, common in queries using >,<,is Null,between, in, and like operators.

5.7 Index

Index full table scan, sweep the index from start to finish, it is common to use the index column to handle queries that do not need to read the data file, can use the index to sort or group queries.

5.8 All

This is the full table scan data file, and then the server layer to filter back to meet the requirements of the record.

6, Possible_keys & Key

Possible_key: The index to which the query may be used is listed here.

Key: The query is really used to the index, when Select_type is Index_merge, there may be more than two index, the other select_type here only one appears.

7, Key_len

The length of the index used to process the query, if it is a single-column index, then the entire index length, and if it is a multi-column index, then the query may not be able to use all the columns, the specific number of columns to use the index, here will be calculated, no use of the column, here is not counted in.

Pay attention to the value of this column, and calculate the total length of your multi-column index to see if all the columns are used. Note that the index used by the ICP attribute of MySQL is not counted into it. In addition, Key_len only calculates the index length used by the Where condition, and sorting and grouping are not counted to Key_len, even if the index is used.

If the key field value is null, the Key_len field value is also null, and the smaller the Key_len, the better, of course, cannot be null.

8. Ref

If the constant equivalent query is used, the const is shown here, and if it is a connection query, the execution plan of the driver table shows the associated field of the driver table, if the condition uses an expression or function, or if the condition column has an internal implicit conversion, it may appear as Func

9. Rows

Here is the estimated number of scanned rows in the execution plan, not the exact values

10, Filtered

This column appears when you use explain extended, and the version after 5.7 has this field by default and does not require the use of explain extended. This field indicates how much of the data returned by the storage engine is filtered by the server layer, the percentage of records that are satisfied with the query, and the percent, not the specific number of records.

11, Extra

This column can display a lot of information, there are dozens of kinds, commonly used are:

• Distinct: The DISTINC keyword was used in the Select section
• No tables used: query without FROM clause or from dual query
• A connection query using the not in () Form subquery or the NOT EXISTS operator, which is called an anti-join. That is, the general connection query is to query the inner table, and then query the appearance, anti-connection is to query the appearance first, then query the inner table.
• Using Filesort: This occurs when the index cannot be used when sorting. Common in order BY and GROUP by statements
• Using index: Query is not required to return a table query, directly through the index to get the data of the query.
• Using join buffer (block nested loop), using join buffer (batched key Accss): Version after 5.6.x optimizes the Bnl,bka attribute of the associated query. It is mainly to reduce the number of loops in the inner table and scan the query sequentially.
• Using sort_union,using_union,using intersect,using sort_intersection:
• Using intersect: Represents a condition that is used to obtain an intersection from a processing result when using an index of and
• Using union: Indicates that when a condition using an or connection is used for each index, that information represents a collection from the processing result
• Using Sort_union and using sort_intersection: Similar to the previous two counterparts, except that they appear when querying for large amounts of information with and and OR, the primary key is queried, and then the merge is sorted before the record can be read and returned.
• Using temporary: Indicates that interim tables are used to store intermediate results. Temporary tables can be memory temporary tables and disk temporary tables, the execution plan is not visible, you need to see the status variables, used_tmp_table,used_tmp_disk_table to see.
• Using where: Indicates that the records returned by the storage engine do not all satisfy the query criteria and need to be filtered at the server level. The query criteria are divided into constraints and check conditions, before 5.6, the storage engine can only scan the data based on the constraints and return, and then the server layer is filtered based on the inspection criteria and then returns the data that really conforms to the query. After 5.6.x, the ICP feature can be pushed down to the storage engine layer, data that does not meet the check conditions and restrictions, and does not read directly, thus greatly reducing the number of records scanned by the storage engine. The extra column displays the using index condition
• Firstmatch (tb_name): 5.6.x introduces one of the new features of the optimized subquery, which is common in the WHERE clause contains a subquery of type in (). If the data volume of the inner table is larger, this may occur
• Loosescan (M.. N): One of the new features of the optimized subquery introduced after 5.6.x, which may occur when a subquery returns a duplicate record in the in () type subquery

In addition to these, there are a number of query data dictionary libraries, in the execution of the plan found that there is no possibility of the results of some information.

Reference address: MySQL explain execution plan detailed

MySQL execution plan explain detailed