Understanding MySQL Index Structures, Execution Plans, Transactions, and MVCC

MySQL indexes are built on underlying data structures like B+‑tree and hash; B+‑tree stores only leaf nodes with data, while hash is rarely used for sorting. Different tree types (binary tree, red‑black tree, B‑tree, B+‑tree) have trade‑offs in height, disk I/O, and memory usage.

MyISAM uses non‑clustered indexes where the index stores pointers to data files, whereas InnoDB has both primary (clustered) indexes that store the row data and secondary (non‑clustered) indexes that store the primary key for lookup, requiring a “back‑table” query.

SQL execution plans can be examined with EXPLAIN , revealing columns such as id, table, type (system, const, eq_ref, ref, range, index, ALL), key, rows, and Extra (e.g., Using index, Using where, Using temporary, Using filesort) that indicate how the optimizer will access data.

The execution flow of a SQL statement includes: establishing a TCP connection, checking the query cache (LRU eviction), parsing the SQL with a C‑based parser, optimizing the query (cost calculation, possible FORCE_INDEX), invoking the storage engine (InnoDB or MyISAM), and finally using the chosen index to retrieve rows.

Transaction isolation levels (read‑uncommitted, read‑committed, repeatable‑read, serializable) address concurrency problems such as dirty reads, non‑repeatable reads, and phantom reads. InnoDB supports row‑level locks (shared S‑lock and exclusive X‑lock) and uses gap locks to prevent phantom reads under repeatable‑read.

MySQL implements MVCC using a ReadView and an undo‑log version chain. Each row modification creates an undo record linked by hidden trx_id and roll_pointer fields; ReadView determines which versions are visible to a transaction, enabling consistent reads without locking.

InnoDB’s buffer‑pool caches pages in memory, writes modifications to the undo log, updates the buffer pool, records changes in the redo log buffer, and on commit flushes redo logs and binlogs to ensure durability and crash recovery.

Note: The article also contains promotional material for ChatGPT services and a community, which is not part of the technical discussion.