MySQL面试经典：为什么用B+树而不是B树？99%的人答不到点上

开篇

阿里P6面试现场。

面试官："MySQL的索引结构是什么？"

候选人自信回答："B+树。"

面试官追问："为什么用B+树，而不是B树？"

候选人开始紧张："因为...B+树查询更稳定？"

面试官继续追问："为什么更稳定？B树有什么问题？如果用红黑树呢？"

候选人沉默了。

这是MySQL面试中最经典的连环追问。

大部分候选人的问题是：只记住了"B+树"这个答案，却不知道这个设计背后的深层原因。

面试官考察的根本不是"B+树"这三个字，而是：

  • 理解数据库索引的设计取舍
  • 分析磁盘IO和内存的性能差异
  • 具备系统设计的思维方式

本文将从面试官视角出发，深度拆解MySQL索引、事务、锁、优化这四大核心考点。

本文结构

第一章 索引原理（必问）
├── 1.1 B+树 vs B树 vs 红黑树 vs Hash
├── 1.2 聚簇索引与回表
├── 1.3 索引失效的8种场景
└── 1.4 覆盖索引与索引下推

第二章 事务与锁（必问）
├── 2.1 四种隔离级别
├── 2.2 MVCC实现原理
├── 2.3 行锁、间隙锁、Next-Key Lock
└── 2.4 死锁检测与排查

第三章 SQL性能优化（高频）
├── 3.1 EXPLAIN全字段解读
├── 3.2 慢查询分析与优化
├── 3.3 深分页优化
└── 3.4 大表DDL方案

第四章 高可用架构（加分项）
├── 4.1 主从复制原理
├── 4.2 读写分离
└── 4.3 分库分表策略

第一章 索引原理

1.1 B+树 vs B树 vs 红黑树 vs Hash

这是MySQL索引面试的必问题。不是考你能不能背出"B+树"，而是考你能不能讲清楚为什么。

第一个问题：为什么不用红黑树？

红黑树的问题：树太高

假设有 1000 万条数据：
├── 红黑树高度 = log₂(10000000) ≈ 24 层
├── 每查一条数据，最多需要 24 次磁盘IO
├── 机械硬盘一次随机IO约 10ms
└── 最坏情况：24 × 10ms = 240ms（不可接受）

B+树的优势：树矮胖
├── B+树高度通常 3~4 层
├── 每查一条数据，只需 3~4 次磁盘IO
└── 最坏情况：4 × 10ms = 40ms（可接受）

为什么B+树能这么矮？

关键：每个节点存储更多的key

红黑树：每个节点只存 1 个key
B+树：每个节点存 数百~数千 个key

原因：
├── 磁盘读取按页（Page）进行，MySQL默认 16KB/页
├── B+树非叶子节点只存key（8字节）+ 指针（6字节）= 14字节
├── 一个16KB的页可以存：16384 / 14 ≈ 1170 个key
└── 三层B+树可存：1170 × 1170 × 16 ≈ 2000万条数据

第二个问题：为什么不用B树？

这是追问的重点。B树和B+树的区别：

特性B树B+树
数据位置所有节点都存数据只有叶子节点存数据
叶子节点独立的用链表连接
查询路径可能在任意层结束必须到叶子节点

B+树的三大优势：

优势1：磁盘IO更少

B树：非叶子节点存数据，每个节点能存的key变少
    → 树更高
    → IO次数更多

B+树：非叶子节点只存key，每个节点能存更多key
    → 树更矮
    → IO次数更少

举例（相同数据量）：
├── B树：4层，查询需要 4 次IO
└── B+树：3层，查询只需 3 次IO

优势2：范围查询更高效

-- 范围查询
SELECT * FROM users WHERE id BETWEEN 100 AND 200;

B+树执行过程：
1. 定位到 id=100 的叶子节点（3次IO）
2. 沿着链表顺序扫描到 id=200（顺序IO，很快）

B树执行过程：
1. 找到 id=100
2. 中序遍历整棵树找到 id=101, 102, ..., 200（随机IO，很慢）

优势3：查询性能稳定

B+树：所有查询都要到叶子节点
    → 每次查询都是 3 次IO
    → 性能可预期

B树：数据可能在任意层
    → 运气好 1 次IO，运气差 4 次IO
    → 性能不可预期

第三个问题：为什么不用Hash索引？

Hash索引特点：
├── 等值查询：O(1)，极快
├── 范围查询：O(n)，极慢（无法范围查询）
└── 无法排序

适用场景对比：
├── Redis（内存数据库）：大量使用Hash
└── MySQL（磁盘数据库）：主要使用B+树

MySQL中Hash索引的场景：
├── Memory引擎：默认Hash索引
└── InnoDB自适应哈希：自动为热点数据建立Hash索引

1.2 聚簇索引与回表

什么是聚簇索引？

InnoDB的两种索引：

1. 聚簇索引（主键索引）
   └── 叶子节点存储：完整的行数据

2. 二级索引（非主键索引）
   └── 叶子节点存储：主键值

图解：

主键索引（聚簇索引）：
         ┌───────────────────┐
         │      Root         │
         └─────────┬─────────┘
                   │
    ┌──────────────┼──────────────┐
    ▼              ▼              ▼
┌───────┐    ┌───────┐    ┌───────┐
│ id=1  │───▶│ id=5  │───▶│ id=10 │
│张三,20│    │李四,25│    │王五,30│
└───────┘    └───────┘    └───────┘
   叶子节点存储完整行数据

二级索引（name索引）：
         ┌───────────────────┐
         │      Root         │
         └─────────┬─────────┘
                   │
    ┌──────────────┼──────────────┐
    ▼              ▼              ▼
┌───────┐    ┌───────┐    ┌───────┐
│李四,5 │───▶│王五,10│───▶│张三,1 │
└───────┘    └───────┘    └───────┘
   叶子节点只存储主键值

什么是回表？

-- 创建表和索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name(name)
);

-- 查询
SELECT * FROM users WHERE name = '张三';

执行过程（需要回表）：

步骤1：在 idx_name 索引中查找 name='张三'
       → 得到主键 id=1
       → 3次IO

步骤2：拿着 id=1 回到主键索引查找完整数据
       → 得到 (1, '张三', 20)
       → 3次IO

总计：6次IO

如何避免回表？使用覆盖索引

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询只需要 name 和 age
SELECT name, age FROM users WHERE name = '张三';

执行过程（覆盖索引，无需回表）：

步骤1：在 idx_name_age 索引中查找 name='张三'
       → 索引中已包含 name 和 age
       → 直接返回结果
       → 3次IO

总计：3次IO（省一半）

面试常问：什么是索引覆盖？

当查询的字段都包含在索引中时，不需要回表查询主键索引。

EXPLAIN中的标志：Extra列显示 "Using index"

1.3 索引失效的8种场景

这是面试的重灾区。很多候选人背不全，或者理解不深。

场景1：违反最左前缀原则

-- 创建联合索引
CREATE INDEX idx_abc ON users(a, b, c);

-- ✅ 走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3  -- 只用到 a

-- ❌ 不走索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

原理：联合索引按照(a, b, c)的顺序排序。没有a，就无法利用索引的有序性。

场景2：在索引列上使用函数

-- ❌ 索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;
SELECT * FROM users WHERE LEFT(name, 2) = '张三';

-- ✅ 改写后走索引
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM users WHERE name LIKE '张三%';

原理：对索引列使用函数后，索引值被改变，无法利用索引的有序性。

场景3：隐式类型转换

-- phone 字段是 VARCHAR 类型

-- ❌ 索引失效（字符串转数字）
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 使用索引
SELECT * FROM users WHERE phone = '13800138000';

原理：MySQL会对phone字段做隐式转换，相当于 CAST(phone AS SIGNED) = 13800138000，触发了"函数"失效。

场景4：LIKE以通配符开头

-- ❌ 索引失效
SELECT * FROM users WHERE name LIKE '%张三';
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅ 使用索引
SELECT * FROM users WHERE name LIKE '张三%';

原理：索引是按照字符顺序排列的。%张三无法确定起始位置，只能全表扫描。

场景5：使用OR连接不同字段

-- ❌ 索引失效（假设只有name有索引）
SELECT * FROM users WHERE name = '张三' OR age = 20;

-- ✅ 改写后走索引
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 20;

原理：OR的两边必须都有索引才能走索引。一边没索引，整个查询就放弃索引。

场景6：使用 != 或 NOT IN

-- ❌ 可能不走索引
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- ✅ 改写为正向查询
SELECT * FROM users WHERE status IN (0, 2, 3);

原理：MySQL优化器认为全表扫描可能更快（需要根据数据分布判断）。

场景7：索引列参与计算

-- ❌ 索引失效
SELECT * FROM users WHERE age + 1 = 21;
SELECT * FROM users WHERE id * 2 = 100;

-- ✅ 使用索引
SELECT * FROM users WHERE age = 20;
SELECT * FROM users WHERE id = 50;

原理：同"函数"失效，计算后的值无法利用索引。

场景8：使用IS NULL / IS NOT NULL

-- ⚠️ 视情况而定
SELECT * FROM users WHERE name IS NULL;

-- 如果NULL值很少，走索引
-- 如果NULL值很多，不走索引（优化器选择全表扫描）

1.4 索引下推（ICP）

MySQL 5.6引入的优化，面试加分项。

-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询
SELECT * FROM users WHERE name LIKE '张%' AND age = 20;

无索引下推（MySQL 5.5及之前）：

1. 在索引中找到所有 name LIKE '张%' 的记录（假设1000条）
2. 回表1000次，取出完整数据
3. 在Server层过滤 age = 20（假设只有10条符合）

IO次数：1000次回表

有索引下推（MySQL 5.6+）：

1. 在索引中找到所有 name LIKE '张%' 的记录
2. 在索引中直接过滤 age = 20（因为索引包含age）
3. 只对符合条件的10条记录回表

IO次数：10次回表

EXPLAIN标志：Extra列显示 Using index condition

第二章 事务与锁

2.1 四种隔离级别

隔离级别脏读不可重复读幻读实现方式
READ UNCOMMITTED可能可能可能无锁
READ COMMITTED不会可能可能MVCC（每次SELECT生成新快照）
REPEATABLE READ不会不会InnoDB避免MVCC + Next-Key Lock
SERIALIZABLE不会不会不会读也加锁

MySQL默认：REPEATABLE READ（RR）

Oracle默认：READ COMMITTED（RC）

三种并发问题图解

脏读：读到未提交的数据

事务A                     事务B
─────────────────────────────────────
BEGIN;
UPDATE SET balance=100;
                          SELECT balance; → 100（脏数据）
ROLLBACK;
                          使用这个100进行计算... → 错误！

不可重复读：同一事务内，两次读取结果不同

事务A                     事务B
─────────────────────────────────────
BEGIN;
SELECT balance; → 50
                          UPDATE SET balance=100;
                          COMMIT;
SELECT balance; → 100     两次结果不同！

幻读：同一事务内，两次查询的记录数不同

事务A                     事务B
─────────────────────────────────────
BEGIN;
SELECT COUNT(*) WHERE age=20; → 10
                          INSERT INTO (age=20);
                          COMMIT;
SELECT COUNT(*) WHERE age=20; → 11  多了一条！

2.2 MVCC实现原理

MVCC（多版本并发控制）是InnoDB实现RC和RR隔离级别的核心。

隐藏字段

每行数据都有两个隐藏列：

字段作用
trx_id最后修改该行的事务ID
roll_pointer指向undo log中的旧版本

版本链

当前数据：(id=1, name='张三', trx_id=300)
              │
              ▼ roll_pointer
undo log：(id=1, name='李四', trx_id=200)
              │
              ▼ roll_pointer
undo log：(id=1, name='王五', trx_id=100)

ReadView

事务在读取数据时，会生成一个ReadView：

字段含义
m_ids当前活跃（未提交）事务ID列表
min_trx_id最小活跃事务ID
max_trx_id下一个要分配的事务ID
creator_trx_id创建该ReadView的事务ID

可见性判断

读取一行数据，trx_id 为该行的事务ID：

1. trx_id < min_trx_id
   → 该事务已提交，可见

2. trx_id >= max_trx_id
   → 该事务在ReadView创建后才开始，不可见

3. min_trx_id <= trx_id < max_trx_id
   → 在 m_ids 中：未提交，不可见
   → 不在 m_ids 中：已提交，可见

4. trx_id == creator_trx_id
   → 自己的修改，可见

RC vs RR的区别

隔离级别ReadView生成时机
READ COMMITTED每次SELECT都生成新的ReadView
REPEATABLE READ只在第一次SELECT时生成，后续复用

这就是为什么RR级别可以避免不可重复读的原因！

2.3 行锁、间隙锁、Next-Key Lock

InnoDB的锁算法是面试重点。

三种锁类型

锁类型锁定范围作用
Record Lock单行记录防止并发修改同一行
Gap Lock索引间隙防止插入新记录
Next-Key Lock记录 + 间隙防止幻读

Next-Key Lock图解

-- 表数据
id: 1, 5, 10, 15

-- 间隙
(-∞, 1], (1, 5], (5, 10], (10, 15], (15, +∞)

-- 执行
SELECT * FROM t WHERE id = 10 FOR UPDATE;

-- Next-Key Lock锁定范围：(5, 10]
-- 包括：间隙(5, 10) + 记录10

实战示例

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT,
    KEY idx_age(age)
);

INSERT INTO users VALUES (1, 10), (5, 20), (10, 30);

-- 事务A
BEGIN;
SELECT * FROM users WHERE age = 20 FOR UPDATE;

-- 锁定范围分析：
-- 1. Record Lock：age=20 的行（id=5）
-- 2. Gap Lock：(10, 20) 和 (20, 30)
-- 3. Next-Key Lock = 以上组合

-- 事务B尝试插入
INSERT INTO users VALUES (3, 15);  -- 阻塞！（在间隙10-20内）
INSERT INTO users VALUES (7, 25);  -- 阻塞！（在间隙20-30内）
INSERT INTO users VALUES (11, 35); -- 成功（不在锁定范围内）

2.4 死锁检测与排查

死锁产生条件

1. 互斥：资源不能共享
2. 持有并等待：持有资源的同时等待其他资源
3. 不可剥夺：资源只能主动释放
4. 循环等待：A等B，B等A

死锁示例

-- 事务A
BEGIN;
UPDATE users SET age = 21 WHERE id = 1;  -- 锁住 id=1

-- 事务B
BEGIN;
UPDATE users SET age = 22 WHERE id = 2;  -- 锁住 id=2

-- 事务A
UPDATE users SET age = 23 WHERE id = 2;  -- 等待 id=2...

-- 事务B
UPDATE users SET age = 24 WHERE id = 1;  -- 等待 id=1...

-- 死锁！MySQL自动检测，回滚其中一个事务

排查死锁

-- 1. 查看最近一次死锁日志
SHOW ENGINE INNODB STATUS\G

-- 关键信息：
-- LATEST DETECTED DEADLOCK 部分
-- 显示两个事务各自持有和等待的锁

-- 2. 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 3. 查看当前锁
SELECT * FROM information_schema.INNODB_LOCKS;

避免死锁的方法

1. 按固定顺序访问资源
   └── 所有事务都按 id 升序更新，不会形成循环

2. 缩短事务时间
   └── 减少持锁时间

3. 使用低隔离级别
   └── RC级别没有Gap Lock，不容易死锁

4. 添加合理索引
   └── 索引查询锁定范围小

第三章 SQL性能优化

3.1 EXPLAIN全字段解读

EXPLAIN SELECT * FROM users WHERE name = '张三';

核心字段详解

字段重要性说明
type⭐⭐⭐访问类型，性能从好到差
key⭐⭐⭐实际使用的索引
rows⭐⭐⭐预估扫描行数
Extra⭐⭐额外信息

type字段（从好到差）

system  表只有一行
  │
const   主键/唯一索引等值查询
  │      SELECT * FROM users WHERE id = 1;
  │
eq_ref  关联查询时，被驱动表用主键/唯一索引
  │      SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
  │
ref     普通索引等值查询
  │      SELECT * FROM users WHERE name = '张三';
  │
range   索引范围查询
  │      SELECT * FROM users WHERE age BETWEEN 20 AND 30;
  │
index   全索引扫描
  │      SELECT id FROM users;
  │
ALL     全表扫描（最差）
         SELECT * FROM users WHERE age + 1 = 21;

面试必答：type至少要达到range级别，ALL需要优化。

Extra字段

值含义需要优化？
Using index覆盖索引好
Using whereWHERE过滤正常
Using index condition索引下推好
Using filesort额外排序需优化
Using temporary使用临时表需优化

3.2 慢查询分析与优化

开启慢查询日志

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';

-- 开启
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

分析慢查询

# 使用mysqldumpslow
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 输出示例：
# Count: 1000  Time=2.5s  Lock=0.00s  Rows=10000
# SELECT * FROM orders WHERE status = 'N'

优化案例

案例1：缺少索引

-- 问题SQL
SELECT * FROM orders WHERE status = 'pending';
-- EXPLAIN: type=ALL, rows=1000000

-- 优化：添加索引
CREATE INDEX idx_status ON orders(status);
-- EXPLAIN: type=ref, rows=100

**案例2：避免SELECT ***

-- 问题SQL（回表）
SELECT * FROM users WHERE name = '张三';

-- 优化：只查需要的字段（覆盖索引）
SELECT id, name FROM users WHERE name = '张三';

3.3 深分页优化

这是面试高频问题。

-- 问题SQL
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 需要扫描 1000010 行，丢弃前 1000000 行
-- 时间：10s+

方案1：延迟关联

SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

-- 原理：
-- 1. 子查询只在索引树上定位（不回表）
-- 2. 只对10条结果回表
-- 时间：0.1s

方案2：游标分页

-- 记录上一页最后一条的id
-- 假设上一页最后一条 id = 1000000

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 原理：从指定位置开始扫描，无需跳过前面的数据
-- 限制：只能顺序翻页，不能跳页

方案3：业务限制

直接限制用户只能看前100页（淘宝、京东都这么做）

为什么：
1. 用户不会真的看第10000页
2. 前100页的商品已经足够选择
3. 避免恶意爬虫

3.4 大表DDL方案

大表加字段、加索引如何不影响线上？

问题

-- 1000万行的表
ALTER TABLE orders ADD COLUMN remark VARCHAR(200);

-- MySQL 5.6之前：锁表，业务阻塞
-- MySQL 5.6+：Online DDL，但仍可能影响性能

方案1：pt-online-schema-change

pt-online-schema-change \
  --alter "ADD COLUMN remark VARCHAR(200)" \
  D=mydb,t=orders \
  --execute

# 原理：
# 1. 创建新表 orders_new
# 2. 创建触发器，同步增量数据
# 3. 分批复制历史数据
# 4. 重命名表

方案2：gh-ost

gh-ost \
  --alter "ADD COLUMN remark VARCHAR(200)" \
  --database=mydb \
  --table=orders \
  --execute

# 与pt-osc区别：
# 不用触发器，使用binlog同步
# 对主库压力更小

第四章 高可用架构

4.1 主从复制原理

Master（主库）
  │
  │ 1. 执行SQL，写入Binlog
  │
  ▼
Binlog Dump线程
  │
  │ 2. 发送Binlog给Slave
  │
  ▼
Slave IO线程
  │
  │ 3. 接收Binlog，写入Relay Log
  │
  ▼
Slave SQL线程
  │
  │ 4. 执行Relay Log
  │
  ▼
Slave数据

三种复制模式

模式特点一致性性能
异步复制Master不等Slave可能丢数据最高
半同步复制Master等一个Slave确认可能丢数据中等
全同步复制Master等所有Slave确认不丢数据最低

主从延迟问题

原因：

1. Slave SQL线程单线程执行（瓶颈）
2. 大事务长时间执行
3. 网络延迟

解决：

-- 并行复制（MySQL 5.7+）
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 4;

4.2 读写分离

应用层
  ├── 写操作 → Master
  └── 读操作 → Slave（负载均衡）

强一致性读怎么办？

方案1：强制读Master
       适用：支付完成后查询订单状态

方案2：等待延迟追平
       SELECT MASTER_POS_WAIT('binlog.000001', 12345, 1);

方案3：使用缓存
       写入后同时更新缓存，读缓存

4.3 分库分表策略

什么时候需要分库分表？

单表超过 500万~1000万 行，或单表超过 10GB
查询性能明显下降

垂直拆分 vs 水平拆分

垂直拆分：按业务拆分
├── 用户库：users
├── 订单库：orders
└── 商品库：products

水平拆分：按数据拆分
├── orders_0（user_id % 4 = 0）
├── orders_1（user_id % 4 = 1）
├── orders_2（user_id % 4 = 2）
└── orders_3（user_id % 4 = 3）

分片策略

策略优点缺点
Range扩容简单数据分布不均
Hash分布均匀扩容困难
一致性Hash扩容迁移数据少实现复杂

中间件选择

ShardingSphere：功能全面，社区活跃
MyCat：老牌，功能稳定
Vitess：YouTube开源，适合超大规模

本章小结

MySQL面试的四大核心考点：

考点必问题面试官关注
索引B+树 vs B树理解设计权衡，不是死记硬背
事务隔离级别 + MVCC能画出可见性判断流程
锁Next-Key Lock能分析锁范围
优化EXPLAIN分析有实际调优经验

面试回答技巧：

  1. 先答核心：用一句话回答问题
  2. 再说原理：解释为什么
  3. 举个例子：用具体场景说明
  4. 说出边界：什么情况下不适用

下期预告

下一篇：《Redis面试三连击：穿透、击穿、雪崩，你真的能答清楚吗？》

将揭秘：

  • 缓存穿透/击穿/雪崩的本质区别
  • 布隆过滤器的原理与实现
  • 分布式锁的正确姿势
  • Redis高可用架构设计

💬 互动话题

评论区聊聊：在MySQL优化中，你遇到过最棘手的问题是什么？