MySQL statement execution sequence and MySQL statement execution sequence
MySQL statements are divided into 11 steps. As marked, the first FROM operation is always executed, and the last LIMIT operation is executed. Each operation generates a virtual table, which serves as a processing input, but these virtual tables are transparent to users, however, only the last virtual table is returned as a result. If a clause is not specified in a statement, the corresponding steps are skipped.
Next, we will analyze each stage of query processing.
- FORM: Calculates the Cartesian product for the table on the left and the table on the right of FROM. Generate virtual table VT1
- ON: Virtual table VT1 is filtered ON. Only the rows that match <join-condition> are recorded in virtual table VT2.
- JOIN: If outer join (such as left join and right join) is specified, the unmatched rows in the table are retained and added as external rows to VT2, resulting in virtual table VT3, if the rug from clause contains more than two tables, the result VT3 generated by the previous join and the next table are repeatedly executed in step 1 ~ 3. The three steps are completed until all the tables are processed.
- WHERE: Perform WHERE filter on the virtual table VT3. Only records that match <where-condition> are inserted into the virtual table VT4.
- GROUP: Groups records in VT4 Based on the columns in the group by clause to generate VT5.
- CUBE | ROLLUP: Perform cube or rollup operations on table VT5 to generate table VT6.
- HAVING: Apply having filtering to the virtual table VT6. Only records that meet
- SELECT: Execute the select Operation, select the specified column, and insert it to the virtual table VT8.
- DISTINCT: Deduplicates records in VT8. Generate virtual table VT9.
- ORDER: Sort the records in virtual table VT9 by <order_by_list> to generate virtual table VT10.
- LIMIT: Retrieves records of the specified row, generates the virtual table VT11, and returns the result.
