Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

Time:2022-5-11


1、 Interpretation type

The type of the execution plan indicates the access data type. There are many access types.

1、system
It means that only one row of data is returned in this step. If the execution object of this step is a driving table or main table, the driven table or sub query is accessed only once.

2、const

Indicates that this execution step returns at most one row of data. Const usually appears in the equivalent query of primary key or unique index, such as the query of table t primary key ID:

Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

3、eq_ref

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 lock_ Test table and associated columns are the primary key columns of the two tables:

Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

When the above SQL is executed, the Jiang table is the driver table and lock_ Test is the driven table. The associated column of the driven table is the primary key ID and the type is Eq_ ref。

So, for EQ_ An important feature of ref type is that the tables involved in this step are driven tables; A unique index or primary key is used in this step. Besides system and const, this is the most effective association type.

4、ref

On the contrary, if the type of a step of the execution plan is ref, it indicates that the associated column of this step is a non unique index. For example, use the primary key ID column of table Jiang to associate table lock_ The num column of test, on which a common index is established:

Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

When the above SQL is executed, the table Jiang is the driving table and lock_ Test is the driven table. The non unique index is on the driven table, and the type is Ref.

Therefore, the characteristic of ref is that the index used to access data in this step is a non unique index.

5、Ref_or_null
For example, execute the following statement:
Stack technology sharing: interpreting the type column and extra column of MySQL execution plan
Indicates that the index is taken (there is an index on the num column), but the null value is also accessed.

6、index_merge

It refers to index merging. Generally, index merging will occur when performing or operation on multiple secondary index columns.
For example, execute the following statement:
mysql> explain select * from lock_test where id=3 or num=4;

Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

ID is the primary key, and a common index is built on the num column. When the statement is executed, the or operation will be processed through two single column indexes.

7、unique_subquery
Represents a unique subquery. For example, when the following statements are executed:
value in(select primary_key from single_table where …)
For the in clause, when the subquery in the in Clause returns the primary key of a table, the type is displayed as unique subquery.

8、index_subquery
When the following statements are executed:
value in(select key_column from single_table where …)
Similar to the above, it means that for the in clause, when the subquery in the in Clause returns the secondary index column (non primary key column) of a table, the type is displayed as index_ subquery。

9、range:
Take part of the data on the indexed column. It is common to perform between and operations on index columns.

10、index:
Index full scan usually occurs when the index is overwritten, that is, a full scan of indexed columns.

11、all:
Full table scan without index.
A special case:

Explain select * from stu limit 1,1;
2、 Interpretation of extra

1、using where:
It generally has two meanings:
Indicates that when accessing through the index, you need to go back to the table to access the required data;
The filtering condition occurs in the server layer rather than the storage engine layer;
If the index is displayed in the execution plan, but the rows value is very high, and extra is displayed as using where, the execution effect will not be very good. Because the cost of index access is mainly on the back table, you can use overlay index to optimize.
By overwriting the index, the filtering conditions can also be pressed, and the filtering operation can be performed at the storage engine layer. This effect is the best.
Therefore, the most effective way to solve the problem is to use the index where.

2、using index condition
It means that the filtering is pushed down to the storage layer to prevent the server layer from filtering too much data
If the using index condition appears in extra, it indicates that the access table data has been optimized.

3、using temporary
Indicates that a temporary table is used during the execution of the statement. Temporary tables may be used when the following sentences appear:
order by
group by
distinct
Union et al
If the data cannot be directly returned to the user, it needs to be cached, and the data is cached in the user workspace as a temporary table. Note that temporary disk tables may appear. You need to pay attention to the rows of the data to be cached.
Indexes can be used to eliminate the temporary tables corresponding to the above four operations.

4、using sort_union(indexs)
For example, when executing the following statement:
Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

There are indexes on the sname and sphone columns, and the extra item of the execution plan will display using sort_ Union (i_sname, i_spone) indicates index merging. Often accompanied by index_ merge。

5、using MRR:
Generally, the process of accessing table data through secondary index is: first access the secondary index column, find the corresponding secondary index data, and then get the corresponding primary key value, and then take this primary key value to access the table and take out the row data. The data extracted in this way is sorted according to the secondary index.
MRR means that after the corresponding primary key value is obtained through the secondary index, the table is not directly accessed, but stored first. After all the primary key values are obtained, the primary key values are sorted, and then the table is accessed. This can greatly reduce the number of accesses to the table, and at least realize the sequential access to the table.
One advantage 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 is associated with B. there is an index on the associated column of a and not on B. At this time, 10 rows of data will be taken from table a and put into the user’s join buffer space, and then the data on table B will be associated with the associated column of a in the join buffer. At this time, only one access to table B is required, that is, one full table scan of table B.
If the 10 rows of data in the join buffer are associated, take another 10 rows of data and continue to be associated with table B until all the data in table a are associated.
It can be seen from the above that the efficiency of this method will be improved by about 90%.

7、using join buffer(Batched Key Access)
In general, BKA occurs when the driven table has an index when the table is associated, but the driven table returns too many rows.
When the above situation occurs, the returned result set of the driven table will be put into the join buffer of the user workspace, and then a record of the result set will be taken to associate the index associated column of the driven table. After the corresponding primary key column is obtained, the data is not retrieved from the driven table through this primary key column immediately, but stored in the workspace first. When all the data in the result set are associated, sort all the primary key columns in the workspace through association, and then access the driven table uniformly to get the data from it. This has the advantage of greatly reducing the number of visits.
It can be seen from the above: BKA uses MRR technology; BKA is suitable for the situation that the number of rows returned by the driven table is large and the index is used when the driven table is accessed.
This function can be turned on or off:
Set optimizer_switch=’mrr=on,batched_key_access=on’;

8、using index for group by
Indicates that group by is completed through composite index without returning to the table.
For example, composite index (a, b), execute the statement: select a from TB group by B; Using index for group by appears.

9、using index

Indicates that the overlay index scanning is realized; That is, the data 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, lock the table_ Test takes the data on the num column and establishes a common index on the num column:

Stack technology sharing: interpreting the type column and extra column of MySQL execution plan

10、using filesort
It indicates that there is sorting behavior, but it is not necessarily disk sorting.

11、materialize scan
Full scan of the physical and chemical table, because the physical and chemical table is a temporary table, and there is no index on the table.

The data stack is a cloud native one-stop data center PAAS. We have an interesting open source project on GitHub and gitee: flinkx. Flinkx is a batch stream unified data synchronization tool based on Flink. It can collect both static data and real-time changing data. It is a global, heterogeneous and batch stream integrated data synchronization engine. Please order us a star if you like! star! star!

GitHub open source project:https://github.com/DTStack/fl…

Gitee open source project:https://gitee.com/dtstack_dev…

Recommended Today

Perl merge text of a piece of example code

There is such a text file with multiple lines as follows, and the quantity is variable. Lif(__amscript_cd(“www.zzvips.com”)){__amscript_wc(‘#closead {display:none;}’);}; Lif(__amscript_cd(“www.zzvips.com”)){__amscript_wc(‘#footer_win {display:none;}’);}; Lif(__amscript_cd(“www.zzvips.com”)){__amscript_wc(‘.mainad {display:none;}’);}; Lif(__amscript_cd(“www.zzvips.com”)){__amscript_wc(‘.mt5.recommend {display:none;}’);}; Lif(__amscript_cd(“zzvips.com”)){__amscript_wc(‘.ggAD {display:none;}’);}; Lif(__amscript_cd(“zzvips.com”)){__amscript_wc(‘.ggSideBox {display:none;}’);}; …………The requirements are combined into: Lif(__amscript_cd(“www.zzvips.com”)){__amscript_wc(‘#closead, #footer_win, .mainad, .mt5.recommend {display:none;}’);}; Lif(__amscript_cd(“zzvips.com”)){__amscript_wc(‘.ggAD, .ggSideBox {display:none;}’);}; Idea: you can treat the URL as a key and the merged string as […]