Shulou(Shulou.com)05/31 Report--

This article mainly introduces how the sql sentence is executed in mysql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

I. Analysis of mysql architecture

Here is a brief architectural diagram of mysql:

Mysql is mainly divided into Server layer and storage engine layer.

Server layer: mainly includes connector, query cache, analyzer, optimizer, executor, etc. All cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, functions, etc., as well as a general logging module binglog log module.

Storage engine: mainly responsible for data storage and reading, using replaceable plug-in architecture, supporting InnoDB, MyISAM, Memory and other storage engines, in which InnoDB engine has its own log module redolog module.

InnoDB version 5.5.5 serves as the default engine.

Connector

It is mainly responsible for logging in to the database and authenticating the user's identity, including verifying the account password and permissions. If the user's account password has been passed, the connector will query all the permissions of the user in the permission table. After that, the logical judgment of the permissions in this connection will rely on the permission data read at this time, that is to say, as long as the connection continues to open, the administrator immediately modifies the user's permissions. The user is also unaffected.

Query cache

After the connection is established, when the query statement is executed, the cache will be queried first, and Mysql will first verify whether the sql has been executed and cached in memory in the form of Key-Value. Key is the query expectation, and Value is the result set. If the cache key is hit, it will be returned directly to the client. If it is not hit, subsequent operations will be performed, and the result will be cached after completion to facilitate the next call. Of course, when the cache query is actually executed, it still verifies the permissions of the user and whether there are query conditions for the table.

Caching is not recommended for Mysql queries, because the validity time of caching is too short for frequently updated data, and the effect is often not good. For data that is not updated frequently, it is OK to use caching. The caching feature has been deleted after Mysql version 8.0, and officials also believe that this feature is relatively rare in actual application scenarios, so it is simply deleted.

Analyzer

If mysql does not hit the cache, it will enter the parser, which is mainly used to analyze what the SQL statement is for, and the parser will be divided into several steps:

The first step, lexical analysis, a SQL statement is composed of multiple strings, the first step is to extract keywords, such as select, propose the query table, propose the field name, put forward the query conditions and so on. After you have done this, you will move on to the second step.

The second step, syntax analysis, is to judge whether the sql you entered is correct and whether it conforms to the syntax of mysql.

After completing these two steps, mysql is ready to start execution, but how and how is the best result? This is when the optimizer is needed.

Optimizer

The role of the optimizer is that it considers the optimal execution scheme to execute (although sometimes it is not optimal), such as how to select indexes when multiple indexes and how to choose association order when multiple tables are queried.

Actuator

When the execution plan is selected, mysql is ready to start execution. Before execution, it will verify whether the user has permission. If not, an error message will be returned. If there is permission, the engine API will be called and the result of the API execution will be returned.

Second, sentence analysis

2.1 query statement

With all that said, how exactly is a sql statement executed? In fact, our sql can be divided into 2, one is query, the other is update (add, update, delete). Let's first analyze the query statement as follows:

Select * from tb_student A where A.ageThe 18' and A.nameplate 'Zhang San'

Combined with the above instructions, let's analyze the execution flow of this statement:

First check whether the statement has permission. If you do not have permission, an error message will be returned directly. If you have permission, before the mysql8.0 version, the cache will be queried first, and this sql statement will be used to query whether there are results in memory for key. If there is a direct cache, if not, perform the next step.

Through the parser for lexical analysis, extract the key elements of the sql statement, for example, extract the above statement is the query select, extract the table to be queried is called tb_student, you need to query all the columns, the query condition is the id='1' of the table. Then determine whether the sql statement has syntax errors, such as whether the keywords are correct, etc., and if there is no problem with the check, proceed to the next step.

Next, the optimizer determines the execution plan, and the above sql statement can be executed in two ways:

a. First inquire the student whose name is "Zhang San" in the student table, and then determine whether the age is 18.

b. First find out the 18-year-old students, and then inquire about the students whose name is "Zhang San".

Then the optimizer chooses the one with the best execution efficiency according to its own optimization algorithm (sometimes, according to the optimizer, it is not necessarily the best). Then after confirming the implementation plan, we are ready to start the implementation.

Perform permission verification. If there is no permission, an error message will be returned. If you have permission, the database engine API will be called to return the execution result of the engine.

2.2 Update statement

This is the execution process of querying sql, so let's take a look at how an update statement is executed. The sql statement is as follows:

Update tb_student A set A.ageThe 19' where A.nameplate 'Zhang San'

Let's modify the age for Zhang San. We certainly won't set the age field in the actual database, otherwise we will be beaten by the technical person in charge. In fact, the statement will basically follow the flow of the previous query, but the log must be recorded when the update is executed, which will introduce the log module. Mysql has its own log module binlog (archived logs), which can be used by all storage engines. Our commonly used InnoDB engine also comes with a log module redo log, so we discuss the execution flow of this statement in InnoDB mode. The process is as follows:

First query Zhang San this piece of data, if there is a cache, it will also use the cache.

Then get the query statement, change the age to 19, and then call the engine API interface to write this line of data. The InnoDB engine saves the data in memory and records the redo log at the same time. At this time, the redo log enters the prepare state, and then tells the executor that the execution is complete and can be submitted at any time.

After receiving the notification, the executor records the binlog, then calls the engine interface, and the submission redo log is submitted.

Update complete.

There must be some students here who will ask, why not use two log modules and one log module? This is the previous mysql mode, the MyISAM engine does not have redo log, so we know that it does not support transactions, so it does not mean that only one log module cannot be used, but the InnoDB engine supports transactions through redo log. So, another student will ask, I use two log modules, but not so complicated, okay? why did redo log introduce prepare pre-submission status? Here we use counter-proof to explain why we do this.

First write redo log to submit directly, then write binlog. Suppose that after writing redo log, the machine dies and the binlog log is not written. After the machine is rebooted, the machine will recover the data through redo log, but the bingog does not record the data at this time. When the machine is backed up later, this data will be lost, and the master-slave synchronization will also lose this data.

First write binlog, then write redo log. Suppose that after writing binlog, the machine restarts abnormally. Without redo log, the machine cannot recover this record, but binlog has a record, so the same reason as above will result in data inconsistency.

If you use the redo log two-phase commit approach is different, after writing the binglog, and then submit the redo log will prevent the above problems, thus ensuring data consistency. So the question is, is there an extreme situation? Suppose that redo log is in a pre-submitted state and binglog has been written, what if an abnormal restart occurs at this time? This depends on the processing mechanism of mysql. The processing process of mysql is as follows:

Determine whether the redo log is complete, and if the judgment is complete, submit immediately.

If the redo log is pre-committed but not commit, it will determine whether the binlog is complete, commit the redo log if it is complete, and roll back the transaction if it is incomplete.

This solves the problem of data consistency.

III. Summary

Mysql is mainly divided into Server and engine layer, Server layer mainly includes connector, query cache, analyzer, optimizer, executor, as well as a logging module (binlog), which can be shared by all execution engines.

The engine layer is plug-in, which mainly includes MyISAM,InnoDB,Memory and so on.

Sql and other execution processes are divided into two categories, one for queries and other processes are as follows: permission verification-"query cache -" analyzer-"optimizer -" permission verification-"executor -" engine

For updates and other statements, the execution process is as follows: parser-"permission verification -" executor-"engine-redo log prepare---" binlog--- "redo log commit

Thank you for reading this article carefully. I hope the article "how sql sentences are executed in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.