工作知识库

Appearance

MySQL 8.0 面试题精选 ​

生成时间：2026-06-06 20:48 | 共 10 题 覆盖：InnoDB架构、索引优化、事务隔离、MVCC、锁机制、SQL优化、主从复制、分库分表、窗口函数、CTE、JSON、性能调优

1. InnoDB 架构：Buffer Pool 的设计与优化 ​

难度：⭐⭐

题目： 请描述 InnoDB Buffer Pool 的内部结构，以及 MySQL 8.0 中对 Buffer Pool 做了哪些重要改进？在实际生产中如何合理配置 Buffer Pool 大小？

标准答案：

InnoDB Buffer Pool 是一块内存区域，用于缓存表数据和索引数据，是 InnoDB 性能的核心。其内部结构包括：

  • 页（Page）：Buffer Pool 的最小管理单位，默认 16KB。每个页可以缓存一个数据页或索引页。
  • LRU List：采用改良版 LRU 算法，将链表分为 young 区（热数据，前 5/8） 和 old 区（冷数据，后 3/8）。新读入的页先插入 old 区头部，若在 innodb_old_blocks_time（默认1秒）内被再次访问，才移入 young 区头部。这有效避免了全表扫描污染热数据。
  • Flush List：管理脏页（已修改但未刷盘的页），按修改顺序排列，用于checkpoint推进。
  • Free List：管理空闲页，新数据页优先从 Free List 获取。

MySQL 8.0 的重要改进：

  1. 多 Buffer Pool 实例（8.0已成熟）：通过 innodb_buffer_pool_instances 配置多个实例减少锁竞争，建议 Buffer Pool ≥ 1GB 时设为多个实例。
  2. Buffer Pool Dump/Load：支持在关闭时导出热数据页列表，启动时快速预热，减少冷启动影响。
  3. 动态调整大小：innodb_buffer_pool_size 支持在线动态修改，无需重启。
  4. 更细粒度的 LRU 管理：优化了淘汰策略，减少不必要的页面移动。

生产配置建议：

  • Buffer Pool 大小通常设为物理内存的 60%-80%（专用数据库服务器）
  • innodb_buffer_pool_instances 设为 CPU 核心数或 Buffer Pool 大小/1GB（取较小值）
  • 监控 Buffer_pool_wait_free 指标，若非零说明 Buffer Pool 不足

延伸思考： 如果 Buffer Pool 命中率低于 99%，可能的原因有哪些？如何通过 SHOW ENGINE INNODB STATUS 和 Performance Schema 定位问题？

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

难度：⭐⭐

题目： 什么是覆盖索引（Covering Index）和索引下推（Index Condition Pushdown, ICP）？请结合具体 SQL 示例说明它们如何减少回表次数，以及在 MySQL 8.0 中如何通过 EXPLAIN 识别它们的使用。

标准答案：

覆盖索引： 当查询所需的所有列都包含在索引中时，无需回表读取聚簇索引的数据行，直接从索引树返回结果。

sql
-- 假设有索引 idx_name_age (name, age)
SELECT name, age FROM users WHERE name = '张三';  -- 覆盖索引，无需回表
SELECT name, age, email FROM users WHERE name = '张三';  -- 需回表，email不在索引中

在 EXPLAIN 中，覆盖索引表现为 Extra 列显示 Using index。

索引下推（ICP）： MySQL 5.6+ 引入，8.0 默认开启。在没有 ICP 时，存储引擎根据最左前缀原则通过索引找到行，再由 Server 层判断 WHERE 中的其他条件。ICP 将 WHERE 中能在索引上评估的条件下推到存储引擎层，在索引遍历时就过滤掉不满足条件的行，减少回表次数。

sql
-- 联合索引 idx_name_age (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age > 25;
  • 无 ICP：存储引擎通过 name LIKE '张%' 找到所有匹配行 → 逐行回表 → Server 层过滤 age > 25
  • 有 ICP：存储引擎在索引上同时判断 name LIKE '张%' AND age > 25 → 只对满足条件的行回表

EXPLAIN 中，ICP 表现为 Extra 列显示 Using index condition。

ICP 的限制：

  • 只适用于 range、ref、eq_ref、ref_or_null 访问方法
  • InnoDB 的聚簇索引不支持 ICP（聚簇索引本身就是数据，无需回表）
  • 子查询条件不能下推

延伸思考： 对于 SELECT * FROM t WHERE a = 1 AND b LIKE '%abc%' AND c > 10，索引 (a, b, c) 和 (a, c, b) 哪个更能受益于 ICP？为什么？

3. 事务隔离级别与 MVCC ​

难度：⭐⭐⭐

题目： MySQL 8.0 默认的事务隔离级别是什么？请详细说明 InnoDB 的 MVCC 机制是如何在不同隔离级别下工作的，特别是 RR（可重复读）级别下如何避免幻读？

标准答案：

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

MVCC 核心机制：

MVCC 通过 隐藏列 + Undo Log + ReadView 实现无锁并发读：

  1. 隐藏列：每行数据包含 DB_TRX_ID（最近修改的事务ID）和 DB_ROLL_PTR（回滚指针，指向 Undo Log 中的前版本）
  2. Undo Log 版本链：通过 DB_ROLL_PTR 将行的所有历史版本串成链表
  3. ReadView：事务进行快照读时创建的"可见性判断快照"，包含：
    • m_ids：生成 ReadView 时当前活跃（未提交）的事务ID集合
    • min_trx_id：m_ids 中的最小值
    • max_trx_id：生成 ReadView 时系统应分配给下一个事务的ID
    • creator_trx_id：创建该 ReadView 的事务ID

可见性判断规则：对于版本链中的某个版本，其 DB_TRX_ID 记为 trx_id：

  • trx_id == creator_trx_id → 可见（自己修改的）
  • trx_id < min_trx_id → 可见（事务已提交）
  • trx_id >= max_trx_id → 不可见（在 ReadView 创建后才开始的事务）
  • min_trx_id <= trx_id < max_trx_id：若 trx_id 在 m_ids 中 → 不可见（事务未提交）；否则 → 可见

不同隔离级别的 ReadView 策略：

隔离级别ReadView 生成时机效果
READ COMMITTED每次 SELECT 都生成新的 ReadView能看到其他事务已提交的最新数据
REPEATABLE READ事务第一次 SELECT 时生成，后续复用事务内多次读取结果一致

RR 级别如何避免幻读：

InnoDB 在 RR 级别通过 MVCC + Next-Key Lock 双重机制避免幻读：

  • 快照读（普通 SELECT）：通过 MVCC 的 ReadView 机制，始终读取事务开始时的快照，自然不会出现幻读
  • 当前读（SELECT ... FOR UPDATE / INSERT / UPDATE / DELETE）：通过 Next-Key Lock（临键锁 = 行锁 + 间隙锁） 锁住记录及其间隙，阻止其他事务在范围内插入新行

注意： RR 级别并非完全避免幻读。特殊场景：事务A先快照读（无记录），事务B插入并提交，事务A再当前读 → 能看到新行。这是因为当前读总是读取最新已提交数据，不使用 MVCC 快照。

延伸思考： 如果在 RR 级别下，事务A执行 SELECT * FROM t WHERE id > 5 FOR UPDATE，事务B尝试 INSERT INTO t VALUES(8) 会被阻塞吗？Next-Key Lock 锁定的具体范围是什么？

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

难度：⭐⭐⭐

题目： 请详细说明 InnoDB 中行锁（Record Lock）、间隙锁（Gap Lock）、临键锁（Next-Key Lock）和意向锁（Intention Lock）的各自作用、加锁场景以及相互关系。在以下场景中分别会加什么锁？

sql
-- 表 t 有索引 idx_age (age)，数据：10, 20, 30, 40
-- 事务隔离级别：RR

-- 场景1
SELECT * FROM t WHERE age = 20 FOR UPDATE;

-- 场景2
SELECT * FROM t WHERE age = 25 FOR UPDATE;

-- 场景3
SELECT * FROM t WHERE age > 35 FOR UPDATE;

标准答案：

四种锁详解：

锁类型作用加锁对象
Record Lock锁定索引记录本身索引上的单行记录
Gap Lock锁定索引记录之间的间隙，防止其他事务在间隙中插入索引记录之间的间隙（开区间）
Next-Key LockRecord Lock + Gap Lock 的组合（左开右闭）锁住记录本身 + 前面的间隙
Intention Lock表级锁，表示事务即将对表中的行加行锁整个表

意向锁的作用： 意向锁是 InnoDB 的表级锁，分为意向共享锁（IS）和意向排他锁（IX）。其核心目的是快速判断表级锁与行级锁是否冲突，无需逐行检查。例如，事务A要对某行加X锁，需先对表加IX锁；事务B要对表加S锁，发现表上有IX锁，就知道存在行级排他锁，直接等待。

加锁分析（RR级别）：

场景1：age = 20（命中记录）

  • 加 Next-Key Lock：(10, 20] — 锁住20及前面的间隙
  • 加 Gap Lock：(20, 30) — 因为唯一索引等值查询找到记录后，Next-Key Lock退化为Record Lock，但还会向右加Gap Lock防止右间隙插入
  • 实际效果：Record Lock 锁住 age=20，Gap Lock 锁住 (10,20) 和 (20,30)
  • 注意：如果 idx_age 是唯一索引，Next-Key Lock 会退化为 Record Lock，只锁 age=20 这一行

场景2：age = 25（未命中记录，在间隙中）

  • 加 Gap Lock：(20, 30) — 锁住25所在的间隙即可
  • 阻止其他事务插入 age 在 (20, 30) 之间的任何值

场景3：age > 35（范围查询）

  • 加 Next-Key Lock：(30, 40] — 锁住40及前面的间隙
  • 加 Next-Key Lock：(40, +∞) — 锁住40到正无穷的间隙
  • 阻止插入 age > 30 的任何值

关键规则总结：

  1. 等值查询命中记录：唯一索引退化为 Record Lock；非唯一索引加 Next-Key Lock + 右侧 Gap Lock
  2. 等值查询未命中：加 Gap Lock 锁住所在间隙
  3. 范围查询：对扫描到的每条记录加 Next-Key Lock，直到不满足条件为止
  4. RC 隔离级别下没有 Gap Lock，只有 Record Lock

延伸思考： 如果 SELECT * FROM t WHERE age = 20 FOR UPDATE 中 idx_age 是唯一索引 vs 普通索引，加锁行为有何不同？如果 SQL 还涉及到主键回表，主键上会加什么锁？

5. SQL 优化：EXPLAIN 执行计划深度解读 ​

难度：⭐⭐

题目： 请解读以下 EXPLAIN 输出，分析查询性能瓶颈并给出优化建议：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | key           | ref  | rows   | filtered | Extra                     |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | orders| NULL       | ALL  | NULL          | NULL | 987654 |  1.00    | Using where; Using filesort|
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

对应 SQL：SELECT order_id, user_id, amount FROM orders WHERE status = 'PAID' AND create_time > '2026-01-01' ORDER BY amount DESC LIMIT 100;

标准答案：

执行计划解读：

字段值含义
typeALL全表扫描，最差的访问类型
keyNULL未使用任何索引
rows987654预估扫描近100万行
filtered1.00%只有1%的行满足WHERE条件
ExtraUsing where; Using filesort在Server层过滤 + 额外排序（未使用索引排序）

性能瓶颈：

  1. 全表扫描：近100万行全部读入内存，I/O开销巨大
  2. 低过滤率：仅1%有效数据，99%的无用I/O
  3. Filesort：无法利用索引排序，需要在内存中排序大量数据
  4. 综合代价：扫描100万行 → 过滤剩约1万行 → 内存排序 → 取前100条

优化方案：

方案一：联合索引（推荐）

sql
ALTER TABLE orders ADD INDEX idx_status_time_amount (status, create_time, amount);
  • status 等值过滤放在最左
  • create_time 范围过滤在第二位
  • amount 排序列在第三位（但注意：范围查询后的列无法用于排序优化）

优化后 type 应为 ref 或 range，Extra 可能仍为 Using filesort（因为 create_time 是范围查询，破坏了 amount 的排序连续性）。

方案二：覆盖索引 + 延迟关联

sql
-- 第一步：通过覆盖索引快速找到满足条件的 order_id
SELECT order_id FROM orders 
WHERE status = 'PAID' AND create_time > '2026-01-01' 
ORDER BY amount DESC LIMIT 100;

-- 第二步：根据 order_id 回表获取完整数据
SELECT o.* FROM orders o
JOIN (上述子查询) t ON o.order_id = t.order_id;

方案三：如果 status 的选择性差，考虑调整索引顺序

sql
-- 如果 create_time 的选择性更好（区间更窄）
ALTER TABLE orders ADD INDEX idx_time_status_amount (create_time, status, amount);

方案四：分区表（如果数据量持续增长）

sql
ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(create_time)) (...);

验证方法：

sql
EXPLAIN ANALYZE SELECT ...;  -- MySQL 8.0 特有，输出实际执行时间
SHOW WARNINGS;  -- 查看 optimizer 重写后的 SQL

延伸思考： 如果 status 只有 'PAID' 和 'UNPAID' 两个值且各占50%，索引 (status, create_time) 的效果如何？这种低基数列索引在什么场景下反而不如全表扫描？

6. 主从复制：GTID 复制与数据一致性 ​

难度：⭐⭐⭐

题目： MySQL 8.0 中 GTID 复制相比传统复制有哪些优势？请说明 GTID 的工作原理，以及在主从切换、数据修复场景下 GTID 如何简化运维操作。如果从库出现 Errant Transaction（孤儿事务），如何检测和修复？

标准答案：

GTID（Global Transaction Identifier）工作原理：

GTID = source_id:transaction_id，例如 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

  • source_id：MySQL 实例的 UUID
  • transaction_id：该实例上提交事务的序号

每个事务在主库提交时被分配唯一 GTID，从库执行时记录该 GTID。MySQL 维护 gtid_executed（已执行的GTID集合）和 gtid_purged（已清理的GTID集合）。

GTID 复制 vs 传统复制的优势：

维度传统复制GTID 复制
主从切换需手动定位 binlog 位点（file + position）自动根据 GTID 找到续传位点
从库提升需比较各从库的 Relay Log 位点直接比较 gtid_executed 集合
数据一致性验证需外部工具（pt-table-checksum）可通过 GTID 集合差异快速判断
重新配置复制需要备份恢复 + 位点对齐CHANGE MASTER TO MASTER_AUTO_POSITION=1 自动对齐
多源复制位点管理复杂每个 Channel 独立维护 GTID 集合

主从切换场景：

sql
-- 传统方式：需要知道精确位点
CHANGE MASTER TO MASTER_HOST='new_master', MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=4567;

-- GTID 方式：自动定位
CHANGE MASTER TO MASTER_HOST='new_master', MASTER_AUTO_POSITION=1;

Errant Transaction 检测与修复：

Errant Transaction 是指从库上直接执行了写操作（非通过复制通道），导致从库的 gtid_executed 包含主库没有的事务。

检测方法：

sql
-- 在从库执行
SELECT @@GLOBAL.gtid_executed;
-- 在主库执行
SELECT @@GLOBAL.gtid_executed;
-- 对比两个集合，从库多出的 GTID 即为 Errant Transaction

也可以使用 SHOW SLAVE STATUS 中 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的差异判断。

修复方法：

  1. 注入空事务（推荐用于事务已在从库执行但主库无此GTID）：
sql
SET GTID_NEXT='source_id:orphan_trx_id';
BEGIN; COMMIT;  -- 空事务，仅记录 GTID
SET GTID_NEXT='AUTOMATIC';
  1. 重新构建从库（Errant Transaction 影响数据时）：
bash
mysqldump --all-databases --single-transaction --set-gtid-purged=OFF | mysql -h slave
  1. MySQL 8.0 的 RESET MASTER（慎用，清空所有 GTID 历史）：
sql
-- 仅在确认从库数据可丢弃时使用
RESET MASTER;
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
START SLAVE;

延伸思考： 在 MHA 或 Orchestrator 的高可用架构中，GTID 如何影响故障切换的决策逻辑？如果发生脑裂（双主同时写入），如何通过 GTID 集合分析并修复？

7. 分库分表：ShardingSphere 实战策略 ​

难度：⭐⭐⭐

题目： 一个电商系统订单表日均增长 200 万行，当前单表已超 5000 万行，查询性能明显下降。请设计分库分表方案，包括：分片键选择、分片策略、跨分片查询处理、全局唯一ID生成、扩容方案。分库分表后如何处理以下场景？

sql
-- 场景1：用户查看自己的订单列表
SELECT * FROM orders WHERE user_id = ? ORDER BY create_time DESC LIMIT 20;

-- 场景2：商家查看自己店铺的订单
SELECT * FROM orders WHERE merchant_id = ? AND status = 'PAID';

-- 场景3：后台按时间范围统计订单金额
SELECT SUM(amount) FROM orders WHERE create_time BETWEEN ? AND ?;

标准答案：

分片键选择：

订单场景下，user_id 是最佳分片键：

  • 用户维度的查询最高频（场景1），分片键 = 查询条件可避免跨分片
  • 数据分布相对均匀
  • 同一用户的订单在同一分片，支持用户维度的聚合和排序

分片策略：

  • 分库数：根据预估数据量和并发量，建议 8-16 库
  • 分表数：每个库 4-8 张表，总计 32-128 张表
  • 分片算法：user_id % 64（假设64个分表，8库 × 8表）
    • 库序号 = (user_id / 8) % 8，表序号 = user_id % 8

全局唯一ID生成方案：

方案优点缺点
Snowflake趋势递增、性能高、无中心节点时钟回拨问题
号段模式（Leaf-segment）简单可靠、DB压力小ID不连续、需DB
UUID无需协调无序、索引性能差
MySQL 8.0 序列简单单点、性能瓶颈

推荐 Snowflake + 号段模式双保险。

跨分片查询处理：

场景1（用户订单列表）：user_id 是分片键 → 单分片查询，无需跨分片，性能最佳。

场景2（商家订单）：merchant_id 不是分片键 → 两种方案：

  • 方案A：建立 merchant_id → user_id 的映射表，先查映射得到相关 user_id，再定向查分片
  • 方案B：冗余一张以 merchant_id 为分片键的商家订单表（异构索引），通过 Canal 监听 binlog 同步

场景3（时间范围统计）：create_time 不是分片键 → 全分片扫描：

  • 方案A：ShardingSphere 的归并引擎并行查所有分片 → 内存归并排序/聚合
  • 方案B：将统计数据同步到 Elasticsearch/ClickHouse 做分析查询
  • 方案C：定时预聚合，生成日/周/月统计表

扩容方案（2倍扩容为例）：

原方案：8库 × 8表 = 64分片
扩容后：16库 × 8表 = 128分片

扩容步骤：
1. 部署新库（9-16库）
2. 采用"成倍扩容"策略：每个旧库的数据按 user_id 重新 hash 拆分到新旧两个库
3. 使用 DataX 或 ShardingSphere-Scaling 做数据迁移
4. 数据校验通过后切换流量

关键点：成倍扩容时，只需要将原分片的数据按新的 hash 规则迁移一半到新分片，不需要全量重分布。

延伸思考： 如果业务要求支持"用户合并"（两个账号合并为一个），分片键 user_id 变更后如何处理已有数据的迁移？如何设计分片方案才能支持这种业务场景？

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

难度：⭐⭐

题目： MySQL 8.0 引入了窗口函数，请说明窗口函数与普通聚合函数的区别，并使用窗口函数解决以下问题：

  1. 按部门分组，查询每个员工的薪资排名（相同薪资同排名，不跳位）
  2. 查询每个员工的累计薪资（按入职日期排序）
  3. 查询每个部门薪资前三名的员工

标准答案：

窗口函数与聚合函数的区别：

维度聚合函数窗口函数
行数多行聚合为一行每行都保留，额外计算聚合值
GROUP BY必须配合 GROUP BY使用 OVER() 定义窗口
灵活性结果行=分组数结果行=原表行数

1. 部门内薪资排名（DENSE_RANK，相同薪资同排名不跳位）

sql
SELECT 
    dept_id,
    emp_name,
    salary,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM employees;

对比三种排名函数：

  • ROW_NUMBER()：1, 2, 3, 4（严格递增，相同值不同排名）
  • RANK()：1, 1, 3, 4（相同值同排名，跳位）
  • DENSE_RANK()：1, 1, 2, 3（相同值同排名，不跳位）

2. 累计薪资（按入职日期排序的累计求和）

sql
SELECT 
    emp_name,
    hire_date,
    salary,
    SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM employees;

窗口框架说明：

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：从第一行到当前行
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：当前行及前后各一行
  • RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW：按值范围（近7天）

3. 部门薪资前三名

sql
WITH ranked AS (
    SELECT 
        dept_id,
        emp_name,
        salary,
        DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT dept_id, emp_name, salary
FROM ranked
WHERE rnk <= 3;

窗口函数完整语法：

sql
函数名() OVER (
    [PARTITION BY 分组列]          -- 可选，定义分区
    [ORDER BY 排序列]              -- 可选，定义排序
    [frame_clause]                 -- 可选，定义窗口框架
)

常用窗口函数分类：

  • 排名：ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
  • 聚合：SUM, AVG, COUNT, MAX, MIN + OVER()
  • 取值：LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE
  • 分布：CUME_DIST, PERCENT_RANK

延伸思考： 如何用窗口函数实现"同比/环比增长"计算？LAG() 和 LEAD() 函数在处理缺失月份的数据时需要注意什么？

9. CTE 与递归查询：树形结构处理 ​

难度：⭐⭐

题目： MySQL 8.0 支持公用表表达式（CTE）和递归 CTE。请说明 CTE 相比子查询的优势，并使用递归 CTE 解决以下问题：

  1. 查询组织架构中某员工的所有上级（从当前员工到CEO的路径）
  2. 查询某经理的所有下属（包括间接下属）
  3. 使用 CTE 实现分层数据的 breadth-first 和 depth-first 遍历

表结构：employees(id, name, manager_id)，manager_id 指向直属上级的 id。

标准答案：

CTE 相比子查询的优势：

维度子查询CTE
可读性嵌套层级深，难读懂逻辑扁平化，像临时视图
复用性同一子查询需重复写定义一次，引用多次
递归不支持支持递归（WITH RECURSIVE）
优化器可能多次执行物化一次（MySQL的实现）

1. 查询某员工的所有上级链

sql
WITH RECURSIVE manager_path AS (
    -- 锚点：起始员工
    SELECT id, name, manager_id, 1 AS level, CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE id = 42  -- 目标员工ID
    
    UNION ALL
    
    -- 递归：向上查找经理
    SELECT e.id, e.name, e.manager_id, mp.level + 1, 
           CONCAT(e.name, ' > ', mp.path)
    FROM employees e
    JOIN manager_path mp ON e.id = mp.manager_id
)
SELECT * FROM manager_path ORDER BY level;

2. 查询某经理的所有下属（含间接下属）

sql
WITH RECURSIVE subordinates AS (
    -- 锚点：指定经理
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 1  -- 经理ID
    
    UNION ALL
    
    -- 递归：向下查找下属
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates WHERE id != 1  -- 排除经理自己
ORDER BY depth;

3. BFS vs DFS 遍历

sql
-- Breadth-First（广度优先）：按层级遍历
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level, 
           CAST(id AS CHAR(1000)) AS bfs_order
    FROM employees WHERE manager_id IS NULL  -- CEO
    UNION ALL
    SELECT e.id, e.name, e.manager_id, ot.level + 1,
           CONCAT(ot.bfs_order, '-', e.id)
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;  -- 按层级排序 = BFS

-- Depth-First（深度优先）：按路径遍历
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 
           CAST(name AS CHAR(1000)) AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id,
           CONCAT(ot.path, ' > ', e.name)
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY path;  -- 按路径排序 = DFS

递归 CTE 注意事项：

  • 必须有锚点成员（非递归部分）和递归成员，用 UNION ALL 连接
  • 设置 cte_max_recursion_depth（默认1000）防止无限递归
  • 递归成员不能包含：聚合函数、GROUP BY、ORDER BY、LIMIT、DISTINCT
  • MySQL 8.0 的 CTE 是物化的，不会多次执行（非内联）

延伸思考： 如果组织架构中存在循环引用（A的经理是B，B的经理是A），递归 CTE 会如何表现？如何在递归查询中检测并处理循环引用？

10. JSON 支持与性能调优综合 ​

难度：⭐⭐⭐

题目： MySQL 8.0 大幅增强了 JSON 支持。请说明：

  1. MySQL 8.0 中 JSON 数据的物理存储格式，以及 JSON 列的索引优化策略
  2. MySQL 8.0 新增的 JSON 函数（如 JSON_TABLE、->> 运算符等）的典型应用场景
  3. 给定以下场景：一个商品表 products，包含 JSON 类型的 attributes 列存储商品属性，如何优化高频查询 SELECT * FROM products WHERE attributes->>'$.brand' = 'Apple' AND attributes->'$.price' < 9999？

标准答案：

1. JSON 物理存储与索引优化

MySQL 8.0 的 JSON 存储格式：

  • 内部格式：JSON 数据在存储时被解析为二进制格式（Binary JSON / Partial In-place Update Format），并非纯文本
  • 存储位置：JSON 值太长时使用溢出页（类似 BLOB），短值内联存储
  • 8.0 改进：支持局部原地更新（Partial In-place Update），修改 JSON 内某个字段时不必重写整个 JSON 值，前提是修改后的值不超过原值占用的空间

JSON 列的索引优化策略：

sql
-- 策略一：生成列（Generated Column）+ 索引（MySQL 5.7+ 支持）
ALTER TABLE products 
ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.brand'))) STORED,
ADD INDEX idx_brand (brand);

-- 策略二：MySQL 8.0.17+ 的多值索引（Multi-Valued Index）
-- 适用于 JSON 数组场景，如 $.tags 是数组
ALTER TABLE products 
ADD INDEX idx_tags ((CAST(attributes->'$.tags' AS CHAR(32) ARRAY)));

-- 策略三：函数索引（MySQL 8.0.13+）
ALTER TABLE products
ADD INDEX idx_brand_func ((CAST(attributes->>'$.brand' AS CHAR(50))));

2. MySQL 8.0 新增 JSON 函数

函数/特性说明示例
->> 运算符等价于 JSON_UNQUOTE(JSON_EXTRACT())，返回文本attributes->>'$.brand'
JSON_TABLE()将 JSON 数组展开为关系表见下方示例
JSON_MERGE_PATCH()RFC 7396 合并合并两个JSON
JSON_MERGE_PRESERVE()保留数组合并数组合并
JSON_OVERLAPS()判断两个JSON是否有交集JSON_OVERLAPS('[1,2]', '[2,3]') → 1
JSON_VALUE()提取标量值（8.0.21+）带类型转换的提取

JSON_TABLE 示例：

sql
-- 将 JSON 数组展开为行，与其他表 JOIN
SELECT p.name, tag.tag_value
FROM products p,
JSON_TABLE(p.attributes->'$.tags', '$[*]' COLUMNS (
    tag_value VARCHAR(32) PATH '$'
)) AS tag
WHERE tag.tag_value = '5G';

3. 高频查询优化方案

sql
-- 原查询
SELECT * FROM products 
WHERE attributes->>'$.brand' = 'Apple' 
  AND attributes->'$.price' < 9999;

-- 优化方案：函数索引（MySQL 8.0.13+）
ALTER TABLE products
ADD INDEX idx_brand_price ((
    CAST(attributes->>'$.brand' AS CHAR(50))
), (
    CAST(attributes->'$.price' AS DECIMAL(10,2))
));

-- 或者使用生成列 + 复合索引
ALTER TABLE products
ADD COLUMN brand_vc VARCHAR(50) GENERATED ALWAYS AS (attributes->>'$.brand') VIRTUAL,
ADD COLUMN price_dc DECIMAL(10,2) GENERATED ALWAYS AS (attributes->'$.price') VIRTUAL,
ADD INDEX idx_brand_price_vc (brand_vc, price_dc);

-- 优化后查询
SELECT * FROM products 
WHERE brand_vc = 'Apple' AND price_dc < 9999;

性能调优关键点：

  1. JSON 读取优化：->> 比 JSON_UNQUOTE(JSON_EXTRACT()) 更简洁，性能等价
  2. 避免全表扫描：必须通过生成列或函数索引为 JSON 字段创建索引
  3. Virtual vs Stored 生成列：Virtual 不占存储但每次读取需计算，Stored 占存储但读取快。高频查询用 Stored
  4. Partial Update 优化：确保 JSON 列的更新只修改部分字段时，InnoDB 能做原地更新
    • 需使用 JSON_SET(), JSON_REPLACE(), JSON_REMOVE() 而非 UPDATE ... SET col = '新JSON'
  5. BINLOG 格式：Partial Update 需要 binlog_row_value_options=PARTIAL_JSON 减少 binlog 体积

延伸思考： 如果 attributes 中的字段是动态的（不同品类商品有完全不同的属性结构），如何设计索引策略才能兼顾灵活性和查询性能？与 MongoDB 的文档模型相比，MySQL JSON 在此场景下的优劣是什么？

📌 总结：以上10题覆盖了 MySQL 8.0 核心知识点，建议结合实际生产环境深入实践。面试时不仅要能回答"是什么"，更要能解释"为什么"和"怎么做"。