面试高频核心汇总

本文汇总 MySQL 面试中最高频的核心知识点，每个主题以问答形式整理，便于快速复习。

一、索引原理

Q1：MySQL 为什么用 B+ 树做索引？

关键回答点：

核心答案：

1. 磁盘 IO 少：非叶子节点不存数据，每个节点可存更多键值（约 1170 个），3 层即可存储约 2000 万数据
2. 范围查询高效：叶子节点用双向链表连接，直接遍历即可
3. 查询稳定：任何查询都要走到叶子节点，IO 次数相近

Q2：聚簇索引和非聚簇索引的区别？

-- 聚簇索引（Clustered Index）
-- 叶子节点存储完整数据行
-- 一张表只能有一个
-- InnoDB 中主键即聚簇索引

-- 非聚簇索引（Secondary Index）
-- 叶子节点存储主键值
-- 一张表可有多个
-- 查询需要回表（查到主键再查聚簇索引）

回表优化：

• 覆盖索引：查询字段全部在索引中，无需回表
• 索引下推（ICP）：在索引层提前过滤，减少回表次数

Q3：联合索引的最左前缀原则？

联合索引 (a, b, c) 按 a → b → c 顺序排序，查询条件必须从最左列开始。

能用索引：a, a+b, a+b+c, a+排序b
不能用：b, c, b+c（跳过了 a）
部分用：a+c（只用到 a，c 用不到索引）
范围后失效：a > 1 AND b = 2（b 用不到索引）

设计原则：等值在前、排序在后、区分度高在前、范围放最后。

二、MVCC

Q4：MVCC 的实现原理？

MVCC（多版本并发控制）让读不阻塞写、写不阻塞读。

三个核心组件：

1. 隐藏字段：DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）
2. Undo Log 版本链：每行数据的多个历史版本
3. Read View：事务的"快照"

可见性规则：

版本事务ID < up_limit_id → 可见（已提交）
版本事务ID = creator_trx_id → 可见（自己修改）
版本事务ID in trx_ids → 不可见（未提交）
版本事务ID >= low_limit_id → 不可见（未来事务）

RC vs RR：

• RC：每个语句创建新的 Read View（不可重复读）
• RR：事务开始后第一个查询创建 Read View 并复用（可重复读）

Q5：MVCC 怎么解决不可重复读和幻读？

• 不可重复读：RR 级别复用 Read View，每次读同一快照 → 已解决
• 幻读：MVCC 的 Read View 机制只能解决快照读的幻读；当前读（SELECT ... FOR UPDATE/UPDATE）通过**临键锁（Next-Key Lock）**解决

三、事务隔离级别

Q6：四大隔离级别及其问题？

表象问题：

• 脏读：读到未提交的数据
• 不可重复读：同一事务两次读同一行结果不同
• 幻读：同一事务两次查询范围，行数不同

四、锁机制

Q7：InnoDB 有哪些锁？

按粒度：表锁、行锁、意向锁
按类型：记录锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）
按模式：共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）

Q8：间隙锁和临键锁是什么？

• 记录锁：锁单条索引记录
• 间隙锁：锁记录之间的间隙，防止幻读
• 临键锁：记录锁 + 间隙锁，InnoDB RR 级别默认加锁方式

-- 数据：id=1,3,5,7
-- 临键锁范围：(-∞,1], (1,3], (3,5], (5,7], (7,+∞)

SELECT * FROM users WHERE id > 3 FOR UPDATE;
-- 锁住：(3,5], (5,7], (7,+∞)
-- 不能插入 id=4,5,6,7,8...

Q9：死锁的成因和解决办法？

成因：两个事务互相等待对方释放锁。

解决办法：

1. 固定更新顺序（如按 ID 升序更新）
2. 缩短事务时间
3. 设置 innodb_lock_wait_timeout 超时
4. 降低隔离级别（RC 减少间隙锁）
5. MySQL 自动检测死锁并回滚其中一个事务

五、EXPLAIN 执行计划

Q10：执行计划中关键字段？

Q11：如何优化一条慢 SQL？

1. EXPLAIN 分析执行计划（type、rows、Extra）
2. 检查索引（是否全表扫描、索引是否失效）
3. 优化 SQL（减少 SELECT *、避免函数、小表驱动大表）
4. 改写 SQL（子查询 → JOIN、OR → IN）
5. 大分页优化（延迟关联、游标分页）
6. 必要时添加/优化索引

六、SQL 优化常见场景

Q12：索引失效的场景？

函数操作：WHERE DATE(col) = '2024-01-01'
隐式转换：WHERE phone = 13812345678（phone 是 varchar）
LIKE 前缀%：WHERE name LIKE '%张三'
违反最左前缀：联合索引 (a,b,c)，WHERE b = 1
!= / <>：WHERE status != 1
OR 混有无索引字段：WHERE name = '张三' OR age = 25
NOT IN：WHERE id NOT IN (1,2,3)

Q13：大分页为什么慢？怎么优化？

LIMIT 100000, 20 慢的原因是 MySQL 需要扫描前 100010 行再丢弃。

优化1（延迟关联）：
  子查询先走覆盖索引找到 id，再 JOIN 回原表

优化2（游标分页）：
  WHERE id > 上一页最后一条 id → 无需扫描无用行

优化3（范围查询）：
  利用索引的有序性，记录上一页的最后值

七、Binlog

Q14：Binlog 的作用和格式？

作用：主从复制、数据恢复、审计。

与 Redo Log 的区别：

Q15：两阶段提交是什么？

Prepare 阶段：Redo Log 写入 Prepare 状态
Commit 阶段：写入 Binlog → Redo Log 改为 Commit 状态

崩溃恢复时：
  Redo Log Prepare + Binlog 存在 → 提交
  Redo Log Prepare + Binlog 不存在 → 回滚
  Redo Log Commit → 直接提交

八、主从复制

Q16：主从复制原理？

三线程：

主库 Binlog Dump Thread → 发送 binlog
从库 IO Thread → 接收并写入 relay log
从库 SQL Thread → 读取 relay log 并执行

同步延迟原因：

1. 从库 SQL Thread 单线程执行
2. 主库大事务
3. 从库硬件性能差
4. 从库有大量读查询

解决方案：并行复制（slave_parallel_workers）、避免大事务、半同步复制。

九、分库分表

Q17：分库分表的策略？

Q18：分片键怎么选？

用户维度（推荐）：user_id % N → 用户查询高效
时间维度：按月/按年分表 → 归档方便，但热门数据集中
哈希：均匀分布 → 范围查询困难

核心问题：

• 全局主键：雪花算法
• 跨分片查询：映射表、广播查询
• 数据扩容：一致性哈希、双写迁移

十、分布式事务

Q19：分布式事务方案？

十一、实战排查思路

Q20：数据库连接池满了怎么办？

1. 查看当前连接分布
   SHOW PROCESSLIST;
2. 查找长时间 Sleep 的连接
3. 查找锁等待
   SELECT * FROM sys.innodb_lock_waits;
4. 查看大事务
   SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
5. 紧急处理：KILL 阻塞的线程
6. 根本原因：优化慢 SQL、增加连接池、升级硬件

Q21：主从延迟排查？

1. 查看延迟时间
   SHOW SLAVE STATUS\G → Seconds_Behind_Master
2. 检查 relay log 积压
   Relay_Log_Space 持续增长
3. 查看 SQL Thread 是否卡住
   SHOW PROCESSLIST → System lock 或 updating
4. 查看主库是否有大事务
5. 并行复制配置
   slave_parallel_workers = 4
   slave_parallel_type = LOGICAL_CLOCK

Q22：死锁排查？

1. 查看最近死锁
   SHOW ENGINE INNODB STATUS\G
2. 查看当前锁等待
   SELECT * FROM performance_schema.data_lock_waits;
3. 查看阻塞的事务
   SELECT * FROM information_schema.INNODB_TRX;
4. 分析死锁原因
   - 事务更新顺序不一致？
   - 间隙锁导致？
   - 缺少索引导致表锁？

十二、高频面试题速记