MySQL 高频 20 题详解
数据库是后端面试权重最高的板块之一。每道题按「考察点 → 答案 → SQL / 图」展开,建议配合
MySQL面试题.md(速查版)一起复习。
1. 索引数据结构:为什么是 B+ 树?
考察点:理解索引选型的本质——磁盘 IO。
数据库索引存在磁盘上,磁盘 IO 才是性能瓶颈,因此选型的核心是尽量降低树高、减少 IO 次数。
| 结构 | 问题 |
|---|---|
| 二叉/红黑树 | 树太高,深度大,磁盘 IO 次数多 |
| 哈希 | 等值查询 O(1),但不支持范围、排序 |
| B 树 | 非叶子节点也存数据,单页存不了多少 key,树变高 |
| B+ 树 | 非叶子只存 key,叶子存全部数据且有序链表相连,支持范围查询 |
B+ 树优势:
- 非叶子节点不存数据,单页能存更多索引,树更矮,IO 更少。
- 叶子节点用双向链表连接,天然支持范围查询和排序。
- 查询效率稳定,每次都是树高次 IO。
graph TD
R["根节点(仅key)"] --> N1["内节点(仅key)"]
R --> N2["内节点(仅key)"]
N1 --> L1["叶子(数据+key) ⇄ 叶子"]
N1 --> L2["叶子(数据+key) ⇄ 叶子"]
N2 --> L3["叶子(数据+key) ⇄ 叶子"]
style L1 fill:#e6f7e6
style L2 fill:#e6f7e6
style L3 fill:#e6f7e62. 聚集索引 vs 非聚集索引、回表
考察点:InnoDB 的存储结构。
- 聚集索引(主键索引):叶子节点直接存整行数据。InnoDB 表本身就是以主键为索引的 B+ 树。
- 非聚集索引(二级索引):叶子节点存主键值,查到主键后还要回表到聚集索引查整行数据。
graph TD
P["主键索引 B+树"] --> PR["叶子存完整行数据"]
S["二级索引(name) B+树"] --> SR["叶子存主键id"]
SR -->|回表| P
style PR fill:#e6f7e6
style SR fill:#fff3cd一个表只能有一个聚集索引,但可以有多个二级索引。
3. 联合索引与最左前缀原则
考察点:联合索引的排序规则和命中条件。
联合索引 (a, b, c) 按 a → b → c 的顺序排序,类似字典序。查询必须从最左列开始,且不能跳过中间列。
-- 假设建了联合索引 idx_abc(a, b, c)
SELECT * FROM t WHERE a = 1; -- ✅ 命中 a
SELECT * FROM t WHERE a = 1 AND b = 2; -- ✅ 命中 a,b
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3; -- ✅ 全命中
SELECT * FROM t WHERE b = 2; -- ❌ 不命中(未用最左 a)
SELECT * FROM t WHERE a = 1 AND c = 3; -- ⚠️ 只用 a,c 无法用(跳过了 b)
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- ⚠️ 范围后 c 失效记忆:最左列必须出现;范围查询会「断掉」后续列的索引。
4. 覆盖索引 + 索引下推
考察点:两个重要的优化手段。
覆盖索引:查询的列都在索引上,无需回表。EXPLAIN 的 Extra 显示 Using index。
-- 有联合索引 idx(name, age),查询列都在索引内 → 覆盖索引,不回表
SELECT name, age FROM t WHERE name = '张三';索引下推(ICP,Index Condition Pushdown):MySQL 5.6+,把部分 WHERE 过滤下推到存储引擎层,减少回表次数。
-- 联合索引 idx(name, age)
-- 无 ICP:先按 name 回表查出所有行,再在 Server 层过滤 age
-- 有 ICP:存储引擎直接用 age 过滤,减少回表
SELECT * FROM t WHERE name LIKE '张%' AND age = 20;5. 索引失效场景
考察点:面试高频,背熟这 7 条。
- 违反最左前缀原则。
- 对索引列做函数或运算:
WHERE age + 1 = 20、WHERE DATE(create_time) = '2024-01-01'。 - 隐式类型转换:字符串列用数字查
WHERE phone = 13800000000。 LIKE '%xx'以%开头。- 使用
!=、<>、NOT IN。 OR连接非索引列(需两边都是索引列才走索引)。- 优化器认为全表扫描更快(数据量小或返回占比大)。
-- 反例
SELECT * FROM t WHERE age + 1 = 20; -- 函数运算
SELECT * FROM t WHERE name LIKE '%三'; -- 前导 %
SELECT * FROM t WHERE phone = 13800000000; -- phone 是 varchar,隐式转换6. 索引设计原则
考察点:工程经验,聊「你怎么建索引」时用。
- 区分度高(选择性高)的列优先建索引。
- 联合索引把区分度高、常查询的列放最左。
- 高频查询尽量用覆盖索引,避免回表。
- 频繁更新的列慎建索引(维护成本)。
- 大字段(text/blob)不建普通索引。
- 主键建议用自增:聚集索引顺序插入,减少页分裂,二级索引叶子存主键也更小。
- 控制单表索引数量,避免写放大。
7. 事务 ACID 与隔离级别
考察点:四大特性 + 四种级别 + 各自问题。
- ACID:原子性、一致性、隔离性、持久性。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 有 | 有 | 有 |
| 读已提交 | 无 | 有 | 有 |
| 可重复读(默认) | 无 | 无 | 基本解决 |
| 串行化 | 无 | 无 | 无 |
- 脏读:读到别的事务未提交的数据。
- 不可重复读:同一事务两次读同一行不一致(被修改)。
- 幻读:同一事务两次查询行数不一致(被插入/删除)。
8. MVCC 原理
考察点:多版本并发控制的底层实现。
MVCC 依赖两样东西:
- 隐藏字段:每行有
trx_id(最近修改事务 ID)、roll_pointer(回滚指针,指向 undo log)。 - undo log 版本链:每次修改生成旧版本,通过 roll_pointer 串成版本链。
读取时用 Read View 判断版本链上哪个版本可见,实现不加锁的快照读。
graph LR
R["当前行<br/>trx_id=300"] -->|roll_pointer| U1["undo: trx_id=200"]
U1 -->|roll_pointer| U2["undo: trx_id=100"]
U2 -->|roll_pointer| U3["初始版本"]
style R fill:#e6f7e6
style U1 fill:#fff3cd
style U2 fill:#fff3cd
style U3 fill:#f0f0f0Read View 可见性规则:
- 版本
trx_id小于 Read View 中最小活跃事务 ID → 已提交,可见。 - 大于最大事务 ID → 不可见。
- 在活跃列表内 → 若是当前事务自己,可见;否则不可见,沿版本链向前找。
9. 快照读 vs 当前读
考察点:区分两种读,理解 MVCC 与锁的分工。
- 快照读:普通
SELECT,走 MVCC,读历史快照,不加锁。 - 当前读:
SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT,读最新数据并加锁。
-- 快照读(MVCC)
SELECT * FROM t WHERE id = 1;
-- 当前读(加锁,读到的是最新提交的数据)
SELECT * FROM t WHERE id = 1 FOR UPDATE;可重复读下,快照读读的是事务开始时的快照;当前读读的是最新已提交数据,通过锁保证。
10. 可重复读如何解决幻读?
考察点:MVCC + 间隙锁的组合。
- 快照读的幻读:由 MVCC 解决——事务内始终读同一快照,看不到别的事务插入的新行。
- 当前读的幻读:由间隙锁(Gap Lock)+ 临键锁解决——锁住索引区间,防止别的事务插入。
-- 当前读,会加间隙锁,防止 id 在 (5,10) 之间被插入
SELECT * FROM t WHERE id > 5 AND id < 10 FOR UPDATE;因此 InnoDB 在可重复读级别下,通过「MVCC + 间隙锁」基本解决了幻读,这也是它默认级别的原因之一。
11. 行锁、间隙锁、临键锁
考察点:InnoDB 锁的三种形态。
| 锁 | 作用 | 说明 |
|---|---|---|
| 记录锁 Record Lock | 锁住单条索引记录 | 精确匹配 |
| 间隙锁 Gap Lock | 锁住记录之间的区间 | 防幻读,不含记录本身 |
| 临键锁 Next-Key Lock | 记录锁 + 间隙锁 | 左开右闭区间,InnoDB 默认 |
graph LR
subgraph 索引记录
A["id=5"] --- B["id=10"] --- C["id=15"]
end
style A fill:#e6f7e6
style B fill:#e6f7e6
style C fill:#e6f7e6- 对
id=10加临键锁 → 锁住(5, 10]。 - 间隙锁只在可重复读及以上级别生效,读已提交级别没有间隙锁。
12. 乐观锁 vs 悲观锁
考察点:并发控制的两种思想。
- 悲观锁:认为会冲突,先加锁。
SELECT ... FOR UPDATE,或表锁/行锁。 - 乐观锁:认为冲突少,更新时校验。用版本号/时间戳,CAS 思想。
-- 乐观锁:带版本号更新,失败则重试
UPDATE t SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- 影响行数为 0 表示版本已变,需重试选择:读多写少、冲突少用乐观锁;冲突激烈、强一致用悲观锁。
13. 死锁排查
考察点:死锁产生与定位。
产生:两个事务互相持有对方需要的锁。排查:
-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS;输出中 LATEST DETECTED DEADLOCK 会列出死锁涉及的事务和锁。InnoDB 会自动回滚其中一个事务(回滚代价小的)。
预防:
- 按固定顺序访问表和行。
- 尽量缩短事务。
- 避免大事务中交叉更新。
- 合理设计索引,减少锁范围。
14. SQL 执行顺序 + EXPLAIN
考察点:写 SQL 和优化都要懂执行顺序。
SELECT DISTINCT ... FROM t JOIN ... ON ...
WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...;执行顺序:FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT。
EXPLAIN 关注字段:
EXPLAIN SELECT * FROM t WHERE name = '张三';type:访问类型,system > const > eq_ref > ref > range > index > ALL,最好到 range 以上。key:实际使用的索引。rows:预估扫描行数。Extra:Using index:覆盖索引(好)。Using where:需回表过滤。Using filesort:额外排序(需优化)。Using temporary:临时表(需优化)。
15. 慢查询排查流程
考察点:线上 SQL 优化的完整套路。
- 开启慢查询日志(
long_query_time),定位慢 SQL。 - 用
EXPLAIN分析执行计划,看 type/key/rows/Extra。 - 优化手段:加索引、覆盖索引、改写 SQL、避免大表 join、分页优化。
- 观察是否锁等待、大事务、热点行。
- 必要时分库分表。
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录16. 深分页 + order by 优化
考察点:经典性能陷阱。
深分页问题:LIMIT 1000000, 10 会扫描并丢弃前 100 万条,越来越慢。
-- 慢:需要回表 100 万条
SELECT * FROM t ORDER BY id LIMIT 1000000, 10;
-- 优化1:延迟关联,先查主键再回表
SELECT * FROM t a
INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) b
ON a.id = b.id;
-- 优化2:游标分页,记录上次最大 id(适合连续翻页)
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;order by 优化:排序字段走索引可避免 Using filesort。
17. 主从复制原理与延迟
考察点:高可用核心,画图必考。
graph LR
M[主库] -->|写 binlog| B[binlog]
B -->|IO线程拉取| R[从库 relay log]
R -->|SQL线程重放| D[从库数据]
style M fill:#e8f4fd
style B fill:#fff3cd
style R fill:#e6f7e6流程:主库写 binlog → 从库 IO 线程拉取到 relay log → 从库 SQL 线程重放。
延迟原因:从库单线程重放慢、大事务、网络延迟、从库负载高。 解决:并行复制、半同步复制、读写分离时关键读走主库兜底、提升从库配置。
18. binlog / redo log / undo log
考察点:三种日志的分工。
| 日志 | 类型 | 作用 |
|---|---|---|
| binlog | 逻辑日志 | 主从复制、数据恢复(Server 层) |
| redo log | 物理日志 | 崩溃恢复,保证持久性(InnoDB,WAL) |
| undo log | 逻辑日志 | 事务回滚 + MVCC |
- redo log 是循环写(固定大小),binlog 是追加写。
- 事务提交先写 redo log(WAL,Write-Ahead Logging)再写 binlog。
19. 一条 UPDATE 的执行流程(两阶段提交)
考察点:高频难题,体现对整体架构的理解。
UPDATE t SET name = '李四' WHERE id = 1;graph TD
A[执行器找到行] --> B[InnoDB 改内存数据页]
B --> C[写 undo log]
C --> D[写 redo log prepare]
D --> E[写 binlog]
E --> F[redo log commit]
style F fill:#e6f7e6两阶段提交:redo log 先 prepare → 写 binlog → redo log commit。
为什么?为保证 redo log 和 binlog 一致性:
- 先写 redo 后写 binlog:崩溃可能 binlog 缺失,主从不一致。
- 先写 binlog 后写 redo:崩溃可能 redo 缺失,事务丢失。
- 两阶段提交保证两者要么都成功、要么都失败。
20. 分库分表
考察点:大数据量下的演进方案。
拆分方式:
- 垂直拆分:按业务拆库(用户库、订单库)、按列拆表(大字段单独表)。
- 水平拆分:按行拆(如按 user_id 哈希),单表数据量降下来。
水平拆分策略:范围分片、哈希分片、一致性哈希。
带来的问题:
- 分布式事务(见分布式事务专题)。
- 跨库 JOIN 困难(应用层拼装)。
- 全局唯一 id(雪花算法)。
- 分页、聚合统计跨节点计算。
中间件:ShardingSphere、MyCAT。
-- 常见:按用户 id 取模路由
-- 分 4 库,库下标 = user_id % 4附:高频易错点速记
- B+ 树叶子节点是双向链表,所以支持范围查询。
- InnoDB 必须有主键,无则生成隐藏 rowid。
- 可重复读是默认级别,靠 MVCC + 间隙锁解决幻读。
- 覆盖索引的 Extra 是
Using index,Using index condition是索引下推。 - 深分页用延迟关联或游标分页。
- 两阶段提交解决 redo log 与 binlog 一致性。