Use of explain

During a query optimization process, you need to constantly use the explain statement to verify the effectiveness of various adjustments.

The MySQL explain function displays the following information:

ID: The serial number queried in the execution plan selected by MySQL query optimizer.

Select_type: the query type used, mainly including the following query types.

• Dependent subquery: The first select in the subquery layer, which depends on the result set of the external query.
• Dependent Union: the Union in the subquery, which is all the select statements after the second SELECT statement in the Union statement. It also depends on the result set of the external query.
• Primary: the outermost query in the subquery. Note that it is not a primary key query.
• Simple: queries other than subqueries or union.
• Subquery: The first select statement in the subquery's inner layer. The result does not depend on the result set of the external query.
• Uncachetable subquery: subqueries that cannot be cached in the result set.
• Union: All the select statements after the second SELECT statement starts in the Union statement. The first select statement is primary.
• Union result: Merge result in union.

Table: displays the name of the table in the Database accessed in this step.

Type: indicates the access method used by the Team table, which includes the following types.

• ALL: full table scan.
• Const: Read Constant. Only one record can be matched at most. Because it is a constant, you only need to read it once.
• Eq_ref: Only one matching result is available. It is generally accessed through a primary key or a unique key index.
• Fulltext: Full-text index retrieval.
• Index: full index scan.
• Index_merge: two (or more) indexes are used in the query. Then, the index results are merged and the table data is read.
• Index_subquery: the combination of returned fields in a subquery is an index (or an index combination), but it is not a primary key or a unique index.
• Rang: index range scanning.
• Ref: Query referenced by the index of the driver table in the join statement.
• Ref_or_null: The only difference from ref is that a query with a null value is added in addition to the query referenced by the index.
• System: A system table with only one row of data.
• Unique_subquery: the combination of returned fields in a subquery is a primary key or a unique constraint.

Possible_keys: index available for this query. If no index is available, it is displayed as null. This content is very important for tuning the index.

Key: MySQL query optimizer: select the index used from possible_keys.

Key_len: the length of the index key selected for use.

Ref: Used to list whether a constant (const) or a field in a table (if jion) has been used (key.

Rows: Number of result set records estimated by MySQL query optimizer based on the statistical information collected by the system.

Extra: query the extra details of each step, including the following content:

Distinct: query the distinct value. When MySQL finds the first matching result, it stops querying the value and converts it to the query of other values.

Full scan on Null key: An Optimization Method in subqueries. It is mainly used when null values cannot be accessed through indexes.

Impossible where noticed after reading const tables: MySQL query optimizer identifies impossible results by collecting statistics.

No tables: the query statement uses from dual or does not contain any from clause.

Not exists: In some left connections, MySQL query optimizer can partially reduce the number of Data Accesses by changing the composition of the original query.

Range checked for each record (index map: N): The description in the MySQL official manual. when MySQL query optimizer does not find any available indexes, if the column values in the preceding table are known, some indexes can be used. For each row combination in the preceding table, MySQL checks whether the range or index_merge access method can be used to index rows.

Select tables optimized away: When we use some Aggregate functions to access a field of an existing index, MySQL query optimizer will locate the required data row directly through the index to complete the entire query, of course, the premise is that no group by operation is allowed in the query.

Using filesort: When the query contains the order by operation and the index cannot be used to complete the sorting operation, MySQL query optimizer has to select the corresponding sortingAlgorithm.

Using Index: you only need to obtain all the required data in the index. You do not need to retrieve data from the table.

Using index for group-by: data access is the same as using index. You only need to read the required data. If the group by or distinct clause is used in the query, if the group field is also in the index, the information is displayed.

Using temporary: This occurs in extra information when MySQL must use a temporary table in some operations. It is common in group by and order by operations.

Using where: if you do not read all the data in the table, or you can obtain all the required data by indexing, the using where information will appear.

Using where with pushed condition: This is a message that only appears in the ndbcluster storage engine. In addition, you need to enable the condition Pushdown optimization function to be used. The control parameter is engine_condition_pushdown.