MySQL architecture and Storage Engine overview

I. Defining databases and instances

Database:

A collection of physical operating system files or other forms of file types. The database file can be a file ending with frm, MYD, IBD.

Conceptually, a database is a collection of files that are organized according to a data model and stored in level two memory;

Instance:

The MySQL database consists of a background process and a shared memory area. Shared memory can be shared by the running background thread. It is important to note that the DB instance is really used to manipulate the database files.

Conceptually, a database instance is a program, a layer of data management software that is located between the user and the operating system, and any operation of the database data, including database definition, data query, etc. is performed under the instance, and the application can only interact with the database through the database instance.

Ps:mysql is a single-process multithreaded architecture database. This means that the MySQL database instance is represented as a process in the system.

Second, MySQL architecture

As you can see, MySQL consists of the following parts:

1. Connection Pool Build

2. Management Services and tool formation

3. SQL interface components (SQL Interface)

4. Query Analyzer (Parser)

5. Optimizer components (Optimizer)

6. Buffer components (cache & buffer)

7. Plug-in storage engine (pluggable Storage Engines)

8. Physical files (file Sysgtem & Logs)

Third, MySQL storage engine

One of the features of MySQL compared to other databases is the unique plug-in architecture, each of which has its own characteristics and the ability to build different storage engine tables based on specific business or application scenarios.

Below is a brief introduction to the following MySQL several common storage engines:

1. InnoDB Storage Engine:

The InnoDB storage engine supports transactions, and the design goals are primarily for OLTP applications, characterized by row lock design and foreign key support. Ps:mysql5.6+ starts to support full-text indexing.

2. MyISAM Storage Engine:

The MyISAM storage engine does not support transactional, table lock design, and supports full-text indexing, primarily for OLAP database applications.

3. NDB Storage Engine:

NDB storage Engine is a cluster storage engine, characterized by putting all the data in memory (mysql5.1+ can start to put non-index data on disk) so the primary key to find the data fast, by adding data storage node, can improve the performance of the database linearly, is a high availability, high performance cluster system.

4. Memory Storage Engine:

The memory storage engine places all the data in the table in RAM, and if the database restarts or crashes, the data disappears. Memory all uses hash index by default.

5. Archive Storage Engine:

Only insert and select operations are supported, and data row (row) compression is stored using the zlib algorithm. Ideal for storing archived data, such as log information.

6, Federated

7. Maria

......

MySQL architecture and Storage Engine overview