MySQL InnoDB Explained: Core Architecture & Performance

MySQL InnoDB is the storage engine that makes MySQL safe for transactional workloads: ACID transactions, row-level locking, automatic crash recovery, and foreign keys. Everything below reflects InnoDB as shipped in MySQL 8.0 through 8.4, the current GA line, with older differences noted only where they matter. This guide covers the InnoDB storage engine's core — storage, transactions, and locking — plus the tuning moves that pay off fastest.

InnoDB as the default MySQL storage engine

InnoDB has been MySQL's default storage engine since MySQL 5.5 (2010). A storage engine owns how data is stored and cached, how indexes are built, and how durability and concurrency are enforced. InnoDB delivers what production systems need: transactions that survive crashes, row-level locking so concurrent writers don't queue, automatic deadlock detection, and foreign keys (MySQL's InnoDB introduction).

InnoDB vs the MySQL server layer

Everything above the engine — parsing, the optimizer, query execution — lives in the MySQL server layer, shared by all storage engines; InnoDB takes over when a query needs rows read or written. For a deeper look at how MySQL's server layer and storage engines split responsibilities, see our MySQL architecture guide. This post stays inside the engine.

InnoDB vs other storage engines

InnoDB displaced MyISAM, which still appears in legacy systems:

MyISAM still fits read-mostly, disposable data; anything that writes belongs on InnoDB.

The MySQL InnoDB architecture splits into memory structures, write-ahead logs, and on-disk tablespaces (InnoDB architecture in the MySQL manual).

InnoDB splits its work between memory structures, write-ahead logs, and on-disk tablespaces.

The buffer pool and change buffer

The InnoDB buffer pool caches 16KB data and index pages; every read and write flows through it, and the default is a modest 128MB. Pages are evicted LRU-style, with new pages inserted at a midpoint so a full-table scan can't evict hot pages. The change buffer defers changes to secondary index pages that aren't in the pool, recording the change and merging it when the page is read rather than forcing a disk read — it is not a general write cache. The adaptive hash index automatically builds hash entries for pages with repeated lookups, speeding point reads; its activity shows in SHOW ENGINE INNODB STATUS.

The redo log and log buffer

Modifying a row doesn't immediately write the 16KB page to disk — that would be too slow. Instead, the change is appended to the InnoDB redo log, a circular sequential log, and dirty pages are flushed in the background. At COMMIT, the transaction's redo entries move from the log buffer to the redo log and are forced to disk (with the default innodb_flush_log_at_trx_commit = 1) — this write-ahead logging is what makes commits durable. In MySQL 8.0.30 and later, redo capacity is set by innodb_redo_log_capacity, which replaced innodb_log_file_size and innodb_log_files_in_group.

Undo logs, doublewrite buffer, and tablespaces

Every modification also writes an InnoDB undo log entry — what's needed to reverse the change — powering ROLLBACK and MVCC's older row versions. Since MySQL 8.0, undo logs live in dedicated undo tablespaces (two by default), not the system tablespace. The doublewrite buffer guards against torn pages: a crash mid-flush can leave a 16KB page half-written, so dirty pages go to the doublewrite area first (its own .dblwr files since MySQL 8.0.20) before their final location — doublewrite protects page writes, while commit durability comes from the redo log. On disk, data and indexes live in tablespaces: the system tablespace plus one .ibd file per table (innodb_file_per_table, on by default), alongside the undo and redo files.

Clustered indexes: the primary key is the table

InnoDB indexes are B-trees. In a clustered index — every InnoDB table is one — rows live in the leaf pages, ordered by primary key. (For background, see our guide on how B-tree database indexes work.) A primary key lookup reads the row directly — there is no separate table heap. Define no primary key and InnoDB still creates the table, but builds a hidden GEN_CLUST_INDEX on an internal 6-byte ROWID: a key you can't query, that no secondary index can use, and that replicates poorly. Always declare one.

Secondary indexes store primary key values

Secondary indexes are separate B-trees whose leaf entries store the indexed columns plus the table's primary key values — not a physical row location. Reading through one takes two lookups: the primary key in the secondary B-tree, then the row in the clustered B-tree. A covering index holds every column a query needs, stopping after the first lookup — one of the cheapest performance wins available.

Why primary key design matters

Primary key choice propagates through the table. Size: every secondary index entry duplicates the primary key, so a 16-byte key bloats them all. Insert pattern: a monotonically increasing key like auto-increment appends at the right edge of the B-tree, keeping pages dense; a random key like a UUIDv4 lands unpredictably, causing page splits, fragmentation, and scattered I/O — while also being large. Prefer small, ordered primary keys; keep UUIDs in ordinary columns.

A single row update touches nearly every component above; the flow below traces one from modification to durable commit.

The life of a row update: modification, undo entry, redo entry, commit, and recovery.

ACID transactions in InnoDB

InnoDB transactions earn each ACID property with a dedicated mechanism. Atomicity: undo logs reverse every change, so COMMIT applies all of a transaction or ROLLBACK removes all of it. Durability: redo write-ahead logging — a commit's redo on disk means the change survives, even before the data pages are written. Isolation: row locks for writes, MVCC for reads. Consistency follows from the three, plus constraints and application logic.

How MVCC provides consistent reads

Every row carries two hidden columns: DB_TRX_ID, the transaction that last modified it, and DB_ROLL_PTR, a pointer into the undo log where older versions can be rebuilt. A reading transaction takes a read view — a snapshot of which transactions had committed — and picks the visible version: the current row, or an older one from the undo chain. Because a plain SQL SELECT statement reads a snapshot, not live rows, readers take no row locks and never block writers (multi-versioning in the MySQL manual).

READ COMMITTED vs REPEATABLE READ

InnoDB defaults to REPEATABLE READ. The levels differ in when the snapshot is taken: REPEATABLE READ builds its read view at the transaction's first read and reuses it, so every SELECT sees the same picture; READ COMMITTED takes a fresh snapshot per statement, so a transaction can observe other transactions' commits mid-flight. They lock differently too — REPEATABLE READ uses next-key locks to prevent phantoms, READ COMMITTED mostly drops gap locking (isolation levels in the MySQL manual).

Commit, rollback, and crash recovery

Trace the update end to end: InnoDB changes the page in the buffer pool, writes the old row image to the undo log, and appends the change to the log buffer. COMMIT flushes those redo entries to disk — that flush is the commit; the data pages follow later, through the doublewrite buffer. ROLLBACK applies undo in reverse. After a crash, recovery replays redo to restore committed changes, then uses undo to remove those never committed.

Row locks, gap locks, and next-key locks

InnoDB locks index records, so what a statement locks depends on what your WHERE clause matches and which index it uses — an unindexed filter can lock far more records than it returns. Record locks (shared or exclusive) sit on index records; intention locks (IS and IX) are table-level flags that row locks exist. A gap lock covers the space between index records, blocking inserts into it; a next-key lock is a record lock plus the preceding gap.

How isolation changes locking behavior

Under REPEATABLE READ (the default), range scans take next-key locks, so re-reading a range returns identical rows — no phantoms. Under READ COMMITTED, gap locking is largely disabled — record locks only, except for foreign-key and duplicate-key checks — shrinking the lock footprint and deadlock surface at the cost of possible phantoms. To read the latest committed data instead of a snapshot, use SELECT ... FOR UPDATE or SELECT ... FOR SHARE.

Deadlocks: causes, detection, and prevention

A deadlock is a lock cycle — two transactions each holding what the other needs. It takes two updates in a different order:

-- Session 1                      -- Session 2
BEGIN;                            BEGIN;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;                     UPDATE accounts SET balance = balance + 10
                                  WHERE id = 2;
UPDATE accounts SET balance = balance + 10
WHERE id = 2;   -- waits          UPDATE accounts SET balance = balance - 10
                                  WHERE id = 1;   -- deadlock
-- ERROR 1213 (40001): Deadlock found when trying to get lock;
-- try restarting transaction

InnoDB detects the cycle automatically (innodb_deadlock_detect is on by default), rolls back the transaction that modified fewer rows, and returns error 1213 to that session while the other proceeds. The LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS shows recent cycles. Prevention: touch rows in a consistent order, index the columns your updates filter on, keep transactions short, and retry on error 1213 in application code.

Sizing the buffer pool

This is the first setting worth changing on any server. On a dedicated database host, give the buffer pool a large share of RAM, leaving room for per-connection buffers and the OS, then verify with the hit ratio: Innodb_buffer_pool_read_requests counts reads served from memory; Innodb_buffer_pool_reads counts reads that had to hit disk. When the disk-read fraction stops falling as memory grows, you've found your size — and innodb_buffer_pool_size resizes online in MySQL 8.0.

Designing efficient primary and secondary indexes

Apply the storage rules from earlier: small, ordered primary keys; covering secondary indexes for hot queries so reads stop in one B-tree; and an index for every column combination your UPDATE statements filter on — locks land on index records, and an unindexed update locks a wide range.

Keeping transactions short

Long transactions do double damage: they hold locks longer, meaning more waiting and deadlocks; and they pin undo history — InnoDB can't purge old row versions while a transaction might still read them, so undo grows and version chains lengthen. Find offenders in information_schema.innodb_trx (check trx_started) and commit in small batches.

Diagnosing bottlenecks with EXPLAIN and InnoDB status

Run EXPLAIN for the chosen access path, and EXPLAIN ANALYZE (MySQL 8.0.18+) to execute and time each step. SHOW ENGINE INNODB STATUS is a dense one-screen health report: buffer pool statistics, redo activity, the latest deadlock. For lock contention, performance_schema.data_locks and sys.innodb_lock_waits show who holds what and who waits. Under heavy write load, review innodb_redo_log_capacity (MySQL 8.0.30+) — too little capacity forces aggressive flushing (InnoDB configuration in the MySQL manual).