Shulou(Shulou.com)06/02 Report--

This article is to share with you about how to build MySQL logic architecture, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

If you can build an architectural diagram of how the components of MySQL work together in your mind, it will contribute to a deeper understanding of MySQL servers.

Layer 1 services are not unique to MySQL. Most web-based client, server tools or services have similar architectures. Such as connection processing, authorization authentication, security and so on. The client here can be understood as PHP,python,java

The second layer architecture is MySQL most core service functions, including query parsing, analysis, optimization, caching and all built-in functions (date, time, number and encryption functions) all cross-storage engine functions are implemented in this layer: stored procedures, triggers, views, etc.

The third layer contains the storage engine. Storage engine is responsible for the storage and extraction of data in MySQL. Like every file system under GNU/Linux, each storage engine has its advantages and disadvantages. The server communicates with the storage engine through api. These interfaces shield the differences between different storage engines and make these differences transparent to the upper query process. The storage engine API contains dozens of underlying functions, such as "start a thing" or "extract a row of records based on the primary key", but the storage engine does not parse the SQL, and different storage engines do not communicate with each other, but simply respond to requests from the upper server.

A more detailed implementation flowchart

1 start

Start the MySQL service with the command net start mysql (windows) / service mysql start (linux)

Call the initial module; the initialization module is the initialization of the entire database when the database is started, such as initialization of various system environment variables, various caches, storage engine initialization settings and so on.

The core api:MySQL database core api mainly realizes the optimization function of the underlying operation of the database, including IO operation, formatting output, optimization of high-performance storage data result algorithm, string processing, the most important of which is memory management.

2 connection

When the user sends a SQL, the user's operation request will be monitored by the network interaction module and passed to the "connection management module".

Receive the request and forward it to the 'forward / thread connection module'

Call the 'user module' for permission detection (permissions to access the database)

After the detection, it will go to the connection / thread module to find the free cached connection thread from the thread connection pool and the client request docking, and if it fails, create a new connection request

Return to the connection thread

Network interaction module: provides an api interface that can receive and send data. When other modules need to interact, they can call through the api interface.

Connection management module, input / thread connection module, thread connection pool: the connection management module is responsible for listening to various requests of MySQL Server, according to different requests, and then forwarding them to the thread management module. Each client request is automatically assigned by the database to an independent thread to serve it alone, while the main work of the connection thread is responsible for the communication between MySQL Server and the client, and the thread management module is responsible for managing these generated threads.

3 treatment

After the user rights verification is successful and a new connection pool is obtained, it will go to the 'command dispatcher'. If the command type is select, it will access the 'query cache', and if it does not, it will go down.

If it is select, and after "query cache" is enabled, it will query the cache to see if there is a matching SQL. If it has, it will verify the user's permission to access the data. If it is passed, it will return an error message. If the data is not available, it will be executed.

The SQL operation in the process will be recorded in the log file.

After steps 8 and 9 do not meet the corresponding conditions, execute down to enter the 'command parser' and generate the parsing tree after lexical analysis and syntax analysis.

Go to the corresponding module processing (preprocessing phase) according to the operation, and select the module to be executed according to the SQL.

After the module receives the request, through the "access control module" to check whether the connected user has access to the target table and target fields (refers to the access to these data)

With permission, the 'table management module' first checks whether it exists in the table cache. If so, it directly corresponds to the table and acquires the lock, which is responsible for reopening the table file.

Obtain information such as the type of storage engine of the table according to the ENGINE data of the table

Call the corresponding storage engine processing through the interface

Returns the data content after the query

User module: the main function is to control the rights of users to log on to the connection and user authorization management.

Access control module: mainly used to monitor every operation of the user. The function of the access control module is to control the user's access to the data according to the different user authorization in the user module and the various constraints of its database. The user module and the access control module are combined to form the authority management function of the MySQL database.

Query optimizer: this module is mainly about the query request sent by the client. Based on the analysis of the previous algorithm, an optimal query strategy is calculated. After optimization, the query access speed will be improved. Finally, the query statement is returned according to the optimal strategy.

Table change management module: mainly responsible for the completion of DML and DDl query, such as, insert,update,delete,create table,alter table and other statement processing.

Table maintenance module: mainly used to detect the status of the table, analyze, optimize the structure of the table, and repair the table.

Replication module: the replication module is divided into Master module and Slave module. The Master module is mainly responsible for reading the binary log on the Master side and the I / O thread interaction on the Slave side in the replication environment.

Status module: when the client requests the system state, the system state module is mainly responsible for returning the data of various states to the user. Some of the most commonly used commands for querying status, such as show status,show variable, are returned through this module.

Table management module: mainly to maintain the table files generated by the system. For example, the MyISAM storage engine generates frm,myd,myi files, maintains these files, caches the information about the table structure, and the module also manages table-level locks.

Storage engine interface module: MySQL implements the plug-in management of the underlying storage engine of its database and highly abstracts all kinds of data processing.

4 results

After the command is executed, the result set is returned to the 'understanding / threading module' (the corresponding identity can also be returned, success or failure)

The 'understand forward / threading module' performs subsequent cleanup work and continues to wait for a request or disconnect from the client

The above is how to build the logical structure of MySQL, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.