This is the 29th day of my participation in the August Wenwen Challenge.More challenges in August

| author: jiangxia

| CSDN：blog.csdn.net/qq_41153943

| the nuggets: juejin. Cn/user / 651387…

| zhihu: www.zhihu.com/people/1024…

| GitHub:github.com/JiangXia-10…

This article is about 2,307 words and takes nine minutes to read

One, foreword

As a back-end developer, you have to deal with databases every day. Whether you are Java, PHP, or Python, you may not be able to speak other languages, but databases are a must. MySQL is one of the mainstream databases used by many companies. Therefore, understanding the architecture of MySQL can help us master MySQL more skillfully and greatly improve the usual development efficiency.

Second, the body

The MySQL architecture diagram is as follows:

The structural components of MySQL can be divided into four layers, which are:

1) Connection layer

The connection layer is the topmost set of client and connection services, containing local SOCK communications and most TCP/ IP-like communications implemented based on client/server tools. Mainly complete some similar link processing, authorization and authentication, and related security schemes. In this layer, the concept of thread pool is introduced to provide threads for clients that are securely accessed through authentication. SSL – based secure links can also be implemented at this layer. The server also validates that it has operational permissions for each client that is securely connected.

2) Service layer

The second layer architecture mainly completes most of the core service functions, such as SQL interface, and completes the cache query, SQL analysis and optimization, and the execution of some built-in functions. All cross-storage engine functionality is also implemented in the services layer. In the service layer, the server will parse the query and create the corresponding internal parse tree, and perform the corresponding optimization, such as determining the query order of the table, whether to use the index query, and finally regenerate the corresponding execution operations. If it is a query statement, the server will also query the internal cache. If the cache space is sufficient, this can greatly improve the system performance in an environment where a large number of read operations are handled.

3) Engine layer

Storage engine layer, storage engine is really responsible for the storage and extraction of data in MySQL, server through API and storage engine communication. Different storage engines have different functions. The MySQL database provides a variety of storage engines. Users can select different storage engines for data tables according to different requirements, or write their own storage engines based on their own requirements. Even different tables in a library using different storage engines are allowed. Therefore, we can select an appropriate storage engine based on actual requirements.

4) Storage layer

The data storage layer, which stores data on file systems and interacts with the database storage engine.

As you can see from the architecture diagram above, the entire MySQL architecture consists of the following parts:

  • Connectors: Connectors

  • Connection Pool: indicates a Connection Pool component

  • Management Services & Utilities: Components that manage Services and tools

  • SQL Interface: SQL Interface component

  • Parser: Query analysis component

  • Optimizer: Optimizer component

  • Caches & Buffers: Caches & Buffers

  • Pluggable Storage Engines: Storage engine

  • File System: indicates the File System

Connectors: Code from different languages to interact with mysql (SQL interaction), e.g. JAVA JDBC, Python, PHP, etc.

Connection Pool: the database Connection Pool is responsible for managing, buffering user connections, thread processing, and other requirements that need to be cached.

Management Services & Utilities: System Management and control tools, such as backup and restoration, Mysql replication, and clustering.

SQL Interface: The SQL Interface receives SQL commands from the user and returns the query results.

Parser: Queries the Parser. SQL commands passed to the Parser are validated and parsed (permissions, syntax structures).

Optimizer: A query Optimizer component that is used by SQL statements to optimize the query before it is queried.

select id,name from user where age = 20;
Copy the code

The above query statements are in the following order:

Select a table based on where statement instead of querying all the tables and then filtering age. 2. Project attributes based on ID and name instead of filtering attributes after fetching all the tables

3. Join the two query conditions to generate the final query result

Caches & Buffers: Cache pool assembly. If the query cache has a matching query result, the query statement can directly fetch data from the query cache

Pluggable Storage Engine: Pluggable Storage Engine component. MySQL is a plug-in storage engine. Storage engines are basically a way of managing operational data (storing data, updating data, querying data, etc.). Because the storage of data in a relational database is in the form of tables, a storage engine can also be called a table type (that is, the type that stores and operates on this table)

File System: the File System includes log files, data files, configuration files, PID files, and socket files.

Compared to other databases, MySQL is special in that its architecture can be used in different scenarios and play a better role. This is because MySQL’s storage engine is a plug-in storage engine architecture that separates query processing from other system tasks and data storage extraction. In this architecture, you can select an appropriate storage engine based on service requirements. For the detailed introduction of MySQL storage engine, I previously in today we say MySQL storage engine this article has written in detail, there is a need for reference.

Third, the end

The above is about the introduction of the MySQL structural system. Only by understanding the MySQL structural system, can we understand the operating principle of MySQL, which is very helpful for daily development and SQL optimization. MySQL tuning will be covered in more detail later. If you have any questions, please leave a comment below or send a private message.

History of hits

Interview questions series: database must ask knowledge points big inventory

Here’s what you need to know about databases

Today we are going to talk about MySQL’s storage engine

Don’t you know what a database index is

Mom no longer has to worry about me forgetting the database account password