Proficient in MySQL master explain

Explanation of the Explain column:

Table: Shows which table the data in this row is about

Type: This is an important column that shows what type the connection uses. 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

Key: The index that is actually used. 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)

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

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

The number of rows that the Rows:mysql considers must be checked to return the requested data

Extra: Additional information about how MySQL resolves queries. will be discussed in Table 4.3, but here is a bad example of using temporary and using filesort, meaning MySQL cannot use indexes at all, and the result is a slow retrieval

The meaning of the description that the extra column returns

Distinct: Once MySQL finds a row that matches the row, it stops searching.

The not Exists:mysql optimizes the left join, and once it finds a row that matches the left join criterion, it stops searching

Range checked for every record (index map:#): No ideal index is found, so for 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 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 only uses the information in the index without reading the actual action, which occurs when the entire Request column for the 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 on an order by, not a group by

Where used uses a WHERE clause to restrict which rows match the next table or return to the user. If you do not want to return all the rows in the table, and the connection type all or index, this occurs, or the query has a problem. 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

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 line, this value is actually constant, because MySQL first reads the value and treats it as a constant.

Eq_ref: In the 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 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 connection type uses the 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