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

Xiaobian to share with you a SQL statement in MySQL how to execute, I believe most people still do not know how, so share this article for everyone's reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

MySQL Infrastructure Analysis

1.1 MySQL Basic Architecture Overview

The following figure is a brief architecture diagram of MySQL. From the figure below, you can clearly see how the user's SQL statements are executed inside MySQL.

To help you understand the diagram, let's briefly describe the basic functions of some of the components involved in the following diagram. The functions of these components are described in detail in Section 1.2.

·Connector: authentication and permissions related (when logging into MySQL).·Query cache: Query cache is first used when executing query statements (removed after MySQL version 8.0, because this feature is not very practical).·Parser: If there is no cache hit, the SQL statement will pass through the parser. To put it bluntly, the parser first needs to see what your SQL statement wants to do, and then check whether your SQL statement syntax is correct.·Optimizer: Executes according to what MySQL considers optimal.·Executor: Executes the statement and returns data from the storage engine.

MySQL is 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., there is also a general log module binglog log module. Storage engine: mainly responsible for data storage and reading, using a replaceable plug-in architecture, supporting InnoDB, MyISAM, Memory and other storage engines, of which InnoDB engine has its own log module redolog module. The most popular storage engine is InnoDB, which has been used as the default storage engine since MySQL version 5.5.5.

1.2 Introduction to Server Layer Basic Components 1) Connector

Connectors are primarily associated with authentication and permission-related functions, like a high-level janitor.

It is mainly responsible for user login database, user identity authentication, including account password verification, permissions and other operations. If the user account password has been passed, the connector will query all permissions of the user in the permission table, and then the permission logic judgment in this connection will rely on the permission data read at this time. That is to say, as long as the connection is not opened later, the administrator will modify the user's permissions immediately, and the user will not be affected.

2)Query cache (removed after MySQL 8.0)

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

After the connection is established, when the query statement is executed, the cache will be queried first. MySQL will first verify whether the sql has been executed and cache it in memory in the form of Key-Value. Key is the query estimate, 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 results will be cached after completion to facilitate the next call. Of course, when the cache query is actually executed, the user's permissions will still be verified to see if there are query conditions for the table.

MySQL queries do not recommend caching 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. For data that isn't updated often, it's okay to use a cache.

Therefore, in most cases we do not recommend the use of query caching.

MySQL 8.0 version after the deletion of the cache function, the official is also that the function in the actual application scenario is relatively small, so simply deleted.

3)analyzer

MySQL does not hit the cache, then it will enter the analyzer, the analyzer is mainly used to analyze the SQL statement is to do, the analyzer will also be divided into several steps:

The first step, lexical analysis, an SQL statement has a number of strings, first to extract keywords, such as select, put forward the query table, put forward the field name, put forward the query conditions and so on. After doing this, you will proceed to step 2.

The second step, syntax analysis, is mainly to determine whether the sql you input is correct and whether it conforms to MySQL syntax.

After completing these two steps, MySQL is ready to start execution, but how to execute it, how to execute it is the best result? This is when the optimizer comes on.

4)Optimizer

The role of the optimizer is that it thinks the optimal execution scheme to execute (sometimes it may not be optimal, this article involves an in-depth explanation of this part of the knowledge), such as how to choose indexes when multiple indexes, how to choose association order when multiple tables are queried, etc.

It can be said that after passing through the optimizer, it can be said that the specific execution of this statement has been decided.

5)effector

After selecting the execution scheme, MySQL is ready to start execution. Before execution, it will check whether the user has permission. If there is no permission, it will return an error message. If there is permission, it will call the interface of the engine and return the execution result of the interface.

two-sentence analysis

2.1 query statement

Having said that, how exactly does a SQL statement work? In fact, our sql can be divided into two types, one is query, the other is update (add, update, delete). Let's analyze the query statement first. The statement is as follows:

select * from tb_student A where A.age='18' and A.name ='Zhang San';

Combined with the above description, we analyze the execution flow of this statement:

First check whether the statement has permission, if there is no permission, return an error message directly, if there is permission, in MySQL version 8.0 before, will first query the cache, with this sql statement as the key in memory query whether there is a result, if there is a direct cache, if not, go to the next step.

Lexical analysis is performed through the analyzer to extract key elements of SQL statements. For example, extracting the above statement is the query select, extracting the table name to be queried is tb_student, and all columns need to be queried. The query condition is id='1' of this table. Then determine whether this sql statement has syntax errors, such as whether the keyword is correct, etc. If there is no problem, execute the next step.

Next, the optimizer determines the execution scheme. The sql statement above can have two execution schemes:

a. First query the student whose name is "Zhang San" in the student table, and then judge whether the age is 18. b. Find out the students aged 18 first, and then search for the students named "Zhang San."

Then the optimizer selects the most efficient solution according to its optimization algorithm (the optimizer thinks that sometimes it is not necessarily the best). So, after confirming the execution plan, we are ready to start execution.

Permission verification, if no permission will return an error message, if permission will call the database engine interface, return the engine execution results.

2.2 UPDATE statement

The above is a query sql execution process, then let's see how an update statement is executed? SQL statements are as follows:

update tb_student A set A.age='19' where A.name ='Zhang San';

Let's modify Zhang San's age. In the actual database, the age field will definitely not be set, otherwise it will be typed by the technical director. In fact, the statement will basically follow the process of the previous query, but the update must be logged, which will introduce the log module, MySQL comes with a log module binlog (archive log), all storage engines can use, we commonly use InnoDB engine also comes with a log module redo log (redo log), we will use InnoDB mode to explore the execution process of this statement. The process is as follows:

·First query Zhang San this piece of data, if there is cache, it will also use 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 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, and then calls the engine interface to submit the redo log as the commit status.·Update complete.

There must be students here who will ask, why do you want to use two log modules, and why not use one log module?

This is because MySQL did not start with the InnoDB engine ( InnoDB engine is inserted into MySQL by other companies in the form of plug-ins), MySQL comes with the engine is MyISAM, but we know that redo log is unique to InnoDB engine, other storage engines do not, which leads to no crash-safe ability (crash-safe ability Even if the database restarts abnormally, the previously submitted records will not be lost), binlog log can only be used for archiving.

This is not to say that only one log module cannot be used, but the InnoDB engine supports transactions through redo logs. Then, some students will ask, I use two log modules, but don't be so complicated, why do redo log introduce prepare pre-commit state? Here we use counter-evidence to explain why we should do this?

·First write redo log and submit directly, then write binlog. Suppose the machine hangs up after finishing redo log and binlog is not written, then after the machine restarts, the machine will recover data through redo log, but bingog does not record this data at this time. When subsequent machine backups are performed, this piece of data will be lost, and master-slave synchronization will also lose this piece of data.·First write binlog, then write redo log, assuming that the binlog is finished, the machine restarts abnormally, because there is no redo log, the machine cannot recover this record, but binlog has records, then the same reason as above, there will be inconsistent data.

If the redo log is submitted in two phases, it will be different. After writing the binglog, submitting the redo log will prevent the above problems and ensure the consistency of the data. So the question arises, is there an extreme situation? Suppose the redo log is pre-committed and the binglog is finished. What happens if an abnormal restart occurs at this time? This depends on MySQL processing mechanism, MySQL processing process is as follows:

·Determine if redo log is complete, and if so, submit immediately.·If the redo log is pre-committed but not committed, it will determine whether the binlog is complete. If it is complete, the redo log will be committed, and if it is incomplete, the transaction will be rolled back.

This solves the problem of data consistency.

III Summary

MySQL is mainly divided into Server layer and engine layer. Server layer mainly includes connector, query cache, analyzer, optimizer, executor, and a log module (binlog). This log module can be shared by all execution engines. Redolog only has InnoDB. Engine layer is plug-in type, currently mainly includes, MyISAM,InnoDB,Memory, etc.·SQL and other execution processes are divided into two categories, one for queries and other processes are as follows: permission check---Query cache---Analyzer--Optimizer---Permission check---Executor---Engine for updates and other statements execution process is as follows: Analyzer--Permission check---Executor--Engine--redo log prepare--binlog--redo log commit

The above is "how to execute a SQL statement in MySQL" all the contents of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.