MySQL most commonly used storage engine is InnoDB, we today with the help of an update statement execution, understand how InnoDB is processed, in-depth understanding of its architecture.

Suppose the update statement looks like this:

update user set name ='xxx' where id = 1;
Copy the code

After the SQL statement is sent to MySQL, it goes through the SQL interface, parser, optimizer, and executor to parse the SQL and generate an execution plan. Then the executor invokes the storage engine to execute the execution plan.

As shown below:

Figure 1 MySQL underlying architecture

Let’s take a look at the innoDB storage engine architecture following an UPDATE statement.

1. Innodb’s most important component: BufferPool

A very important component of innoDB’s storage engine is the BufferPool, which buffers a lot of data so that later data can be manipulated directly into memory without accessing disk.

Figure 2 innoDB’s significant component buffer pool

When innoDB executes the above update statement, it first checks whether the statement with id = 1 is in the buffer pool, if not, it needs to be loaded from disk to the buffer pool, and it also assigns an exclusive lock to the record.

Lock related knowledge points, behind will explain, here is not the key, will not expand.

2. Undo log files

Next, when preparing to update the data with id = 1, the original values of id = 1 and name are first written to the undo log file.

What is the purpose of this? Of course, it’s convenient to roll back.

If the transaction fails, it can be rolled back according to the undo log.

Figure 3 undo log files

After loading the data to be updated with ID = 1 into the buffer pool and writing the old value of the data to be updated to the undo log file, you can start updating the record.

When updating, update the buffer pool data first. After the update, the data in the buffer pool becomes: name=’ XXX ‘, while the data on the disk is still name=’zhangsan’. InnoDB data state now looks like this:

Figure 4 Updating buffer pool data

Redo log file

In this case, the buffer pool and disk data are inconsistent. What if MySQL is down?

When MySQL is down, data in the buffer pool must be lost.

At this point, a new component is introduced: redo logging.

The redo log is also a memory buffer for redo logs, which record the changes you made to the data.

For example, if id = 1 was changed to name, redo logs might look like this: id = 1, name = ‘XXX’.

Figure 5. Redo log

With redo log, MySQL restarts after downtime and restores updated data.

But what happens if the MySQL database goes down at this point?

The redo log and buffer logs are lost.

However, this does not matter, because your transaction to update the data is not committed, then MySQL is down, the transaction will fail to execute, the client will receive a database exception, MySQL restart after the disk data is the same as before.

So the data are consistent.

In addition, redo logging is a unique innoDB component.

Commit transaction

After the above steps are complete, it is time to commit the transaction, and the redo log is flushed to disk.

The flush policy can be configured by using innodb_flush_log_at_trx_commit.

This configuration has several options:

0, redo logs are not flushed to disk when a transaction is committed.

1. Default: Redo logs are flushed to disk when a transaction is committed. Once the transaction is committed, redo logs are flushed to disk.

2. When a transaction is committed, redo is flushed to the OS cache. The operating system flushs data from the OS cache to disk from time to time.

So when innodb_flush_log_at_trx_commit = 0 or 2, the redo log is always committed successfully and not written to disk, and the updated data in the buffer pool is lost. When MySQL restarts, the updated data cannot be restored based on redo and data inconsistency occurs.

Normally, we set innodb_flush_LOG_at_trx_COMMIT to 1.

Figure 6 redo log

5. Binlog log

In MySQL, a binlog is recorded when a transaction is committed. Binlog is the MySQL Server’s own log file.

The redo log is a physical redo log that records “a change was made to a log on a data page.”

A binlog is called an archive log, and it is a logical log, similar to redis’ AOF log.

When we commit something, we write the redo log to disk and the corresponding binlog to the disk file.

Figure 7. Binlog log

Similar to redo log, binlog has two flush policies. The corresponding configuration item is sync_binlog.

0, the default value. When committing a transaction, binlog will be flushed to the OS cache.

1. When the transaction is committed, the binlog will be written to disk.

So, when sync_binlog is set to 0, the binlog is at risk of being lost if the machine goes down. When set to 1, binlog logs will not be lost even if the machine is down.

When the binlog is written to disk, the final transaction commits, and the file name and location of the updated binlog are written to the redo log. Also write a commit flag in the redo log file.

At this point, a transaction commit is complete.

Figure 8. Binlog flush to disk

Finally, write a commit flag in the redo log to keep the redo log consistent with the binlog.

That is, innoDB determines whether a transaction has been successfully executed based on the COMMIT flag. If you look at steps 5, 6, and 7 in Figure 8, all three steps must be executed successfully before the transaction is committed. What if the machine goes down while performing one of these steps?

Because there is no COMMIT flag in the redo log, the transaction is considered unsuccessful and data inconsistencies do not occur.

6. Background threads flush memory data to disk

The transaction has been committed, the BufferPool has been updated, and there are redo logs and binlogs on disk, but the data on disk is still old.

So MySQL will have a background IO thread that will randomly flush the BufferPool data to disk at some point in time.

Figure 9 The complete flow of innoDB executing the update statement

It doesn’t matter if MySQL goes down before the background IO thread flusher the buffer pool data to disk, because after the machine restarts, it commits the changes made before the redo log replies.

7,

You can see what innoDB storage engine does through a data update process. InnoDB uses this complex architecture to ensure high performance and consistency of data updates by logging undo logs, updating BufferPool data, logging redo logs, and binlogs.

Original text: mp.weixin.qq.com/s/e-5plTcE4…