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

This article mainly introduces the example analysis of update statement in mysql, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

One, ready.

Create a test table

CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c` int(11) NOT NULL DEFAULT '0' COMMENT 'value', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT ='test table';

Insert three pieces of data

INSERT INTO `test` (`c`) VALUES (1), (2), (3);

2, testing

Add I want to add 1 to the c value of the first data, then

UPDATE `test` SET `c` = `c` + 1 WHERE `id` = 1;

According to our usual thinking, it is to find this record, change its value, and save it OK.

But let's dig into the details, because it involves modifying data, so it involves logs.

3 Order of operations

3.1 Find records: the executor first finds the engine and takes the row id=1. ID is the primary key, the engine directly with tree search to find this row. If the data page where id=1 is located is already in memory, it is returned directly to the executor; otherwise, it needs to read into memory from disk first, and then return;

3.2 The executor gets the row data returned by the engine, changes num to 2 to get a new row of data, and then calls the engine interface to write this new row of data;

3.3 The engine updates this new row of data to memory and records this update operation in the redo log, which is in the prepare state at this time;

3.4 The engine tells the executor that my execution is complete and that you can always call my interface to commit the transaction;

3.5 The executor generates a binlog of the operation and writes the binlog to disk.

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

binlog is MySQL's internal coordinator for implementing two-phase commit, assigning each transaction a transaction ID: XID

Phase I:

Start the transaction, redo log and undo log have recorded corresponding logs, and the transaction status is prepare at this time.

Phase II:

binlog After write and fsync are completed, the transaction must have been committed, otherwise the transaction is rolled back, commit is sent, undo information is cleared, redo is swiped, and the transaction status is set to completed.

4, two types of logs 4.1 redo log redo log

The redo log is usually a physical log that records the physical modifications of a data page, not how a row or rows have been modified, and it is used to restore the physical data page after submission (restore the data page, and only to the last committed position).

The general update will be as follows:

Query the original data directly and update it immediately; find a temporary notepad first, make a record, and update it when you are not busy/settlement.

The first approach is very pessimistic in the case of high concurrent IO. The second method is usually used.

There is also such a problem in MySQL. If every update operation needs to be written to the disk, and then the disk also needs to find the corresponding record, and then update, the IO cost and search cost of the whole process are very high. To solve this problem, MySQL designers used redo log to improve update efficiency.

The whole process of temporary notepad and raw data operation is also corresponding to WAL(pre-written logging) technology often mentioned in MySQL. The full name of WAL is Write-Ahead Logging. Its key point is to write logs first and then write disks. Specifically, when a record needs to be updated, the InnoDB engine will first write the record to redo log (notepad) and update the memory. At this time, the update is complete. At the same time, the InnoDB engine will update the operation records in this notebook to disk at the appropriate time, and this update is often done when the system is relatively idle.

Question: How to solve the problem when redo log is full?

InnoDB redo log is of fixed size. For example, it can be configured as a group of 4 files, each of which is 1GB in size, so this "notepad" can record 4GB operations in total. Start from the beginning, write to the end and then go back to the beginning again, as shown in the diagram below.

Write pos is the position of the current record, move backwards as you write, write to the end of file No. 3 and return to the beginning of file No. 0. Checkpoint is the current location to be erased, but also backward and cyclic, to update the record to the data file before erasing the record.

Between write pos and checkpoint is the empty space on notepad that can be used to record new operations. If the write pos catches up with the checkpoint, it means that the "powder board" is full. At this time, no new updates can be performed. We have to stop and erase some records first to push the checkpoint forward.

With redo log, InnoDB ensures that previously committed records are not lost even if the database restarts abnormally, a capability called crash-safe.

4.2 Archive log binlog

MySQL as a whole, in fact, there are two pieces: one is the Server layer, which mainly does things at the MySQL functional level; there is also an engine layer, responsible for specific matters related to storage. The redo log above is a log specific to the InnoDB engine, and the Server layer also has its own log, called binlog (archive log).

Why are there two journals?

Initially MySQL did not have an InnoDB engine. MySQL comes with MyISAM, but MyISAM does not have crash-safe capabilities, binlog logs can only be used for archiving. InnoDB is another company that introduced MySQL as a plug-in. Since binlog alone does not have crash-safe capabilities, InnoDB uses another logging system-redo log-to implement crash-safe capabilities.

There are three differences between these two types of logs.

redo log is specific to the InnoDB engine;binlog is a Server layer implementation of MySQL that can be used by all engines.

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

redo log is written cyclically, and the space will be used up;binlog is writable additionally. "Append write" means that when binlog files are written to a certain size, they switch to the next one without overwriting the previous log.

5 Two-stage submission

Split redo log writing into two steps: prepare and commit, which is called "two-phase commit."

5.1 Why must there be a "two-phase submission"? This is to make the logic consistent between the two logs.

Binlog records all logical operations and takes the form of "append writes." If your DBA promises to restore within half a month, then the backup system will save all binlogs for the last half month, and the system will make regular backups of the entire database. The term "regular" here depends on the importance of the system and can be either daily or weekly.

When you need to recover to a specified second, for example, one day at 2:00 p.m., you find that there is a wrong deletion table at 12:00 p.m., and you need to retrieve the data, you can do this:

First, find the most recent full backup, or if you're lucky, last night's backup, and restore it to the temporary repository.

Then, starting from the backup time point, the backup binlog is taken out in turn and re-placed at the time before the table was deleted by mistake at noon.

This way your temporary repository is the same as the online repository before the deletion, and then you can pull the table data from the temporary repository and restore it to the online repository as needed.

Having said that about the data recovery process, let's come back to why logs require a "two-phase commit." Here may wish to use counter-evidence to explain.

5.2 If you don't have a two-phase submission,

Since redo log and binlog are two separate logics, if you don't have a two-phase commit, you either write redo log first and then binlog, or vice versa.

Let's see what's wrong with both approaches.

Still using the previous update statement as an example, the current row ID=1, the value of field c is 1.

5.2.1 Write redo log first, then binlog

Suppose the MySQL process restarts abnormally when the redo log is finished but the binlog is not finished. Since we said earlier that after redo log is written, even if the system crashes, it can still recover the data, so the value of c in this line after recovery is 2. However, since binlog is crashed before binlog is finished, this statement is not recorded in binlog at this time. If you need to use this binlog to restore the temporary library, since the binlog of this statement is lost, this temporary library will be updated less this time. The value of c in this recovered line is 1, which is different from the value of the original library.

5.2 Write binlog first, then redo log

If there is a crash after binlog is written, since redo log has not been written yet, the transaction is invalid after crash recovery, so the value of c in this line is 1. But binlog has already recorded "change c from 1 to 2". Therefore, when binlog is used to recover later, the value of c in this row in the recovered temporary library is 2, which is different from the value of the original library.

As you can see, if you do not use two-phase commit, the state of the database may not be consistent with the state of the library recovered from its logs.

In fact, this process is not just needed to recover data after misoperation. When expansion is needed, that is, when more backup databases need to be built to increase the reading capacity of the system, the common practice now is to use full backup plus binlog application to achieve, this "inconsistency" will lead to inconsistency between the master and slave databases on your line.

Simply put, redo log and binlog can be used to represent the commit state of a transaction, and two-phase commit is to keep these two states logically consistent.

redo log is used to ensure crash-safe capability. innodb_flush_log_at_trx_commit When this parameter is set to 1, it means that the redo log of each transaction is directly persisted to disk. This parameter is recommended to be set to 1, so as to ensure that data is not lost after MySQL restarts abnormally.

sync_binlog When this parameter is set to 1, it means that the binlog of each transaction is persisted to disk. This parameter is also recommended to be set to 1, so as to ensure that binlog is not lost after MySQL is restarted abnormally.

Thank you for reading this article carefully. I hope that the article "Example Analysis of Update Statements in mysql" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you 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.