Understanding MySQL Indexes, Execution Plans, Transaction Isolation, MVCC, and Buffer Pool

MySQL uses various index structures such as B‑tree, B+‑tree, and hash to store sorted data efficiently; B+‑tree stores data only in leaf nodes and includes bidirectional pointers to improve range scans.

SQL execution plans can be examined with EXPLAIN , revealing columns like id, table, type, key, rows, and Extra, which indicate the optimizer's choices and potential issues such as full table scans or temporary tables.

When a query runs, MySQL maintains a TCP connection, checks the query cache (LRU eviction), parses the SQL with a C‑based parser, optimizes it (cost‑based), and then the executor invokes the appropriate storage engine (InnoDB or MyISAM) to fetch data via the optimal index.

InnoDB and MyISAM differ: InnoDB supports transactions and row‑level locks, while MyISAM uses non‑clustered indexes and separate data files. Locks are classified as optimistic vs. pessimistic, shared (read) vs. exclusive (write), and table vs. row locks.

MySQL provides four transaction isolation levels—READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE—each preventing phenomena like dirty reads, non‑repeatable reads, and phantom reads to varying degrees; InnoDB uses gap locks to address phantom reads under REPEATABLE READ.

MVCC (Multi‑Version Concurrency Control) enables non‑blocking reads by maintaining a version chain (undo_log) and a ReadView that lists active transaction IDs; READ COMMITTED generates a new ReadView per query, while REPEATABLE READ reuses the first ReadView within a transaction.

InnoDB’s buffer pool caches pages in memory, writes changes to undo logs, updates the buffer, records modifications in the redo log buffer, and finally flushes redo logs and binlogs on commit, ensuring high performance and data consistency.