The flow of an update operation

update t N=N+1 where id=2
Copy the code
  1. The executor first finds the engine and fetches the line ID=2. ID is the primary key, and the engine uses the tree search directly to find this row. If the row ID=2 is already in memory, it is returned directly to the executor. Otherwise, you need to read into the memory from disk and then return.
  2. The executor takes the row given by the engine, adds 1 to it, for example, N+1 to get a new row of data, and then calls the engine interface to write the new row of data.
  3. The engine updates the data to memory and logs the update to the redo log. The redo log is prepared. The executor is then told that the execution is complete and the transaction can be committed at any time.
  4. The executor generates a binlog of this operation and writes the binlog to disk.
  5. The executor calls the commit transaction interface of the engine, and the engine changes the redo log to the commit state.

redolog

Write-ahead Logging is the key to WAL technology, which stands for WRITE-Ahead Logging

When a record needs to be updated, the InnoDB engine writes the record to the redo log and updates the memory. Meanwhile, the InnoDB engine updates this operation record to disk when appropriate, and this update is usually done when the system is idle

With redo log, InnoDB guarantees that all previously committed records will not be lost in the event of an unexpected database restart. This capability is called crash-safe

Redolog – binlog difference

  1. Redo logs are unique to InnoDB; Binlog is implemented in the Server layer of MySQL and is available to all engines.
  2. A redo log is a physical log of what was changed on a data page. The binlog is the logical log that records the original logic of the statement, such as “add 1 to the C field on the line ID=2”.
  3. Redo logs are written in a redo log cycle. Binlog can be appended. “Appending” means that the binlog file will be switched to the next one after being written to a certain size without overwriting the previous log.

undo log

Purpose: Provides rollback and multiple line versioning (MVCC).

During data modification, not only redo but also the corresponding Undo is recorded, which can be used to roll back the transaction if it fails or is rolled back for some reason.

The undo log is a logical log, unlike the redo log. You can assume that when a record is deleted, the Undo log records a corresponding INSERT record, and vice versa, when a record is updated, it records a corresponding reverse update record

Two-phase commit

  1. Write redo log and then binlog. MySQL restarts unexpectedly when the redo log is complete. Because the system can recover data from a redo log crash, the value of c is 1. But because the binlog didn’t finish writing the crash, the binlog didn’t record the statement. Therefore, when the log is backed up later, the saved binlog does not contain this statement. Then you will notice that if you need to restore the temporary library with this binlog, the temporary library will miss the update because the binlog of this statement is lost, and the restored line of C will have a value of 0, which is different from the original library.
  2. Write binlog and redo log. If there is a crash after the binlog has written, the transaction is invalid after the crash because the redo log has not been written, so the value of c in this row is 0. But binlog already records “change c from 0 to 1”. The value of c in the restored row is 1, which is different from that in the original library.

Commonly used Settings

innodb_flush_log_at_trx_commit

  1. When set to 1, a transaction writes logs from the log buffer to the OS buffer and calls fsync() to the log file on disk for each commit. This way, no data is lost even if the system crashes, but IO performance is poor because every commit is written to disk.
  2. When set to 0, instead of writing logs from the log buffer to the OS buffer during transaction commits, the OS buffer is written every second and fsync() is called to log file on disk. This means that 0 is written to disk (approximately) every second, and when the system crashes, 1 second of data is lost.
  3. When set to 2, each commit is written only to the OS buffer, followed by a call to fsync() every second to write the logs from the OS buffer to the log file on disk.

Sync_binlog =1 Refreshes binlog logs to disk every time