MySQL Interview: Indexes, Transactions and Locks
B+ tree structure, index failure patterns, isolation levels, MVCC and row/gap locks — the core of almost every MySQL interview.
Indexes
Q1: Why B+ tree and not B tree / hash / red-black?
B+ tree: non-leaf nodes hold no data, only keys → huge fanout → a 3-level tree covers tens of millions of rows (page size 16KB). All data lives in leaf nodes linked in a linked list → range scans are sequential reads. Hash does point lookups fine but not ranges; red-black trees are binary → too tall for disk pages.
Q2: Clustered index vs secondary index.
The clustered index IS the table (leaf = whole row, InnoDB uses the PK or a hidden rowid). A secondary index leaf holds the PK value, so a non-covering select does a 'table lookup' (回表). Covering index = all selected columns already in the index → skip the lookup.
Q3: When does an index FAIL to be used?
- Leftmost-prefix violation: composite index (a,b,c) but condition only on b/c
- Functions/math on the column: WHERE YEAR(create_time), WHERE id+1=10
- Implicit type conversion: varchar column compared to a number
- LIKE with leading wildcard: LIKE '%abc'
- OR across indexed and non-indexed columns
- Optimizer decides scanning is cheaper (low selectivity / few rows)
Transactions and isolation
Q4: The four anomalies and four isolation levels.
| Level | Dirty read | Unrepeatable | Phantom |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不可能 | 可能 | 可能 |
| REPEATABLE READ（InnoDB 默认） | 不可能 | 不可能 | InnoDB 用间隙锁基本解决 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 |
Q5: How does MVCC work?
Each row has hidden trx_id and roll_pointer; the undo log chains historical versions. Each transaction gets a read view (active transaction list) at its snapshot; a version is visible if its trx_id is committed before the view's min boundary. This lets reads see a consistent snapshot while writes create new versions — non-locking reads.
Q6: Record lock / gap lock / next-key lock.
- Record lock: locks the index entry itself (a row)
- Gap lock: locks the interval BETWEEN rows, only under RR, to stop INSERTs into that range
- Next-key = record + the gap before it; default under RR
-- 间隙锁实战：事务 A
BEGIN;
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE; -- 锁住 100..200 及间隙
-- 事务 B 想插 id=150 → 阻塞；插 id=300 → 正常
-- 间隙锁只在 RR 存在，RC 下没有，所以 RC 更易并发也更易幻读索引
Q1：为什么用 B+ 树而不是 B 树/哈希/红黑树？
B+ 树非叶节点不存数据只存键 → 扇出极大 → 3 层即可覆盖千万级数据（页 16KB）。数据都在叶子且叶子间有链表 → 范围扫描就是顺序读。哈希做等值查询快但做不了范围；红黑树是二叉的 → 对磁盘页来说太高。
Q2：聚簇索引与二级索引。
聚簇索引就是表本身（叶子存整行，InnoDB 用主键或隐藏 rowid）。二级索引叶子存主键值，所以非覆盖查询要「回表」。覆盖索引 = 要查的列都在索引里，直接返回，不再回表。
Q3：索引什么时候会失效？
- 不满足最左前缀：联合索引 (a,b,c) 却只查 b/c
- 对列做函数/运算：YEAR(create_time)、id+1=10
- 隐式类型转换：varchar 列与数字比较
- 左模糊：LIKE '%abc'
- OR 连接了有索引和没索引的列
- 优化器判断全表扫描更划算（区分度低/行数少）
事务与隔离
Q4：四种异常与四种隔离级别。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不可能 | 可能 | 可能 |
| REPEATABLE READ（InnoDB 默认） | 不可能 | 不可能 | InnoDB 用间隙锁基本解决 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 |
Q5：MVCC 是怎么实现的？
每行有隐藏的 trx_id 和回滚指针，undo log 串起历史版本。事务在快照点拿到读视图（活跃事务列表），版本可见的条件是它在视图下限之前已提交。于是读看到一致快照、写产生新版本——读不加锁。
Q6：记录锁、间隙锁、Next-Key 锁。
- 记录锁：锁住索引项本身（一行）
- 间隙锁：锁住行与行之间的区间，只在 RR 下存在，防止往这个范围插入
- Next-Key = 记录锁 + 前面间隙；RR 下默认就是它
-- 间隙锁实战：事务 A
BEGIN;
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE; -- 锁住 100..200 及间隙
-- 事务 B 想插 id=150 → 阻塞；插 id=300 → 正常
-- 间隙锁只在 RR 存在，RC 下没有，所以 RC 更易并发也更易幻读