What Happens Inside MySQL When You Run an UPDATE? A Deep Dive

Hello, I am Sanyou. Today we discuss the execution flow of a SQL UPDATE statement.

1. Some concepts to know

For any MySQL storage engine, data is stored on disk and must be loaded into memory before it can be operated on.

Loading data page‑by‑page would be too slow, so both the OS and MySQL use pre‑reading based on the principle of locality.

Spatial locality: data that is close in address is likely to be accessed soon, so it is prefetched into memory.

1.1 InnoDB page

InnoDB divides data into pages, the smallest unit of interaction between disk and memory. The default page size is 16 KB, meaning at least 16 KB is read from disk or flushed to disk at a time.

All data, indexes, metadata, and system data are stored as pages.

1.2 Tablespace

MySQL introduces the concept of a tablespace to manage pages. A tablespace can be split into many InnoDB pages, and each table has a unique tablespace ID and each page has a page number.

1.2.1 Data table representation in the file system

In a test database we create three tables:

<code>test
├─ t_user_innodb.frm
├─ t_user_innodb.ibd
├─ t_user_myisam.frm
├─ t_user_myisam.MYD
├─ t_user_myisam.MYI
├─ t_user_memory.frm
└─ db.opt</code>

1.2.2 How InnoDB stores table data

InnoDB creates two files for the table t_user_innodb :

t_user_innodb.frm – describes the table structure

t_user_innodb.ibd – the tablespace file that holds the actual data pages

1.2.3 How MyISAM stores table data

MyISAM stores data and indexes directly in the database directory without a tablespace concept. The table t_user_myisam corresponds to three files:

t_user_myisam.MYD – data file

t_user_myisam.MYI – index file

t_user_myisam.frm – table definition

1.2.4 How MEMORY stores table data

MEMORY engine only creates a .frm file ( t_user_memory.frm ) to describe the table structure; data resides in memory.

2. Buffer Pool

When InnoDB needs a page, it loads the whole page into memory (the Buffer Pool). The Buffer Pool is a contiguous memory region allocated at server startup and is divided into buffer pages of the same size as InnoDB pages (16 KB).

If a page is already in the Buffer Pool, it is read directly; otherwise it is loaded from disk. Modified pages become “dirty”. A background thread periodically flushes dirty pages to disk (the “flush dirty” operation).

3. Redo Log

3.1 Why redo log is needed

If the server crashes before dirty pages are flushed, data would be lost. Writing every modified page to disk before commit would be wasteful because a page is 16 KB. Instead InnoDB writes the changes to a redo log, which is sequential I/O and much faster.

The redo log provides durability (the “D” in ACID).

“re” means “again”; redo means MySQL re‑applies the logged changes during recovery.

3.2 Disk addressing

3.2.1 Random I/O

When data is scattered across different sectors, the disk arm must seek each sector, which is slow.

3.2.2 Sequential I/O

If data is contiguous, the disk can read it in one sweep, which is much faster. Redo logging uses sequential I/O, while flushing dirty pages is random I/O.

3.3 Redo log system variables

Redo logs are stored as ib_logfile0 and ib_logfile1 in the data directory, each 48 MB by default.

<code>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     |
+-----------------------------+----------+</code>

innodb_log_file_size : size of each redo log file, default 48 MB

innodb_log_files_in_group : number of redo log files, default 2

innodb_log_group_home_dir : directory of redo logs, defaults to the data directory

4. Undo Log

Undo log records the state of data before a transaction modifies it, enabling rollback. It stores logical undo operations (e.g., INSERT → DELETE, UPDATE → old value).

Undo log together with redo log constitute the transaction log.

5. SQL UPDATE Execution Summary – Initial Version

Example update:

<code>update t_user_innodb set name='chanmufeng1994' where id = 1;</code>

Transaction starts, the relevant page is fetched from Buffer Pool or disk.

Server executor modifies the row’s name value.

Old value is recorded in the undo log.

New value is recorded in the redo log.

Modified page is written back to Buffer Pool.

Transaction commits.

6. Binlog

The server layer writes a binary log (binlog) that records all DDL and DML statements as logical events. Binlog is used for replication and point‑in‑time recovery, while redo log provides crash‑recovery at the page level.

6.1 Why both redo log and binlog are needed

MySQL originally used MyISAM, which lacked crash recovery; InnoDB introduced redo log.

Binlog records logical operations for replication but does not guarantee durability of the actual data pages.

Redo log records physical page changes; binlog records logical statements.

6.2 Binlog uses

6.2.1 Master‑slave replication

Master writes all write operations to binlog; slaves read the binlog and replay the changes.

6.2.2 Data recovery

Combine a recent full backup with binlog events after the backup to restore to a specific point in time.

6.3 Two‑Phase Commit

Two‑phase commit is a general technique for maintaining logical consistency across systems, not unique to MySQL.

The redo log write is split into two states: prepare and commit .

InnoDB writes changes to redo log in prepare state.

Server writes the corresponding binlog entry and flushes it.

InnoDB changes the redo log state to commit and the transaction is committed.

7. SQL UPDATE Execution Summary – Final Version

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

Server requests the row with id=1 from InnoDB.

Storage engine fetches the row from Buffer Pool or loads it from disk.

Server executor modifies the name field.

Storage engine updates the page in memory.

Storage engine writes a redo log entry with state prepare .

Engine notifies executor that the change is ready for commit.

Server writes a binlog entry.

Server commits the transaction.

Storage engine changes the redo log entry state to commit .