Table of Contents
Let us start with a general description of the MySQL statement processing workflow in order to provide the reader with understanding of the problem of reexecution and vocabulary for the following sections.
Conventional statements, that is, SQL queries sent in
COM_QUERY protocol packet, are the only
statements present in MySQL server prior to version 4.1. Execution
of such statements is performed in a batch mode, one query processed
by the server at a time. The original implementation is streamlined
for this mode and has a single global connection state
THD shared among all operational steps.
When executing a query in conventional mode, the server sequentially parses its text, acquires table level locks, analyzes the parsed tree, builds an execution plan, executes the built plan and releases the locks.
Memory for parsing is allocated using block allocator
MEM_ROOT in 4k chunks and freed once in the end
of execution. Memory for execution is allocated in the memory root
of the parsed tree, as well as in the system heap, and in some cases
in local "memory roots" of execution modules.
The role of the parser is to create a set of objects to represent
the query. E.g. for a
SELECT statement, this set
includes a list of Item's for
SELECT list, a list
of tables (
TABLE_LIST object for each table) for
FROM clause, and a tree of Item's for
During context analysis phase, links are established from the parsed
tree to the physical objects of the database, such as open tables
and table columns. A physical table is represented by a heir of
class handler that corresponds to the storage engine the table
belongs to, and is saved in
When context analysis is done, the query optimizer is run. It performs two major tasks:
Query transformation — a transformation of the parsed tree to an equivalent one, which is simpler and more efficient to execute.
Creation of an execution plan, including evaluation of an order of joins and initialization of methods to access the used tables. At this step parts of the execution plan are attached to the parsed tree.
Finally, the query is passed to the execution runtime — an interpreter that operates with and modifies both the parsed tree and the execution plan in order to execute the query.
It should be noted that the overall procedure is infamous for
breaking borders between abstraction layers. For example, MySQL
performs [sub]query transformation during context analysis;
moreover, most parts of the code rely on the fact that
THD is processing only one statement at a time.