High performance MySQL (6) query execution mechanism (i)

When you want to optimize query performance, the best way is to figure out how MySQL optimizes and executes the query. Understand the internal mechanism, in order to better implement the design.

When MySQL executes a query, what exactly does it do, look at a picture first:

1, the client sends a query to the server.

2, the server first check the query cache, if hit the cache, then immediately return to the results of the cache, or into the next step.

3, the server side of the SQL parsing, preprocessing, and then by the optimizer to generate the corresponding execution plan.

4, MySQL according to the optimizer generated execution plan, call the storage Engine API to query.

5, return the result to the client

Each of these steps is complex, especially the query optimizer section, and then the specifics of each step are described.

1. Client/server communication protocol

The communication protocol between the MySQL client and the server is "Half-duplex", meaning that at any one time, either the client sends the data to the clients or the client sends the data to it, and these 2 actions cannot occur concurrently. Once a message begins to occur at one end, the other end receives the entire message to respond to the other.

The client uses a separate packet to the server, which is why when the query statement is very long, the parameter max_allowed_packet is particularly important, the server will reject too large data to throw the corresponding error.

In contrast, the server to the client is generally more data, composed of multiple packets, which is why the query must add limit restrictions.

Most library functions that connect to MySQL can obtain all result sets and are cached in memory by default, usually by obtaining all result sets and caching them in memory. But if you need to return a large result set, this is not good because the library function takes a lot of time to store all the result sets, but it also takes up server resources throughout the process of client interaction.

Let's give you an example of PHP to illustrate:

<?php
$link = mysql_connect (' localhost ', ' root ', ' 123456 ');
$result = mysql_query (' SELECT * from emp ', $link);
while ($row = Mysql_fetch_array ($result)) {
//do something
}