Summary of MySQL Architecture Knowledge Points

1. Databases and database instances

In the study of MySQL, there are two concepts that are very easy to confuse, namely database and database instance. In MySQL, databases and database instances are defined as follows:

Database: a collection of stored data;

Database instance: A collection of operational databases.

The above definition is very clear. The database is used to store data, and the database instance is used to operate the data. From the perspective of the operating system, the database instance is represented as a process, corresponding to multiple threads.

In a non-cluster database architecture, there is a one-to-one correspondence between a database and a database instance. In a database cluster, there may be multiple database instances operating one database, that is, a many-to-one relationship.

2. MySQL Scaffold

For MySQL, although it has gone through multiple version iterations (MySQL5.5, MySQL 5.6, MySQL 5.7, MySQL 8), each iteration is based on MySQL

The MySQL scaffold generally includes the following major module components:

(1) MySQL provides external interactive interfaces (Connectors)

(2) Management Service & Utilities

(3) Connection Pool

(4) SQL Interface

(5) Query Analyzer Component (Parser)

(6) Optimizer component

(7) Caches & Buffers

(8) Pluggable Storage Engines

(9) Physical File (File System)

(I) MySQL provides external interactive interfaces (Connectors)

The Connectors component is an interactive component provided by MySQL. Languages ​​such as Java, .net, and PHP can use this component to operate SQL statements and interact with SQL.

2. Management Service & Utilities

Provides integrated management of MySQL, such as backup, recovery, security management, etc.

(III) Connection Pool

Responsible for monitoring various requests from the client to the MySQL Server, receiving requests, and forwarding requests to the target module. Each client request that successfully connects to MySQL Server will be

Create or allocate a thread, which is responsible for the communication between the client and the MySQL Server, receiving commands sent by the client, and transmitting result information from the server.

(IV) SQL Interface

Receive user SQL commands, such as DML, DDL, and stored procedures, and return the final results to the user.

(V) Query Analyzer Component (Parser)

First, the legality of the SQL command syntax is analyzed, and an attempt is made to decompose the SQL command into a data structure. If the decomposition fails, it indicates that the SQL statement is unreasonable.

(VI) Optimizer

Optimize and analyze SQL commands according to standard procedures.

7. Caches & Buffers

Cache and buffering components

MySQL Storage Engine

1. What is MySQL storage engine?

MySQL is a relational database, and the storage of relational databases is in the form of tables. The creation of tables, storage, retrieval, and update of data are all performed by MySQL.

This is done by the storage engine, which is also the important role played by the MySQL storage engine in MySQL.

Readers who have studied SQL Server and Oracle may know that these two databases have only one storage engine, while MySQL has more types of storage engines, such as MyISAM storage

Engine, InnoDB storage engine and Memory storage engine.

The reason why MySQL has multiple storage engines is because of the open source nature of MySQL. MySQL storage engines can be roughly divided into official storage engines and third-party storage engines.

Caused by square storage. The open source nature of MySQL allows third parties to develop storage engines that suit their business needs based on the MySQL framework.

2. MySQL storage engine function

The MySQL storage engine plays an important role in MySQL. Its role is mainly summarized in the following two aspects:

Function 1: Manage table creation, data retrieval, index creation, etc.

Function 2: Meet the needs of custom storage engine development.

3. MySQL engine types

Different types of storage engines have different storage engine table mechanisms when storing tables. In terms of MySQL storage engine types, they can be divided into official storage engines and third-party storage engines.

Currently, there are also many MySQL storage engines, such as MyISAM storage engine, InnoDB storage engine, NDB storage engine, Archive storage engine, Federated storage engine, Memory

storage engine, Merge storage engine, Parter storage engine, Community storage engine, Custom storage engine, and other storage engines.

Among them, the more commonly used storage engines include InnoDB storage engine, MyISAM storage engine and Momery storage engine.

4. Comparison of several typical MySQL storage engines

9. Physical Files (File System)

The system that actually stores MySQL database files and some log files, such as Linux, Unix, Windows, etc.

Three one query flow chart

4. References

[01] The Definitive Guide to MySQL (US) by Paul Dubois Translated by Yang Tao, Yang Xiaoyun, Wang Qun, etc.

【02】MySQL Technical Insider: InnoDB Storage Engine by Jiang Chengyao

【03】SQL Learning Guide by Alan Beaulieu