Two halves: the server and the storage engine

MySQL is split in two. The server layer speaks the protocol, parses SQL, chooses a plan and runs it; it knows nothing about how rows are stored. The storage engine stores rows and indexes, and handles transactions, locks and crash safety. They talk through the handler API: calls such as index_read, rnd_next, write_row, update_row and delete_row. The default engine, and the one drawn here, is InnoDB.

The client sends SQL text to the server layer: connection thread, parser, optimizer and executor, which also writes the binlog. The executor calls the handler API into InnoDB, which holds the buffer pool of pages in RAM, the undo log, the redo log buffer flushed to the redo log at COMMIT, and the users.ibd data file written later.
The server layer turns SQL into handler calls; InnoDB does the storing, caching, logging and crash safety.

The animation uses one table and one client in autocommit mode, so every statement is its own transaction:

CREATE TABLE users (
    id   INT PRIMARY KEY,
    name VARCHAR(20),
    age  INT,
    KEY idx_age (age)
);   -- 8 rows, ids 1..10 with gaps

The server layer, for every statement

  1. Connection thread. Each client connection is served by its own thread (reused from the thread cache). The statement arrives as text in a COM_QUERY packet.
  2. Parser. A lexer turns the text into tokens and a parser builds a parse tree. Names are then resolved (does users exist, which columns does * mean) and privileges checked. Syntax errors (1064) and unknown columns (1054) stop here.
  3. Optimizer. Picks how to reach the rows: which index, in which order to join tables, using cost estimates from index statistics. EXPLAIN prints its choice.
  4. Executor. Runs the plan by calling the handler API, row by row, tests any conditions the engine did not, and sends result rows back to the client.

The query cache, which used to sit before the parser, was removed in MySQL 8.0: it was invalidated by every write to a table and became a point of contention.

Where rows live: the clustered index and secondary indexes

InnoDB stores a table as a B+ tree on the primary key, the clustered index. Its leaf pages (16 KB each) hold the whole rows in key order, plus hidden columns: DB_TRX_ID (the transaction that last changed the row) and DB_ROLL_PTR (a pointer to the previous version in the undo log). Leaves are linked to their neighbours, so a range or a full scan just walks along the leaf level.

A secondary index such as idx_age is another B+ tree whose leaves hold (age, primary key), not the row. So SELECT * … WHERE age = 30 does two searches per match: one in idx_age to get the id, and one in the clustered index to fetch the row (back to the table; in EXPLAIN terms, not "Using index"). A covering query such as SELECT id FROM users WHERE age = 30 needs only the first. A column with no index, like name, can only be found by reading every leaf page: a full table scan.

idx_age leaves hold (age, id) pairs; the entries (30, 5) and (30, 7) lead to ids 5 and 7, which are then found in the PRIMARY clustered index leaves that hold whole rows: id 5 Eve age 30 and id 7 Gus age 30.
A secondary index gives only the primary key; fetching the rest of the row is a second search in the clustered index.

In the animation the tree shapes are fixed (three PRIMARY leaves and two idx_age leaves); real trees split pages as they grow and have more levels, typically 3–4 for millions of rows.

The buffer pool

InnoDB never reads or changes a row on disk directly. Every page is first brought into the buffer pool, a large cache in memory (innodb_buffer_pool_size, often most of the server's RAM). A page already there is a hit; otherwise it costs a disk read. Grey pages in the animation are only on disk; run the first demo to see a cold read and then a warm one. A page changed in memory but not yet written back is dirty (orange).

A SELECT

A plain SELECT is a consistent read: InnoDB takes a read view, a snapshot of which transactions had committed, and takes no locks. For each record it finds, it checks DB_TRX_ID against the read view; if that version is too new, it follows DB_ROLL_PTR into the undo log and rebuilds the older version. This is MVCC (multi-version concurrency control): readers never block writers and writers never block readers. Delete-marked records are simply invisible. (SELECT … FOR UPDATE and FOR SHARE are locking reads instead.)

An INSERT, UPDATE or DELETE

Every change follows the same order:

  1. Find and lock. Search the clustered index for the row and take an exclusive (X) record lock, held until commit. An insert usually needs no lock object: the new record's DB_TRX_ID acts as an implicit lock.
  2. Undo first. Write the old version to the undo log: for an insert, "remove this id"; for an update, the old column values; for a delete, the row. Undo is what ROLLBACK uses, and what other transactions' read views use to see the old data.
  3. Change the page in memory. The leaf page in the buffer pool is modified and becomes dirty, and a redo record describing the change is appended to the log buffer.
  4. Secondary indexes. Each affected index is changed the same way. An index key is never updated in place: an UPDATE of age delete-marks the old entry and inserts a new one.

Two details surprise people. DELETE does not remove the record; it only sets a delete mark, because older read views may still need it. And an UPDATE that sets a column to its current value changes nothing ("Rows matched: 1, Changed: 0").

COMMIT: redo, binlog and the two-phase commit

At commit the data pages are not written. Writing a few random 16 KB pages would be slow; instead InnoDB follows write-ahead logging: the small, sequential redo log is written and fsynced, and the pages go to disk later. If the server crashes, the redo log can rebuild them.

MySQL also keeps a binary log (binlog), written by the server layer, which replicas and point-in-time recovery replay. Redo and binlog must agree, so a commit is a small two-phase commit:

1. InnoDB prepare:  mark trx prepared, write + fsync the redo log   (innodb_flush_log_at_trx_commit = 1)
2. binlog:          write the transaction's events, fsync            (sync_binlog = 1)
3. InnoDB commit:   write the commit mark, release locks, free insert-undo

After a crash, a transaction that is prepared but whose events are not in the binlog is rolled back; one that reached the binlog is committed. With both settings at 1, a committed transaction is never lost. Setting either to 0 or 2 trades up to about a second of commits for fewer fsyncs.

Three commit steps: InnoDB prepare with redo fsync, binlog write with fsync, InnoDB commit. A crash after prepare but before the binlog rolls the transaction back; a crash after the binlog write commits it.
The binlog write is the decision point: a prepared transaction counts as committed exactly when its events reached the binlog.

Later, in the background: page cleaner and purge

  • Page cleaner. Writes dirty pages to the tablespace, each first through the doublewrite buffer so that a crash in the middle of a 16 KB write cannot leave a half-written page. When all changes up to some LSN (log sequence number, a byte position in the redo log) are on disk, the checkpoint moves there and the older redo space can be reused. Press Flush Dirty Pages.
  • Purge. Once no read view can need an old version, the purge threads remove delete-marked records and free the undo records. Only then is a DELETE final. A long-running transaction holds back purge, so undo and "history list length" keep growing. Press Purge.

Crash recovery

After a crash, pages on disk may be older than the committed data. On restart InnoDB reads the checkpoint LSN, scans the redo log from there, and applies each record to its page if the page's own LSN is older (redo). Then it rolls back transactions that were still active, using undo (undo), and resolves prepared ones against the binlog. Run the last demo: the delete of id 3 was never written to users.ibd, yet after the crash it is still gone.

What each statement touches

locksundopages changedredobinlog
SELECTnone (read view)read, for old versionsnonenonenone
INSERTimplicitinsert-undo, freed at commitleaf + each indexyesWrite_rows
UPDATEX on the rowupdate-undo, kept for purgeleaf; changed indexes: delete-mark + insertyesUpdate_rows
DELETEX on the rowdelete-undo, kept for purgedelete-mark in leaf and each indexyesDelete_rows

What the animation leaves out

  • Other sessions: lock waits, deadlocks, gap and next-key locks (which REPEATABLE READ takes on ranges to stop phantoms).
  • Explicit transactions (BEGIN … COMMIT / ROLLBACK): here every statement commits by itself.
  • Page splits and merges, the adaptive hash index, the change buffer (off by default since MySQL 8.4), the LRU's young and old sublists, read-ahead, and group commit, which lets many transactions share one fsync.

Practical consequences

  • Index the columns you filter on, and put the most selective equality columns first in composite indexes. Check with EXPLAIN: type = ALL means a full scan.
  • Keep the primary key short and increasing (an AUTO_INCREMENT integer): every secondary index entry carries a copy of it, and random keys such as UUIDv4 split pages all over the tree.
  • Select only the columns you need; a covering index avoids the lookup back to the table.
  • Keep transactions short: they hold locks, and an old read view stops purge.
  • Size the buffer pool to hold the working set, and the redo log (innodb_redo_log_capacity) so that checkpoints do not force flushing during write bursts.

See also How PostgreSQL Runs a Query and How MongoDB Runs a Command (the same data in a document store: WiredTiger update chains, journal, oplog and write concern).