2025/12/12大约 8 分钟
MySQL 面试题
MySQL 数据库面试高频考点，覆盖索引、事务、锁、优化、主从复制等核心知识。
一、索引
Q1: B+Tree 为什么比 B-Tree/Hash 更适合 MySQL？
B+Tree vs B-Tree：
| 特性 | B+Tree | B-Tree |
|---|---|---|
| 数据存储 | 只在叶子节点存数据 | 所有节点都存数据 |
| 叶子节点 | 有链表串联，支持范围查询 | 无链表 |
| 每页存储 | 更多 key（因为不存数据） | 较少 key |
| IO 次数 | 更少（树更矮） | 更多 |
B+Tree vs Hash：
| 特性 | B+Tree | Hash |
|---|---|---|
| 等值查询 | O(logN) | O(1) |
| 范围查询 | ✅ 支持 | ❌ 不支持 |
| 排序 | ✅ 支持 | ❌ 不支持 |
| 前缀匹配 | ✅ 支持 | ❌ 不支持 |
结论： B+Tree 更适合 OLTP 场景的范围查询和排序需求。
Q2: 聚簇索引和二级索引的区别？什么是回表？
聚簇索引（Clustered Index）：
- 叶子节点存储完整数据行
- InnoDB 主键索引就是聚簇索引
- 每表只有一个
二级索引（Secondary Index）：
- 叶子节点存储主键值
- 非主键索引都是二级索引
- 查询完整数据需要回表
回表： 通过二级索引查到主键后，再根据主键去聚簇索引查完整数据。
-- 回表查询
SELECT * FROM user WHERE name = 'Tom';
-- 1. 通过 name 索引找到主键 id
-- 2. 通过主键 id 回表查完整数据
-- 覆盖索引，无需回表
SELECT id, name FROM user WHERE name = 'Tom';
-- name 索引已包含 id 和 name，直接返回Q3: 什么是最左前缀法则？
联合索引 (a, b, c) 的最左前缀法则：
| 查询条件 | 是否使用索引 |
|---|---|
a = 1 | ✅ |
a = 1 AND b = 2 | ✅ |
a = 1 AND b = 2 AND c = 3 | ✅ |
b = 2 | ❌ 无法使用 |
b = 2 AND c = 3 | ❌ 无法使用 |
a = 1 AND c = 3 | ✅ 只用 a |
范围查询右侧失效：
-- 索引 (a, b, c)
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3;
-- 只能用到 a 和 b，c 无法使用（范围查询后的列失效）Q4: 索引失效的常见场景
| 场景 | 示例 | 原因 |
|---|---|---|
| 函数/表达式 | WHERE YEAR(date) = 2024 | 索引列被加工 |
| 隐式类型转换 | WHERE varchar_col = 123 | 字符串列用数字查 |
| 左模糊 | WHERE name LIKE '%Tom' | 无法走索引 |
| OR 部分无索引 | WHERE a = 1 OR b = 2 | b 无索引则全表扫 |
| 数据分布差 | 回表代价超过全表扫描 | 优化器放弃索引 |
| 不等于 | WHERE status != 1 | 需扫描大部分数据 |
Q5: 如何创建高效的联合索引？
选择原则：
- 区分度高的列在前
- 常用查询条件优先
- 考虑排序/分组需求
- 尽量覆盖高频查询
-- 查询：WHERE user_id = ? AND status = ? ORDER BY created_at
-- 推荐索引：(user_id, status, created_at)
-- 避免冗余索引
-- (a), (a,b), (a,b,c) 只需要 (a,b,c)二、事务与 MVCC
Q6: 事务的 ACID 特性及实现
| 特性 | 说明 | 实现机制 |
|---|---|---|
| A 原子性 | 全成功或全失败 | Undo Log |
| C 一致性 | 数据状态正确 | 依赖其他三个特性 |
| I 隔离性 | 事务互不干扰 | MVCC + 锁 |
| D 持久性 | 提交后永久保存 | Redo Log |
Q7: MySQL 的四种隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | ✅ | ✅ | ✅ |
| READ COMMITTED (RC) | ❌ | ✅ | ✅ |
| REPEATABLE READ (RR) | ❌ | ❌ | ✅* |
| SERIALIZABLE | ❌ | ❌ | ❌ |
MySQL 默认 RR，通过 MVCC + Next-Key Lock 基本解决幻读。
Q8: MVCC 的实现原理
核心组件：
隐藏列
trx_id：最后修改的事务 IDroll_ptr：指向 Undo Log 的指针
Undo Log
- 存储数据的历史版本
- 形成版本链
Read View
- 记录活跃事务列表
- 判断哪些版本可见
Read View 生成时机：
| 隔离级别 | 生成时机 |
|---|---|
| RC | 每条语句都生成新的 |
| RR | 事务开始时生成，后续复用 |
Q9: Redo Log 和 Binlog 的两阶段提交
为什么需要两阶段提交？
保证 Redo Log 和 Binlog 的一致性。
流程：
1. 写入 Redo Log（prepare 状态）
2. 写入 Binlog
3. 提交事务，Redo Log 改为 commit 状态崩溃恢复：
- Redo Log 是 prepare，Binlog 无记录 → 回滚
- Redo Log 是 prepare，Binlog 有记录 → 提交
三、锁
Q10: InnoDB 的锁类型
按粒度分：
| 锁类型 | 说明 |
|---|---|
| 全局锁 | FLUSH TABLES WITH READ LOCK |
| 表锁 | MDL 锁、意向锁 |
| 行锁 | Record Lock、Gap Lock、Next-Key Lock |
行锁类型：
| 类型 | 说明 | 解决问题 |
|---|---|---|
| Record Lock | 锁定单行 | 解决脏读、不可重复读 |
| Gap Lock | 锁定间隙 | 解决幻读 |
| Next-Key Lock | Record + Gap | InnoDB 默认 |
Q11: 什么情况会产生间隙锁？
RR 隔离级别下：
-- 假设 id 有 1, 5, 10
-- 等值查询（不存在）
SELECT * FROM t WHERE id = 3 FOR UPDATE;
-- 锁住间隙 (1, 5)
-- 范围查询
SELECT * FROM t WHERE id > 3 AND id < 8 FOR UPDATE;
-- 锁住间隙 (1, 5] 和 (5, 10)间隙锁的问题： 可能导致死锁，降低并发。
Q12: 如何排查和解决死锁
排查方法：
-- 查看最近死锁
SHOW ENGINE INNODB STATUS\G
-- 开启死锁日志
SET GLOBAL innodb_print_all_deadlocks = 1;解决策略：
- 按固定顺序访问表/行
- 减小事务粒度
- 使用索引避免全表扫描
- 设置锁等待超时
innodb_lock_wait_timeout
四、SQL 优化
Q13: EXPLAIN 关键字段解读
| 字段 | 说明 | 关键值 |
|---|---|---|
| type | 访问类型 | const > eq_ref > ref > range > index > ALL |
| key | 实际使用的索引 | NULL 表示未使用索引 |
| rows | 预估扫描行数 | 越少越好 |
| Extra | 额外信息 | Using index（好）/ Using filesort（注意） |
type 值解释：
- const：主键/唯一索引等值查询
- eq_ref：关联查询，唯一索引匹配
- ref：普通索引等值查询
- range：范围查询
- ALL：全表扫描（需优化）
Q14: 慢查询如何排查
开启慢查询日志：
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2; -- 超过 2 秒记录分析工具：
# 官方工具
mysqldumpslow -s t -t 10 slow.log
# Percona 工具（推荐）
pt-query-digest slow.log优化步骤：
- 开启慢查询日志
- 找出 Top N 慢 SQL
- EXPLAIN 分析执行计划
- 添加/优化索引
- 重写 SQL 或调整业务
Q15: 分页查询如何优化
问题： LIMIT 100000, 10 需要扫描 100010 行
优化方案：
-- 原始 SQL
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 优化 1：子查询
SELECT * FROM orders
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
LIMIT 10;
-- 优化 2：游标分页（推荐）
SELECT * FROM orders WHERE id > 上次最大ID ORDER BY id LIMIT 10;五、复制与高可用
Q16: 主从复制的原理
流程：
1. Master 写入 Binlog
2. Slave IO 线程拉取 Binlog
3. Slave 写入 Relay Log
4. Slave SQL 线程重放 Relay Log复制模式：
| 模式 | 说明 | 特点 |
|---|---|---|
| 异步 | Master 不等 Slave | 性能好，可能丢数据 |
| 半同步 | 至少一个 Slave 确认 | 折中方案 |
| 全同步 | 所有 Slave 确认 | 强一致，性能差 |
Q17: 主从延迟的原因和解决方案
原因：
- 大事务执行慢
- 从库机器性能差
- 单线程回放（MySQL 5.6 前）
- 网络延迟
解决方案：
- 拆分大事务
- 开启并行复制
slave_parallel_workers - 提升从库配置
- 关键读操作强制走主库
Q18: 分库分表的时机和策略
触发条件：
- 单表超过 2000 万行
- 表大小超过 2GB
- QPS 超过单实例瓶颈
分表策略：
| 策略 | 说明 | 适用场景 |
|---|---|---|
| Hash | 按 key 取模 | 数据均匀分布 |
| Range | 按范围划分 | 时间序列数据 |
| 一致性哈希 | 减少扩容迁移 | 动态扩容场景 |
代价：
- 跨分片查询复杂
- 分布式事务
- 唯一 ID 生成
六、更多八股文
Q19: MySQL 的三大日志
| 日志 | 作用 | 层级 |
|---|---|---|
| binlog | 归档日志，主从复制 | Server 层 |
| redo log | 崩溃恢复，保证持久性 | InnoDB 存储引擎层 |
| undo log | 事务回滚，MVCC | InnoDB 存储引擎层 |
redo log vs binlog：
| 特性 | redo log | binlog |
|---|---|---|
| 写入方式 | 循环写，固定大小 | 追加写，归档 |
| 内容 | 物理日志（页修改） | 逻辑日志（SQL 语句） |
| 用途 | 崩溃恢复 | 复制、恢复 |
Q20: Buffer Pool 的作用
作用： 缓存数据页和索引页，减少磁盘 IO
组成：
- 数据页：缓存表数据
- 索引页：缓存索引
- Change Buffer：缓存二级索引修改
- 自适应哈希索引：热点数据索引
淘汰策略： 改进的 LRU，分为新生代和老年代
Q21: Change Buffer 的作用
作用： 缓存二级索引的写操作，减少随机 IO
条件：
- 只对二级索引生效
- 索引不是唯一索引（唯一索引需要判断重复）
合并时机：
- 访问该数据页时
- 后台线程定期合并
- 数据库关闭时
Q22: InnoDB 和 MyISAM 的区别
| 特性 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ | ❌ |
| 行锁 | ✅ | ❌（只有表锁） |
| 外键 | ✅ | ❌ |
| 崩溃恢复 | ✅ | ❌ |
| 全文索引 | ✅（MySQL 5.6+） | ✅ |
| 存储文件 | .ibd | .MYD + .MYI |
Q23: 大表优化方案
| 方案 | 说明 |
|---|---|
| 分区表 | 按时间/范围分区 |
| 归档冷数据 | 历史数据迁移 |
| 分库分表 | 水平/垂直拆分 |
| 读写分离 | 主写从读 |
| 索引优化 | 添加合适索引 |
| SQL 优化 | 避免全表扫描 |
Q24: 如何优化 COUNT(*)
-- 慢：全表扫描
SELECT COUNT(*) FROM orders;
-- 优化方案：
-- 1. 使用二级索引（比主键索引小）
SELECT COUNT(*) FROM orders FORCE INDEX(idx_status);
-- 2. 维护计数表
-- 3. 估算值（information_schema.TABLES）
-- 4. Redis 缓存计数Q25: 什么是当前读和快照读
| 类型 | 说明 | 示例 |
|---|---|---|
| 快照读 | 读取历史版本，通过 MVCC | 普通 SELECT |
| 当前读 | 读取最新数据，加锁 | SELECT ... FOR UPDATE |
Q26: MySQL 锁等待排查
-- 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;
-- 查看锁
SELECT * FROM performance_schema.data_locks;
-- 杀死阻塞事务
KILL <thread_id>;