How is an update SQL executed in mysql

redo log

If every update operation needs to be written to the disk, and the disk also needs to find the corresponding record and then update it, the IO cost and search cost are very high in the whole process. To solve this problem, the designers of MySQL use a technique called WAL, which stands for write-Ahead Logging. The key point is to Write to the log first and then to the disk. When a record needs to be updated, the InnoDB engine writes the record to the redo log and updates the memory. The InnoDB engine updates this 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, which records 4GB of operations. Start at the beginning, and then cycle back to the beginning at the end.

crash-safe

binlog

MySQL as a whole, in fact, there are two: one is the Server layer, which mainly does things at the functional level of MySQL; There is also the engine layer, which takes care of the details of storage. The redo log is InnoDB’s engine specific log. The Server layer has its own log called binlog.

Why are there two logs?

Because there was no InnoDB engine in MySQL at first. The MySQL engine is MyISAM, but MyISAM does not have crash-safe capability. Binlog can only be used for archiving. InnoDB uses a redo log to implement crash-safe functionality, since it does not rely solely on binlogs.

There are three differences between the two types of logs.

  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. A binlog is a 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 switch to the next one after being written to a certain size without overwriting the previous log.

Combine the above two logs to understand the execution of an UPDATE.

  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 ID=2, the data page is already in memory

    , is directly returned to the actuator; 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 is ready to commit.

  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.

Here I show the flow chart of the execution of the update statement. The light boxes indicate that it is executed inside InnoDB and the dark boxes indicate that it is executed inside the executor.

How to restore the database to any second in half a month?

The binlog records all logical operations in the form of “appending”. If your DBA promises to restore the database in half a month, all binlogs from the last half a month will be stored in the backup system and the entire database will be backed up periodically.

  • First, find the most recent full backup, or if you’re lucky, last night’s backup, and restore from that to the temporary repository.

  • Then, from the backup point in time, take out the backup binlog in turn, and resend to the time before the table was deleted by mistake at noon.

Why do logs need “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. And then you’ll notice that if you need to restore the temporary library with this binlog, because the binlog of this statement is missing, the temporary library is missing and this time it’s even newer, and the restored line of C has 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.