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
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
YACCimplemented, it is a very long script. In
MySQLthe habit we all
Clientsend end to
Serverend of the command is called
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.
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.
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
Is used to select - projection - join strategy of the query:
An example can be understood:
select uid,name from user where gender = 1;
whereselects based on the statement, instead of querying all the tables and then
genderfiltering; and then performing attribute projection based on
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
queryreturning request result set
cacheinto memory, the
hashto make a corresponding value. The
querybase table data taken any data after the change,
MySQLautomatically so that
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.
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
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
MySQLprocessed and returned to the execution result.
First of all, other programming languages are
SQLcalled through the support interface
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
MyIsam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。MyIsam 只支持表锁，不支持事务。MyIsam 由于有单独的索引文件，在读取数据方面的性能很高。Myisam是以堆结构进行组织数据，其表容易损坏。
InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。InnoDB 中存在表锁和行锁，不过行锁是在命中索引的情况下才会起作用。InnoDB 支持事务，且支持四种隔离级别（读未提交、读已提交、可重复读、串行化），默认的为可重复读。
Comparison of the two storage engine: from
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