What is the three-tier logical architecture of MySQL?

MySQL three-tier logical architecture

MySQL’s storage engine architecture separates query processing from data storage/retrieval. The following is the logical architecture diagram of MySQL:

1. The first layer is responsible for connection management, authorization authentication, security, etc.

Each client connection corresponds to a thread on the server. A thread pool is maintained on the server to avoid creating and destroying a thread for each connection. When a client connects to a MySQL server, the server authenticates it. Authentication can be done through username and password, or through SSL certificate. After the login authentication is passed, the server will also verify whether the client has the authority to execute a certain query.

2. The second layer is responsible for parsing the query

Compiling SQL and optimizing it (such as adjusting the reading order of the table, selecting appropriate indexes, etc.). For SELECT statements, before parsing the query, the server will first check the query cache. If the corresponding query result can be found in it, the query result will be returned directly without the need for query parsing, optimization, etc. Stored procedures, triggers, views, etc. are all implemented in this layer.

3. The third layer is the storage engine

The storage engine is responsible for storing data in MySQL, extracting data, starting a transaction, etc. The storage engine communicates with the upper layer through APIs. These APIs shield the differences between different storage engines, making these differences transparent to the upper layer query process. The storage engine will not parse SQL.

The above is the detailed content of What is the three-tier logical architecture of MySQL?. For more information, please follow other related articles on the PHP Chinese website!