MySQL explain usage (optimized query)

　　explain shows how MySQL uses indexes to process SELECT statements and join tables. can help select better indexes and write more optimized query statements .

1. Create a database

The SQL statements are created as follows:

/*Navicat MySQL Data Transfer source Server:localhost-newpassword source Server version:50550 Source Host  : localhost Source database:testexplain Target Server version:50550 File encoding:utf-8 Date: 08/05/2016 18:06:12 PM*/SETNAMES UTF8;SETForeign_key_checks= 0;-- ------------------------------Table structure for ' user '-- ----------------------------DROP TABLE IF EXISTS`User`;CREATE TABLE`User' (' ID 'int( One) not NULL, ' name 'varchar( -) not NULL, ' age 'int( One) not NULL, ' sex 'tinyint(4) not NULL, ' isDeleted 'tinyint(4) not NULL,  PRIMARY KEY(' id '),UNIQUE KEY' Id_unidx ' (' id ') USING BTREE,UNIQUE KEY' Name_unidx ' (' name ') USING BTREE) ENGINE=InnoDBDEFAULTCHARSET=latin1;-- ------------------------------Records of ' user '-- ----------------------------BEGIN;INSERT  into`User`VALUES('1','wwwwe',' One','1','0'), ('2','222',' A','1','0'), ('3','2222','Ten','0','0');COMMIT;SETForeign_key_checks= 1;

2. How to use explain

How to use: Add explain before the SELECT statement, such as:

Select *  from User where = 1

Results:

+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+|Id|Select_type| Table |Type|Possible_keys| Key     |Key_len|Ref|Rows|Extra|+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+| 1  |Simple| User  |Const| PRIMARY, Id_unidx| PRIMARY | 4       |Const| 1    |       |+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+

3, explain each parameter explanation

ID: select identifier. This is the Select query serial number. This is not important, and the query ordinal is the order in which the SQL statement executes.

Select_type mainly have the following values:

• Simple It represents a straightforward select with no union and subquery
• Primary The outermost select, in the statement with the subquery, the outermost select query is primary
• The second of the Union Union statement, or the one that follows.
• Dependent Union The second or subsequent SELECT statement in the Union, depending on the outside query
• Union result Result of Union

table: The tables used for the output rows

Type: Shows what type of connection is used. the best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all

• 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's important to remember that you must use primary KEY or unique
• eq_reg: The MySQL manual reads: "For each row combination from the previous table, read one row from the 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: Ref for each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or primary key (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good.
• Range: Searches within a given range, using an index to examine rows.
• 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. (That is, although all and index are read-only, index is read from the index and all is read from the hard disk)
• all perform a full table scan for each row combination from the previous table. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.

Possible_keys: Displays the indexes that may be applied to this table. If it is empty, there is no possible index. You can select an appropriate statement from the where statement for the related domain

key: The actual index used. If NULL, the index is not used. In rare cases, MySQL chooses an index that is poorly optimized. In this case, use Index (indexname) can be used in the SELECT statement to force an index or use ignore index (indexname) to force MySQL to ignore the index

Key_len: The length of the index used. The shorter the length the better, without loss of accuracy

ref: Shows which column of the index is being used and, if possible, a constant

rows: MySQL considers the number of rows that must be checked to return the requested data, the larger the value, the better the index

Extra: Additional information on how MySQL resolves queries. Using temporary and using Filesort are the worst cases , meaning MySQL simply cannot use the index , and the result is that the retrieval will be slow

The meaning of the description returned by the extra column

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

Not Exists:mysql optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches for

Range checked for each record (index map:#): No ideal index was found, so for every combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

Using Filesort: When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

Using index: 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

Using temporary when you see this, the query needs to be optimized. here, MySQL needs to create a temporary table to store the results, which usually occurs on an order by on a different set of columns, rather than on the group by

Where used uses a WHERE clause to restrict which rows will match the next table or return 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 different connection types of interpretation (in order of efficiency)

The system table has only one row: the system table. This is a special case of the const connection type

Const: The maximum value of a record in a table can match this query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads the value first and treats it as a constant.

Eq_ref: In a connection, when MySQL queries, from the previous table, the union of each record reads a record from the table, which is used when the query uses the index as the primary key or the unique key.

Ref: This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row union of the previous table, all records are read from the table. This type is heavily dependent on how many records are matched against the index-the less the better

Range: This connection type uses an index to return rows in a range, such as what happens when you use > or < to find something

Index: This connection type is fully scanned for each record in the previous table (better than all because the index is generally less than the table data)

All: This connection type is fully scanned for each previous record, which is generally bad and should be avoided as much as possible.

　　 Thanks: Thank you for reading!

MySQL explain usage (optimized query)