MySQL 深入理解-专辑demo

samsung_samsung

281人浏览 · 2026-06-30 21:23:18

samsung_samsung · 2026-06-30 21:23:18 发布

从基础架构到生产实践，全面掌握 MySQL 核心技术

第1章 MySQL 体系结构与运行机制

1.1 MySQL 整体架构

MySQL 采用分层架构设计，从上到下分为四层：

┌─────────────────────────────────────────────┐
│            客户端连接层 (Connectors)          │
│  JDBC / ODBC / PHP / Python / Go / Navicat  │
├─────────────────────────────────────────────┤
│            服务层 (MySQL Server)             │
│  ┌─────────┐ ┌──────────┐ ┌──────────────┐ │
│  │连接管理  │ │SQL接口    │ │解析器(Parser)│ │
│  └─────────┘ └──────────┘ └──────────────┘ │
│  ┌───────────┐ ┌─────────────────────────┐  │
│  │优化器      │ │缓存(Buffer/Cache)       │  │
│  │(Optimizer)│ │8.0已移除查询缓存         │  │
│  └───────────┘ └─────────────────────────┘  │
├─────────────────────────────────────────────┤
│            存储引擎层 (Storage Engine)       │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌───────────┐  │
│  │InnoDB│ │MyISAM│ │Memory│ │Archive... │  │
│  └──────┘ └──────┘ └──────┘ └───────────┘  │
├─────────────────────────────────────────────┤
│            文件系统层 (File System)          │
│  数据文件 / 日志文件 / 配置文件 / Socket     │
└─────────────────────────────────────────────┘

1.1.1 连接层

连接管理：处理客户端连接、认证、授权
连接池：复用已建立的连接，减少连接开销
线程模型：每个连接分配一个线程（One-Thread-Per-Connection）

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 查看连接详情
SHOW PROCESSLIST;

1.1.2 服务层

服务层是 MySQL 的核心，包含 SQL 处理的完整流程：

组件	功能
连接管理	认证、授权、连接池管理
SQL 接口	接收 DML/DDL/DCL 语句
解析器	词法分析、语法分析，生成解析树
优化器	选择最优执行计划（CBO）
执行器	调用存储引擎接口执行查询

SQL 执行流程：

客户端发送SQL
    → 连接管理（认证/授权）
    → 解析器（词法/语法分析）
    → 优化器（生成执行计划）
    → 执行器（调用存储引擎API）
    → 返回结果

1.1.3 存储引擎层

MySQL 的插件式存储引擎架构是其最大特色：

-- 查看支持的存储引擎
SHOW ENGINES;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'user'\G

-- 修改表的存储引擎
ALTER TABLE user ENGINE = InnoDB;

1.2 MySQL 8.0 新架构特性

1.2.1 数据字典重构

MySQL 8.0 使用事务性数据字典替代了旧的 .frm 文件：

特性	5.7	8.0
元数据存储	.frm/.par 文件	InnoDB 表
原子 DDL	不支持	支持
DDL 回滚	不支持	支持
信息查询	INFORMATION_SCHEMA	PERFORMANCE_SCHEMA 优化

1.2.2 原子 DDL

-- MySQL 8.0 原子DDL：要么全部成功，要么全部回滚
CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
-- 如果创建失败，不会留下残余文件

-- 8.0之前：DROP TABLE t1, t2; 如果t2不存在，t1已被删除
-- 8.0：原子操作，t1和t2要么都删，要么都不删
DROP TABLE IF EXISTS t1, t2;

1.2.3 默认字符集变更

-- 5.7 默认字符集
-- character_set_server = latin1
-- collation_server = latin1_swedish_ci

-- 8.0 默认字符集
-- character_set_server = utf8mb4
-- collation_server = utf8mb4_0900_ai_ci

-- 查看当前字符集
SHOW VARIABLES LIKE 'character_set_server';

1.3 MySQL 查询执行流程详解

1.3.1 查询缓存（8.0 已移除）

MySQL 8.0 移除了查询缓存功能，原因：

并发性能差：查询缓存加全局锁，高并发下成为瓶颈
命中率低：任何表修改都导致相关缓存失效
内存浪费：缓存大量无效结果

替代方案：使用 Redis 等外部缓存。

1.3.2 解析器工作原理

SELECT name, age FROM user WHERE id = 1;

词法分析：识别关键字 SELECT、FROM、WHERE，表名 user，列名 name/age/id

语法分析：验证 SQL 语法正确性，生成解析树（Parse Tree）

1.3.3 优化器工作原理

优化器基于代价模型（CBO）选择执行计划：

-- 查看优化器决策过程
EXPLAIN FORMAT=JSON SELECT * FROM user WHERE age > 20;

-- 查看优化器追踪
SET optimizer_trace = 'enabled=on';
SELECT * FROM user WHERE age > 20;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

1.3.4 执行器工作原理

执行器根据执行计划调用存储引擎接口：

-- 慢查询日志记录执行时间
SET long_query_time = 1;
SET slow_query_log = ON;

-- 查看执行状态
SHOW STATUS LIKE 'Handler%';

1.4 MySQL 9.0 新特性概览

特性	说明
InnoDB 引擎重构	事务处理速度提升 30%+
AI/ML 集成	内置 ML_SERVICES 插件
云原生优化	原生 K8s 支持、S3 云存储
安全增强	TLS 1.3 默认、国密 SM4、列级脱敏
性能优化	索引推荐、动态内存分配

面试实战题

题目 1：MySQL 一条 SQL 查询语句是如何执行的？

答：

完整执行流程：

连接器：客户端建立 TCP 连接，进行用户认证和权限校验
解析器：词法分析识别 SQL 关键字和对象名，语法分析验证语法正确性，生成解析树
优化器：基于代价模型选择最优执行计划，包括选择索引、决定连接顺序等
执行器：根据执行计划调用存储引擎 API，逐行获取数据并返回结果

关键点：

连接器会缓存该用户的权限信息，中途修改权限需重新连接才生效
8.0 移除了查询缓存，每次查询都走完整流程
优化器可能选错索引，可通过 FORCE INDEX 强制指定

题目 2：MySQL 为什么采用插件式存储引擎架构？有什么好处？

答：

设计理念：将 SQL 处理与数据存储解耦，服务层统一处理 SQL 解析和优化，存储引擎层负责数据的实际存取。

好处：

灵活性：不同业务场景选择不同引擎（InnoDB 事务、Memory 临时表、Archive 归档）
可扩展：第三方可开发自定义存储引擎
解耦：上层优化器不需要关心底层存储细节
演进性：引擎可以独立升级和优化

代价：跨引擎功能受限（如跨引擎事务、外键）

题目 3：MySQL 8.0 为什么要移除查询缓存？

答：

移除原因：

全局锁竞争：查询缓存使用全局互斥锁，高并发下严重阻塞
命中率低：任何对表的写操作都会使该表所有缓存失效，写密集场景命中率接近 0
额外开销：每次查询都要检查缓存，缓存未命中时反而增加延迟
内存浪费：大量无效缓存占用宝贵内存

替代方案：应用层使用 Redis/Memcached 作为缓存层，更灵活且不影响数据库性能。

题目 4：什么是原子 DDL？MySQL 8.0 的原子 DDL 有什么意义？

答：

原子 DDL：DDL 操作（CREATE/DROP/ALTER）要么完全成功，要么完全回滚，不会留下中间状态。

8.0 之前的问题：

DROP TABLE t1, t2; 如果 t2 不存在，t1 已被删除，操作部分成功
CREATE TABLE 失败可能留下 .frm 文件残留

8.0 的改进：

DDL 操作写入 Redo Log 和 Binlog，支持崩溃恢复
DROP TABLE t1, t2; 要么都删，要么都不删
CREATE TABLE 失败不会留下残余文件
数据字典更新与存储引擎操作在同一事务中

题目 5：MySQL 的连接器、解析器、优化器、执行器各自的作用是什么？

答：

组件	作用	关键点
连接器	管理 client 连接、认证、授权	长连接内存占用（8.0 自动断开）
解析器	词法/语法分析，生成解析树	语法错误在此阶段报出
优化器	选择最优执行计划	CBO 代价模型，可能选错索引
执行器	调用存储引擎 API 执行	检查权限，逐行获取数据

执行器权限检查：执行器在获取行之前会检查当前用户是否有该表的查询权限，这也是为什么有时 SELECT 报权限错误而非语法错误。

题目 6：MySQL 8.0 和 5.7 在架构上有哪些核心差异？

答：

维度	5.7	8.0
数据字典	.frm 文件	InnoDB 事务性数据字典
查询缓存	支持	移除
原子 DDL	不支持	支持
默认字符集	latin1	utf8mb4
认证插件	mysql_native_password	caching_sha2_password
窗口函数	不支持	支持
CTE	不支持	支持（递归/非递归）
降序索引	语法支持但实际 ASC	真正支持 DESC
函数索引	不支持	支持
角色管理	不支持	支持
InnoDB Redo	单一 ib_logfile0/1	多 redo log 文件
信息 Schema	查询慢	PERFORMANCE_SCHEMA 优化

第2章 InnoDB 存储引擎架构

2.1 InnoDB 整体架构

┌──────────────────────────────────────────────────────────┐
│                    InnoDB 存储引擎                        │
├────────────────────────┬─────────────────────────────────┤
│    内存结构 (In-Memory) │     磁盘结构 (On-Disk)          │
│                        │                                 │
│  ┌──────────────┐      │  ┌──────────────┐              │
│  │ Buffer Pool  │      │  │ System       │              │
│  │ (缓冲池)     │      │  │ Tablespace   │              │
│  └──────────────┘      │  └──────────────┘              │
│  ┌──────────────┐      │  ┌──────────────┐              │
│  │ Change Buffer│      │  │ File-Per-    │              │
│  │ (写缓冲)     │      │  │ Table        │              │
│  └──────────────┘      │  │ Tablespace   │              │
│  ┌──────────────┐      │  └──────────────┘              │
│  │ Log Buffer   │      │  ┌──────────────┐              │
│  │ (日志缓冲)   │      │  │ General      │              │
│  └──────────────┘      │  │ Tablespace   │              │
│  ┌──────────────┐      │  └──────────────┘              │
│  │ Adaptive Hash│      │  ┌──────────────┐              │
│  │ Index (AHI)  │      │  │ Redo Log     │              │
│  └──────────────┘      │  └──────────────┘              │
│                        │  ┌──────────────┐              │
│                        │  │ Undo         │              │
│                        │  │ Tablespace   │              │
│                        │  └──────────────┘              │
├────────────────────────┴─────────────────────────────────┤
│                  后台线程 (Background Threads)            │
│  Master Thread │ IO Thread │ Purge Thread │ Page Cleaner │
└──────────────────────────────────────────────────────────┘

2.2 Buffer Pool（缓冲池）

2.2.1 核心设计

Buffer Pool 是 InnoDB 内存中最重要的组件，缓存热点数据页和索引页，减少磁盘 I/O：

Buffer Pool
├── 数据页（Data Page）—— 表数据
├── 索引页（Index Page）—— B+树节点
├── Undo 页 —— Undo Log
├── Change Buffer 页 —— 二级索引变更
├── 自适应哈希索引 —— AHI
└── 锁信息 —— 行锁/表锁元数据

2.2.2 LRU 算法改进

InnoDB 对传统 LRU 进行了关键改进，采用冷热分离策略：

┌──────────────── 热端 (Young Region, 5/8) ────────────────┐
│  [热点页1] → [热点页2] → [热点页3] → ... → [热点页N]     │
├──────────────── 中间点 (Midpoint) ────────────────────────┤
│  [新加载页] → [冷页1] → [冷页2] → ... → [冷页N]         │
└──────────────── 冷端 (Old Region, 3/8) ──────────────────┘

关键机制：

冷热分区：热端占 5/8，冷端占 3/8
中间插入：新加载的页插入冷端头部，而非热端头部
老生常谈时间：innodb_old_blocks_time（默认 1000ms），冷端页需被访问超过此时间才晋升热端
防止全表扫描污染：大表扫描的页先入冷端，短时间不晋升

-- 查看 Buffer Pool 状态
SELECT POOL_ID, POOL_SIZE, DATABASE_PAGES, FREE_BUFFERS,
       MODIFIED_DATABASE_PAGES
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 计算缓存命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

2.2.3 三大链表

链表	作用	说明
Free List	空闲页链表	管理未使用的缓存页
LRU List	最近最少使用链表	冷热分离，管理数据页淘汰
Flush List	脏页链表	记录被修改但未刷盘的页

2.2.4 关键配置

-- Buffer Pool 大小（建议物理内存的 50%~80%）
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024; -- 8GB

-- 多实例（减少锁竞争，建议每 1GB 一个实例）
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 冷端比例（默认 37%）
SET GLOBAL innodb_old_blocks_pct = 37;

-- 冷端晋升时间（默认 1000ms）
SET GLOBAL innodb_old_blocks_time = 1000;

-- 最大脏页比例
SET GLOBAL innodb_max_dirty_pages_pct = 10;

2.3 Change Buffer（写缓冲）

2.3.1 设计原理

Change Buffer 针对非唯一二级索引的 DML 操作进行优化：

传统流程：
  INSERT → 读取二级索引页(随机IO) → 修改页 → 写回

Change Buffer 流程：
  INSERT → 将变更记录到 Change Buffer → 后台合并(merge)

适用条件：

仅适用于非唯一二级索引（唯一索引需要立即检查唯一性）
索引页不在 Buffer Pool 中时才生效
适合写多读少的场景

2.3.2 工作流程

1. DML 操作修改二级索引
2. 检查索引页是否在 Buffer Pool
   ├── 在 Buffer Pool → 直接修改
   └── 不在 Buffer Pool → 记录到 Change Buffer
3. 后台线程异步合并（merge）
   ├── 访问该索引页时触发合并
   ├── 后台 Master Thread 定期合并
   └── 数据库关闭时合并

2.3.3 配置与监控

-- Change Buffer 最大占比（默认 25%）
SET GLOBAL innodb_change_buffer_max_size = 25;

-- Change Buffer 类型（inserts/deletes/purges/all/none）
SET GLOBAL innodb_change_buffering = all;

-- 查看 Change Buffer 状态
SELECT * FROM information_schema.INNODB_METRICS
WHERE NAME LIKE '%change_buffer%';

2.4 Log Buffer（日志缓冲）

2.4.1 Redo Log Buffer

Log Buffer 是 Redo Log 的内存缓冲区，事务修改先写入 Log Buffer，再刷入磁盘：

事务修改 → Log Buffer → Redo Log File (磁盘)
              ↑              ↑
         innodb_log_buffer_size   innodb_flush_log_at_trx_commit

-- Log Buffer 大小（默认 16MB）
SET GLOBAL innodb_log_buffer_size = 16 * 1024 * 1024;

-- Redo Log 刷盘策略（关键参数）
-- 0: 每秒刷盘（可能丢失1秒数据）
-- 1: 每次事务提交刷盘（最安全，默认）
-- 2: 每次提交写入OS缓存，每秒fsync
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

2.5 自适应哈希索引（AHI）

2.5.1 工作原理

InnoDB 自动为频繁访问的索引页构建哈希索引：

B+树查找：根节点 → 中间节点 → 叶子节点（3~4次比较）
AHI查找：哈希计算 → 直接定位（1次查找）

触发条件：

同一索引页被连续访问超过一定次数
访问模式为等值查询（WHERE id = ?）

-- 查看 AHI 状态
SHOW ENGINE INNODB STATUS\G
-- 搜索 "INSERT BUFFER AND ADAPTIVE HASH INDEX" 部分

-- 开启/关闭 AHI
SET GLOBAL innodb_adaptive_hash_index = ON;

AHI 的利弊：

优势	劣势
等值查询加速	占用 Buffer Pool 内存
减少 B+ 树遍历	高并发更新时 AHI 锁竞争
自动管理	Like/Range 查询无效

2.6 后台线程

2.6.1 Master Thread

核心后台线程，负责：

每秒：刷新脏页、合并 Change Buffer、刷新 Redo Log
每 10 秒：刷新脏页、合并 Change Buffer、删除无用 Undo Log
每次空闲：刷新脏页

2.6.2 IO Thread

-- 查看 IO 线程状态
SHOW ENGINE INNODB STATUS\G
-- --------
-- FILE I/O
-- --------
-- I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
-- I/O thread 1 state: waiting for completed aio requests (log thread)
-- I/O thread 2 state: waiting for completed aio requests (read thread)
-- I/O thread 3 state: waiting for completed aio requests (read thread)
-- I/O thread 4 state: waiting for completed aio requests (write thread)
-- I/O thread 5 state: waiting for completed aio requests (write thread)
-- I/O thread 6 state: waiting for completed aio requests (write thread)
-- I/O thread 7 state: waiting for completed aio requests (write thread)

-- 配置 IO 线程数
SET GLOBAL innodb_read_io_threads = 4;
SET GLOBAL innodb_write_io_threads = 4;

2.6.3 Purge Thread

清理无用的 Undo Log：

-- Purge 线程数（默认 4）
SET GLOBAL innodb_purge_threads = 4;

2.6.4 Page Cleaner Thread

负责脏页刷盘，减轻 Master Thread 压力：

-- Page Cleaner 线程数
SET GLOBAL innodb_page_cleaners = 4;

2.7 磁盘结构

2.7.1 表空间类型

表空间	说明	文件
System Tablespace	系统表空间，包含数据字典	ibdata1
File-Per-Table	每表独立表空间	tablename.ibd
General Tablespace	通用表空间，多表共享	自定义
Undo Tablespace	Undo Log 表空间	undo_001, undo_002
Temporary Tablespace	临时表空间	ibtmp1
Redo Log	重做日志	ib_logfile0/1 → #innodb_redo/*

-- 开启独立表空间（8.0 默认开启）
SET GLOBAL innodb_file_per_table = ON;

-- 查看表空间信息
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME = 'test/user';

2.7.2 逻辑存储结构

Tablespace (表空间)
  └── Segment (段)
        ├── Data Segment (数据段/叶子节点段)
        ├── Index Segment (索引段/非叶子节点段)
        └── Rollback Segment (回滚段)
              └── Extent (区, 1MB = 64个页)
                    └── Page (页, 16KB)
                          └── Row (行)
                                ├── DB_TRX_ID (6B, 事务ID)
                                ├── DB_ROLL_PTR (7B, 回滚指针)
                                └── DB_ROW_ID (6B, 隐藏主键)

面试实战题

题目 1：Buffer Pool 的 LRU 算法为什么不用传统 LRU？

答：

传统 LRU 的问题：全表扫描会将大量冷数据加载到 LRU 头部，把真正的热数据挤出缓存（缓存污染）。

InnoDB 的改进：

冷热分区：LRU 分为 Young（5/8）和 Old（3/8）两个区域
中间插入：新加载的页插入 Old 区头部，而非 Young 区头部
时间窗口：innodb_old_blocks_time（默认 1000ms），Old 区的页必须被访问超过此时间才晋升到 Young 区
效果：全表扫描的页进入 Old 区后很快被淘汰，不会污染 Young 区的热数据

题目 2：Change Buffer 的适用场景和限制是什么？

答：

适用场景：

非唯一二级索引的 INSERT/UPDATE/DELETE
写多读少（DML 操作不立即读取索引页）
索引页不在 Buffer Pool 中

限制：

不支持主键索引（聚簇索引）
不支持唯一二级索引（需要立即检查唯一性）
不支持全文索引和空间索引
读取索引页时会触发 merge，读多写少场景反而增加开销

关闭场景：SSD 磁盘随机 I/O 性能好，Change Buffer 收益有限；读多写少场景，频繁 merge 增加开销。

题目 3：innodb_flush_log_at_trx_commit 的三个值有什么区别？

答：

值	行为	安全性	性能
0	每秒刷盘，事务提交不触发	最低（可能丢1秒数据）	最高
1	每次事务提交都 fsync	最高（不丢数据）	最低
2	每次提交写 OS 缓存，每秒 fsync	中等（OS 崩溃才丢）	中等

生产建议：

核心业务（金融/订单）：设为 1
非核心业务（日志/统计）：可设为 2
配合 sync_binlog = 1 使用，保证主从数据一致

题目 4：InnoDB 的三大链表分别是什么？各自的作用？

答：

Free List（空闲链表）：管理未被使用的缓存页。当需要加载新页时，从 Free List 取空闲页；Free List 为空时，从 LRU List 淘汰旧页。
LRU List（最近最少使用链表）：按访问顺序管理缓存页，冷热分区。内存不足时从 Old 区尾部淘汰。记录所有被使用的数据页和索引页。
Flush List（脏页链表）：记录所有被修改但未刷盘的脏页，按修改时间排序。后台线程从 Flush List 选取脏页刷盘。

关系：一个缓存页可以同时在 LRU List 和 Flush List 中（脏页），但不会在 Free List 中。

题目 5：InnoDB 的逻辑存储结构是怎样的？

答：

从大到小：表空间 → 段 → 区 → 页 → 行

表空间：InnoDB 存储的最高层，一个表空间可包含多个段
段：分为数据段（B+树叶子节点）、索引段（非叶子节点）、回滚段
区：1MB，由 64 个连续的 16KB 页组成，保证页的连续性
页：16KB，InnoDB 磁盘管理的最小单位，包含多行数据
行：每行数据包含用户字段 + 3 个隐藏字段（DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID）

题目 6：自适应哈希索引（AHI）什么时候应该关闭？

答：

关闭场景：

高并发更新：AHI 使用 rw-lock，高并发更新时锁竞争严重
多范围查询：AHI 只对等值查询有效，范围查询无收益
内存紧张：AHI 占用 Buffer Pool 空间，内存不足时得不偿失
不稳定访问模式：访问模式频繁变化，AHI 频繁构建/拆除

开启场景：

大量等值查询（WHERE id = ?）
访问模式稳定
Buffer Pool 充足

可通过 SHOW ENGINE INNODB STATUS 查看 AHI 的命中率和锁等待情况来决定。

第3章 InnoDB 日志系统：Redo Log 与 Undo Log

3.1 WAL 机制

3.1.1 Write-Ahead Logging 原理

WAL（Write-Ahead Logging）是 InnoDB 的核心设计原则：先写日志，再写磁盘。

传统方式：
  修改数据页 → 随机写磁盘（慢）

WAL方式：
  修改数据页(Buffer Pool) → 顺序写Redo Log（快）
  → 后台异步刷脏页到磁盘

核心优势：

顺序写远快于随机写（磁盘顺序写 ~100MB/s vs 随机写 ~1MB/s）
崩溃恢复时重放 Redo Log 即可恢复数据

3.2 Redo Log（重做日志）

3.2.1 Redo Log 架构

┌─────────────────────────────────────────────┐
│              Redo Log 架构                    │
│                                              │
│  事务修改 → Log Buffer → Redo Log File      │
│               ↑              ↑               │
│         log_buffer_size   flush_at_trx_commit│
│                                              │
│  ┌──────────────────────────────────────┐    │
│  │  Redo Log File (循环写入)            │    │
│  │  ┌────┐ ┌────┐ ┌────┐ ┌────┐       │    │
│  │  │ ib1 │ │ ib2 │ │ ib3 │ │ ib4 │   │    │
│  │  └────┘ └────┘ └────┘ └────┘       │    │
│  │   ↑ write pos          ↑ checkpoint │    │
│  │   (写入位置)            (检查点)     │    │
│  └──────────────────────────────────────┘    │
└─────────────────────────────────────────────┘

3.2.2 循环写入机制

Redo Log 采用固定大小、循环写入的方式：

write pos (当前写入位置)
    ↓
┌───┬───┬───┬───┬───┬───┬───┬───┐
│ ✓ │ ✓ │ ✓ │   │   │   │ ✓ │ ✓ │
└───┴───┴───┴───┴───┴───┴───┴───┘
                        ↑
                   checkpoint (已刷盘位置)

空闲空间 = write pos → checkpoint 之间的距离
当 write pos 追上 checkpoint → 需要先推进 checkpoint（刷脏页）

3.2.3 LSN（Log Sequence Number）

LSN 是 Redo Log 的全局递增序号，贯穿整个 InnoDB：

-- 查看当前 LSN
SHOW ENGINE INNODB STATUS\G
-- Log sequence number: 1234567890  (当前Redo Log写入位置)
-- Log flushed up to:   1234567880  (已刷盘位置)
-- Pages flushed up to: 1234567800  (脏页刷盘位置)
-- Last checkpoint at:  1234567700  (检查点位置)

-- LSN 关系
-- Log sequence number >= Log flushed >= Pages flushed >= Last checkpoint

3.2.4 MySQL 8.0 Redo Log 改进

-- 8.0 之前：固定2个文件 ib_logfile0/1
-- 8.0：支持多 Redo Log 文件，动态调整

-- 查看 Redo Log 配置
SELECT @@innodb_log_files_in_group;  -- 文件数量
SELECT @@innodb_log_file_size;       -- 单文件大小
SELECT @@innodb_log_group_home_dir;  -- 存储目录

-- 8.0 动态修改 Redo Log 大小（在线修改）
ALTER INSTANCE ENABLE INNODB REDO_LOG;   -- 启用
ALTER INSTANCE DISABLE INNODB REDO_LOG;  -- 禁用（加速批量导入）

3.2.5 Checkpoint 机制

Checkpoint 推进已刷盘位置，释放 Redo Log 空间：

类型	触发条件	说明
Sharp Checkpoint	正常关闭数据库	将所有脏页刷盘
Fuzzy Checkpoint	运行时异步刷盘	多种触发条件

Fuzzy Checkpoint 触发条件：

Master Thread 定期刷脏
Redo Log 空间不足（write pos 接近 checkpoint）
脏页比例超过 innodb_max_dirty_pages_pct
前台查询需要淘汰 LRU 脏页

3.3 Undo Log（回滚日志）

3.3.1 Undo Log 的两大作用

事务回滚：保存数据修改前的状态，ROLLBACK 时恢复
MVCC：为一致性读提供历史版本

3.3.2 Undo Log 类型

类型	产生操作	生命周期
Insert Undo Log	INSERT	事务提交后可立即删除
Update Undo Log	UPDATE/DELETE	需等所有活跃事务不再需要该版本

3.3.3 版本链

当前行数据 (trx_id=5, roll_ptr → undo3)
    ↓
Undo Log 3 (trx_id=4, roll_ptr → undo2)  -- 第3次修改
    ↓
Undo Log 2 (trx_id=3, roll_ptr → undo1)  -- 第2次修改
    ↓
Undo Log 1 (trx_id=2, roll_ptr = NULL)   -- 第1次插入

3.3.4 Undo Tablespace

-- 8.0 Undo 配置
SELECT @@innodb_undo_tablespaces;    -- Undo 表空间数量
SELECT @@innodb_max_undo_log_size;   -- Undo 表空间最大大小（默认1GB）
SELECT @@innodb_undo_log_truncate;   -- 自动截断（默认ON）

-- 查看 Undo 表空间
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo';

3.3.5 Purge 线程与 Undo 清理

Purge 线程工作流程：
1. 找到最早活跃事务的 trx_id
2. 遍历 Undo Log 版本链
3. 如果某版本对所有活跃事务都不可见 → 可以清理
4. 物理删除已标记为 deleted 的行
5. 释放 Undo Log 空间

长事务的危害：长事务持有旧的 Read View，导致大量 Undo Log 无法被 Purge，表空间持续膨胀。

-- 查看运行时间最长的事务
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

-- 查看 Undo Log 统计
SHOW STATUS LIKE 'Innodb_undo_log%';

3.4 Redo Log 与 Undo Log 的协作

3.4.1 事务提交流程

BEGIN;
UPDATE user SET name='Tom' WHERE id=1;

1. 读取数据页到 Buffer Pool
2. 记录 Undo Log（旧值 name='Jerry'）
3. 修改数据页（name='Tom'）
4. 记录 Redo Log（物理修改记录）
5. 事务提交：
   a. Redo Log 刷盘（innodb_flush_log_at_trx_commit=1）
   b. 事务状态标记为已提交

COMMIT;

3.4.2 崩溃恢复流程

MySQL 崩溃重启
    ↓
1. 从 Last Checkpoint LSN 开始扫描 Redo Log
    ↓
2. 重做（Redo）：重放 Redo Log，恢复已提交事务的修改
    ↓
3. 回滚（Undo）：回滚未提交事务的修改
    ↓
4. 恢复完成，对外提供服务

3.4.3 ACID 特性的实现

特性	实现机制
原子性 (A)	Undo Log（回滚未提交事务）
一致性 ©	原子性 + 隔离性 + 持久性共同保证
隔离性 (I)	MVCC + 锁机制
持久性 (D)	Redo Log（重放已提交事务）

3.5 Binlog 与 Redo Log 的区别

维度	Redo Log	Binlog
产生者	InnoDB 引擎	MySQL Server 层
内容	物理日志（页修改）	逻辑日志（SQL/行变更）
写入方式	循环写入，空间固定	追加写入，文件递增
用途	崩溃恢复	主从复制、数据恢复
引擎	仅 InnoDB	所有存储引擎

3.5.1 两阶段提交

保证 Redo Log 和 Binlog 的一致性：

事务提交：
1. Prepare 阶段：写入 Redo Log，标记为 prepare 状态
2. 写入 Binlog
3. Commit 阶段：Redo Log 标记为 commit 状态

崩溃恢复规则：
- Redo Log = prepare + Binlog 完整 → 提交事务
- Redo Log = prepare + Binlog 不完整 → 回滚事务

面试实战题

题目 1：WAL 机制是什么？为什么 MySQL 使用 WAL？

答：

WAL（Write-Ahead Logging）即先写日志再写磁盘。核心思想：将随机写转化为顺序写。

为什么使用 WAL：

性能：顺序写 Redo Log（_{100MB/s）远快于随机写数据页（}1MB/s）
持久性：事务提交时只需保证 Redo Log 刷盘，数据页可异步刷盘
崩溃恢复：通过重放 Redo Log 恢复已提交事务的修改

工作流程：修改数据 → 写入 Buffer Pool → 记录 Redo Log → 后台异步刷脏页

题目 2：Redo Log 为什么采用循环写入？写满了怎么办？

答：

循环写入原因：Redo Log 只需要保留从 Checkpoint 到当前的数据，更早的 Redo Log 对应的脏页已经刷盘，不再需要。

写满处理：

write pos 追上 checkpoint，Redo Log 空间不足
触发 Fuzzy Checkpoint，强制刷脏页
推进 checkpoint 位置，释放 Redo Log 空间
如果刷脏速度跟不上写入速度，用户线程会被阻塞

优化：

增大 Redo Log 文件大小
增加脏页刷盘速度（innodb_io_capacity）
避免大事务产生过多 Redo Log

题目 3：什么是两阶段提交？为什么需要两阶段提交？

答：

两阶段提交保证 Redo Log 和 Binlog 的一致性。

流程：

Prepare：写入 Redo Log，标记 prepare
写入 Binlog
Commit：Redo Log 标记 commit

不用两阶段提交的问题：

先写 Redo Log 后写 Binlog：Redo Log 有记录但 Binlog 没有，从库少数据
先写 Binlog 后写 Redo Log：Binlog 有记录但 Redo Log 没有，主库少数据

崩溃恢复规则：

Redo Log prepare + Binlog 完整 → 提交
Redo Log prepare + Binlog 不完整 → 回滚

题目 4：长事务对 Undo Log 有什么影响？如何处理？

答：

影响：

Undo Log 无法被 Purge，表空间持续膨胀
占用大量 Buffer Pool 空间
影响其他事务的版本链遍历性能
可能导致 Undo 表空间耗尽

处理方法：

监控长事务，设置 innodb_kill_idle_transaction
设置 wait_timeout 和 interactive_timeout 自动断开空闲连接
开启 innodb_undo_log_truncate 自动截断 Undo 表空间
业务层避免长事务，拆分大事务
定期检查 information_schema.INNODB_TRX

题目 5：innodb_flush_log_at_trx_commit 和 sync_binlog 如何配合？

答：

组合	数据安全性	性能	适用场景
flush=1 + sync=1	最高	最低	金融/核心业务
flush=1 + sync=N	高	中等	一般业务
flush=2 + sync=1	中高	中等	可接受少量丢失
flush=2 + sync=N	低	最高	日志/统计

最佳实践：

核心业务：innodb_flush_log_at_trx_commit=1 + sync_binlog=1
非核心业务：可适当降低安全级别换取性能
两者必须配合，否则可能出现主从不一致

题目 6：Redo Log 和 Binlog 有什么区别？

答：

维度	Redo Log	Binlog
层级	InnoDB 引擎层	MySQL Server 层
内容	物理日志（某个页某个偏移量的修改）	逻辑日志（SQL语句或行变更）
写入方式	循环写入，空间可复用	追加写入，文件不断递增
用途	崩溃恢复	主从复制、数据恢复
引擎	仅 InnoDB	所有引擎
事务	事务进行中持续写入	事务提交时一次性写入

关键区别：Redo Log 是物理日志，记录"页的哪个位置改了什么"；Binlog 是逻辑日志，记录"执行了什么SQL"或"行从什么变成了什么"。

第4章 InnoDB 索引原理

4.1 索引概述

4.1.1 索引的分类

分类维度	类型	说明
数据结构	B+树索引、Hash索引、全文索引	InnoDB 默认 B+树
物理存储	聚簇索引、二级索引	主键 vs 非主键
逻辑功能	主键索引、唯一索引、普通索引、前缀索引	不同约束
字段个数	单列索引、联合索引	一个 vs 多个字段

4.2 B+树索引

4.2.1 B+树结构

                     [根节点: 50]
                    /            \
          [中间节点: 20,40]    [中间节点: 60,80]
         /     |     \        /     |     \
    [叶:10,20] [叶:30,40] [叶:50,60] [叶:70,80]
         ↔       ↔         ↔         ↔
              双向链表连接

B+树特点：

非叶子节点只存储键值，不存储数据（扇出大，树矮）
所有数据存储在叶子节点
叶子节点通过双向链表连接（范围查询高效）
树高度通常 3~4 层（千万级数据只需 3~4 次 IO）

4.2.2 B+树 vs B树

特性	B+树	B树
数据位置	仅叶子节点	所有节点
叶子链表	双向链表	无
非叶节点	仅键值	键值+数据
树高度	更矮	更高
范围查询	高效（链表遍历）	需要中序遍历
磁盘IO	更少	更多

4.2.3 为什么不用 Hash 索引

特性	B+树	Hash
等值查询	O(log n)	O(1)
范围查询	支持	不支持
排序	支持	不支持
最左前缀	支持	不支持
模糊查询	支持（最左前缀）	不支持

4.3 聚簇索引与二级索引

4.3.1 聚簇索引

聚簇索引将数据和索引存储在一起，B+树叶子节点就是完整的数据行：

聚簇索引（主键 id）
┌──────────────────────────────────────────┐
│ 非叶节点: [id=50]                        │
│            /        \                     │
│ 叶子节点:                              │
│ [id=10,name=A,age=20] ↔ [id=20,name=B]  │
│ [id=30,name=C] ↔ [id=40,name=D]         │
└──────────────────────────────────────────┘

聚簇索引规则：

有主键 → 主键作为聚簇索引
无主键 → 第一个唯一非空索引
都没有 → 自动生成 6 字节 DB_ROW_ID

4.3.2 二级索引

二级索引叶子节点存储主键值，而非完整数据行：

二级索引（name 列）
┌──────────────────────────────────┐
│ 非叶节点: [name='M']             │
│            /        \             │
│ 叶子节点:                        │
│ [name=A,id=10] ↔ [name=B,id=20] │
│ [name=C,id=30] ↔ [name=D,id=40] │
└──────────────────────────────────┘

4.3.3 回表查询

通过二级索引查找完整数据的过程：

SELECT * FROM user WHERE name = 'Tom';

1. 在 name 索引 B+树中找到 name='Tom' → id=5
2. 拿 id=5 到聚簇索引 B+树中找到完整行数据
3. 返回结果

这个过程叫"回表"（Bookmark Lookup）

4.3.4 覆盖索引

如果查询的字段都在索引中，就不需要回表：

-- 联合索引 idx_name_age(name, age)
-- 不需要回表（覆盖索引）
SELECT name, age FROM user WHERE name = 'Tom';

-- 需要回表（SELECT * 包含索引外的字段）
SELECT * FROM user WHERE name = 'Tom';

-- EXPLAIN 中 Extra = Using index 表示覆盖索引
EXPLAIN SELECT name, age FROM user WHERE name = 'Tom';

4.4 联合索引与最左前缀原则

4.4.1 联合索引结构

CREATE INDEX idx_name_age_city ON user(name, age, city);

联合索引 B+树排序规则：
先按 name 排序 → name 相同按 age 排序 → age 相同按 city 排序

叶子节点：
[name=A, age=20, city=BJ, id=1]
[name=A, age=25, city=SH, id=2]
[name=B, age=20, city=GZ, id=3]
[name=B, age=30, city=BJ, id=4]

4.4.2 最左前缀原则

-- idx_name_age_city(name, age, city)

-- ✅ 命中索引
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 20;
SELECT * FROM user WHERE name = 'Tom' AND age = 20 AND city = 'BJ';

-- ✅ 命中索引（优化器会调整顺序）
SELECT * FROM user WHERE age = 20 AND name = 'Tom';

-- ❌ 不命中索引（缺少最左列 name）
SELECT * FROM user WHERE age = 20;
SELECT * FROM user WHERE city = 'BJ';

-- ⚠️ 命中 name，但 age 和 city 无法用索引过滤
SELECT * FROM user WHERE name = 'Tom' AND city = 'BJ';

4.4.3 索引下推（Index Condition Pushdown, ICP）

MySQL 5.6+ 引入，在索引遍历过程中直接过滤，减少回表次数：

-- idx_name_age_city(name, age, city)
SELECT * FROM user WHERE name LIKE 'T%' AND city = 'BJ';

-- 无 ICP：
-- 1. 在 name 索引中找到所有 name LIKE 'T%' 的记录
-- 2. 逐条回表，再过滤 city = 'BJ'

-- 有 ICP：
-- 1. 在索引中找到 name LIKE 'T%' 的记录
-- 2. 直接在索引中过滤 city = 'BJ'（索引包含 city）
-- 3. 只对满足条件的记录回表

-- 查看 ICP 是否生效
EXPLAIN SELECT * FROM user WHERE name LIKE 'T%' AND city = 'BJ';
-- Extra: Using index condition

4.5 索引创建与优化

4.5.1 索引创建原则

-- 1. 在 WHERE/ORDER BY/GROUP BY 的列上建索引
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 2. 选择性高的列优先（区分度高）
-- 选择性 = COUNT(DISTINCT col) / COUNT(*)
SELECT COUNT(DISTINCT name) / COUNT(*) AS selectivity FROM user;

-- 3. 联合索引把选择性高的列放前面
CREATE INDEX idx_high_low ON user(high_cardinality_col, low_cardinality_col);

-- 4. 前缀索引（长字符串列）
CREATE INDEX idx_email_prefix ON user(email(20));

-- 5. 函数索引（8.0+）
CREATE INDEX idx_upper_name ON user((UPPER(name)));

4.5.2 索引失效场景

-- 1. 对索引列使用函数
SELECT * FROM user WHERE LEFT(name, 1) = 'T';  -- ❌
SELECT * FROM user WHERE name LIKE 'T%';         -- ✅

-- 2. 隐式类型转换
SELECT * FROM user WHERE phone = 13800138000;   -- ❌ (phone是varchar)
SELECT * FROM user WHERE phone = '13800138000';  -- ✅

-- 3. LIKE 以通配符开头
SELECT * FROM user WHERE name LIKE '%Tom';       -- ❌
SELECT * FROM user WHERE name LIKE 'Tom%';       -- ✅

-- 4. OR 条件包含非索引列
SELECT * FROM user WHERE name = 'Tom' OR age = 20;  -- ❌ (age无索引)

-- 5. 不满足最左前缀
-- idx(name, age)
SELECT * FROM user WHERE age = 20;              -- ❌

-- 6. NOT IN / NOT EXISTS / != / <>
SELECT * FROM user WHERE status != 1;           -- ❌ (全表扫描)

4.5.3 MySQL 8.0 索引新特性

-- 1. 隐藏索引（测试索引必要性）
ALTER TABLE user ALTER INDEX idx_name INVISIBLE;
ALTER TABLE user ALTER INDEX idx_name VISIBLE;

-- 2. 降序索引（真正支持 DESC）
CREATE INDEX idx_created_desc ON orders(created_at DESC);

-- 3. 函数索引
CREATE INDEX idx_upper_name ON user((UPPER(name)));

-- 4. 不可见索引对优化器不可见，但对 DML 仍需维护

面试实战题

题目 1：为什么 MySQL 使用 B+树而不是 B树作为索引？

答：

IO 次数更少：B+树非叶节点只存键值，单个节点能存更多键值，扇出更大，树更矮。3层 B+树可存千万级数据，只需 3 次 IO
范围查询高效：叶子节点通过双向链表连接，范围查询只需找到起点后顺序遍历
查询稳定：所有数据都在叶子节点，每次查询路径长度相同
更适合磁盘：节点大小等于页大小（16KB），一次 IO 读取一个完整节点

题目 2：什么是回表？如何避免回表？

答：

回表：通过二级索引找到主键值，再到聚簇索引查找完整行数据的过程。

避免回表（覆盖索引）：将查询需要的字段都包含在索引中。

-- 需要回表
SELECT * FROM user WHERE name = 'Tom';

-- 覆盖索引，不需要回表
SELECT name, age FROM user WHERE name = 'Tom';
-- Extra: Using index

-- 联合索引覆盖
CREATE INDEX idx_name_age ON user(name, age);
SELECT name, age FROM user WHERE name = 'Tom';  -- 覆盖索引

题目 3：什么是最左前缀原则？联合索引 (a,b,c) 能命中哪些查询？

答：

最左前缀原则：联合索引从最左列开始匹配，遇到范围查询（>/</LIKE/BETWEEN）会停止匹配后续列。

idx(a,b,c) 命中情况：

WHERE 条件	命中索引	说明
a = 1	a	命中最左列
a = 1 AND b = 2	a, b	命中前两列
a = 1 AND b = 2 AND c = 3	a, b, c	全部命中
b = 2	无	缺少最左列
a = 1 AND c = 3	a	c 无法使用索引
a > 1 AND b = 2	a	范围查询后停止
a = 1 AND b > 2 AND c = 3	a, b	b 范围查询后 c 停止
b = 2 AND a = 1	a, b	优化器调整顺序

题目 4：什么是索引下推（ICP）？解决了什么问题？

答：

ICP 是 MySQL 5.6 引入的优化，在索引遍历阶段直接进行条件过滤，减少回表次数。

无 ICP：存储引擎根据索引找到所有满足最左前缀的记录 → 逐条回表 → Server 层过滤其他条件

有 ICP：存储引擎在索引中直接过滤所有可用索引列的条件 → 只对满足条件的记录回表

示例：idx(name, age, city)，WHERE name LIKE 'T%' AND city = 'BJ'

无 ICP：找到所有 name LIKE ‘T%’ 的记录，全部回表，再过滤 city
有 ICP：在索引中同时过滤 name 和 city，只回表满足条件的记录

题目 5：索引失效的常见场景有哪些？

答：

函数操作：WHERE LEFT(name,1) = 'T' → 改为 WHERE name LIKE 'T%'
隐式类型转换：WHERE phone = 138 (phone 是 varchar) → 改为字符串
LIKE 通配符开头：WHERE name LIKE '%Tom' → 全文索引或 ES
OR 含非索引列：WHERE a=1 OR b=2 (b 无索引) → 给 b 加索引
不满足最左前缀：联合索引跳过左列
NOT IN/!=：优化器认为全表扫描更快
索引列参与计算：WHERE id + 1 = 10 → 改为 WHERE id = 9

题目 6：聚簇索引和非聚簇索引有什么区别？

答：

维度	聚簇索引	非聚簇索引（二级索引）
叶子节点	完整行数据	主键值
数量	每表仅一个	可多个
物理顺序	索引顺序即数据物理顺序	独立的 B+树
查询	直接获取数据	需要回表
插入顺序	建议按主键递增插入	无特殊要求
页分裂	随机插入可能触发	影响较小

核心区别：聚簇索引的叶子节点就是数据本身，二级索引的叶子节点是主键值，需要回表才能获取完整数据。

第5章 MySQL 数据类型与 SQL 高级特性

5.1 数据类型深入

5.1.1 整数类型

类型	字节	范围（有符号）	范围（无符号）
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT	4	-2^31~231-1	0~2^32-1
BIGINT	8	-2^63~263-1	0~2^64-1

-- INT(M) 的 M 不影响存储范围，只影响显示宽度（配合 ZEROFILL）
CREATE TABLE t (id INT(5) ZEROFILL);
INSERT INTO t VALUES (42);  -- 显示为 00042

-- 8.0.17+ INT(M) 的显示宽度已废弃，建议用标准格式
CREATE TABLE t (id INT);

5.1.2 字符串类型

类型	最大长度	存储	适用场景
CHAR(N)	255字符	定长	MD5、手机号
VARCHAR(N)	65535字节	变长	姓名、地址
TEXT	64KB	变长	文章内容
MEDIUMTEXT	16MB	变长	长文本
LONGTEXT	4GB	变长	超长文本

-- VARCHAR 长度选择
-- N 是字符数，不是字节数
-- utf8mb4 下，VARCHAR(255) 最多占 255*4 + 2 = 1022 字节

-- CHAR vs VARCHAR
-- CHAR：定长，不足补空格，最大255字符
-- VARCHAR：变长，按实际长度存储，最大65535字节

-- 索引前缀长度限制
-- InnoDB 索引键最大 3072 字节
-- utf8mb4 下 VARCHAR(768) 可完整索引
-- 超过需用前缀索引
CREATE INDEX idx_content ON articles(content(100));

5.1.3 时间类型

类型	字节	格式	范围
DATE	3	YYYY-MM-DD	1000~9999
TIME	3	HH:MM:SS	-838~838小时
DATETIME	8	YYYY-MM-DD HH:MM:SS	1000~9999
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970~2038
YEAR	1	YYYY	1901~2155

-- TIMESTAMP vs DATETIME
-- TIMESTAMP：4字节，自动时区转换，范围到2038年
-- DATETIME：8字节，无时区转换，范围到9999年

-- 8.0 TIMESTAMP 改进
-- 5.7：默认 NOT NULL，第一个 TIMESTAMP 列自动更新
-- 8.0：默认 NULL，不再自动更新，需显式指定

CREATE TABLE t (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 推荐使用 DATETIME(3) 存储毫秒精度
CREATE TABLE orders (
    created_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
);

5.1.4 JSON 类型

-- MySQL 8.0 JSON 增强
CREATE TABLE user_profile (
    id INT PRIMARY KEY,
    profile JSON,
    INDEX idx_profile_name ((CAST(profile->'$.name' AS CHAR(50))))
);

-- JSON 操作
INSERT INTO user_profile VALUES (1, '{"name": "Tom", "age": 25, "hobbies": ["reading", "coding"]}');

-- 提取值
SELECT profile->'$.name' FROM user_profile;           -- "Tom"（带引号）
SELECT profile->>'$.name' FROM user_profile;          -- Tom（不带引号）

-- JSON_TABLE（8.0）：将 JSON 数组转为关系表
SELECT * FROM JSON_TABLE(
    '[{"id":1,"name":"A"},{"id":2,"name":"B"}]',
    '$[*]' COLUMNS (id INT PATH '$.id', name VARCHAR(50) PATH '$.name')
) AS jt;

-- JSON 聚合
SELECT JSON_ARRAYAGG(name) FROM users;               -- ["A","B","C"]
SELECT JSON_OBJECTAGG(name, age) FROM users;          -- {"A":20,"B":25}

5.2 窗口函数

5.2.1 基本语法

函数名() OVER (
    [PARTITION BY 分区列]
    [ORDER BY 排序列 [ASC|DESC]]
    [frame_clause]
)

5.2.2 排名函数

-- ROW_NUMBER：连续排名，无并列
-- RANK：并列排名，跳号（1,2,2,4）
-- DENSE_RANK：并列排名，不跳号（1,2,2,3）

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
    RANK() OVER (ORDER BY score DESC) AS rk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS drk
FROM students;

-- 分区排名
SELECT
    dept,
    name,
    salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

5.2.3 聚合窗口函数

-- 累计求和
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 移动平均
SELECT
    order_date,
    amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

-- 分区聚合
SELECT
    dept,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

5.2.4 偏移函数

-- LEAD/LAG：前后行偏移
SELECT
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders;

-- FIRST_VALUE/LAST_VALUE
SELECT
    dept,
    name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_max
FROM employees;

5.3 公用表表达式（CTE）

5.3.1 非递归 CTE

-- 简化复杂查询，提高可读性
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.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;

5.3.2 递归 CTE

-- 生成数字序列
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- 组织架构树遍历
WITH RECURSIVE org_tree(id, name, manager_id, level) AS (
    -- 锚点：顶级管理者
    SELECT id, name, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 递归：下属
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level;

5.4 其他 8.0 SQL 新特性

5.4.1 CHECK 约束

-- 8.0 真正支持 CHECK 约束（5.7 语法支持但不生效）
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_name CHECK (CHAR_LENGTH(name) >= 2)
);

-- 查看约束
SELECT * FROM information_schema.CHECK_CONSTRAINTS;

5.4.2 隐藏列

-- 隐藏列对 SELECT * 不可见
CREATE TABLE t (
    id INT,
    secret_data VARCHAR(100) INVISIBLE
);

INSERT INTO t(id, secret_data) VALUES (1, 'hidden');
SELECT * FROM t;           -- 只返回 id
SELECT id, secret_data FROM t;  -- 返回两列

5.4.3 NOWAIT 和 SKIP LOCKED

-- NOWAIT：锁等待立即报错
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR 3572: Statement aborted because lock(s) could not be acquired immediately

-- SKIP LOCKED：跳过已锁定的行
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
-- 只返回未被锁定的行（适合任务队列）

5.4.4 VALUES 语句

-- 8.0.19+ VALUES 可作为表源
SELECT * FROM (
    VALUES ROW(1, 'a'), ROW(2, 'b'), ROW(3, 'c')
) AS t(id, name);

面试实战题

题目 1：VARCHAR 和 CHAR 有什么区别？如何选择？

答：

维度	CHAR	VARCHAR
存储	定长，不足补空格	变长，按实际长度
最大长度	255字符	65535字节
额外开销	无	1~2字节长度前缀
更新	不会产生碎片	可能产生碎片
适用	MD5、手机号等定长	姓名、地址等变长

选择建议：

长度固定或接近固定 → CHAR（如手机号、MD5）
长度变化大 → VARCHAR（如姓名、地址）
VARCHAR(N) 的 N 是字符数不是字节数，utf8mb4 下 VARCHAR(255) 最多占 1022 字节

题目 2：DATETIME 和 TIMESTAMP 有什么区别？

答：

维度	DATETIME	TIMESTAMP
字节	8	4
范围	1000~9999年	1970~2038年
时区	不转换	自动转换
默认	NULL	8.0默认NULL
NULL	允许	允许
精度	微秒	微秒

选择建议：

需要时区支持 → TIMESTAMP
超出2038年 → DATETIME
8.0 推荐用 DATETIME(3) 存储毫秒精度

题目 3：窗口函数和 GROUP BY 有什么区别？

答：

维度	GROUP BY	窗口函数
结果行数	聚合为一行	保留原始行数
用途	分组聚合	分组计算但保留明细
语法	GROUP BY	OVER(PARTITION BY)

示例：

-- GROUP BY：每个部门一行
SELECT dept, AVG(salary) FROM employees GROUP BY dept;

-- 窗口函数：每行都显示部门平均
SELECT name, dept, salary, AVG(salary) OVER(PARTITION BY dept) FROM employees;

题目 4：JSON 类型在 MySQL 中如何高效使用？

答：

索引优化：对 JSON 字段中的常用路径创建生成列 + 索引

ALTER TABLE t ADD COLUMN name_char VARCHAR(50)
    GENERATED ALWAYS AS (JSON_UNQUOTE(profile->'$.name'));
CREATE INDEX idx_name ON t(name_char);

8.0 函数索引：直接对 JSON 路径创建索引

CREATE INDEX idx_name ON t((CAST(profile->'$.name' AS CHAR(50))));

JSON_TABLE：将 JSON 数组展开为关系表
避免过度使用：频繁更新的字段不适合用 JSON，更新代价大
部分更新：8.0 支持 JSON 部分更新，只记录变更部分到 Binlog

题目 5：递归 CTE 有哪些典型应用场景？

答：

组织架构树：查询某人的所有下属（含多级）
菜单树：查询某菜单的所有子菜单
评论树：查询某评论的所有回复
图遍历：好友关系、路径查找
日期序列：生成日期维度表
数字序列：生成连续数字

-- 典型：查询所有下属
WITH RECURSIVE subordinates(id, name, level) AS (
    SELECT id, name, 1 FROM employees WHERE id = 1  -- 起始人
    UNION ALL
    SELECT e.id, e.name, s.level + 1
    FROM employees e JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

题目 6：NOWAIT 和 SKIP LOCKED 有什么实际用途？

答：

NOWAIT：如果获取不到锁立即报错，不等待。适合需要实时响应的场景。

SKIP LOCKED：跳过已锁定的行，返回未被锁定的行。适合任务队列场景。

-- 任务队列：多个消费者竞争处理任务
-- 消费者1
SELECT * FROM task_queue WHERE status = 'pending'
    FOR UPDATE SKIP LOCKED LIMIT 10;
-- 返回未被其他消费者锁定的任务

-- 库存扣减：如果锁不住立即返回
SELECT * FROM inventory WHERE product_id = 1
    FOR UPDATE NOWAIT;
-- 锁不住立即报错，避免长时间等待

注意：SKIP LOCKED 返回的结果不是一致性读，可能跳过某些行，不适合需要完整结果集的查询。

第6章 SQL 查询优化实战

6.1 EXPLAIN 执行计划

6.1.1 EXPLAIN 输出字段

EXPLAIN SELECT * FROM user WHERE age > 20;

字段	说明
id	查询序号，越大越先执行
select_type	查询类型（SIMPLE/PRIMARY/SUBQUERY等）
table	访问的表
partitions	匹配的分区
type	访问类型（重要）
possible_keys	可能使用的索引
key	实际使用的索引
key_len	索引使用长度
ref	索引查找的参考值
rows	预估扫描行数
filtered	过滤比例
Extra	额外信息（重要）

6.1.2 type 字段详解

性能从好到差排列：

type	说明	场景
system	表中只有一行	系统表
const	主键/唯一索引等值查询	WHERE id = 1
eq_ref	连接时主键/唯一索引	JOIN ON t1.id = t2.id
ref	非唯一索引等值查询	WHERE name = ‘Tom’
ref_or_null	ref + NULL 查询	WHERE name = ‘Tom’ OR name IS NULL
range	索引范围扫描	WHERE age > 20
index	全索引扫描	覆盖索引但无过滤
ALL	全表扫描	无索引或索引失效

-- type = const（最优）
EXPLAIN SELECT * FROM user WHERE id = 1;

-- type = ref
EXPLAIN SELECT * FROM user WHERE name = 'Tom';

-- type = range
EXPLAIN SELECT * FROM user WHERE age BETWEEN 20 AND 30;

-- type = ALL（最差，需优化）
EXPLAIN SELECT * FROM user WHERE age + 1 > 20;

6.1.3 Extra 字段详解

Extra	说明	优化建议
Using index	覆盖索引，无需回表	最优，无需优化
Using where	Server 层过滤	检查是否可下推到索引
Using index condition	索引下推（ICP）	较优
Using temporary	使用临时表	需优化，加索引
Using filesort	文件排序	需优化，加排序索引
Using join buffer	连接缓冲	检查连接条件索引
Select tables optimized away	优化器直接返回	最优（如 COUNT(*)）

6.2 慢查询定位与分析

6.2.1 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未用索引的查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 8.0 增强慢日志
SET GLOBAL log_slow_extra = ON;  -- 记录额外信息

6.2.2 mysqldumpslow 分析

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

# 参数说明
# -s t: 按查询时间排序
# -s c: 按查询次数排序
# -s l: 按锁定时间排序
# -s r: 按返回记录数排序
# -t N: 显示前N条

6.2.3 Performance Schema 监控

-- 8.0 查询最耗时的SQL
SELECT DIGEST_TEXT, COUNT_STAR,
       SUM_TIMER_WAIT/1000000000 AS total_ms,
       AVG_TIMER_WAIT/1000000 AS avg_us
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 查看正在执行的SQL
SELECT * FROM sys.session WHERE command = 'Query';

6.3 常见查询优化

6.3.1 分页优化

-- 传统分页（深分页性能差）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 扫描 1000010 行，丢弃前 1000000 行

-- 方案1：延迟关联（推荐）
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;
-- 子查询走覆盖索引，只扫描索引列

-- 方案2：游标分页（适合连续翻页）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 方案3：WHERE 条件过滤
SELECT * FROM orders WHERE id BETWEEN 1000001 AND 1000010;

6.3.2 COUNT 优化

-- COUNT(*) vs COUNT(1) vs COUNT(列)
-- COUNT(*) 和 COUNT(1) 等价，统计总行数
-- COUNT(列) 统计该列非 NULL 的行数

-- 优化1：使用覆盖索引
CREATE INDEX idx_status ON orders(status);
SELECT COUNT(*) FROM orders WHERE status = 1;

-- 优化2：近似计数（不需要精确值）
SHOW TABLE STATUS LIKE 'orders';  -- Rows 列为近似值

-- 优化3：汇总表（适合实时性要求不高的场景）
CREATE TABLE order_stats (
    stat_date DATE PRIMARY KEY,
    order_count INT
);

6.3.3 ORDER BY 优化

-- 原则：ORDER BY 的列在索引中，且顺序一致

-- 联合索引 idx(a, b)
SELECT * FROM t WHERE a = 1 ORDER BY b;     -- ✅ 索引排序
SELECT * FROM t ORDER BY a, b;              -- ✅ 索引排序
SELECT * FROM t ORDER BY a DESC, b DESC;    -- ✅ 8.0降序索引
SELECT * FROM t ORDER BY a ASC, b DESC;     -- ❌ 排序方向不一致
SELECT * FROM t ORDER BY b, a;              -- ❌ 顺序不一致

-- 查看是否使用文件排序
EXPLAIN SELECT * FROM t ORDER BY b;
-- Extra: Using filesort → 需要优化

6.3.4 GROUP BY 优化

-- 8.0 不再隐式排序，需显式 ORDER BY
-- 5.7: GROUP BY 默认按分组列排序
-- 8.0: GROUP BY 不保证排序

-- 优化：使用索引避免临时表
CREATE INDEX idx_dept_salary ON employees(dept, salary);
SELECT dept, AVG(salary) FROM employees GROUP BY dept;
-- Extra: Using index → 覆盖索引，无需临时表

-- 松散索引扫描（Loose Index Scan）
SELECT COUNT(DISTINCT dept) FROM employees;
-- 如果索引 idx(dept) 可用松散扫描

6.3.5 JOIN 优化

-- 原则：被驱动表的连接列有索引

-- Nested Loop Join
-- 驱动表每行 → 查找被驱动表 → 索引查找
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- u.id 是主键，查找高效

-- Block Nested Loop Join（无索引时）
-- 使用 join_buffer 缓存驱动表数据，减少被驱动表扫描次数
SET join_buffer_size = 8 * 1024 * 1024;  -- 8MB

-- 8.0 Hash Join（替代 BNL）
-- 对无索引的等值连接使用 Hash Join
EXPLAIN FORMAT=TREE
SELECT * FROM t1 JOIN t2 ON t1.col = t2.col;
-- → Hash join

6.4 优化器 Hint

6.4.1 索引 Hint

-- 强制使用索引
SELECT * FROM user FORCE INDEX(idx_name) WHERE name = 'Tom';

-- 建议使用索引
SELECT * FROM user USE INDEX(idx_name) WHERE name = 'Tom';

-- 忽略索引
SELECT * FROM user IGNORE INDEX(idx_name) WHERE name = 'Tom';

6.4.2 优化器 Switch

-- 关闭 ICP
SET optimizer_switch = 'index_condition_pushdown=off';

-- 关闭 MRR
SET optimizer_switch = 'mrr=off';

-- 查看 optimizer_switch
SELECT @@optimizer_switch;

面试实战题

题目 1：EXPLAIN 中 type 字段的各个值代表什么？哪些需要优化？

答：

type 从优到差：system > const > eq_ref > ref > range > index > ALL

const：主键/唯一索引等值查询，一次 IO，最优
eq_ref：JOIN 时主键/唯一索引查找，每次一行
ref：非唯一索引等值查询，可能多行
range：索引范围扫描，BETWEEN/IN/>/<
index：全索引扫描，比 ALL 好（索引比数据小）
ALL：全表扫描，必须优化

优化目标：至少达到 range 级别，最好 ref 及以上。ALL 必须加索引优化。

题目 2：深分页如何优化？

答：

问题：LIMIT 1000000, 10 扫描 1000010 行，前 1000000 行被丢弃。

优化方案：

延迟关联：子查询走覆盖索引只取 id，再回表

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t ON o.id = t.id;

游标分页：记录上一页最后一条的 id

SELECT * FROM orders WHERE id > 上一页最后id ORDER BY id LIMIT 10;

WHERE 过滤：业务上限制查询范围

SELECT * FROM orders WHERE id BETWEEN 1000001 AND 1000010;

业务限制：不允许跳页，只提供"上一页/下一页"

题目 3：Using filesort 和 Using temporary 分别代表什么？如何优化？

答：

Using filesort：MySQL 需要额外排序操作，无法通过索引直接获取有序结果。

优化：在 ORDER BY 的列上创建合适的索引，确保排序方向一致。

Using temporary：MySQL 使用临时表存储中间结果，常见于 GROUP BY + ORDER BY 不同列、DISTINCT、UNION。

优化：

GROUP BY 和 ORDER BY 使用相同列
在分组/排序列上创建联合索引
避免不必要的 DISTINCT

题目 4：MySQL 8.0 的 Hash Join 有什么优势？

答：

8.0 引入 Hash Join 替代 Block Nested Loop Join：

BNL：驱动表每批数据加载到 join_buffer → 被驱动表全表扫描匹配 → O(M*N)

Hash Join：

构建阶段：扫描小表，在内存中构建 Hash 表
探测阶段：扫描大表，在 Hash 表中查找匹配 → O(M+N)

优势：

无索引等值连接性能大幅提升
时间复杂度从 O(M*N) 降为 O(M+N)
适合数据仓库/分析查询

限制：仅支持等值连接（=/<=>），非等值连接仍用 NLJ。

题目 5：如何定位和分析慢查询？

答：

开启慢查询日志：slow_query_log=ON, long_query_time=1
分析工具：mysqldumpslow 按时间/次数排序
Performance Schema：events_statements_summary_by_digest 查看最耗时 SQL
sys 库：sys.statements_with_runtimes_in_95th_percentile
EXPLAIN 分析：查看执行计划，关注 type/key/Extra
Optimizer Trace：查看优化器决策过程

SET optimizer_trace='enabled=on';
SELECT * FROM user WHERE age > 20;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

题目 6：覆盖索引是什么？为什么能提升性能？

答：

覆盖索引：查询所需的所有字段都包含在索引中，无需回表。

原理：二级索引叶子节点存储索引列值 + 主键值。如果查询只需要索引列和主键，直接从索引获取，不需要到聚簇索引查找完整行。

优势：

减少IO：索引比数据小，更多记录可缓存在Buffer Pool
避免回表：省去聚簇索引查找的开销
随机IO变顺序IO：索引按顺序存储

使用方法：

避免 SELECT *，只查需要的列
将查询列加入联合索引
EXPLAIN 中 Extra = Using index 表示覆盖索引

第7章索引优化进阶

7.1 索引设计原则

7.1.1 建索引的场景

-- 1. WHERE 条件列
SELECT * FROM orders WHERE user_id = 100;
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. ORDER BY / GROUP BY 列
SELECT * FROM orders ORDER BY created_at DESC;
CREATE INDEX idx_created_at ON orders(created_at DESC);

-- 3. JOIN 连接列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- users.id 已是主键，orders.user_id 需要索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 4. DISTINCT 列
SELECT DISTINCT status FROM orders;
CREATE INDEX idx_status ON orders(status);

-- 5. 覆盖索引（避免回表）
SELECT user_id, status FROM orders WHERE user_id = 100;
CREATE INDEX idx_user_status ON orders(user_id, status);

7.1.2 不建索引的场景

区分度低的列：如性别（只有2~3个值），索引过滤效果差
频繁更新的列：每次更新都需维护索引
小表：全表扫描比索引查找更快
查询很少的列：索引占空间，维护有开销

7.1.3 联合索引设计

-- 原则：选择性高的列在前，范围查询列在后

-- 反例：status 区分度低，放前面浪费
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 正例：created_at 区分度高，放前面
CREATE INDEX idx_created_status ON orders(created_at, status);

-- 计算选择性
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT created_at) / COUNT(*) AS created_selectivity
FROM orders;
-- status_selectivity: 0.0001 (低)
-- created_selectivity: 0.85 (高)

7.2 索引优化策略

7.2.1 前缀索引

-- 长字符串列，只索引前N个字符
CREATE INDEX idx_email_prefix ON user(email(20));

-- 确定前缀长度：选择性接近完整列
SELECT
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS p10,
    COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS p15,
    COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) AS p20,
    COUNT(DISTINCT email) / COUNT(*) AS full
FROM user;
-- 选择接近 full 的最小前缀长度

前缀索引的限制：

无法用于覆盖索引（Extra 不会出现 Using index）
无法用于 ORDER BY / GROUP BY
无法用于索引下推

7.2.2 函数索引（8.0+）

-- 对列的函数结果建索引
CREATE INDEX idx_upper_name ON user((UPPER(name)));

-- 查询时自动使用
SELECT * FROM user WHERE UPPER(name) = 'TOM';
-- 命中 idx_upper_name

-- JSON 路径索引
CREATE INDEX idx_profile_age ON user((CAST(profile->'$.age' AS UNSIGNED)));

7.2.3 降序索引（8.0+）

-- 8.0 真正支持降序索引
CREATE INDEX idx_created_desc ON orders(created_at DESC, id ASC);

-- 5.7 语法支持 DESC 但实际创建为 ASC
-- 8.0 真正按 DESC 存储，避免 filesort

-- 验证
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC, id ASC LIMIT 10;
-- 8.0: Extra 无 Using filesort
-- 5.7: Extra 有 Using filesort

7.2.4 隐藏索引

-- 测试索引是否必要：先隐藏，观察性能
ALTER TABLE orders ALTER INDEX idx_created_at INVISIBLE;

-- 确认无影响后删除
ALTER TABLE orders DROP INDEX idx_created_at;

-- 恢复可见
ALTER TABLE orders ALTER INDEX idx_created_at VISIBLE;

7.3 索引统计信息

7.3.1 统计信息收集

-- 查看索引统计信息
SHOW INDEX FROM orders;

-- 关键字段
-- Cardinality: 索引中唯一值的估计数量
-- Cardinality / 表行数 ≈ 选择性

-- 手动更新统计信息
ANALYZE TABLE orders;

-- 自动更新配置
SET GLOBAL innodb_stats_auto_recalc = ON;  -- 默认ON
SET GLOBAL innodb_stats_persistent = ON;   -- 持久化统计信息
SET GLOBAL innodb_stats_persistent_sample_pages = 20;  -- 采样页数

7.3.2 统计信息不准确的影响

-- Cardinality 不准确 → 优化器选错索引
-- 现象：某天突然查询变慢，EXPLAIN 发现换了索引

-- 解决
ANALYZE TABLE orders;  -- 重新收集统计信息

-- 8.0 直方图（Histogram）
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, user_id WITH 100 BUCKETS;

-- 查看直方图
SELECT * FROM information_schema.COLUMN_STATISTICS;

7.4 Online DDL

7.4.1 DDL 算法

-- ALGORITHM 选项
-- COPY: 创建临时表 → 复制数据 → 替换原表（锁表）
-- INPLACE: 在原表上修改（部分操作不锁表）
-- INSTANT: 只修改元数据（8.0.12+，最快）

-- 添加索引（INPLACE，不锁表）
ALTER TABLE orders ADD INDEX idx_status(status), ALGORITHM=INPLACE;

-- 添加列（INSTANT，8.0.12+）
ALTER TABLE orders ADD COLUMN remark VARCHAR(200), ALGORITHM=INSTANT;

-- 查看DDL是否支持INSTANT
SELECT * FROM information_schema.INNODB_TABLES;

7.4.2 Online DDL 最佳实践

-- 1. 选择低峰期执行
-- 2. 使用 ALGORITHM=INPLACE, LOCK=NONE
ALTER TABLE orders ADD INDEX idx_created(created_at),
    ALGORITHM=INPLACE, LOCK=NONE;

-- 3. 大表加索引使用 pt-online-schema-change
-- pt-online-schema-change --alter "ADD INDEX idx_status(status)" D=db,t=orders

-- 4. 监控 DDL 进度
SHOW ALTER TABLE STATUS;  -- 8.0+

7.5 索引监控与维护

7.5.1 未使用索引

-- 查找未使用的索引
SELECT s.table_schema, s.table_name, s.index_name,
       s.non_unique, s.seq_in_index, s.column_name
FROM information_schema.statistics s
LEFT JOIN sys.schema_unused_indexes u
    ON s.table_schema = u.object_schema
    AND s.table_name = u.object_name
    AND s.index_name = u.index_name
WHERE u.index_name IS NOT NULL
    AND s.table_schema NOT IN ('mysql', 'sys', 'performance_schema');

7.5.2 冗余索引

-- 查找冗余索引
-- 如果有 idx(a,b)，则 idx(a) 是冗余的
SELECT * FROM sys.schema_redundant_indexes;

-- 冗余索引的危害
-- 1. 占用磁盘空间
-- 2. DML 需要维护多个索引
-- 3. 优化器可能选错索引

7.5.3 索引碎片整理

-- 查看表碎片
SELECT table_name, data_free / 1024 / 1024 AS free_mb
FROM information_schema.tables
WHERE table_schema = 'test' AND data_free > 0;

-- 整理碎片
ALTER TABLE orders ENGINE=InnoDB;  -- 重建表
OPTIMIZE TABLE orders;              -- 同上

-- 在线整理（8.0）
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE;

面试实战题

题目 1：联合索引的设计原则是什么？

答：

选择性高的列在前：区分度高的列放最左边，过滤效果最好
范围查询列在后：范围查询后的列无法使用索引
覆盖索引优先：将查询需要的列都加入索引
排序/分组列考虑：ORDER BY/GROUP BY 的列纳入索引
避免冗余：idx(a,b) 已包含 idx(a) 的功能

示例：查询 WHERE a=1 AND b>2 ORDER BY c

索引设计：idx(a, b, c) — a 等值过滤，b 范围过滤，c 排序

题目 2：什么是索引下推？什么场景下有效？

答：

ICP（Index Condition Pushdown）将部分 WHERE 条件下推到存储引擎层，在索引遍历时直接过滤。

有效场景：

联合索引 idx(name, age)
WHERE name LIKE 'T%' AND age > 20
name 走索引范围扫描，age 在索引中直接过滤
减少回表次数

无效场景：

WHERE 条件的列不在索引中
聚簇索引（不需要回表）
子查询条件

题目 3：如何判断一个索引是否需要删除？

答：

sys.schema_unused_indexes：查看从未使用的索引
隐藏索引测试：ALTER INDEX idx INVISIBLE，观察业务是否受影响
Performance Schema：统计索引使用频率
判断标准：
- 从未使用 → 删除
- 偶尔使用但性能影响小 → 考虑删除
- 冗余索引（被更宽的联合索引覆盖）→ 删除
注意：唯一索引用于约束而非查询，不能仅凭使用频率删除

题目 4：Online DDL 的三种算法有什么区别？

答：

算法	原理	锁表	耗时	适用
INSTANT	只改元数据	不锁	极快	8.0.12+，加列等
INPLACE	原表修改	部分锁	中等	加索引、改列类型
COPY	创建新表复制	锁表	慢	改字符集等

INSTANT：只修改数据字典，不修改数据文件，瞬间完成
INPLACE：在原表上直接修改，允许并发 DML（取决于操作类型）
COPY：创建临时表 → 复制数据 → 替换原表，期间锁表

题目 5：统计信息不准确会导致什么问题？如何解决？

答：

问题：优化器基于统计信息选择执行计划，统计信息不准确会导致选错索引。

表现：

查询突然变慢
EXPLAIN 发现换了索引
Cardinality 与实际差异大

解决：

ANALYZE TABLE 手动更新统计信息
开启 innodb_stats_persistent 持久化统计信息
增大 innodb_stats_persistent_sample_pages 采样页数
8.0 使用直方图：ANALYZE TABLE t UPDATE HISTOGRAM ON col
定期在低峰期执行 ANALYZE TABLE

题目 6：前缀索引有什么限制？如何确定前缀长度？

答：

限制：

无法用于覆盖索引（EXPLAIN 不会出现 Using index）
无法用于 ORDER BY / GROUP BY
无法用于索引下推（ICP）
区分度可能不如完整索引

确定前缀长度：

SELECT
    COUNT(DISTINCT LEFT(col, 5)) / COUNT(*) AS p5,
    COUNT(DISTINCT LEFT(col, 10)) / COUNT(*) AS p10,
    COUNT(DISTINCT LEFT(col, 15)) / COUNT(*) AS p15,
    COUNT(DISTINCT col) / COUNT(*) AS full
FROM t;

选择区分度接近 full 的最小前缀长度。一般 email 取 20~30，URL 取 30~50。

第8章事务与 ACID 特性

8.1 事务基础

8.1.1 事务的定义

事务是一组操作的逻辑单元，具有 ACID 四大特性：

-- 典型事务：银行转账
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- A扣款
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- B收款
COMMIT;

-- 如果中间出错
ROLLBACK;  -- 所有修改回滚

8.1.2 ACID 特性详解

特性	含义	InnoDB 实现
原子性 (Atomicity)	事务中的操作要么全部成功，要么全部回滚	Undo Log
一致性 (Consistency)	事务前后数据库从一个一致状态到另一个一致状态	A+I+D 共同保证
隔离性 (Isolation)	并发事务之间互不干扰	MVCC + 锁
持久性 (Durability)	事务提交后修改永久保存	Redo Log

8.1.3 事务控制语句

-- 开始事务
START TRANSACTION;  -- 或 BEGIN

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- 隐式提交（以下语句会自动提交当前事务）
-- DDL: CREATE/ALTER/DROP/TRUNCATE
-- DCL: GRANT/REVOKE
-- LOCK TABLES
-- SET AUTOCOMMIT = 1

8.2 事务隔离级别

8.2.1 四种隔离级别

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	可能	可能	可能	能读到未提交数据
READ COMMITTED (RC)	不会	可能	可能	Oracle 默认
REPEATABLE READ (RR)	不会	不会	部分解决	MySQL 默认
SERIALIZABLE	不会	不会	不会	完全串行化

-- 查看当前隔离级别
SELECT @@transaction_isolation;  -- 8.0
SELECT @@tx_isolation;           -- 5.7

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

8.2.2 并发问题详解

脏读：读到其他事务未提交的数据

-- 事务A                    -- 事务B
SET TX RC;
START TRANSACTION;
                           START TRANSACTION;
                           UPDATE account SET balance = 200 WHERE id = 1;
SELECT balance FROM account WHERE id = 1;
-- 读到 200（未提交）
                           ROLLBACK;
-- 实际 balance 仍为 100，但 A 读到了 200 → 脏读

不可重复读：同一事务内两次读同一行结果不同

-- 事务A                    -- 事务B
SET TX RC;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;
-- 读到 100
                           UPDATE account SET balance = 200 WHERE id = 1;
                           COMMIT;
SELECT balance FROM account WHERE id = 1;
-- 读到 200 → 不可重复读

幻读：同一事务内两次范围查询结果集不同

-- 事务A                    -- 事务B
SET TX RR;
START TRANSACTION;
SELECT * FROM account WHERE balance < 200;
-- 3行
                           INSERT INTO account VALUES(4, 50);
                           COMMIT;
SELECT * FROM account WHERE balance < 200;
-- 4行 → 幻读

8.3 MVCC 多版本并发控制

8.3.1 MVCC 核心组件

隐藏列：DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）
Undo Log 版本链：通过 DB_ROLL_PTR 串联历史版本
Read View：一致性读视图

8.3.2 版本链

当前行: {id=1, name='Tom', DB_TRX_ID=5, DB_ROLL_PTR → undo3}
    ↓
Undo3: {id=1, name='Jerry', DB_TRX_ID=4, DB_ROLL_PTR → undo2}
    ↓
Undo2: {id=1, name='Bob', DB_TRX_ID=3, DB_ROLL_PTR → undo1}
    ↓
Undo1: {id=1, name='Alice', DB_TRX_ID=2, DB_ROLL_PTR = NULL}

8.3.3 Read View

Read View 记录生成时刻的活跃事务状态：

字段	说明
creator_trx_id	创建该 Read View 的事务 ID
m_ids	生成时活跃的读写事务 ID 列表
min_trx_id	m_ids 中的最小值（低水位）
max_trx_id	下一个待分配的事务 ID（高水位）

8.3.4 可见性判断规则

遍历版本链，对每个版本的 trx_id 判断：

1. trx_id == creator_trx_id → 可见（自己修改的）
2. trx_id < min_trx_id → 可见（ReadView 创建前已提交）
3. trx_id >= max_trx_id → 不可见（ReadView 创建后启动的事务）
4. min_trx_id <= trx_id < max_trx_id：
   - trx_id 在 m_ids 中 → 不可见（创建时仍活跃）
   - trx_id 不在 m_ids 中 → 可见（创建时已提交）

如果当前版本不可见 → 沿 DB_ROLL_PTR 找上一个版本

8.3.5 RC vs RR 的本质差异

RC（读已提交）：每次 SELECT 生成新的 Read View

事务A (RC):
  T1: SELECT → ReadView1 → 读到版本X
  -- 事务B 修改并提交
  T2: SELECT → ReadView2 → 读到版本Y（新ReadView看到已提交的修改）

RR（可重复读）：事务内复用同一个 Read View

事务A (RR):
  T1: SELECT → ReadView1 → 读到版本X
  -- 事务B 修改并提交
  T2: SELECT → ReadView1 → 仍读到版本X（同一ReadView）

8.4 当前读与快照读

8.4.1 快照读

普通 SELECT 语句，读取 MVCC 历史版本，不加锁：

-- 快照读（不加锁）
SELECT * FROM user WHERE id = 1;

8.4.2 当前读

读取最新已提交数据，并加锁：

-- 当前读（加锁）
SELECT * FROM user WHERE id = 1 FOR UPDATE;        -- 排他锁(X)
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁(S) 5.7
SELECT * FROM user WHERE id = 1 FOR SHARE;          -- 共享锁(S) 8.0

-- DML 也是当前读
UPDATE user SET name = 'Tom' WHERE id = 1;  -- 加X锁
DELETE FROM user WHERE id = 1;               -- 加X锁

8.4.3 RR 下的幻读问题

-- RR 隔离级别下，快照读不会幻读（MVCC保证）
-- 但当前读可能幻读：

-- 事务A                          -- 事务B
START TRANSACTION;
SELECT * FROM user WHERE age > 20;
-- 3行
                                 INSERT INTO user VALUES(4, 'Tom', 25);
                                 COMMIT;
UPDATE user SET name = 'new' WHERE age > 20;
-- 影响4行！（包括事务B新插入的行）
SELECT * FROM user WHERE age > 20;
-- 4行 → 幻读！
COMMIT;

InnoDB 通过临键锁（Next-Key Lock）解决当前读的幻读问题：

-- 事务A                          -- 事务B
START TRANSACTION;
SELECT * FROM user WHERE age > 20 FOR UPDATE;
-- 加 Next-Key Lock，锁定 (20, +∞)
                                 INSERT INTO user VALUES(4, 'Tom', 25);
                                 -- 阻塞！等待锁
COMMIT;
                                 -- 此时才能插入

面试实战题

题目 1：MySQL 的四种隔离级别分别解决了什么问题？

答：

隔离级别	脏读	不可重复读	幻读	实现方式
READ UNCOMMITTED	❌	❌	❌	无隔离
READ COMMITTED	✅	❌	❌	MVCC（每次SELECT新ReadView）
REPEATABLE READ	✅	✅	⚠️	MVCC（复用ReadView）+ Next-Key Lock
SERIALIZABLE	✅	✅	✅	所有读加共享锁

RC 解决脏读：每次 SELECT 生成新 ReadView，只能看到已提交的数据
RR 解决不可重复读：事务内复用同一 ReadView，保证多次读结果一致
RR 部分解决幻读：快照读通过 MVCC 解决，当前读通过 Next-Key Lock 解决
SERIALIZABLE 完全串行化，性能最差

题目 2：MVCC 的实现原理是什么？

答：

MVCC 通过三个组件实现读写不冲突：

隐藏列：每行记录包含 DB_TRX_ID（最后修改事务ID）和 DB_ROLL_PTR（回滚指针）
Undo Log 版本链：每次修改产生 Undo Log，通过 DB_ROLL_PTR 将历史版本串联
Read View：记录生成时刻的活跃事务状态（m_ids、min_trx_id、max_trx_id）

查询流程：

生成 Read View（RC 每次 SELECT，RR 事务首次 SELECT）
从最新版本开始遍历版本链
根据 Read View 可见性规则判断当前版本是否可见
不可见则沿 DB_ROLL_PTR 找上一个版本
找到可见版本返回结果

题目 3：RC 和 RR 的本质区别是什么？

答：

核心区别：Read View 的生成时机不同。

RC：每次 SELECT 都生成新的 Read View → 能看到其他事务已提交的最新修改 → 不可重复读
RR：事务内第一次 SELECT 生成 Read View，后续复用 → 只能看到事务开始时已提交的数据 → 可重复读

其他差异：

RR 下存在 Gap Lock / Next-Key Lock，RC 下只有行锁
RC 的 Gap Lock 不生效，不会阻塞 INSERT
RC 适合需要看到最新数据的场景（如电商详情页）
RR 适合需要数据一致性的场景（如金融对账）

题目 4：什么是快照读和当前读？有什么区别？

答：

维度	快照读	当前读
语句	普通 SELECT	SELECT FOR UPDATE/SHARE, DML
读取	MVCC 历史版本	最新已提交数据
加锁	不加锁	加行锁/间隙锁
幻读	RR下不会	需 Next-Key Lock 防止

关键点：

快照读通过 MVCC 实现，读写不冲突
当前读读取最新数据并加锁，保证一致性
同一事务中，先快照读后当前读，可能导致看到不一致的数据

题目 5：RR 隔离级别下是否完全解决了幻读？

答：

不完全解决。RR 下幻读有两种情况：

快照读：通过 MVCC 解决，不会幻读
当前读：通过 Next-Key Lock 解决，不会幻读

但存在例外：

-- 事务A                          -- 事务B
START TRANSACTION;
SELECT * FROM user WHERE id = 5;
-- 不存在
                                 INSERT INTO user VALUES(5, 'Tom', 25);
                                 COMMIT;
UPDATE user SET name = 'new' WHERE id = 5;
-- 影响1行（当前读能看到新插入的行）
SELECT * FROM user WHERE id = 5;
-- 存在！→ 幻读

原因：先快照读（id=5 不存在，不加锁），再 UPDATE（当前读），导致幻读。

解决：使用 SELECT FOR UPDATE 代替普通 SELECT。

题目 6：长事务有什么危害？如何避免？

答：

危害：

Undo Log 膨胀：长事务持有旧 Read View，大量 Undo Log 无法 Purge
锁持有时间长：增加死锁概率，阻塞其他事务
Buffer Pool 污染：长事务可能加载大量冷数据
主从延迟：长事务的 Binlog 在提交时一次性发送

避免方法：

设置 innodb_kill_idle_transaction 自动杀空闲事务
设置 wait_timeout 自动断开空闲连接
业务层拆分大事务为小事务
监控长事务：information_schema.INNODB_TRX
避免在事务中做 RPC 调用等耗时操作

AI编程社区

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

码道 · 寻艺——从零构建“非遗交互式对话平台“的完整实践

AI编程社区

码道：用 Python + FastAPI 快速构建一个学生信息管理接口：从零到 Swagger 在线文档

在日常开发和教学中，我们经常需要一套简单、可控、能快速上手的后端接口来演示"前后端分离"、练习 Restful API 设计，或者作为前端联调的 Mock 服务。传统做法往往是搭一套完整的 Web 工程：配置数据库、编写 DAO、引入 ORM、做请求分发……一套流程下来，光环境搭建就要花去不少时间，对于想快速验证想法、或者刚入门 API 开发的同学来说，显得过于沉重。

AI编程社区

码道：从零构建学生信息管理系统 API：FastAPI 内存版 CRUD 项目全解析

FastAPI 的强大，很大程度上来自 Pydantic 的校验能力。我们写下类型声明，FastAPI 在运行时自动把它变成边界检查器。name：字符串，长度限制 1 到 50；age：整数，限制在 6 到 120 之间（ge大于等于、le小于等于）；gender：字符串，用正则限定只能是"男"“女”“其他”；email：邮箱格式字符串；grade与major：可选字段，代表班级和专业方向。