Mysql > select * from Users where id=1; update Users where id=1; Mysql then creates a threaded process that accesses the data through the SQL interface, interpreter, optimizer, executor, and finally executor calling the storage engine. For those who don’t know, see juejin.cn/post/692046… Let’s discuss the internal structure of InnoDB.
Buffer Pool
InnoDB’s Buffer Pool is the most important memory component of the storage engine (InnoDB). The purpose of InnoDB is to improve mysql’s read performance. If the data we need to modify exists in the Buffer Pool, we will directly modify it; if not, we will load it from disk.
undo-Log
Now we start to modify the data, but the original name= “Wang Pockmarked” is now changed to “Zhang SAN”. We have not considered the problem of cancellation before the modification is completed, so we need to save the name= “Wang Pockmarked” in a place first. The Log file that is prepared to undo (rollback) changes is undo-log. Now I pre-write the old value to undo-log and add an exclusive lock to load the data from the disk file if it is not in the Buffer Pool. The modified data has been updated in BufferPool to name= “John”.
redo-Log Buffer
The data has been modified successfully. We thought about a question: the data in the cache is actually dirty data, because the data in the disk file is not updated to “Zhang SAN”. If the database suddenly goes down, will the data be lost? The changes we made in buffer-pool are stored in redo-log Buffer to avoid data loss due to sudden outages. But redore-log Buffer is also an in-memory component that goes down and it can’t save itself. We don’t have to worry because our data changed but we didn’t commit the transaction, so it doesn’t matter that the data was lost before the transaction committed. We write records from the redo-log buffer to the redo-log file when an early transaction commits. Of course, there is a policy for this write, by setting InnoDB_flush_log_at_trx_commit to 1.
binLog
In fact, redolog is a physical redolog that records what was done to that data page and what was changed. It belongs to the innoDB storage engine itself. A binlog is a logical log that records whether you add a record to that table or delete a record from that table like delete from User where ID =2. Binlog itself belongs to Mysql Server. When we commit a transaction, the log will be written to the binlog at the same time. In the case of the binlog, there is also a policy configuration. The sync_binlog parameter controls the write policy of the binlog. Its default value of 0 means that the binlog data is written to the OS Cache first rather than immediately to disk when a transaction is committed. An outage at this time will result in the loss of binlog data. If sync_binlog is set to 1, the logs can be written to disk for persistence immediately upon transaction submission without data loss. A commit flag is written to the redolog file and a commit flag is written to the redolog file.
Why do I need the Commit tag
Redolog is used to make sure that redolog is consistent with binlog. The transaction failed if the commit flag was not finally written. Finally, the background I/O thread randomly writes the changed data from the buffer pool to the data file.
