This is the third day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021

Today let’s talk about the execution flow of an update statement. The update process also involves two important log modules, which we will discuss today: redo logs and binlogs.

  • redo log

When we execute an update statement, MySQL doesn’t immediately write the update to disk, find the corresponding record, modify it, and update it. Doing so consumes too much IO. WAL stands for write-Ahead Logging. The key point of WAL is to Write logs first and then disks. When a log needs to be updated, InnoDB writes the log to the redo log. And update memory, at which point the update is complete. Meanwhile, the InnoDB engine updates this operation record to disk when appropriate, and this update is usually done when the system is idle.

InnoDB’s redo log is fixed in size. For example, it can be configured as a group of 4 1GB redo logs. This area records 4GB of operations. Start at the beginning, and then cycle back to the beginning at the end. This area is controlled by two markers. One is write pos, which is the position of the current record that moves backwards as you write, returning to the beginning of file 0 at the end of file 3. Checkpoint is the current point of erasure, which is also iterated backwards, updating the record to the data file before erasure. Checkpoint is always chasing Write POS. When checkpoint catches Up with Write POS, it is running out of space and can’t perform new updates. It has to stop and erase some records and update the records to the data file so that checkpoint can push forward.

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.

  • binlog

Difference between binlog and redo log

  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.

When we execute

update T set c=c+1 where ID=2;
Copy the code

What is the internal process of InnoDB engine?

  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 new row of data to memory and logs the update toredo logInside, at this pointredo logIs in the prepare state. The executor is then told that the execution is complete and the transaction can be committed at any time.
  4. The executor that generates the operationbinlogAnd put thebinlogWrite to disk.
  5. The executor calls the commit transaction interface of the engine, and the engine writes theredo logChange the status to Commit, and the update is complete.

Binlog is the log generated during mysql operation. If we later need to configure the master/slave database, if we need to synchronize the contents of the master database from the database, we can do this through binlog.