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

This article mainly gives you a brief introduction to the basic working principles 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 on the basic working principles of mysql can bring you some practical help.

1. Components of Mysql

Mysql consists of SQL interface, parser, optimizer, cache, and storage engine.

2. Mysql working principle diagram

3. Description of each component of Mysql schematic diagram

3-1:connectors

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

3-2:Management Serveices & Utilities

System management and control tools

3-3, Connection Pool (connection pooling)

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

3-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.

3-5, Parser (parser)

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

The main functions of the parser:

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, it means that the sql statement is unreasonable and the statement will not continue to execute

3-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.

3-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.

3-8, Engine (Storage engine)

Storage engine is a specific subsystem dealing with files in MySql, and it is also one of the most characteristic parts of Mysql.

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

4. 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. After the request arrives, 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). The interpreter will judge 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.

5. Attention

(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 mysql schematic, there are actually two buffer and cache in the cache, so the 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 or not, what is the advantage of caching?

In fact, this is not the case. After the first query, mysql processes the query statement and the query results with hash and keeps 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.

The basic working principle of mysql will first tell you here, for other related issues you want to know 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.