Core Execution Pipeline

The execution of an UPDATE statement follows a similar pipeline to SELECT queries, passing through the parser and optimizer before reaching the executor. The key difference lies in the post-retrieval operations performed on the matched data.

Buffer Pool Mechanics

InnoDB stores data on disk and operates with pages as the minimal logical unit (both data and index pages). To avoid slow disk I/O operations, InnoDB employs a buffer pool technique - caching frequently accessed pages in memory. When subsequent requests target the same pages, InnoDB first checks the buffer pool, serving data from memory if available, thus eliminating disk access.

During modifications, changes are applied to the cached pages first. When cached and disk data diverge, these become "dirty pages." InnoDB maintains background threads that periodically flush multiple dirty pages to disk in batch operations, a process known as checkpointing.

InnoDB Memory and Disk Architecture

Memory Components

InnoDB's memory architecture consists of four main areas: Buffer Pool, Change Buffer, Adaptive Hash Index, and Redo Log Buffer.

Buffer Pool

The Buffer Pool caches page information including data pages and index pages. The default size is 128MB but can be adjusted. You can monitor its status using:

SHOW ENGINE INNODB STATUS;

And view configuration parameters with:

SHOW VARIABLES LIKE 'innodb_buffer_pool%';

When the Buffer Pool reaches capacity, InnoDB uses an improved LRU algorithm, splitting pages into young and old sublists to identify hot data for retention.

Change Buffer

For non-unique indexes where duplicate checking isn't required, InnoDB optimizes write operations (INSERT, DELETE, UPDATE) by buffering changes in memory instead of immediate loading affected pages from disk. This area is called the Change Buffer. The process of applying these buffered changes to actual data pages is called "merge," which occurs during page access, background thread operations, database shutdown, or when the redo log fills up.

The Change Buffer size is configurable as a percentage of the Buffer Pool:

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

The default setting is 25% of the Buffer Pool size.

Redo Log Buffer

To insure data durability during crashes, InnoDB implements Write-Ahead Logging (WAL). All page modifications are first recorded in a redo log before being written to disk. The redo log consists of two files (ib_logfile0 and ib_logfile1), typically 48MB each.

Log configuration can be viewed with:

SHOW VARIABLES LIKE 'innodb_log%';

The redo log buffer caches log entries before writing to disk, defaulting to 16MB. This reduces disk I/O by batching log writes.

Disk Structure

InnoDB's disk architecture is organized into various tablespaces, which represent the highest level of InnoDB's logical structure.

System Tablespace

The default system tablespace (ibdata1) contains the InnoDB data dictionary, doublewrite buffer, Change Buffer, and Undo Logs. If file-per-table is disabled, it also stores user-created tables and indexes.

The doublewrite buffer addresses partial page writes - when a 16KB InnoDB page requires four 4KB OS page writes, a crash mid-write could corrupt data. Before applying redo logs during recovery, InnoDB uses a clean page copy from the doublewrite buffer to restore corrupted pages.

File-Per-Table Tablespaces

When enabled (default), each table gets its own tablespace (.ibd file). This can be checked with:

SHOW VARIABLES LIKE 'innodb_file_per_table';

These files store table data and indexes separately from the system tablespace.

General Tablespaces

Shared tablespaces that can store tables from different databases. Creation syntax:

CREATE TABLESPACE custom_space ADD DATAFILE '/var/lib/mysql/custom_space.ibd' FILE_BLOCK_SIZE=16K ENGINE=InnoDB;

Undo Tablespaces

Undo logs record pre-modification data states for transaction rolback. They implement atomicity by logically restoring data to pre-transaction states. While default storage is in the system tablespace, separate undo tablespaces can be created.

Update Execution Flow

Consider an UPDATE operation:

UPDATE employees SET employee_name = 'Robert' WHERE employee_id = 101;

Execution steps:

1. Transaction begins, data is retrieved from memory or disk
2. Executor modifies the employee_name field to 'Robert'
3. Original value is recorded in undo log
4. New value is recorded in redo log
5. Buffer Pool page is updated in memory
6. Transaction is committed

Binary Log (Binlog)

The binary log records all DDL and DML statements as events, enabling point-in-time recovery and replication. Unlike the fixed-size redo log, binlog files can grow indefinitely.

Complete Update Flow

The full update process integrates both storage engine and server layers:

1. Data is located, utilizing cache if available
2. Storage engine updates memory buffer and records redo log in prepare state
3. Executor records the operation in binlog
4. Storage engine commits the redo log
5. Operation completes

Key aspects of this flow: the two-phase commit of redo logs, separation of storage engine and server logs, and the critical sequence of logging operations before data modification.