Interpreting MySQL client and server protocols

If you need to get high performance from your MySQL server, the best way is to spend time studying the mechanics of how MySQL optimizes and executes queries. Once you understand these, most query optimizations are well-founded, making the entire query optimization process more logical. The following diagram shows the process of MySQL executing a query:

1. The client sends the SQL statement to the server.
2. The server checks the query cache. If there is data in the cache, the cached result is returned directly; otherwise, the SQL statement is passed to the next link.
3. After the server parses, preprocesses and optimizes the SQL statement, it passes it to the query optimizer to form a query plan.
4. The query execution engine executes the query plan by calling the storage engine interface.
5. The server returns the query results to the client.

The above steps are all complex, and the next few articles will describe each link in detail. The query optimization process is particularly complex and important to understand.

MySQL Client/Server Protocol

Although it is not necessary to understand the internal details of the MySQL client/server protocol, it is necessary to understand how it works at a high application level. This protocol is half-duplex, which means that the MySQL server cannot send and receive messages at the same time and cannot split messages into multiple shorter messages. On the one hand, this mechanism makes MySQL communication simple and fast, but on the other hand, it also adds some restrictions. For example, this means that flow control is impossible and once one party sends a message, the other party must receive the entire message before responding. It's like playing table tennis back and forth. Only one side has the ball at a time, and only when you receive the ball can you hit it back.

The client sends queries to the server in a single packet, so it is important to configure max_allowed_packet when there are large queries. Once the client sends a query, it can only wait for the results to be returned.

In contrast, the server's response usually consists of multiple packets. Once the server responds, the client must retrieve the entire result set. The client can't simply fetch a few rows and then tell the server not to send the rest of the data. If the client only needs to return the first few rows of data, it can only wait for the server to return all the data and then discard the unnecessary data, or rudely disconnect. Neither method is a good choice, so a suitable LIMIT clause is very important.

Most MySQL connection libraries support fetching the entire result set and caching it in memory, or fetching the required data rows. The default behavior is usually to fetch the entire result set and cache it in memory. It is important to know this because the MySQL server will not release the locks and resources for this query until all requested rows are returned. Most client libraries will make you think that the data is being retrieved from the server, but in reality the data may just be read from the cache. This is fine most of the time, but it is not suitable for large data queries that take a long time or occupy a lot of memory. If you specify not to cache query results, the memory usage will be smaller and the results can be processed faster. The disadvantage is that this method will cause server-side locks and resource usage during queries.

Taking PHP as an example, the following are commonly used query codes in PHP:

<?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //Processing data results}

?>

This code looks like it is fetching only the rows that are needed. However, this query actually puts all the results into memory after calling mysql_query. The while loop actually iterates the data in the memory. Conversely, if you use mysql_unbuffered_query instead of mysql_query, the results are not cached.

<?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_unbuffered_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //Processing data results}

?>

Different programming languages ​​handle cache overwrites differently. For example, the Perl DBD::mysql driver needs to specify the C-language client library (the default is mysql_buffer_result) through the mysql_use_result attribute, as shown below:

#!/usr/bin/perl

use DBI;
my $dbn = DBI->connect('DBI:mysql:;host=localhost', 'user', 'password');
my $sth = $dbn->prepare('SELECT * FROM huge_table', {mysql_use_result => 1});
$sth->execute();
while (my $row = $sth->fetchrow_array()) {
	#Processing data results}

Note that prepare specifies to use the result rather than cache it. It can also be specified when connecting, which will cause each query to not be cached.

my $dbn = DBI->connect('DBI:mysql:;mysql_use_result=1;host=localhost', 'user', 'password');

The above is the detailed interpretation of MySQL client and server protocols. For more information about MySQL client and server protocols, please pay attention to other related articles on 123WORDPRESS.COM!