MySQL 高频 20 题详解

2026-11-01

数据库是后端面试权重最高的板块之一。每道题按「考察点 → 答案 → SQL / 图」展开,建议配合 MySQL面试题.md(速查版)一起复习。


1. 索引数据结构:为什么是 B+ 树?

考察点:理解索引选型的本质——磁盘 IO。

数据库索引存在磁盘上,磁盘 IO 才是性能瓶颈,因此选型的核心是尽量降低树高、减少 IO 次数。

结构问题
二叉/红黑树树太高,深度大,磁盘 IO 次数多
哈希等值查询 O(1),但不支持范围、排序
B 树非叶子节点也存数据,单页存不了多少 key,树变高
B+ 树非叶子只存 key,叶子存全部数据且有序链表相连,支持范围查询

B+ 树优势:

  1. 非叶子节点不存数据,单页能存更多索引,树更矮,IO 更少。
  2. 叶子节点用双向链表连接,天然支持范围查询和排序。
  3. 查询效率稳定,每次都是树高次 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:#e6f7e6

2. 聚集索引 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 条。

  1. 违反最左前缀原则。
  2. 对索引列做函数或运算:WHERE age + 1 = 20、WHERE DATE(create_time) = '2024-01-01'。
  3. 隐式类型转换:字符串列用数字查 WHERE phone = 13800000000。
  4. LIKE '%xx' 以 % 开头。
  5. 使用 !=、<>、NOT IN。
  6. OR 连接非索引列(需两边都是索引列才走索引)。
  7. 优化器认为全表扫描更快(数据量小或返回占比大)。
-- 反例
SELECT * FROM t WHERE age + 1 = 20;        -- 函数运算
SELECT * FROM t WHERE name LIKE '%三';     -- 前导 %
SELECT * FROM t WHERE phone = 13800000000; -- phone 是 varchar,隐式转换

6. 索引设计原则

考察点:工程经验,聊「你怎么建索引」时用。

  1. 区分度高(选择性高)的列优先建索引。
  2. 联合索引把区分度高、常查询的列放最左。
  3. 高频查询尽量用覆盖索引,避免回表。
  4. 频繁更新的列慎建索引(维护成本)。
  5. 大字段(text/blob)不建普通索引。
  6. 主键建议用自增:聚集索引顺序插入,减少页分裂,二级索引叶子存主键也更小。
  7. 控制单表索引数量,避免写放大。

7. 事务 ACID 与隔离级别

考察点:四大特性 + 四种级别 + 各自问题。

  • ACID:原子性、一致性、隔离性、持久性。
隔离级别脏读不可重复读幻读
读未提交有有有
读已提交无有有
可重复读(默认)无无基本解决
串行化无无无
  • 脏读:读到别的事务未提交的数据。
  • 不可重复读:同一事务两次读同一行不一致(被修改)。
  • 幻读:同一事务两次查询行数不一致(被插入/删除)。

8. MVCC 原理

考察点:多版本并发控制的底层实现。

MVCC 依赖两样东西:

  1. 隐藏字段:每行有 trx_id(最近修改事务 ID)、roll_pointer(回滚指针,指向 undo log)。
  2. 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:#f0f0f0

Read 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 会自动回滚其中一个事务(回滚代价小的)。

预防:

  1. 按固定顺序访问表和行。
  2. 尽量缩短事务。
  3. 避免大事务中交叉更新。
  4. 合理设计索引,减少锁范围。

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 优化的完整套路。

  1. 开启慢查询日志(long_query_time),定位慢 SQL。
  2. 用 EXPLAIN 分析执行计划,看 type/key/rows/Extra。
  3. 优化手段:加索引、覆盖索引、改写 SQL、避免大表 join、分页优化。
  4. 观察是否锁等待、大事务、热点行。
  5. 必要时分库分表。
-- 开启慢查询日志
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 哈希),单表数据量降下来。

水平拆分策略:范围分片、哈希分片、一致性哈希。

带来的问题:

  1. 分布式事务(见分布式事务专题)。
  2. 跨库 JOIN 困难(应用层拼装)。
  3. 全局唯一 id(雪花算法)。
  4. 分页、聚合统计跨节点计算。

中间件:ShardingSphere、MyCAT。

-- 常见:按用户 id 取模路由
-- 分 4 库,库下标 = user_id % 4

附:高频易错点速记

  1. B+ 树叶子节点是双向链表,所以支持范围查询。
  2. InnoDB 必须有主键,无则生成隐藏 rowid。
  3. 可重复读是默认级别,靠 MVCC + 间隙锁解决幻读。
  4. 覆盖索引的 Extra 是 Using index,Using index condition 是索引下推。
  5. 深分页用延迟关联或游标分页。
  6. 两阶段提交解决 redo log 与 binlog 一致性。