How MySQL Executes an UPDATE: Inside InnoDB Pages, Buffer Pool, and Logs

Core Concepts

MySQL stores data on disk; the storage engine must first load required pages into memory. InnoDB uses fixed‑size pages (default 16 KB) as the smallest unit transferred between disk and memory.

InnoDB Pages

Each page is 16 KB by default; reads and writes always operate on whole pages.

Tablespaces

A tablespace groups many pages and is identified by a space ID. Each page also has a unique page number, allowing precise location of any row.

Example database test with three tables:

t_user_innodb (InnoDB)
t_user_myisam (MyISAM)
t_user_memory (MEMORY)

Filesystem representation:

t_user_innodb.frm   (metadata)
t_user_innodb.ibd   (InnoDB tablespace file)
t_user_myisam.MYD   (MyISAM data)
t_user_myisam.MYI   (MyISAM index)
t_user_memory.frm   (MEMORY metadata only)

drwxr-x--- 2 mysql mysql 4096 Jan 26 09:28 .
-rw-r----- 1 mysql mysql 8556 Jan 26 09:28 t_user_innodb.frm
-rw-r----- 1 mysql mysql 98304 Jan 26 09:28 t_user_innodb.ibd
-rw-r----- 1 mysql mysql 8556 Jan 26 09:27 t_user_memory.frm
-rw-r----- 1 mysql mysql    0 Jan 26 09:28 t_user_myisam.MYD
-rw-r----- 1 mysql mysql 1024 Jan 26 09:28 t_user_myisam.MYI
-rw-r----- 1 mysql mysql 8556 Jan 26 09:28 t_user_myisam.frm

Buffer Pool

The Buffer Pool is a contiguous memory region allocated at server start to cache InnoDB pages. It is divided into 16 KB pages. When a page is needed, InnoDB first checks the pool; if absent, the page is read from disk into the pool. Updates are written to the pool first; modified pages become “dirty” until a background thread flushes them to disk.

Redo Log

Purpose

If a dirty page has not been flushed and the server crashes, the changes would be lost. Writing every modified page to disk would be wasteful because pages are 16 KB. Instead, InnoDB records each page modification in a sequential redo log, enabling crash recovery with minimal I/O.

IO Characteristics

Redo‑log writes are sequential (fast), while flushing dirty pages is random (slow).

System Variables

Typical files: ib_logfile0 and ib_logfile1, each 48 MB by default. Their size and count are controlled by innodb_log_file_size, innodb_log_files_in_group, and innodb_log_group_home_dir.

mysql> show variables like 'innodb_log%';
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| innodb_log_buffer_size    | 16777216 |
| innodb_log_checksums      | ON       |
| innodb_log_compressed_pages | ON    |
| innodb_log_file_size      | 50331648 |
| innodb_log_files_in_group | 2        |
| innodb_log_group_home_dir | ./       |
| innodb_log_write_ahead_size | 8192 |
+---------------------------+----------+

Undo Log

The undo log stores the before‑image of rows, allowing transaction rollback and providing atomicity. It records logical inverse operations (e.g., INSERT → DELETE, UPDATE → old value) and works together with the redo log as the overall transaction log.

Update Statement Execution Flow (example)

update t_user_innodb set name='chanmufeng1994' where id=1;

Transaction starts; the page containing the target row is fetched from the Buffer Pool or loaded from disk.

The server executor modifies the row’s name value.

The old value is recorded in the undo log.

The new value is recorded in the redo log (state prepare).

The modified page remains in the Buffer Pool as a dirty page.

The server writes a binlog entry for the statement and flushes it.

The transaction is committed; InnoDB changes the redo‑log entry state to commit.

Binary Log (Binlog)

Binlog is a logical log at the MySQL server layer. It records all DDL/DML statements as events, enabling replication and point‑in‑time recovery, but it does not guarantee durability of the actual data pages.

Why Binlog Complements Redo Log

MyISAM lacked crash recovery; InnoDB introduced redo logs.

Binlog stores logical statements for replication; redo log stores physical page changes for crash recovery.

Two‑Phase Commit

Two‑phase commit ensures atomicity across redo log and binlog.

InnoDB writes changes to the Buffer Pool and records them in the redo log with state prepare.

The server writes the corresponding binlog entry and flushes it.

InnoDB changes the redo‑log state to commit, completing the transaction.

Final UPDATE Execution Summary

Client sends UPDATE to MySQL server; connection handling, parsing, and optimization occur.

Server requests the row (id=1) from InnoDB.

InnoDB fetches the page from the Buffer Pool or loads it from disk.

Server executor updates the name column.

InnoDB updates the page in memory.

InnoDB writes a redo‑log entry with state prepare.

Engine notifies executor that the change is ready for commit.

Server writes the corresponding binlog entry.

Server commits the transaction.

InnoDB changes the redo‑log entry state to commit.