MySQL 8.0 面试题精选

生成时间：2026-06-07 23:43 | 共 10 题

1. InnoDB 架构：Buffer Pool 的三大链表及作用

难度：⭐⭐

题目： 请简述 InnoDB Buffer Pool 中 Free List、LRU List 和 Flush List 的作用，以及 InnoDB 对 LRU 算法做了什么改进？

标准答案：

Free List：管理空闲页，数据库启动时所有页都在 Free List 上；当需要读取新页时从此链表获取空闲页。
LRU List：管理已使用的页，采用改良的 LRU 算法。InnoDB 将 LRU 分为 Young 区（热数据，前 5/8） 和 Old 区（冷数据，后 3/8），新读入的页先放入 Old 区头部，如果在 innodb_old_blocks_time（默认 1 秒）内再次被访问，才移入 Young 区头部。这有效防止全表扫描等操作将热数据挤出 Buffer Pool。
Flush List：管理脏页（已修改但未刷盘的页），按修改时间排序。后台线程根据 Flush List 将脏页刷回磁盘，保证数据持久性。

延伸思考： 如果线上出现 Buffer Pool 命中率骤降，你会从哪些角度排查？提示：关注全表扫描、大事务、innodb_old_blocks_time 配置。

2. 索引优化：覆盖索引与索引下推

难度：⭐⭐

题目： 什么是覆盖索引和索引下推（ICP）？它们各自如何提升查询性能？请举例说明。

标准答案：

覆盖索引：查询所需的所有列都包含在索引中，无需回表查询聚簇索引。例如 SELECT name, age FROM users WHERE age = 25，若在 (age, name) 上建立联合索引，则直接从索引获取数据，避免回表。
索引下推（Index Condition Pushdown，ICP）：MySQL 5.6+ 引入，将部分 WHERE 条件下推到存储引擎层在索引遍历时过滤，减少回表次数。例如联合索引 (a, b)，查询 WHERE a = 1 AND b LIKE '%abc%'，无 ICP 时先通过 a=1 找出所有行再回表判断 b；有 ICP 时在索引中直接判断 b LIKE '%abc%'，不满足则跳过，大幅减少回表。

延伸思考： ICP 在什么场景下不会生效？提示：聚簇索引、全文索引、子查询条件下不适用。

3. 事务隔离级别与 MVCC

难度：⭐⭐⭐

题目： MySQL 8.0 默认的事务隔离级别是什么？请详细说明 InnoDB 如何通过 MVCC 机制实现 RC 和 RR 两个隔离级别，两者的 Read View 有何区别？

标准答案：

MySQL 8.0 默认隔离级别为 REPEATABLE READ（RR）。

InnoDB 的 MVCC 核心机制：

隐藏列：每行数据包含 DB_TRX_ID（最后修改的事务 ID）、DB_ROLL_PTR（回滚指针，指向 undo log 中的前版本）。
Undo Log 版本链：通过 DB_ROLL_PTR 将数据的多个版本串成链表。
Read View：事务执行查询时创建的"快照"，包含：
- m_ids：生成 Read View 时当前活跃（未提交）的事务 ID 列表
- min_trx_id：活跃事务最小 ID
- max_trx_id：下一个即将分配的事务 ID
- creator_trx_id：创建该 Read View 的事务 ID

RC 与 RR 的核心区别：

RC（READ COMMITTED）：每次 SELECT 都生成新的 Read View，因此能看到其他事务已提交的最新数据。
RR（REPEATABLE READ）：只在事务首次 SELECT 时生成 Read View，后续复用，保证同一事务内多次读取结果一致。

可见性判断规则：若 DB_TRX_ID < min_trx_id，则可见；若 DB_TRX_ID >= max_trx_id，不可见；若在 m_ids 中，不可见（未提交）；不在 m_ids 中且 < max_trx_id，可见（已提交）。

延伸思考： RR 级别下能否完全避免幻读？提示：快照读可以，当前读需要 Gap Lock 配合。

4. 锁机制：行锁、间隙锁、临键锁与意向锁

难度：⭐⭐⭐

题目： 请解释 InnoDB 中行锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）和意向锁（Intention Lock）各自的作用与关系，并说明在 RR 隔离级别下如何防止幻读。

标准答案：

Record Lock：锁定索引记录本身，只在命中索引时生效。
Gap Lock：锁定索引记录之间的间隙（开区间），防止其他事务在间隙中插入新记录。仅在 RR 级别生效。
Next-Key Lock：Record Lock + Gap Lock 的组合（左开右闭区间），是 InnoDB 在 RR 级别下默认加的行锁类型。
Intention Lock：表级锁，分为意向共享锁（IS）和意向排他锁（IX）。事务在加行锁前先加对应的意向锁，用于快速判断表中是否存在行锁，避免逐行检查。意向锁之间兼容，意向锁与表级 S/X 锁互斥。

RR 防幻读机制：通过 Next-Key Lock 锁住查询范围及间隙，阻止其他事务在该范围内插入新行。例如 SELECT * FROM t WHERE id > 5 FOR UPDATE，会在 (5, +∞) 范围加 Next-Key Lock，其他事务无法插入 id > 5 的新记录。

延伸思考： Gap Lock 在什么场景下可能造成死锁？如何通过降低隔离级别或调整索引来避免？

5. SQL 优化：EXPLAIN 执行计划分析

难度：⭐⭐

题目： 使用 EXPLAIN 分析 SQL 时，type 列的 const、eq_ref、ref、range、index、ALL 分别代表什么？优化时应该关注哪些关键字段？

标准答案：

type 值	含义	性能
`const`	通过主键/唯一索引匹配一行	最优
`eq_ref`	关联查询中，对每行通过主键/唯一索引匹配一行	优
`ref`	使用非唯一索引匹配多行	良
`range`	索引范围扫描（BETWEEN, >, <, IN）	中
`index`	全索引扫描	较差
`ALL`	全表扫描	最差

重点关注的 EXPLAIN 字段：

type：访问类型，目标至少达到 range
key：实际使用的索引，若为 NULL 说明未命中索引
rows：预估扫描行数，越少越好
filtered：过滤比例，100% 最佳
Extra：额外信息，Using index 表示覆盖索引，Using filesort/Using temporary 需重点优化
possible_keys：可能用到的索引，与 key 对比判断索引选择是否合理

延伸思考： 当 possible_keys 有多个索引但 key 选择不理想时，如何干预优化器？提示：FORCE INDEX、optimizer_switch。

6. 主从复制：GTID 与多线程复制

难度：⭐⭐

题目： MySQL 8.0 中基于 GTID 的主从复制相比传统基于 binlog position 的复制有哪些优势？多线程复制（MTS）有哪几种模式？

标准答案：

GTID 优势：

全局唯一标识：每个事务有唯一 server_uuid:transaction_id，自动定位复制位点，无需手动指定 binlog 文件和 position。
故障恢复简化：从库崩溃重启后自动跳过已执行的事务，GTID_NEXT 机制保证事务不重复执行。
主从切换便捷：新主库的 GTID 集合自动包含所有已提交事务，从库自动对齐，简化了 failover 流程。
一致性校验：通过 gtid_executed 和 gtid_purged 可快速判断主从数据一致性。

多线程复制（MTS）模式：

模式	说明
`DATABASE`（默认）	按库并行，不同库的事务可并行回放，同库串行
`LOGICAL_CLOCK`	基于组提交的并行，同一组提交的事务可并行回放，不受库级限制
`WRITESET`（MySQL 8.0.14+）	基于行级依赖检测，无数据冲突的事务均可并行，粒度最细，效率最高

延伸思考： 开启 WRITESET 模式需要什么前提条件？对 binlog 格式有什么要求？

7. 分库分表：方案选型与跨分片查询

难度：⭐⭐⭐

题目： 什么场景下需要分库分表？常见的分片策略有哪些？跨分片的聚合查询和分页查询如何实现？有哪些业界常见的中间件？

标准答案：

分库分表场景：

单表数据量超过 2000 万行（或数据文件超过 20GB），B+ 树层级增加导致查询变慢
并发量超过单库承载能力（QPS > 5000~10000）
单机 I/O 或 CPU 成为瓶颈

分片策略：

策略	特点	适用场景
Hash 取模	数据分布均匀，扩容需迁移数据	用户 ID 等离散键
Range 范围	天然支持范围查询，易热点	时间维度数据
一致性哈希	扩容迁移量少	频繁扩缩容
基因法	将分片信息嵌入 ID	多维度查询

跨分片查询方案：

聚合查询：各分片并行执行 → 汇总层合并。如 SUM 需各分片求和再汇总，ORDER BY 需各分片排序后归并排序。
分页查询：深度分页问题严重。常用方案：(1) 各分片取前 N 条汇总排序；(2) 游标分页（记住上次最大 ID）；(3) 二次查询法。
全局唯一 ID：Snowflake、号段模式、UUID 等。

常见中间件： ShardingSphere、MyCat、Vitess、TiDB（NewSQL 替代方案）。

延伸思考： 分库分表后如何处理跨片事务？2PC、TCC、Saga 各自的适用场景和局限是什么？

8. 窗口函数：排名与累计计算

难度：⭐⭐

题目： MySQL 8.0 引入了窗口函数，请说明 ROW_NUMBER()、RANK()、DENSE_RANK() 三者的区别，并给出一个计算各部门薪资累计总和的 SQL 示例。

标准答案：

三种排名函数区别：

sql

-- 假设薪资数据: A=10000, B=10000, C=8000

ROW_NUMBER() -- 1, 2, 3  （严格递增，同值不同号）
RANK()       -- 1, 1, 3  （同值同号，跳号）
DENSE_RANK() -- 1, 1, 2  （同值同号，不跳号）

部门薪资累计总和：

sql

SELECT
    dept_id,
    emp_name,
    salary,
    SUM(salary) OVER (
        PARTITION BY dept_id
        ORDER BY salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employees
ORDER BY dept_id, salary;

窗口函数核心语法：函数名() OVER (PARTITION BY ... ORDER BY ... 窗口框架)

常用窗口框架：ROWS BETWEEN start AND end，其中 start/end 可以是 UNBOUNDED PRECEDING、n PRECEDING、CURRENT ROW、n FOLLOWING、UNBOUNDED FOLLOWING。

延伸思考： ROWS 和 RANGE 窗口框架的区别是什么？在处理重复值时有何不同表现？

9. CTE 与 JSON 支持

难度：⭐⭐

题目： (1) 什么是 CTE（公用表表达式）？递归 CTE 如何实现层级查询？(2) MySQL 8.0 的 JSON 类型支持哪些常用函数？如何为 JSON 列创建索引？

标准答案：

(1) CTE 与递归 CTE：

CTE（Common Table Expression）是通过 WITH 子句定义的临时命名结果集，比子查询更清晰，可多次引用。

sql

-- 递归 CTE：查询员工层级树
WITH RECURSIVE emp_hierarchy AS (
    -- 锚点：顶级管理者
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归：逐层展开
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    INNER JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy ORDER BY level;

递归 CTE 必须包含锚点成员（非递归部分）和递归成员，通过 UNION ALL 连接，MySQL 通过 cte_max_recursion_depth（默认 1000）限制递归深度。

(2) JSON 函数与索引：

函数	用途
`JSON_EXTRACT(doc, '$.key')`	提取值
`->>`	等价于 `JSON_UNQUOTE(JSON_EXTRACT(...))`
`JSON_CONTAINS(doc, val)`	是否包含指定值
`JSON_ARRAY / JSON_OBJECT`	构造 JSON
`JSON_TABLE()`	将 JSON 数组展开为关系表

JSON 列索引方案：

sql

-- 方案1：生成列 + 普通索引
ALTER TABLE orders ADD COLUMN cust_id VARCHAR(32)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(info, '$.customer_id'))) STORED,
    ADD INDEX idx_cust_id(cust_id);

-- 方案2：MySQL 8.0.17+ 多值索引
ALTER TABLE tags ADD INDEX idx_tag_values(
    (CAST(tag_list AS CHAR(32) ARRAY))
);

延伸思考： JSON 字段适合存储什么类型的数据？过度使用 JSON 列可能带来哪些问题？

10. 性能调优：慢查询分析与参数优化

难度：⭐⭐⭐

题目： 线上 MySQL 出现性能瓶颈，请描述你的排查和调优思路，包括慢查询定位、关键参数调整和监控手段。

标准答案：

一、慢查询定位：

开启慢查询日志：slow_query_log = ON，long_query_time = 1（秒）
使用 mysqldumpslow 或 Percona pt-query-digest 分析 Top N 慢 SQL
通过 performance_schema.events_statements_summary_by_digest 实时定位高频/高耗时 SQL
sys 库：sys.statements_with_runtimes_in_95th_percentile、sys.schema_index_usage_statistics

二、关键参数调优：

参数	建议值	说明
`innodb_buffer_pool_size`	物理内存 60%~80%	最重要参数，缓存数据和索引
`innodb_io_capacity`	SSD: 10000~20000	控制后台刷脏页速率
`innodb_flush_method`	`O_DIRECT`	避免 OS 页缓存双重缓存
`innodb_log_file_size`	1G~4G	redo log 大小，影响检查点频率
`innodb_flush_log_at_trx_commit`	1（安全）/ 2（折中）	1 每次提交刷盘，2 每秒刷
`sync_binlog`	1（安全）/ 100（性能）	binlog 同步策略
`innodb_adaptive_hash_index`	按需关闭	高并发读写混合场景可能争用

三、监控手段：

SHOW ENGINE INNODB STATUS：查看死锁、锁等待、Buffer Pool 命中率
performance_schema：等待事件、文件 I/O、锁统计
sys 库视图：预置的高可读性监控视图
Prometheus + Grafana + mysqld_exporter：持续监控趋势
information_schema.INNODB_TRX：长事务排查

四、常见优化手法：

索引优化（覆盖索引、避免索引失效）
大事务拆分，避免长事务持有锁
分页优化（游标分页代替深度 OFFSET）
读写分离，主从分流
热点数据缓存（Redis 层）

延伸思考： innodb_flush_log_at_trx_commit = 2 和 sync_binlog = 100 在什么业务场景下可以接受？如何评估数据丢失风险？

📌 总结：MySQL 8.0 面试核心考点覆盖 InnoDB 架构（Buffer Pool 三链表）、索引优化（覆盖索引 + ICP）、MVCC（Read View 差异）、锁机制（Next-Key Lock 防幻读）、SQL 调优（EXPLAIN）、复制（GTID + MTS）、分库分表、窗口函数、CTE/JSON、性能调优十大方向。

MySQL 8.0 面试题精选 ​

1. InnoDB 架构：Buffer Pool 的三大链表及作用 ​

2. 索引优化：覆盖索引与索引下推 ​

3. 事务隔离级别与 MVCC ​

4. 锁机制：行锁、间隙锁、临键锁与意向锁 ​

5. SQL 优化：EXPLAIN 执行计划分析 ​

6. 主从复制：GTID 与多线程复制 ​

7. 分库分表：方案选型与跨分片查询 ​

8. 窗口函数：排名与累计计算 ​

9. CTE 与 JSON 支持 ​

10. 性能调优：慢查询分析与参数优化 ​

MySQL 8.0 面试题精选

1. InnoDB 架构：Buffer Pool 的三大链表及作用

2. 索引优化：覆盖索引与索引下推

3. 事务隔离级别与 MVCC

4. 锁机制：行锁、间隙锁、临键锁与意向锁

5. SQL 优化：EXPLAIN 执行计划分析

6. 主从复制：GTID 与多线程复制

7. 分库分表：方案选型与跨分片查询

8. 窗口函数：排名与累计计算

9. CTE 与 JSON 支持

10. 性能调优：慢查询分析与参数优化