MySQL explain detailed

The use of the explain command and the relevant parameter description.

Https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

EXPLAIN Output Columns

Column Name	Description
Id	Executes the number that identifies the row to which the select belongs. If there are no subqueries or associated queries in the statement, only the unique select, each row will display 1. Otherwise, the inner SELECT statement is typically numbered sequentially, corresponding to its position in the original statement
Select_type	Shows that the bank is a simple or complex select. If the query has any complex subqueries, the outermost tag is primary (DERIVED, Union, Union RESUlT)
Table	Access which table is referenced (referencing a query, such as "Derived3")
Type	Data access/Read operation type (all, index, range, ref, Eq_ref, Const/system, NULL)
Possible_keys	Reveal which indexes may be useful for efficient lookups
Key	Show MySQL decide which index to use to refine the query
Key_len	Shows the number of bytes that MySQL uses in the index
Ref	Shows the columns or constants used by the previous table to look up values in the index of the key column record
Rows	The number of rows that need to be read in order to find the desired row, the estimate, is imprecise. By multiplying all Rows column values, you can roughly estimate the number of rows that the entire query will check
Extra	Additional information, such as using index, Filesort, etc.

Id

The ID is used to sequentially identify the SELELCT statement in the entire query, and the larger the ID in the nested query, the more the statement executes first. The value may be null if this line is used to describe the union result of another row.

Select_type

Represents the type of query

type	Description
Simple	A simple subquery that does not contain subqueries and union
Primary	Contains union or subquery, the outermost part is marked primary
Subquery	subqueries in a generic subquery are marked as subquery, which is the query in the select list
Derived	Derived table--the temporary table is a subquery derived from a subquery that is located in a form
Union	The second and subsequent subquery in the Union is marked as union, and the first one is marked as primary if the Union is in the from and is marked derived
Union result	Select that is used to retrieve results from the anonymous temporary table is marked as union result
Dependent Union	As the name implies, you first need to meet the conditions of the Union, and the second and subsequent SELECT statements in the Union, and the statement relies on the external query
Subquery	The first SELECT statement in a subquery
Dependent subquery	and dependent union, as opposed to union.

Table

Which table, table name, or alias the corresponding row is accessing

• The Association optimizer selects the Association order for the query, and the left depth first
• When there is a subquery in the From, the table name is in the form of Derivedn, and N points to the subquery, which is the next column in the explain result
• When there is a union result, the table name is the form of Union, and the query ID that participates in the Union

Note: MySQL treats these tables like normal tables, but these "temporary tables" are not indexed.

Type

Type shows the type of access, which is an important indicator, and the resulting values from good to bad are:
System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query > Range > Index > All, in general, ensure that queries reach at least the range level and are best to reach ref.

type	Description
All	Worst case, full table scan
Index	Same as the full table scan. Just scan the table in the order of the index instead of the row. The main advantage is to avoid sorting, but the overhead is still very large. If you see the using index in the extra column, indicating that you are using the overwrite index to scan only the indexed data, it is much less expensive than the full table scan in indexed order
Range	Range Scan, a restricted index scan. The key column shows which index is used. When you use the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators to compare key columns with a constant, you can use the range
Ref	An index access that returns all rows that match a single value. Such index access occurs only if the non-uniqueness index or uniqueness index is used with a non-unique prefix. This type differs from eq_ref in that it uses only the leftmost prefix of the index for the association operation, or that the index is not unique and primary KEY. Ref can be used with indexed columns that use the = or <=> operator.
Eq_ref	Only one record that matches the criteria is returned. Occurs when a unique index or primary key lookup is used (efficient)
Const	When it is determined that there will be at most one row, the MySQL optimizer reads it before the query and reads it only once, so it is very fast. When the primary key is placed in the WHERE clause, MySQL shifts the query to a constant (efficient)
System	This is a special case of the const connection type, where only one row of the table satisfies the condition.
Null	This means that MySQL can decompose query statements during the optimization phase, even without access to tables or indexes at the execution stage (efficient)

Possible_keys

Shows which indexes are used by the query to indicate that the index can be efficiently found, but that the indexed indexes may be useless for subsequent optimization processes

Key

The key column shows the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Key_len

The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null. The length of the index to use. Without loss of accuracy, the shorter the better.

Ref

The ref column shows which column or constant is used together with key to select rows from the table.

Rows

The Rows column shows the number of rows that MySQL must check when it executes the query. Note that this is a pre-valuation.

Extra

Extra is another important column in the explain output that shows some of the details of MySQL during the query process, which is an important addition to the query plan during the execution of the query by the MySQL query optimizer.

type	Description
Using Filesort	There are two ways MySQL can generate ordered results, either by sorting operations or by using an index, when a using Filesort in extra shows that MySQL uses the latter, but note that although it is called filesort, it does not mean that the file is used for sorting, As long as the possible sort is done in memory. Indexing is faster in most cases, so it's generally time to consider optimizing queries. Using a file to complete the sort operation, which is probably the result of the Ordery By,group by statement, may be a CPU-intensive process that can improve performance by selecting the appropriate index and using indexes to sort the results of the query.
Using Temporary	Save intermediate results with temporary tables, commonly used in group by and ORDER by operations, generally see that it indicates that the query needs to be optimized, even if the use of temporary tables to avoid the use of hard disk temporary table.
NOT EXISTS	MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches.
Using Index	Indicates that the query is indexed, does not need to read the data file, and obtains information from the index tree (index file). If a using where is present, indicating that the index is used to perform the lookup of the index key value, there is no using where, indicating that the index is used to read the data rather than perform a lookup action. This is done by the MySQL service layer, but there is no need to return the table query records.
Using Index Condition	This is the new feature of MySQL 5.6, called "Index conditional Push". To put it simply, MySQL was unable to perform an operation like this on the index, but now it does, which reduces unnecessary IO operations, but it can only be used on a level two index.
Using where	A WHERE clause is used to restrict which rows will match the next table or are returned to the user. Note : The extra column appears using where to indicate that the MySQL server returns the storage engine to the service layer and then applies the Where Condition filter.
Using Join Buffer	Connection Cache used: BlockNestedLoop, the connection algorithm is a nested loop connection; batched Key Access, the connection algorithm is a batch index connection
Impossible where	The value of the WHERE clause is always false and cannot be used to get any tuples
Select tables Optimized away	In the absence of a GROUP BY clause, the Min/max operation is optimized based on the index, or the count (*) operation is optimized for the MyISAM storage engine, without having to wait until the execution phase to perform the calculation, and the phase of the query execution plan generation completes the optimization.
Distinct	Optimize the distinct operation to stop finding the same value after the first matching tuple is found

MySQL explain detailed