Shulou(Shulou.com)06/02 Report--

What this article shares with you is about how to analyze the overall structure of MySQL. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Overall architecture diagram

Let's first take a look at the architecture diagram of MySQL to get an overall understanding of it. MySQL is mainly divided into four layers: network connection layer, service layer, storage engine layer and physical layer. The SQL statement we usually write and the optimization of the SQL statement are all in the service layer. In fact, it follows certain principles to make the SQL statement execute as we expect.

Network connection layer

Mainly responsible for connection management, authorization authentication, security and so on. Each client connection corresponds to a thread on the server. A thread pool is maintained on the server to avoid creating and destroying threads for each connection. When the client connects to the MySQL server, the server authenticates it. It can be authenticated by user name and password, or by SSL certificate. After login authentication, the server also verifies that the client has permission to execute a query. This layer is not unique to MySQL.

Service layer

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

Query cache

Before the formal query, the server will check the query cache. If the corresponding query can be found, it does not need to go through the process of query parsing, optimization, execution and so on, and directly return the result set in the cache.

Parsers and preprocessors

The parser of MySQL will construct a parsing tree based on the query statement, which is mainly used to verify whether the statement is correct according to syntax rules, such as whether the keywords of SQL are correct and the order of keywords is correct.

The preprocessor is mainly for further verification, such as table name, field name and so on.

Query optimizer

The query optimizer converts the parsing tree into a query plan. In general, a query can be executed in many ways and eventually return the same result. The optimizer is to find the best execution plan among them.

Carry out the plan

After completing the parsing and optimization phase, MySQL invokes the corresponding interface provided by the storage engine layer according to the corresponding execution plan to get the results.

Storage engine layer

Responsible for the storage and extraction of MySQL data, by providing a series of interfaces to shield the differences between different engines.

Note: the storage engine is for tables, not libraries. This means that 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 the difference between them.

First, let's create a test1 table with the storage engine MyISAM.

Create table test1 (

An INTEGER

B varchar (10)

) ENGINE=MyISAM

We can go to the relevant directory of MySQL to take a look at what he actually stores and find that he corresponds to three files.

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

Create table test2 (

An INTEGER

B varchar (10)

) ENGINE=INNODB

Let's take a look at what he actually stored and find that he corresponds to this file.

Then the question is where his data files and index files are stored. Leave a question here, and we'll talk about the next "document".

Physics

Store the data on the hard drive.

Overall process

We send a SQL statement, what exactly is the overall flow in MySQL?

Users first establish a connection with the server through clients such as Navicat, where a user name and password are required for authentication, or a SSL certificate is used for authentication.

After the login is successful, MySQL will determine whether the role has some table permissions according to the corresponding permissions.

If you have the 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 returns directly, and if not, the following process is executed. If you update update, add insert, delete delete, do not query the cache, and directly execute the following process.

MySQL parses the SQL statement into a tree and verifies it, such as whether the keywords are correct, the keyword order is correct, the table name is correct, the fields are correct, and so on. If the authentication is not successful, an error is returned directly. If the authentication is successful, go straight to the following process.

MySQL optimizes the query of the parse tree because multiple SQL may mean the same thing, but the time consumed may vary widely. So MySQL finds the optimal statement execution for the storage engine for the table, that is, generates the corresponding execution plan.

Use the execution plan generated above to invoke the interface of the storage engine layer. That is, we usually use explain, which can be used to check whether to walk the index, 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 you get the result set and it is a select statement, MySQL will put the result in the cache to avoid resource consumption caused by the same operation next time, and return the result to the client at the same time. At this point, the execution of a SQL statement is over.

The above is how to analyze the overall structure of MySQL, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow 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.