工作知识库

Appearance

MySQL 8.0 面试题（10道） ​

生成时间：2026-06-06 21:08 覆盖：InnoDB架构、索引优化、事务隔离级别、MVCC、锁机制、SQL优化、主从复制、分库分表、窗口函数、CTE、JSON支持、性能调优

题目 1：请描述 InnoDB 的存储引擎架构，主要包含哪些内存结构和磁盘结构？ ​

难度： ⭐⭐

标准答案：

InnoDB 架构分为内存结构和磁盘结构两大块。

内存结构：

  • Buffer Pool：缓存数据页和索引页，是 InnoDB 最核心的内存组件，默认占物理内存的 75%。采用 LRU 算法管理，分为 Young 区（热数据）和 Old 区（冷数据）。
  • Change Buffer：缓存二级索引的 DML 操作（INSERT/UPDATE/DELETE），当该索引页不在 Buffer Pool 时先写入 Change Buffer，后续通过后台线程合并。对唯一索引无效。
  • Log Buffer：redo log 的缓冲区，事务提交时刷入磁盘 redo log file。大小由 innodb_log_buffer_size 控制。
  • Adaptive Hash Index：自适应哈希索引，InnoDB 自动对热点页建立哈希索引加速等值查询。
  • Lock System / Data Dictionary Cache：锁信息缓存和数据字典缓存。

磁盘结构：

  • 表空间（Tablespace）：包括系统表空间（ibdata1）、独立表空间（.ibd，MySQL 8.0 默认）、临时表空间、undo 表空间。
  • redo log：WAL（Write-Ahead Logging）机制的核心，用于崩溃恢复，循环写入。
  • undo log：存储数据修改前的旧值，用于 MVCC 和事务回滚。MySQL 8.0 将 undo log 从系统表空间独立出来。
  • Doublewrite Buffer（磁盘）：位于系统表空间，解决页撕裂问题（partial write），每次写数据页前先顺序写入 doublewrite 区域。

延伸思考：

  • Buffer Pool 的 LRU 算法如何避免预读/全表扫描污染？→ 答案是 Young/Old 分区 + 预读页只在 Old 区停留 innodb_old_blocks_time（默认 1s）才晋升。
  • MySQL 8.0 的 Change Buffer 对 DML 性能提升有多大？→ 取决于二级索引数量和写比例，读多写少场景收益大，SSD 场景收益变小。
  • 为什么 MySQL 8.0 默认独立表空间？→ 便于管理单个表的空间、TRUNCATE 即释放磁盘、支持 DDL 原子性。

题目 2：什么是聚簇索引和非聚簇索引（二级索引）？MySQL 8.0 中它们的区别和查询流程是怎样的？ ​

难度： ⭐⭐

标准答案：

聚簇索引（Clustered Index）：

  • InnoDB 中，聚簇索引即主键索引，数据和索引存储在同一棵 B+ 树中。
  • 聚簇索引的叶子节点存储完整的行数据（所有列）。
  • 一张表只能有一个聚簇索引。
  • 如果没有显式定义主键，InnoDB 会找一个唯一非空列，若都没有则自动生成一个 6 字节的隐藏行 ID（DB_ROW_ID）。

二级索引（Secondary Index / 非聚簇索引）：

  • 叶子节点存储索引列的值 + 对应主键值（而非行数据指针）。
  • 通过二级索引查询时，先找到主键值，再回表到聚簇索引查找完整行数据（即 "bookmark lookup"）。
  • 一张表可以有多个二级索引。

查询流程对比：

sql
-- 聚簇索引查询（直接命中）
SELECT * FROM t WHERE id = 100;  -- 走聚簇索引，一次 I/O 获取数据

-- 二级索引查询（需要回表）
SELECT * FROM t WHERE name = 'Alice';  -- 先查二级索引得到主键，再回表

延伸思考：

  • 覆盖索引（Covering Index）：当 SELECT 的列全部包含在二级索引中时，不需要回表，直接从二级索引返回。这是重要的优化手段。
  • 联合索引的最左前缀原则：(a, b, c) 索引能匹配 a、(a,b)、(a,b,c) 的查询条件，但无法直接用 b 或 (b,c) 查询。
  • MySQL 8.0 引入了降序索引（Descending Index），真正支持 DESC 索引列的物理排序，此前版本只是语法支持实际还是升序存储。

题目 3：MySQL 事务的四种隔离级别分别是什么？InnoDB 默认哪种？各解决了哪些并发问题？ ​

难度： ⭐⭐

标准答案：

SQL 标准定义四种隔离级别，逐级递增：

隔离级别脏读不可重复读幻读
READ UNCOMMITTED❌ 可能❌ 可能❌ 可能
READ COMMITTED✅ 防止❌ 可能❌ 可能
REPEATABLE READ✅ 防止✅ 防止❌ 可能（SQL标准）/ ✅ 防止（InnoDB）
SERIALIZABLE✅ 防止✅ 防止✅ 防止

InnoDB 默认 REPEATABLE READ（可重复读）。

三种并发问题解释：

  • 脏读（Dirty Read）：读到其他事务未提交的数据。
  • 不可重复读（Non-Repeatable Read）：同一事务内两次读取同一行，结果不同（被其他事务 UPDATE）。
  • 幻读（Phantom Read）：同一事务内两次执行范围查询，返回的行数不同（被其他事务 INSERT/DELETE）。

关键区别：InnoDB 在 RR 级别通过 MVCC + Next-Key Lock 在一定程度上防止了幻读，但并非 100%：

  • 快照读（普通 SELECT）：基于 MVCC，不会幻读。
  • 当前读（SELECT ... FOR UPDATE / INSERT / UPDATE / DELETE）：通过 Next-Key Lock（行锁 + 间隙锁）锁定范围，防止幻读。

延伸思考：

  • 为什么阿里等大厂推荐将隔离级别设为 READ COMMITTED？→ 减少 GAP Lock 争用、降低死锁概率、性能更好，代价是牺牲可重复读语义。
  • SERIALIZABLE 的代价极高（本质是所有读加共享锁，写加排他锁），实际生产几乎不使用。

题目 4：详细解释 InnoDB 的 MVCC 实现机制，包括 undo log 版本链和 Read View。 ​

难度： ⭐⭐⭐

标准答案：

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 InnoDB 实现事务隔离的核心机制，让读写操作互不阻塞。

三个核心组件：

1. 隐藏字段： 每行数据有两个隐藏列：

  • DB_TRX_ID：最近修改（插入/更新）该行的事务 ID。
  • DB_ROLL_PTR：回滚指针，指向 undo log 中该行的上一个版本。

2. undo log 版本链： 每次 UPDATE 一行数据，不是原地修改，而是将旧版本写入 undo log，通过 DB_ROLL_PTR 形成版本链：

当前行 ← undo v2 ← undo v1 ← undo v0（原始插入版本）

3. Read View（读视图）： 事务执行快照读（普通 SELECT）时创建的一个"可见性判断"快照，包含：

  • m_ids：当前所有活跃（未提交）事务 ID 列表。
  • min_trx_id：m_ids 中最小的事务 ID。
  • max_trx_id：下一个将要分配的事务 ID（当前系统最大事务 ID + 1）。
  • creator_trx_id：创建该 Read View 的事务自身 ID。

可见性判断规则（逐条对比）：

条件结果原因
trx_id == creator_trx_id✅ 可见自己修改的数据，当然可见
trx_id < min_trx_id✅ 可见该事务在 Read View 创建前已提交
trx_id >= max_trx_id❌ 不可见该事务在 Read View 创建后才启动
min_trx_id <= trx_id < max_trx_id若 trx_id 在 m_ids 中则 ❌，否则 ✅检查该事务是否仍活跃

如果当前版本不可见，则沿 DB_ROLL_PTR 找上一个版本继续判断，直到找到可见版本或无更多版本。

RC vs RR 级别的 Read View 差异：

  • READ COMMITTED：每次 SELECT 都生成新的 Read View，所以能看到其他事务已提交的新数据。
  • REPEATABLE READ：事务第一次 SELECT 时生成 Read View，后续复用，所以始终看到事务开始时的快照，不受其他已提交事务影响。

延伸思考：

  • MVCC 能解决 UPDATE 丢失问题吗？→ 不能。两个事务同时 UPDATE 同一行，后提交的会覆盖先提交的（Last-Write-Wins），需要通过乐观锁（版本号）或悲观锁解决。
  • undo log 会无限膨胀吗？→ 不会。已提交且没有活跃 Read View 引用的 undo log 会被 purge 线程清理（由 innodb_purge_rseg_truncate_frequency 等参数控制）。
  • MySQL 8.0 对 undo log 做了什么改进？→ undo 表空间独立，支持在线 truncate，解决了历史版本中 undo 表空间只增不减的问题。

题目 5：InnoDB 有哪些锁类型？请详细说明行锁、表锁、间隙锁、临键锁和意向锁。 ​

难度： ⭐⭐⭐

标准答案：

一、按粒度分：

1. 表级锁：

  • 表锁（Table Lock）：LOCK TABLES t READ/WRITE。整张表加锁，粒度粗，极少使用。
  • 元数据锁（MDL, Metadata Lock）：MySQL 5.5+ 自动加，防止 DDL 和 DML 冲突。如 ALTER TABLE 期间会阻塞对表的 DML。MySQL 8.0 引入了 MDL 的超时机制（lock_wait_timeout）。
  • 意向锁（Intent Lock）：表级锁，用于快速判断表中是否有行级锁，本身不阻塞，只有当其他事务要加表锁时才检查意向锁。
    • 意向共享锁（IS）：事务打算对某行加 S 锁前，先在表级加 IS。
    • 意向排他锁（IX）：事务打算对某行加 X 锁前，先在表级加 IX。

兼容矩阵：

ISIXSX
IS兼容兼容兼容❌
IX兼容兼容❌❌
S兼容❌兼容❌
X❌❌❌❌

2. 行级锁（Record Lock）：

  • 记录锁（Record Lock）：锁定索引上的某一条记录（不是行本身，是索引项）。
    • 共享锁（S Lock）：SELECT ... LOCK IN SHARE MODE（MySQL 8.0 改为 SELECT ... FOR SHARE）。
    • 排他锁（X Lock）：SELECT ... FOR UPDATE、UPDATE、DELETE。
  • 间隙锁（Gap Lock）：锁定索引记录之间的间隙（开区间），防止其他事务在间隙中 INSERT，不阻止已存在的记录被修改。
    • WHERE id > 10 AND id < 20，会对 (10, 20) 间隙加锁。
    • 间隙锁之间是兼容的（两个事务可以对同一间隙加间隙锁），但它们共同阻止其他事务在该间隙插入。
  • 临键锁（Next-Key Lock） = Record Lock + Gap Lock，左开右闭区间 [prev, current]。是 InnoDB 在 RR 级别加行锁的默认算法。
    • WHERE id = 15 实际锁定 (10, 15]。
  • 插入意向锁（Insert Intention Lock）：一种特殊的间隙锁，INSERT 时加在目标间隙上。多个事务在相同间隙的不同位置 INSERT 时不互斥。

二、InnoDB 加锁规则（丁奇《MySQL实战45讲》总结，适用于 RR 级别）：

  1. 加锁的基本单位是 Next-Key Lock。
  2. 查找过程中访问到的对象才会加锁（未命中的不额外加锁）。
  3. 优化：等值查询命中唯一索引时，退化为 Record Lock。
  4. 优化：等值查询未命中时，退化为 Gap Lock。

延伸思考：

  • 死锁检测：InnoDB 默认开启死锁检测（innodb_deadlock_detect=ON），通过 wait-for graph（WFG）算法检测。高并发场景可考虑关闭 + 用 innodb_lock_wait_timeout 作为兜底，但这会降低并发度。
  • MySQL 8.0 引入了** NOWAIT 和 SKIP LOCKED** 语法，用于解决排队等待问题：SELECT ... FOR UPDATE NOWAIT（锁冲突立即报错）和 SELECT ... FOR UPDATE SKIP LOCKED（跳过被锁的行）。适用于任务队列场景。

题目 6：如何分析一条慢 SQL？从 EXPLAIN 到优化，请描述完整的排查思路。 ​

难度： ⭐⭐

标准答案：

一、定位慢 SQL：

  1. 开启慢查询日志：
    sql
    SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询
  2. 使用 mysqldumpslow 或 pt-query-digest 工具分析慢查询日志。
  3. Performance Schema（MySQL 8.0 默认开启）：通过 events_statements_summary_by_digest 按模式聚合统计。

二、EXPLAIN 分析：

sql
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

MySQL 8.0 引入的 EXPLAIN ANALYZE 会实际执行查询并输出执行时间，比普通 EXPLAIN 更精确。

关键字段：

列含义
type访问类型，从好到差：system > const > eq_ref > ref > range > index > ALL（全表扫描）
key实际使用的索引
key_len使用的索引长度（字节），联合索引可判断用了几个列
rows预估扫描行数
filtered预估满足条件的行百分比（rows × filtered = 实际返回行）
Extra额外信息：Using index（覆盖索引）、Using filesort（额外排序）、Using temporary（临时表）

三、常见优化手段：

  1. 索引优化：

    • 为 WHERE、ORDER BY、GROUP BY 的列建立合适索引。
    • 注意最左前缀和覆盖索引。
    • 避免 SELECT *，有利于覆盖索引。
    • 避免对索引列做函数操作或隐式类型转换。

  2. SQL 改写：

    • 避免 LIKE '%xxx%'（全模糊无法走索引）。
    • 避免 OR（可能不走索引），改为 UNION ALL。
    • 避免 IN 子查询过多项，可用 JOIN 替换。
    • 大分页优化：WHERE id > 100000 LIMIT 20 代替 LIMIT 100000, 20。

  3. 避免索引失效的写法：

    sql
    -- ❌ 索引失效
WHERE YEAR(created_at) = 2024        -- 函数操作
WHERE name = 123                     -- 隐式类型转换
WHERE name LIKE '%John%'             -- 前缀通配符
WHERE a + 1 = 10                     -- 表达式

-- ✅ 索引有效
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE name = 'John'
WHERE name LIKE 'John%'              -- 前缀匹配
WHERE a = 9

延伸思考：

  • MySQL 8.0 的不可见索引（Invisible Index）：ALTER TABLE t ALTER INDEX idx_name INVISIBLE，隐藏后优化器不使用，但物理保留，可用于灰度测试索引效果。
  • 索引下推（Index Condition Pushdown, ICP）：MySQL 5.6+ 引入，二级索引查询时，将 WHERE 条件推到存储引擎层过滤，减少回表次数。EXPLAIN 中 Using index condition 表示使用了 ICP。

题目 7：MySQL 8.0 主从复制原理是什么？GTID 复制与传统 binlog 位点复制有什么区别？ ​

难度： ⭐⭐⭐

标准答案：

一、主从复制原理（基于 Binlog）：

Master                              Slave
  │                                   │
  │  1. Client 写入                     │
  │  ──→ 执行事务 → 写入 Binlog          │
  │  ──→ Dump Thread ──→ IO Thread      │
  │                    （拉取 Binlog）   │
  │                         ──→ 写入 Relay Log
  │                                   ──→ SQL Thread（回放 Relay Log）

三线程模型：

  1. Binlog Dump Thread（主库）：将 Binlog 事件发送给从库。
  2. IO Thread（从库）：接收主库 Binlog，写入本地 Relay Log。
  3. SQL Thread（从库）：读取 Relay Log 并重放（apply）SQL 语句。

MySQL 8.0 支持多线程复制（Parallel Replication），SQL Thread 可将事务分配给多个 Worker Thread 并行回放，参数 slave_parallel_workers。

二、GTID 复制 vs 传统位点复制：

传统位点复制（binlog + pos）：

sql
CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='bin.000003', MASTER_LOG_POS=1546;
  • 问题：需要手动记录 binlog 文件名和位置，主从故障切换困难。

GTID 复制（Global Transaction Identifier）：

sql
-- MySQL 8.0 推荐语法
CHANGE REPLICATION SOURCE TO SOURCE_HOST='xxx', SOURCE_AUTO_POSITION=1;
  • GTID 格式：source_id:transaction_id，如 3E11FA47-71CA-11E1-9E33-C80AA9429562:23。
  • 从库自动寻找需要的 GTID，无需手动指定 binlog 位置。
  • 主从切换只需找最接近的从库，无需遍历 binlog 文件。

GTID 复制的优势：

  1. 故障切换简单：直接 CHANGE REPLICATION SOURCE TO 到新主库。
  2. 复制拓扑管理方便：可以追踪每个事务在哪些服务器上已执行。
  3. 跳过错误事务更安全：通过 SET GTID_NEXT='xxx' 精确控制。

MySQL 8.0 对复制的改进：

  • MySQL 8.0 支持 binlog 加密（binlog_encryption=ON）。
  • 克隆插件（Clone Plugin）：CLONE INSTANCE FROM ... 快速建立从库，替代 mysqldump/xtrabackup。
  • 组复制（Group Replication）：基于 Paxos 协议的强一致性复制方案。

延伸思考：

  • 半同步复制（Semi-Sync Replication）：主库提交事务后至少等待一个从库确认接收到 Binlog（不等重放完成），在数据安全和性能之间折中。MySQL 8.0 默认使用 AFTER_COMMIT 模式。
  • 延迟复制：CHANGE REPLICATION SOURCE TO SOURCE_DELAY = 3600，故意延迟 1 小时，可用于误操作回滚。

题目 8：请解释分库分表的方案、适用场景以及常见问题（分布式事务、跨库 JOIN 等）。 ​

难度： ⭐⭐⭐

标准答案：

一、什么时候需要分库分表？

  • 单表数据量超过 2000 万行（经验值，视行宽和查询模式而定）。
  • 单库 QPS 超过数据库承载能力（如 MySQL 单机 5000+ QPS）。
  • 磁盘 I/O 或网络带宽成为瓶颈。

二、分库 vs 分表 vs 分库分表：

策略场景说明
只分表单表数据量大，但整体数据库压力不大同一实例内水平拆分
只分库单表不大，但整体并发/连接数超限垂直拆分到不同实例
分库分表数据量大 + 并发高同时拆分

三、垂直拆分 vs 水平拆分：

  • 垂直拆分：按业务模块/表拆分（如用户库、订单库、商品库）。每个库的表结构不同。
  • 水平拆分：同一张表按某个分片键拆到多张表/多个库。表结构相同，数据不同。

四、分片策略：

  • Hash 分片：shard_id = hash(user_id) % N，分布均匀，但扩容困难。
  • Range 分片：按范围（如时间、ID 区间）分片，扩容方便，但可能数据不均。
  • 一致性 Hash：虚拟节点解决分布不均问题，扩缩容只影响相邻节点。

五、核心难点与解决方案：

问题解决方案
分布式事务两阶段提交（2PC）、TCC（Try-Confirm-Cancel）、Saga 模式、Seata
跨库 JOIN冗余字段（反范式）、数据同步（Canal/Debezium）、应用层组装
分布式 IDSnowflake、Leaf-segment、UUID（不推荐做主键，聚簇索引下页分裂严重）
跨库分页/排序二次查询、归并排序，或改为按分片键查询避免
数据迁移/扩容双写方案、Canal 增量同步、ShardingSphere-Scaling
聚合函数（COUNT/SUM）各分片执行后应用层汇总

六、中间件：

  • ShardingSphere（Apache）：支持 Sharding-JDBC（客户端）和 Sharding-Proxy（代理端）。
  • MyCat：代理模式，支持读写分离和分库分表。
  • Vitess：Google 开源，基于 Vitess/KV 存储，支持 MySQL 协议。

延伸思考：

  • 先分库分表还是先读写分离？ → 通常是先读写分离（简单），再分库分表（代价大）。分库分表后查询复杂度大幅上升，不到万不得已不要轻易分。
  • MySQL 8.0 的替代方案：先评估是否能通过优化索引、升级硬件、读写分离解决，再考虑分库分表。TiDB 等分布式 NewSQL 也是近年流行的替代路径。

题目 9：MySQL 8.0 中窗口函数和 CTE（Common Table Expression）分别怎么用？请举例说明。 ​

难度： ⭐⭐

标准答案：

一、窗口函数（Window Function）：

窗口函数是 MySQL 8.0 引入的重要特性，允许在不折叠行的情况下执行聚合计算（GROUP BY 会折叠行）。

语法：

sql
窗口函数() OVER (
  [PARTITION BY 列 ...]
  [ORDER BY 列 ...]
  [框架子句 ROWS/RANGE BETWEEN ... AND ...]
)

分类：

类型函数
聚合窗口函数SUM(), AVG(), COUNT(), MAX(), MIN()
排名窗口函数ROW_NUMBER(), RANK(), DENSE_RANK()
偏移窗口函数LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

实用示例：

sql
-- 1. 每个部门内按薪资排名（经典面试题）
SELECT 
    dept_id,
    emp_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_num
FROM employees;

-- 2. 计算每个员工的薪资与部门平均薪资的差值
SELECT 
    emp_name,
    salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff
FROM employees;

-- 3. LAG/LEAD：获取上一行/下一行数据（环比分析）
SELECT 
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_growth
FROM monthly_sales;

-- 4. 累计求和
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROM orders;

ROW_NUMBER vs RANK vs DENSE_RANK 区别：

sql
-- 数据: 100, 100, 90
ROW_NUMBER():  1, 2, 3    -- 无并列，严格递增
RANK():        1, 1, 3    -- 并列后跳号
DENSE_RANK():  1, 1, 2    -- 并列后不跳号

二、CTE（公共表表达式，WITH 语法）：

CTE 允许将复杂 SQL 拆分为命名的临时结果集，提高可读性。支持递归（RECURSIVE）。

非递归 CTE：

sql
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_earners AS (
    SELECT e.emp_name, e.salary, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.dept_id = d.dept_id
    WHERE e.salary > d.avg_salary
)
SELECT * FROM high_earners ORDER BY salary DESC;

递归 CTE：

sql
WITH RECURSIVE emp_hierarchy AS (
    -- 锚点查询
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询
    SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy ORDER BY level;

延伸思考：

  • CTE 是否提升性能？→ CTE 本身是语法糖，执行计划和子查询等价。但 CTE 可以引用多次而不重复计算（MySQL 8.0 中 CTE 可能被物化，注意内存使用）。
  • MySQL 8.0.14+ 增强了窗口函数支持：可以在 GROUP BY 后使用窗口函数（之前不行）。
  • 窗口函数在分页中非常实用：如"取每组的前 N 名"（Top-N per Group）。

题目 10：MySQL 8.0 有哪些重要的新特性？请从 JSON 支持和性能调优角度重点说明。 ​

难度： ⭐⭐⭐

标准答案：

MySQL 8.0 是一个大版本升级，包含大量重要新特性。

一、JSON 支持（MySQL 5.7 引入，8.0 大幅增强）：

1. JSON 数据类型和函数：

sql
-- 创建带 JSON 列的表
CREATE TABLE user_profile (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    attributes JSON,
    INDEX idx_json_attrs ((CAST(attributes AS CHAR(255))))
);

-- 插入 JSON 数据
INSERT INTO user_profile VALUES 
(1, 'Alice', '{"age": 28, "skills": ["Python", "MySQL"], "address": {"city": "Beijing"}}');

-- JSON 函数查询
SELECT attributes->>'$.age' AS age FROM user_profile WHERE id = 1;            -- 28
SELECT attributes->'$.skills' AS skills FROM user_profile WHERE id = 1;       -- ["Python", "MySQL"]
SELECT JSON_EXTRACT(attributes, '$.address.city') FROM user_profile;          -- "Beijing"
SELECT JSON_CONTAINS(attributes, '"Python"', '$.skills');                      -- 1 (true)
SELECT JSON_KEYS(attributes);                                                 -- ["age", "skills", "address"]

-- JSON 修改函数
UPDATE user_profile 
SET attributes = JSON_SET(attributes, '$.age', 29)
WHERE id = 1;

-- JSON_TABLE（MySQL 8.0 新增）：将 JSON 数组展开为关系表
SELECT *
FROM user_profile,
JSON_TABLE(attributes->'$.skills', '$[*]' COLUMNS(skill VARCHAR(50) PATH '$')) AS skills;

2. MySQL 8.0 的 JSON 增强：

  • JSON Table Functions：JSON_TABLE() 将 JSON 数据转换为关系表，可用于 JOIN。
  • JSON aggregation：JSON_ARRAYAGG() 和 JSON_OBJECTAGG()。
  • 多值索引（Multi-Valued Index）（MySQL 8.0.17+）：为 JSON 数组中的元素创建索引，大幅提升 JSON 查询性能。
sql
-- 多值索引
ALTER TABLE user_profile 
ADD INDEX idx_skills (
    (CAST(attributes->'$.skills[*]' AS UNSIGNED ARRAY))
);

-- 查询自动使用多值索引
SELECT * FROM user_profile 
WHERE JSON_CONTAINS(attributes->'$.skills', '"Python"');

二、性能调优相关新特性：

1. 索引增强：

  • 降序索引（Descending Index）：真正的物理降序存储，之前版本只是语法支持。
  • 隐藏索引（Invisible Index）：ALTER TABLE t ALTER INDEX idx INVISIBLE，用于灰度测试/下线索引。
  • 函数索引（Functional Index）（MySQL 8.0.13+）：对表达式的值建索引。
sql
-- 函数索引：解决 WHERE LOWER(name) = 'alice' 无法走索引的问题
ALTER TABLE users ADD INDEX idx_lower_name ((LOWER(name)));

2. 查询优化器增强：

  • 不可见索引：安全删除/测试索引。
  • 直方图（Histogram）：ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH N BUCKETS，为优化器提供更精确的数据分布信息。
  • 成本模型改进：更准确估算查询成本。

3. CTE 和窗口函数（见题目 9）。

4. 数据字典重构：

  • MySQL 8.0 将元数据从 .frm 文件迁移到 InnoDB 数据字典表，DDL 操作原子性更强。

5. 角色管理：

sql
CREATE ROLE app_developer;
GRANT SELECT, INSERT, UPDATE ON mydb.* TO app_developer;
GRANT app_developer TO user1;

6. 其他重要特性：

  • 原子 DDL：DDL 操作要么全部成功，要么全部回滚。
  • 密码和账户管理增强：密码历史记录、密码复用限制、双密码。
  • 资源组（Resource Groups）：将线程分配到不同资源组，控制 CPU 优先级。
  • Clone Plugin：快速克隆实例/复制数据。

延伸思考：

  • JSON 类型 vs 独立列：JSON 适合灵活/稀疏数据，但查询性能远不如独立列+索引。在高频查询字段上应避免使用 JSON 存储关键数据。
  • 多值索引是 MySQL 8.0 对 JSON 的杀手级改进，但仅适用于简单类型（字符串、整数），复杂嵌套 JSON 的性能仍需谨慎评估。
  • MySQL 8.0 的函数索引可以解决很多 "索引失效" 的老问题，如 WHERE DATE(created_at) = '2024-01-01' 现在可以通过函数索引优化。

📝 本面试题覆盖 MySQL 8.0 核心知识点，建议结合实际项目经验进行深入准备。