Shulou(Shulou.com)05/31 Report--

This article mainly introduces the overall structure of MySql sample analysis, the text is very detailed, has a certain reference value, interested friends must read!

MySql overall structure

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

In the figure above, Connection pool is the connection layer, Management Services & Utilities …Caches & Buffers is the SQL layer, 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 a variety of clients, application services, mainly refers to the interaction between different languages and SQL.

1. connection layer

Applications connect to MySQL through interfaces (such as ODBC, JDBC), and the first connection is processed at the connection layer. The connection layer consists of three parts: communication protocol, thread processing and user name and password authentication.

The communication protocol is responsible for checking whether the client version is compatible with the MySQL server.

- Thread processing means that each connection request will allocate a corresponding thread, which is equivalent to one SQL thread, one thread corresponds to one logical CPU, and switches between multiple logical CPUs.

Password authentication is used to verify that the user created the account, password, and host authorization to connect to MySQL servers.

Connection Pool belongs to the connection layer. Since it takes a lot of time to establish a connection each time, the role of the connection pool is to cache the user connection, username, password, permission verification, thread processing and other requirements that need to be cached. Next time, you can directly use the established connection to improve server performance.

2. SQL layer

SQL layer is the core of MySQL, MySQL 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 verify whether a user has permission to access a certain database, a certain table, or a certain row of data in the table.

Query Cache operates through Query Cache. If the data is in Query Cache, the result is returned directly to the client without query parsing, optimization and execution.

The query parser parses SQL statements to determine whether the syntax is correct.

The preprocessor processes semantics that the parser cannot parse.

Query optimizer rewrites SQL and optimizes it accordingly, and generates the optimal execution plan, then you 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 and are described in detail in the following table.

Management Services & Utilities MySQL system administration and control tools, including backup recovery, MySQL replication, clustering, etc. SQL Interface (SQL Interface) is used to receive SQL commands from users and return the results that users need to query. SELECT FROM, for example, calls SQL Interface. The parser validates and parses SQL commands as they are passed to the parser, so that the MySQL optimizer can recognize data structures or return SQL statement errors. Query Optimizer SQL statements use the Query Optimizer to optimize queries before they are queried, verifying that the user has permission to query and that the latest data is available in the cache. It queries using a "pick-project-join" strategy. For example, SELECT id, name FROM student WHERE gender = "female"; statement, SELECT query first according to the WHERE statement to select, rather than query all the tables after gender filtering. SELECT queries project attributes based on id and name first, rather than filtering them after extracting all attributes, and concatenate these two query criteria to generate the final query result. Caches & Buffers If the query cache has hit query results, the query statement can directly fetch data from the query cache. This caching mechanism consists of a series of small caches, such as table cache, record cache, key cache, permission cache, etc.

3. Storage Engine Layer

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

Because data is stored in tables in relational databases, storage engines can also be called table types (i.e., the types that store and manipulate the tables).

4. file system layer

File system layer is mainly used to store database data on file system of operating system and complete interaction with storage engine.

File system layer is mainly to store the data of database on the file system of operating system, and complete the interaction with storage engine.

The above is "MySql overall structure of the sample analysis" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to 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.