MySQL various logic modules work together

After learning about MySQL's various modules (click to view MySQL's various logic modules), let's look at how the MySQL modules work together with each other. Next, we analyze this process by starting MySQL, client connection, request query, get return results, and finally exit.

1. When we execute the MySQL command, the MySQL 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. At the same time, each storage engine is also started, and the respective initialization work is done. When the entire system is initialized, it is taken over by the connection management module. The connection Management module initiates a listener that handles client connection requests, including TCP/IP network snooping, and a UNIX socket. At this point, MySQL Server completes the basic boot and is ready to receive client requests.

2, when the connection management module to hear the client's connection request (with the help of the network interaction module related functions), the two sides through the client & Server Interaction Protocol module defined by the protocol "greeting" A few words, the connection management module will forward the connection request to the thread management module, to request a connection thread.

3, the thread management module will immediately give control to the connection thread module, tell the connection thread module: Now I have a connection request over here, need to establish a connection, you hurry to deal with. Connection thread module after receiving a connection request, it is preferred to check the current connection thread pool for the free connection thread of the cache, and if so, take out one and the client request connection, and if there is no idle connection thread, establish a new connection thread to connect with the client request. Of course, the connection thread module does not immediately take out a connection thread and a client connection 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.

4, in MySQL, the client 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, one is command, do not need to call parser can directly execute the request. If the function of full Query logging is turned on in our initialization configuration, then the query parsing and forwarding module invokes the logging module to count the request into the log, whether it is a Query-type request or a command-type request, it will be logged into the log. Therefore, for performance reasons, it is generally very rare to open the full Query logging feature.

5, when the client request and connection thread "Exchange code (Interoperability Protocol)" After the top, the connection thread began to process the client request sent over the various commands (or query), to accept the relevant request. It passes the query statement received to the query parsing and forwarding module, the query parser first to query the basic semantics and syntax parsing, and then depending on the type of command, some will be processed directly, some will be distributed to other modules to deal with.

6, if it is a query type of request, will give control to the query parser. The query parser first parses to see if it is a select type of query, and if it does, call the querying cache module and let it check whether the query already exists in query cache. If so, the data in the cache is returned directly to the connection thread module, which then transmits data to the client through the thread of the client's connection. If it is not a query type that can be used by the cache, or the data in the cache does not have the query, then query will be passed back to the query parser, with the query parser handled accordingly, and distributed to the relevant processing module by the query dispatcher.

7, if the parser resolves the result is a SELECT statement that violates the cache, then gives control to optimizer, which is the query optimizer module, if it is a DML or DDL statement, it will be given to the table change management module, if it is some update statistics, detection, The repair and collation of the 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 is forwarded 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 responsible for different DML and DDL.

8, after the various modules received query resolution and distribution of the request, the first access control module to check whether the connection user has access to the 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 first checks to see if the table already exists in table cache, and 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 hand the open table to the table change management module.

9. When the table change management module "gets" The open table, the table's storage engine type and other related information are judged based on the related meta information of the table. 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 module, only the storage Engine interface module provides a series of "standard" interface, the underlying storage engine implementation of the module implementation, for the table change management module is transparent. He only needs to invoke the corresponding interface and indicate the table type, and the interface module calls the correct storage engine according to the table type to handle it accordingly.

10, when a query or command processing is completed (success or failure), the controller will be 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 accent 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.

If, in the process above, the corresponding module changes the data in the database and MySQL opens the Bin-log function, the corresponding processing module also invokes the log processing module to record the corresponding change statement as an update event in the binary log file specified by the relevant parameter.

During the processing of each of the above modules, the core arithmetic processing functions of each module are highly dependent on the core API modules of MySQL, such as memory management, file I/O, digital and string processing, etc.

The following diagram shows the process flow for each module.

　　　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　

This article references: "MySQL performance tuning and architecture design"

MySQL various logic modules work together