Databases Reference
In-Depth Information
The Query Cache
Before even parsing a query, MySQL checks for it in the query cache, if the cache is
enabled. This operation is a case-sensitive hash lookup. If the query differs from a
similar query in the cache by even a single byte, it won't match,
7
and the query pro-
cessing will go to the next stage.
If MySQL does find a match in the query cache, it must check privileges before returning
the cached query. This is possible without parsing the query, because MySQL stores
table information with the cached query. If the privileges are OK, MySQL retrieves the
stored result from the query cache and sends it to the client, bypassing every other stage
in query execution. The query is never parsed, optimized, or executed.
You can learn more about the query cache in
Chapter 7
.
The Query Optimization Process
The next step in the query lifecycle turns a SQL query into an execution plan for the
query execution engine. It has several substeps: parsing, preprocessing, and optimiza-
tion. Errors (for example, syntax errors) can be raised at any point in the process. We're
not trying to document the MySQL internals here, so we're going to take some liberties,
such as describing steps separately even though they're often combined wholly or par-
tially for efficiency. Our goal is simply to help you understand how MySQL executes
queries so that you can write better ones.
The parser and the preprocessor
To begin, MySQL's
parser
breaks the query into tokens and builds a “parse tree” from
them. The parser uses MySQL's SQL grammar to interpret and validate the query. For
instance, it ensures that the tokens in the query are valid and in the proper order, and
it checks for mistakes such as quoted strings that aren't terminated.
The
preprocessor
then checks the resulting parse tree for additional semantics that the
parser can't resolve. For example, it checks that tables and columns exist, and it resolves
names and aliases to ensure that column references aren't ambiguous.
Next, the preprocessor checks privileges. This is normally very fast unless your server
has large numbers of privileges.
7. Percona Server has a feature that strips comments from queries before the hash lookup is performed,
which can help make the query cache more effective when queries differ only in the text contained in
their comments.
