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

This article mainly introduces what is the operation principle and infrastructure of MySQL. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone. Let's work with the author to understand what the operation principle and infrastructure of MySQL is.

1. Connectors

Interact with sql statements in other programming languages, such as php, java, etc.

2. Management Serveices & Utilities

System management and control tools

3. Connection Pool (connection pool)

Manage caching requirements such as buffering user connections, threading, etc.

4. SQL Interface (SQL interface)

Accept the user's SQL command and return the results that the user needs to query. For example, select from calls SQL Interface.

5. Parser (parser)

The SQL command is validated and parsed by the parser when it is passed to the parser.

Main functions:

A. Decompose the SQL statement into a data structure and pass this structure to the next step, based on which the delivery and processing of the SQL statement is based

b. If an error is encountered in the decomposition composition, then the sql statement is unreasonable and the statement will not continue to execute.

6. Optimizer (query optimizer)

The SQL statement uses the query optimizer to optimize the query before the query (generate multiple execution plans, and eventually the database will choose the optimized scheme to execute and return the results as soon as possible) he uses the "select-projection-join" strategy to query.

It can be understood by an example: select uid,name from user where gender = 1

This select query is selected according to the where statement first, rather than querying all the tables first and then gender filtering.

This select query first projects attributes based on uid and name, rather than taking out all the attributes and then filtering them.

Join the two query conditions together to generate the final query results.

7. Cache and Buffer (query caching)

If the query cache has a hit query result, the query statement can directly fetch data from the query cache.

This caching mechanism consists of a series of small caches. Such as table cache, record cache, key cache, permission cache, etc.

8.Engine (Storage engine)

Storage engine is a specific subsystem dealing with files in MySql. It is also one of the most distinctive features of Mysql.

Mysql's storage engine is plug-in. It customizes a file access mechanism based on an abstract interface of the file access layer provided by MySql AB (this access mechanism is called storage engine).

-+-

SQL statement execution process

The database is usually not used directly, but is called by other programming languages through SQL statements, which is processed by mysql and returns the execution result. So what happens when Mysql accepts the SQL statement?

First of all, the program's request will interact with it through mysql's connectors, and after the request is everywhere, it will be temporarily stored in the connection pool (connection pool) and managed by the processor (Management Serveices & Utilities). When the request goes from the waiting queue to the processing queue, the manager throws the request to the SQL interface (SQL Interface). After receiving the request, the SQL API will hash the request and compare it with the result in the cache. If there is an exact match, the result will be returned directly through the cache. Otherwise, you need to go through a complete process:

(1) the SQL interface is thrown to the following interpreter (Parser). As mentioned above, the interpreter will determine whether the SQL statement is correct or not, and if correct, convert it into a data structure.

(2) after the interpreter has finished processing, it will come to the following optimizer (Optimizer), which will produce a variety of execution plans. Finally, the database will choose the optimal scheme to execute and return the results as soon as possible.

(3) after the optimal execution plan is determined, the SQL statement can be processed by the storage engine (Engine). The storage engine will obtain the corresponding data from the back-end storage device and return it to the program.

Here are a few points to note:

(1) how to cache query data?

When the storage engine processes the data and returns it to the program, it also keeps a piece of data in the cache so that the next same request can be processed more quickly. Specifically, mysql will hash the query statement, execution result, and so on, and keep it in cache, waiting for the next query.

(2) the difference between buffer and cache?

As you can see from the figure above, there are actually buffer and cache in the cache, so is there any difference between them? To put it simply, buffer is the write cache and cache is the read cache.

(3) how to determine whether the required data has been cached in the cache

There may be a misunderstanding here that when processing SQL statements, in order to determine whether the query results have been cached, we will walk through the whole process, obtain the execution results, and then compare them with those needed to see if they are hit, and say that since we have to go through the whole process regardless of whether the query content is cached in the cache, what is the advantage of caching?

In fact, this is not the case. After the first query, mysql hash the query statement and the query results and keep them in the cache. After the SQL query arrives, after the same hash processing, the two hash values are compared. If they are the same, the query results are returned from the cache; otherwise, the whole process needs to go through.

After reading the above about the operation principle and infrastructure of MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.