MySQL Database SQL statement execution efficiency check-explain command

The explain command is the first recommended command for solving database performance. Most performance problems can be solved simply by using this command. The explain command can be used to viewSQLThe statement execution result can help you select a better index and query statement, and write a better optimization statement.

Explain Syntax: Explain select... From... [Where...]

Example: Explain select * from news;

Output:

+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------- +


The following describes the attributes:

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

2. select_type: select_type is the select type, which can be:

Simple: simple select (Union or subquery is not used)

Primary: exclusive select

Union: the second or subsequent SELECT statement in Union

Dependent Union: the second or subsequent SELECT statement in union, depending on the external Query

Union result: the result of union.

Subquery: The first select in the subquery.

Dependent subquery: The first select in the subquery, depending on the external Query

Derived: Select (subquery of the from clause) of the export table)

3. Table: displays the data of this row about which table

4. Type: this column is the most important. It shows the category used for the connection and whether or not the index is used. It is one of the key items for analyzing the performance bottleneck using the explain command.

The result values are as follows:

System> const> eq_ref> ref> Fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> All

In general, make sure that the query reaches at least the range level, and it is best to reach the ref level. Otherwise, performance problems may occur.

5. possible_keys: indicates which index MySQL can use to find rows in the table.

6. Key: displays the key (INDEX) actually determined by MySQL ). If no index is selected, the key is null.

7. key_len: displays the key length determined by MySQL. If the key is null, the length is null. The length of the index used. The shorter the length, the better.

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

9. Rows: displays the number of rows that MySQL considers to be required for query execution.

10. Extra: contains detailed information about MySQL queries and is also a key reference item.

Distinct
Once MySQL finds the row that matches the row, it does not search any more.

Not exists
MySQL optimizes left join. Once it finds a row that matches the left join standard,

No more search

Range checked for each

Record (index map :#)
No ideal index is found. Therefore, for each row combination in the preceding table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.

Using filesort
At this point, the query needs to be optimized. MySQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.

Using Index
The column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in the table are part of the same index.

Using Temporary
When you see this, the query needs to be optimized. In this case, MySQL needs to create a temporary table to store the results. This usually happens when order by is applied to different column sets, rather than group.

Using where
The where clause is used to limit which rows match the next table or are returned to the user. If you do not want to return all rows in the table and the connection type is all or index, this may occur or the query is faulty.

Other tips:

1. If the value of type is "Index" and the value of extra is "Using Index", the index is overwritten.