MySQL explain obtains query instruction information principle and example

explain is used to obtain query execution plan information.

1. Grammar

Just add explain before select, such as:

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

2. Information in explain

1. id: indicates the row to which the SELECT belongs. The larger the id, the higher the execution order. If the id is the same, it will be executed from top to bottom.

2. select_type: Displays whether the corresponding row is a simple or complex query

1) SIMPLE: Simple query, which means it does not contain subqueries and UNION

2) SUBQUERY: indicates a subquery

3) DERIVED: used to indicate the SELECT in the subquery contained in the FORM clause

4) UNION:

3. table: indicates which table to access

4. partitions: access partitions

5. type: association type, which is how to find rows in the table.

1) ALL: full table scan. In order to find data, all data must be scanned from the beginning to the end (the limit keyword will not scan all data)

2) index: index scan. This is the same as a full table scan, except that the table is scanned in index order rather than row order. The main advantage is that sorting is avoided. The biggest disadvantage is the overhead of reading the entire table in index order.

3) range: range scan. It is a restricted index scan that starts at a certain point in the index without traversing the entire index.

4) ref: index access. It returns all rows that match a single value. This only happens when using non-unique ownership or unique ownership with a non-unique prefix.

5) eq_ref: Use this index to search and return at most one record if it is a primary key index and a unique index.

6) const, system: These access types are used when MySQL can optimize a part of the query and convert it into a constant.

6. possible_keys: shows which keys can be used for the query

7. key: MySQL decides which index to use to optimize access to this table. If this index does not appear in possible_keys, it may choose a covering index. If no index is used, this value is NULL

8. key_len: The number of bytes of the index, the shorter the better. Generally speaking, key_len is equal to the length of the index column field type, such as int is 4 bytes, bigint is 8 bytes, date is 3 bytes, and datetime is 8 bytes. If the index column is a string type, its character set needs to be considered. Each character of utf8 occupies 3 fields, and variable type (varchar) requires an additional 2 bytes. If the index column is nullable, an additional field is required.

9. ref:

10. rows: The number of rows that MySQL estimates it will need to read to find the required row

11. filtered: The percentage of rows returned to the number of rows read (estimated). The larger the value, the better.

12. Extra: Displays important information that does not fit in other columns. Common values include:

1) Using index: indicates using a covering index to avoid accessing the table

2) Using where: MySQL server will filter after the storage engine retrieves the row

3) Using temporary: Indicates that MySQL will use a temporary table when sorting query results.

3. Examples

Example 1:

mysql> explain select * from bd_dept;
+----+-------------+---------+------------+------+---------------+-----+---------+------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----+---------+------+------+------+------+------+
| 1 | SIMPLE | bd_dept | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-----+---------+------+------+------+------+------+

It can be seen that the statement performs a full table scan without using the index

Example 2:

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

This statement uses the primary key index. You only need to scan one record to get the result. The int type occupies 4 bytes, so ken_len=4.

Example 3:

mysql> explain select * from bd_dept where dept_code='01';
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+------+------+------+
| 1 | SIMPLE | bd_dept | NULL | const | dept_code | dept_code | 32 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+-----------+---------+-------+------+------+------+------+

dept_code is a unique index field, the field type is varchar(10), and it is not empty, so the index length is 10*3+2=33.

Example 4:

mysql> explain select * from bd_dept where create_date>'2020-04-29';
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------+
| 1 | SIMPLE | bd_dept | NULL | range | create_date | create_date | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+----------------------+

create_date is of date type, a common index field, which can be empty. The query condition is greater than, so the association type is range, and the index length is 3+1=4.

Example 5:

mysql> explain select a.id, a.dept_name, b.dept_name parent_name from bd_dept a inner join bd_dept b on a.id=b.parent_id;
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+-------------+
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zhi_test.b.parent_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+--------+---------+----------------------+------+----------+-------------+

It can be seen that MySQL first performs a full table scan and then associates it through the primary key

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

Basic usage analysis of Explain, a magical tool for MySQL performance optimization
MySQL query statement process and basic concepts of EXPLAIN statement and its optimization
How to analyze SQL execution plan in MySQL through EXPLAIN
How to use explain to query SQL execution plan in MySql
Detailed explanation of the role of explain in MySQL
In-depth analysis of explain in MySQL query optimization
Detailed explanation of explain usage in MySQL
MySQL summary explain

<<: Detailed explanation of Xshell common problems and related configurations

>>: Vue implements scrollable pop-up window effect