MySQL EXPLAIN Result set analysis – with lots of cases

Lots of examples to help you understand Explain output content, easy to handle slow queries

EXPLAIN: View the execution plan of SQL statements

The EXPLAIN command can provide insight into MySQL’s overhead based optimizer, as well as many details about the access policies that the optimizer may take into account, and which policies are expected to be adopted by the optimizer when running SQL statements, which can be useful when optimizing slow queries

After executing Explain, the result set contains the following information

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
Copy the code

Each of these values is explained below

1, id

Id is used to identify the order of SELELCT statements in the entire query. In nested queries, statements with larger IDS are executed first. The value may be NULL

If the ids are the same, run them from top to bottom. A larger ID indicates a higher execution priority. If a row references the union result of other rows, the value can be NULL

2, select_type

Select_type specifies the type used for the query. The options are as follows:

Simple: A simple select query without union or subquery

mysql> explain select * from  test where id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
Copy the code

Primary: the outermost select query

mysql> explain select * from (select * from test whereid = 1000) a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type  | table |type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test       | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
Copy the code

Union: The second or subsequent SELECT query in the union, independent of the result set of the external query

mysql> explain select * from test where id = 1000 union all select * from test2; +----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY |test       | const | PRIMARY       | PRIMARY | 8       | const |     1 | NULL            |
|  2 | UNION        | test2 | | NULL ALL | NULL | NULL | NULL | 67993 | NULL | | NULL | UNION RESULT | < 2 > union1, | | NULL ALL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+Copy the code

Dependent Union: The second or subsequent select query in the union that depends on the result set of the external query

mysql> explain select * from test where id in (select id  from test where id = 1000 union all select id from test2); +----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+ | id | select_type | table |type   | possible_keys | key     | key_len | ref   | rows  | Extra           |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|  1 | PRIMARY            | test       | ALL    | NULL          | NULL    | NULL    | NULL  | 68505 | Using where     |
|  2 | DEPENDENT SUBQUERY | test       | const  | PRIMARY       | PRIMARY | 8       | const |     1 | Using index     |
|  3 | DEPENDENT UNION    | test2 | eq_ref | PRIMARY | PRIMARY 8 | | func | 1 | Using index | | NULL | UNION RESULT | < union2, 3 > | | NULL ALL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+Copy the code

Subquery: The first SELECT query in a subquery, independent of the result set of the external query

mysql> explain select * from test where id = (select id from test whereid = 1000); +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type  | table |type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY     | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL        |
|  2 | SUBQUERY    | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
Copy the code

Dependent subquery: The first select query in a subquery that depends on the result set of the external query

mysql> explain select * from test where id in (select id  from test where id = 1000 union all select id from test2); +----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+ | id | select_type | table |type   | possible_keys | key     | key_len | ref   | rows  | Extra           |
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|  1 | PRIMARY            | test       | ALL    | NULL          | NULL    | NULL    | NULL  | 68505 | Using where     |
|  2 | DEPENDENT SUBQUERY | test       | const  | PRIMARY       | PRIMARY | 8       | const |     1 | Using index     |
|  3 | DEPENDENT UNION    | test2 | eq_ref | PRIMARY | PRIMARY 8 | | func | 1 | Using index | | NULL | UNION RESULT | < union2, 3 > | | NULL ALL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+Copy the code

Derived: Used when there are subqueries in the FROM clause that mysql recursively executes, and the result set is placed in a temporary table

mysql> explain select * from (select * from test2 whereid = 1000)a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type  | table |type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test2      | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
Copy the code

3, table

Table is used to represent the name of the table referenced by the output row

4. Type (important)

Type represents the access type, and the various types are explained in sequence, from best to worst

System: The table has only one row and is a special case of const type

mysql> explain select * from (select * from test2 whereid = 1000)a; +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+ | id | select_type  | table |type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | test2      | const  | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
Copy the code

Because the subquery has only one row of data, simulating a single table with only one row of data, type is system

Const: When it is determined that only one row matches, the mysql optimizer reads it and only reads it once before querying, which is very fast

mysql> explain select * from test where id =1 ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
Copy the code

Eq_ref: Reads a row from the table for each combination of rows from the previous table, usually with an index that is unique or primary key

mysql> explain select * from test.test2 where test.com_key=test2.com_key; +----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+ | id  | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- + | 1 | SIMPLE |test2 | ALL    | IDX(com_key)  | NULL         | NULL    | NULL               | 67993 | NULL  |
|  1 | SIMPLE      | test  | eq_ref | IDX(com_key)  | IDX(com_key) | 194     | test.test2.com_key |     1 | NULL  |
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
Copy the code

Ref: For combinations of rows from the previous table, all rows with matching index values are read from the 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 a KEY)

Ref can be used for indexed columns that use the = or <=> operators

mysql> explain select * from test ,test2 where test.bnet_id=test2.aid; +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+--------------------- --+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+--------------------- --+ | 1 | SIMPLE |test  | ALL  | NULL          | NULL    | NULL    | NULL              | 68505 | Using where           |
|  1 | SIMPLE      | test2 | ref | idx_aid | idx_aid | 5 | test.test.bnet_id | 34266 | Using index condition | +----+-------------+-------+------+---------------+---------+---------+-------------------+-------+--------------------- --+Copy the code

Select bnet_id from test and aid from test2

Ref_or_null: Similar to ref, but with rows that can be specifically searched for null values

mysql> explain select * from test wherebnet_id=1 or bnet_id is null; +----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+ |  id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+ |  1 | SIMPLE |test  | ref_or_null | idx_bnet      | idx_bnet | 9       | const |    2 | Using index condition |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
Copy the code

The bnet_id column is the index, and the bnet_id column has a null value

Index_merge: This access type uses the index merge optimization method, where the key column contains a list of indexes used and key_len contains the longest key element of the indexes used

mysql> explain select * from test whereid = 1 or bnet_id = 1; +----+-------------+-------+-------------+------------------+------------------+---------+------+------+---------------- ----------------------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+------------------+------------------+---------+------+------+---------------- ----------------------------+ | 1 | SIMPLE |test| index_merge | PRIMARY, idx_bnet | PRIMARY idx_bnet, 8, 9 | | NULL | 2 | Using union (PRIMARY, idx_bnet); Usingwhere| +----+-------------+-------+-------------+------------------+------------------+---------+------+------+---------------- ----------------------------+Copy the code

The condition is that both id and bnet_ID columns have single-column indexes. If index_merge occurs, and this type of SQL is used frequently later on, you can consider replacing single-column indexes with composite indexes for greater efficiency

Range: Retrieves only rows in a given range, using an index to select rows. The key column shows which index is used. Key_len contains the longest key element of the index used. Ref is listed as NULL in this type

You can use range when comparing keyword columns with constants using the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN operators

mysql> explain select * from test where bnet_id > 1000 and  bnet_id < 10000;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE |test  | range | idx_bnet      | idx_bnet | 9       | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
Copy the code

The bnet_id column has an index

Index: Very common when doing statistics, this join type actually scans the index tree

mysql> explain select count(*) from test;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_bnet | 9       | NULL | 68505 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
Copy the code

All: For each combination of rows from the previous table, a full table scan can usually add more indexes than all, so that the rows can be retrieved based on constant values or column values in the previous table

mysql> explain select *  from test where create_time = '0000-00-00 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 68505 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
Copy the code

5, possible_keys

Possible_keys means that in this SQL, mysql can use the index to assist in finding records. When querying, all fields involved in possible_keys will be listed, but not necessarily used in the query. If it is empty, no index is available. In this case, you can improve performance by checking the WHERE statement to see if you can reference certain columns or create new indexes.

6. Key (important)

The key column displays the actual INDEX used by the current table. If no INDEX is selected, the column is NULL. To FORCE MySQL to USE or IGNORE the possible_keys INDEX, USE FORCE INDEX, or IGNORE INDEX in the query

7, key_len

The key_len column shows the key length MySQL has decided to use. If the KEY KEY is NULL, the length is NULL. With no loss of accuracy, the shorter the length, the better

The length of key len also depends on the character set. Latin1 is 1 byte, GBK is 2 bytes, and UTf8 is 3 bytes. Calculation method of key_len:

The column type	KEY_LEN	note
id int	key_len = 4+1	Int is 4bytes, NULL is allowed, and 1byte is added
id bigint not null	key_len=8	Bigint is 8 bytes
user char(30) utf8	key_len=30*3+1	Each utf8 character is 3bytes, NULL is allowed, and 1byte is added
user varchar(30) not null utf8	key_len=30*3+2	Utf8 is 3bytes per character, variable-length data type, plus 2bytes
user varchar(30) utf8	key_len=30*3+2+1	Utf8 each character is 3bytes, NULL allowed, plus 1byte, variable length data type, plus 2bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT intercepts, which are considered dynamic column types.

Key_len indicates only the selected index columns in where that are used for conditional filtering. The selected index columns do not contain the order by or group BY sections

8 ref.

The ref column is used to show which column or constant is used with the key to select the corresponding row from the table. The name (or const) of the column it displays, which is null most of the time

9 rows.

The rows column shows the number of rows that the mysql parser thinks must be scanned to execute this SQL. This value is an estimate, not a specific value, and is usually smaller than the actual value

10, filtered,

When a JOIN is used, the size of the result set of the previous table directly affects the number of rows in the loop

11. Extra

Extra represents additional information that is not in other columns and is also important

Using index: This value indicates that the SQL statement uses an overwrite index, which is most efficient when the desired result can be obtained directly from the index column without having to go back to the table

mysql> explain select id from test;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_bnet | 9       | NULL | 68505 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
Copy the code

In this example, the id column is the primary key, but the key column is not the primary key index. This is because all secondary indexes in mysql contain all primary key information. Mysql does not store primary key indexes separately, so the overhead of scanning secondary indexes is faster than that of full table scanning

Using WHERE: indicates that the storage engine conducts post-filter. If the query fails to use the index, using WHERE simply reminds us that mysql uses the WHERE condition to FILTER the result set

mysql> explain select * from test whereid > 1; +----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+ | id | select_type  | table |type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 8       | NULL | 34252 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
Copy the code

Using temporary indicates that mysql needs to use temporary tables to store result sets. This is common for sorting and grouping queries

mysql> explain select * from test where id in(1, 2) group by bnet_id; +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+-------- --------------------------------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+-------- --------------------------------------+ | 1 | SIMPLE |test  | range | PRIMARY,IDX(event_key-bnet_Id),idx_bnet | PRIMARY | 8       | NULL |    2 | Using where; Using temporary; Using filesort | +----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+-------- --------------------------------------+Copy the code

Using filesort: mysql does not have the ability to use indexes to sort columns

mysql> explain select * from test order by bnet_id;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 68505 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
Copy the code

Using join buffer: Emphasizes that no index is used to obtain join conditions and that the join buffer is needed to store intermediate results. (Performance can be improved by adding indexes or modifying connection fields)

mysql> explain select * from test left join test2 on test.create_time = test2.create_time; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------ ----------------------------------------+ | id | select_type | table | partitions |type| possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------ ----------------------------------------+ | 1 | SIMPLE |test| NULL | | NULL ALL | NULL | NULL | NULL | 959692 | | NULL 100.00 | | SIMPLE | | 1test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 958353 |   100.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------ ----------------------------------------+ 2 rowsin set, 1 warning (0.00 sec)
Copy the code

Nested Block Loop refers to the Block nested-loop Join algorithm: the row/result set of the outer Loop is stored in the Join buffer, and each row of the inner Loop is compared with the records in the entire buffer to reduce the number of inner loops.

Impossible WHERE: Indicates a row that is not returned because of the WHERE condition

mysql> explain select * from test whereid is null; +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type  | table |type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
Copy the code

Using index condition: is a new feature added after mysql 5.6. It is used in conjunction with index Condition Pushdown (ICP) of mysql. The main optimization is that like lookups can be performed on indexes (secondary indexes only)

If more than one of the above results occurs in extra, the above method is used to parse the query sequentially

---

Related articles recommended reading:

• Small and medium-sized teams quickly build SQL automatic audit systems
• Automation of data migration for operation and maintenance efficiency