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

This article mainly gives you a brief introduction to the logical architecture of MySQL. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article of introduction to MySQL logical architecture can bring you some practical help.

I. logical architecture diagram

The MySQL logical architecture as a whole is divided into four layers:

The first layer is link threading, which is not unique to MySQL. In this layer, the main functions are link handling, authorization verification, security and other operations.

The second layer is the main layer of MySQL, where all statement parsing, analysis, optimization and caching are carried out, and built-in functions, such as date and time, are also carried out in this layer.

All the cross-storage engine functions in the third layer are completed in this layer, such as views, stored procedures, and so on.

The fourth layer is the storage engine, which is responsible for data acquisition and storage. In this layer, a lot of API are provided for the upper service layer to call to complete the data operation.

Working process:

When each client initiates a new request, the connection thread processing layer on the cloud server receives the request from the client and opens up a new memory space, in which a new thread is generated. When each user connects to the cloud server, a new thread is generated in the process address space to respond to the client request, and the query requests initiated by the user are run in the thread space. The results are also cached here and returned to the cloud server. Finally, thread destruction and reuse are done by the connection thread processing manager.

II. MySQL query process

As shown in the following figure:

1. Client / CVM communication protocol

This part is in a half-duplex state. Either the client sends data to the CVM or the CVM sends data to the half-duplex. Both cannot be performed at the same time.

When the client sends data to the CVM, it is sent as a single data packet. If the query is too large, the CVM will refuse to receive more data and throw an exception.

When the CVM sends a packet to the client, it generally includes multiple packets. The client must receive all the data completely and cannot refuse to receive part of the data and only get the first few items. You should try to keep queries simple and necessary during the development process, which is why you reduce select * and add limit restrictions.

2. Query cache

Before parsing the query statement, if the query cache is enabled, MySQL will check whether the current query hits the data in the cache, and if it does, it will directly respond to the data in the cache to the client, otherwise it will perform subsequent parsing and other operations.

MySQL's cache is stored in a reference table, indexed by a hash. The index contains a series of information related to the query, such as the query itself, the table to be queried, and so on. If two queries differ on either character, they will not hit the cache. Of course, not all queries are stored in the cache, and they are not cached when the query statement contains functions, user variables, and temporary tables. For example, if a query statement contains the NOW () function, the query will have different query results at different times, and there is no point in caching it, so the query will not be cached.

The cache of MySQL also has an invalid state, and all the information that will affect the query results will be blended into a hash value as the index, so when the data or structure of a table changes, all the caches involved in the table will be invalidated. That is, when a write operation is performed on a table, the cache involved in the table is set to invalidate. When the query cache is very large, this operation will result in a large system consumption. During a read operation, each query statement is checked to see if it hits the cache before execution, and is stored in the cache after execution. You should be very careful whether or not to open the cache.

3. Syntax parsing and preprocessing

Syntax parsing parses the query statement to generate a parsing tree, which is mainly checked by syntax. The preprocessor parses the parsing tree again and checks whether the tables, columns, and so on contained in the query exist.

4. Query optimization

There are many ways to implement a statement, and the role of the optimizer is to evaluate a certain execution cost and choose the one with the lowest cost. Of course, the way we expect to execute is not necessarily the real way of execution of MySQL. The optimizer will reorder the execution order and execute it to select the optimal solution that MySQL considers.

MySQL's query optimizer is a very complex component, which uses a lot of optimization strategies to generate an optimal execution plan: 1. Redefine the association order of tables (when multiple tables are associated with queries, they do not necessarily follow the order specified in SQL, but there are some tricks to specify the association order)

two。 Optimize the MIN () and MAX () functions (find the minimum value of a column. If the column has an index, you only need to find the leftmost end of the B+Tree index. Otherwise, you can find the maximum value. For details, see below)

3. Terminate the query early (for example, when using Limit, the query will be terminated as soon as a satisfied number of result sets are found)

4. Optimized sorting (in the old version of MySQL will use two transfer sort, that is, first read the row pointer and the fields that need to be sorted in memory, and then read the data rows according to the sort results, while the new version uses a single transfer sort, that is, read all the data rows at once, and then sort according to the given column. For Istroke O-intensive applications, it will be much more efficient)

5. Query execution engine

The query execution engine executes and gives the results in turn according to the execution plan generated in the optimization phase. These main implementations are implemented by calling the API of the storage engine. These API provide powerful functions and implement queries through operations such as overlay.

6. Respond to the client

Whether there are query results or not, it will be returned to the client, including the number of rows affected, the length of execution, and so on.

At this point, if the query cache is turned on, the query results will be cached.

When there are query results, the returned result set is an incremental process. Mysql may return the result to the client when the first result is generated, and the client continues to receive it until it is finished. The server does not need to store the result set to occupy memory and the client can receive the result at the first time.

The introduction to MySQL logical architecture will stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.