The order of statement execution in MySQL and analysis of the query processing phase

This article gives you the content is about MySQL sentence execution sequence and query processing stage analysis, there is a certain reference value, the need for friends can refer to, I hope to help you.

The MySQL statement is divided into 11 steps, as noted, the first execution is always the from operation, and finally the limit operation is performed. Each of these operations produces a virtual table that acts as a processing input, except that the virtual tables are transparent to the user, but only the last virtual table is returned as a result. If the corresponding action is not specified in the statement, the corresponding step is skipped.

(7)  SELECT (8)  DISTINCT <select_list> (1) from  <left_table> (3)  <join_type> join <right_ Table> (2) on  <join_condition> (4)  WHERE <where_condition> (5)  GROUP by <group_by_list > (6) has  
Let's take a concrete look at each phase of query processing

1. Form: The Cartesian product is calculated from the table on the left and the table on the right. Generating a virtual table VT1

2,on: The virtual table VT1 on the filter, only those matching the <join-condition> line will be recorded in the virtual table VT2.

3. Join: If a outer join is specified (such as a LEFT JOIN or right join), rows that are not matched in the reserved table are added as outer rows to the virtual table VT2, resulting in a virtual table VT3, rug If you have more than two tables in the FROM clause, you will repeat the steps for the result VT3 and the next table for the previous join join three steps until all the tables have been processed.

4. Where: the Where condition is filtered on the virtual table VT3. Only records that match <where-condition> will be inserted into the virtual table VT4.

5, groupby: According to the column in the GROUP BY clause, the records in VT4 are grouped to produce VT5.

6, CUBE | ROLLUP: A cube or ROLLUP operation is performed on the table VT5, resulting in a table VT6.

7,having: the virtual table VT6 application has filtering, only the records that match 
8. Select: Perform a select operation, select the specified column, and insert into the virtual table VT8.

9, DISTINCT: The VT8 in the record to go to heavy. Generates virtual table VT9.

10. Orderby: The records in virtual table VT9 are sorted by <order_by_list>, resulting in virtual table VT10.

11, LIMIT: Take out the records of the specified row, generate the virtual table VT11, and return the results.