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

Today, I will talk to you about how a SQL statement is executed in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Preface

Recently began to learn mysql-related knowledge, according to the knowledge learned, according to their own understanding to organize and share, the editor will analyze the next sql statement in the mysql implementation process, including the sql query in the mysql how to flow, how the update of the sql statement is completed. Before I analyze it, I will show you the infrastructure of MySQL to see what components MySQL is made of and what these components do, which can help us understand and solve these problems.

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 *, it will be returned directly to the client. If there is no *, 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 have a * cache, it will enter the parser. The parser is mainly used to analyze what the SQL statement is for, and the parser will be divided into several steps:

* 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, propose 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 to execute and how to execute is the result of * *? This is when the optimizer is needed.

Optimizer

The role of the optimizer is to execute what it considers to be an execution plan (although sometimes it is not), such as how to select an index when there are multiple indexes and how to choose the association order when querying multiple tables.

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 two types, 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 a scheme of execution efficiency according to its own optimization algorithm (sometimes, according to the optimizer, it is not necessarily *). 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.

The execution process of the query statement is as follows: permission check (if * cache)-"query cache -" analyzer-"optimizer"-"permission check -" executor-"engine

The execution process of the update statement is as follows: parser-"permission verification -" executor-engine-redo log (prepare status-"binlog---" redo log (commit status)

After reading the above, do you have any further understanding of how a SQL statement is executed in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.