MySQL深入
2026/1/15大约 9 分钟Java面试MySQL数据库后端
MySQL 深入面试题
本章深入讲解 MySQL 索引原理、事务机制、锁机制等高频面试考点。
索引原理
Q1: B+树索引结构是怎样的？
B+树特点：
- 非叶子节点只存索引：不存数据，可以存更多索引，树更矮
- 叶子节点存数据：所有数据都在叶子节点
- 叶子节点有序链表：支持范围查询
- 树高度低：3层可存约 2000万数据
Q2: 聚簇索引和非聚簇索引的区别？
| 类型 | 叶子节点存储 | 查询方式 |
|---|---|---|
| 聚簇索引 | 完整行数据 | 直接获取数据 |
| 非聚簇索引 | 主键值 | 需要回表查询 |
-- 聚簇索引查询（无需回表）
SELECT * FROM users WHERE id = 1;
-- 非聚簇索引查询（需要回表）
SELECT * FROM users WHERE name = 'Tom';
-- 先在 name 索引找到 id，再用 id 查聚簇索引Q3: 什么是覆盖索引？
覆盖索引：查询的列都在索引中，无需回表。
-- 创建联合索引
CREATE INDEX idx_name_age ON users(name, age);
-- 覆盖索引（无需回表）
SELECT name, age FROM users WHERE name = 'Tom';
-- 需要回表（查询了 email）
SELECT name, age, email FROM users WHERE name = 'Tom';Q4: 联合索引的最左前缀原则？
-- 联合索引 (a, b, c)
CREATE INDEX idx_abc ON t(a, b, c);| 查询条件 | 索引使用情况 |
|---|---|
a=1 | ✅ 使用 a |
a=1 AND b=2 | ✅ 使用 a,b |
a=1 AND b=2 AND c=3 | ✅ 使用 a,b,c |
b=2 | ❌ 不使用 |
a=1 AND c=3 | ⚠️ 只使用 a |
a=1 AND b>2 AND c=3 | ⚠️ 使用 a,b（范围后失效） |
Q5: 索引失效的场景有哪些？
-- 1. 对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024; -- ❌
SELECT * FROM users WHERE create_time >= '2024-01-01'; -- ✅
-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000; -- ❌ phone是varchar
SELECT * FROM users WHERE phone = '13800138000'; -- ✅
-- 3. LIKE 以 % 开头
SELECT * FROM users WHERE name LIKE '%Tom'; -- ❌
SELECT * FROM users WHERE name LIKE 'Tom%'; -- ✅
-- 4. OR 条件（部分列无索引）
SELECT * FROM users WHERE name = 'Tom' OR age = 18; -- age无索引则全表
-- 5. 不等于条件
SELECT * FROM users WHERE status != 1; -- 可能不走索引
-- 6. IS NULL / IS NOT NULL（视情况）
SELECT * FROM users WHERE name IS NULL; -- 可能不走索引事务机制
Q6: 事务的 ACID 特性？
| 特性 | 说明 | 实现机制 |
|---|---|---|
| 原子性 | 事务要么全成功，要么全失败 | undo log |
| 一致性 | 数据从一个一致状态到另一个一致状态 | 其他三者保证 |
| 隔离性 | 事务之间互不干扰 | 锁 + MVCC |
| 持久性 | 提交后数据永久保存 | redo log |
Q7: 事务隔离级别及问题？
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | ✅ 可能 | ✅ 可能 | ✅ 可能 |
| 读已提交（RC） | ❌ 解决 | ✅ 可能 | ✅ 可能 |
| 可重复读（RR） | ❌ 解决 | ❌ 解决 | ⚠️ 部分解决 |
| 串行化 | ❌ 解决 | ❌ 解决 | ❌ 解决 |
-- 查看隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Q8: MVCC 是什么？如何实现？
MVCC（多版本并发控制）：通过版本链实现读写不阻塞。
MVCC 核心组件：
- 隐藏列：
trx_id（事务ID）、roll_pointer（回滚指针） - undo log：存储历史版本，形成版本链
- ReadView：判断哪个版本对当前事务可见
可见性判断规则：
trx_id < min_trx_id：可见（事务已提交）trx_id > max_trx_id：不可见（事务在 ReadView 后开启）trx_id在活跃列表中：不可见（事务未提交）trx_id不在活跃列表中：可见（事务已提交）
Q9: RC 和 RR 的 MVCC 区别？
| 隔离级别 | ReadView 生成时机 | 效果 |
|---|---|---|
| RC | 每次 SELECT 生成新的 | 能看到其他事务已提交的数据 |
| RR | 第一次 SELECT 生成，后续复用 | 整个事务看到的数据一致 |
锁机制
Q10: MySQL 有哪些锁？
| 锁类型 | 粒度 | 说明 |
|---|---|---|
| 全局锁 | 整个数据库 | FTWRL，用于全库备份 |
| 表锁 | 整张表 | LOCK TABLES |
| 行锁 | 单行记录 | InnoDB 特有 |
| 间隙锁 | 索引间隙 | 防止幻读 |
| 临键锁 | 记录+间隙 | 行锁+间隙锁 |
Q11: 行锁的加锁规则？
-- 假设表中有 id: 1, 5, 10, 15
-- 唯一索引等值查询（存在）：记录锁
SELECT * FROM t WHERE id = 5 FOR UPDATE;
-- 锁：id=5 的记录锁
-- 唯一索引等值查询（不存在）：间隙锁
SELECT * FROM t WHERE id = 7 FOR UPDATE;
-- 锁：(5, 10) 间隙锁
-- 普通索引等值查询：临键锁 + 间隙锁
SELECT * FROM t WHERE age = 20 FOR UPDATE;
-- 锁：age=20 的临键锁 + 下一个间隙Q12: 死锁是怎么产生的？如何避免？
死锁产生条件：
- 互斥条件
- 持有并等待
- 不可剥夺
- 循环等待
避免死锁：
-- 1. 按固定顺序访问资源
-- 先锁 id 小的，再锁 id 大的
-- 2. 减少锁持有时间
-- 事务尽量短小
-- 3. 使用低隔离级别
-- RC 比 RR 锁更少
-- 4. 添加合适的索引
-- 避免全表扫描导致锁表SQL 优化
Q13: EXPLAIN 各字段含义？
EXPLAIN SELECT * FROM users WHERE name = 'Tom';| 字段 | 说明 | 重点关注 |
|---|---|---|
| type | 访问类型 | system > const > eq_ref > ref > range > index > ALL |
| key | 实际使用的索引 | NULL 表示未使用索引 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index（覆盖索引）、Using filesort（需优化） |
Q14: 慢 SQL 优化思路？
常见优化手段：
-- 1. 避免 SELECT *
SELECT id, name FROM users; -- ✅
-- 2. 分页优化
-- 慢
SELECT * FROM orders LIMIT 1000000, 10;
-- 快（游标分页）
SELECT * FROM orders WHERE id > 1000000 LIMIT 10;
-- 3. 避免在 WHERE 中使用函数
-- 慢
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- 快
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
-- 4. 用 EXISTS 替代 IN（大表）
-- 慢
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- 快
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);日志机制
Q15: redo log 和 binlog 的区别？
| 特性 | redo log | binlog |
|---|---|---|
| 层级 | InnoDB 引擎层 | Server 层 |
| 内容 | 物理日志（页修改） | 逻辑日志（SQL 语句） |
| 写入方式 | 循环写入 | 追加写入 |
| 作用 | 崩溃恢复 | 主从复制、数据恢复 |
Q16: 两阶段提交是什么？
两阶段提交保证 redo log 和 binlog 的一致性：
- 如果在步骤 2 之前崩溃：回滚事务
- 如果在步骤 2 之后崩溃：根据 binlog 恢复
小结
MySQL 深入面试重点：
- B+树索引结构和原理
- 聚簇索引、覆盖索引、最左前缀
- 事务 ACID 和隔离级别
- MVCC 实现原理
- 行锁、间隙锁、死锁
- EXPLAIN 分析和 SQL 优化
- redo log、binlog、两阶段提交
面试题预览
高频面试题
- B+树为什么适合做索引？
- 什么情况下索引会失效？
- MVCC 是如何实现的？
- RR 级别如何解决幻读？
- 如何优化慢 SQL？