MySQL explain field explanation

1.select_type

　　Simple: It represents a single select, with no union and sub-query

Dependent union:union dependent outer-layer query

Dependent subquery:subquery dependent outer-layer query

2.type

System: Table has only one row, this is a const type of special column, usually does not appear, usually appear in MySQL built system table

Const: The table has a maximum of one matching row, and the const is used to compare primary key or unique index. Because it matches only one row of data, it quickly

　　Eq_ref: For each row from the previous table (join table), only one row is read from the table (the joined table). This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or PRIMARY KEY ". Eq_ref can be used to compare indexed columns with =.

ref :for each row combination from the preceding table, all rows with matching index values are read from this table. If you don't meet a one-off line,, the index is used, using theref. If you use a key that matches only a few rows, the join type is good.

　　Range : Retrieves within a given range, such as in (xx,xxxx) or between

　　Index: The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file, and is typically used with overlay index optimizations

　　All: a complete table scan for each row combination from the previous table.

Index_merge: The join type indicates that the use of the index merge optimization method is common such as: Idx1 =? or idx2 =?

Key

The index used by the MySQL execution plan

Ref

　The field that is connected to the front table, and the const represents a constant value connection

rows  

Displays the number of rows that MySQL executes the query, simple and important, the larger the value the worse

Extra

　　DISTINCT: Once MySQL finds a row that matches a row, it no longer searches for

　　Range checked for each: No ideal index is found, so for every combination of rows from the preceding table,MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections to use the index

　　Using Filesort: A hard disk or memory sort has occurred, typically triggered by an order by or group by; Typically, the sorted field is not a field of the driver table, then the data is added using a temporary table, sorted, if the data is large, the hard disk is sorted, if the memory is small .

　　Using index : column data is returned from a table that uses only the information in the index without reading the actual action, and uses the overlay index optimization

　　Using temporary : temporary tables are used, usually with filesort. Typical scenario: a derived table is used.

　　Using Where: The database service layer extracts data from the storage engine and makes a conditional filter

The using index CONDITION:ICP is optimized, which is provided after 5.6. Pushing the filter to the storage engine layer performs better using composite indexes to filter data and reduce IO.

　　

MySQL explain field explanation