MySQL Internals

Monty, ISAM, and “fast enough” · MyISAM’s reign, and why a table lock was acceptable · Innobase, Heikki Tuuri, and Oracle’s two purchases · Sun, the fork, and Percona · 5.5 → 8.4: what actually changed, and why · The pluggable storage engine API · MySQL and Postgres as two philosophies

Connection: socket, handshake, and the auth plugin that breaks your driver · One thread per connection · The parser, and the ghost of the query cache · Resolution and preparation · The optimizer’s place in the path · The executor and the iterator model · The handler API: where the server ends · Result packets on the wire

The files MySQL keeps · Tablespaces · The 16KB page, byte by byte · Extents, segments, and how space is allocated · Row formats, compared at byte level · Off-page storage, and the 767-byte ghost · Choosing innodb_page_size · Reading a real .ibd file

Why a B+tree, and nothing else · The clustered index: the table is the tree · Secondary indexes and the double lookup · Insert: how a page fills · Page splits · Page merges and MERGE_THRESHOLD · Fragmentation, fill factor, and the rebuild ritual · AUTO_INCREMENT versus UUID keys · The change buffer · The adaptive hash index · Index dives, cardinality, and persistent statistics · Covering indexes, ICP, and MRR · Prefix, functional, and multi-valued indexes · Descending indexes, and why 5.7’s were a lie · Invisible indexes as a production safety tool · InnoDB full-text internals · Spatial indexes

ACID, honestly · The read view · The hidden columns · Undo logs and the version chain · Purge, and how one long transaction fills your disk · The four isolation levels · REPEATABLE READ in MySQL specifically · Phantoms, write skew, and lost updates · Consistent reads versus locking reads · SKIP LOCKED as a job queue

The lock hierarchy · Metadata locks, and the ALTER that hung everything · Intention locks · Record locks, gap locks, next-key locks · Insert intention locks, and the two-insert deadlock · AUTO-INC lock modes · Deadlock detection · Reading INNODB STATUS line by line · performance_schema.data_locks — the modern way · Designing deadlocks away

Write-ahead logging, and the LSN · The redo log · innodb_flush_log_at_trx_commit — the durability dial · Group commit · The doublewrite buffer, and torn pages · fsync, the page cache, and disks that lie · Crash recovery · innodb_force_recovery

Buffer pool structure · The midpoint LRU · Flush list, page cleaners, adaptive flushing · Read-ahead · Checkpoint age and the stall · Dump and load on restart · Per-connection memory, and the OOM arithmetic · Temporary tables

The cost model · Statistics and histograms · Join algorithms · Join order search · Semijoin strategies · Derived tables: merge or materialize · Index merge, and when it is a trap · ORDER BY, GROUP BY, and filesort · Reading EXPLAIN ANALYZE properly · Optimizer trace · Hints · CTEs and window functions

Binlog formats · Two logs, and the XA commit between them · The replication pipeline · GTIDs · Semi-synchronous replication · Multi-threaded appliers · Measuring lag truthfully · Group Replication and InnoDB Cluster · Router, ProxySQL, Vitess · Failover and split brain · Read-your-writes on replicas

Schema changes and INSTANT DDL · gh-ost and pt-online-schema-change · Connections and pooling · Backups and point-in-time recovery · Observability · The configuration that actually matters · Character sets and collations · JSON columns · Partitioning · The security surface

Design of the toy engine · The pager · Record encoding · The B+tree · A write-ahead log · MVCC · A tiny SQL executor · Torture testing it

Sixty questions, ranked by depth · Ten war stories · MySQL versus Postgres, defended properly · Where MySQL is going