MySQL Tutorial: Introduction to the use of explain

Explain shows how MySQL uses indexes to process SELECT statements and join tables. can help you choose better indexes and write more optimized query statements.
How to: Add explain before the SELECT statement. such as EXPLAIN SELECT * from ' users '

Explanation of the Explain column:
Table shows how the data in this row is about
Type This is an important column that shows what type of connection is used. The connection types from best to worst are const, EQ_REG, ref, range, Indexhe, and all
Possible_keys displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement for the related field from the WHERE statement
The index that the key actually uses. If NULL, the index is not used. In rare cases, MySQL chooses to optimize an index that is insufficient. In this case, the use index (indexname) can be used in a SELECT statement to force an index or to force MySQL to ignore the index with ignore index (INDEXNAME)
The length of the index used by the Key_len. Without loss of accuracy, the shorter the length the better.
Ref shows which column of the index is used and, if possible, a constant
Rows MySQL think must be checked to return the requested data of the number of lines
Extra additional information about how MySQL resolves queries. The bad examples here are the using temporary and the using filesort, meaning MySQL can't use the index at all, and the result is a slow retrieval.

The meaning of the description returned by the Extra column
Distinct once MySQL finds a row that matches the row, it no longer searches for
not exists MySQL optimizes the left join, once it finds a row that matches the left join criteria, You no longer search the
Range checked for every
record (index map:#) does not find the ideal index, so with each row from the previous table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using the index
using Filesort when you see this, the query needs to be optimized. MySQL needs to take extra steps to find out how to sort the rows returned. It sorts all rows based on the type of connection and the row pointers of all rows that store the sort key values and matching criteria
the using index column data is returned from a table that only uses the information in the index and does not read the actual action. This occurs when the entire Request column for a table is 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 typically occurs when a different set of columns is placed on an order by instead of a group by
where used uses a WHERE clause to restrict which rows match the next table or return to the user. This occurs if you do not want to return all the rows in the table, and the connection type all or index, or if the query is problematic

Interpretation of different connection types (sorted in order of efficiency)
The system table has only one row: the system table. This is a special case of the const connection type
The maximum value of a record in the const table can match this query (the index can be a primary key or a unique index). Because there is only one line, this value is actually constant, because MySQL first reads the value and treats it as a constant.
Eq_ref in the connection, MySQL in the query, from the previous table, the union of each record reads a record from the table, which is used when the query uses all of the index primary key or 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 of the previous table, all records are read from the table. This type relies heavily on the number of records matched according to the index-the less the better
Range This type of connection uses an index to return rows in a range, such as what happens when you use > or < to find something
Index This connection type makes a full scan of each record in the previous table (better than all because the index is generally less than the table data)
All this connection type is completely scanned for each of the previous records, which is generally bad and should be avoided