A brief discussion on the overall architecture of MySQL

Preface

It’s another new week, happy Monday everyone.

Due to a series of things like changing jobs and looking for a house, I have stopped updating for more than a month recently. Now that everything has been settled, I can code in peace.

Okay, without further ado, let’s start a new journey. I have been reading the book "MySQL Technology Insider - InnoDB Storage Engine" recently, so I just want to record it.

Overall Architecture Diagram

Let’s first take a look at the architecture diagram of MySQL to have an overall understanding of it. MySQL is mainly divided into four layers of architecture, namely network connection layer, service layer, storage engine layer and physical layer. The SQL statements we usually write and the optimization of SQL statements are all in the service layer. It actually follows certain principles so that the SQL statements can be executed according to our expected results.

Introduction to each part

Network connection layer

is mainly responsible for connection management, authorization authentication, security, etc. Each client connection corresponds to a thread on the server. Maintain a thread pool on the server to avoid creating and destroying threads for each connection. When a client connects to a MySQL server, the server authenticates it. It can be authenticated through username and password, or it can be authenticated through SSL certificate. After login authentication, the server will also verify whether the client has the permission to execute a certain query. This layer is not a technology unique to MySQL.

Service layer

This layer is the core of MySQL, including query cache, parser, parse tree, preprocessor, and query optimizer.

• Query Cache

Before the formal query, the server will check the query cache and if it can find the corresponding For queries, there is no need to perform query parsing, optimization, execution and other processes, and the result set in the cache is directly returned.

• Parser and preprocessor

MySQL's parser will construct a parse tree based on the query statement. It is mainly used It is used to verify whether the statement is correct based on grammatical rules, such as whether the SQL keywords are correct and whether the order of the keywords is correct.

The preprocessor is mainly for further verification, such as whether the table name and field name are correct, etc.

• Query optimizer

The query optimizer converts the parse tree into a query plan. In general, a query can be executed in many ways , ultimately returning the same result, the optimizer is to find the optimal execution plan

• Execution plan

After completing the analysis After the optimization phase, MySQL calls the corresponding interface provided by the storage engine layer according to the corresponding execution plan to obtain the results.

Storage engine layer

is responsible for the storage and retrieval of MySQL data. It provides a series of interfaces to shield the differences between different engines.

Note: The storage engine is for tables, not for libraries. In other words, different tables in the same library can have different storage engines.

There are two common storage engines, MyISAM and InnoDB. Let’s take a look at their differences.

First, we create a test1 table with the storage engine MyISAM.

create table test1( a INTEGER, b varchar(10) )ENGINE=MyISAM;

We can go to the relevant directory of MySQL to see its actual stored content and find that it corresponds to three files.

#Secondly, we create a test2 table with the storage engine InnoDB.

create table test2( a INTEGER, b varchar(10) )ENGINE=INNODB;

Let’s take a look at its actual stored content and find that it corresponds to this file.

Then the question arises, where are his data files and index files stored? I’ll leave you with a question here for now, and we’ll talk about it in the next chapter, “Documents.”

Physical layer

Stores data on the hard disk.

Overall process

We send a SQL statement, what is its overall process in MySQL?

• The user first establishes a connection with the server through a client such as Navicat. A username and password are required for authentication, or an SSL certificate can be used for authentication.

• After successful login, MySQL will determine whether the role has permissions on some tables based on the corresponding permissions.

• If you have relevant permissions, when the user sends a query select statement, MySQL first queries the cache. If there is already a cache for this statement, it will return directly. If not, execute the following process. If it is an update, a new insert, or a delete, the cache will not be queried and the following process will be executed directly.

• MySQL will parse the SQL statement into a tree and then verify it, such as whether the keywords are correct, whether the keyword order is correct, whether the table name is correct, whether the fields are correct, etc. If the authentication is not successful, an error will be returned directly. If the authentication is successful, proceed directly to the following process.

• MySQL performs query optimization on the parse tree, because multiple SQL statements may express the same meaning, but the time consumed may vary greatly. Therefore, MySQL finds the optimal statement execution for the storage engine of the table, that is, generates the corresponding execution plan.

• Use the execution plan generated above to call the storage engine layer interface. That is the explain we usually use, which can be used to check whether the index is indexed, the time consumed and other information.

• Different storage engines will go to the corresponding physical storage location, find the corresponding data, encapsulate and return the results.

• If the result set is obtained and it is a select statement, MySQL will put the results into the cache to avoid resource consumption caused by performing the same operation next time, and return it to Client result. At this point, the execution process of a SQL statement is over.

For more MySQL related technical articles, please visit the MySQL Tutorial column to learn!

The above is the detailed content of A brief discussion on the overall architecture of MySQL. For more information, please follow other related articles on the PHP Chinese website!