第 43 章

高频面试题精讲

MySQL 面试题大全

本章收录 100+ 道高频 MySQL 面试题，按考察方向分类，每题附详细解析与面试官视角点评。难度标签：初级中级高级。

**使用建议：**建议先自己思考再展开答案。面试时不要只背结论，要能说出"为什么"和"在什么场景下例外"。

一、索引类（25 题）

初级Q1. MySQL 索引的底层数据结构是什么？为什么用 B+Tree 而不是 B-Tree 或哈希？

**答：**InnoDB 使用 B+Tree。关键区别：

**vs 哈希：**哈希 O(1) 等值查找，但不支持范围查询、排序、前缀匹配。MySQL Memory 引擎用哈希，InnoDB 不用。
**vs B-Tree：**B-Tree 非叶子节点也存数据，导致每个节点能放的 key 更少，树更高，I/O 更多。B+Tree 非叶子节点只存 key，扇出更大（InnoDB 16KB 页一般 1000+ key），3-4 层覆盖亿级数据。
**叶子链表：**B+Tree 叶子节点通过双向链表连接，范围查询只需找到起点后顺序扫描，极为高效。

面试官想听的深度：能说出 InnoDB 页大小 16KB、B+Tree 高度 = log_m(N)、为什么磁盘 I/O 决定数据库索引选型。

初级Q2. 聚簇索引和非聚簇索引的区别？InnoDB 里的回表是什么？

**聚簇索引（Clustered Index）：**InnoDB 将数据行与主键索引存储在同一个 B+Tree 中。叶子节点直接存储完整行数据。每张表只有一个聚簇索引。

**二级索引（Secondary Index）：**叶子节点存储的是主键值而非行数据。

**回表（Index Lookup）：**通过二级索引找到主键后，再去聚簇索引查完整行数据的过程。如果 EXPLAIN 看到 Using index（覆盖索引），说明不需要回表。

-- 需要回表：age 是二级索引，但要查 name 字段
SELECT name FROM users WHERE age = 25;

-- 覆盖索引，不回表：INDEX(age, name)
SELECT name FROM users WHERE age = 25;

追问：为什么 MyISAM 没有回表问题？（因为 MyISAM 所有索引都是非聚簇索引，叶子存物理地址，数据文件和索引文件分离）

中级Q3. 联合索引最左前缀原则是什么？哪些查询可以用到 INDEX(a,b,c)？

B+Tree 联合索引按 (a, b, c) 顺序排列：先按 a 排，a 相同则按 b 排，b 相同则按 c 排。

查询条件	能否用索引	说明
WHERE a=1	✅ 用 a	前缀匹配
WHERE a=1 AND b=2	✅ 用 a,b	前缀匹配
WHERE a=1 AND b=2 AND c=3	✅ 用 a,b,c	全部使用
WHERE a=1 AND c=3	⚠️ 只用 a	b 断开，c 无法用
WHERE b=2	❌	缺少 a 前缀
WHERE a=1 AND b>2 AND c=3	⚠️ 用 a,b（范围断链）	b 范围查询后 c 无法用（MySQL 8.0 MRR/ICP 部分优化）
WHERE a=1 ORDER BY b	✅ 用 a,b 且避免 filesort	利用索引有序性

MySQL 8.0 引入 Index Skip Scan，在某些场景可跳过前缀列，但限制较多，不能当通用规则记。

中级Q4. 列举 10 种常见的索引失效场景

对索引列使用函数：WHERE YEAR(create_time) = 2024 → 改为范围查询
**隐式类型转换：**字段 VARCHAR 但条件用整数 WHERE phone = 13812345678
LIKE 左模糊：WHERE name LIKE '%张'（右模糊 '张%' 可用索引）
OR 连接非索引列：WHERE a=1 OR b=2（b 无索引时全表扫）
**NOT IN / NOT EXISTS：**优化器通常选全表扫
**范围条件后的列：**联合索引 (a,b,c)，WHERE a>1 AND b=2 中 b 用不上
**字符集不匹配：**JOIN 两表字段 collation 不同导致类型转换
IS NULL / IS NOT NULL：（视数据分布，低选择性时优化器可能放弃）
**优化器 cost 估算放弃：**返回行数超过全表 30%，优化器认为全表更快
索引列参与运算：WHERE id + 1 = 100 而非 WHERE id = 99

中级Q5. 什么是覆盖索引？如何设计？

覆盖索引：查询所需的所有列都包含在索引中，无需回表。EXPLAIN 中 Extra 列显示 Using index。

-- 原始查询，需要回表
SELECT user_id, status, amount FROM orders WHERE user_id = 100;

-- 创建覆盖索引（查询字段 + 条件字段全在索引里）
CREATE INDEX idx_uid_status_amt ON orders(user_id, status, amount);
-- 现在 Extra = "Using index"，零回表

**设计原则：**把 WHERE 条件列放前面，SELECT 的列追加到后面。但不要无限追加字段——索引越宽，写入越慢，维护成本越高。

追问：索引下推（Index Condition Pushdown，ICP）是什么？— 存储引擎层在遍历索引时就过滤掉不满足条件的行，减少回表次数。MySQL 5.6+ 默认开启。

高级Q6. 为什么主键推荐用自增整数而不是 UUID？

InnoDB 聚簇索引按主键顺序存储数据。

**自增整数：**新行总是追加到 B+Tree 最右边叶子节点，页分裂极少，写入性能好，主键值小（8 字节），所有二级索引叶子也更小。
**UUID（随机）：**新行随机插入 B+Tree 中间位置，触发频繁页分裂，碎片严重，写入性能差约 3-5 倍。每个二级索引叶子存 16 字节 UUID 而非 8 字节 BIGINT，空间翻倍。

如果必须用 UUID（分布式场景防碰撞），用 UUID v7（时间有序） 或 雪花 ID，保证大致递增。

二、事务与锁类（25 题）

初级Q7. MySQL 事务的 ACID 是什么？InnoDB 如何实现？

特性	含义	InnoDB 实现机制
A 原子性	事务要么全提交，要么全回滚	Undo Log — 回滚时按 Undo 记录逆向操作
C 一致性	事务前后数据满足约束（外键、唯一等）	依赖 AID + 应用层约束
I 隔离性	并发事务互不干扰	MVCC + 锁（行锁/间隙锁/Next-Key Lock）
D 持久性	提交后数据不丢失	Redo Log — WAL 机制，crash 后重放

中级Q8. 四种事务隔离级别的区别？MySQL 默认是哪种？

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	✅ 会	✅ 会	✅ 会	最高
READ COMMITTED	❌	✅ 会	✅ 会	高
REPEATABLE READ（默认）	❌	❌	⚠️ 基本解决	中
SERIALIZABLE	❌	❌	❌	最低

MySQL 默认 REPEATABLE READ。InnoDB 通过 Next-Key Lock 在 RR 级别解决了大部分幻读，但快照读（SELECT）和当前读（SELECT FOR UPDATE）混用时仍可能出现幻读。

高频追问：RR 隔离级别下幻读真的完全解决了吗？— 不是。事务 A 快照读不到新行，但 UPDATE 这些行后再读就能看到（当前读触发），算是"幻读"的变种。

中级Q9. 行锁、间隙锁、Next-Key Lock 的区别？什么时候会锁表？

**行锁（Record Lock）：**锁定索引中的某一行。
**间隙锁（Gap Lock）：**锁定两个索引值之间的区间，防止幻读插入。RR 级别才有。
**Next-Key Lock：**行锁 + 其左侧间隙锁的组合，InnoDB 默认的锁粒度。

-- id 有索引，已有 id = 10, 20, 30
BEGIN;
SELECT * FROM t WHERE id = 15 FOR UPDATE;
-- 加 Next-Key Lock: (10, 20] 区间 + id=15 行锁（实际不存在）
-- 另一会话 INSERT id=17 会阻塞

**何时表锁：**① 无索引或索引失效的 UPDATE/DELETE（行锁升级为全表锁）② LOCK TABLES ③ DDL ④ MyISAM 引擎

实战：线上遇到大量行锁等待，先查 SELECT * FROM performance_schema.data_locks\G 看谁持有锁，再找对应事务。

中级Q10. 什么是 MVCC？InnoDB 如何实现？

MVCC（Multi-Version Concurrency Control）：为每行数据维护多个版本，读操作看到一致性快照，不阻塞写操作。

实现要素：

**隐藏列：**每行有 DB_TRX_ID（最后修改的事务 ID）、DB_ROLL_PTR（指向 Undo Log 链）
**Read View：**快照读时创建，记录当前活跃事务列表。根据 DB_TRX_ID 与 Read View 的关系判断版本可见性。
**Undo Log：**旧版本数据链，Read View 需要更早版本时沿链回溯。

RC 每次 SELECT 创建新 Read View，RR 在事务开始时创建一次 Read View 并复用。

高级Q11. 死锁是如何产生的？如何预防和排查？

典型死锁场景（循环等待）：

-- 事务 A          -- 事务 B
BEGIN;              BEGIN;
UPDATE t SET ...    UPDATE t SET ...
  WHERE id=1;         WHERE id=2;  -- A 持有 1，B 持有 2
UPDATE t SET ...    UPDATE t SET ...
  WHERE id=2;  -- A 等 B      WHERE id=1;  -- B 等 A → 死锁

**InnoDB 自动检测：**检测到死锁时自动回滚代价小的事务（Undo 记录少的）。

预防方法：

固定加锁顺序（所有业务逻辑按相同顺序操作资源）
缩短事务，减少持锁时间
将大事务拆分，批量操作加 LIMIT
避免用户等待在事务中（不在事务里等 HTTP 响应）

排查：SHOW ENGINE INNODB STATUS\G 查 LATEST DETECTED DEADLOCK 节，或开启 innodb_print_all_deadlocks=ON 写入 error log。

三、查询优化类（20 题）

初级Q12. EXPLAIN 输出中最重要的字段是哪些？如何判断查询是否有问题？

字段	关注点	危险信号
type	访问类型	ALL（全表扫）、index（全索引扫）
key	实际使用的索引	NULL 表示未用索引
rows	预估扫描行数	远大于返回行数
Extra	附加信息	Using filesort、Using temporary
filtered	条件过滤率	低值（如 1%）说明索引选择性差

type 优先级（好到差）：system > const > eq_ref > ref > range > index > ALL

中级Q13. 深分页（LIMIT 1000000, 10）为什么慢？如何优化？

LIMIT 1000000, 10 需要扫描并丢弃前 100 万行，即使有索引也要 100 万次回表。

优化方案 1 — 延迟关联：

-- 先用覆盖索引找主键，再 JOIN 拿完整行
SELECT t.* FROM t
INNER JOIN (
  SELECT id FROM t ORDER BY create_time LIMIT 1000000, 10
) sub ON t.id = sub.id;

优化方案 2 — 游标分页（推荐）：

-- 记录上次最后一条的 create_time + id
SELECT * FROM t
WHERE (create_time, id) > ('2024-01-15 10:30:00', 99980)
ORDER BY create_time, id
LIMIT 10;

方案 2 最优，但需要前端配合传入 cursor，不支持随机跳页。

中级Q14. COUNT(*) / COUNT(1) / COUNT(id) 哪个快？

**结论：**在 InnoDB 中 COUNT(*) = COUNT(1)，官方推荐 COUNT(*)，优化器会自动选择最小的二级索引扫描。

COUNT(id) 稍慢，因为要判断 id 是否为 NULL（主键不会 NULL，但引擎层需要判断列值）。COUNT(非索引列) 最慢，需要回表。

追问：MyISAM 的 COUNT(*) 为什么比 InnoDB 快？— MyISAM 在表文件中维护了行数计数器，O(1) 返回。InnoDB 因为 MVCC 每个事务看到的行数不同，无法缓存，必须扫描。

中级Q15. 什么情况下 ORDER BY 会产生 filesort？如何避免？

filesort 不一定在磁盘上排序（小数据在内存里），但都意味着额外排序开销。

触发 filesort 的情况：

ORDER BY 的列没有索引
ORDER BY 使用了两个不同索引
ORDER BY 字段与 WHERE 条件字段不在同一联合索引
ORDER BY 方向混合（a ASC, b DESC），MySQL 8.0 引入降序索引解决此问题

**避免：**建立 (WHERE条件列, ORDER BY列) 的联合索引，或直接建 (ORDER BY 列) 的索引（当 WHERE 选择性高时）。

高级Q16. 如何分析和优化一条慢 SQL 的完整流程？

**EXPLAIN：**看 type、key、rows、Extra，定位扫描方式
**EXPLAIN ANALYZE（MySQL 8.0.18+）：**真实执行，看实际行数 vs 估算行数，找统计信息偏差
**检查索引：**SHOW INDEX FROM table，看 Cardinality，必要时 ANALYZE TABLE 更新
**SQL 改写：**消除 SELECT *、优化 JOIN 顺序、改写子查询为 JOIN、分页优化
**Schema 检查：**字段类型合理？字符集一致？有无隐式转换？
**系统层面：**innodb_buffer_pool_size 够大吗？有无 I/O 瓶颈？

四、InnoDB 内核类（15 题）

中级Q17. Redo Log 和 Binlog 的区别？两阶段提交是什么？

	Redo Log	Binlog
归属层	InnoDB 存储引擎层	MySQL Server 层
内容	物理日志（数据页的变更）	逻辑日志（SQL 或行变更）
大小	固定大小循环写	追加写，可归档
用途	crash recovery（崩溃恢复）	主从复制、PITR

**两阶段提交（2PC）：**为保证 Redo Log 和 Binlog 的一致性，InnoDB 提交时先写 Redo Log（prepare），再写 Binlog，再将 Redo Log 标记为 commit。crash 时按此顺序判断是否需要重做。

中级Q18. Buffer Pool 是什么？为什么它这么重要？

Buffer Pool 是 InnoDB 在内存中缓存数据页和索引页的区域。所有读写都先经过 Buffer Pool，只有脏页（修改过未落盘的页）在适当时机由后台线程刷写到磁盘。

**为什么重要：**内存 ns 级访问 vs 磁盘 ms 级。Buffer Pool 命中率通常需要 >99% 才算健康。

-- 查看 Buffer Pool 命中率
SELECT 1 - (
  (SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_reads') /
  (SELECT variable_value FROM performance_schema.global_status
   WHERE variable_name = 'Innodb_buffer_pool_read_requests')
) AS hit_ratio;

**推荐大小：**物理内存的 70-80%（专用 MySQL 服务器）。分配为 innodb_buffer_pool_size。

高级Q19. WAL（Write-Ahead Logging）机制是什么？MySQL 如何保证不丢数据？

WAL 原则：修改数据前先写日志。InnoDB 实现：

事务修改时先写 Undo Log（回滚用）
修改 Buffer Pool 中的数据页（内存，不立即落盘）
写 Redo Log Buffer（内存）
提交时，Redo Log Buffer 刷到磁盘（innodb_flush_log_at_trx_commit=1 保证每次提交 fsync）

即使服务器 crash，重启后 InnoDB 读取 Redo Log 重放未落盘的变更，读取 Undo Log 回滚未提交的事务，保证数据一致。

innodb_flush_log_at_trx_commit：0=每秒刷一次（可能丢1秒数据），1=每次提交刷（最安全），2=每次提交写OS缓冲（OS crash丢数据）。生产推荐1。

五、架构类（10 题）

中级Q20. MySQL 主从复制的原理？延迟是什么原因造成的？

复制流程：

主库事务提交，写入 Binlog
从库 IO Thread 连接主库，拉取 Binlog 写入本地 Relay Log
从库 SQL Thread 读取 Relay Log，重放执行

延迟原因：

单线程回放（5.6 以前）：主库并发写，从库单线程串行，天然有差距
大事务：一个事务 1 分钟，从库全部等这 1 分钟
从库压力：从库也承担大量读请求，CPU/IO 竞争
网络抖动

**解决：**开启并行复制（slave_parallel_workers > 0，MySQL 5.7+ LOGICAL_CLOCK 模式），拆分大事务，从库 ProxySQL 读写分离保护。

高级Q21. 分库分表的时机和方案如何选择？

**分表时机：**单表行数 > 1000 万且查询变慢，或磁盘 > 500GB 且 innodb_buffer_pool 无法全量缓存热数据。

分片方式：

**水平分片（Sharding）：**同一张表的数据分散到多个库/表，按 user_id % N、范围、hash 等
**垂直分库：**不同业务表分到不同库（微服务数据库）

**分片键选择原则：**① 查询路由率高（大部分查询携带该字段）② 数据均匀分布 ③ 不频繁修改

**中间件方案：**ShardingSphere（轻量级 JDBC/代理双模式）、Vitess（YouTube，Kubernetes 原生）

注意：分库后跨库 JOIN、分布式事务、全局唯一 ID、COUNT/GROUP BY 聚合都变得复杂，这些是真正的难点。

六、场景题（5 题）

高级Q22. 线上 CPU 突然飙高到 100%，如何排查？

SHOW PROCESSLIST 找 State 为 executing 的长时间查询
SELECT * FROM performance_schema.events_statements_current 看当前正在执行的 SQL
定位慢 SQL，EXPLAIN 分析，看是否有全表扫描
检查是否有锁等待：SELECT * FROM performance_schema.data_lock_waits
确认 Buffer Pool 命中率，低时增大 innodb_buffer_pool_size
临时：KILL 杀掉罪魁祸首查询；根本：加索引或优化 SQL

高级Q23. 如何实现高并发下的库存扣减（防超卖）？

方案 1 — 数据库乐观锁：

UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE item_id = 100 AND version = :old_version AND stock > 0;
-- 影响行数为 0 则说明被抢占，重试

方案 2 — 数据库悲观锁：

BEGIN;
SELECT stock FROM inventory WHERE item_id = 100 FOR UPDATE;
-- 检查 stock > 0 后扣减
UPDATE inventory SET stock = stock - 1 WHERE item_id = 100;
COMMIT;

**方案 3 — Redis 预扣 + 异步落库（推荐高并发）：**Redis DECR 原子扣减，成功后发消息队列，消费者异步写 MySQL。

方案 4 — 直接 WHERE stock > 0（最简单）：

UPDATE inventory SET stock = stock - 1
WHERE item_id = 100 AND stock > 0;
-- InnoDB 行锁天然串行，stock > 0 防止负数

高级Q24. 数据库连接池应该设多大？

经典公式（来自 HikariCP 作者 Brett Wooldridge）：

连接数 = (CPU核心数 * 2) + 有效磁盘数
-- 例：8核SSD服务器 = 8 * 2 + 1 = 17

大多数人设几百个连接反而适得其反：每个连接消耗 ~1MB 内存，大量上下文切换比少量连接 + 排队等待更慢。

**实践：**从 10-20 开始，观察 pool_waiting 指标，逐步增大直到等待消失。不要盲目设大。

高级Q25. 如何做 MySQL 的不停机升级（5.7 → 8.0）？

**兼容性检查：**用 mysqlcheck --upgrade 和 MySQL Shell util.checkForServerUpgrade() 扫描不兼容项
**从库先升：**搭建一台 8.0 从库，验证复制正常、业务查询正常
**滚动升级：**逐台从库升级 → 业务读流量验证 → 主从切换（从库升为主）→ 原主库升级
**回滚预案：**保持 5.7 主库在线直到确认 8.0 稳定（注意 8.0 主无法向 5.7 从复制）

重大变更：utf8mb4 默认 collation 变了（从 utf8mb4_general_ci 到 utf8mb4_0900_ai_ci），可能影响字符串比较和索引。

本章评分

4.6 / 5 (3 评分)