Inside MySQL: How Buffer Pools, Indexes, and Logs Power Modern Databases

What is MySQL

MySQL is a relational database that sits between applications and data, providing Create, Read, Update, Delete (CRUD) operations via tables that resemble Excel sheets.

Data Pages

MySQL stores tables on disk as .ibd files. Large tables are split into 16 KB data pages so that only a few pages need to be read for partial queries.

Indexes

Each data page gets a page number and each row a primary‑key value. The smallest primary‑key on a page and its page number are stored in a higher‑level page, forming a B+‑tree that speeds look‑ups. Primary‑key indexes and secondary (auxiliary) indexes work on the same principle.

Buffer Pool

To avoid frequent disk reads, MySQL adds an in‑process cache called the Buffer Pool, which holds recently used 16 KB pages and index pages. Reads first check the Buffer Pool; if a page is missing, it is fetched from disk.

Adaptive Hash Index

For hot pages MySQL builds an O(1) hash table where the key is the frequently accessed value (e.g., a username) and the value is the address of the data page. This is called the Adaptive Hash Index.

Example: for rows where the name 'xiaobai' is queried often, the hash key is xiaobai and the value is the address of the page containing those rows.

Change Buffer

When secondary indexes are updated, MySQL temporarily stores the modifications in memory. Once the corresponding index page is loaded into the Buffer Pool, the pending changes are applied, reducing random I/O.

Undo Log

Undo Log records the original version of each row before it is modified, enabling transaction rollback. Undo records are kept in special pages inside the Buffer Pool and are periodically flushed to an undo‑log file on disk.

Redo Log

Redo Log buffers all changes of a transaction in memory. At commit, the buffer is flushed sequentially to a redo‑log file. Sequential writes are far faster than random writes, and the redo log ensures that committed transactions survive a crash.

InnoDB Storage Engine

InnoDB combines the memory components (adaptive hash, Buffer Pool, redo‑log buffer) with on‑disk files (.ibd, undo log, redo log) and exposes functions such as write_row(), update_row(), create(), drop() that the server layer calls.

Server Layer

The server layer parses SQL, performs syntax checking, chooses indexes via the optimizer, and executes plans by invoking InnoDB’s API. It also manages client connections and writes every change to the binary log (binlog).

Binlog and Replication

Binlog records all statement‑level changes on disk, preserving the full history unlike the circular redo log. Because it sits in the server layer, it works regardless of the storage engine and enables point‑in‑time recovery and master‑slave replication.

Query and Update Flow

Read: Client → Server (parser → optimizer → executor) → InnoDB → Buffer Pool (or disk) → optional Adaptive Hash Index → return data.

Write: Data written to Buffer Pool → Undo Log → Redo Log Buffer (flushed to redo‑log file) → Change Buffer for secondary indexes → binlog for replication.