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

This article mainly gives you a brief introduction to the method of executing SQL sentences in 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 how to execute SQL sentences in MySQL can bring you some practical help.

-MySQL infrastructure analysis

1.1 Overview of MySQL basic architecture

The following figure is a brief architectural diagram of MySQL, from which you can clearly see how the user's SQL statements are executed within MySQL.

Let's first briefly introduce the basic functions of some of the components involved in the following figure to help you understand this picture, which will be described in detail in Section 1.2.

Connector: authentication is related to permissions (when logging in to MySQL).

Query caching: when a query statement is executed, the cache is queried first (MySQL version 8.0 is removed because this feature is not very practical).

Parser: if you don't hit the cache, the SQL statement will pass through the parser. To put it bluntly, the parser is to see what your SQL statement is going to do, and then check whether your SQL statement syntax is correct.

Optimizer: execute according to what MySQL considers to be the best solution.

Executor: executes the statement and then returns data from the storage engine.

To put it simply, 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. The most commonly used storage engine today is InnoDB, which has been used as the default storage engine since MySQL version 5.5.5.

1.2 introduction of basic components of Server layer 1) Connector

Connectors are mainly related to authentication and permissions-related functions, just like a high-level doorman.

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.

2) query cache (removed after MySQL version 8.0)

The query cache is mainly used to cache the SELECT statement we execute and the result set of the statement.

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 query cache invalidation can be very frequent in real business scenarios, and if you update a table, all query caches on that table will be emptied. It is OK to use caching for data that is not updated frequently.

Therefore, it is generally not recommended to use query caching in most cases.

The cache 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.

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

4) Optimizer

The role of the optimizer is to implement what it considers to be the optimal execution plan (sometimes it may not be optimal, and this article involves an in-depth explanation of this part of the knowledge), such as how to select an index when there are multiple indexes. how to choose the association order when querying multiple tables.

It can be said that after the optimizer, it can be said that exactly how the statement should be executed has been decided.

5) Actuator

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

Two 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 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 basically follows the process of the previous query, but the log must be recorded when the update is executed, which will introduce the log module. MySQL comes with log module binlog (archived log), which can be used by all storage engines. Our commonly used InnoDB engine also comes with a log module redo log (redo log). We will 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 because at the beginning, MySQL does not work with InnoDB engine (InnoDB engine is plugged into MySQL by other companies as plug-ins). MySQL comes with MyISAM engine, but we know that redo log is unique to InnoDB engine and other storage engines do not have it. As a result, there will be no crash-safe capability (even if the database is restarted abnormally, the previously submitted records will not be lost), and binlog logs can only be used for archiving.

This is not to say that it is not possible to use only one logging module, 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.

Three summaries

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), this logging module can be shared by all execution engines, and only InnoDB has redolog.

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 is hit)-"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) [related recommendations: MySQL tutorial]

MySQL in the implementation of SQL statements to introduce the method to 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.