- Connect to the database.
- Clear the cache of the update table.
- The analyzer will know that this is an update statement through lexical and syntax parsing.
- The optimizer decides to use the ID index.
- The executor is responsible for specific execution, finds this row, and then updates it.
The update process also involves two important log modules: redo log (redo log) and binlog (archive log).
redo log
If every update operation needs to be written into the disk, and then the disk has to find the corresponding record, and then update it, the entire process of IO cost, and the search cost is very high, so the strategy of writing logs first, then writing disks is used (WAL technology-Write Ahead Logging). When a record needs to be updated, the InnoDB engine will first write the record to the redo log, and update the memory. At this time, the update is considered complete. At the same time, the InnoDB engine will update this operation record to the disk at an appropriate time, and this update is often done when the system is relatively idle.
InnoDB's redo log is of fixed size. For example, it can be configured as a group of 4 files, each file is 1GB, a total of 4GB.
Write pos is the current record position, moving back and forth, writing to the end of the 3rd file and then returning to the beginning of the 0th file. Checkpoint is the current position to be erased, it is also pushed back and cycled, and the record must be updated to the data file before erasing the record.
With the redo log, InnoDB can ensure that previously submitted records will not be lost even if the database crashes, which is called crash-safe.
binlog
InnoDB engine-specific logs, and the Server layer also has its own logs, called binlog (archive logs).
Differences:
- Redo log is specific to the InnoDB engine; binlog is implemented by the MySQL Server layer and can be used by all engines.
- Redo log is a physical log, recording "what modifications were made on a certain data page"; binlog is a logical log, recording the original logic of this statement, such as "add 1 to the c field of ID=2".
- Redo log is written in cycles, the space is fixed and will be used up; binlog can be appended. "Append write" means that the binlog file will switch to the next one after writing to a certain size, and it will not overwrite the previous log.
The writing of the redo log is divided into two steps: prepare and commit, which is the "two-phase commit".
Two-phase commit
The two-phase commit is to make the logic consistent between the two logs. Since the redo log and binlog are two independent logics, if you don't use two-phase commit, either write the redo log first and then write the binlog, or use the reverse order, problems will occur in a crash (take "update T set c=c+1 where ID=2;" as an example)
- Write the redo log first and then the binlog. Suppose that after the redo log is written, the MySQL process crashes before the binlog is written. After the redo log is written, the system can still recover the data even if it crashes, so after recovery, the value of c is 1. But because binlog crashed before it was written, there is no record of this statement in binlog at this time. Therefore, when backing up logs later, there is no this statement in the stored binlog. Then you will find that if you need to use this binlog to restore the temporary library, due to the loss of the binlog of this statement, this temporary library will miss this update, and the recovered value of c is 0, which is different from the original library value.
- Write binlog first and then write redo log. If it crashes after binlog is written, since redo log has not been written, the transaction is invalid after crash recovery, so the value of c is 0. But binlog has recorded the log of "changing c from 0 to 1". Therefore, when using binlog to recover later, an extra transaction will come out, and the recovered value of c is 1, which is different from the original library value.
If the "two-phase commit" is not used, the state of the database may not be consistent with the state of the library recovered from its log.
