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

Editor to share with you about the MySQL 5.7Explain implementation plan, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Catalogue

1. Introduction

2. Detailed explanation of Explain result list

2.1 id

2.2 select_type

2.3 table

2.4 partitions

2.5 type (very important)

2.6 possible_keys

2.7 key

2.8 key_len

2.9 ref

3.10 rows

2.11 filtered

2.12 Extra

[note]

Current system environment: MySQL 5.7. other versions are slightly different and will be explained separately later.

Only common scenarios are introduced, and other rare scenarios will not be studied for the time being. If necessary, you can look it up in the official documents.

Non-basic, you need to have some understanding of MySQL's underlying data structure, B + tree.

Documentation reference:

MySQL official Explain documentation

1. Introduction

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL statements and analyze the performance bottlenecks of query statements.

2. Detailed explanation of Explain result list

2.1 id

The number of the id column is the sequence number of the select, and there are usually several select (join table queries have duplicate id), and the order of the id is increased in the order in which the select appears.

The higher the id, the higher the priority of execution, while the same id (which usually appears in the join table query) is executed from top to bottom, and id is the last execution of NULL.

2.2 select_type

Select_type indicates whether the corresponding row is a simple or complex query. Common values are:

Simple: simple query, query does not include subquery and union.

Primary: the outermost select in a complex query.

Subquery: subqueries contained in select (not in the from clause)

Derived: for subqueries contained in the form clause, MySQL places the results in a temporary table, also known as a derived table.

Union: the second or subsequent select in the union.

[note] in MySQL 5.7, the derived tables are merged and optimized. If you want to directly view the value of select_type, you need to temporarily disable this function (default is on). This operation is required for all those related to derived tables in the following description.

# turn off merge optimization for derived tables (valid for this session only) set session optimizer_switch='derived_merge=off'; # turn on merge optimization for derived tables (valid for this session only) set session optimizer_switch='derived_merge=on'

2.3 table

The table corresponding to the row query.

[note]

When there is a subquery in the from clause, the table column is in the format of yes, indicating that this row is executing a query with row id = N.

When there is a union, the data of the table is in the format of, and M and N represent the select line id that participates in the union.

2.4 partitions

To be continued.

2.5 type (very important)

Type represents the association type (access type, or query type) of this row of queries, which gives you an approximate range of the row's query data records.

The common values from the best to the worst are: system > const > eq_ref > ref > range > index > ALL; generally if we want to ensure efficiency, we should optimize our statements to at least make it to the range level, if possible, do a good optimization to ref;range generally used for range search, so in other words, in addition to scope lookup, other query statements we had better optimize to the ref level.

Common values indicate:

NULL: indicates that MySQL can decompose query statements during the optimization phase without accessing tables and indexes during the execution phase.

System / const: MySQL can optimize a part of a query and convert it into a constant (you can view the optimized results through show warnings), mainly querying the records corresponding to the primary key (Primary Key) or unique key index (Unique Key). Because there is no duplication, you can query at most one record, so it is relatively fast. System is a special case of const, which is system when there is only one record in the temporary table.

# there is a record with primary key id 1 in the table-there is only one record in the derived table-systemexplain select * from (select * from student where id = 1) tmp# Note: if there is a text type in the column of the query, then type will become ALL here. # because temporary tables cannot be used in memory and can only be created on disk, the performance will be degraded, and the effect is equivalent to querying ALL in full tables.

Req_ref: when related columns of a primary or unique key index are used with joins (join table queries), at most one matching record is matched. This is the best join type besides const. Simple select queries do not appear in req_ref, but more in join table queries.

# although there are multiple records in the returned result, a student id only corresponds to one class in the query, so it is req_ref,# when querying the class, but ALL when querying student, and querying explain select * from student left join banji on student.id = banji.student_id in the full table.

[note] the returned results during the query are as follows:

When querying the join table, it will be regarded as a query SQL, so their corresponding id is the same. When the id is all the same, it is executed in the order from top to bottom. Here, all the students in the class are queried first (full table query ALL), and then the corresponding class information (req_ref) is found according to the student id.

Ref: when using a partial prefix of a normal index (Normal) or a federated index, the index compares to a value and may find multiple rows of records that meet the criteria, starting with the root node of the secondary index and finding the corresponding record.

# simple select query, name is Normal Index explain select * from student where name = 'Zhang San' # simple select query, joint index of banji_id (first) and student_id (second) EXPLAIN SELECT * FROM banji_student WHERE banji_student.banji_id = associative table query # contains banji table, banji_student is the class-student relationship table # the idx_banji_stu_id index of banji_id (first) and student_id (second) is included in the relational table # the following query only uses the banji_id (first) explain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id of the federated index

Range: range scan, which usually occurs in in,between, >, =, and so on, using an index to retrieve a given range of rows.

# query student information with id greater than 1 explain select * from student where id > 2

Index:

# student table has only id primary key, name normal index select * from student;# will go name index # because name is a normal index, you can reach the ref level select * from student where name = 'Ana' if you add where

Overlay index definition: an overlay index is generally aimed at a secondary index, not a real index, but a way of index lookup. If all the fields of the select query are obtained in the secondary index tree, the overlay index is usually used. There is no need to find the primary key through the secondary index tree, and then use the primary key to go to the primary key index tree to get other field values.

You can get the results by scanning the full index, usually by scanning a secondary index (secondary index, index other than the primary key). This kind of index does not start from the root node of the primary key index tree, but traverses and scans the leaf nodes of the secondary index directly to find out the corresponding record rows.

This kind of query generally uses the overlay index, and when all the result sets required for the query are in both the secondary index and the primary key index, because the secondary index is generally small (because the secondary index is nonclustered, its leaf node is the corresponding address of the primary key index, while the primary key index is clustered, and its leaf node stores the complete dataset), so the secondary index is given priority. This is usually faster than ALL.

In some cases, if the number of columns in the table is particularly large, the performance of querying through the secondary index is not as efficient as using the primary key index directly (if the secondary index is queried, it will also return to the primary key index to find more fields, that is, back to the table query, of course, in some cases, the performance of using the back table query will be better than that of using only the primary key index). At this time, the primary key index will be used. This is also faster than ALL.

ALL: full table scan, scanning all leaf nodes of the primary key (clustering, clustering) index tree. In this case, other indexes are added to optimize according to the business scenario.

# student table with id as primary key. There are no other indexes. The query is ALL.select * from student.

2.6 possible_keys

Possible_keys mainly shows which indexes the query may use to find it, but it may be used, but it does not necessarily mean that it will be used.

Common values indicate:

NULL: there is no related index. If it is NULL, you can consider creating an appropriate index in the where clause to improve query performance, and then continue to use explain to see its effect; it is also possible that possible_keys is NULL, but key has a value, so the index is actually gone.

There are column values: if a column in the table is displayed, it means that the index corresponding to the column value may be taken; if possible_keys has a value, but key displays NULL, this situation generally exists when the amount of data in the table is small, because the MySQL statement optimizer thinks that the index is not very helpful to this query, so it chooses a full table query.

2.7 key

Key indicates which index MySQL actually uses to optimize the query on the table.

If the index is not used, the column NULL, and if you want to force MySQL to use or ignore the index in the possible_keys column, you can use force index or ignore index.

2.8 key_len

Shows the number of bytes used by the MySQL index, which allows you to calculate which columns in the index are used (mainly for federated index optimization).

[note] the maximum length of the index is 768 bytes. When the string is too long, MySQL will do a processing similar to the left prefix index, extracting the first half of the characters for indexing.

Example: a student-class relationship table: banji_student, there is a federated index using a combination of banji_id and student_id columns, and each index int is 4 bytes. You can see that only the first column of the federated index, banji_id, is used to perform an index lookup by a key_len value of 4.

# only the first column of the federated index select * from banji_student where banji_id = 2

The calculation rules for key_len are as follows:

String: common are char (n) and varchar (n). After MySQL 5.0.3, n represents the number of characters, not the number of bytes. In the case of UTF-8, a number or letter occupies 1 byte, and a Chinese character occupies 3 bytes.

Describe that the length of char (n) non-Chinese characters is n, if the length of stored Chinese characters is 3n bytes varchar (n) the length of non-Chinese characters is 3n+2 2, if the length of stored Chinese characters is 3n+2 bytes; because varchar is a variable length string, 2 bytes are needed to store the string length

Numeric type:

Describe tinyint length as 1 byte smallint length is 2 bytes int length is 4 bytes bigint length is 8 bytes

Time type:

Describe date length as 3 bytes timestamp length as 4 bytes datetime length as 8 bytes

NULL

If the field is allowed to be set to NULL, 1 byte is required to record whether the column is NULL; Not NULL or not.

2.9 ref

Shows the column names and constants used in the table lookup when using the actual index in the key column; the common field (column) name for the const constant or index association query.

# the constant 2 is used, so when querying, ref is constselect * from student where id = associative table query # contains the banji table, banji_student is the class-student relationship table # the relational table has the joint index idx_banji_stu_id index of banji_id (the first) and student_id (the second) # where the ref is test.id That means banji.idexplain select * from banji_id from banji left join banji_student on banji.id = banji_student.banji_id.

3.10 rows

Displays the number of results of the expected query, not the real number of records (rows) in the result set, for reference only.

2.11 filtered

To be continued.

2.12 Extra

This column shows additional information, there are many values, and different scenarios and different versions of MySQL have different meanings, so they can only express the general meaning and only serve as optimization reference. Here, only common values are introduced.

Using index: using an overlay index, when the type is the same, the Extra value of Using index is better than NULL.

For example, in the banji table, there are id,name,create_time columns, id primary keys and name normal indexes.

# override the index, and directly query the index tree corresponding to name to satisfy the query column select id,name from banji# non-overlay index behind select. Although the index is also gone, a query back to the table is performed to query the create_time field. Select * from banji where name = 'sophomore'

Using where: use the where keyword to query, and the corresponding column is not indexed, and the corresponding key is NULL.

In this case, it is generally necessary to add corresponding indexes to the columns of the query to optimize.

Using index condition: a non-overriding index query is made and the table is returned, and the secondary index uses conditional query statements (where or other).

For example, a banji_student relational table has id,banji_id,student_id,create_time columns, an id primary key, and a combined (federated) index of banji_id and student_id.

# query back to the table to find out the create_time column, and the combined index makes a range lookup select * from banji_student where banji_id > 3

Using temporary:MySQL needs to create a temporary table to process the query, which is usually optimized by adding an index.

# if name does not add a normal index, you need to create a temporary table to remove duplicates. The value of Extra is Using temporary#. If an index is added, the index tree corresponding to name will be taken and the index will be overridden. The value of Extra is Using indexexplain select distinct name from student.

Using filesort: use external sort instead of index sort, use memory sort when the data is small, frequently access the disk when the amount of data is large, and write the sorted data to disk.

# if name does not add a normal index, you need to create a temporary table to remove duplicates. The value of Extra is Using filesort#. If an index is added, the index tree corresponding to name will be taken and the index will be overridden. The value of Extra is Using indexexplain select name from student order by name.

Select tables optimized away: when using aggregate functions (such as max, min, etc.) to access fields that have an index, only the ordered leaves in the index tree are accessed, and the node performance is very high.

# for example, using the aggregate function min to query the smallest student id (primary key) explain select min (id) from student is all the content of the article "MySQL 5.7Explain execution Plan". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.