Mysql statement execution efficiency

Mysql viewing statement execution efficiency bitsCN.com

EXPLAIN

I. purpose:

1. When must I add an index to the table to obtain a faster select statement that can be remembered using the index?

2. check whether the optimizer joins the table in an optimal order.

The official documentation on explain in http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

II. usage:

EXPLAIN table_name

Or

Explain select select_options

The former can obtain the field structure of a table, and the latter mainly provides related index information. The latter focuses on the latter.

Eg:

Mysql> explain select * from event;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
|Id|Select_type|Table|Type|Possible_keys|Key|Key_len|Ref|Rows|Extra|
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
| 1 | SIMPLE | event | ALL | NULL | 13 |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- +
1 row in set (0.00 sec)
Meaning of each attribute

Id
Serial Number of the select query

Select_type
Select queries are different from complex queries such as common queries and joint queries and subqueries.

Table
The table referenced by the output row.

Type
The type used by the union query.
Type displays the access type and is an important indicator. 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 the range level at least, and it is best to reach the ref level.

Possible_keys
Specifies which index MySQL can use to find rows in the table. If it is null, there is no relevant index. To improve performance, you can check the WHERE clause to see if some fields are referenced or if the fields are not suitable for indexing.

Key
Displays the Keys actually determined by MySQL. If no index is selected, the key is NULL.

Key_len
Displays the key length determined by MySQL. If the key is NULL, the length is NULL. Note that this value can be used to determine which part of mysql is actually used in multiple primary keys.

Ref
Shows the field or constant used with the key.

Rows
This number indicates how much data mysql needs to traverse before it can be found, which is inaccurate in innodb.

Extra
If it is Only index, this means that information is Only retrieved from the index tree, which is faster than scanning the entire table.
If it is where used, the where restriction is applied.
If it is impossible where, it indicates that the where is not needed. generally, nothing is found.
If this information shows Using filesort or Using temporary, it will be very difficult, and the WHERE and order by indexes are often unable to take into account. if the index is determined by where, then in order, this will inevitably lead to Using filesort. it depends on whether filtering and sorting are cost-effective, or sorting and filtering are cost-effective.

/* Common glossary

Using filesort
MySQL requires an additional pass to find out how to retrieve rows in order.

Using index
You can use only the information in the index tree without further searching and reading the actual row to retrieve the column information in the table.

Using temporary
To solve the query, MySQL needs to create a temporary table to accommodate the results.

Ref
For each row combination from the preceding table, all rows that match the index value are read from this table.

ALL
If there is no index at all, the performance is very poor.

Index
Same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files.

SIMPLE
Simple SELECT (do not use UNION or subquery)

*/

BitsCN.com