Basics of MySQL Query execution

When you want MySQL to run queries with higher performance, the best way is to figure out how MySQL optimizes and executes queries. Once this is understood, many query optimizations actually follow some principles that allow the optimizer to operate in a reasonable manner as expected.

In other words, it's time to look back at what we discussed earlier: the process of MySQL executing a query. What did MySQL do when it sent a request to MySQL.

1 The client sends a query to the server.

2 The server first checks the cache, and if the cache is hit, returns the results stored in the cache immediately, otherwise entering the next stage.

3 The server performs SQL parsing, preprocessing, and then generates the corresponding execution plan by the optimization generator.

4 MySQL invokes the API of the storage engine to execute the query, based on the execution plan generated by the optimizer.

The 5 strong results are returned to the client.

Each of the above steps is more than the expected load, and we will continue to discuss them in subsequent chapters. We'll see what the state is in each phase of the query. The query optimizer is a particularly complex and particularly difficult part to understand. There are many exceptions, for example, when the query uses bound variables, the execution path will be different, and we'll discuss this in the next chapter.

A MySQL client/server communication protocol

In general, there is no need to understand the internal implementation details of the MySQL communication protocol, just a general understanding of how the communication protocol works. The communication protocol between MySQL client and server is "half duplex", which means that at any one time, either the server sends the data to the client or the client sends the data to the server, and the two actions cannot occur simultaneously. Therefore, we cannot and do not need to cut a message into small pieces to send independently.

This protocol makes MySQL communication simple and fast, but it also restricts MySQL from many places. One obvious limitation is that this means that traffic control is not possible. Once a message begins to occur at one end, the other end receives the entire message to respond to it. It's like tossing and dropping games: at any moment only one person can control the ball, and only the side that controls the ball can throw it back (send a message).

The client passes the query to the server with a separate packet. This is why the parameter max_allowed_packet is especially important when the query's statement is long. Once the client sends a request, what it can do is just wait for the result.

Conversely, the average server responds to customers with a lot of data, consisting of multiple packets. When the server starts responding to the client request, the client must fully accept the entire return result, not simply take the first few results, and then the server stops sending the data, in which case the client receives the complete result, then takes the previous several required results, or after receiving a few results, the rough disconnection , is not a good idea. This is also the reason why you must add limit limits to query statements when necessary.

Another way to explain this behavior is that when the client pulls data from the server, it looks like a process of data pulling, but it is actually the process of MySQL pushing the data to the client. Clients are constantly receiving data pushed from the server, and the client is unable to stop the server.

Most of the library functions that connect to MySQL can get all the result sets and cache them in memory, and you can get the required data row by line. The default is generally to get all result sets and cache them in memory. MySQL usually waits for all of the data to be sent to the client to free up the resources that the query takes, and all that is usually done to reduce the server pressure so that the query can end early and release the appropriate resources early.

When retrieving data from MySQL using most of the library functions that connect to MySQL, the result looks like data fetched from the MySQL server, and actually reads the data from the buffer of the library function. This is not a problem in most cases, but if you need to return a large result set, it is not good to go, because the library function takes a lot of time and memory to store all the result sets. If you can start processing these result sets as early as possible, you can greatly reduce the memory consumption, in which case the results can be processed without using the cached records. The disadvantage of this is that, for the server, the query must be completed before releasing resources, so in the process of interacting with the client, the server's resources are occupied by this query.

Query status

For a MySQL connection, or a thread, there is a state at any moment that indicates what MySQL is currently doing. There are many ways to view the current state, and the worst is to use the show full Processlist command (which returns the command column in the result to indicate the current state). In the life cycle of a query, the turntable changes back many times. MySQL official collection The most authoritative explanations for the meanings of these status values are listed below, and a simple explanation is made.

Sleep

The thread is waiting for the client to send a new request

Query

The thread is executing a query or is sending the results to the client.

Locked

At the MySQL server layer, the thread is waiting for a table lock. Locks implemented in the storage engine, such as InnoDB row locks, are not reflected in the thread state. This is a more typical state for MyISAM, but it also appears in other engines that do not have a row lock.

Analyzing and statistics

The thread is collecting statistics for the storage engine and generating the execution plan for the query.

Copying to TMP table "on disk"

The thread is executing the query and copying its result set to a temporary table, which is typically either a group by operation, a file sort operation, or a union operation. If there is an on disk tag behind this state, it means that MySQL is putting a temporary memory table on disk.

sorting result

The thread is sorting the result set.

Sending data

This means that a thread may be transferring data between multiple states, or a result set, or returning data to the client.

Understanding the alert meanings of these states is useful, which allows you to get a quick idea of who is holding the ball now. On a busy server, you may see a large number of unhealthy states, such as statistics, that are taking up a lot of time. This usually means that there is an exception somewhere.

　　

Basics of MySQL Query execution