Databases Reference
In-Depth Information
Query Execution Basics
If you need to get high performance from your MySQL server, one of the best ways to
invest your time is in learning how MySQL optimizes and executes queries. Once you
understand this, much of query optimization is a matter of reasoning from principles,
and query optimization becomes a very logical process.
In other words, it's time to revisit what we discussed earlier: the process MySQL follows
to execute queries. Follow along with
Figure 6-1
to see what happens when you send
MySQL a query:
1. The client sends the SQL statement to the server.
2. The server checks the query cache. If there's a hit, it returns the stored result from
the cache; otherwise, it passes the SQL statement to the next step.
3. The server parses, preprocesses, and optimizes the SQL into a query execution
plan.
4. The query execution engine executes the plan by making calls to the storage engine
API.
5. The server sends the result to the client.
Each of these steps has some extra complexity, which we discuss in the following sec-
tions. We also explain which states the query will be in during each step. The query
optimization process is particularly complex and important to understand. There are
also exceptions or special cases, such as the difference in execution path when you use
prepared statements; we discuss that in the next chapter.
The MySQL Client/Server Protocol
Though you don't need to understand the inner details of MySQL's client/server pro-
tocol, you do need to understand how it works at a high level. The protocol is half-
duplex, which means that at any given time the MySQL server can be either sending
or receiving messages, but not both. It also means there is no way to cut a message short.
This protocol makes MySQL communication simple and fast, but it limits it in some
ways too. For one thing, it means there's no flow control; once one side sends a message,
the other side must fetch the entire message before responding. It's like a game of tossing
a ball back and forth: only one side has the ball at any instant, and you can't toss the
ball (send a message) unless you have it.
The client sends a query to the server as a single packet of data. This is why the
max_allowed_packet
configuration variable is important if you have large queries.
5
Once
the client sends the query, it doesn't have the ball anymore; it can only wait for results.
5. If the query is too large, the server will refuse to receive any more data and throw an error.
