First, MySQL architecture

First, Introduction

MySQL is an open source database management system, which is more lightweight, less expensive than Oracle, and becomes more and more popular with businesses, especially small and medium-sized enterprises, as they become more sophisticated.

The overall architecture of MySQL consists of the following aspects:

1) Main structure

2) Physical File composition

3) Composition of SQL logic modules

4) SQL Logic module coordination work

Second, the main structure

There are two main components of MySQL server:

1) SQL layer

2) Storage Engine layer

From what we can learn:

1) MySQL is a c/s Architecture model, and the client operates the service-side data by establishing a connection with the server;

2) server-side connection module, each client sends a request as a thread processing;

3) The parser analyzes the request and forwards it to the optimizer;

4) Improve query performance through caching;

5) The optimizer is responsible for interacting with the underlying storage engine to store and query MySQL data;

Iii. Composition of physical documents

The MySQL physical file consists of three blocks:

1) Log file

1-1) Error log: Logs all critical error messages encountered, details of each boot shutdown;

1-2) binary Log binary logs: That is, Binlog, record all the operations to modify the database ;

1-3) query log: Record all query operations, large size, after opening the performance has an impact;

1-4) Slow query log: Records all SQL statements with execution time exceeding long_query_time, and statements that reach min_examined_row_limit distance;

1-5) InnoDB redo log: records the physical changes and transaction information made by InnoDB ;

2) Data file

2-1). frm file: table structure definition Information

2-2). MyD file: Data file for MyISAM engine;

2-3). Myi File: Index file of the MyISAM engine;

2-4). ibd files and. ibdata files: Innodb Data and Indexes ,. Ibdata is configured to share tablespace when used,. IBD is configured to use when the table space is exclusive;

3) Other documents

3-1) System configuration file:/etc/my.cnf

3-2) PID file: Store your own process ID

3-3) Socket file: Connection client use

Iv. composition of SQL logic modules

The MySQL logical architecture uses the separation of SQL layer and storage engine to realize the separation of data storage and logical service. The macro can be divided into three layers:

1) Connecting the processing layer

2) analysis, cache processing layer

3) Optimizer layer

In fact, the SQL layer can be subdivided into many sub-modules:

1) Initialize the module: When the database starts, the database initialization operation;

2) Core API module: The optimization function of the bottom operation;

3) Network Interaction Module: external to provide the API interface to receive the sending data;

4) Server Client Interaction Protocol module: Implement the client service side of the interactive Protocol;

5) User module: Control user connection login and authorization;

6) Access Control module: Monitor the user's each operation, depends on the user module;

7) connection management, connection thread, and thread Management module: monitor and manage the threads connected to the client;

8) Forwarding module: Forward the request to the corresponding processing module;

9) Cache module: The result of query request cache, improve performance;

10) Optimizer module: According to the query request computation to improve the query Access speed optimization strategy, according to the optimal strategy to return query statements;

11) Table Change module: DML and DDL statement processing;

12) Table Maintenance module: Inspection table status, analysis, optimization table structure, repair table;

13) System State Management module: Return various status data, such as: Show status;

14) Table Manager: Maintain system-generated table files such as the. frm file. ibd file ... The table structure of the information is cached, and the module also control the table-level lock;

15) Logging module: Responsible for the entire database logical layer of log files;

Copy module : Divided into Master module and slave module ; Master module is responsible for copying binary files and interacting with slave I/O threads The slave module is primarily responsible for receiving binary logs from the master side, writing to the local I/O thread , and reading the logs from the relay log file , parsing the commands executed on the slave side. To the slave end of the SQL threading process;

17) Storage Engine Interface module: the implementation of the underlying storage engine plug-in management, the data processing is highly abstract;

Five, the SQL logical module coordination work

1) After MySQL is started, the initialization module will read the system parameters and command parameters from the system configuration file, initialize the whole system, and the storage engine will start;

2) at the end of initialization, the connection management module listens to the client's connection request and forwards the connection request to the thread management module to request a connection thread;

3) After the thread module receives the request, the user module is called to authorize the check, and after the authorization checks whether the idle thread is removed and if there is a connection with the client, if there is no new set up a thread to connect with the client;

4) MySQL request is divided into two types, one is the need for command parsing and distribution to execute, the other can be executed directly; either way, if the log is turned on, the log module logs the log;

5) If it is a query type request, the control will be given to the query parser , the query parser checks whether the select type, if yes, starts the querying cache module , if the cache hit the cache data back to the connection thread module , the connection thread passes the data to the client, and if there is no cache or a query that can be cached, the parser will handle the corresponding processing module through the query dispatcher;

6) If the parser result is dml/ddl, give it to the change module , if it is a check, fix the query to the table maintenance module , if it is a statement that is not cached , then give query optimizer module . In fact, the table change module is divided into several small modules, such as: Insert processor, delete processor, update processor, create processor, and alter processor these small modules to take charge of different DML and DDL. In summary, the query optimizer, table change module, table Maintenance module, replication module, and state module are all distributed to different types of modules based on the results of the command parser , finally interacting with the storage engine .

7) When a command is completed, control is returned to the connection thread module , which relies on the core API modules during each module processing, such as: memory management, file I/O, string processing, etc.

Reference: "MySQL Technology insider"

First, MySQL architecture