preface

Database is a common tool in our work, most of the company’s important business information will be stored in the database.

Therefore, for developers or operations personnel, mastering the basic principles of database is a necessary skill. No matter what database you are working with, it is essential that you understand the architecture of the database. “Only by knowing one thing and knowing the other can you win every battle.”

The following series will take you through the architecture of MySQL.

First of all, we need to clarify that the MySQL architecture is mainly composed of databases and database instances. Before we understand the essence of MySQL, we need to understand the following concepts.

  1. MySQL is a single process multithreaded database, and the database instance is reflected in the system in the way of a process.
  2. The MySQL file formats include ibD, MYI, MYD, and FRM.

1. Schematic diagram of MySQL architecture

As you can see from the figure above, the entire MySQL framework consists of two parts,

1, the upper layer is MySQLD, also known as the “LAYER of SQL”, which contains query parsing, analysis, optimization, cache and system custom functions (such as time, trigonometry, encryption function, etc.), the main functions can be realized are: view, trigger, stored procedure, etc.

2. The lower layer is a variety of storage engines that interface with the upper layer, also known as the “storage engine layer.” Storage engine contains many underlying storage engines such as InnoDB, MyISAM, Memory… ., they are used for methods like “query a row by primary key” and so on. The storage engines do not parse the SQL, and the storage engines do not communicate directly with each other. They simply respond to requests from the upper-layer server.

2. Schematic diagram of query working principle

1. The client sends a query statement to the server.

When the server receives the query statement, it does two things:

  • First, check whether the query statement matches exactly.
  • Second, it then checks whether the statement has permissions.

3. When the two conditions are met, the index cache will be started. If the index cache exists, the result will be returned directly.

4. Preprocessing stage, SQL statement parsing, new parse tree generation, and then to the corresponding module processing, optimization according to the optimizer, and then generate the corresponding execution plan.

5. The execution plan of the optimizer is to call the API in the storage engine to execute the schedule. When the data changes, the log function is turned on, and it will be recorded in the corresponding binary log file.

6. Set up the cache. After the request completes, return the result to the connected/thread module, then go to the client and decide whether to wait for the request or disconnect from the client.

conclusion

The above is the software architecture and query working principle of MySQL, which is very important for developers and operation and maintenance personnel. Knowing them will remove many obstacles from your database development path.