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

The following is mainly to bring you what is the overall architecture of MySQL, I hope these contents can bring you practical use, this is also the main purpose of my editing what is the overall architecture of MySQL this article. Okay, no more nonsense, let's go straight to the following.

Overall framework diagram

Let's first look at MySQL's architecture diagram 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 statements we usually write, as well as the optimization of SQL statements, are at the service level, which actually follows certain principles so that SQL statements can be executed according to our expected results.

Each section introduces the network connectivity layer

Mainly responsible for connection management, authorization authentication, security, etc. Each client connection corresponds to a thread on the Cloud Virtual Machine. A thread pool is maintained on the Cloud Virtual Machine to avoid creating and destroying threads for each connection. When a client connects to MySQL Cloud Virtual Machine, Cloud Virtual Machine authenticates it. Authentication can be by username and password or by SSL Certificates. After login authentication, the Cloud Virtual Machine also verifies whether the client has the operation permission to execute a query. This layer is not a MySQL specific technology.

service layer

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

query cache

Before a formal query, the Cloud Virtual Machine checks the query cache. If it can find the corresponding query, it does not need to perform query parsing, optimization, execution, etc., and directly returns the result set in the cache.

Parsers and Preprocessors

MySQL's parser constructs a parse 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 whether the order of keywords is correct.

The preprocessor is mainly to further verify, such as table name, field name is correct, etc.

query optimizer

Query optimizer transforms parse tree into query plan. Generally, a query can have many execution ways, and finally returns the same result. Optimizer is to find the optimal execution plan among them.

plan of implementation

After completing the parsing and optimization phases, MySQL calls the corresponding interfaces provided by the storage engine layer according to the corresponding execution plan to obtain the results.

Storage Engine Layer

MySQL is responsible for storing and retrieving MySQL data by providing a series of interfaces to mask differences between different engines.

Note: The storage engine is for tables, not libraries. This means that different tables in the same repository 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 a storage engine called MyISAM.

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

We can go to MySQL's relevant directory to see what it actually stores and find that it corresponds to three files.

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

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

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

Then the question arises where his data files and index files are stored. Let me ask you a question first. I'll talk about it in the next "file."

physical layer

Store data on hard disk.

overall process

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

The user first establishes a connection with the server through a client such as Navicat, which requires a username and password for authentication, or SSL Certificates for authentication.

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

If you have relevant permissions, when the user sends a query select statement, MySQL queries the cache first. If there is already a cache for this statement, it returns directly. If there is no cache, execute the following process. If update, add insert, delete, do not query cache, directly execute the following process.

MySQL parses SQL statements into trees and checks them, such as whether the keywords are correct, whether the keyword order is correct, whether the table name is correct, and whether the fields are correct. If authentication is unsuccessful, an error is returned directly. If the authentication is successful, the following process is directly followed.

MySQL performs query optimization on parse trees because multiple SQL queries may mean the same thing, but the time consumed may vary greatly. So MySQL finds the optimal statement execution for the table storage engine, that is, generates the corresponding execution plan.

Use the execution plan generated above to invoke the interfaces of the storage engine layer. That is, we usually use explain, which can be used to check whether to go index, consumption time and other information.

Different storage engines go to the appropriate physical storage location, find the appropriate data, encapsulate it, and return the results.

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

For the above on what is MySQL's overall architecture, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

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.