Most explanations of MySQL stop at "it has tables, and InnoDB is the storage engine." That's true and mostly useless. This is the version that goes all the way down — from the moment a query leaves your application to the moment the bytes it touched are durable on disk, survive a crash, and show up correctly on a replica.

table of contents

• -
  the layers
• -
  what a query actually goes through
• -
  the buffer pool
• -
  pages, extents, and tablespaces
• -
  the clustered index: why every InnoDB table is a B+Tree
• -
  secondary indexes and the extra hop
• -
  the redo log: why commit doesn't mean "on disk"
• -
  the undo log: how rollback and time-travel reads work
• -
  the binary log and the two-phase commit
• -
  MVCC: how readers never block writers
• -
  isolation levels, honestly
• -
  locking: row locks, gap locks, next-key locks
• -
  deadlocks
• -
  the change buffer and the adaptive hash index
• -
  the doublewrite buffer
• -
  crash recovery, end to end
• -
  replication
• -
  storage engines compared
• -
  putting it all together

the layers

MySQL is really two programs stacked on top of each other, connected by a narrow, deliberate interface.

flowchart TD
    A[Client] --> B[Connection Layer]
    B --> C[Parser]
    C --> D[Optimizer]
    D --> E[Executor]
    E --> F[Storage Engine API]
    F --> G[InnoDB]
    G --> H[Buffer Pool]
    H --> I[("Disk: tablespaces, redo log, undo log")]
    E --> J[Binary Log]

Everything above the storage engine API — connection handling, SQL parsing, the optimizer, the executor — has no idea whether the table underneath is InnoDB, MyISAM, or Memory. That boundary is MySQL's oldest and most consequential design decision: it's what makes "pluggable storage engines" possible at all. The optimizer asks the storage engine layer things like "give me the row after this one" or "how many rows roughly match this range," and each engine answers in whatever way its own data structures allow.

This piece focuses on InnoDB, because it's been the default engine since MySQL 5.5 and almost everything running in production today uses it.

what a query actually goes through

Take a single UPDATE users SET last_login = NOW() WHERE id = 42; and follow it.

1. Connection layer hands the query to a thread (or a thread-pool worker) already assigned to that client connection.
2. Parser turns the SQL text into a parse tree, then a more structured internal representation. This stage also catches syntax errors — before anything about users or id is even looked up.
3. Optimizer decides how to satisfy the query. For this UPDATE, it checks whether id has a usable index (it's the primary key, so yes), and decides to use a primary key point lookup rather than a full table scan.
4. Executor carries out the optimizer's plan by calling into the storage engine API — "fetch the row where the clustered index key equals 42."
5. InnoDB does the actual work: locates the page holding that row (from the buffer pool if it's cached, from disk if not), takes a row lock, modifies the row in memory, writes a redo log record, and marks the transaction for commit.
6. Once committed, the change is also written to the binary log, for replication and point-in-time recovery.

The important thing to notice: steps 1–4 would look identical if the table were MyISAM instead of InnoDB. Everything engine-specific happens in step 5 onward — which is why the rest of this article lives almost entirely inside that box.

the buffer pool

Disk is slow. Memory is fast. The buffer pool is InnoDB's answer to that gap: a large, configurable chunk of RAM (innodb_buffer_pool_size) that caches table and index pages so that most reads and writes never have to touch disk synchronously.

Naively, you'd think "cache the most recently used pages" — plain LRU. InnoDB does something more deliberate, because plain LRU has an obvious failure mode: one large sequential scan (a backup job, a bad SELECT * with no WHERE) would blow through the entire buffer pool and evict every genuinely hot page.

flowchart LR
    NewPage[Page read from disk] --> Old["old sublist (~37%)"]
    Old -->|"accessed again after a short delay"| Young["young sublist (~63%)"]
    Young -->|"least recently used, evicted first"| Evict[Evicted]
    Old -->|"never touched again"| Evict

The LRU list is split into two sublists: a smaller old sublist and a larger young sublist (the split ratio is innodb_old_blocks_pct, default 37%). A page that's read from disk always enters at the head of the old sublist, not the young one. It only gets promoted to young if it's accessed again after a short delay (innodb_old_blocks_time). A one-off scan reads thousands of pages that enter old, sit there briefly, and get evicted without ever displacing the pages that are actually hot — because a single sequential read touches each page exactly once, it never earns the second access needed for promotion.

The buffer pool also tracks dirty pages — pages modified in memory but not yet written back to their tablespace file. Those get flushed to disk lazily, either by a background thread or when the redo log needs to advance its checkpoint (more on that shortly). This is exactly what makes reads and writes fast: a write only has to modify memory and append to a sequential log file to be considered durable, not rewrite a random page on disk.

pages, extents, and tablespaces

InnoDB never reads or writes single rows to disk — it always operates on fixed-size pages, 16KB by default (innodb_page_size). A row lookup that needs one row still pulls in the entire 16KB page that row lives in.

Pages are grouped into extents (1MB, 64 consecutive pages), and extents are grouped into tablespaces — the actual .ibd files on disk (or one shared ibdata1 file, depending on configuration). This grouping exists so InnoDB can allocate space in efficient chunks instead of scattering individual pages, keeping related data physically close together on disk.

the clustered index: why every InnoDB table is a B+Tree

This is the single most consequential design decision in InnoDB, and it's easy to miss because it's invisible in ordinary use: every InnoDB table is stored as a B+Tree ordered by its primary key. There's no separate "row storage" that the primary key merely points to — the primary key is the storage structure. This is called a clustered index.

flowchart TD
    Root[Root page] --> N1[Internal node]
    Root --> N2[Internal node]
    N1 --> L1["Leaf: rows 1–100 (full row data)"]
    N1 --> L2["Leaf: rows 101–200 (full row data)"]
    N2 --> L3["Leaf: rows 201–300 (full row data)"]
    N2 --> L4["Leaf: rows 301–400 (full row data)"]
    L1 -.-> L2
    L2 -.-> L3
    L3 -.-> L4

A B+Tree, not a plain B-Tree, specifically because of that dotted line along the bottom: every leaf page holds a pointer to the next leaf page, forming a doubly linked list across all the leaves. Internal nodes only ever hold keys and pointers, never row data — that keeps them small enough that a huge fraction of the tree fits in the buffer pool even for enormous tables, so a lookup rarely needs more than 3–4 page reads regardless of table size. And because leaves are linked, a range scan (WHERE id BETWEEN 100 AND 500) doesn't need to re-traverse the tree for every row — it walks the root once to find the start, then just follows the linked list forward.

If you don't declare a primary key, InnoDB doesn't just give up on clustering — it picks the first UNIQUE NOT NULL column, or as a last resort, generates a hidden 6-byte auto-incrementing row ID internally. The table is always clustered by something; you just don't get to choose it if you don't specify one.

secondary indexes and the extra hop

Every other index you create — a UNIQUE on email, a plain index on created_at — is a secondary index, and it works fundamentally differently from what you might assume. A secondary index's leaf pages don't store a pointer to the row's physical location on disk. They store the primary key value.

This means every secondary index lookup is really two B+Tree traversals: one through the secondary index to find the primary key, then one through the clustered index to fetch the actual row.

This is why primary key choice matters more in InnoDB than in almost any other engine: a large, non-sequential primary key (a UUID, say) doesn't just cost extra space in the clustered index — it inflates the size of every single secondary index, since each one is silently carrying a copy of that primary key in every leaf entry. It's also why sequential auto-increment primary keys are the conventional recommendation: new rows always insert at the rightmost edge of the B+Tree instead of forcing random page splits throughout it.

A query that only needs columns present in a secondary index — including the primary key itself — can skip the second traversal entirely. That's a covering index, and it's one of the highest-leverage optimizations available: EXPLAIN will show Using index when it happens.

the redo log: why commit doesn't mean "on disk"

Here's a question worth sitting with: if the buffer pool holds dirty (modified but unwritten) pages, and those pages only get flushed to their tablespace lazily, how does a committed transaction survive a crash the instant before that flush happens?

The answer is write-ahead logging, and it's the mechanism that makes fast commits and crash safety compatible at all.

sequenceDiagram
    participant Client
    participant InnoDB
    participant LogBuffer as Redo Log Buffer
    participant LogFile as Redo Log Files (disk)
    Client->>InnoDB: UPDATE row
    InnoDB->>InnoDB: modify page in buffer pool (dirty)
    InnoDB->>LogBuffer: append redo record
    Client->>InnoDB: COMMIT
    InnoDB->>LogFile: fsync log buffer to disk
    LogFile-->>Client: commit acknowledged
    Note over InnoDB,LogFile: the actual data page is still only in memory

The redo log is a small, fixed-size, circular set of files (ib_logfile0, ib_logfile1, sized by innodb_redo_log_capacity) that InnoDB writes to sequentially. When a transaction commits, InnoDB doesn't need the modified data page to reach disk — it only needs the redo log record describing that change to be durably written. Sequential appends to a small log file are dramatically cheaper than random writes to scattered data pages, which is the entire performance case for the design.

Every redo record is tagged with a Log Sequence Number (LSN) — a monotonically increasing counter representing "how far the log has progressed." Periodically, InnoDB performs a checkpoint: it flushes enough dirty pages to disk that it can advance the "oldest LSN we'd need to replay from" forward, and record that position. This is what keeps the (fixed-size, circular) redo log from ever needing to hold more than the changes since the last checkpoint — older log space gets safely overwritten because everything before the checkpoint is now guaranteed to be reflected in the actual data files.

The setting that controls exactly how durable a commit is: innodb_flush_log_at_trx_commit. At 1 (the default, and the only fully ACID-compliant setting), every commit forces an fsync of the redo log to disk before returning — zero committed transactions can be lost. At 0 or 2, MySQL trades some durability for throughput by flushing less often — a real, deliberate tradeoff some systems make, not an oversight.

the undo log: how rollback and time-travel reads work

Redo log answers "how do we replay changes after a crash." Undo log answers a different question: "how do we undo a change that hasn't committed yet, and how does another transaction see the old value of a row that's currently being modified."

Every time InnoDB modifies a row, it doesn't just change the row in place — it first writes the previous version of that row to an undo log segment, and links the new row version back to it via a hidden pointer (DB_ROLL_PTR). Roll back a transaction, and InnoDB just walks that chain backward, restoring each prior version. This is also, not coincidentally, the exact mechanism that makes MVCC possible — covered next.

Undo log space isn't kept forever. Once no active transaction could possibly still need an old version (nothing has a read view old enough to require it), the purge thread reclaims it.

MVCC: how readers never block writers

This is the part that surprises people coming from simpler systems: in InnoDB, a SELECT almost never takes a lock, and almost never blocks on a concurrent write to the same row. That's not an accident or a relaxation of correctness — it's Multi-Version Concurrency Control, and it's built directly on the undo log chain above.

flowchart LR
    Row["Current row (in the page)"] --> U1["Undo record: version at T3"]
    U1 --> U2["Undo record: version at T2"]
    U2 --> U3["Undo record: version at T1"]
    View["A transaction's read view, opened at T2"] -. "walks the chain until it finds a visible version" .-> U2

Every row secretly carries two hidden columns: DB_TRX_ID (which transaction last modified it) and DB_ROLL_PTR (a pointer into the undo log, to the previous version). When a transaction starts a consistent read, it takes a snapshot called a read view — essentially, "which transaction IDs were already committed at this moment, and which were still in-flight." From then on, every row that transaction reads gets checked against that read view: if the row's current DB_TRX_ID is newer than what the read view should be allowed to see, InnoDB walks the undo chain backward until it finds a version that is visible.

The result: a long-running report query can read a perfectly consistent snapshot of the whole database while writes keep happening underneath it, without taking a single lock on the rows it's reading, and without ever seeing a half-committed change.

isolation levels, honestly

SQL defines four isolation levels, and each one is really a statement about which of three anomalies it tolerates.

\* InnoDB's REPEATABLE READ prevents phantom reads too — which is stricter than the SQL standard technically requires at that level, and it does it via the gap locking described in the next section, not purely through MVCC.

Most engines treat REPEATABLE READ as a weaker middle option. InnoDB's implementation is strong enough that a lot of production systems never have a real reason to reach for SERIALIZABLE, which — unlike the other three — does take locking reads and gives up real concurrency for the guarantee.

locking: row locks, gap locks, next-key locks

MVCC handles plain reads without locking. But writes, and reads that explicitly ask for locking (SELECT ... FOR UPDATE), still need real locks — and InnoDB's locking model has a piece that regularly surprises people: it locks gaps between rows, not just rows themselves.

Why gaps matter: without them, this sequence would be possible under REPEATABLE READ — transaction A runs SELECT * FROM orders WHERE amount > 100 FOR UPDATE, gets zero rows locked because none currently match, and transaction B inserts a new row with amount = 150 and commits. If A re-runs the same query, a row has appeared that wasn't there before — a phantom read, from a transaction that thought it had already locked everything relevant. A next-key lock closes that hole by locking not just matching rows but the gaps a new matching row could be inserted into.

This is also the reason REPEATABLE READ occasionally produces lock waits that look confusing at first: an INSERT can block on a gap lock held by a completely unrelated-looking SELECT ... FOR UPDATE, because that select silently locked the gap the new row wants to occupy.

deadlocks

Two transactions, each holding a lock the other needs, each waiting for the other to release it — a deadlock. InnoDB doesn't let this hang forever: a background thread walks the wait-for graph between transactions, and when it finds a cycle, it picks the transaction that's done the least work (by internal cost heuristics, roughly "smallest number of rows modified") and rolls that one back immediately, returning Error 1213: Deadlock found to it. The other transaction proceeds as if nothing happened.

This is a normal, expected event in a loaded system with row-level locking — not a sign of corruption or a bug. The correct application-level response is simply to retry the rolled-back transaction.

the change buffer and the adaptive hash index

Two smaller but genuinely clever pieces of machinery worth knowing:

Change buffer — when you modify a secondary index and the relevant page isn't currently in the buffer pool, InnoDB doesn't necessarily go fetch it from disk immediately. For inserts, updates, and deletes on non-unique secondary indexes, it can buffer the change in memory (the change buffer, physically stored inside the system tablespace) and merge it into the actual index page later — either when that page happens to get read in for some other reason, or during a background merge. This turns what would be a random disk read into a deferred, batchable operation, which matters a lot on write-heavy workloads with several secondary indexes.

Adaptive hash index — InnoDB monitors which B+Tree pages are being looked up repeatedly with the exact same lookup pattern, and can automatically build an in-memory hash index over those pages, turning a several-step B+Tree traversal into an O(1) hash lookup for that specific hot access pattern. It's entirely automatic and self-tuning — there's no equivalent of CREATE HASH INDEX you write yourself; InnoDB decides when it's worth it and discards it when the pattern changes. It can occasionally hurt more than it helps under some workloads, which is why innodb_adaptive_hash_index is a toggle, not a permanent guarantee.

the doublewrite buffer

There's a subtle danger in flushing a 16KB page from memory to disk: the underlying filesystem/disk block size is usually smaller (4KB, sometimes less), so a single page write isn't atomic at the hardware level. If the server crashes exactly mid-write, you can end up with a torn page — half old data, half new data — and the redo log can't fix that, because redo log records describe changes to a page, not a full replacement; they assume the page they're patching is intact.

The doublewrite buffer solves this cheaply: before a page is written to its real location, it's first written sequentially to a contiguous doublewrite buffer area. Only after that write is confirmed does InnoDB write the page to its actual position. If a crash happens during the real write and leaves a torn page, recovery detects the corruption and restores the page from its known-good doublewrite copy before redo log replay even starts.

crash recovery, end to end

Putting the redo log, undo log, and doublewrite buffer together — here's what actually happens when a crashed MySQL instance restarts.

flowchart TD
    A[Crash] --> B[Restart]
    B --> C["Check doublewrite buffer for torn pages, restore if needed"]
    C --> D[Scan redo log from last checkpoint LSN]
    D --> E["Replay every redo record, reconstructing all changes"]
    E --> F["Find transactions that were PREPARE but not COMMIT"]
    F --> G{"Is the transaction in the binary log?"}
    G -->|yes| H["Roll forward: commit it"]
    G -->|no| I[Roll back via undo log]

Redo log replay is applied blindly and completely first — every change since the last checkpoint gets reconstructed, including changes from transactions that never actually committed. It's only after that full replay that InnoDB looks at which transactions were left in a PREPARE state (the intermediate state used for the two-phase commit described next) and decides, per-transaction, whether to finish committing it or roll it back using the undo log. This two-pass structure — "replay everything, then reconcile" — is what makes recovery both fast (no complex logic during replay) and correct (the reconciliation step handles the edge cases separately).

replication

MySQL's default replication mechanism is built directly on a log you've already met: the binary log.

flowchart LR
    Primary["Primary: writes + binlog"] -->|binlog events, streamed| Dump[Binlog Dump Thread]
    Dump --> Relay["Replica: Relay Log"]
    Relay --> Apply["Replica: Apply Thread"]
    Apply --> ReplicaData[("Replica's own data + its own redo/undo")]

The primary streams its binary log events to each connected replica. Each replica writes those events into its own local relay log, then a separate apply thread replays them against its own storage engine — meaning a replica is doing real transactional work, not just copying bytes; it has its own buffer pool, its own redo log, its own crash recovery.

binlog_format decides what gets logged: STATEMENT logs the SQL text itself (compact, but risky — anything non-deterministic like NOW() or RAND() can produce different results on the replica), ROW logs the actual before/after row values (larger, but exactly reproducible), and MIXED picks per-statement. ROW is the modern default for good reason: it sidesteps an entire category of replication-divergence bugs.

By default this replication is asynchronous — the primary commits and returns to the client without waiting for any replica to confirm it received the change, which means a crash on the primary right after commit can lose transactions that never made it to a replica. Semi-synchronous replication closes that specific gap by having the primary wait for at least one replica to acknowledge receipt (not full apply, just receipt into its relay log) before returning — a real latency-for-safety tradeoff, not a free upgrade.

storage engines compared

InnoDB has been the default since 5.5, but the pluggable engine architecture from the first diagram still means other engines exist and occasionally make sense.

putting it all together

Trace one UPDATE all the way through with everything above in view: the optimizer picks a plan using clustered/secondary index statistics → the executor asks InnoDB for the row → InnoDB finds the page in the buffer pool (or pulls it in, via the young/old LRU policy) → takes a next-key lock appropriate to the isolation level → writes the row's previous version to the undo log → modifies the page in memory, marking it dirty → appends a redo log record and fsyncs it on commit → writes the same logical change to the binary log as part of a two-phase commit → periodically, a checkpoint flushes that dirty page to its real tablespace location, through the doublewrite buffer for torn-page safety → and if a replica is attached, that binary log event streams out and gets replayed independently on the other side.

None of these pieces is complicated in isolation. What makes InnoDB InnoDB is that they're all consistent with each other under concurrency and under a crash at literally any point in that sequence — which is the actual engineering problem a storage engine is solving.