MySQL SQL Layer Module Introduction

The following is the logical architecture of MySQL, SQL layer is mainly responsible for the following functions: permission judgment, SQL parsing, Execution plan optimization, query cache processing operations, these operations are in the database system before processing the underlying data work;

The Storage engine layer is primarily responsible for the implementation of the underlying data access, which is composed of a variety of storage engines.

The SQL Layer contains multiple sub-modules, and I'll take a brief look at the following:

The structure diagram is as follows:

1. Initialize the module
As implies, the initialization module is the beginning of the MySQL Server, the whole system to do a variety of initialization operations, such as the initialization of various buffer,cache structures and memory space applications, the initialization of various system variables, various storage engine initialization settings, and so on.

2. Core API
The core API module is designed to provide some optimization implementations that require very efficient underlying operations, including implementation of various underlying data structures, implementation of special algorithms, string processing, digital processing, small file I/O, and format output
And the most important part of memory management. All the source code of the core API module is concentrated under the Mysys and Strings folders, and interested readers can study it.

3. Network Interaction Module
The underlying network interaction module abstracts the interface APIs used by the underlying network interaction, enabling the receiving and sending of the underlying network data to facilitate the invocation of other modules, as well as the maintenance of this part. All source code is under the Vio folder.

4. Client & Server Interaction Protocol Module
Any software system of C/s structure will certainly have its own unique information interaction protocol, MySQL is no exception. MySQL's Client & Server Interaction Protocol Module Section implements all of the protocols that clients have with MySQL during the interaction process. Of course, these protocols are built on existing OS and network protocols, such as TCP/IP and 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. He decided to "open the door" to the visitor, just like the door guard at MySQL.

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 threading, and thread management
The connection Management module is responsible for listening to various requests to MySQL Server, receiving connection requests, and forwarding all connection requests to the thread management module. Each client request to a MySQL Server on a connection is assigned (or created) by a connection thread for its individual service. The main task of connection thread is to be responsible for the communication between MySQL server and client, accept the command request from client, pass the result information of server side and so on. The thread management module is responsible for managing and maintaining these connection threads. This includes the creation of threads, the cache of threads, and so on.

8. Query Parsing and forwarding module
In MySQL we are accustomed to the command that all client side sends to server side is called query, inside MySQLServer, the connection thread receives a query from the client, it passes the query directly to the specially responsible
Classify the various query and then forward to each corresponding processing module, this module is the query parsing and forwarding module. The main task is to make the query statement semantic and grammatical analysis, and 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, its memory consumption is also very large.

10. Query Optimizer Module
Query optimizer, as the name implies, is to optimize the client request query, according to the client request query statement, and the database of some statistical information, based on 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
Table Change Management module is mainly responsible for the completion of some DML and DDL query, such as: update,delte,insert,create table,alter table and other statements processing.

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 State 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 various show status commands commonly used by DBAs, the show variables command, and so on, and the resulting results are returned by this module.

14. Table Manager
This module is easily confused with the table change and table maintenance module, but its function is completely different from the change and maintenance module. As you know, every MySQL table has a table definition file, which is the *.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, it maintains table-level lock management.

15. Log Recording Module
The logging module is responsible for logging the entire system-level logic layer, including error log,binarylog,slow query log.

16. Copy Module
Replication module can be divided into Master module and slave module two parts, Master module is mainly responsible for reading the binary log of the master side in the replication environment, and the interaction with the I/O thread of the slave side.
The Slave module has a little more to do than the Master module, which is mainly embodied on two threads in the system. One is responsible for requesting and accepting binary logs from Master and writing the I/O thread in the local relay log. The other one is responsible for reading the related log events from relay log and parsing them into a command that can be executed correctly on the slave side and get exactly the same results as the master, and then handed to the SQL thread executed by slave.

17. Storage Engine Interface Module
The storage Engine interface module can be said to be 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 an abstract class, but it is precisely because it successfully abstracts the various data processing, it is the feature of today's MySQL pluggable storage engine.

Through an example, we explain the process of working together with each module:

We do this by starting MySQL, client connections, requesting query, getting the results returned, and finally exiting, so that the entire process is analyzed:

When we execute the start 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 accept the client request.

When the connection management module hears the client's connection request (with the help of the Network interaction module), the connection management module will connect with the protocol "greeting" through the protocols defined by the client & Server Interaction Protocol module.
The request is forwarded to the thread Management module to request a connection thread. The thread management module will now hand over the control to the connection thread module, telling the connection thread module: Now I have a connection request coming over, need to establish a connection, you quickly deal with. Connection thread module after receiving the connection request, 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 immediately take out a connection request after receiving a connection thread attached and a client connection, but rather first by invoking the user module for authorization checks, only after the client request passed the authorization check, he will be the client request and responsible for the requested connection line thread attached.

In MySQL, there are two types of client requests: A query that calls parser, which is the query parsing and forwarding module parsing to execute, and a command that does not need to call parser to execute the request directly. 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 turn on fullquery Logging functionality.

When the client request and the connection thread "exchange the password (interworking protocol)", the connection thread begins processing various commands (or query) sent by the client request, accepting the request. It turns the received query statement
To query parsing and forwarding module, query parser first to query the basic semantic and syntactic parsing, and then depending on the type of command, some will be processed directly, some will be distributed to other modules to deal with.

If it is a query-type request, control is given 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 that query
Whether the query cache already exists. 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 connected to the client. 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.

If the parser resolves the result to be a SELECT statement that is not the cache, the control is given to optimizer, the query optimizer module, and, in the case of DML or DDL statements, to the table change management module, if
Some of the update statistics, detection, 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 the small modules responsible for different DML and DDL. After each module receives the query resolution and the distribution module to distribute the request, first through the 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, if it is already open, locks-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. 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 relevant meta information for that 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 management 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.

When a query or command processing completes (success or failure), control is returned to the connection thread module. If the processing succeeds, the result is processed (possibly a result set, or it may be a success or
The 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.

If, in the process above, the relevant module changes the data in the database and MySQL opens the Binlog 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.

MySQL SQL Layer Module Introduction