Shulou(Shulou.com)06/01 Report--

This article mainly shows you MySQL explain how to obtain query instruction information, the content is easy to understand, I hope you can learn, after learning, there will be a harvest, the following let the editor take you to take a look at it.

Explain is used to obtain query execution plan information

First, grammar

You only need to add explain before select, such as:

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

II. Information in explain

1. Id: indicates the line to which SELECT belongs. The larger the id, the higher the execution order, and when the id is the same, it executes from top to bottom.

2. Select_type: displays whether the corresponding row is a simple or complex query

1) SIMPLE: simple query, which means that subqueries and UNION are not included

2) SUBQUERY: indicates that it is a subquery

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

4) UNION:

3. Table: indicates which table to access

4. Partitions: access zone

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

1) ALL: full table scan. In order to find data, all data must be scanned from beginning to end (limit keyword does 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 rows, the main advantage is to avoid sorting, and the biggest disadvantage is to bear the overhead of reading the entire table in index order.

3) range: range scan. Is a limited index scan that starts at some point in the index and does not have to traverse the entire index.

4) ref: index access. It returns all rows that match a single value. Occurs only if a non-unique prefix that is not unique or unique is used.

5) eq_ref: use this index to look up and return at most one record, if the primary key index and the unique index.

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

6. Possible_keys: shows which all can be used by the query

7. Key:MySQL decides which index to use to optimize the access to the table. If the index does not appear in possible_keys, it may choose an overlay index. If no index is used, the value is NULL.

8. Key_len: the number of bytes in the index, the shorter the better. Generally speaking, key_len is equal to the field type length of the index column, 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, you need to consider his character set, utf8 takes up 3 fields for each character, and varchar needs 2 extra bytes; if the index column can be empty, you need an extra field.

9 、 ref:

10. Rows:MySQL estimates the rows to be read in order to find the required rows

11. Filtered: the number of rows returned as a percentage of the number of rows read (estimated). The larger the value, the better.

12. Extra: displays important information that is not suitable for other columns. Common values are:

1) Using index: means to use an override index to avoid accessing the table

2) the Using where:MySQL server will filter after the rows are retrieved by the storage engine

3) Using temporary: indicates that MySQL uses a temporary table when sorting query results.

III. 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 | 3 | 100.00 | NULL | +-+-- -+-+

You can see that the statement scans the whole table and no index is used.

Example 2:

Mysql > explain select * from bd_dept where id=1 +- + | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | 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 a record to get the result, the int type is 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 with a field type of varchar (10) and 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 a date type, a normal index field, which can be empty, and 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 an 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 | 3 | 100.00 | Using where | | 1 | SIMPLE | a | | NULL | eq_ref | PRIMARY | PRIMARY | 4 | zhi_test.b.parent_id | 1 | 100.00 | NULL | +-+-+ | -- +

You can see that MySQL first performs a full table scan, and then associates it with the primary key

This is about how MySQL explain gets the information about query instructions. If you have learned knowledge or skills, you can share it for more people to see.

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.