Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing MySQL EXPLAIN result set". In daily operation, I believe many people have doubts about analyzing MySQL EXPLAIN result set. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "analyzing MySQL EXPLAIN result set". Next, please follow the editor to study!

EXPLAIN: view the execution plan of the SQL statement

The EXPLAIN command can help us gain an in-depth understanding of MySQL's cost-based optimizer, and obtain many details of access policies that may be considered by the optimizer, as well as which policies are expected to be adopted by the optimizer when running SQL statements, which are very useful in optimizing slow queries.

After explain, the result set contains the following information

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

Each value 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 id are executed first. This value may be NULL.

If the id is the same, it is executed from top to bottom. Id is different. The higher the id value, the higher the execution priority. If the row refers to the union result of other rows, the value can be NULL.

2 、 select_type

Select_type indicates the type of query used, and there are several types:

Simple:

Column type length remarks id int4+1int for 4bytes, allowed for NULL, plus 1byteid bigint not null8bigint for 8bytesuser char (30) utf830*3+1utf8 each character for 3bytes, allowed for NULL, plus 1byteuser varchar (30) not null utf830*3+2utf8 for each character for 3bytes, variable length data type, plus 2bytesuser varchar (30) utf830*3+2+1utf8 each character for 3bytes, allowed for NULL, plus 1byte, variable length data type, plus 2bytesdetail text (10) utf830*3+2+1TEXT intercept part, is regarded as a dynamic column type.

Key_len indicates only the selected index column in where for conditional filtering, and is the selected index column that does not contain order by or group by.

8 、 ref

The ref column is used to show which column or constant to use with key to select the appropriate row from the table. It displays the name of the column (or const), which is mostly null

9 、 rows

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

10 、 filtered

This parameter is a new parameter for mysql 5.7.It refers to the proportion of the number of rows that return the result to the rows that need to be read (the value of rows).

When using join, the result set size of the previous table directly affects the number of rows of the loop

11. Extra (important)

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 overlay index (which means that you can get the desired results directly in the index column without going back to the table), which is the most efficient.

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 |

+-- +

In this example, the id field is the primary key, but the key does not show the primary key index. This is because all the primary key information is contained in all the secondary indexes of mysql, and mysql does not store the primary key index separately, so scanning the secondary index is faster than scanning the whole table.

Using where: indicates that the storage engine POST-FILTER after finding the record. If the query fails to use the index, the function of using where is to remind us that mysql needs to filter the result set with where condition.

Mysql > explain select * from test where id > 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 |

+-- +

Using temporary indicates that mysql requires temporary tables to store result sets, which are common in sorting and grouping queries

Mysql > explain select * from test where id in (1BI 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 |

+- -- +

Using filesort: it means that mysql cannot use the index to sort directly (the sorted field is not the index field), so the buffer space will be used for sorting.

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 | 68505 | Using filesort |

+-- +

Using join buffer: emphasize that indexes are not used when getting join conditions and that connection buffers are 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 | ALL | NULL | 959692 | 100.00 | NULL |

| | 1 | SIMPLE | test2 | NULL | ALL | NULL | 958353 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

+- -- +

2 rows in set, 1 warning (0.00 sec)

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

Impossible where: indicates that the where condition causes no returned rows

Mysql > explain select * from test where id is null

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | NULL | Impossible WHERE |

+-- +

Using index condition: a new feature added after mysql 5.6.It is used in conjunction with mysql's ICP (Index Condition Pushdown) feature. It is mainly optimized to perform like lookups on indexes (secondary indexes only).

If more than one of the above results appears in the extra, it indicates that the query is parsed sequentially using the above method

At this point, the study on "analyzing MySQL EXPLAIN result sets" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.