Learn MySQL execution plan

1. Introduction to the Implementation Plan

An execution plan refers to the specific execution method of a SQL statement after it is optimized by the MySQL query optimizer. MySQL provides us with the EXPLAIN statement to obtain relevant information about the execution plan. It should be noted that the EXPLAIN statement does not actually execute the related statements, but analyzes the statements through the query optimizer, finds the optimal query solution, and displays the corresponding information.

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

explain execution plans support SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. We generally use it to analyze select query statements.

2. Execution plan in practice

Let's take a quick look at the execution plan for 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 |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

As you can see, there are 12 columns in the execution plan results. The meaning of each column is summarized in the following table:

Let's take a look at some important columns in the execution plan:

id:

SELECT identifier. This is the ordinal number of the SELECT in the query. The value can be NULL if the row refers to the union result of other rows. When the ids are the same, the execution order is from top to bottom; when the ids are different, the larger the id value, the higher the priority, and the earlier it is executed.

select_type:

The type of query. Common values ​​are:

• SIMPLE: Simple query, does not contain UNION or subquery.
• PRIMARY: If the query contains subqueries or other parts, the outer SELECT will be marked as PRIMARY.
• SUBQUERY: The first SELECT in a subquery.
• UNION: In a UNION statement, the SELECT that appears after UNION.
• DERIVED: Subqueries that appear in FROM will be marked as DERIVED.
• UNION RESULT: The result of a UNION query.

table:

Indicates the table name used for the query. Each row has a corresponding table name. In addition to normal tables, the table name may also be the following values:

• <unionM,N>: This row references the UNION result of rows with ids M and N;
• <derivedN>: This row references the derived table result generated by the table with id N. It is possible for a derived table to be created from a subquery in the FROM clause.
• <subqueryN>: This row references the materialized subquery result generated by the table with id N.

type:

The type of query execution, which describes how the query 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

The specific meanings of several common types are as follows:

• system: If the engine used by the table is accurate in counting the number of rows in the table (such as MyISAM), and there is only one row in the table, the access method is system, which is a special case of const.
• const: There is at most one matching row in the table, which can be found with one query. It is often used to use all fields of the primary key or unique index as query conditions.
• eq_ref: When querying a joined table, there is only one row in the current table that corresponds to the row in the previous table. It is the best join method besides system and const, and is often used to use all fields of the primary key or unique index as the join condition.
• ref: Use a common index as the query condition. The query result may find multiple rows that meet the condition.
• index_merge: When the query condition uses multiple indexes, it means that the Index Merge optimization is enabled. In this case, the key column in the execution plan lists the indexes used.
• range: Performs a range query on the index column. 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:

The possible_keys column indicates the possible indexes that MySQL may use when executing the query. If this column is NULL, it means that there is no index that may be used; in this case, you need to check the columns used in the WHERE clause to see if you can improve query performance by adding indexes to one or more of these columns.

key：

The key column indicates the index actually used by MySQL. If NULL, the index is not used.

key_len:

The key_len column indicates the maximum length of the index actually used by MySQL; when a joint index is used, it may be the sum of the lengths of multiple columns. The shorter the better, as long as it meets the needs. If the key column displays NULL, the key_len column also displays NULL.

rows:

The rows column represents a rough estimate of the number of records to be found or rows to be read based on table statistics and selection conditions. The smaller the value, the better.

Extra:

This column contains additional information about how MySQL parses the query. This information can help you understand more accurately how MySQL executes the query. Common values ​​are:

• Using filesort: External index sorting is used for sorting, and the internal index of the table is not used for sorting.
• Using temporary: MySQL needs to create a temporary table to store query results, which is common in ORDER BY and GROUP BY.
• Using index: indicates that the query uses a covering index and does not require table return, so the query efficiency is very high.
• Using index condition: Indicates that the query optimizer chooses to use the index condition pushdown feature.
• Using where: indicates that the query uses the WHERE clause for conditional filtering. This usually occurs when the index is not used.
• Using join buffer (Block Nested Loop): This is a table-joining query method. When the driven table does not use an index, MySQL will first read the driving table and put it into the join buffer, and then traverse the driven table and the driving table for query.

Here is a reminder that when the Extra column contains Using filesort or Using temporary, MySQL performance may have problems and should be avoided as much as possible.

The above is the detailed content of learning MySQL execution plan. For more information about MySQL execution plan, please pay attention to other related articles on 123WORDPRESS.COM!