The following figure shows the typical execution process of an SQL query from its reception by the SQL engine.
Note
This execution process applies to DML and SELECT statements. Other statements (such as DCL) do not include steps like the optimizer.
The following table describes modules involved in SQL query execution.
| Module | Description |
|---|---|
| Fast parameterization module | The fast parameterization module converts constants in SQL statements into parameters, then uses the parameterized SQL text as a key to retrieve the execution plan from the plan cache. This allows SQL statements that differ only in their parameters to share the same execution plan. In other words, it uses only lexical analysis to directly parameterize the text string, obtaining the parameterized text and constant parameters. |
| Plan cache | The plan cache stores the execution plan generated for every unique SQL query in the memory to speed up SQL query processing. The cached plans can be repeatedly executed in the future, which avoids repeated query optimization. |
| Parser | After the parser receives an SQL query string sent by a user, it splits the string into "words" and parses the entire query based on the preset syntax rules. Then, it converts the SQL query string into an in-memory data structure with syntax structure information, which is called a syntax tree. |
| Resolver | The resolver converts the syntax tree generated by the parser into an internal data structure with database semantic information. During the conversion, the resolver translates tokens in the SQL query into the corresponding objects (such as libraries, tables, columns, and indexes) based on the database metadata to generate a statement tree. |
| Query rewrite | Query rewrite refers to transforming an SQL statement into another form that is easier to optimize. It analyzes the semantics of the user's SQL and, based on internal rules or cost models, rewrites the user's SQL into other equivalent forms, which are then provided to the optimizer for further optimization. Query rewriting works by performing equivalent transformations on the original syntax tree, and the result of the transformation is still a syntax tree. |
| Optimizer | As the core of the SQL tuning process, the optimizer generates the optimal execution plans for SQL queries. During the optimization, the optimizer needs to comprehensively consider various factors, such as SQL query semantics, object data characteristics, and the physical distribution of objects. It solves many problems such as access path selection, connection order selection, connection algorithm selection, and distributed plan generation. Finally, the optimizer selects the best execution plan for each SQL query. |
| Code generator | The code generator converts an execution plan to executable code without any optimization. |
| Executor | Initiates the execution process of the SQL statement.
|