preface

The previous article introduced the scenario of index invalidation( Talk about the failure of MySQL index ), the explain execution plan is used. The execution plan returns the information of each step in the execution process instead of executing it.

Through the returned one or more rows of information, each part of the execution plan and the execution order are displayed, so that the performance bottleneck of query statement or table structure can be found from the analysis results.

Today, let's give some practical examples to explain the meaning of the fields in the explain execution plan.

First, establish user, role, relationship and partition table.

CREATE TABLE table_user(
id INT AUTO_INCREMENT,
user VARCHAR(30),
pwd VARCHAR(30),
description VARCHAR(90),
PRIMARY KEY (id));

CREATE TABLE table_role(
id INT AUTO_INCREMENT,
name VARCHAR(30),
description VARCHAR(90),
PRIMARY KEY (id));

CREATE TABLE table_relation(
id INT AUTO_INCREMENT,
user_id INT,
role_id INT,
FOREIGN KEY (user_id) REFERENCES table_user (id),
FOREIGN KEY (role_id) REFERENCES table_role (id),
PRIMARY KEY (id));

CREATE TABLE table_partitions(
id INT AUTO_INCREMENT,
name VARCHAR(30),
age INT,
address VARCHAR(30),
PRIMARY KEY (id))PARTITION BY HASH(id) PARTITIONS 2;

Insert some data.

insert into table_user(user,pwd,description) value('tony','abc123','admin');
insert into table_user(user,pwd,description) value('tom','123456','general user');
insert into table_user(user,pwd,description) value('jerry','123456','general user');

insert into table_role(name,description) value('admin','admin role');
insert into table_role(name,description) value('general','general role');

insert into table_relation(user_id,role_id) value(1,1);
insert into table_relation(user_id,role_id) value(2,2);
insert into table_relation(user_id,role_id) value(3,2);

insert into table_partitions(name,age,address) value('wang',21,'shenzhen');
insert into table_partitions(name,age,address) value('zhang',23,'shanghai');
insert into table_partitions(name,age,address) value('li',26,'beijing');

Create index.

CREATE INDEX index_age ON table_partitions(age);
CREATE INDEX index_name_age ON table_partitions(name,age);

1, id field

The sequence number of the select query indicates the order in which the select clause or operation table is executed in the query. The larger the id value, the higher the priority, and the earlier it is executed.

explain select * from table_role,table_user; #Because of the typesetting problem, some information was removed
+----+-------------+------------+------------+------+---------------+------+
| id | select_type | table      | partitions | type | possible_keys | key  |
+----+-------------+------------+------------+------+---------------+------+
|  1 | SIMPLE      | table_role | NULL       | ALL  | NULL          | NULL |
|  1 | SIMPLE      | table_user | NULL       | ALL  | NULL          | NULL |
+----+-------------+------------+------------+------+---------------+------+

explain select * from table_relation where role_id=(select id from table_role where name='admin');
+----+-------------+----------------+------------+------+---------------+---------+
| id | select_type | table          | partitions | type | possible_keys | key     |
+----+-------------+----------------+------------+------+---------------+---------+
|  1 | PRIMARY     | table_relation | NULL       | ref  | role_id       | role_id |
|  2 | SUBQUERY    | table_role     | NULL       | ALL  | NULL          | NULL    |
+----+-------------+----------------+------------+------+---------------+---------+

2, select_type field

The type of query is mainly to distinguish between ordinary query and complex query such as joint query and sub query.

2.1 simple: simple query, simple select query. The query does not contain sub query or union query. Please refer to the execution results in the previous step.

2.2 primary: primary key query. If the query contains any complex sub parts, the outermost query will be marked as primary. Please refer to the execution results in the previous step.

2.3 subquery: subquery. Subquery is included in the select or where list. Please refer to the execution results in the previous step.

2.4 derived: temporary table. The subquery containing the temporary table in the from table is marked as derived.

explain select * from (select version())temp;
+----+-------------+------------+------------+--------+---------------+------+
| id | select_type | table      | partitions | type   | possible_keys | key  |
+----+-------------+------------+------------+--------+---------------+------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL |
+----+-------------+------------+------------+--------+---------------+------+

2.5 Union: Union query. The second select appears and is marked as union query.

explain select description from table_user union select description from table_role;
+------+--------------+------------+------------+------+---------------+------+
| id   | select_type  | table      | partitions | type | possible_keys | key  |
+------+--------------+------------+------------+------+---------------+------+
|  1   | PRIMARY      | table_user | NULL       | ALL  | NULL          | NULL |
|  2   | UNION        | table_role | NULL       | ALL  | NULL          | NULL |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL |
+------+--------------+------------+------------+------+---------------+------+

2.6 union result: the result of the query in the union query. For the select query that obtains the result from the union table, please refer to 2.5 execution result.

3, talbe field

Represents the table name to be queried in a row of explain. It may be a temporary table < derived n >, or the result of a union query < union m, n >. If the operation on the data table is not involved, it is displayed as NULL. Please refer to the results of the previous steps.

4, partitions field

Indicates which table partition a row of explain needs to access.

explain select * from table_partitions where id=1;
+----+-------------+------------------+------------+-------+---------------+---------+
| id | select_type | table            | partitions | type  | possible_keys | key     |
+----+-------------+------------------+------------+-------+---------------+---------+
|  1 | SIMPLE      | table_partitions | p1         | const | PRIMARY       | PRIMARY |
+----+-------------+------------------+------------+-------+---------------+---------+






**V type field**

Indicates the association type or access type. This field is sql Query optimization is a very important index.

5.1null: Return results directly without accessing any tables and indexes

5.2system: For a system table with only one data or a derived table with only one data, please refer to 2.4 Execution results.

5.3const: Means pass primary key perhaps unique The index was found once.

5.4eq_ref: The unique index is used. For the association query using the primary key, there is only one record found in the association query.

5.5ref: Using a partial prefix of a normal index or a unique index, multiple qualified rows may be found.

5.6range: Index range scanning, commonly used in>,<,is null,between ,in ,like In the query of the operator.

5.7index: Scan the whole index table and scan the index from beginning to end.

5.8all: Scan the full table data file.

**Vi possible_keys field**

Index that may be used.

**VII keys field**

For the index actually used, refer to the execution results in the previous step.



**VIII key_len field**

Represents the number of bytes used in the index. The displayed value is the maximum possible length of the index field, not the actual length. In theory, the shorter the better

By indexing fields index_name_age,index_age By comparison, it can be seen that INT Type field index length is much shorter.



**IX ref field**

Displays what is used to compare with the index column, which may be empty, a column of a table, or a constant.

This field is often misspelled in many articles, thinking it is a column used for comparison. For example, it is described as: the column displaying the index is used. If possible, it is a constant.

In the previous step, we didn't use the comparison field. The display is NULL，If you add a constant field to the index comparison, the constant is displayed.

If the index is compared with the columns of a table, the columns of a table are displayed.

**X row field**

According to table statistics and index selection, estimate the number of rows to read to find the required records.

**Xi filtered field**

Indicates the proportion of the number of records that meet the query after the data returned by the storage engine is filtered. Unit is percentage, 100%Indicates that the data is not filtered.

**XII extra field**

Display additional information.

Possible values include:


* Using index The query column is overwritten by the index, which is actually the use of overwriting the index.

* Using where The available index is not used in the query, which is passed where Filter data conditionally.

* Using where,Using index adopt where Filter the data conditionally, and the query uses the overlay index.

* Using index condition The index is used in the query, but the back table query is required.

* Using temporary The query results need to be stored in a temporary table, which is generally used in sorting or grouping queries.

* Using filesort Sort operations that cannot be done with an index, that is ORDER BY The field has no index.

* Using join buffer When we join the table query, if the table connection conditions do not use the index, we need to have a connection buffer to store the intermediate results.

* Impossible where When we use incorrect where Statement, resulting in no qualified rows.