MySQL explain detailed

In the daily work, we will sometimes slow query to record some long-running SQL statements, to find out these SQL statements does not mean that it is done, we often use the explain command to view the execution plan of these SQL statements, to see whether the SQL statement is used index, There is no full table scan, which can be viewed through the explain command. So we have an in-depth look at the cost-based optimizer of MySQL, as well as the details of the access policies that might be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running the SQL statement. (Qep:sql generates an execution plan query execution plan)

Mysql> Explain select * from servers;+----+-------------+---------+------+---------------+------+---------+----- -+------+-------+| ID | Select_type | Table   | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-------+|  1 | Simple      | servers | All  | NULL          | NULL | NULL    | NULL |    1 | NULL  |+----+-------------+---------+------+---------------+------+---------+------+------+-------+1 row in Set (0.03 sec)

There are 10 columns of expain information, namely ID, select_type, table, type, Possible_keys, key, Key_len, ref, rows, and Extra, which can be explained in the following fields:

First, ID

My understanding is the identification of the order of SQL execution, SQL from large to small execution

1. Same ID, execution order from top to bottom

2. If it is a subquery, the ID number is incremented, the higher the ID value, the higher the priority, the more executed

3.id if the same, can be considered a group, from the top down in order to execute; In all groups, the higher the ID value, the higher the priority, the higher the first execution

Second, Select_type

the type of each SELECT clause in the display query

(1) Simple (easy Select, do not use union or sub-query, etc.)

(2) PRIMARY (if any complex sub-parts are included in the query, the outermost select is marked as PRIMARY)

(3) Union (the second or subsequent SELECT statement in the Union)

(4) DEPENDENT Union (the second or subsequent SELECT statement in Union, depending on the query outside)

(5) Union result (Results of Union)

(6) Subquery (the first select in a subquery)

(7) DEPENDENT subquery (the first select in a subquery, depending on the query outside)

(8) DERIVED (select of derived table, subquery from clause)

(9) Uncacheable subquery (the result of a subquery cannot be cached, the first row of the outer link must be re-evaluated)

Third, table

Show the data in this row about which table, sometimes not the actual table name, see Derivedx (X is a number, my understanding is the result of the first few steps)

mysql> Explain Select (SELECT * from T1 where id=2602) b;+----+-------------+------------+--- -----+-------------------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  3 | DERIVED |     T1 |         Const  | primary,idx_t1_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

Iv. type

Indicates how MySQL finds the desired row in the table, also known as the "access type."

Common types are: all, index, range, ref, EQ_REF, const, System, NULL (from left to right, performance from poor to good)

All:full table Scan, MySQL will traverse the full table to find a matching row

Index:full index Scan,index is different from all for index type only traversal index tree

Range: Retrieves only the rows for a given range, using an index to select rows

Ref: Represents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index column

Eq_ref: Similar to ref, the difference is that the index used is a unique index, for each index key value, there is only one record match in the table, simply, the use of primary key or unique key as the association condition in a multi-table connection

Const, System: Use these types of access when MySQL optimizes a part of the query and converts it to a constant. If you put the primary key in the where list, MySQL can convert the query to a constant, the system is a special case of the const type, and when the queried table has only one row, use the system

Null:mysql decomposes statements during optimization, and does not even have access to tables or indexes, such as selecting a minimum value from an indexed column to be completed by a separate index.

Wu, Possible_keys

Indicates which index MySQL can use to find records in the table, and if there are indexes on the fields involved in the query, the index will be listed but not necessarily used by the query.

The column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.
If the column is null, there is no index associated with it. In this case, you can improve your query performance by examining the WHERE clause to see if it references some columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

Six, Key

The key column shows the keys (indexes) that MySQL actually decides to use

If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

Seven, Key_len

Represents the number of bytes used in the index, which calculates the length of the index used in the query (the value displayed by Key_len is the maximum possible length of the indexed field, not the actual length, i.e., Key_len is computed from the table definition, not retrieved from the table)

Without loss of accuracy, the shorter the better

Viii. ref

Represents the connection matching criteria for the above table, that is, which columns or constants are used to find the value on the index column

Nine, rows

indicates that MySQL estimates the number of rows to be read to find the required records based on table statistics and index selection

Ten, Extra

This column contains the details of the MySQL resolution query, in the following cases:

Using Where: 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, indicating that the MySQL server will filter after the storage engine retrieves the rows

Using temporary: Indicates that MySQL needs to use temporary tables to store result sets, which are common in sorting and grouping queries

Sort operations that cannot be done with indexes in the using Filesort:mysql are called "File sorting"

Using join buffer: The change emphasizes that the index is not used when getting the join condition, and the connection buffer is required to store the intermediate result. If this value is present, it should be noted that depending on the specifics of the query, you may need to add an index to improve the performance.

Impossible where: This value emphasizes that the where statement results in rows that do not meet the criteria.

Select tables Optimized away: This value means that the optimizer may return only one row from the result of an aggregate function by using the index only

Summarize:
? Explain won't tell you about triggers, stored procedures, or user-defined functions that affect queries
? Explain does not consider the various caches
? Explain cannot display the optimizations that MySQL made when executing queries
? Some of the statistics are estimates, not exact values
? Expalin can only interpret the select operation, and other operations are rewritten as Select to view the execution plan.

Reference: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

Http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

Http://www.cnblogs.com/gomysql/p/3720123.html

MySQL explain detailed