MySQL's explain detailed

Source: Internet
Author: User

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. Using the method, add explain to the SELECT statement.
 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 |+----+-------------+---------+------+---------------+------+---------+------+------+-------+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, Extra, The following are explanations of the possible occurrences of these fields:
First, id     The identification of the order in which the SQL executes, the larger the ID, the higher the first execution 1. ID is the same, the execution sequence from top to bottom 2. If it is a subquery, the ID number is incremented, the higher the ID value, the higher the priority, and the first execution  3. If the ID is the same, it can be thought of as a group, executed from the top down; in all groups, the higher the ID value, the higher the priority, the more the first execution
Second, Select_type displays the type of each SELECT clause in the query (1simple Select, do not use union or subquery, etc.) (2PRIMARY (if the query contains any complex sub-parts, 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 outside query) (5) Union result (Result of Union) (6) subquery (the first select in a subquery) (7) DEPENDENT subquery (the first select in the subquery, depending on the outside query) (8) DERIVED (sub-query of a SELECT, from clause of a derived table) (9) Uncacheable subquery (the result of a subquery cannot be cached, the first row of the outer link must be reassessed)
Table shows which table the data in this row is about, sometimes not the actual table name, see Derivedx (X is a number that indicates the result of the first step)
Mysql> explainSelect* from(Select* from(Select* fromT1whereId=2602) a) 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| |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
  ref , Eq_ref,  Const  , System (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 The index Scan,index is distinguished from all as the index type only traverses the index tree range: Retrieves only the rows of a given range, using an index to select rows  ref . Span style= "COLOR: #000000": 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, and for each index key value, there is only one record match in the table, simply Is the use of primary key or unique key as an association condition in a multi-table connection  const , System: When MySQL optimizes a part of the query, and converted to a constant, these types are used for access. 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 
V. Possible_keys points out 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 the order in which the column is completely independent of the table 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
The Keykey column shows the keys (indexes) that MySQL actually decides to use if the index is not 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.
Key_len represents the number of bytes used in the index, which calculates the length of the index used in the query (Key_len displays the maximum possible length of the indexed field, not the actual length, that is, Key_len is calculated from the table definition, Not be retrieved through the table) without loss of accuracy, the shorter the better
Eight,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
wherewhere: 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

MySQL's explain detailed

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.