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

In this issue, the editor will bring you the type column and extra column about how to interpret the MySQL implementation plan. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Benmeng was recently encouraged by a piece of news that Bai Yutong, the hard-core "goddess of study" of the Western University of Technology, received doctoral admission notices from six top universities in the world.

The real value of the goods is both talented and beautiful, the children of other people.

When I failed in the college entrance examination and missed my favorite major, I chose the major of software engineering that I didn't understand at all. Even if it all goes back to zero, you have to prove that you have been ranked first in a comprehensive professional ranking for three consecutive years, and all kinds of award-winning experiences have filled 5 pages of PPT. Success begins with continuous efforts and hard work, and constantly improve yourself in study and practice.

# skill book # would like to be the cornerstone of your success, hurry to receive today's spiritual food ~

I. interpretation of type

The type that executes the plan represents the access data type, and there are many access types.

1 、 system

Indicates that this step returns only one row of data, and if the execution object of this step is a driven table or main table, then the driven table or subquery is accessed only once.

2 、 const

Indicates that this execution step returns at most one row of data. Const usually appears in equivalent queries for primary keys or unique indexes, such as for table t primary key id:

3 、 eq_ref

The eq_ref type generally means that when a table is associated, the associated column on the associated table takes the primary key or unique index. For example, the table jiang is associated with the lock_ test table, and the associated columns are the primary key columns of the two tables:

When the above SQL is executed, the jiang table is the driven table, the lock_test is the driven table, and the associated column of the driven table is the primary key id,type type eq_ref.

Therefore, an important feature for the eq_ref type is that the table involved in this step is a driven table; a unique index or primary key is used in this step. With the exception of system and const, this is the most effective association type.

4 、 ref

In contrast, if the type for a step in the execution plan is ref, the associated column for that step is a non-unique index. For example, associate the id column of table lock_test with the primary key id column of table lock_test, and a normal index is established on the num column:

When the above SQL is executed, the table jiang is the driven table, the lock_test is the driven table, the driven table is a non-unique index, and the type type is ref.

So the characteristic of ref is that it indicates that the index used to access the data in this step is non-unique.

5 、 Ref_or_null

For example, execute the following statement:

Indicates that the index is gone (there is an index on the num column), but null values are also accessed.

6 、 index_merge

Represents index merging, which usually occurs when multiple secondary index columns are or.

For example, execute the following statement:

Mysql > explain select * from lock_test where id=3 or num=4

Id is the primary key, and there is a normal index on the num column. When the statement is executed, the or operation is handled by two single-column indexes.

7 、 unique_subquery

Represents a unique subquery. For example, when the following statement is executed:

Value in (select primary_key from single_table where...)

For the in clause, type is displayed as unique subquery when the subquery in the in clause returns the primary key of a table.

8 、 index_subquery

When the following statement is executed:

Value in (select key_column from single_table where...)

Similar to the above, it means that for the in clause, when a subquery in the in clause returns a secondary index column (non-primary key column) of a table, the type is displayed as index_subquery.

9 、 range:

Fetch part of the data on an indexed column. It is common to perform between and operations on index columns.

10 、 index:

A full scan of an index usually occurs when an index is overwritten, that is, a full scan of an indexed column.

11 、 all:

Full table scan without index.

A special case:

Explain select * from stu limit 1

II. Interpretation of extra

1 、 using where:

Generally speaking, it has two meanings:

Indicates that when accessing through an index, you need to go back to the table to access the required data

Filtering conditions occur at the server layer rather than the storage engine layer

If the index is gone in the execution plan, but the extra value is high and the extra is shown as using where, then the execution effect will not be very good. Because the cost of index access is mainly on the back table, overlay indexes can be used to optimize.

The filter condition can also be pressed by overwriting the index, and the filter operation can be performed at the storage engine layer, which works best.

Therefore, overriding index is the most effective way to solve using where.

2 、 using index condition

Means to press the filter down to the storage layer to prevent the server layer from filtering too much data

If using index condition appears in extra, the access to the table data is optimized.

3 、 using temporary

Indicates that temporary tables are used during the execution of the statement. Temporary tables may be used in the appearance of the following clauses:

Order by

Group by

Distinct

Union et al.

The data cannot be returned directly to the user, so it needs to be cached, and the data is cached in the user's workspace as a temporary table. Note that a disk temporary table may appear and you need to pay attention to the rows of the data that needs to be cached.

You can use the index to eliminate the temporary tables corresponding to the above four operations.

4. Using sort_union (indexs)

For example, when executing the following statement:

There are indexes on both the Sname and sphone columns, and the extra item that executes the plan displays using sort_union, indicating the index merge. Often accompanied by index_merge.

5 、 using MRR:

Generally, the process of accessing table data through the secondary index is: first visit the secondary index column, find the corresponding secondary index data, then get the corresponding primary key value, and then take the primary key value to access the table and extract the row data. The data extracted in this way is sorted according to the secondary index.

MRR said: after getting the corresponding primary key values through the secondary index, we do not directly access the table but store them first. After getting all the primary key values, sort the primary key values, and then access the table. This greatly reduces the number of visits to the table, or at least implements sequential access to the table.

One of the advantages of MRR is to improve the efficiency of index access to the table, that is, to reduce the cost of returning to the table. But there is a big problem: the extracted data is not sorted according to the secondary index.

6. Using join buffer (Block Nested Loop)

BNL mainly occurs when two tables are associated, and there is no index on the associated table.

BNL means that A has an index on the associated column of BMagol An and B does not. At this time, 10 rows of data from table A will be taken out and put into the user's join buffer space, and then the data on B will be associated with the associated column of An in join buffer. In this case, you only need to access table B once, that is, table B will have a full table scan.

If 10 rows of data in join buffer have been associated, 10 more rows of data will continue to be associated with table B until all the data in table An is associated.

From the above, it can be seen that this approach will probably improve the efficiency by about 90%.

7. Using join buffer (Batched Key Access)

The general case of BKA is that when the table is associated, there is an index on the driven table, but the driven table returns too many rows.

When this happens, the returned result set of the driven table is placed in the join buffer of the user's workspace, and then a record of the result set is taken to associate the index associated column of the driven table. After getting the corresponding primary key column, the data is not immediately fetched from the driven table through this primary key column, but is first stored in the workspace. When all the data in the result set is associated, sort all the primary key columns in the workspace through association, and then uniformly access the driven table and fetch the data from. The advantage is that the number of visits is greatly reduced.

It can be seen from the above that BKA uses MRR technology; BKA is suitable for driving a table with a large number of rows returned and an index when the driven table is accessed.

This feature can be turned on or off:

Set optimizer_switch='mrr=on,batched_key_access=on'

8 、 using index for group by

Indicates that the group by is completed through a composite index without going back to the table.

For example, using index for group by occurs when a compound index (select a from tb group by b; b) executes a statement: using index for group by.

9 、 using index

Indicates that an overlay index scan is implemented; that is, all the data that needs to be accessed is in the index and there is no need to return to the table. In general, reducing unnecessary data access can improve efficiency.

For example, take the data on the num column on the table lock_test, and create a normal index on the num column:

10 、 using filesort

Indicates that there is sorting behavior, but not necessarily disk sorting.

11 、 materialize scan

A full scan of the materialized table because the materialized table is a temporary table and there is no index on the table.

The above is the type column and extra column that the editor shares with you on how to interpret the MySQL implementation plan. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are 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.