Let’s talk about MySQL logical architecture

This article brings you relevant knowledge about mysql, which mainly introduces the related issues of mysql logical architecture. The general architecture is divided into three layers, which are used for connection thread processing, Contains most of mysql core services and storage engines. I hope it will be helpful to everyone.

Recommended learning: mysql learning tutorial

Mysql logical architecture (roughly divided into three layers)

First layer: connection thread processing

• Client——>Connection thread processing (connection processing, authorization authentication, security)

The services included are not unique to mysql. They all serve C/S programs or what these programs need (connection processing, authorization authentication, security, etc.)

Second layer: Contains most of mysql core services

• ##Query cache——>Parser——>Optimizer——>Execute query

Query caching, parsing, analysis, optimization, caching, all built-in functions (date, time, mathematical and encryption functions). At the same time, all the functions provided by the storage engine are concentrated in this layer (storage Process, trigger, view)

Process: Before parsing the query, the cache must be queried first. The cache can only save the query information and result data. If a query is requested and exists in the cache, no parsing is required. The query is optimized and executed, and the results of the query stored in the cache are directly returned.

The third layer: contains the storage engine

• The storage engine is responsible for mysql Data storage and retrieval (similar to the file system under Linux)

Each storage engine has advantages and disadvantages, and the intermediate service layer communicates with the storage engine through APIs , these API interfaces shield the differences between different storage engines and make the query layer as transparent as possible.

The storage engine API contains more than a dozen low-level functions, such as executing "Start a transaction" or fetching rows with specific primary keys, but the storage engine generally does not parse SQL (InnoDB will parse foreign key definitions , because it does not implement this function itself), different storage engines will not communicate with each other, but will simply respond to upper-layer server requests.

Mysql logical architecture - detailed introduction (divided into eight steps)

1.Connectors

• refers to different prophecies Interaction with SQL

Nactive C API, JDBC, ODBC, .NET, PHP, Python, Perl, Ruby, VB

2. Enterprise Management Services & Utilities

• System Management and Control Tools

##Backup & Recovery, Security, Replication, Cluster, Partitioning, Instance Manager, INPORMATICN_SCHEMA, Administrator, Workbench, Query Browser, Migration Toolkit

3. Connection Pool

Manage buffered user connections, thread processing, etc. Caching requirements are required.

Responsible for monitoring various requests to MySQL Server, accepting connection requests, and forwarding all connection requests to the thread management module.
Every client request connected to MySQL Server will be assigned (created) a connection thread to serve it separately, and the connection thread is cached, so there is no need to Client connections are created and destroyed separately, and the main job of the connection thread is to be responsible for the communication between MySQL Server and the client, receive the client's command request, and transmit the result information from the server. The thread management module is responsible for managing and maintaining these connection threads, including the creation of threads. Thread cache, etc.

Authentication -Thread Reuse - Connection Limits - Check Memory -Caches

4.SQL Interface (SQL interface)

Accepts the user's SQL command and returns the results that the user needs to query. For example, select from is to call SQL Interface

DML, DDL, Stored Procedures, Views, Triggers, etc

5.Parser (parser)

When the SQL command is passed to the parser, it will be Verification and parsing, the parser is implemented by Lex and YACC, and is a very long script. In MySQL, we are used to calling all commands sent by the Client to the Server side called queries. In MySQL Server, the connection thread receives the client After receiving a Query on the end, the query will be directly passed to the module responsible for classifying various Queries and then forwarding them to each corresponding processing module

Main functions:

• a. Perform semantic and syntactic analysis of SQL statements, decompose them into data structures, then classify them according to the same operation type, and make targeted forwarding to subsequent steps. In the future, the SQL statements will be transmitted and The processing is based on this structure

• b. If an error is encountered during the decomposition, it means that the sql statement is unreasonable

Query Translation, Object Privilege

6.Optimizer (query optimizer)

SQL statements will use the query optimizer to optimize the query before querying, which is to optimize the customer The client requests the query. Based on the query statement requested by the client and some statistical information in the database, it is analyzed based on a series of algorithms and an optimal strategy is obtained to tell the subsequent program how to obtain the result of the query statement. The use of "Select-Projection-Join" strategy for query;
For example: select uid,name from user where gender=1;
This query statement first selects based on the statement after where, instead of First query all the tables and then perform gender filtering, and then perform attribute projection based on uid and name, instead of taking out all the attributes and then filtering, and finally connect these two query conditions to generate the final query result

Access Paths, Statistics

7.Cache and Buffer (query cache)

The main function is to submit the client to the Select class query of Mysql The result set returned by the request is cached in memory and corresponds to a hash value of the query. After any data changes occur in the base table of the data obtained by the query, MySQL will automatically invalidate the cache of the query. After reading and writing, In application systems with a very high proportion, Query Cache can significantly improve performance, but of course it consumes a lot of memory.
If there is a valid hit query result in the query cache, the query statement can directly fetch data from the query cache. This cache mechanism is composed of a series of small caches, such as table cache, record cache, and Key cache. , permission caching, etc.

Global and Engine Specific Caches & Buffers

8.pluggable storage Engines

• Storage engine interface: The most important feature that distinguishes MySQL from other databases is its plug-in table storage engine.

• MySQL plug-in storage engine architecture provides a series of standard management and service support. These standards have nothing to do with the storage engine itself and may be required by every database system, such as SQL Analyzers and optimizers, etc., while the storage engine is the implementation of the underlying physical structure. Each storage engine developer can develop according to their own wishes.

9.file system

• File system, data, log (redo, undo) index, error log, query record, slow query, etc.

Note: The storage engine is based on tables, not databases

Database workflow

Establishment TCP connection——>Verify user——>Create thread to parse SQL——>Generate execution plan——>Open table——>Search buffer to see if the required data page is cached——>Scan from disk Data——>Get data and write to buffer pool——>Return data to client——>Close table——>Close thread——>Close connection

• Top layer: Client connection

1. Connection processing: The client establishes a TCP connection with the database service layer. The connection management module will establish the connection and request a Connection thread. If there is an idle connection thread in the connection pool, it will be allocated to this connection. If not, a new connection thread will be created to be responsible for this client without exceeding the maximum number of thread connections.

2. Authorization authentication: Before the query operation, the user module needs to be called to perform authorization check to verify whether the user has permission. After passing the authorization, the service will be provided and the connection thread will begin to accept and process the SQL statement from the client

• Second Layer: Core Service

1. After the connection thread receives the SQL statement, it hands the statement to the SQL statement parsing module for syntax and semantics analyze.

2. If it is a query statement, you can first check whether there is a result in the query cache. If there is a result, it will be returned directly to the client.

3. If there are no results in the query cache, you need to query the database engine layer and send the SQL statement to the optimizer to optimize the query. If it is a table change, the classification is handed over to insert, update, delete. create, alert processing module for processing

• The third layer: database engine layer

1. Open the table, if Obtain the corresponding lock if necessary.

2. First check whether there is corresponding data in the cache page. If there is, it can be returned directly. If not, read it from the disk.

3. When the corresponding data is found on the disk After that, it will be loaded into the cache, making subsequent queries more efficient. Due to the limited cache, flexible LRU tables are often used to manage cache pages to ensure that the cached data is frequently accessed.

4. Finally, after obtaining the data, return it to the client, close the connection, and release the connection thread.

FAQ Analysis

1. What is a plug-in storage engine

The storage engine is to read and write data to the disk. Different storage engines , the read and write operation methods are also different, transactions, locks, etc. are different. Because we have different business needs, there will be many storage engines. In a database, because the storage engine is based on tables, different tables can have Different storage engines allow the storage engine to be loaded into the running MySQL server. This is the plug-in storage engine

2. What is LRU cache

Cache elimination mechanism strategy algorithm, because the cache memory is always limited, so some content must be deleted when the cache is full to make room for new content. Under the LRU mechanism, the eliminated data is called useless data. The full name of LRU is Least Recently Used, that is, we think that the data that has been used recently is useful, and the data that has not been used for a long time should be useless. When the memory is full, the data that has not been used for a long time will be deleted first

• The caching strategy of Ehcache in the JVM includes

1. ##LRU - least recently used (least recently used)

2. LFU - least frequently used (least frequently used)

3. FIFO - first in first out, the oldest element by creation time (clear the oldest cached data, Don’t care if you use it frequently)

Recommended learning:

mysql tutorial

The above is the detailed content of Let’s talk about MySQL logical architecture. For more information, please follow other related articles on the PHP Chinese website!