Explan of MySQL

Time:2020-11-22

Using explain to query and analyze SQL execution records, you can optimize the performance of SQL!

Explain usage

mysql> explain select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set

mysql> explain extended select * from students;
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 |       |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

The meaning of explain result field

id

  • Select identifier. This is the query sequence number of select, indicating the order of executing the select clause or operation table in the query!

select_type

Select type, which can be any of the following:

  • Simple: simple select (without union or subquery)
  • Primary: the outermost select
  • Union: the second or subsequent select statement in a union
  • Dependent Union: the second or subsequent select statement in a union, depending on the external query
  • Union result: the result of union
  • Subquery: the first select in a subquery
  • Dependent subquery: the first select in the subquery, depending on the external query
  • Derived: Select of exported table (subquery of from clause)

table

The table referenced by the output row!

type

Connection type. The following shows the various join types, sorted from the best type to the worst type:

  • System: the table has only one row (= system table). This is a special case of the const join type.
  • Const: the table has at most one matching row that will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constant by the rest of the optimizer. Const tables are fast because they are read only once!
  • Eq_ Ref: for each row combination from the previous table, read one row from that table. This is probably the best join type, except for the const type.
  • For all rows in this table, the values are read from the preceding table row.
  • ref_ Or_ Null: the join type is like ref, but MySQL can be added to specifically search for rows containing null values.
  • index_ Merge: the join type indicates that the index merge optimization method is used.
  • unique_ Subquery: this type replaces the ref: value in (select primary) of the in subquery of the following form_ key FROM single_ table WHERE some_ expr) unique_ Subquery is an index lookup function, which can completely replace subquery and is more efficient.
  • index_ Subquery: the join type is similar to unique_ subquery。 The in subquery can be replaced, but only suitable for the non unique index in the following form of subquery: value in (select key_ column FROM single_ table WHERE some_ expr)
  • Range: retrieve only the rows of a given range, using an index to select 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 index files are usually smaller than data files.
  • All: performs a full table scan for each row combination from the previous table.

possible_keys

It points out which index MySQL can use to find records in the table. If there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query

key

Displays the index actually used by MySQL in the query. If no index is used, it is displayed as null

key_len

Represents the number of bytes used in the index. The length (key) of the index used in the query can be calculated by this column_ The value displayed by Len is the maximum possible length of the index field, not the actual length, i.e. key_ Len is calculated according to the table definition, not retrieved from the table)

ref

Represents the join matching criteria for the above table, that is, which columns or constants are used to find values on index columns

rows

Shows the number of rows MySQL thinks it must check when it executes the query. Multiply the data between multiple rows to estimate the number of rows to process

filtered

Shows an estimate of the percentage of rows filtered by the condition.

Extra

This column contains the details of MySQL resolving the query

  • Distinct: when MySQL finds the first matching row, it stops searching for more rows for the current row combination.
  • Not exists: MySQL can optimize the left join of the query. After finding a row that matches the left join standard, it no longer checks more rows in the table for the previous row combination.
    Range checked for each record (index map: ා): MySQL did not find a good index to use, but found that if the column values from the previous table are known, some indexes may be available.
  • Using filesort: MySQL needs an extra pass to find out how to retrieve rows in sort order.
  • Using index: retrieve column information from a table by using only the information in the index tree without further searching to read the actual rows.
  • Using temporary: to solve the query, MySQL needs to create a temporary table to hold the results.
  • Using where:WHERE Clause is used to restrict which row matches the next table or is sent to the customer.
  • Using sort_ union(… ), Using union(… ), Using intersect(… ): these functions show how to index_ Merge join type merge index scan.
  • Using index for group by: similar to the using index method to access a table, using index for group by means MySQL has found an index, which can be used to query all columns of group by or distinct queries, instead of searching the hard disk to access the actual table.

Explain using examples

mysql> show keys from students;
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY           |            1 | stud_id     | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| students |          1 | index_create_date |            1 | create_date | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
+----------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

mysql> explain  select * from students where stud_id = '1';
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set

mysql> explain  select * from students where create_date = '2010-01-01';
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | ref  | index_create_date | index_create_date | 4       | const |    1 | Using where |
+----+-------------+----------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set

summary

  • Explain doesn’t tell you about triggers, stored procedures, or the impact of user-defined functions on queries
  • Explain does not consider various caches
  • Explain does not display the optimization work done by MySQL during query execution
  • Some statistics are estimated, not exact
  • Expalin can only interpret the select operation. Other operations should be rewritten to view the execution plan after select.

This work adoptsCC agreementThe author and the link to this article must be indicated in the reprint

You’re far from it!