Explain implementation of SQL statement execution efficiency check in MySQL

Explain syntax: Explain select ... from ... [Where ...]

For example: Explain select * from news;

Output:

+--+ ————-+ ——-+ ——-+ ——————-+ ——— + ——— + ——-+ +--+ ——-+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+--+ ————-+ ——-+ ——-+ ——————-+ ——— + ——— + ——-+ +--+ ——-+

The following is an understanding of each property:

1, ID: This is the query serial number of SELECT

2, Select_type:select_type is the type of select, can have the following several:

Simple: Simply select (Do not use union or subquery, etc.)

PRIMARY: The outermost Select

The second or subsequent SELECT statement in the Union:union

The second or subsequent SELECT statement in the DEPENDENT union:union, depending on the query outside

The result of the UNION result:union.

Subquery: The first select in a subquery

DEPENDENT subquery: The first select in a subquery, depending on the query outside

DERIVED: Exporting a table's SELECT (subquery FROM clause)

3, table: Show this line of data is about which table

4, type: This column is the most important, showing the type of connection used, whether or not to use the index, is the use of the explain command to analyze performance bottlenecks in one of the key.

The resulting values are from good to bad in turn:

System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_sub Query

> Range > Index > All

In general, you have to ensure that the query reaches at least the range level, preferably in ref, or there may be a performance problem.

5. Possible_keys: column indicating which index MySQL can use to find rows in the table

6, key: Display MySQL actually decided to use the key (index). If no index is selected, the key is null

7, Key_len: Display MySQL decided to use the key length. If the key is null, the length is null. The length of the index used. Without losing the accuracy of the love

condition, the shorter the length the better.

8. Ref: Shows which column or constant is used with the key to select rows from the table.

9. Rows: Displays the number of rows that MySQL must check when it executes the query.

10, Extra: Contains the details of the MySQL resolution query, but also one of the key references.

Distinct
Once MySQL finds a row that matches the row, it stops searching.

NOT EXISTS
MYSQL optimizes the left join, and once it finds a row that matches the left join criteria,

We stop searching.

Range checked for each

Record (index map:#)
The ideal index was not found, so for each row from the previous table, MySQL checks which index is used and uses it to return rows from the table.

This is one of the slowest connections using the index

Using Filesort
When you see this, the query needs to be optimized. MySQL needs to take extra steps to find out how to sort the rows returned. It depends on the type of connection and

A row pointer that stores all rows of a sort key value and matching criteria to sort all rows

Using Index
Column data is returned from a table that uses only the information in the index without reading the actual action, which occurs when the entire Request column for a table is the same cable

The primer part of the time

Using Temporary
When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which typically occurs when a different set of columns

On the order by, not on the group by

Using where
The WHERE clause is used to restrict which rows match the next table or return to the user. If you do not want to return all rows in the table, and the connection type all

Or index, this happens, or there is a problem with the query

Some other tip:

When type is displayed as "index", and extra is displayed as "using Index", the overlay index is used.

Attach some field descriptions

Id:mysql query optimizer The serial number of the queries in the selected execution plan.

Select_type: The type of query used is mainly the following types of queries.

DEPENDENT subquery: The first select in the inner layer of the subquery relies on the result set of the external query.

DEPENDENT Union: The Union in a subquery, and all the select after the start of the second select in Union, also dependent on the knot of the outer query

Fruit set.

PRIMARY: The outermost query in a subquery, note that it is not a primary key query.

Simple: A query other than a subquery or union.

Subquery: The first select of a subquery inner-level query, and the result does not depend on the external query result set.

Uncacheable subquery: A subquery that cannot be cached by the result set.

All select after the second select starts in the Union:union statement, and the first select is primary.

The result of the merge in UNION Result:union.

Table: Displays the names of the tables in the database to which this step is accessed.

Type: tells us how to access the table, mainly with the following set of types.

All: Full table scan.

Const: Read constants, at most only one record will match, because it is a constant, actually only need to read once.

Eq_ref: There will be only one matching result, typically accessed through primary key or 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 (merge) and the table data is read.

Index_subquery: The returned result field combination in a subquery is an index (or combination of indexes), but is not a primary key or a unique index.

Rang: Index range scan.

Query that is referenced by the driver table index in the Ref:join statement.

Ref_or_null: The only difference to ref is a query that adds a null value outside the query that uses the index reference.

System: Systems table with only one row of data in the table;

Unique_subquery: The return result field combination in a subquery is a primary KEY or a unique constraint.

Possible_keys: The index that the query can take advantage of. If no index is available, it is displayed as NULL, which is tuned to optimize indexing

The whole thing is very important.

Key:mysql Query Optimizer The index that is selected from the Possible_keys.

Key_len: The index key length selected to use the index.

REF: Whether the list is filtered (via key) by constant (const) or a field in a table (if it is a join).

Rows:mysql Query Optimizer The number of result set records that are estimated by the statistical information collected by the system.

Extra: The additional details that are implemented in each step of the query are mainly the following.

Distinct: Find the Distinct value, and when MySQL finds the first matching result, it stops the query for that value and turns to the other value query later.

Full scan on null key: An optimization method in a subquery that is primarily encountered using null values that cannot be accessed through an index.

Range checked for each record (index Map:n): described in the official MySQL manual, when MySQL Query Optimizer did not

If you find a good available index, some indexes can be used if you find that the column values in the previous table are known. For each row combination of the previous table, the MySQL check is

You can use a range or Index_merge access method to request a row.

SELECT tables Optimized away: When we use certain aggregate functions to access a field that has an index, MySQL Query Optimizer

The entire query is completed at once by indexing directly to the desired data row. Of course, the premise is that you cannot have a GROUP by operation in Query. If you use

MIN () or Max ().

Using filesort: When query contains an order by operation, and you cannot use the index to complete the sort operation, MySQL Query Optimizer

Have to choose the appropriate sorting algorithm to achieve.

Using Index: The required data is available only at index, and no more data is needed in the table.

Using index for group-by: data access is the same as using index, and the required data only needs to read the index, and when query uses group by or

DISTINCT clause, if the grouping field is also in the index, the information in extra is the Using index for group-by.

Using temporary: A using temporary appears in Extra information when MySQL must use a temporary table in some operations. Mainly often

Found in operations such as GROUP by and order by.

Using Where: If you do not read all the data for the table, or if you can get all the data that you want only through the index, you will receive a using where

Information.

Using where with pushed condition: This is a message that only appears in the Ndbcluster storage engine and needs to be hit by

Open Condition pushdown optimization function can be used. The control parameter is Engine_condition_pushdown.

Impossible WHERE noticed after reading const TABLES:MYSQL Query Optimizer The statistical information collected to determine not

There may be a result.

The no Tables:query statement uses the from dual or does not contain any FROM clause.

NOT exists: In some left connections, MySQL query optimizer the optimization method used by changing the composition of the original Query to partially subtract

Number of visits to minority sources