MySQL optimization--explain analysis of SQL statement execution efficiency

The explain command is the first recommended command to address database performance, and most performance issues can be easily resolved with this command, explain can be used to see how SQL statements are executed, to help select better indexes and refine query statements, and to write better optimization statements.

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

For example: Explain select * from news;

Output:

+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------


Here's a look at the individual properties:

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: Easy Select (Do not use union or subquery, etc.)

PRIMARY: The outermost Select

The second or subsequent SELECT statement in a union:union

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

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 outside query

DERIVED: Export table's SELECT (subquery FROM clause)

3, table: Shows the data of this line is about which table

4. Type: This column is the most important, showing which category the connection uses, whether or not the index is used, and is one of the keys to analyzing the performance bottleneck using the explain command.

The result 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, you must ensure that the query reaches at least the range level, preferably ref, or performance issues may occur.

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

6. Key: Displays the key (index) that MySQL actually decides to use. If no index is selected, the key is null

7. Key_len: Displays the key length that MySQL decides to use. If the key is null, the length is null. The length of the index to use. The shorter the length the better, without loss of accuracy

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

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

10. Extra: Contains the details of MySQL solution query and is also one of the key reference items.

Distinct
Once MySQL finds a row that matches the row, it no longer searches for

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

We don't search anymore.

Range checked for each

Record (Index map:#)
The ideal index was not found, so for each combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using Filesort
When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

Using Index
Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index

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 usually occurs on an order by on a different set of columns, rather than on the group by

Using where
A WHERE clause is used to restrict which rows will 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 all or index, this occurs, or the query has a problem

Some other tip:

1. When the type is displayed as "index" and extra is displayed as "using index", the overwrite index is used.

MySQL optimization--explain parsing SQL statement execution efficiency