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:
|
<<: Detailed explanation of Xshell common problems and related configurations
>>: Vue implements scrollable pop-up window effect
Recommend
How to use IDEA to create a web project and publish it to tomcat
Table of contents Web Development 1. Overview of ...
JavaScript Interview: How to implement array flattening method
Table of contents 1 What is array flattening? 2 A...
Solution to the MySQL server has gone away error
MySQL server has gone away issue in PHP 1. Backgr...
The big role of HTML meta
There are two meta attributes: name and http-equiv...
Sample code for testing technology application based on Docker+Selenium Grid
Introduction to Selenium Grid Although some new f...
Use DIV mask to solve the problem that directly checking the checkbox with the mouse is invalid
During the front-end development process, a situat...
WeChat Mini Program video barrage position random
This article shares the specific code for randomi...
Solution to the automatic termination of docker run container
Today I encountered a problem when I used Dockerf...
Comprehensive analysis of isolation levels in MySQL
When the database concurrently adds, deletes, and...
Using js to achieve the effect of carousel
Today, let's talk about how to use js to achi...
HTML hyperlink a tag_Powernode Java Academy
Anyone who has studied or used HTML should be fam...
Summary of various common join table query examples in MySQL
This article uses examples to describe various co...
Teach you how to use docker-maven-plugin to automate deployment
1. Introduction to docker-maven-plugin In our con...
HTML left, center, right adaptive layout (using calc css expression)
In the latest HTML standard, there is a calc CSS e...
Docker installation and configuration command code examples
Docker installation Install dependency packages s...