• Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

The overall architecture

The overall logical architecture of MySQL is shown in the following figure:

Detailed introduction

1. The connection layer

With the client and connection services, including local and most of the Sock communication based on client/server tools to implement similar to TCP/IP communication, mainly done some similar to join processing, authorization, and relevant safety plan, introduced the concept of a thread pool on the floor, for certified safety access client threads, SSL – based secure links can also be implemented at this layer. The server also validates that it has operational permissions for each client that is securely connected.

Note: Allocate database connection thread pool, control database connection and close resources.

2. Business logic processing layer

Mainly complete most of the core service functions, such as SQL interface, and complete the cache query, SQL analysis and optimization and part of the built-in function operation. All cross-operation engine functions are also implemented in this layer, such as procedures, functions, and so on. In this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization, such as determining the order of the query table, whether to use the index, and finally generate the corresponding execution operation. If the SELECT statement is used, the server also queries the internal cache. If the cache space is large enough, the system performance can be improved in an environment where a large number of read operations are performed.

Note: The SQL operation interface is provided, the SQL script is parsed according to certain rules, and the execution sequence is optimized through the SQL optimizer. The query statements are also entered into the cache to improve the system performance.

3. Data storage engine layer

The storage engine is really responsible for the storage and extraction of data in MySQL. The server communicates with the storage engine through API. Different storage engines have different functions, so we can choose according to our actual needs.

Note: All storage engines are pluggable. Each storage engine provides different services, so we need to choose the right storage engine according to the specific business needs. There are only two common storage engines, MyISAM and InnoDB.

4. Data storage layer

It stores data on a file system running on a raw device and interacts with the storage engine.

Note: Stores data to disks and collaborates with the storage engine to read and write data.

conclusion

Database logical structure is divided into four layers, namely connection layer (thread connection pool), business logic processing layer (SQL parsing read), data storage engine layer (storage engine), data storage layer (data storage).

Compared with other databases, MySQL is a little different. Its architecture can be used in a variety of different scenarios and play a good role, mainly reflected in the architecture of the storage engine. The plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction. In this architecture, you can select an appropriate storage engine based on service requirements.