A logical diagram of MySQL and InnoDB. The best understanding is a little hands-on, observation, recording, thinking.

Change the MySQL file every 0.1 seconds and flash to see the order of MySQL processing when SQL executes.

Watch-n 0.1-d stat/var/lib/mysql/ib_logfile0/var/lib/mysql/ib_logfile1/var/lib/mysql/ibdata1

I understand the order of execution: (also missing insert buffer merge) very coarse frame understanding, but tossing for many days.

The data file of the hard disk MYTABLE.IBD has the record of mytable this table id=1, name=123. (Innodb_per_file set to ON)

When update MYTABLE SET name= ' ABC ' WHERE id=1 This statement executes, the system generates an LSN of 1

INNODB Read THREAD reads this record and the dictionary from the hard disk into the data page of the memory, and writes it to an in-memory block of undo buffer. and locking the record (if there is an index is a row-level lock, otherwise it is a table lock)

INNODB Write THREAD writes the undo log of this record to the rollback segment of the Undo table space (by default, the Ibdata1 file).

INNODB Update the records in the data page, turn the Dirty page, and then write the Lsn=1 log in Redolog BUFFER;

To commit, either explicitly or implicitly, refreshes the LSN into the redo log file, and then releases the lock, according to the Innodb_flush_log_at_trx_commit-specified method. The purge thread can then clean up the undolog. Purge is by transaction number not by log.

INNODB MASTER THREAD Timing The dirty page brush back into the data file MYTABLE.IBD. The process InnoDB first copy the dirty page data into a double write buffer in the memory pool, and then call System Fsync () to refresh the system cache to confirm that it was actually written. The Dirty page is then released after the Dirty page is flushd to the corresponding data file MYTABLE.IDB.

INNODB MASTER THREAD will timing the log LSN that has been written back to the data file as checkpoint written into Redolog.

More intuitive MySQL overall logical organization diagram:

MySQL SQL execution process is a great help to understand MySQL execution plan

Brush disk mechanism when ' innodb_flush_log_at_trx_commit = X ' x=0,1,2 each value

MySQL memory allocation is very useful for understanding memory allocation

INNODB the memory to interact with the hard disk.

Watch-n 0.1-d stat/var/lib/mysql/ib_logfile0/var/lib/mysql/ib_logfile1/var/lib/mysql/ibdata1

A logical diagram of MySQL and InnoDB. The best understanding is a little hands-on, observation, recording, thinking.