Reference · Course map

MySQL keystones & review plan

The compressed spine of the course: one keystone idea per lesson, plus a spaced-review schedule to keep them.

Covers lessons 0001–0008Context: InnoDB, MySQL 8.0+

Pillar 1 · Indexing & query planning

0001	The table is the primary-key B+tree; secondary indexes point back by PK → a non-covering read is a double (bookmark) lookup.
0002	A composite index is a phone book: only leftmost prefixes seek; equality columns before the range column; matching ORDER BY rides the index.
0003	Grade any plan: type → key → rows×filtered → Extra. Ladder: const → eq_ref → ref → range → index → ALL.
0004	An index seeks only on a bare column vs a matching-type constant. Killers: function wrap · leading wildcard · type mismatch · low selectivity.

Pillar 2 · Concurrency

0005	A plain read is a lock-free MVCC snapshot; the isolation level only times it. RR = snapshot at first read; RC = fresh per statement.
0006	Locks sit on index records: record / gap / next-key (default, stops phantoms). Lock cycles deadlock → InnoDB kills a victim (1213) → retry.

Pillar 3 · Durability & replication

0007	Write-ahead logging: redo rolls forward on crash; undo rolls back (and feeds MVCC). Dial: `innodb_flush_log_at_trx_commit` (1 = full ACID).
0008	The binlog is a logical change feed (replication + PITR), distinct from redo. Async can lose a failover commit; semisync waits for a replica to receive.

The system in one breath

clustered index → secondary points back by PK → covering/composite avoids the double lookup → EXPLAIN grades it → (non-)sargable decides if the index is used. plain reads = MVCC snapshots (undo) · writes = record/gap/next-key locks → deadlocks. COMMIT durable via redo (WAL); binlog ships changes to replicas (async | semisync).

Spaced-review schedule

Retrieval beats re-reading. Do these from memory, then check against the linked lesson.

When	Do
Day 1	Recite each lesson's keystone from the left column, covered.
Day 3	Re-take the interleaved review quiz cold.
Day 7	Do the 5 "diagnose it" scenarios; re-drill any pillar that felt slow.
Day 21	Explain the whole system out loud to someone (or rubber-duck it) in 3 minutes.
Ongoing	Each real `EXPLAIN` / deadlock / lag at work = a live rep. Bring the hard ones to your teacher.

All claims trace to the lessons above and dev.mysql.com. · All lessons · Glossary