Practical application of MySQL Query Optimizer

The following articles mainly describe the serial number queried in the execution plan selected by MySQL Query Optimizer. MySQL Query Optimizer can be used only when the serial number is queried in the execution plan. If you are interested in the actual operation, you may wish to browse the following articles.

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 results do not depend on the external query result set.

Uncacheable subquery: SUBQUERY 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 to access a table. It mainly 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, and then the index results are merged with merge), and then 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 drive table index 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 can be used, it will be displayed as null. This content is very important for tuning the index.

Key: The index selected by MySQL Query Optimizer from possible_keys.

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

Ref: Used to list whether a constant const is used, or a field in a table is used as a join clause to filter keys.

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, which includes the following content.

Distinct: query the distinct value. When mysql finds the first matching result, it will stop querying the value and convert it to other values later.

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

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 obtain rows.

SELECT tables optimized away: When we use some Aggregate functions to access a field with an 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. Such as MIN () or MAX.

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 sorting algorithm.

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 in Extra will be Using index for group-.

Using temporary: when MySQL must use a temporary table in some operations, Using temporary will appear in the Extra information. 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 retrieve all the required data by Using indexes, the Using where information will appear.

Using where with pushed condition: This is a message that appears only in the NDBCluster storage engine. You must enable the Condition Pushdown optimization function to use it. The control parameter is engine_condition_pushdown.

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.