The display of MySQL execution plan does not match the execution process

The display of MySQL execution plan does not match the execution process

The process of creating a table and phenomena is as follows:

Create table t1 (id1 INT, a1 INT, b1 INT, primary key (id1 ));
Create table t3 (id3 int unique, a3 INT, b3 INT );

Insert into t1 VALUES (1, 1, NULL );
Insert into t3 VALUES (1, 1, NULL );

Mysql> select * from (select * from t1 where id1 = (select id3 from t3 where id3 = 1) t;
+ ----- + ------ +
| Id1 | a1 | b1 |
+ ----- + ------ +
| 1 | 1 | NULL |
+ ----- + ------ +
1 row in set (0.01 sec)

Mysql> explain extended select * from (select * from t1 where id1 = (select id3 from t3 where id3 = 1) t;
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| 1 | PRIMARY | <derived2> | system | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | t1 | const | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 3 | SUBQUERY | t3 | const | id3 | id3 | 5 | const | 1 | 100.00 | Using index |
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
3 rows in set, 1 warning (0.00 sec)

Mysql> show warnings;
+ ------- + ------ + ------------------------------------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + ------------------------------------------------------------------------- +
| Note | 1003 |/* select #1 */select '1' AS 'id1', '1' AS 'a1', null as 'b1 'from dual |
+ ------- + ------ + ------------------------------------------------------------------------- +
1 row in set (0.00 sec)

Second analysis:
First, check the execution plan:
The select_type value of 1 id is PRIMARY, which is a '<derived2>' table, indicating that the subquery appears in the FROM clause, in addition, the content of this row is a 'virtual table' similar to "shell" and the number 2 in '<derived2>' comes from the result of the id 2 in the next row. The subquery alias t does appear in the FROM at the outermost layer.
The select_type value of 2 id is DERIVED, indicating that this is a driven table (subquery in the from clause being driven ).
The select_type value of 3 id is SUBQUERY, which indicates the second SUBQUERY In the nested table t3, it is not optimized to other types (for example, subqueries can be optimized to internal connections after they are eliminated ).

Next, check the warning information:
1. the table after the FROM clause changes to the virtual table 'dual', and t1 and t2 all disappear. Why?
2 In the optimization process, MySQL learns the following by using the nature of equations:
2.1 id1 = id3 = 1
2.2 The query statement can be transformed from logical reasoning to: select * from t1 where id1 = 1
At this point, the subquery is actually eliminated, but it is not reflected in the execution plan. This is an error in the execution plan.
2.3 Further, the id1 column in table t1 is the primary key. Therefore, you can check * the value of the target Column Based on the index. In this way, the value of the target column can be changed:
Select '1' AS 'id1', '1' AS 'a1', null as 'b1 'from t1 where id1 = 1
2.4 because the value of the target column can be known during optimization, the table in the FROM clause can be identified as a constant table ".
3. After the query execution plan is displayed (the explain command is later), the Code has a judgment: if all the tables are constant tables and are "optimized" (optimized_away) ", replace the table object in the FROM clause with" dual. This is why "from dual" appears in the warning.

Again, the execution plan and warning information are different. So, who will execute MySQL during execution?
This can be analyzed by tracking code.
The execution process is as follows:
1. Optimization stage: the execution plan generation stage
The process of MySQL in the optimization stage is like the results displayed in the execution plan. The execution plan display process is performed on each clause, first, because the subquery in the from clause executes the process of id 1, and then optimizes the drive id as 2, in the process of not ending the optimization of id as 2, it is found that the sub-query with id 2 is a subquery, And the subquery is optimized by nested calls, which leads to equality simplification and other processes.
In the process of using various technologies to simplify, some values or results have been learned, and some solutions have been completed.
This also indicates a problem: Some friends ask the serial number between IDs to indicate the execution order? Because of the nested relationship, the first step is to start a query clause with a small id value and a large id value during execution, the first to complete the execution is the person with a large id value; the first to execute is the person with a small id value.
2 execution phase:
It is known that it is a constant solution, and the results are known during the optimization process and are directly output.
This also explains the "from dual" in the warning information, which is consistent with the meaning of the execution process. That is, in the execution phase, the query plan indicates that the process has ended.

Iii. Additional instructions

Mysql> explain extended select * from (select * from t1 where id1 in (select id3 from t3 where id3 = 5) t;
->;
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
| 1 | PRIMARY | <derived2> | system | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | t1 | const | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | t3 | const | id3 | id3 | 5 | const | 1 | 100.00 | Using index |
+ ---- + ------------- + ------------ + -------- + --------------- + --------- + ------- + ------ + ---------- + ------------- +
3 rows in set, 1 warning (49.53 sec)

Mysql> show warnings;
+ ------- + ------ + ------------------------------------------------------------------------- +
| Level | Code | Message |
+ ------- + ------ + ------------------------------------------------------------------------- +
| Note | 1003 |/* select #1 */select '5' AS 'id1', '5' AS 'a1', null as 'b1 'from dual |
+ ------- + ------ + ------------------------------------------------------------------------- +
1 row in set (0.00 sec)

The SQL statement IN "Heading 3" and "Heading 1" differs from the nested subquery IN the form of equal signs or IN.

The IN form is identified by MySQL to optimize the logo subquery without "DERIVED". This is the form IN which MySQL currently only supports Optimization of sub-queries IN the form of IN. For such forms, not supported. However, in terms of equality, the SQL statement in "Title 1" is finally optimized.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: