Understanding MySQL Architecture: Storage Engine, Buffer Pool, Logs, and Indexes

What is MySQL?

MySQL is a relational database that acts as a middle layer between applications and data, providing core CRUD (Create, Read, Update, Delete) operations through its storage engines.

InnoDB Storage Engine

The default InnoDB engine stores rows and indexes in .ibd files on disk, while keeping frequently accessed data in memory structures such as the buffer pool, adaptive hash index, and log buffers.

Data Pages

Data is organized into 16 KB pages, similar to Excel sheets, allowing the system to read or write only the required pages instead of the whole file.

Indexes

Pages are linked via a B+Tree index built on primary keys; auxiliary indexes can be created on other columns to speed up lookups.

Buffer Pool

The buffer pool caches data pages and index pages in process memory, reducing disk I/O. It also supports custom caching strategies and locking.

Adaptive Hash Index

Hot pages are added to an in‑memory hash table, providing O(1) lookup for frequently accessed rows.

Change Buffer

Write operations to secondary indexes are first collected in the change buffer and applied later when the corresponding pages are loaded into the buffer pool, further reducing I/O.

Undo Log

Before a row is modified, the original version is recorded in an undo log stored in memory and later flushed to disk, enabling transaction rollback.

Redo Log

All modifications are written to a redo‑log buffer and periodically flushed to a redo‑log file. This Write‑Ahead Logging (WAL) guarantees that committed transactions survive crashes.

Binlog

The server layer records every data‑changing statement in a binary log (binlog). Unlike the circular redo log, the binlog preserves the full history of changes, supporting point‑in‑time recovery and replication.

Server Layer

The server parses SQL, runs the optimizer to choose indexes, generates an execution plan, and then calls InnoDB’s API functions (e.g., write_row() , update_row() , create() , drop() ).

Master‑Slave Replication

Because the binlog contains all changes, a replica can replay the log to stay synchronized with the master, allowing read traffic to be offloaded to slaves.

Query and Update Flow

Read: the buffer pool is checked first; if the needed page is missing, it is loaded from disk and optionally added to the adaptive hash index.

Write: data is written to the buffer pool, an undo log entry is created, the change is recorded in the redo‑log buffer, and secondary‑index updates are staged in the change buffer before being flushed.

Summary

MySQL consists of a server layer and interchangeable storage engines (InnoDB is the most common).

InnoDB splits its workload between memory (buffer pool, adaptive hash, log buffers) and disk (data files, ibd, undo/redo logs).

The server’s binlog records all changes for recovery and replication.