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
Analysis of parameter transfer process of driver module in Linux
Declare the parameter name, type and permission y...
How to use MySQL stress testing tools
1. MySQL's own stress testing tool - Mysqlsla...
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...
Quickly solve the problem of slow startup after Tomcat reconfiguration
During the configuration of Jenkins+Tomcat server...
Teach you how to use MySQL8 recursive method
I have previously written an article about recurs...
Tutorial on installing the latest MySQL 8.0.18 using a compressed package on Win10 64-bit (with pictures and text)
WIN10 64-bit install the latest MySQL8.0.18 downl...
Explanation of the process of docker packaging node project
As a backend programmer, sometimes I have to tink...
MySQL count detailed explanation and function example code
Detailed explanation of mysql count The count fun...
Summary of problems encountered in the implementation of Vue plug-ins
Table of contents Scene Introduction Plugin Imple...
Learn the common methods and techniques in JS arrays and become a master
Table of contents splice() Method join() Method r...
Example of troubleshooting method to solve Nginx port conflict
Problem Description A Spring + Angular project wi...
Detailed explanation of the steps to build a Vue project with Vue-cli
First you need to install Vue-cli: npm install -g...
Detailed graphic explanation of hadoop installation and configuration based on Linux7
Prepare the ingredients as shown above (ps: hadoo...
Mysql optimization tool (recommended)
Preface While browsing GitHub today, I found this...
Vue3 draggable left and right panel split component implementation
Table of contents Breaking down components Left P...