Databases Reference
In-Depth Information
The Query Execution Engine
The parsing and optimizing stage outputs a query execution plan, which MySQL's
query execution engine uses to process the query. The plan is a data structure; it is not
executable byte-code, which is how many other databases execute queries.
In contrast to the optimization stage, the execution stage is usually not all that complex:
MySQL simply follows the instructions given in the query execution plan. Many of the
operations in the plan invoke methods implemented by the storage engine interface,
also known as the
handler API
. Each table in the query is represented by an instance
of a handler. If a table appears three times in the query, for example, the server creates
three handler instances. Though we glossed over this before, MySQL actually creates
the handler instances early in the optimization stage. The optimizer uses them to get
information about the tables, such as their column names and index statistics.
The storage engine interface has lots of functionality, but it needs only a dozen or so
“building-block” operations to execute most queries. For example, there's an operation
to read the first row in an index, and one to read the next row in an index. This is
enough for a query that does an index scan. This simplistic execution method makes
MySQL's storage engine architecture possible, but it also imposes some of the optimizer
limitations we've discussed.
Not everything is a handler operation. For example, the server manages
table locks. The handler might implement its own lower-level locking,
as InnoDB does with row-level locks, but this does not replace the
server's own locking implementation. As explained in
Chapter 1
, any-
thing that all storage engines share is implemented in the server, such
as date and time functions, views, and triggers.
To execute the query, the server just repeats the instructions until there are no more
rows to examine.
Returning Results to the Client
The final step in executing a query is to reply to the client. Even queries that don't
return a result set still reply to the client connection with information about the query,
such as how many rows it affected.
If the query is cacheable, MySQL will also place the results into the query cache at this
stage.
The server generates and sends results incrementally. Think back to the single-sweep
multijoin method we mentioned earlier. As soon as MySQL processes the last table and
generates one row successfully, it can and should send that row to the client.
