Brief description of MySQL logical architecture and workflow

MySQL logical architecture

1. Connection layer

The top layer is some client and connection services, including local sock communication and most similar tcp/ipcommunication based on client/server tools . Mainly complete some similar to connection processing, authorization authentication, and related security programs. In this layer, the concept of thread pool is introduced to provide threads for clients that have passed authentication and secure access. Also, SSLa secure connection based on this layer can be implemented . The server will also verify the operating authority it has for each client that is securely connected.

2. Service layer

The second layer architecture mainly completes most of the core service functions, such as SQLinterfaces, and completes cache query, SQLanalysis and optimization, and the execution of some built-in functions. All cross-storage engine functions are also implemented at this layer, such as procedures, functions, and so on. In this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization, such as determining the order of the query table, whether to use the index, etc., and finally generate the corresponding execution operation. If it is a selectstatement, the server will also query the internal cache. If the cache space is large enough, the performance of the system can be well improved in an environment that solves a large number of read operations.

3. Engine layer

At the storage engine layer, the storage engine is really responsible for MySQLthe storage and extraction of data in the medium, and the server communicates APIwith the storage engine. Different storage engines have different functions, so we can choose according to our actual needs.

4. Storage layer

The data storage layer mainly stores data on the file system running on the bare device and completes the interaction with the storage engine.

More detailed explanation

Insert picture description here

1. Connectors
   refer to SQLthe interaction with different languages .
2. The Connection Pool
   manages buffering user connections, thread processing and other needs that require caching. He is responsible for monitoring for MySQL Servera variety of requests, receiving the connection request, forwards all requests to the thread connection management module. Each MySQL Serverclient request on a connection will be allocated (or created) a connection thread to serve it individually. The main thread is responsible for work connected MySQL Serverwith the client communication, accept client command requests, deliver Serverresults and other information terminals. The thread management module is responsible for managing and maintaining these connection threads. Creating a thread, the thread cacheand so on.

3. Management Services & Utilities
   system management and control tools.

4. SQL Interface
   accepts the user's SQLcommand and returns the result that the user needs to query.

5. When the Parser
  SQL command is passed to the parser, it will be verified and parsed by the parser. The parser is made Lexand YACCimplemented, it is a very long script. In MySQLthe habit we all Clientsend end to Serverend of the command is called query, in MySQL Serverwhich connecting thread receives a client Queryafter, will direct the querypass to be responsible for a variety of Queryclassification and then forwarded to the respective corresponding processing module.
Main function:
  A, the SQLstatement syntax and semantic analysis, decomposed into a data structure, and then classified according to different types of operation, and then forwards to make targeted subsequent step, after SQLtransfer and processing of the statement is based on this structure ;
  B. If an error is encountered in the decomposition composition, then this sqlstatement is unreasonable.

6. Optimizer
   query optimizer: The query optimizer SQLwill be used to optimize the query before the statement is queried. Is to optimize the client's request query, according to client request querystatements, and some of the statistical information in a database and analyzed on the basis of a series of algorithms, come to an optimal strategy, tells the program how to get behind the queryresults statement.
  Is used to select - projection - join strategy of the query:
An example can be understood:

select uid,name from user where gender = 1;

This selectquery first whereselects based on the statement, instead of querying all the tables and then genderfiltering; and then performing attribute projection based on uidsum name, rather than taking all the attributes out and then filtering. Finally, these two query conditions are joined to generate the final query result.

7, Cache and Buffer
   query cache: The main function of the client is to be submitted to MySQLthe Selectclass queryreturning request result set cacheinto memory, the queryone hashto make a corresponding value. The querybase table data taken any data after the change, MySQLautomatically so that querythe Cachefailure. In an application system with a very high ratio of reads and writes, Query Cachethe performance improvement is very significant. Of course, it consumes a lot of memory.
  If the query cache has a matching query result, the query statement can go directly to the query cache to fetch data. This caching mechanism is composed of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

  MySQL The most important feature that distinguishes the storage engine interface from other databases is its plug-in table storage engine. MySQLThe 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 necessary for every database system itself, such as SQLanalyzers and optimizers. The storage engine is the underlying physics The realization of the structure, each storage engine developer can develop according to his own wishes.
Note: The storage engine is based on tables, not databases.

Database workflow

Insert picture description here

Top layer: client connection
  1. Connection processing: the client establishes a TCPconnection with the database service layer , the connection management module will establish a connection and request a connection thread. If there is an idle connection thread in the connection pool, it is assigned to this connection. If not, if the maximum number of connections is not exceeded, a new connection thread is created to be responsible for this client.
  2. Authorization and authentication: Before the actual operation, it is necessary to call the user module for authorization check to verify whether the user has authority. After passing, the service is provided, and the connection thread starts to receive and process the SQLstatements from the client .

The second layer: core service
  1. After the connection thread receives the SQLsentence, it passes the sentence to the SQLsentence parsing module for grammatical and semantic analysis.
  2. If it is a query statement, you can first check whether there is a result in the query cache, and if there is a result, it can be directly returned to the client.
  3. If there is no result in the query cache, you need to really query the database engine layer, so it is sent to the SQLoptimizer for query optimization. If the table is changed, respectively, to the insert, update, delete, create, alterprocessing module for processing.

The third layer: database engine layer
  1 , open the table, if necessary, obtain the corresponding lock.
  2. First query whether there is corresponding data in the cache page, if there is, you can return directly, if not, you will read it from the disk.
  3. When the corresponding data is found in the disk, it will be loaded into the cache to make subsequent queries more efficient. Due to limited memory, flexible LRUtables are often used to manage cache pages to ensure that the cached items are frequently accessed data.

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

SQL statement execution process

The database is usually not used alone, but is SQLcalled by other programming languages ​​through supporting interfaces MySQL, MySQLprocessed and returned to the execution result.
First of all, other programming languages ​​are SQLcalled through the support interface MySQL. After MySQLreceiving the request, the request will be temporarily placed in the connection pool and managed by management services and tools.
When the request enters the processing queue from the waiting queue, the manager will pass the request to the SQLinterface. After the SQLinterface receives the request, it will hashprocess the request and compare it with the data in the cache. If it matches , it will return directly through the cache. Process the result; otherwise, go to the file system to query: the SQLinterface is passed to the subsequent parser, and the parser will determine whether the SQLstatement is correct, and if it is correct, it will be converted into a data structure.
After the parser is processed, it will pass the processed request to the optimizer controller, which will generate a variety of execution plans, and finally the database will choose the best plan to execute.
After determining the optimal execution plan, the SQLstatement is processed by the storage engine, and the storage engine will obtain the corresponding data in the file system and return it the same way.

MySQL storage engine

MySQLThe default storage engine is before version 5.1 MyISAM, and it is after this version InnoDB.

1. MyISAMStorage engine

 MyIsam 的存储文件有三个,后缀名分别是 .frm、.MYD、MYI,其中 .frm 是表的定义文件,.MYD 是数据文件,.MYI 是索引文件。MyIsam 只支持表锁,不支持事务。MyIsam 由于有单独的索引文件,在读取数据方面的性能很高。Myisam是以堆结构进行组织数据,其表容易损坏。


 InnoDB 的存储文件有两个,后缀名分别是 .frm 和 .idb,其中 .frm 是表的定义文件,而 idb 是数据文件。InnoDB 中存在表锁和行锁,不过行锁是在命中索引的情况下才会起作用。InnoDB 支持事务,且支持四种隔离级别(读未提交、读已提交、可重复读、串行化),默认的为可重复读。

Comparison of the two storage engine: from MyISAMand InnoDBcan be seen to store files, MyISAMattention is quickly read the data, but the MyISAMtransactions are not supported, and lack of flexibility. It InnoDBsupports transaction and row-level locks, so the default storage engine will be 5.1later .MySQLInnoDB