MySQL view 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 second or subsequent SELECT statement in the outermost selectunion:union dependent The second or subsequent SELECT statement in union:union depends on the result of the outer query Union result:union. Subquery: The first selectdependent in a subquery subquery: The first select in a subquery, depending on the outer 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.

Const ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range > Inde x > 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.

checked  for  where clauses are 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. Not original.

MySQL view SQL statement execution efficiency