MySQL system architecture

has not written a blog for a long time, usually feel no time to write, but there is a lot of time to wander, to do some useless things. Blogging is quite a process of training people, not only exercise writing ability, insist on writing can also greatly improve the technical level, write to also can have the vast number of netizens to help us correct, together to explore the problem. Usually have a lot of knowledge point indefinitely, may put a vacation, the only knowledge point also with the wind to fly to distant. Technology is endless, many times will let us overwhelmed, but there are a lot of knowledge points is original aim, grasp two principles, less is more, fast is slow. have enough depth on one aspect, and then expand the breadth. Recently began to study MySQL in-depth, like-minded people walk together.

The following is based on "MySQL Performance Tuning and architecture design" to do the knowledge point summary, interested friends can read this book, after all, according to the book to learn the comparison system. Can grasp the whole knowledge context.

First, the MySQL logic module composition

MySQL can be seen as a two-tier architecture, the first layer called the SQL layer, this part of the main function is to complete the MySQL database system before processing the underlying data of all the preparation work, including authority judgment, SQL parsing, Execution plan optimization, query cache processing, etc. The second layer is the storage engine layer (Storage engine layers), which is the implementation of database system data access operation, which is completed by a variety of storage engines.

　　

It looks like a simple structure, but each layer contains a lot of small modules.

The SQL layer layer contains the following modules:

1. Initialize the module

Initialization module is when the MySQL server starts, the whole system to do a variety of initialization operations, such as various buffer,cache structure initialization and memory space applications, the initialization of various system variables, various storage engine initialization settings, etc.

2. Core API

The core API module is designed to provide some optimization implementations that require very efficient underlying operations, including implementations of various underlying data structures, implementation of special algorithms, character channeling, digital processing, small file I/O, formatted output, and most important memory management. All source code for the core API module is concentrated under the Mysys and Strings folders.

3. Network Interaction Module

The underlying network interaction module abstracts the interface APIs used by the underlying network interaction, and realizes the receiving and sending of the underlying network data to facilitate the invocation of various other modules. and partial maintenance of this procedure. All source code is under the Vio folder.

4. Client & Server Interaction Protocol Module

Any of the C/s structure of the software system, will certainly have their own unique information exchange protocol, MySQL is no exception, MySQL client & Server Interactive Protocol module part, to achieve the clients and MySQL in the process of interaction with all the protocols. Of course, these protocols are built on existing OS and network protocols. such as TCP/IP, Unix sockets.

5. User Module

The user module realizes the function, mainly includes the user's login connection permission control and the user's authorization management. Like the door guard of MySQL, decide whether to "open the door" to the visitor.

6. Access Control Module

What do you want to do when you visit the guests? For security reasons, it must not be so casual. The access control module is required to monitor the guest's actions in real time and give different privileges to different guests. The function of the access control module is to control the user's access to the data according to the authorization information of each user in the user module and various constraints peculiar to the database itself. Both the user module and the Access control module combine to form the function of security management of the entire MySQL database system.

7. Connection management, connection thread, thread management module

The connection management module listens to various requests for MySQL server, accepts connection requests, forwards all connection requests to the thread management module, and each client request for MySQL server on each connection is assigned (or created) a connection thread for its own service. The main task of the connection thread is to be responsible for the MySQL Server communication with the client, accepting the client's command request. Delivers the result information for the server side. The thread management module is responsible for managing and maintaining these connection threads. Also includes thread creation, thread cache, etc.

8. Query parsing and forwarding module

In MySQL, we used to send all client-side commands to the server as query, and in MySQL server, the connection thread accepted a query from the client. The query is passed directly to the appropriate processing module, which is specifically responsible for classifying the various query types and then forwarding the query parsing and forwarding module. The main task is to make the query statement semantic and grammatical analysis, then according to different types of operation classification, and then make targeted forwarding.

9. Query Cache Module

The query cache module is a very important module in MySQL, his main function is to submit the client to the MySQL Select Class query request return result set cache into memory, and a hash value of the query to do a corresponding. After any data changes occur to the base table of the data taken by the query, MySQL automatically invalidates the query's cache. In applications with very high read-write ratios, Query cache is a significant improvement in performance. Of course, his memory consumption is also huge.

. query Optimizer Module

Query optimizer, which is to optimize the client request query, according to the client request query statement, and the database of some statistics, on the basis of a series of algorithms to analyze, to obtain an optimal strategy, tell the following program how to get the results of this query statement.

11. Table Change Management module

The table change Management module is primarily responsible for the processing of some DML and DDL queries, such as UPDATE, delete, insert, create Table,alter table, and so on.

12. Table Maintenance Module

Table status checks, bug fixes, and optimizations and analyses are all things that the table maintenance module needs to do.

13. System Status Management module

The System State Management module is responsible for returning various state data to the user when the client requests the state of the system, such as the show Status command commonly used by the DBA, the show variables command, etc., and the resulting results are returned by this module.

14. Table Manager

This module is confused with the table change and table maintenance module from the name, but its function is completely different from the table change and maintenance module. As you know, every MySQL table has a table definition file, which is still a *.frm file. The main task of the Table manager is to maintain these files, as well as a cache, where the main content of the cache is the structure information of the individual tables. In addition, he maintains table-level lock management.

15. Log Recording Module

Responsible for the entire system level of the logical layer of log records, including error log, binary log, show query log and so on.

16. Copy the module

The replication module can be divided into Master module and slave module, Master module is mainly responsible for reading the binary log of master in the replication environment, and interacting with the I/O thread of the slave side. The slave module has a little more to do than the master module. In the system is mainly embodied in two threads above. One is responsible for requesting and accepting binary logs from Master and writing the I/O thread in the local relay log. The other is responsible for reading the relevant log file from the relay log, and then parsing it into a SQL thread that can be executed correctly on the slave side and get exactly the same results as the master side Minin to slave execution.

17. Storage Engine Interface Module

The storage Engine interface module is one of the most distinctive features of the MySQL database. Currently, only MySQL can implement the plug-in management of its underlying data storage engine in a variety of database products. This module is actually just an abstract class, but it is because it successfully abstracts the various database processing, it is the feature of today's MySQL pluggable storage engine .

• You can see that only the first layer has so many modules, a simple and easy-to-use database, its underlying implementation is also ultra-complex.

Second, the work of each module to cooperate

The point has come. We use an example to illustrate how the various modes of the MySQL system are loving each other and accomplishing a very simple query that we consider to be.

We start MySQL, the client establishes a connection, requests query, gets the result returned, and eventually exits. Such an entire process to be analyzed.

　　First step : When we execute the command to start the MySQL system, theMySQL initialization module reads the system parameters and command line parameters from the system configuration file and initializes the entire system with parameters, such as requesting and allocating buffer, initializing global variables, and various structures and so on. At the same time, the various storage engines are also initiated to perform their own initialization work. When the whole system is initialized, and the Connection Management module takes over, the connection management module initiates the listener that handles the client connection request, including TCP/IP network monitoring, and the UNIX socket, when MySQL serve is basically started, Ready to accept client requests.

　　The second step : When the connection Management module hears the client's connection request (with the help of the Network interaction module function), the two parties through the client & Server Interaction Protocol module defined by the protocol "greeting" a few words, The connection Management module forwards the connection request to the thread management module to request a connection thread.

　　The third step : the thread management module then give control to the connection thread module , tell the connection thread module, now I have the connection request come over, need to establish a connection, you hurry to deal with. After receiving the connection request, the connection thread module first checks whether there is an idle connection thread in the current connection thread pool, and if so, takes out one and the client request connection, and if there is no idle connection thread, establishes a new connection thread to connect with the client request. Of course, the connection thread module does not remove a connection thread and a client connection immediately after receiving the connection request, but rather first by invoking the user module for authorization checks, and only after the client request passes the authorization check will the client request and the connection line responsible for the request be thread attached.

In MySQL, the client's request is divided into two types, one is query, need to call parser that is, query parsing and forwarding module parsing to be able to execute the request ; You do not need to call parser to execute the request. If our initialization configuration opens the functionality of full Query logging, then the Query parsing and forwarding module invokes the logging module to count the requests into the log. Either a Query-type request or a command-type request is logged into the log, so the full Query logging feature is rarely turned on for performance reasons.

　　Fourth Step : When the client request and the connection thread "Exchange signal (Interworking protocol)" connected to the top, the connection thread will begin to process the client request sent over the various commands (or query), to accept the relevant request. He forwards the query statements received to query parsing and forwarding modules , the query parser first basic semantic and syntactic parsing, and then depending on the type of command, some will be processed directly, and some will be distributed to other modules to deal with.

If it is a query type of request, will give control to the query parser, the query parser first analysis is not a select type of query, if it is, then call the query cache module , let it check the query in the query Whether the cache already exists. If so, the data in the cache is returned directly to the connection thread module. The data is then output to the client through a thread connected to the client. If it is not a query type that can be used by the cache, or if the data in the cache is not changed, then query will be passed back to the query parser so that the query parser is processed and distributed to the relevant processing module via the query dispatcher.

　　Fifth Step : if the parser resolves a select statement that is not the cache, give control to optimizer, which is the query optimizer module, which, if it is DML or a DDL statement, is given to the table Change Management module . If it is some update statistics, detection, repair and collation of the class query will be given to the table maintenance module to process, copy the relevant query to the replication module to do the corresponding processing, the request status of the query to the State Collection report module. In fact, the table change management module differs from the corresponding processing request by the Insert processor, the delete processor, the update processor, the Create processor, and the Alter processor, which are the small modules responsible for different DML and DDL.

　　Sixth step : after each module receives query resolution and the distribution module sent the request, first through the access control module to check whether the connection user has access control target table and the Target field permissions, if any, will call the Table Management module request the corresponding table, and obtain the corresponding lock. The table management module will first see if the table already exists in table cache, if it is already open, lock-related processing, if not in the cache, you need to open the table file to get the lock, and then give the open table to the table Change Management module .

　　Seventh Step : When the table change management module "gets" The open table, it will determine the table's storage engine type and other related information based on the table's relevant meta information. According to the storage engine type of the table, submit the request to the Storage Engine interface Module , call the corresponding storage engine implementation module, and handle accordingly.

However, for the table Change management module, only the storage Engine interface module provides a series of "standard" interface, the underlying storage engine implementation of the module, for the table change management module is transparent. He only needs to invoke the corresponding interface and specify the table type, and the interface module will call the correct storage engine according to the table type to handle it accordingly.

　　Eighth Step : When a query or command processing completes (success or failure), control is returned to the connection thread module . If the processing succeeds, the processing result (either a result set or a successful or failed identity) is fed back to the client through the connection thread. If an error occurs during processing, the appropriate error message is also sent to the client, and then the thread module cleans up and continues to wait for the subsequent request, repeating the process mentioned above, or completing a client disconnect request.

　　nineth Step : If in the above process, the relevant module makes the data in the database changes, and MySQL called the Bin-log function, then the corresponding processing module will also invoke the log processing module The corresponding change statement is logged in the form of an update event to the binary log file specified by the relevant parameter.

In the process of content processing of each module above, each of the core arithmetic processing function parts will be highly dependent on the entire MySQL API module , such as memory management, file I/O, digital and string processing, etc.

The whole process is as follows:

MySQL system architecture