What are the plans for MySQL execution plans?

Time:2021-11-28

1. Introduction to the implementation plan

Execution plan refers to the specific execution method of an SQL statement after optimization by MySQL query optimizer. MySQL provides us with an explain statement to obtain information about the execution plan. It should be noted that the explain statement does not really execute the relevant statements, but analyzes the statements through the query optimizer to find out the optimal query scheme and display the corresponding information.

Execution plans are usually used for SQL performance analysis, optimization and other scenarios. Through the results of explain, you can learn such information as the query order of the data table, the operation type of data query operation, which indexes can be hit, which indexes will actually hit, and how many row records of each data table are queried.

Explain the execution plan supports select, delete, insert, replace, and update statements. We usually use it to analyze select query statements.

2. Implementation plan and actual combat
Let’s briefly look at the execution plan of the next query statement:

mysql> explain SELECT * FROM dept_ emp WHERE emp_ no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1); +—-+————-+———-+————+——-+—————–+———+———+——+——–+———-+————-+ | id | select_ type | table | partitions | type | possible_ keys | key | key_ len | ref | rows | filtered | Extra | +—-+————-+———-+————+——-+—————–+———+———+——+——–+———-+————-+ | 1 | PRIMARY | dept_ emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where | | 2 | SUBQUERY | dept_ emp | NULL | index | PRIMARY,dept_ No primary 16 null 331143 100.00 using index copy code
It can be seen that there are 12 columns in the execution plan results, and the meanings represented by each column are summarized in the following table:

Column name meaning
Id the sequence identifier of the select query
select_ The query type corresponding to the type select keyword
Table name used by table
Partitions matches partitions. For non partitioned tables, the value is null
Access method of type table
possible_ Possible indexes for keys
Key actual index
key_ Len the length of the selected index
Ref a column or constant that is compared to an index when using an index equivalent query
Rows the number of rows expected to be read
Filtered percentage of records retained after filtering by table conditions
Extra additional information
Let’s take a look at some important details in the implementation plan:

id:

Select identifier. This is the sequence number of the select in the query. If the row references the union result of other rows, the value can be null. When the IDs are the same, the execution order is from top to bottom; When the IDs are different, the higher the ID value, the higher the priority, and the earlier the execution.

select_type:

Query types, common values are:

Simple: simple query without union or subquery.
Primary: if the query contains subqueries or other parts, the select of the outer layer will be marked as primary.
Subquery: the first select in the subquery.
Union: in a union statement, select appears after union.
Derived: subqueries that appear in from will be marked as derived.
Union result: the result of the union query.
table:

Indicates the table name used in the query. Each row has a corresponding table name. In addition to the normal table, the table name may also be the values listed below:

< unionm, n >: the row refers to the union result of the row with ID m and N;
< derived, n >: this line refers to the derived table result generated by the table with ID n. Derived tables may be generated from subqueries in the from statement.
< subquery, n >: this line refers to the materialized subquery result generated by the table with ID n.
type:

The type of query execution, which describes how the query www.sangpi.com is executed. The order of all values from best to worst is: system > const > Eq_ ref > ref > fulltext > ref_ or_ null > index_ merge > unique_ subquery > index_ subquery > range > index > ALL

Common types have the following meanings:

System: if the engine used by the table is accurate for the statistics of table rows (e.g. MyISAM) and there is only one row of records in the table, the access method is system, which is a special case of const.
Const: there is at most one row of matching records in the table, which can be found in one query. It is often used to use all fields of primary key or unique index as query criteria.
eq_ Ref: when querying linked tables, only one row of the previous table corresponds to it in the current table. It is the best join method except system and const. It is often used to use all fields of primary key or unique index asgameConditions for connecting tables.
Ref: using the ordinary index as the query criteria, multiple qualified rows may be found in the query result.
index_ Merge: when multiple indexes are used in the query criteria, the index merge optimization is enabled. At this time, the key column in the execution plan lists the indexes used.
Range: query the range of index columns. The key column in the execution plan indicates which index is used.
Index: the query traverses the entire index tree, which is similar to all, except that the index is scanned, and the index is generally in memory, which is faster.
All: full table scan.
possible_keys:

possible_ The keys column represents the indexes that MySQL may use when executing queries. If this column is null, it indicates that there is no index that may be used; In this case, you need to check the columns used in the where statement to see if you can improve query performance by adding indexes to one or more of these columns.

key:

The key column represents the index actually used by mysql. If NULL, the index is not used.

key_len:

key_ The len column indicates the maximum length of the index actually used by MySQL; When a federated index is used, it may be the length and length of multiple columns. On the premise of meeting the demand, the shorter the better. If the key column displays null, the key_ The len column also displays null.

rows:

The rows column indicates that the number of records or rows to be read is roughly estimated according to the table statistics and selection. The smaller the value, the better.

Extra:

This column contains additional information about MySQL parsing queries. Through this information, you can more accurately understand how MySQL executes queries. Common values are as follows:

Using filesort: the external index is used for sorting, and the intra table index is not used for sorting.
Using temporary: MySQL needs to create temporary tables to store query results, which are common in order by and group by.
Using index: indicates that the query uses an overlay index without returning to the table, and the query efficiency is very high.
Using index condition: indicates that the query optimizer has selected to use the feature of pushing down index conditions.
Using where: indicates that the query uses the where clause for conditional filtering. It usually appears when the index is not used.
Using join buffer (block nested loop): the method of table linked query, which means that when the driven table does not use an index, MySQL will first read out the driven table and put it into the join buffer, and then traverse the driven table and the driven table for query.
Here’s a reminder. When the extra column contains using filesort or using temporary, there may be performance problems in mysql, which should be avoided as much as possible.

Recommended Today

On the mutation mechanism of Clickhouse (with source code analysis)

Recently studied a bit of CH code.I found an interesting word, mutation.The word Google has the meaning of mutation, but more relevant articles translate this as “revision”. The previous article analyzed background_ pool_ Size parameter.This parameter is related to the background asynchronous worker pool merge.The asynchronous merge and mutation work in Clickhouse kernel is completed […]