Take a look at the MySQL execution plan

Preface:

In the previous article, we learned about slow logging in MySQL. When we filter out specific slow SQL, we need to find a way to optimize it. The first step in optimizing SQL should be to read the SQL execution plan. In this article, we will take a look at MySQL Explain execution plans.

1. Introduction to the execution plan

An execution plan is the specific execution of an SQL statement after it has been optimized by the MySQL query optimizer. MySQL provides EXPLAIN statements to get information about the execution plan. Note that the EXPLAIN statement does not actually execute the related statement, but rather analyzes the statement through the query optimizer to find the optimal query solution and display the corresponding information.

Execution plans are usually used in SQL performance analysis and optimization scenarios. The explain results provide information such as the order in which data tables are queried, the types of data query operations, which indexes can be hit, which indexes are actually hit, and how many rows are queried for each data table.

Explain execution plans support SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. We typically analyze SELECT queries.

1. 2. Smart water bottle

Let’s briefly look at the execution plan for the next query:

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 the code

It can be seen that there are 12 columns in the execution plan result, and the meanings of each column are summarized in the following table:

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 this row references the union result of another row, the value can be NULL. If the ids are the same, the execution sequence is from top to bottom. If the IDS are different, the higher the id value, the higher the priority and the earlier the execution.

Select_type:

Type of query. Common values are:

• SIMPLE: SIMPLE query, excluding 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: SELECT after UNION in a UNION statement.
• DERIVED: Subqueries that appear in FROM will be marked as DERIVED.
• UNION RESULT: RESULT of the UNION query.

The table:

Represents the name of the table 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:

• 
  : this line references the UNION result of the rows with ids M and N;
  ,n>
• 
  : This line references the result of a derived table from a table with ID N. Derived tables may arise FROM subqueries in a FROM statement.
  ,n>
• 
  : This line references the materialized subquery result of the table with id N.
  ,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 common types are as follows:

• System: This is a special case of const if the engine used by the table is accurate for table row count (e.g., MyISAM) and there is only one row in the table.
• Const: There is at most one row of matching records in a table that can be found in a single query. This is often used to query all fields that use a primary key or unique index.
• Eq_ref: When a concatenated table is queried, the row of the previous table has only one corresponding row in the current table. Is the best way to join a table other than system and const. It is often used as a join condition for all fields with a primary key or unique index.
• Ref: If a common index is used as the query condition, multiple rows may be found in the query result.
• Index_merge: When multiple indexes are used in a query condition, 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 index columns. The key column in the execution plan indicates which index is used.
• Index: The query traverses the entire index tree, similar to ALL, but scans the index, which is usually in memory and faster.
• ALL: indicates full table scanning.

Possible_keys:

The possible_keys column shows the possible indexes that MySQL can use when performing a query. If this column is NULL, there are no possible indexes; In this case, you need to examine 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 actual index used by MySQL. If NULL, the index is not used.

Key_len:

The key_len column represents the maximum length of the index actually used by MySQL; When using a federated index, it is possible to add the length of multiple columns. Keep it as short as possible to meet your 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 rows to find or read based on table statistics and selection, with the smaller the better.

Extra:

This column contains additional information about how MySQL parses queries to get a more accurate understanding of how queries are executed. Common values are as follows:

• Using filesort: sort by external index, not by internal index
• Using temporary: MySQL needs to create a temporary table to store the results of a query, as seen in ORDER BY and GROUP BY.
• Using index: indicates that an overwrite index is used in a query and the query efficiency is high.
• Using index condition: indicates that the query optimizer has chosen to use the push under index condition feature.
• Using WHERE: indicates that the query uses the WHERE clause for conditional filtering. This occurs when no index is used.
• Using join buffer (Block Nested Loop) : Indicates that when a driven table does not use an index, MySQL reads the driven table and puts it into the join buffer, then iterates over the driven table and the driven table for query.

If Extra contains Using filesort or temporary, MySQL performance may suffer.

Reference:

• Dev.mysql.com/doc/refman/…
• Juejin. Cn/post / 695344…