Give an example to explain the meaning of the explain fields

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
explain select version();
idselect_typetablepartitionstypepossible_keyskey
1SIMPLENULLNULLNULLNULLNULL

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.
explain select * from table_partitions where id=1;
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp1constPRIMARYPRIMARY

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.
explain select * from table_relation join table_user where table_user.id=table_relation.user_id;
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_relationNULLALLuser_idNULL
1SIMPLEtable_userNULLeq_refPRIMARYPRIMARY

5.5ref: Using a partial prefix of a normal index or a unique index, multiple qualified rows may be found.
explain select * from table_partitions where name='zhang';
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp0,p1refindex_name_ageindex_name_age

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

explain select * from table_partitions where name like 'zhang';
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp0,p1rangeindex_name_ageindex_name_age
5.7index: Scan the whole index table and scan the index from beginning to end.
explain select name from table_partitions;
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp0,p1indexNULLindex_name_age
5.8all: Scan the full table data file.
explain select * from table_partitions;
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp0,p1ALLNULLNULL


**Vi possible_keys field**

Index that may be used.
explain select * from table_partitions where name='zhang' and age=20;
idselect_typetablepartitionstypepossible_keyskey
1SIMPLEtable_partitionsp0,p1refindex_age,index_name_ageindex_age



**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
explain select name from table_partitions;
idtypekeykey_lenrefrowsfilteredExtra
1indexindex_name_age128NULL1100.00Using index
explain select age from table_partitions;
idtypekeykey_lenrefrowsfilteredExtra
1indexindex_age5NULL1100.00Using index
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.
explain select name from table_partitions where name='zhang';
idtypekeykey_lenrefrowsfilteredExtra
1refindex_name_age123const1100.00Using index

If the index is compared with the columns of a table, the columns of a table are displayed.
explain select table_relation.id from table_relation,table_role where role_id=table_role.id;
idtypekeykey_lenrefrowsfilteredExtra
1indexPRIMARY4NULL2100.00Using index
1refrole_id5mydb.table_role.id1100.00Using index



**X row field**

According to table statistics and index selection, estimate the number of rows to read to find the required records.
explain select age from table_partitions where age>18;
idtypekeykey_lenrefrowsfilteredExtra
1indexindex_age5NULL3100.00Using where; Using index

**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.
explain select * from table_user where description='admin';
idtypepossible_keyskeykey_lenrefrowsfilteredExtra
1ALLNULLNULLNULLNULL333.33Using where



**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.

Keywords: Database

Added by desoto0311 on Thu, 24 Feb 2022 09:55:35 +0200