MySQL 深入面经

MySQL 核心知识

1. 存储引擎

特性	InnoDB	MyISAM
事务	✅	❌
外键	✅	❌
行级锁	✅	❌（表锁）
崩溃恢复	✅	❌
全文索引	✅（5.6+）	✅
适用场景	OLTP	读多写少

2. 索引深入

B+ 树结构

• 非叶子节点只存 key，叶子节点存 key + data
• 叶子节点通过链表相连，范围查询只需遍历链表
• 树高度通常 2-4 层，亿级数据 IO 次数少

聚簇索引 vs 二级索引

• 聚簇索引（主键）：叶子节点直接存完整行数据
• 二级索引：叶子节点存主键值 → 需回表查完整数据
• 覆盖索引：查询字段全在索引中，无需回表

索引失效场景

-- ❌ 函数操作
WHERE YEAR(create_time) = 2024
-- ✅ 改为范围查询
WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'

-- ❌ 隐式类型转换（字段为 varchar，值为数字）
WHERE phone = 13800138000
-- ✅
WHERE phone = '13800138000'

-- ❌ 联合索引 (a, b, c)，跳过了 b
WHERE a = 1 AND c = 3

3. 事务与锁

MVCC 实现细节

• 每行数据有隐藏字段：trx_id（最后修改的事务ID）、roll_pointer（指向 undo log）
• Read View：包含活跃事务列表，决定当前事务能看哪个版本
• 可重复读：事务开始时创建 Read View，整个事务期间复用
• 读已提交：每次 SELECT 创建新 Read View

锁类型

共享锁（S）：SELECT ... LOCK IN SHARE MODE
排他锁（X）：SELECT ... FOR UPDATE / INSERT / UPDATE / DELETE
意向锁（IS/IX）：表级，InnoDB 自动加，表示行锁存在
间隙锁（Gap Lock）：锁定索引间隙，防止幻读（可重复读级别）
临键锁（Next-Key Lock）= 行锁 + 间隙锁

死锁

• 两个事务互相等待对方持有的锁
• InnoDB 自动检测，回滚代价小的事务
• 预防：固定加锁顺序、减少锁范围、缩短事务

4. EXPLAIN 分析

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

字段	关注点
type	system > const > ref > range > index > ALL（ALL 是全表扫描，需优化）
key	实际使用的索引
rows	预计扫描行数（越小越好）
Extra	Using filesort（文件排序，需优化）/ Using temporary / Using index（覆盖索引，好）

5. 主从复制与高可用

复制原理

Master binlog → Slave IO Thread → relay log → Slave SQL Thread → 执行

• binlog 格式：STATEMENT（可能不一致）/ ROW（准确）/ MIXED
• 半同步复制：至少一个从库收到 binlog 再提交，保证数据不丢
• MGR（MySQL Group Replication）：多主复制，Paxos 协议保证一致性

6. 高频面试题

• 为什么推荐自增主键？ 顺序写入 B+ 树，避免页分裂，减少碎片
• 大表如何 DDL 变更？ pt-online-schema-change / gh-ost，在线变更不锁表
• 慢查询如何定位？ 开启 slow_query_log + EXPLAIN 分析 + 优化索引
• count(*) 和 count(1) 有区别吗？ InnoDB 中几乎没有区别，都走最小索引全扫描
• 为什么 NOT IN 会导致索引失效？ 含 NULL 值时 NOT IN 结果不确定，可改用 NOT EXISTS