Analysis of the order of statement execution in mysql and the query processing stage

The content of this article is about the sequence of statement execution in MySQL and the analysis of the query processing stage. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

MySQL statements are divided into 11 steps in total, as marked in the figure below. The FROM operation is always executed first, and the LIMIT operation is executed last. Each operation will generate a virtual table, which is used as an input for processing. However, these virtual tables are transparent to the user, but only the last virtual table will be returned as a result. If the corresponding operation is not specified in the statement, the corresponding step will be 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)  HAVING <having_condition>
(9)  ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

Let’s analyze each stage of query processing in detail

1. FORM: Calculate the Cartesian product of the table on the left and the table on the right of FROM. Generate virtual table VT1

2, ON: Perform ON filtering on virtual table VT1, and only those rows that meet the will be recorded in virtual table VT2.

3. JOIN: If OUTER JOIN (such as left join, right join) is specified, the unmatched rows in the retained table will be added to virtual table VT2 as external rows. To generate virtual table VT3, if the rug from clause contains more than two tables, then steps 1 to 3 will be repeated for the result VT3 generated by the previous join connection and the next table until all are processed. to the table.

4. WHERE: Perform WHERE condition filtering on virtual table VT3. Only records that meet the will be inserted into virtual table VT4.

5. GROUP BY: Group the records in VT4 according to the columns in the group by clause to generate VT5.

6. CUBE | ROLLUP: Perform cube or rollup operation on table VT5 to generate table VT6.

7, HAVING: Apply having filter to virtual table VT6, only those that meet

8. SELECT: Perform a select operation, select the specified column, and insert it into virtual table VT8.

9. DISTINCT: Deduplicate records in VT8. Generate virtual table VT9.

10, ORDER BY: Sort the records in virtual table VT9 according to to generate virtual table VT10.

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

Related recommendations:

What are the differences between utf8 and utf8mb4 in mysql?

How to modify the length limit of the group_conca function in mysql

Usage of count() in mysql large table and optimization of count() in mysql

The above is the detailed content of Analysis of the order of statement execution in mysql and the query processing stage. For more information, please follow other related articles on the PHP Chinese website!