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

This article mainly talks about "what the MySQL architecture is like". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what the MySQL architecture is like.

One: database and database instance

In the study of MySQL, there are two concepts that are easy to be confused, namely, database and database instance. In MySQL, the database and database instance are defined as follows:

Databases: collections of stored data

Database instance: a collection of manipulated databases.

As defined above, the database is used to store data, and the database instance is used to manipulate the data. from the point of view of the operating system, the database instance behaves as a process corresponding to multiple threads.

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

Second: MySQL base frame

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

The MySQL base roughly includes the following module components:

(1) the interactive interface provided by MySQL (Connectors)

(2) manage service components and tool components (Management Service & Utilities)

(3) connection Pool component (Connection Pool)

(4) SQL Interface component (SQL Interface)

(5) query Analyzer component (Parser)

(6) Optimizer components (Optimizer)

(7) caching main components (Caches & Buffers)

(8) plug-in storage engine (Pluggable Storage Engines)

(9) physical files (File System)

(1) the interactive interface provided by MySQL (Connectors)

Connectors component is the interactive component provided by MySQL. Languages such as java,.net,php can operate SQL statements through this component to realize the interaction with SQL.

(II) manage service components and tool components (Management Service & Utilities)

Provide integrated management of MySQL, such as backup (Backup), recovery (Recovery), security management (Security), etc.

(3) connection Pool component (Connection Pool)

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

Create or assign a thread, which is responsible for the communication between the client and the MySQL server, receiving the commands sent by the client, transmitting the result information of the server, and so on.

(IV) SQL Interface component (SQL Interface)

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

Query Analyzer component (Parser)

Firstly, the validity of SQL command syntax is analyzed, and the SQL command is decomposed into data structures. If the decomposition fails, it indicates that the SQL statement is unreasonable.

(VI) Optimizer components (Optimizer)

Optimize and analyze the SQL command according to the standard process.

(7) caching main components (Caches & Buffers)

Caching and buffering components

(8) MySQL storage engine

1. What is the MySQL storage engine

MySQL belongs to relational database, and the storage of relational database is in the form of tables. Table creation, data storage, retrieval and update are all carried out by MySQL.

The storage engine is completed, which is also the important role that the MySQL storage engine plays in MySQL.

Readers who have studied SQL Server and Oracle may be well aware that there is only one storage engine for these two databases, while MySQL has more storage engines, such as MyISAM storage.

Engine, InnoDB storage engine and Memory storage engine.

The reason why MySQL has multiple storage engines is determined by the open source of MySQL. MySQL storage engine, in terms of category, can be roughly attributed to the official storage engine and the third

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

The role of 2.MySQL storage engine

MySQL storage engine plays an important role in MySQL, and its important role can be summarized as follows:

Function one: manage table creation, data retrieval, index creation, etc.

Function 2: meet the development of custom storage engine.

Type of 3.MySQL engine

Different kinds of storage engine have different storage engine table mechanism when storing tables. From the type of MySQL storage engine, it can be divided into official storage engine and third-party storage engine.

At present, there are 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

(IX) physical documents (File System)

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

Three: a query flow chart

At this point, I believe you have a deeper understanding of "what the MySQL architecture is like". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.