MySQL: Use explain to optimize query performance

Explain introduction

In order to optimize the execution performance of MySQL's SQL statement, MySQL provides the explain keyword to view the SQL execution plan.
The format is as follows:

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

The DESCRIBE and EXPLAIN statements are synonymous. In fact, the DESCRIBE keyword is more commonly used to obtain information about the structure of the table, while EXPLAIN is used to obtain the query execution plan (that is, to explain how MySQL will execute the query).

It can be seen from the usage of EXPLAIN above:

• EXPLAIN can be used with SELECT, DELETE, INSERT, REPLACE and UPDATE to query the execution plan of the corresponding SQL.
• When EXPLAIN is used with an explainable statement, MySQL displays information about the statement execution plan from the optimizer. In other words, MySQL explained how it will handle the statement, including information about how to join tables and in what order.
• When EXPLAIN is used with FOR CONNECTION connect_id instead of an interpretable statement, it will display the execution plan of the statement executed in the named connection.
• For the SELECT statement, EXPLAIN can use other additional execution plan information displayed by the SHOW WARNINGS statement.
• EXPLAIN is useful for checking queries involving partitioned tables.
• The FORMAT option can be used to select the output format. TRADITIONAL displays output in tabular format, the default is TRADITIONAL, and JSON format displays information in JSON format.

With the help of EXPLAIN, you can see where you should add an index to the table so that the statement can be executed faster by using index lookup. You can also use EXPLAIN to check whether the optimizer joins the table in the best order.

When EXPLAIN is used with a SELECT statement, the results of EXPLAIN are displayed and output in a table format, with each row representing a table. MYSQL uses a loop-embedded method to parse the connections of all tables, which means that MYSQL will first read the first row of the first table, then find the matching row in the second table, then the third table, etc. When all the tables are processed, MySQL outputs the selected columns and backtracks all the tables until it finds a table with more matching rows. Read the next row from this table and continue processing the next table.

Explain output

Each output row in EXPLAIN provides information about a table.
The output of EXPLAIN is as follows (the second column is the output when FORMAT=JSON):

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

The following describes each of the above columns one by one:
id : This is the serial number of the SELECT in the query. If the row refers to the UNION result of other rows, the value can be NULL. In this case, the table column displays a value similar to <unionM, N> to indicate that the row refers to the union of rows whose id values are M and N.

explain select * from small_note.small_note_detail where id = 5 union select * from small_note.small_note_detail where id = 6;

The results are as follows:

select_type : The value of select_type is as follows:

select_type Value	Meaning
SIMPLE	Simple query, without using UNION and subqueries
PRIMARY	The outermost SELECT statement
UNION	The second or later SELECT statement in UNION
DEPENDENT UNION	The second or later SELECT statement in UNION depends on the external query
UNION RESULT	The result of UNION, because it does not need to participate in the query, so the id field is NULL
SUBQUERY	Except for the subqueries contained in the from clause, subqueries appearing elsewhere may be of type SUBQUERY
DEPENDENT SUBQUERY	The first SELECT statement in the subquery depends on the external query. For each set of different values of the variables in the context, the subquery is only recalculated once
DERIVED	The subquery that appears in the FROM statement is also called a derived table. When the FROM statement contains multiple SELECT statements, the select_type of the first SELECT statement may also be DERIVED
MATERIALIZED	Materialized word query
UNCACHEABLE SUBQUERY	The results of the subquery cannot be cached, and each row of the external query needs to be recalculated
UNCACHEABLE UNION	The second or later SELECT statement in UNION belongs to UNCACHEABLE SUBQUERY

DEPENDENT SUBQUERY is different from UNCACHEABLE SUBQUERY. For DEPENDENT SUBQUERY, the subquery is only recalculated once for each group of different values of the variables in the external context. For UNCACHEABLE SUBQUERY, the subquery will be recalculated for each row of the external context.

The select_type of a non-SELECT statement is the type of the statement, for example, for a DELETE statement, the select type is DELETE.

table : The name of the table. In addition to the name of the table, this can also be one of the following values.

<unionM, N>: This row represents the union of rows whose id values are M and N.

<derived N>: This row refers to the derived table result of the row whose id value is N. For example, the derived table may come from a subquery in the from clause.

<subquery N>: This row refers to the result of the materialized subquery for the row whose id value is N.

partitions : Query matching partitions. For non-partitioned tables, the value is NULL.

type : The association type determines how to find each row of data. The following is in descending order of speed.
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。

• system, there is only one row of records in the table, which is equivalent to the system table;
• const, the table has at most one matching row, which is read at the beginning of the query. Because there is only one row, the rest of the optimizer can treat the values of the columns in this row as constants. Constant tables are very fast because they are only read once. When comparing all parts of the primary key or unique index with a constant value, the type will be const.
• eq_ref, reads a row of each row in this table and the associated table, that is, only one piece of data is returned. In addition to the system and const types, this is the best connection type. When the join uses all parts of the index, and the index is the primary key or the only non-empty index, the type is eq_ref.
• ref, read all rows with matching index values from this table. If the join uses only the leftmost prefix of the key, or the key is not a primary key or unique index (in other words, if the join cannot select a single row based on the key value), use ref. If the key used only matches a few rows, then this is a good type of join. ref can be used for indexed columns that are compared using the = or <=> operator.
• fulltext, use FULLTEXT index
• ref_or_null, similar to ref, but a query is needed to find NULL data.
• index_merge, when multiple indexes are used for single-table query (cannot merge across tables), each index may return a result, and Mysql will union and intersection of the results. This is index merging.
• unique_subquery, a unique index is used in the subquery of in, and sometimes unique_subquery is used instead of eq_ref
• index_subquery, similar to unique_subquery, only for non-unique indexes.
• range, only retrieve rows in a given range, use an index to select rows, generally used for between, <, >;
• index, only traverse the index tree;
• all, full table scan;

possible_keys : Indicates that MySQL can choose the index from which to find rows in this table. Note that this column is completely independent of the order of the tables displayed in the EXPLAIN output. This means that some keys in possible_keys may not be available in the actual generated table order.

key : Indicates the key (index) that MySQL actually decides to use. If MySQL decides to use an index in possible_keys to find a row, the index will be listed as the key value. The key may also be an index that does not exist in possible_keys. This may happen if all possible_keys are not suitable for finding rows, but all columns selected by the query are columns of other indexes. That is, the named index covers the selected column, so although it is not used to determine the rows to be retrieved, the index scan is more efficient than the data row scan.

key_len : The key_len column indicates the length (bytes) of the key that MySQL decides to use. The char is 4 bytes. It is allowed to be NULL and requires an extra byte. Indefinite length requires an extra 2 bytes to store the length. If the key column indicates NULL, the key_len column also indicates NULL.

ref : Show which field of which table is associated with the index field of the table;

1617bb82220ecb rows rows that MySQL thinks must be checked to execute the query.

filtered : the percentage of the number of rows returned in the result to the number of read rows, the larger the value, the better;

extra : Contains extra information that is not suitable for display in other columns but is very important. Common values are as follows:

• Using filesort, MySQL will use an external index to sort the data instead of reading in the order of the indexes in the table. If there is a change in the value, the SQL statement should be optimized;
• Using temporary, use a temporary table to cache intermediate results. For example, MySQL uses a temporary table when sorting query results, which is common in order by and group by. If this value appears, the SQL should be optimized;
• Using index, only use the information in the index tree to retrieve column information from the table, without additional lookups to read the actual rows. This strategy can be used when the query uses only columns that belong to a single index. Indicates that the select operation uses a covering index, which avoids accessing the data rows of the table;
• Using index condition, use index push down, index push down simply means adding conditional filtering, need to return to the table, but reduce the operation of returning to the table.
• using where, where clause is used to limit which line;
• using join buffer, use connection cache;
• distinct, after finding the first match, stop searching for more rows for the current row combination;