Do you know what goes on behind the back of mysql from the time you execute SQL to the time you see the results?

One, mysql architecture

Client/Server is a client-to-server architecture that everyone is familiar with. In fact, mysql is similar to it.

  • Multiple clients can connect to a server.
  • The client can send requests to the server for adding, deleting, checking, and modifying data.
  • The server processes the stored data according to the request.

In the real environment, the database server process and the client process may run on different hosts, so mysql uses TCP as the network communication protocol between the client and the server.

When the mysql server is started, the default port is 3306, and other processes on the network can connect to the process by IP address + port number.

How does mysql handle requests

Once the connection is successful, you are ready to interoperate by sending the query SQL and the server returns the results. The process is shown as follows:

1. Connection management

Whenever a client process connects to a server process, the server process creates a thread dedicated to handling the interaction with the client.

When a client exits, it disconnects from the server, but the server does not immediately destroy the threads interacting with the client, but caches them.

If a new client tries to connect, the cached thread is allocated to the client to save overhead by not having to create and destroy threads frequently.

When the connection is established, the client sends the request. To the server, the received request is just a text message that needs further processing.

2. Parse optimization

The server receives the request in text form and then goes through several processes.

— Query cache

The mysql server program caches the results of the previous query, and if the next request is the same, it will directly look up the results from the cache.

This cache can be shared between different clients. For example, if client A sends A query request and client B sends the same request, then the data in the cache can be directly queried.

Note that while querying the cache can sometimes improve system performance, it also comes with cache-related maintenance. As of MySQL 5.7.20, the use of query cache was not recommended and was removed in MySQL 8.0.

— Grammar analysis

If the cache misses, a formal query is required, so the text sent from the client is parsed.

  • Determine if the request syntax is correct.
  • Extract from the text to query the table, query conditions into some internal use of mysql data structures.

— Query optimization

After parsing, the server program can retrieve key information, such as query conditions and tables.

However, since the SQL statements we write may not execute very efficiently, mysql will do some optimization. Such as outer join to inner join, simplified expressions, and so on.

3. Storage engine

After the optimization is done, it can actually be implemented. Who will do it? With this storage engine, mysql server encapsulates the storage and retrieval of data into modules of the storage engine.

“Engine” sounds like a fancy name, right? In fact, it has been called a “table processor”, which receives instructions from the upper layer and then reads or writes data from the table.

InnoDB is the default storage engine for mysql, and MyISAM is the most common one.