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

This article mainly introduces "the execution process of inserting and updating with a SQL and the principle of the log system". In the daily operation, I believe that many people have doubts about the execution process of inserting and updating with a SQL and the principle of the log system. The editor consulted all kinds of materials and sorted out a simple and useful method of operation. I hope it will be helpful for you to answer the doubt of "insert and update the execution process and log system principle with a SQL"! Next, please follow the editor to study!

Query how to execute

Usually when we use the database, what we see is usually a whole. For example, if you have the simplest table with only one ID field, when executing the following query statement:

Mysql > select * from T where ID=10

All we see is enter a statement and return a result, but we don't know how the statement is executed within MySQL. How to disassemble this process and look at the background part? The following is a schematic diagram of the basic architecture of MySQL, from which you can clearly see the execution of SQL statements in various functional modules of MySQL.

Generally speaking, MySQL can be divided into two parts: Server layer and storage engine layer.

Server layer: including connectors, query caching, parsers, optimizers, executors, etc., covering most of MySQL's core service functions, as well as all built-in functions (such as date, time, math, and encryption functions, etc.), all cross-storage engine functions are implemented in this layer, such as stored procedures, triggers, views, and so on.

Storage engine layer: responsible for data storage and extraction. Its architecture mode is plug-in, supporting multiple storage engines such as InnoDB, MyISAM, Memory and so on. The most commonly used storage engine today is InnoDB, which has been the default storage engine since MySQL version 5.5.5.

In other words, if you do not specify the engine type when you create a table with create table, the default is InnoDB. However, you can also select other engines by specifying the type of storage engine, such as using engine=memory in the create table statement, to specify that the memory engine is used to create tables. Different storage engines have different ways to access table data and support different functions.

It is not difficult to see from the figure that different storage engines share a Server layer, that is, the part from the connector to the actuator. You can first have an impression of the name of each component, and then I will walk you through the entire execution process with the SQL statement mentioned at the beginning, and look at the role of each component in turn.

Connector

In the first step, you will connect to this database first, and the connector will receive you at this time. Connectors are responsible for establishing connections with clients, obtaining permissions, maintaining and managing connections. The connection command usually goes like this:

Mysql-h 127.0.0.1-P 3306-u root-p

Root

After typing the command, you need to enter the password in the interactive conversation. Although the password can also be written directly after-p on the command line, it may lead to your password being compromised. If you are connected to a production server, it is strongly recommended that you do not do so.

The mysql in the connection command is a client tool that is used to establish a connection with the server. After completing the classic TCP handshake, the connector will begin to authenticate you, using the user name and password you entered.

If the user name or password is wrong, you will receive an Access denied for user error and the client program finishes execution.

If the user name and password authentication is passed, the connector will find out the permissions you have in the permissions table. After that, the permission judgment logic in this connection will depend on the permissions read at this time.

This means that after a user successfully establishes a connection, even if you modify the permissions of the user with the administrator account, it will not affect the permissions of the existing connection. After the modification is complete, only the newly created connection will use the new permission settings.

After the connection is complete, if you have no subsequent action, the connection is idle, which you can see in the show processlist command. The figure in the text is the result of show processlist, where the Command column is shown as the line of Sleep, indicating that there is now a free connection in the system.

If the client does not move for too long, the connector will automatically disconnect it. This time is controlled by the parameter wait_timeout, and the default value is 8 hours.

If the client sends a request again after the connection is disconnected, it will receive an error warning: Lost connection to MySQL server during query. At this point, if you want to continue, you need to reconnect and then execute the request.

In the database, persistent connection means that after a successful connection, the client always uses the same connection if there are persistent requests from the client. A short connection means that the connection is disconnected after a few queries are executed, and a new one is established next time.

The process of establishing a connection is usually complicated, so I suggest you minimize the action of establishing a connection, that is, try to use a long connection.

But after using all persistent connections, you may find that sometimes the memory used by MySQL increases particularly fast, because the memory temporarily used by MySQL during execution is managed in the connection object. These resources are not released until the connection is disconnected. Therefore, if the long connection accumulates, it may lead to too much memory consumption and be forcibly killed by the system (OOM). From the phenomenon, it means that MySQL has been restarted abnormally.

How to solve this problem? You can consider the following two options.

Disconnect the long connection regularly. Use it for a period of time, or after it is judged in the program that a large memory-consuming query has been executed, disconnect, and then query and reconnect.

If you are using MySQL version 5.7 or later, you can reinitialize the connection resources by executing mysql_reset_connection after each large operation. This process does not require reconnection and redo permission verification, but restores the connection to the state it was when it was just created.

Query cache

After the connection is established, you can execute the select statement. The execution logic comes to the second step: query caching.

After MySQL gets a query request, it will first go to the query cache to see if this statement has been executed before. Previously executed statements and their results may be cached directly in memory in the form of key-value pairs. Key is the statement of the query and value is the result of the query. If your query can find key directly in this cache, then the value will be returned directly to the client.

If the statement is not in the query cache, the execution phase continues later. After the execution is completed, the execution results are stored in the query cache. As you can see, if the query hits the cache, MySQL does not need to perform the following complex operations to return the results directly, which is very efficient.

But in most cases I would advise you not to use query caching. Why? Because query caching often does more harm than good.

Query cache invalidation is very frequent, as long as there is an update to a table, all query caches on that table will be emptied. So it's possible that you took great pains to save the results, and before you could use them, you were completely emptied by an update. For databases with high update pressure, the hit rate of the query cache will be very low. Unless your business has a static table, it will take a long time to update. For example, a system configuration table, then the query on this table is suitable for using query caching.

Fortunately, MySQL also provides this on-demand approach. You can set the parameter query_cache_type to DEMAND so that query caching is not used for default SQL statements. For statements for which you are sure you want to use query caching, you can specify them explicitly with SQL_CACHE, like the following statement:

Mysql > select SQL_CACHE * from T where ID=10

PS: version 8.0 of MySQL removes the entire function of query caching directly, which means that it has not been available since 8.0.

Analyzer

If you miss the query cache, you will start to actually execute the statement. First of all, MySQL needs to know what you want to do, so you need to parse the SQL statement.

The analyzer will do lexical analysis first. What you enter is a SQL statement made up of multiple strings and spaces, and MySQL needs to identify what the string is and what it represents.

MySQL is identified by the keyword select you typed, which is a query statement. It also recognizes the string "T" as "table name T" and the string "ID" as "column ID".

After these recognition is done, it is necessary to do "grammatical analysis". According to the results of lexical analysis, the parser will determine whether the SQL statement you enter satisfies the MySQL grammar according to the grammar rules.

If your sentence is incorrect, you will receive an error reminder from You have an error in your SQL syntax, such as the following sentence select without the opening letter "s".

Mysql > elect * from t where ID=1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that correspondsto your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

General syntax errors will indicate the first place where the error occurs, so you should focus on the content immediately following the use near.

Optimizer

After going through the analyzer, MySQL knows what you're going to do. It has to be processed by the optimizer before starting execution.

The optimizer decides which index to use when there are multiple indexes in the table, or the join order of each table when a statement has multiple table associations (join). For example, you execute the following statement, which executes the join of two tables:

Mysql > select * from T1 join T2 using (ID) where t1.c=10 and t2.d=20

It is possible to first take the ID value of the record from table T1, and then associate it to table T2 according to the ID value, and then judge whether the value of d in T2 is equal to 20.

You can also first take the ID value of the record of daddy 20 from table T2, then associate it to T1 according to the ID value, and then judge whether the value of c in T1 is equal to 10.

The logical results of the two execution methods are the same, but the efficiency of execution will be different, and the role of the optimizer is to decide which scheme to use.

After the optimizer phase is complete, the execution plan for this statement is determined and then enters the executor phase. If you still have some questions, such as how the optimizer chooses the index, whether it is possible to choose the wrong index, etc., it doesn't matter, I will explain the optimizer separately in a later article.

Actuator

MySQL knows what you want to do through the parser and what to do through the optimizer, so it enters the executor phase and starts executing the statement.

At the beginning of execution, you should first determine whether you have the permission to execute the query on the table T. if not, an error without permission will be returned, as shown below (in the project implementation, if the query cache is hit, permission verification will be done when the query is cached back to the result. The query also calls precheck to verify permissions before the optimizer.

Mysql > select * from T where ID=10;ERROR 1142 (42000): SELECT command denied to user'baked premises localhost 'for table' T'

If you have permission, open the table and continue execution. When the table is opened, the executor uses the interface provided by the engine according to the engine definition of the table.

For example, in table T in our example, the ID field does not have an index, then the execution flow of the executor is as follows:

Call the InnoDB engine interface to get the first row of the table to determine whether the ID value is 10. If not, skip it. If so, store the row in the result set.

Call the engine interface to fetch the next row and repeat the same judgment logic until the last row of the table is fetched.

The executor returns the recordset composed of all the rows that meet the criteria in the above traversal to the client as a result set.

At this point, the execution of the statement is complete.

For indexed tables, the same logic is performed. The first call is to take the interface of the first line that meets the condition, and then loop to the interface of the next row that meets the condition, which are already defined in the engine.

You will see a rows_examined field in the database's slow query log, indicating how many rows were scanned during the execution of the statement. This value is accumulated each time the executor calls the engine to get the data row.

In some scenarios, the executor calls once and scans multiple lines inside the engine, so the number of rows scanned by the engine is not exactly the same as rows_examined.

PS: if there is no field k in table T, and you execute the statement select * from T where knot 1, you will definitely report the error "this column does not exist": "Unknown column'k'in 'where clause'". The error will explode in the analyzer.

Summary:

The execution process of a query statement usually goes through functional modules such as connector, analyzer, optimizer, executor and so on, and finally reaches the storage engine.

How updates are performed

What is the execution process of an update statement? You may often hear DBA colleagues say that MySQL can return to any second in half a month. At the same time, you can't help but wonder, how do you do this?

Let's start with an update statement for a table. Here is the creation statement for this table, which has a primary key ID and an integer field c:

Mysql > create table T (ID int primary key, c int)

If you want to add the value of the line ID=2 to 1, the SQL statement will write:

Mysql > update T set c=c+1 where ID=2

I have introduced the basic execution link of SQL statement to you earlier. I will bring that picture here again, and you can also take a brief look at this picture and review it first. First of all, it is safe to say that the query statement of the set of processes, update statements will also go through.

It is the job of the connector to connect to the database before you execute the statement.

As we said earlier, when there is an update on a table, the query cache associated with that table is invalidated, so this statement empties all cached results on table T. This is why we generally do not recommend using query caching.

Next, the parser knows through lexical and grammatical parsing that this is an update statement. The optimizer decided to use the index ID. The executor is then responsible for the specific execution, finds this line, and then updates it.

Unlike the query process, the update process also involves two important logging modules, which are the protagonists we are going to discuss today: redo log (redo logs) and binlog (archive logs). Binlog content view, if you come into contact with MySQL, then these two words must not be bypassed, I will continue to emphasize with you in the following content. But then again, redo log and binlog have a lot of interesting aspects of design, and these design ideas can also be used in your own programs.

Important logging module: redo log

I don't know if you still remember the article "Kong Yiji". The hotel shopkeeper has a pink board, which is specially used to record guests' credit records. If there are not many people on credit, he can write down the customer's name and account on the board. But if there are more people on credit, there will always be times when the fan board can't remember it. At this time, the shopkeeper must have a special account book to record credit.

If someone wants credit or repayment, the shopkeeper generally has two ways:

One way is to turn out the ledger directly and add or deduct the credit account this time.

Another way is to write down the account on the pink board first, and then check the account book after closing.

When the business is booming, the counter is very busy, the shopkeeper will definitely choose the latter, because the former operation is really too troublesome. First of all, you have to find the record of this person's total credit. If you think about it, there are dozens of pages. If the shopkeeper wants to find that name, he may have to take presbyopic glasses to find it slowly. After finding it, he will come up with an abacus to calculate it, and finally write the results back on the ledger.

It's troublesome to think about the whole process. By contrast, it is convenient to write it down on the pink board first. If you think about it, if the shopkeeper does not have the help of a pink board and has to turn over the account book every time he keeps accounts, is the efficiency unbearably low?

Similarly, there is this problem in MySQL. If every update operation needs to be written to disk, and then the disk has to find the corresponding record, and then update it, the whole process IO cost and search cost are very high. In order to solve this problem, MySQL designers use ideas similar to hotel shopkeeper powder boards to improve update efficiency.

The whole process of matching the pink board with the account book is actually the WAL technology often mentioned in MySQL. The full name of WAL is Write-Ahead Logging, and its key point is to write the log first, then the disk, that is, write the pink board first, and then write the account book when you are not busy.

Specifically, when there is a record that needs to be updated, the InnoDB engine will first write the record to redo log and update the memory, and then the update is complete. At the same time, the InnoDB engine will update the operation record to disk at the appropriate time, and this update is often done when the system is relatively idle, just like the shopkeeper does after closing.

If there is not much credit today, the shopkeeper can wait until closing time to sort it out. But what if one day there is a lot of credit and the chalk board is full? At this time, the shopkeeper had to lay down his work, update some of the credit records in the pink board into the account book, and then erase these records from the pink board to make room for new accounts.

Similarly, the redo log of InnoDB is a fixed size, for example, it can be configured as a set of 4 files, each of which is 1GB, so the powder board can record the operation of 4GB in total. Write from the beginning, and then go back to the beginning and loop at the end of the write, as shown in the following figure.

Write pos: is the location of the current record, move back while writing, and then go back to the beginning of document 0 after writing to the end of document 3.

Checkpoint: is the current location to be erased, which is also pushed back and looped. Update the record to the data file before erasing the record.

Between write pos and checkpoint is the empty part of the powder board that can be used to record new operations. If write pos catches up with checkpoint, it means that the pink board is full, and you can't perform any new updates at this time. You have to stop and erase some records and push checkpoint.

With redo log,InnoDB, you can ensure that even if the database is abnormally restarted, the previously submitted records will not be lost, which is called crash-safe. Record what changes have been made to this page.

To understand the concept of crash-safe, consider our previous example of credit records. As long as the credit records are recorded on the pink board or in the account book, even if the shopkeeper forgets, such as abruptly suspending business for a few days, the credit account can still be clarified through the data on the account book and the powder board after the business resumes.

Important logging module: binlog

As we mentioned earlier, there are actually two pieces of MySQL as a whole: one is the Server layer, which mainly does things at the functional level of MySQL, and the other is the engine layer, which is responsible for storing specific matters related to it. The pink board redo log we talked about above is a log unique to the InnoDB engine, and the Server layer also has its own log, called binlog (archive log). Binlog has two modes, statement format is to remember the sql statement, row format will record the contents of the line, write down two, both before and after the update.

I'm sure you'll ask, why are there two journals?

Because at first there was no InnoDB engine in MySQL. MySQL comes with an engine of MyISAM, but MyISAM does not have the capability of crash-safe, and binlog logs can only be used for archiving. InnoDB is another company that introduced MySQL as a plug-in, and since there is no crash-safe capability to rely on binlog alone, InnoDB uses another logging system, that is, redo log, to implement crash-safe capabilities.

There are three differences between the two kinds of logs.

Redo log is specific to the InnoDB engine; binlog is implemented at the Server layer of MySQL and can be used by all engines.

Redo log is a physical log that records changes made on a data page; binlog is a logical log that records the original logic of the statement, such as adding 1 to the c field of the line ID=2.

Redo log is written in a loop and the fixed space is used up; binlog can be appended to write. Append write means that when the binlog file is written to a certain size, it will switch to the next one and will not overwrite the previous log.

With a conceptual understanding of these two logs, let's look at the internal flow of the executor and the InnoDB engine when executing this simple update statement.

The actuator first finds the engine to pick up the ID=2 line. ID is the primary key, and the engine uses a tree search to find this line. If the data page of the ID=2 line is already in memory, it is returned directly to the executor; otherwise, you need to read the memory from disk and then return it.

The executor takes the row data given by the engine, adds 1 to this value, for example, it was N, now it is Number1, gets a new row of data, and then calls the engine interface to write this new line of data.

The engine updates the new line of data to memory and records the update operation to redo log, when the redo log is in the prepare state. The executor is then informed that the execution is complete and the transaction can be committed at any time.

The executor generates the binlog for this operation and writes the binlog to disk.

The executor calls the commit transaction interface of the engine, and the engine changes the redo log just written to the commit (commit) state, and the update is completed.

Here I give the flow chart of the execution of this update statement, with a light box indicating that it is executed inside the InnoDB and a dark box indicating that it is executed in the executor.

You may have noticed that the last three steps seem a little winding, splitting the write to redo log into two steps: prepare and commit, which is a two-phase commit.

Two-phase submission

Why is there to be a two-phase submission? This is to make the logic between the two logs consistent. To illustrate this, we have to start with the question at the beginning of the article: how to restore the database to any second in half a month?

As we said earlier, binlog records all logical operations in the form of appended writes. If your DBA promises to restore within half a month, then all the binlog of the last half month will be saved in the backup system, and the whole database will be backed up regularly. The periodicity here depends on the importance of the system, either once a day or once a week.

When you need to restore to a specified second, for example, when you find that there is an erroneous deletion of the table at 12:00 one afternoon, and you need to retrieve the data, you can do this:

First, find the most recent full backup. If you are lucky, it may be a backup from last night, from this backup to the temporary library.

Then, starting from the point in time of the backup, the backup binlog is taken out in turn and replayed to the moment before the table was mistakenly deleted at noon.

In this way, your temporary library is the same as the online library before it was deleted by mistake, and then you can take the table data out of the temporary library and restore it to the online library as needed.

All right, after talking about the data recovery process, let's come back to why the log needs to be committed in two phases. Here, we might as well use the method of counterproof to explain.

Because redo log and binlog are two separate logic, if you don't need a two-phase commit, either write the redo log first and then write the binlog, or in reverse order. Let's see what's wrong with these two approaches.

Still use the previous update statement as an example. Suppose the line of the current ID=2, the value of field c is 0, and what happens if crash occurs during the execution of the update statement after the first log is written and the second log is not finished?

Write redo log first and then binlog. Suppose that the MySQL process restarts abnormally when the redo log is finished and the binlog is not finished. As we said earlier, after the redo log is written, the system can still recover the data even if it crashes, so the value of c in this line after recovery is 1. However, because the crash is not finished with binlog, there is no record of this statement in binlog at this time. Therefore, when you back up the log later, there is no such statement in the saved binlog. Then you will find that if you need to use this binlog to restore the temporary library, because the binlog of this statement is missing, the temporary library will lose this update, and the value of the recovered line c is 0, which is different from the value of the original library.

Write binlog first and then redo log. If crash is written after binlog, the value of c in this line is 0 because the redo log has not been written and the transaction is invalid after crash recovery. But the log of changing c from 0 to 1 has been recorded in binlog. So, when you use binlog to recover later, there is one more transaction, and the value of the recovered row c is 1, which is different from the value of the original library.

As you can see, if two-phase commit is not used, the state of the database may be inconsistent with that of the library recovered with its logs.

1 prepare stage-> 2 write binlog-> 3 commit

When it crashes before 2,

Restart recovery: no commit is found and roll back. Backup restore: no binlog. Consistent

When it crashes before 3

Restart recovery: although there is no commit, it satisfies the integrity of prepare and binlog, so it will automatically commit after restart. Backup: there is binlog. Consistent

You might say, is this probability very low, and there is usually no scene where you need to restore the temporary library?

Actually, no, it's not just that you need to use this process to recover data after misoperation. When you need to expand capacity, that is, when you need to build more backup libraries to increase the reading capacity of the system, the common practice is to use full backup plus application binlog. This inconsistency will lead to inconsistency between master and slave databases online.

In a nutshell, both redo log and binlog can be used to represent the commit status of a transaction, while two-phase commit is to keep the two states logically consistent.

Summary

This paper introduces the two most important logs in MySQL, namely physical log redo log and logical log binlog.

Redo log is used to guarantee crash-safe capabilities. When the innodb_flush_log_at_trx_commit parameter is set to 1, the redo log of each transaction is persisted directly to disk. I suggest you set this parameter to 1 to ensure that data is not lost after an abnormal restart of MySQL.

Binlog can't be removed yet. One reason is that redolog is only available in InnoDB, but not in other engines. Another reason is that redolog is written in a loop and is not persisted. Redolog does not have the archiving function of binlog.

When the sync_binlog parameter is set to 1, the binlog of each transaction is persisted to disk. I also recommend that you set this parameter to 1, which ensures that binlog will not be lost after an abnormal restart of MySQL.

Introduces the two-phase commit that is closely related to the MySQL log system. Two-phase commit is a common solution for maintaining logical consistency of data across systems, which may be used in daily development even if you don't do database kernel development.

A comparison between one preparation a day and one preparation a week

In an one-day mode, you need to apply a day's binlog in the worst case. For example, you make a full backup at 0 o'clock every day, and you have to restore a backup until 23:00 last night. The worst-case scenario is to use binlog for a week. The corresponding indicator of the system is the mentioned RTO (recovery target time). Of course, this has a cost, because more frequent full backups consume more storage space, so the RTO is cost, so you need to evaluate it according to the importance of the business.

At this point, the study of "using a SQL to insert and update the execution process and the principle of the log system" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.