MySQL 8.0 面试题精选（10道）
生成时间：2026-06-07 覆盖主题：InnoDB架构、索引优化、事务隔离级别、MVCC、锁机制、SQL优化、主从复制、分库分表、窗口函数、CTE、JSON支持、性能调优
题目1：请简述InnoDB存储引擎的架构组成
难度：⭐⭐
标准答案：
InnoDB采用多线程架构，主要包含三大组件：
1. 内存结构
- Buffer Pool（缓冲池）：缓存数据页和索引页，采用LRU算法管理，默认128MB（可调），是性能核心
- Change Buffer（变更缓冲区）：缓存非唯一索引的变更操作，减少随机IO
- Adaptive Hash Index（自适应哈希索引）：InnoDB自动为热点页建立的哈希索引，无法手动管理
- Log Buffer（日志缓冲区）：缓存redo log的写入，定期刷盘
2. 后台线程
- Master Thread：核心后台线程，负责缓冲池刷新、合并插入缓冲等
- IO Thread：负责异步IO请求的处理（read/write线程）
- Purge Thread：回收undo log占用的空间
- Page Cleaner Thread：负责脏页刷新
3. 磁盘文件
- 表空间文件（
.ibd，每表独立表空间） - 重做日志文件（redo log：
ib_logfile0/1） - 撤销表空间（undo tablespace）
延伸思考：
- Buffer Pool命中率如何计算？
(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100% - 如何判断Buffer Pool是否足够大？观察
SHOW ENGINE INNODB STATUS中的命中率，建议≥99% - Change Buffer在什么场景下会失效？（唯一索引的插入无法缓存）
题目2：MySQL 8.0索引有哪些类型？如何设计高效的索引策略？
难度：⭐⭐⭐
标准答案：
MySQL 8.0支持以下索引类型：
| 索引类型 | 说明 | 使用场景 |
|---|---|---|
| B+树索引 | 默认索引，支持范围/排序/前缀匹配 | 绝大多数场景 |
| 哈希索引 | Memory引擎支持，InnoDB有自适应哈希（不可手动创建） | 精确匹配 |
| 全文索引（FULLTEXT） | 支持中文分词（ngram解析器） | 文本搜索 |
| 空间索引（SPATIAL） | R树结构 | GIS地理数据 |
| 函数索引 | 8.0新增，对表达式建索引 | LOWER(name)、JSON_EXTRACT() |
| 降序索引 | 8.0新增，支持DESC排序优化 | ORDER BY ... DESC |
索引设计策略：
- 选择合适的列：高选择性（基数大）的列优先；避免在低基数列（如性别）上建索引
- 联合索引最左前缀：
INDEX(a,b,c)可命中a、a,b、a,b,c，但不能命中b或c - 覆盖索引：查询列都在索引中，避免回表（
EXPLAIN中Using index） - 控制索引数量：建议单表不超过5-6个，索引越多写入越慢
索引失效的常见写法（必须避免）：
sql
-- ❌ 对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;
-- ✅ 改为范围查询
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
-- ❌ 隐式类型转换（phone是VARCHAR）
SELECT * FROM users WHERE phone = 13800138000;
-- ✅ 显式传字符串
SELECT * FROM users WHERE phone = '13800138000';
-- ❌ LIKE以%开头
SELECT * FROM users WHERE name LIKE '%张三';
-- ✅ 前缀匹配可用索引
SELECT * FROM users WHERE name LIKE '张三%';
-- ❌ OR条件中部分列无索引，全盘扫描
SELECT * FROM users WHERE name = '张三' OR age = 20;MySQL 8.0 新特性示例：
sql
-- 函数索引
CREATE INDEX idx_name_lower ON users((LOWER(name)));
-- 降序索引
CREATE INDEX idx_create_time_desc ON orders(create_time DESC, id DESC);
-- 不可见索引（测试索引是否有用，不删除）
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;延伸思考：
- 什么是索引下推（Index Condition Pushdown，ICP）？存储引擎层过滤数据，减少回表次数
EXPLAIN中的type字段含义：system > const > eq_ref > ref > range > index > ALL（性能递减）- 如何查找冗余索引？
sys.schema_redundant_indexes
题目3：请详细解释MySQL的事务隔离级别及实现原理
难度：⭐⭐⭐
标准答案：
SQL标准定义4种隔离级别，MySQL全部支持：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED（读未提交） | ✗ | ✗ | ✗ | 直接读最新数据，不加锁，问题最多 |
| READ COMMITTED（读已提交） | ✓ | ✗ | ✗ | 每次读生成新Read View |
| REPEATABLE READ（可重复读，默认） | ✓ | ✓ | ✓* | 事务开始生成Read View，InnoDB通过MVCC+间隙锁解决幻读 |
| SERIALIZABLE（串行化） | ✓ | ✓ | ✓ | 所有SELECT自动加FOR SHARE锁 |
*InnoDB在RR级别通过MVCC（快照读）+ 间隙锁（当前读）解决了幻读问题，这是InnoDB对SQL标准的超越。
实现原理：
- READ UNCOMMITTED：直接读最新数据页，完全不加锁，性能最好但问题最多
- READ COMMITTED：每次执行
SELECT时生成新的Read View，能读到其他事务已提交的最新数据 - REPEATABLE READ：事务第一次执行
SELECT时生成Read View，整个事务期间复用同一个Read View - SERIALIZABLE：隐式将普通
SELECT转为SELECT ... FOR SHARE，相当于所有读都加共享锁
当前读 vs 快照读：
- 快照读：普通的
SELECT，基于MVCC，不加锁，读取历史版本 - 当前读：
SELECT ... FOR UPDATE、SELECT ... FOR SHARE、UPDATE、DELETE，读取最新版本并加锁
延伸思考：
- 为什么互联网业务常用RC而不是RR？RC性能更好，锁范围更小，但可能存在不可重复读
- 如何修改隔离级别？
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; - RR级别下，普通
SELECT会不会阻塞？不会，快照读基于MVCC无锁
题目4：请深入解释InnoDB的MVCC机制工作原理
难度：⭐⭐⭐
标准答案：
MVCC（Multi-Version Concurrency Control，多版本并发控制）是InnoDB实现非锁定一致性读的核心机制。
核心数据结构：
每行记录有3个隐藏列：
DB_TRX_ID（6字节）：最后修改该行的事务IDDB_ROLL_PTR（7字节）：回滚指针，指向undo log中该行的历史版本DB_ROW_ID（6字节）：行ID，表无主键时InnoDB自动生成
undo log分类：
- insert undo：插入操作产生的undo，事务回滚时删除插入的记录，事务提交后可直接删除（不需要用于MVCC）
- update undo：更新/删除操作产生的undo，用于MVCC快照读，需要保留到没有任何事务访问该版本时才可清理
Read View（读视图）结构：
m_ids: 生成Read View时活跃的事务ID列表
min_trx_id: 最小活跃事务ID
max_trx_id: 下一个将被分配的事务ID（不是max，是高水位）
creator_trx_id: 创建该Read View的事务自己的ID数据可见性判断规则（核心，面试高频）：
对于某条记录的DB_TRX_ID：
- 如果
DB_TRX_ID<min_trx_id→ 可见（在Read View创建前已提交） - 如果
DB_TRX_ID>=max_trx_id→ 不可见（在Read View创建后才启动的事务） - 如果
DB_TRX_ID在m_ids列表中 → 不可见（该事务尚未提交） - 如果
DB_TRX_ID=creator_trx_id→ 可见（本事务自己修改的） - 以上都不满足 → 可见（已提交的其他事务）
不可见时，沿着DB_ROLL_PTR找到undo log中的历史版本，递归判断可见性。
RC vs RR在MVCC上的区别：
- RC：每次
SELECT都生成新的Read View → 能读到其他事务最新提交的数据 - RR：事务第一次
SELECT时生成Read View，之后一直复用 → 可重复读
延伸思考：
- 长事务对MVCC的危害？undo log无法回收，版本链过长，表空间膨胀
- 如何监控长事务？
SELECT * FROM information_schema.innodb_trx WHERE trx_started < NOW() - INTERVAL 60 SECOND; purge线程的作用？清理不再需要的undo log和历史版本数据
题目5：InnoDB的锁机制是怎样的？请解释行锁、表锁、间隙锁、意向锁
难度：⭐⭐⭐
标准答案：
按锁粒度分类
1. 表级锁
- 表锁：
LOCK TABLES（MyISAM风格，InnoDB不推荐使用） - 元数据锁（MDL）：访问表时自动加锁，防止DDL和DML冲突（MySQL 5.5+）
- 意向锁（Intention Lock）：表级锁，表示"事务准备在表中的某些行上加锁"
2. 行级锁（InnoDB核心）
- 记录锁（Record Lock）：锁定索引记录本身
- 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止插入
- 临键锁（Next-Key Lock）：Record Lock + Gap Lock，锁定记录及前面的间隙（默认行锁算法）
按锁模式分类
| 锁模式 | 说明 | 加锁方式 |
|---|---|---|
| S锁（共享锁） | 允许其他事务读，不允许写 | SELECT ... FOR SHARE |
| X锁（排他锁） | 其他事务不能读也不能写 | SELECT ... FOR UPDATE |
意向锁（Intention Lock）详解
意向锁是表级锁，但不阻塞行级锁，只是"声明意图"：
- IS（意向共享锁）：事务打算给某些行加S锁
- IX（意向排他锁）：事务打算给某些行加X锁
作用：当另一个事务想给表加表级X锁时，只需检查是否存在意向锁，而不需要逐行检查——这是性能优化手段。
锁兼容性矩阵：
| IS | IX | S | X | |
|---|---|---|---|---|
| IS | ✓ | ✓ | ✓ | ✗ |
| IX | ✓ | ✓ | ✗ | ✗ |
| S | ✓ | ✗ | ✓ | ✗ |
| X | ✗ | ✗ | ✗ | ✗ |
间隙锁（Gap Lock）详解
间隙锁锁定的是索引记录之间的空隙，不包含记录本身。
sql
-- 假设id有值：1, 5, 10
-- 事务A执行：
SELECT * FROM t WHERE id > 5 AND id < 10 FOR UPDATE;
-- 锁定的是 (5, 10) 这个间隙
-- 事务B执行：
INSERT INTO t VALUES (7); -- 阻塞！间隙锁阻止插入间隙锁只在RR隔离级别下生效，目的是防止幻读。
Next-Key Lock（默认行锁算法）= 记录锁 + 间隙锁，锁定范围是"当前记录及前面的间隙"。
加锁规则（简化版）
UPDATE/DELETE：对扫描过的索引记录加Next-Key LockINSERT：加插入意向锁（一种特殊的间隙锁）- 普通
SELECT：不加锁（MVCC快照读） - 唯一索引的唯一性检查：使用记录锁，不使用间隙锁
死锁示例及避免：
sql
-- 事务A 事务B
BEGIN; BEGIN;
UPDATE t SET n=1 WHERE id=1; UPDATE t SET n=1 WHERE id=2;
UPDATE t SET n=1 WHERE id=2; UPDATE t SET n=1 WHERE id=1; -- 死锁！避免死锁：事务按相同顺序加锁；控制事务大小；设置锁等待超时innodb_lock_wait_timeout。
延伸思考：
- 如何查看当前锁等待？
SELECT * FROM performance_schema.data_locks;（MySQL 8.0） SHOW ENGINE INNODB STATUS中的死锁信息如何解读？- 什么是插入意向锁（Insert Intention Lock）？是一种间隙锁，表示"准备在某个间隙插入"
题目6：请列举常见的SQL性能问题及优化方法
难度：⭐⭐⭐
标准答案：
问题1：全表扫描（type=ALL）
原因：无合适索引、索引失效、小表（优化器认为全表扫描更快）
解决：
sql
-- 找出全表扫描的语句
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1000000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%' AND DIGEST_TEXT NOT LIKE '%EXPLAIN%'
ORDER BY COUNT_STAR DESC LIMIT 10;问题2：深分页（LIMIT offset很大）
sql
-- ❌ 慢：需要扫描前1000000条
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 优化1：延迟关联
SELECT * FROM orders a
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) b ON a.id = b.id;
-- ✅ 优化2：游标分页，推荐！
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;游标分页要求有连续自增或时间戳字段，且不能跳页，但性能最优。
问题3：JOIN性能差
优化原则：
- 小表驱动大表（减少外层循环次数）
- 关联列必须有索引（否则笛卡尔积）
- 减少JOIN的表数量（建议不超过3-4个）
- 避免子查询，改用JOIN（MySQL 5.6+子查询优化已大幅改善，但JOIN仍更可控）
sql
-- ❌ 慢：子查询，且外层无法使用索引
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- ✅ 快：JOIN，orders.user_id有索引
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id WHERE o.amount > 1000;问题4：排序无法使用索引（Using filesort）
sql
-- 假设有 INDEX(dept, create_time)
-- ❌ 无法用索引排序（索引列顺序不匹配）
SELECT * FROM users WHERE dept = 'IT' ORDER BY name;
-- ✅ 可以用索引排序
SELECT * FROM users WHERE dept = 'IT' ORDER BY create_time;原则：WHERE条件列 + ORDER BY列 能组成联合索引的前缀。
问题5：GROUP BY临时表（Using temporary）
sql
-- ❌ 产生临时表
SELECT dept, COUNT(*) FROM users GROUP BY dept ORDER BY cnt;
-- ✅ 避免临时表：先过滤再分组
SELECT dept, COUNT(*) AS cnt FROM users WHERE dept IN ('IT', 'HR') GROUP BY dept;优化工具链
sql
-- 1. EXPLAIN：分析执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- 2. EXPLAIN ANALYZE（MySQL 8.0）：实际执行并展示耗时
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
-- 3. 慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 超过1秒记录
-- 4. SHOW PROFILE（细粒度耗时分析）
SET profiling = 1;
SELECT ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;延伸思考：
Using index condition（索引条件下推，ICP）是什么意思？好事还是坏事？→ 好事，存储引擎层过滤，减少回表select *的性能问题除了返回多余列，还有什么？→ 无法使用覆盖索引，必须回表- 如何快速找到全表扫描的SQL？
EXPLAIN+type=ALL，或慢查询日志+log_queries_not_using_indexes=ON
题目7：请解释MySQL主从复制的原理及常见问题
难度：⭐⭐⭐
标准答案：
主从复制原理
基于binlog（二进制日志）实现，核心三步：
主库（Master） 从库（Slave）
│ │
│ 1. 数据变更写入binlog │
│───────────────────────────>│
│ │ 2. IO线程读取binlog → relay log
│ │───────────────────────────
│ │ 3. SQL线程重放relay log
│ │───────────────────────────详细流程：
- 主库将数据变更写入binlog（有三种格式：STATEMENT、ROW、MIXED）
- 从库IO线程连接主库，读取binlog并写入本地relay log（中继日志）
- 从库SQL线程（或并行worker）读取relay log，重放SQL操作
binlog格式对比
| 格式 | 优点 | 缺点 | 推荐场景 |
|---|---|---|---|
| STATEMENT | 日志量小 | 非确定性函数导致主从不一致 | 不推荐 |
| ROW | 数据一致性高 | 日志量大（改多行时） | 推荐 |
| MIXED | 自动切换 | 仍有不一致风险 | 过渡方案 |
复制模式
- 异步复制（默认）：主库写入binlog后立即返回，不等待从库确认 → 性能最好，但存在数据丢失风险
- 半同步复制：主库等待至少一个从库接收binlog后才返回 → 数据安全性更高，性能略有下降
- 组复制（MGR）：基于Paxos协议，支持多主写入，自动故障检测
常见问题
问题1：主从延迟
- 原因：从库单线程重放（已改善为并行复制）、大事务、从库硬件差、网络延迟
- 监控：
SHOW SLAVE STATUS→Seconds_Behind_Master - 解决：
- 启用并行复制（MySQL 5.7+ 基于LOGICAL_CLOCK）
- 拆分大事务（单事务不超过1万行）
- 从库使用更好的硬件（SSD）
- 读写分离时，对一致性要求高的读走主库
问题2：数据不一致
- 原因：从库被写入、非确定性函数（UUID()、RAND()）、binlog格式为STATEMENT
- 解决：
- 从库设置为只读：
SET GLOBAL read_only = 1;（super用户仍需super_read_only=ON） - 使用ROW格式binlog
- 定期校验：
pt-table-checksum（Percona Toolkit）
- 从库设置为只读：
问题3：复制中断
sql
-- 查看错误
SHOW SLAVE STATUS\G
-- 常见错误：1062（主键冲突）、1032（记录不存在）
-- 谨慎处理：跳过该事务（可能丢数据）
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;MySQL 8.0 复制新特性
- 默认认证插件改为
caching_sha2_password（主从都需要支持） - 原子DDL（Crash Safe DDL）
binlog_expire_logs_seconds替代expire_logs_days（更精确的控制）- 克隆插件（Clone Plugin）：快速搭建从库
延伸思考：
- 并行复制的原理？基于LOGICAL_CLOCK，同一个commit group的事务可以并行重放
- 一主多从架构下，从库如何分配读请求？负载均衡器（如ProxySQL）或客户端配置多个从库地址
- GTID复制是什么？全局事务ID，简化主从切换和故障恢复
题目8：什么情况下需要做分库分表？有哪些分片策略？
难度：⭐⭐⭐
标准答案：
分库分表的时机
不要过早分库分表！ 先尝试以下优化手段：
- 加索引、SQL优化
- 读写分离（主写从读）
- 引入缓存（Redis）
- 升级硬件（SSD、更大内存）
- 数据归档（历史数据迁移到归档库）
当以下情况出现时，才考虑分库分表：
- 单表数据量超过1000万（视硬件和业务，不是绝对值）
- 数据库成为性能瓶颈，CPU/IO持续高位
- 单机磁盘空间不足
分片策略
1. 范围分片
sql
-- 按用户ID范围
user_0001: id ∈ [1, 1000000]
user_0002: id ∈ [1000001, 2000000]- ✅ 优点：容易扩展、范围查询友好
- ❌ 缺点：数据分布不均、热点问题（新用户集中访问最新分片）
2. 哈希分片
sql
-- 分8个库
shard_id = hash(user_id) % 8- ✅ 优点：数据分布均匀
- ❌ 缺点：扩缩容需要数据迁移（可用一致性哈希缓解）
3. 一致性哈希
- 将节点和分片键映射到哈希环（0~2^32-1）
- 扩缩容时只迁移少量数据
- 虚拟节点解决数据倾斜
4. 时间分片
sql
-- 按月分表
orders_202401, orders_202402, orders_202403, ...- ✅ 适合时间序列数据（日志、订单历史、监控数据）
- ✅ 自动过期（直接DROP老表）
5. 基因分片（垂直分片+水平分片结合）
- 将用户ID的后N位作为分片键
- 用户相关数据（订单、地址、积分）使用相同的分片键
- 确保同一用户的所有数据在同一分片，避免跨分片JOIN
分库分表中间件
| 类型 | 产品 | 说明 |
|---|---|---|
| Proxy模式 | MyCat、ShardingSphere-Proxy | 独立代理层，对应用透明 |
| 客户端模式 | ShardingSphere-JDBC、TDDL | Jar包集成，性能更好 |
分库分表的挑战
- 分布式事务：2PC（性能差）、TCC（侵入业务）、Saga（最终一致性）、本地消息表
- 跨分片查询：分页、排序、聚合需要合并多个分片的结果
- 分片键选择：一旦确定难以变更，要选择查询最频繁的维度
- 扩容：一致性哈希可以最小化数据迁移量
- 运维复杂度：备份、监控、DDL变更都需要分片感知
延伸思考：
- 分片数量如何确定？建议一次分够（比如按未来3-5年数据量），避免频繁扩容
- 如何做平滑的数据迁移？双写（旧库+新库）→ 数据同步 → 切读流量 → 切写流量 → 下线旧库
- 分库分表后，如何做跨分片的JOIN？尽量避免；必要时在应用层做（先查A分片ID列表，再查B分片）
题目9：MySQL 8.0的窗口函数和CTE是什么？请举例说明应用场景
难度：⭐⭐
标准答案：
窗口函数（Window Functions）
窗口函数在不减少行数的情况下进行计算，常与OVER()子句一起使用。
常用窗口函数分类：
1. 排名函数
sql
-- 每个部门按薪水排名
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drk
FROM employees;
-- 结果示例（部门IT，薪水[10000, 9000, 9000, 8000]）：
-- ROW_NUMBER(): 1, 2, 3, 4 （不并列，序号连续）
-- RANK(): 1, 2, 2, 4 （并列跳号）
-- DENSE_RANK(): 1, 2, 2, 3 （并列不跳号）2. 取值函数
sql
SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY create_time) AS prev_salary, -- 前一行的薪水
LEAD(salary, 1) OVER (ORDER BY create_time) AS next_salary -- 后一行的薪水
FROM employees;3. 聚合窗口函数
sql
-- 累计求和（运行总和）
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly_revenue;
-- 移动平均（近3个月）
SELECT month, revenue,
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM monthly_revenue;经典应用场景：
sql
-- 场景1：取每个部门薪水最高的3名员工
SELECT * FROM (
SELECT dept, name, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees
) t WHERE rn <= 3;
-- 场景2：计算环比增长率
SELECT month, revenue,
ROUND((revenue - LAG(revenue, 1) OVER (ORDER BY month))
/ LAG(revenue, 1) OVER (ORDER BY month) * 100, 2) AS mom_growth_pct
FROM monthly_revenue;
-- 场景3：找出每个用户的最近一笔订单
SELECT * FROM (
SELECT user_id, order_id, create_time,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) AS rn
FROM orders
) t WHERE rn = 1;CTE（公用表表达式）
CTE使用WITH语法，是临时命名结果集，提高复杂查询可读性。
基本用法：
sql
-- 基本CTE
WITH dept_stats AS (
SELECT dept, AVG(salary) AS avg_salary, COUNT(*) AS cnt
FROM employees
GROUP BY dept
)
SELECT * FROM dept_stats WHERE avg_salary > 10000;递归CTE（MySQL 8.0新增，非常强大）：
sql
-- 场景：组织架构树查询（找某个员工的所有下属）
WITH RECURSIVE emp_tree 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, et.level + 1
FROM employees e
JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree;防止无限递归：MySQL提供cte_max_recursion_depth参数（默认1000），超过则报错。
延伸思考：
- 窗口函数和
GROUP BY的区别？GROUP BY减少行数，窗口函数保留所有行 ROWS和RANGE的区别？ROWS是物理行，RANGE是逻辑范围（相同值的行视为同一行）- 递归CTE除了组织架构还能用在哪里？评论楼、分类树、生成连续数字序列
题目10：MySQL 8.0对JSON的支持有哪些？如何高效查询JSON字段？
难度：⭐⭐
标准答案：
MySQL 5.7开始支持JSON数据类型，8.0大幅增强（JSON_TABLE、JSON路径表达式优化等）。
JSON数据类型优势
- 自动校验：插入时自动检查JSON格式合法性
- 存储优化：采用二进制格式存储（不是纯文本），解析更快
- 部分更新（8.0新增）：只更新JSON中的某部分，不需要重写整个JSON
常用JSON函数
sql
-- 建表
CREATE TABLE users (
id INT PRIMARY KEY,
profile JSON
);
-- 插入
INSERT INTO users VALUES
(1, '{"name": "张三", "age": 25, "tags": ["java", "mysql"], "address": {"city": "北京", "district": "海淀"}}');
-- 提取JSON值（两种语法）
SELECT profile->'$.name' AS name1 FROM users; -- 结果带引号："张三"
SELECT profile->>'$.name' AS name2 FROM users; -- 结果不带引号：张三
SELECT JSON_EXTRACT(profile, '$.age') FROM users; -- 等价于 ->
-- 路径中有特殊字符用双引号
SELECT profile->>'$"my-name"' FROM users;
-- 修改JSON
UPDATE users
SET profile = JSON_SET(profile, '$.age', 26, '$.address.city', '上海')
WHERE id = 1;
UPDATE users
SET profile = JSON_REMOVE(profile, '$.tags')
WHERE id = 1;
-- 数组操作
SELECT JSON_ARRAY_APPEND(profile, '$.tags', 'redis') FROM users;
SELECT JSON_CONTAINS(profile, '"mysql"', '$.tags') FROM users; -- 是否包含，返回1/0
SELECT JSON_LENGTH(profile, '$.tags') FROM users; -- 数组长度
-- JSON_TABLE：将JSON转为关系型数据（8.0强大功能）
SELECT u.id, jt.tag
FROM users u
JOIN JSON_TABLE(u.profile, '$.tags[*]' COLUMNS (tag VARCHAR(20) PATH '$')) AS jt
ON TRUE
WHERE u.id = 1;JSON索引（重要！）
MySQL不支持直接对JSON列建索引，但有两种方案：
方案1：函数索引（推荐，8.0）
sql
-- 对JSON字段的某个路径建索引
CREATE INDEX idx_profile_name ON users ((profile->>'$.name'));方案2：虚拟列 + 索引
sql
ALTER TABLE users
ADD COLUMN name VARCHAR(50) GENERATED ALWAYS AS (profile->>'$.name') VIRTUAL,
ADD INDEX idx_name (name);JSON性能优化建议
- 避免在大结果集上使用JSON函数：JSON函数无法使用索引，会导致全表扫描
- 常用查询条件建函数索引或虚拟列索引
- JSON路径表达式尽量精确：
$.name比$.*快得多 - 考虑是否需要用JSON：如果字段结构固定且需要频繁查询/关联，建议用关系型设计
JSON vs EAV模型
| 方案 | 优点 | 缺点 |
|---|---|---|
| JSON | schema灵活、写入简单 | 查询性能差、无法外键约束 |
| EAV（行存属性） | 完全结构化、支持索引 | 查询复杂、JOIN多 |
| 关系型（固定列） | 性能最好、约束完整 | 扩展性差 |
延伸思考：
- 什么场景适合用JSON？动态属性（商品规格、用户偏好）、日志存储、配置数据
- 什么场景不适合用JSON？需要频繁关联查询、需要强约束、高并发写入的核心业务表
- MySQL的JSON和MongoDB相比？MySQL JSON适合"关系型为主、少量JSON辅助"的场景；纯JSON场景MongoDB更专业
总结
以上10道题目覆盖了MySQL 8.0的核心知识点，从存储引擎底层原理到8.0新特性均有涉及。面试准备时建议：
- 理解原理多于背诵结论：比如MVCC的Read View判断规则，理解了就能推导，不需要死记
- 结合实际项目经验：回答时加上自己项目中的实际案例会更有说服力
- 关注8.0新特性：窗口函数、CTE、函数索引、降序索引、JSON增强等都是8.0的亮点
- 动手实践：所有SQL示例都自己跑一遍，印象会更深刻
祝面试顺利！🚀