Detailed explanation of the overall structure of MySql

The overall structure of MySql

MySQL consists of connection pool, SQL interface, parser, optimizer, cache, storage Engine, etc., can be divided into three layers, namely the MySQL Server layer, the storage engine layer and the file system layer. The MySQL Server layer includes the connection layer and the SQL layer. The following is the infrastructure diagram of MySQL in the official documentation:

#In the above figure, Connection pool is the connection layer, Management Services & Utilities...Caches & Buffers is the SQL layer, and Pluggable Storage Engines is the storage engine layer, and File system, Files & Logs are the file system layer.

Connectors do not belong to any of the above layers. Connectors can be understood as various clients and application services, mainly referring to the interaction between different languages ​​and SQL.

Related free learning recommendations: mysql video tutorial

1. Connection layer

The application connects to MySQL through interfaces (such as ODBC, JDBC), and the first connection processing is the connection layer. The connection layer includes three parts: communication protocol, thread processing, and user name and password authentication.

• The communication protocol is responsible for detecting whether the client version is compatible with the MySQL server.
  -Thread processing means that each connection request will be assigned a corresponding thread, which is equivalent to one SQL corresponding to one thread, one thread corresponding to one logical CPU, and switching between multiple logical CPUs.
  - Password authentication is used to verify whether the account, password and host authorization created by the user can connect to the MySQL server.

Connection Pool belongs to the connection layer. Since it takes a lot of time to establish a connection every time, the role of the connection pool is to cache user connections, user names, passwords, permission verification, thread processing and other needs that need to be cached. Next time, you can directly use the established connection. Improve server performance.

2. SQL layer

The SQL layer is the core of MySQL, and MySQL’s core services are implemented in this layer. It mainly includes permission judgment, query cache, parser, preprocessing, query optimizer, cache and execution plan.

• Permission judgment can check whether the user has permission to access a certain library, a certain table, or a certain row of data in the table.
• The query cache operates through the Query Cache. If the data is in the Query Cache, the results are returned directly to the client without the need for query parsing, optimization, and execution.
• The query parser parses the SQL statement to determine whether the syntax is correct.
• The preprocessor processes semantics that the parser cannot parse.
• The query optimizer rewrites and optimizes the SQL accordingly, and generates the optimal execution plan. Then it can call the API interface of the program and access the data through the storage engine layer.

Management Services & Utilities, SQL Interface, Parser, Optimizer, and Caches & Buffers belong to the SQL layer, as detailed in the following table.

##Management Services & UtilitiesSQL Interface (SQL interface) Parser (query parser) Optimizer (Query Optimizer) SELECT id, name FROM student WHERE gender = "female";Caches & Buffers (query cache)

3. Storage engine layer

Pluggable Storage Engines belong to the storage engine layer. The storage engine layer is the core point that distinguishes the MySQL database from other databases, and it is also the most distinctive place of MySQL. Mainly responsible for the storage and retrieval of data in MySQL.

Because in a relational database, data is stored in the form of a table, the storage engine can also be called a table type (that is, the type of storage and operation of this table).

4. File system layer

The file system layer mainly stores database data on the operating system's file system and completes interaction with the storage engine.

The file system layer mainly stores the database data on the file system of the operating system and completes the interaction with the storage engine.

More related free learning recommendations: mysql tutorial(Video)

Name	Description
## System management and control tools for #MySQL, including backup and recovery, MySQL replication, clustering, and more.
is used to receive the user's SQL command and return the results that the user needs to query. For example, SELECT FROM calls the SQL Interface.
When the SQL command is passed to the parser, it will be verified and parsed by the parser so that the MySQL optimizer can recognize the data structure Or return an error for the SQL statement.
The SQL statement will use the query optimizer to optimize the query before querying, and at the same time verify whether the user has permission to query, and the cache Is the latest data available? It uses a "select-project-join" strategy for querying. For example,	In the statement, the SELECT query first selects based on the WHERE statement, instead of querying the entire table and then filtering by gender. The SELECT query first performs attribute projection based on id and name, instead of extracting all attributes and then filtering, and connects these two query conditions to generate the final query result.
If the query cache has a hit query result, the query statement can directly fetch data from the query cache. This caching mechanism is composed of a series of small caches, such as table cache, record cache, key cache, permission cache, etc.

The above is the detailed content of Detailed explanation of the overall structure of MySql. For more information, please follow other related articles on the PHP Chinese website!