Deep Dive into MySQL Indexes, Execution Plans, Transactions, MVCC, and Buffer Pool

The article begins by explaining the fundamental data structures and algorithms behind MySQL indexes, covering B‑tree, B+‑tree, hash, binary tree, and red‑black tree, and discusses the trade‑offs of each structure.

It then moves to SQL execution plan analysis, describing the meaning of columns such as id , table , type , key , rows , and Extra , and explains how to interpret common Extra values like Using index , Using where , Using temporary , and Using filesort .

The next section details how MySQL executes a query: the client communicates over TCP, the query is cached using an LRU algorithm, parsed by a C‑based parser, optimized by the optimizer (including cost estimation), and finally executed by the storage engine (InnoDB or MyISAM).

Transaction isolation levels are then examined, defining dirty reads, non‑repeatable reads, phantom reads, and describing the four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE). Example commands are provided: show variables like '%isolation%'; set transaction_isolation='read-uncommitted';

The article explains MySQL locking mechanisms, distinguishing optimistic vs. pessimistic locks, shared (read) vs. exclusive (write) locks, and table vs. row locks, emphasizing that row locks are the most common in application development.

It then introduces MVCC (Multi‑Version Concurrency Control), describing how InnoDB maintains an undo‑log version chain and a ReadView to provide consistent snapshots for READ COMMITTED and REPEATABLE READ isolation levels, with examples of how different isolation levels affect query results.

Finally, the InnoDB buffer pool architecture is described: data pages are read into the buffer pool, changes are logged to undo logs, updated in memory, written to the redo log buffer, flushed to redo logs on commit, and asynchronously persisted to binlog, ensuring high performance and durability.