Databases Reference
In-Depth Information
X.509 certificates can also be used across an SSL (Secure Sockets Layer) connection.
Once a client has connected, the server verifies whether the client has privileges for
each query it issues (e.g., whether the client is allowed to issue a
SELECT
statement that
accesses the
Country
table in the
world
database).
Optimization and Execution
MySQL parses queries to create an internal structure (the parse tree), and then applies
a variety of optimizations. These can include rewriting the query, determining the order
in which it will read tables, choosing which indexes to use, and so on. You can pass
hints to the optimizer through special keywords in the query, affecting its decision-
making process. You can also ask the server to explain various aspects of optimization.
This lets you know what decisions the server is making and gives you a reference point
for reworking queries, schemas, and settings to make everything run as efficiently as
possible. We discuss the optimizer in much more detail in
Chapter 6
.
The optimizer does not really care what storage engine a particular table uses, but the
storage engine does affect how the server optimizes the query. The optimizer asks
the storage engine about some of its capabilities and the cost of certain operations, and
for statistics on the table data. For instance, some storage engines support index types
that can be helpful to certain queries. You can read more about indexing and schema
optimization in
Chapter 4
and
Chapter 5
.
Before even parsing the query, though, the server consults the query cache, which can
store only
SELECT
statements, along with their result sets. If anyone issues a query that's
identical to one already in the cache, the server doesn't need to parse, optimize, or
execute the query at all—it can simply pass back the stored result set. We write more
about that in
Chapter 7
.
Concurrency Control
Anytime more than one query needs to change data at the same time, the problem of
concurrency control arises. For our purposes in this chapter, MySQL has to do this at
two levels: the server level and the storage engine level. Concurrency control is a big
topic to which a large body of theoretical literature is devoted, so we will just give you
a simplified overview of how MySQL deals with concurrent readers and writers, so you
have the context you need for the rest of this chapter.
We'll use an email box on a Unix system as an example. The classic
mbox
file format
is very simple. All the messages in an
mbox
mailbox are concatenated together, one
after another. This makes it very easy to read and parse mail messages. It also makes
mail delivery easy: just append a new message to the end of the file.
