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

This article mainly explains how to execute a update statement in MySQL. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to execute a update statement in MySQL.

Preparation in advance

⭐⭐ first creates a table and then inserts three pieces of data:

CREATE TABLE T (ID int (11) NOT NULL AUTO_INCREMENT, c int (11) NOT NULL, PRIMARY KEY (ID)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' Test Table'; INSERT INTO T (c) VALUES (1), (2), (3)

Perform the update operation after letting:

Update T set c=c+1 where ID=2

Before we talk about the update operation, let's take a look at the execution flow of sql statements in MySQL.

The execution process of SQL statement

As shown in the figure: MySQL database is mainly divided into two levels: service layer and storage engine layer service layer: server layer includes connector, query cache, analyzer, optimizer, executor, including most of the core functions in MySQL, all cross-storage engine functions are also implemented in this layer, including stored procedures, triggers, views, and so on. Storage engine layer: the storage engine layer includes MySQL's common storage engines, including MyISAM, InnoDB and Memory, the most commonly used is InnoDB, which is now the default storage engine for MySQL.

Introduction to components in the server layer ✨✨✨

Connector: requires a MySQL client login, requires a connector to connect the user to the MySQL database, "mysql-u username-p password" for MySQL login, and after completing the TCP handshake, the connector verifies the login identity based on the user name and password entered.

Query cache: after getting an execution request, MySQL will first go to the query cache to find out whether the SQL statement has been executed. The previously executed statements and the results will be stored in memory in the form of key-value pairs. Key is the query statement and value is the result of the query. If the SQL statement can be found through key, the execution result of SQL is returned directly. If it does not exist in the cache, the execution phase continues later. When the execution is complete, the execution result is put into the query cache. The advantage is high efficiency. However, query caching is not recommended, because if a table is updated in MySQL, all query caches will be invalidated. For databases that update frequently, the hit rate of query cache is very low. It should be noted that in the MySQL8.0 version, the query cache function is removed and there is no query cache function.

Parser: divided into lexical analysis and grammatical analysis

Lexical analysis: first of all, MySQL will parse according to the SQL sentence, and the analyzer will first do lexical analysis. The SQL you write is a SQL statement composed of multiple strings and spaces. MySQL needs to identify what the string is and what it represents.

Syntax analysis: then perform syntax analysis. According to the results of lexical analysis, the parser will judge whether the input SQL statement satisfies the MySQL grammar according to the grammar rules. If the SQL statement is incorrect, prompt: You have an error in your SQL suntax

Optimizer: after analysis by the analyzer, SQL is legal, but before execution, the optimizer still needs to process it. The optimizer will determine which index is used and which connection is used. The role of the optimizer is to determine the most efficient execution plan.

Executor: in the execution phase, MySQL first determines whether it has permission to execute the statement. If it does not have permission, it returns an error without permission; if it has permission, it opens the table to continue execution. When you open a table, the executor uses the interface provided by the engine according to the target engine definition, and performs similar logic for indexed tables.

After understanding the execution process of the SQL statement, let's take a detailed analysis of how the above update T set c=c+1 where ID=2; is executed.

Parsing update T set c=c+1 where ID=2 with Update statement

The query cache associated with this table is invalidated when the update update operation is performed, so this statement empties all cached results on table T. Next, after parsing and lexical analysis, the parser knows that this is an update statement, the optimizer decides which index to use, and then the executor is responsible for the specific execution, first find this line, and then update it.

According to our usual way of thinking, is to find this record, change its value, and save it on OK. But let's look into the details, because it involves modifying the data, so it involves the log. The update operation involves two important logging modules. Redo log (redo log), bin log (archive log). These two logs in MySQL are also required.

Redo log (redo log)

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, the whole process IO cost, search cost is very high.

WAL (pre-written log) technology is used in MySQL. The full name of WAL is Write-Ahead Logging. Its key point is to write the log first and then write the disk.

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 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 idle.

InnoDB redo log is a fixed size, for example, can be configured as a group of 4 files, each file size is 1GB, then a total of 4GB operations can be recorded. Write from the beginning, and then go back to the beginning and cycle at the end.

After listening to the introduction to the redo log log, friends may ask: where is the redo log log stored? The database information is saved on disk and the redo log log is also saved on disk, so why write to redo log before writing to the database? What if the redo log log is full of data? Wait. Next, let's answer these questions.

Where is the redo log stored? ?

The InnoDB engine first writes the record to redo log, where the redo log is, it is also on disk, which is also a process of writing to disk, but unlike the update process, the update process is randomly IO on disk, which is time-consuming. Writing redo log is a sequential IO on disk. Be more efficient.

The redo log space is fixed, so will it run out?

First of all, don't worry that redo log will run out of space, because it is recycled. For example, the redo log log is configured as a set of 4 files, each 1G. The process it writes is as follows:

To sum up briefly: redo log log is a unique mechanism of Innodb storage engine, which can be used to deal with abnormal recovery. Crash-safe,redo can ensure that when mysql restarts abnormally, uncommitted transactions will be rolled back, and committed transactions will be safely stored.

Crash-safe: with redo log,InnoDB, you can ensure that records previously submitted will not be lost even if the database is restarted abnormally. This capability is called crash-safe.

Binlog (archive log)

Redo log is a log unique to the innoDB engine. Binlog is the log of the mysql server layer.

In fact, bin log logs appeared earlier than redo log, because in the beginning, MySQL did not have an InnoDB storage engine, and before 5.5. it was MyISAM. However, 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 in the form of plug-ins, 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.

Summary of redo log and bin log:

Redo log is to ensure the crash-safe capability of the innoDB engine, that is, when mysql restarts due to abnormal downtime, previously committed transactions can be guaranteed not to be lost. (because successfully committed transactions must be written to redo log and can be recovered from redo log)

Bin log is an archive log that appends each update operation to the log. In this way, when you need to restore the log to a certain point in time, it can be implemented according to full backup + bin log replay. If binlog is not enabled, the data can only be restored to the point in time of the full backup, not to any point in time. If the full backup is not done, the mysql is down and the disk is broken, it will be very embarrassing.

The difference between redo log and bin log?

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

Redo log is a physical log that records "what changes have been made on a data page"; bin log is a logical log that records the original logic of the statement, such as "add 1 to the c field of the ID=2 line."

Redo log is written in a loop, and the fixed space will be used up; binlog can be appended. "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.

The internal flow of the InnoDB engine part when executing this simple update statement

Update T set c=c+1 where ID=2

Manually open the transaction with begin, then execute the update statement, and then execute the commit statement. Which of the above update update processes were done after the update statement was executed and which were done after the commit statement was executed?

In fact, redo log has a redo log buffer,binlog and a binlog cache in memory. So in manually opened transactions, you execute sql statements that are actually written to redo log buffer and binlog cache (it is certainly impossible to write disk logs directly, one is poor performance and the other is it is impossible to roll back disk logs when rollback), and then when you execute commit, you should first change redo log's commit status prepare to commit status. Then flush the binlog cache to the binlog log (maybe just the flush to the operating system page cache, depending on your mysql configuration), and the redo log buffer to the redo log log (the refresh time is also configurable). If you roll back, all you have to do is erase the data in binlog cache and redo log buffer.

What happens when mysql goes down suddenly during the update process?

If the redolog has been written, is in the prepare state, and the binlog has not been written yet, then the transaction in the redolog will be rolled back directly after the downtime and restart.

If redolog is written and binlog is written, but redolog has not been updated to commit status, after downtime and restart, mysql will check whether the corresponding transaction is complete in binlog. If so, commit the transaction; if not, roll back the transaction. Redolog is in prepare state, and transactions are committed when binlog is fully started. Why is it designed like this? Mainly because binlog is written, it will be used by libraries recovered from the library or with this binlog, which is used for the sake of data consistency.)

Redo log and binlog are associated through the field xid.

At this point, I believe you have a deeper understanding of "how a update statement is executed in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.