MySQL 深度解析

#组件 · #MySQL · #数据库 · #InnoDB · #索引 · #事务 · #MVCC

从架构到索引、从事务到锁、从 SQL 优化到高可用，全面理解 MySQL 的设计与工程实践。

一、架构与进程/线程模型

1.1 整体架构

┌─────────────────────────────────────────────┐
│             连接层 (Connection Pool)          │
│   TCP/IP、Socket、Named Pipe                │
│   每连接一线程 (one-thread-per-connection)    │
├─────────────────────────────────────────────┤
│           SQL 层 (Server Layer)              │
│  ┌──────┐ ┌──────┐ ┌────────┐ ┌─────────┐ │
│  │解析器 │ │优化器 │ │执行器  │ │查询缓存  │ │
│  │Parser │ │Optim. │ │Executor│ │(8.0移除) │ │
│  └──────┘ └──────┘ └────────┘ └─────────┘ │
├─────────────────────────────────────────────┤
│          存储引擎层 (Pluggable Engines)       │
│  ┌────────┐ ┌────────┐ ┌────────┐          │
│  │ InnoDB  │ │ MyISAM │ │ Memory │  ...    │
│  └────────┘ └────────┘ └────────┘          │
├─────────────────────────────────────────────┤
│           文件系统 / 磁盘                      │
└─────────────────────────────────────────────┘

1.2 线程模型

┌────────────────────────────────────────────────┐
│                MySQL 进程                       │
│                                                 │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐     │
│  │ Thread 1 │  │ Thread 2 │  │ Thread N │ ... │
│  │ (连接1)  │  │ (连接2)  │  │ (连接N)  │     │
│  └──────────┘  └──────────┘  └──────────┘     │
│         │             │             │          │
│         ▼             ▼             ▼          │
│  ┌──────────────────────────────────────┐     │
│  │         InnoDB 后台线程               │     │
│  │  Master / IO / Purge / Page Cleaner  │     │
│  │  (缓冲池刷脏、undo 回收、写盘)        │     │
│  └──────────────────────────────────────┘     │
└────────────────────────────────────────────────┘

核心线程：

线程	作用
连接线程	每客户端连接一个，处理 SQL 执行
Master Thread	每秒/每 10 秒循环：刷脏页、合并插入缓冲、回滚 undo、刷新日志
IO Thread	异步读写磁盘（`innodb_read_io_threads` / `innodb_write_io_threads`）
Purge Thread	清理已标记为删除的 undo log
Page Cleaner Thread	将 Buffer Pool 中脏页刷回磁盘

1.3 版本演进：5.6 → 5.7 → 8.0

MySQL 的版本进化不是小修小补，每次大版本都有足以改变架构设计的重大改进。理解"为什么改"比记住"改了什么"更重要。

5.6 → 5.7：性能与复制革命

改进	5.6	5.7	为什么重要
InnoDB 全文索引	实验性，不可用于生产	正式可用，支持 CJK（中日韩）	之前只能用 MyISAM 做全文索引
独立 Undo 表空间	Undo 在 ibdata1 中，膨胀后无法收缩	支持独立 Undo 表空间（`innodb_undo_tablespaces`）	ibdata1 膨胀是 5.6 最常见的运维噩梦
Online DDL 增强	仅部分 DDL 在线	更多 DDL 操作支持在线（`ALGORITHM=INPLACE`）	减少锁表时间，大表变更从"深夜操作"变为"随时可做"
多源复制	不支持	一个 Slave 可复制多个 Master	数据聚合场景（分库→汇总）的核心能力
GTID 增强	实验性，需重启开启	持久化到表 `mysql.gtid_executed`，在线开启	GTID 从"可用"变成"好用"
JSON 数据类型	无	原生 JSON 类型 + 虚拟列索引	文档存储需求不再只能 MongoDB
sys schema	无	内置性能诊断视图	用 `sys` 替代手写 `information_schema` 查询
Buffer Pool 在线调整	需重启	`SET GLOBAL innodb_buffer_pool_size`	动态伸缩，无需停服
半同步增强	仅 `AFTER_COMMIT`	新增 `AFTER_SYNC`（无损半同步）	提交后才 sync 可能丢数据；sync 后再提交杜绝丢失

5.7 最大的工程意义：MySQL 从"能用"变成"好运维"。GTID 持久化、sys schema、Buffer Pool 在线调整——三个让 DBA 生活更美好的特性。

5.7 → 8.0：现代化与性能飞跃

改进	5.7	8.0	为什么重要
数据字典（DD）	`frm` 文件存储表定义，每次访问需解析文件	事务型数据字典（InnoDB 表存储元数据）	消除了 frm 文件损坏导致数据不可访问的严重 bug
原子 DDL	DDL 中途崩溃可能留下一半定义	DDL 全程原子（要么全部成功，要么全部回滚）	不会再出现"表定义更新了但索引没建完"
SQL 窗口函数	不支持 `ROW_NUMBER()` `RANK()`	完整支持	分页、排名、去重不再需要复杂自连接或变量
CTE（公用表表达式）	不支持	递归 CTE + 普通 CTE	树形查询原来需要反复自连接，现在一条 SQL
角色（Roles）	无	支持 `CREATE ROLE`、`GRANT role TO user`	权限管理从"每人挨个配"变成"按角色批量配"
直方图（Histogram）	无	`ANALYZE TABLE ... UPDATE HISTOGRAM`	优化器能基于数据分布选择索引，解决"索引选错"的经典问题
InnoDB 自增列持久化	重启后自增值重置为 `MAX(id)+1`	持久化到 redo log	删掉最大 ID 的记录后重启不会出现 ID 重复
UTF8MB4 默认字符集	`utf8mb3` 是默认（只支持 BMP）	`utf8mb4` 默认	Emoji、生僻字再也不乱码
Instant DDL	ALTER 需 COPY 整表	8.0.12 起 INSTANT ADD COLUMN（末尾） 8.0.29 起任意位置 INSTANT ADD	大表加列从"等半小时"变成"毫秒完成"
Redo Log 重构	固定大小 log files（`innodb_log_file_size`）	8.0.30 起改为动态容量（`innodb_redo_log_capacity`）	不再需要为 redo log 大小猜一个数然后重启修改
Skip Scan 优化	无	`skip_scan` 访问方法	多列索引中跳过前缀列直接扫描
Descending Index	仅标记支持，实际不生效	真正支持降序索引	`ORDER BY a ASC, b DESC` 能用联合索引
资源组（Resource Group）	无	按线程分配 CPU 资源	OLTP 和 OLAP 混合负载隔离

8.0 最大的工程意义：MySQL 从"好用"变成"现代数据库"。原子 DDL、窗口函数、CTE、Instant DDL——这四个特性让 MySQL 终于追上现代 SQL 标准的步伐。

8.0 → 8.4 LTS：稳定化与可靠性

改进	说明
LTS 版本策略	8.4 是首个 LTS（长期支持）版本，三年支持周期。之前"发一个大版本然后出 bugfix"的模式改为 Innovation + LTS 双轨：创新版每隔一个季度发布新功能，LTS 版只修复 bug
INSTANT DDL 进一步	`ALTER TABLE ... DROP COLUMN` 也支持 INSTANT
克隆插件增强	`CLONE INSTANCE` 支持版本差异克隆（小版本间）
半同步插件替换	推荐使用 Group Replication 的内置流控替代半同步

MySQL 版本演进全景

text

5.5 → 5.6: InnoDB 成为默认引擎、全文索引初现
      → 5.7: JSON 类型、GTID 持久化、无损半同步、sys schema
           → 8.0: 原子 DDL、窗口函数、CTE、Instant DDL、UTF8MB4 默认
                → 8.4: LTS 长期支持、Innovation/LTS 双轨策略

核心变化的旧机制 vs 新机制

以下展开几个"只有对比才知道好在哪"的关键变化。

1. GTID（5.6 实验 → 5.7 持久化）

旧做法（基于文件+位置的复制）：

sql

-- Slave 上指定从 Master 的哪个 binlog 文件的哪个偏移开始同步
CHANGE MASTER TO
    MASTER_LOG_FILE='binlog.000042',
    MASTER_LOG_POS=123456;

致命缺陷：每个 Slave 的"进度"是 (filename, offset) 二元组，这个信息在不同机器上没有统一语义。

Slave 宕机重启后很难定位从哪继续
主从切换（M1→M2）后，M2 的 binlog 文件名从头开始，"从 M1 的 binlog.000042 偏移 123456 继续"在 M2 上没有意义
运维必须手动计算位置——是高危操作

新做法（GTID）：

sql

-- 每个事务有全局唯一 ID：server_uuid:transaction_id
-- 例如：3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10086
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;

GTID 是全局唯一的，随便切主，新主能根据 GTID 知道"哪些事务 Slaves 已经执行过了"
不需手动计算偏移，一行配置搞定

2. 数据字典（5.7 frm → 8.0 事务型DD）

旧做法（frm 文件）：

text

data_dir/db_name/
├── user.frm     ← 表定义（二进制格式）
├── user.ibd     ← 表数据
└── user.MYD     ← MyISAM 数据（如果用了 MyISAM）

.frm 是独立的二进制文件，不经过 InnoDB 事务保护
表定义和表数据是两个独立系统——一个在文件系统，一个在 InnoDB
出事时最常见场景：ALTER TABLE 中途崩溃→ .frm 写了一半→ 表定义损坏→ 数据还在但读不出来

新做法（事务型数据字典）：

表定义存在 InnoDB 系统表空间（mysql.ibd）中，和用户数据在同一个事务域。ALTER TABLE 要么全部提交，要么全部回滚——不会再出现"定义和数据不一致"。

3. 原子 DDL（5.7 非原子 → 8.0 原子）

旧做法：ALTER TABLE ... ADD INDEX 内部是多步操作：

1. 创建临时 frm 文件（新表定义）
2. 创建临时 ibd 文件 → 逐行 copy → 建索引
3. 原子 rename 临时文件覆盖原文件
4. 删除旧文件

如果在步骤 2 中途崩溃：临时文件残留，占用磁盘空间；如果在步骤 3 到 4 之间崩溃：更糟——旧 frm 已被覆盖但对应的 ibd 还没删干净。

新做法（8.0+ 原子 DDL）：DDL 操作在 InnoDB 内部以事务方式执行，崩溃恢复时重放或回滚——不会留下中间状态。

4. Instant DDL（5.7 COPY → 8.0 INSTANT）

旧做法：ALTER TABLE ... ADD COLUMN 内部流程：

1. 创建新表（含新列）
2. 全量 copy 原表每一行到新表  ← 这是瓶颈
3. 期间持有 metadata lock → 阻塞所有读写
4. 建索引 → 再全量扫描一遍
5. rename 替换

1 亿行的表加一列 ≈ 全量拷贝 1 亿行 + 重建索引 → 可能需要数小时。

新做法（8.0.12+ INSTANT ADD COLUMN）：不 copy 数据。只在数据字典中更新表定义——新增列的默认值在"需要时才读"。ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT 毫秒级完成。

基本原理：InnoDB 行格式中有一个"记录头"（record header），包含 NULL-bitmap。Instant DDL 利用这个 bitmap，新增列标识为 NULL/默认值，物理行数据不做任何修改。只有当 UPDATE 给新列赋值时，才真正在行内分配存储。

5. Redo Log（8.0.29- 固定文件 → 8.0.30+ 动态容量）

旧做法：innodb_log_file_size = 2G + innodb_log_files_in_group = 2 → 4GB 写满了循环覆盖。如果要调整大小，必须：①停 MySQL → ②删旧 log 文件 → ③改参数 → ④重启（redo log 是启动时必须一致的关键文件，不能在线改）。

新做法（8.0.30+）：SET GLOBAL innodb_redo_log_capacity = 8589934592（8GB），在线生效。内部自动创建/删除 32MB 的 redo log 文件来凑总容量，不再需要停机。

6. 半同步复制（5.6 AFTER_COMMIT → 5.7 AFTER_SYNC）

这是 MySQL 主从复制中最容易被忽视但最影响数据安全的改进。

旧做法（AFTER_COMMIT）：

mermaid

sequenceDiagram
    participant C as Client
    participant M as Master
    participant S as Slave

    C->>M: COMMIT
    M->>M: 1. 写 binlog
    M->>M: 2. 提交 InnoDB 事务
    M->>M: 3. 返回 OK 给 Client
    Note over M,S: Client 已经收到 OK，认为数据已持久化
    M->>S: 4. 发送 binlog 给 Slave
    S-->>M: ACK

    Note over M,S: 如果在 2→4 之间 Master 宕机：<br/>Slave 永远收不到这个事务的 binlog<br/>但 Client 已经收到 OK！→ 数据丢失

致命问题：主库提交事务和通知备库不是原子的。主库提交了对 Client 返回 OK 之后、备库收到 binlog 之前，如果主库宕机——这个事务在备库上不存在。当备库提升为主库时，Client 以为已持久化的数据就彻底丢了。

新做法（AFTER_SYNC，5.7+，也称"无损半同步"）：

mermaid

sequenceDiagram
    participant C as Client
    participant M as Master
    participant S as Slave

    C->>M: COMMIT
    M->>M: 1. 写 binlog
    M->>S: 2. 发送 binlog 给 Slave
    S-->>M: 3. ACK (收到)
    M->>M: 4. 提交 InnoDB 事务
    M->>C: 5. 返回 OK

    Note over M,S: 备库先确认收到 → 主库再提交<br/>如果 2→4 之间 Master 宕机：<br/>Slave 有这个事务的 binlog → 提升后可以重放 ✅

核心改变：备库先确认收到 binlog，主库再提交事务。即使主库在提交前宕机，备库已经有了完整的 binlog，切换后可以重放。对 Client 来说，收到 OK 意味着至少有一个备库已经确认了——数据不会丢。

代价与权衡：

维度	AFTER_COMMIT	AFTER_SYNC
数据安全	可能丢	不丢 ✅
事务延迟	较低	较高（多一次网络往返 Slave→Master）
配置	`rpl_semi_sync_master_wait_point = AFTER_COMMIT`	`rpl_semi_sync_master_wait_point = AFTER_SYNC`
适用场景	可容忍少量数据丢失	金融、支付、订单等核心业务

8.4 的进一步变化：MySQL 8.4 LTS 推荐使用 Group Replication 的内置 Paxos 流控替代半同步复制，因为 Group Replication 在多数派确认后才提交，理论上比半同步（只需 1 个备库）更安全。

二、InnoDB 文件分布

2.1 磁盘文件布局

data_dir/
├── ibdata1              ← 系统表空间 (共享)
│   ├── 数据字典
│   ├── Doublewrite Buffer
│   ├── Change Buffer
│   └── Undo Log (可选)
├── ib_logfile0          ← Redo Log 文件 1
├── ib_logfile1          ← Redo Log 文件 2
├── undo_001 / undo_002  ← Undo 表空间 (8.0+ 独立)
├── mysql/               ← MySQL 系统库 .ibd
├── db_name/
│   ├── table.ibd        ← 独立表空间 (innodb_file_per_table=ON)
│   └── table.frm        ← 表结构定义 (8.0 后移入数据字典)
├── binlog.000001        ← 二进制日志
├── binlog.index
├── error.log            ← 错误日志
├── slow_query.log       ← 慢查询日志
└── relay-bin.000001     ← 中继日志 (从库)

2.2 InnoDB 页结构

页是 InnoDB 磁盘 I/O 的最小单位，默认 16KB：

┌──────────────────────────────────┐
│  File Header (38B)               │ ← 页号、类型、校验和、LSN
├──────────────────────────────────┤
│  Page Header (56B)               │ ← 槽数量、记录数、层级等
├──────────────────────────────────┤
│  Infimum + Supremum (26B)        │ ← 最小/最大虚拟记录
├──────────────────────────────────┤
│  User Records                    │ ← 实际数据 (单向链表)
├──────────────────────────────────┤
│  Free Space                      │ ← 空闲空间
├──────────────────────────────────┤
│  Page Directory                  │ ← Slot 稀疏目录 (二分查找)
├──────────────────────────────────┤
│  File Trailer (8B)               │ ← 校验和 + LSN (页完整性)
└──────────────────────────────────┘

2.3 InnoDB 行格式

不同行格式决定了每行数据在页内的存储方式，直接影响空间利用率和溢出页行为。

┌──────────────────────────────────────────────────┐
│              一行记录的通用结构                     │
├─────────┬──────────┬──────────┬─────────┬───────┤
│ 变长字段 │ NULL 位图 │ 记录头   │ 主键列  │ 其他列 │
│ 长度列表 │(1bit/列) │ (5B/8B) │        │       │
└─────────┴──────────┴──────────┴─────────┴───────┘

行格式	引入版本	特点	适用场景
REDUNDANT	5.0 以前	最老格式，字段偏移列表，无NULL位图	兼容旧系统
COMPACT	5.0	变长字段长度列表 + NULL位图，紧凑存储	默认格式
DYNAMIC	5.7+	类似 COMPACT，但长字段完全溢出存 `溢出页`	含 BLOB/TEXT 的表
COMPRESSED	5.7+	在 DYNAMIC 基础上支持页级压缩 (zlib)	读多写少的大表

溢出页（Overflow Page）机制：

行内存储 (COMPACT)：
┌──────────────────────────────┐
│ 溢出列: 前 768 字节 + 20B 指针 │ → 指向溢出页
└──────────────────────────────┘
         │
         ▼
┌──────────────────┐
│   溢出页          │
│   (剩余数据)       │
└──────────────────┘

行内存储 (DYNAMIC)：
┌──────────────────────┐
│ 溢出列: 仅 20B 指针    │ → 指向溢出页（完全溢出）
└──────────────────────┘

COMPACT：溢出列前 768 字节存行内，超过部分溢出 → 可能一行占多页，索引效率下降
DYNAMIC：溢出列完全存溢出页 → 行内仅指针，B+Tree 节点可存更多行，推荐格式

如何选择：

sql

-- 查看当前表的行格式
SHOW TABLE STATUS LIKE 'table_name'\G

-- 修改行格式 (需重建表)
ALTER TABLE t ROW_FORMAT=DYNAMIC;

一般场景 → DYNAMIC（MySQL 5.7+ 默认）
含 TEXT/BLOB 大字段 → 强烈推荐 DYNAMIC
读多写少 + 磁盘空间敏感 → COMPRESSED（CPU 换磁盘）

三、索引

3.1 B+ 树结构

InnoDB 所有索引都是 B+ 树：

                              ┌─────────────────┐
                              │  非叶子节点       │
                              │  [10 | 20 | 30]  │  ← 只存 key + 子页指针
                              └──┬───┬───┬───┬──┘
                            ┌────┘   │   │   └────┐
                            ▼        ▼   ▼        ▼
                    ┌──────────┐ ┌──────────┐ ┌──────────┐
                    │ 叶子节点  │ │ 叶子节点  │ │ 叶子节点  │  ← 全量数据 + 双向链表
                    │ (1..10)  │ │(11..20)  │ │(21..30)  │
                    └──────────┘ └──────────┘ └──────────┘

B+ 树特性：

所有数据存在叶子节点
叶子节点之间双向链表（范围查询高效）
非叶子节点只存索引键（单个节点可存更多 key）
高度通常 2-4 层（16KB 页可存约 1200 个 key）

3.2 聚簇索引 vs 二级索引

聚簇索引 (Clustered, 主键索引):
┌─────────────┬──────────────────┐
│  主键值     │   整行数据        │  ← 叶子存完整行
└─────────────┴──────────────────┘

二级索引 (Secondary):
┌─────────────┬──────────┐
│  索引列值   │  主键值   │  ← 叶子只存主键值
└─────────────┴──────────┘
         │
         │ 回表查询 (二次 B+Tree 查找)
         ▼
   聚簇索引 → 取出整行数据

主键设计原则：

✅ 自增 BIGINT：顺序插入，页利用率高，避免页分裂
❌ UUID/随机值：随机插入，频繁页分裂，碎片严重
❌ 过长主键：边长 → 二级索引膨胀

页分裂（Page Split）为什么是性能杀手？

B+Tree 的叶子节点是一个个 16KB 的页，页内记录按主键有序排列。当一个页满了但还要往中间插入新记录时，就会发生页分裂：

页分裂过程（UUID 随机主键的典型场景）：

原始状态：页 A 已满（约 500 行）
  页 A: [aaa, bbb, ccc, ..., zzz]  ← 满了

插入 key = "mmm"（随机值，落在页中间）：
  1. 分配新页 B
  2. 将页 A 后半部分（约 250 行）移动到页 B
  3. 在页 A 中间插入 "mmm"
  4. 更新父节点的索引指针

  页 A: [aaa, bbb, ..., mmm, ...]   ← 前半 + 新记录
  页 B: [nnn, ooo, ..., zzz]        ← 后半

代价：
  - 1 次页分配（磁盘空间）
  - 约 8KB 数据移动（250行 × 平均行长）
  - 父节点更新（可能级联分裂）
  - 页 A 和页 B 都只有 ~50% 利用率（空间浪费）

自增主键为什么不会页分裂？

自增主键总是追加到最后一页的末尾：
  页 A: [1, 2, 3, ..., 500]  ← 满了
  新页 B: [501]               ← 新记录直接追加到新页

不需要移动任何数据，页利用率接近 100%。
这就是为什么自增主键的写入性能比 UUID 高 3-5 倍。

页合并（Page Merge）：当相邻两页的利用率都低于 50% 时，InnoDB 会将它们合并为一页。这通常发生在大量删除之后。OPTIMIZE TABLE 可以强制重建表消除碎片。

3.3 覆盖索引

查询列全在索引中，无需回表：

sql

-- 有索引 idx(a, b, c)
SELECT a, b, c FROM t WHERE a = 1;     -- ✅ 覆盖，Using index
SELECT a, b, c, d FROM t WHERE a = 1;  -- ❌ d不在索引，需回表

EXPLAIN 中 Extra: Using index 即覆盖索引。

3.4 最左前缀原则

联合索引 (a, b, c) 的生效情况：

WHERE 条件	使用索引
`a = 1`	✅
`a = 1 AND b = 2`	✅
`a = 1 AND b = 2 AND c = 3`	✅
`a = 1 AND c = 3`	⚠️ 只用 a (c 跳过 b)
`b = 2`	❌ 不走 a
`b = 2 AND c = 3`	❌
`a > 1 AND b = 2`	✅ a (范围查询后中断)

3.5 索引下推 ICP（5.6+）

将部分 WHERE 条件在存储引擎层过滤，减少回表：

sql

-- 索引 idx(name, age)
SELECT * FROM t WHERE name LIKE '张%' AND age = 10;

无 ICP：LIKE 查出所有→回表→Server 层过滤 age 有 ICP：LIKE 查出→引擎层直接过滤 age→符合条件的才回表

3.6 Change Buffer（写缓冲）

5.6 前称 Insert Buffer，5.6+ 扩展支持 INSERT/UPDATE/DELETE 的二级索引缓存。

核心思想：当修改二级索引页不在 Buffer Pool 时，不立即读取该页，而是将操作暂存到 Change Buffer，等后续该页被读取时再合并（merge）。

INSERT INTO t (pk, idx_col) VALUES (1, 'abc');

┌─────────────────────────────────────────────┐
│ 1. 聚簇索引页: 在 Buffer Pool → 直接修改     │
│ 2. 二级索引页: 不在 Buffer Pool → 写入       │
│    Change Buffer (内存) → Change Buffer     │
│    (系统表空间 ibdata1，持久化)              │
└─────────────────────────────────────────────┘
         ↓ (后续该二级索引页被读取时)
┌─────────────────────────────────────────────┐
│ 3. Merge: 将 Change Buffer 中缓冲的修改      │
│    应用到读取到的二级索引页上                 │
└─────────────────────────────────────────────┘

收益与适用性：

场景	收益
写多读少（日志、流水表）	✅ 巨大：避免大量随机读 I/O
读多写少	❌ 不建议：merge 开销 > 收益
SSD + 高随机读能力	⚠️ 收益较小，可考虑关闭

sql

-- 查看/设置
SHOW VARIABLES LIKE 'innodb_change_buffering';   -- all/none/inserts/deletes/changes/purges
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';  -- 默认 25% of Buffer Pool

Merge 触发时机：

二级索引页被读入 Buffer Pool
后台 Master Thread 定期 merge
数据库关闭时

3.7 自适应哈希索引（Adaptive Hash Index, AHI）

InnoDB 自动在 Buffer Pool 的热点 B+Tree 页上构建哈希索引，将 O(log n) 的 B+Tree 查找加速为 O(1) 哈希查找。

B+Tree (Buffer Pool):
         ┌──────────────┐
         │  根页         │
         └──┬───┬───┬──┘
      ┌─────┘   │   └─────┐
      ▼         ▼         ▼
   ┌─────┐  ┌─────┐  ┌─────┐
   │页 A  │  │页 B  │  │页 C  │  ← 热点页
   └─────┘  └─────┘  └─────┘
       │
       ▼
   Hash Table (自动构建):
   key = 索引键值 → value = 叶子页记录指针

工作原理：

监控访问模式：某页被等值查询访问达到阈值 → 自动为该页构建哈希索引
仅对等值查询有效（=、IN），范围查询不走 AHI
完全自动：无需配置索引，InnoDB 自行决定何时构建/销毁

场景分析：

场景	AHI 效果
大量重复的等值查询	✅ 极佳（命中率高）
范围查询为主	❌ 无效果
写入频繁	⚠️ AHI 维护成本可能 > 收益，考虑关闭
LIKE 模糊查询	❌ 无效果

sql

SHOW ENGINE INNODB STATUS\G  -- 查看 AHI 命中率
-- 关闭 AHI (写入密集场景):
SET GLOBAL innodb_adaptive_hash_index = OFF;

四、事务与 MVCC

4.1 ACID 与 InnoDB

特性	实现方式
A 原子性	Undo Log（回滚）
C 一致性	约束 (PK/FK/Check) + 原子性保证
I 隔离性	MVCC + 锁 (行锁/间隙锁)
D 持久性	Redo Log + DoubleWrite Buffer

4.2 隔离级别

级别	脏读	不可重复读	幻读（InnoDB下）
READ UNCOMMITTED	✅ 会	✅ 会	✅ 会
READ COMMITTED (RC)	❌	✅ 会	✅ 会
REPEATABLE READ (RR)	❌	❌	⚠️ 部分防（间隙锁）
SERIALIZABLE	❌	❌	❌

InnoDB 默认 REPEATABLE READ，通过间隙锁（Gap Lock）+ Next-Key Lock 解决部分幻读。

4.3 MVCC 原理

快照读 vs 当前读：

sql

SELECT * FROM t WHERE id = 1;            -- 快照读 (MVCC, 无锁)
SELECT * FROM t WHERE id = 1 FOR UPDATE; -- 当前读 (加锁)
UPDATE t SET c=c+1 WHERE id = 1;         -- 当前读 (加锁)

隐藏列（每行）：

┌──────────┬──────────┬─────────────┬─────────────┬──────────┐
│ DATA_TRX_ID│ DATA_ROLL_PTR │ DB_ROW_ID  │  用户列...  │
│ (6B)      │ (7B)          │ (6B)       │            │
└──────────┴──────────┴─────────────┴─────────────┴──────────┘

DATA_TRX_ID：最近修改该行的事务 ID
DATA_ROLL_PTR：指向 Undo Log 的回滚指针
DB_ROW_ID：无主键时的隐式主键

ReadView（快照读时构造）判断可见性：

活跃事务列表: [trx_id_1, trx_id_2, ...]
规则: 该行 TRX_ID < min_active → 可见
      该行 TRX_ID > max_active → 不可见 (沿 ROLL_PTR 找历史版本)
      该行 TRX_ID 在活跃列表中 → 不可见

RC 与 RR 的区别：

RC：每次 SELECT 创建新 ReadView
RR：事务内第一次 SELECT 创建 ReadView，事务内复用

4.4 Undo Log 深入

Undo Log 是 InnoDB 实现原子性和 MVCC 的关键组件，存储修改前的旧版本数据。

UPDATE t SET c=100 WHERE id=1;  (原值 c=50)

Undo Log 记录:
┌──────────────────────────────────────────┐
│ 事务ID | 回滚指针 | id=1 | c(旧值)=50    │
│ (TRX_ID)|(ROLL_PTR)|      |              │
└──────────────────────────────────────────┘
        ↓
行记录 (聚簇索引叶子):
┌──────────┬─────────────┬──────────┬─────┐
│ TRX_ID=99│ ROLL_PTR ──→│   c=100  │ ... │  ← 指向 Undo Log
└──────────┴─────────────┴──────────┴─────┘

Undo Log 的类型：

类型	存储位置	内容	用途
INSERT Undo	独立表空间 `undo_00x`	插入行的主键	回滚 INSERT，事务提交后可立即删除
UPDATE Undo	独立表空间 `undo_00x`	修改前的旧值	回滚 UPDATE/DELETE + MVCC 读历史版本

Purge 线程：后台清理已提交事务的 Undo Log。

事务 T1 提交 → Undo Log 标记为可删除
              ↓
         Purge 线程 (innodb_purge_threads=4)
              ↓
         物理删除 Undo Log 记录

Purge 延迟会导致 Undo 表空间膨胀，SHOW ENGINE INNODB STATUS\G 查看 History list length。

Undo Log 的配置演变：

版本	Undo 存储	问题
5.6 及以前	ibdata1 系统表空间	Undo 膨胀导致 ibdata1 无法收缩
5.7	支持独立 Undo 表空间	截断需手动操作
8.0	独立 Undo 表空间 + 自动截断	`innodb_undo_log_truncate=ON`，完美

4.5 Doublewrite Buffer（双写缓冲）

解决**部分写失效（Partial Page Write）**问题：OS 写 16KB 页时断电，导致页只有一部分写入。

写入流程:

1. Buffer Pool 脏页准备刷盘
       │
       ▼
2. 先顺序写入 Doublewrite Buffer (ibdata1 或独立文件, 1MB)
       │
       ▼
3. fsync Doublewrite Buffer
       │
       ▼
4. 再随机写入数据文件 (.ibd) 的实际位置
       │
       ▼
5. 崩溃恢复时:
   ┌─ 数据页 checksum 正常 → 直接用
   └─ 数据页损坏 → 从 Doublewrite Buffer 恢复完整页

性能开销：

方面	说明
额外写入量	每页写两次（Doublewrite + 实际位置）
顺序写特性	Doublewrite 是顺序写（极快），额外开销可忽略
HDD vs SSD	HDD 时代必须，SSD + 原子写（Fusion-io）可关闭
关闭方式	`innodb_doublewrite = OFF`（有原子写能力的 SSD 才建议）

五、锁

5.1 锁粒度

锁类型	粒度	并发	开销
表锁 (`LOCK TABLES`)	表	最低	最小
行锁 (Record Lock)	行	高	中等
间隙锁 (Gap Lock)	记录间隙	中	中等
Next-Key Lock	行 + 间隙	中	中等

5.2 行锁模式

SELECT ... LOCK IN SHARE MODE;   -- S 锁 (共享锁)，8.0 改为 FOR SHARE
SELECT ... FOR UPDATE;            -- X 锁 (排他锁)

兼容矩阵：

	S	X
S	✅	❌
X	❌	❌

意向锁 IS/IX（表级，自动加）：

	IS	IX	S	X
IS	✅	✅	✅	❌
IX	✅	✅	❌	❌

5.3 间隙锁与 Next-Key Lock

sql

-- 表 t: id = 1, 5, 10, 15
SELECT * FROM t WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- 锁范围: (1,5] + (5,10] + (10,15)  ← Next-Key: 行+左开右闭间隙
-- 即锁住 5, 10 两行 + (1,5) (5,10) (10,15) 三个间隙

间隙锁只在 RR 级别下生效，目的防止幻读。

5.4 死锁

T1: UPDATE t SET c=1 WHERE id=1;  (锁住 id=1)
T2: UPDATE t SET c=2 WHERE id=2;  (锁住 id=2)
T1: UPDATE t SET c=1 WHERE id=2;  (等待 T2)
T2: UPDATE t SET c=2 WHERE id=1;  (等待 T1 → 死锁!)

InnoDB 自动检测死锁（等待图），回滚代价小的事务。

避免死锁：

固定访问顺序
尽量短事务
避免大事务中交互等待

六、Redo Log 与 Binlog

6.1 WAL (Write-Ahead Logging)

UPDATE 流程:

1. 修改 Buffer Pool 中的页 (标记为脏页)
2. 写 Redo Log (顺序写, 极快)
3. 写 Binlog
4. 返回客户端 "OK"
5. 后台刷脏页到磁盘

6.2 Redo Log

属性	说明
存储	`#innodb_redo` 目录（8.0.30+，动态容量）
大小	8.0.30+：`innodb_redo_log_capacity`（动态，默认 100MB） 8.0.29-：`innodb_log_file_size` × `innodb_log_files_in_group`（固定，需重启改）
用途	崩溃恢复 (crash-safe)
写方式	顺序追加，LSN 递增

6.3 Binlog

属性	说明
存储	`binlog.000001` 等
大小	轮转 (`max_binlog_size`)
用途	主从复制、数据恢复
格式	`STATEMENT` / `ROW` / `MIXED`（推荐 ROW）

6.4 两阶段提交（2PC）

Redo Log (prepare) → Binlog (write) → Redo Log (commit)

防止崩溃后 Redo Log 和 Binlog 不一致。恢复时：

Redo 有 prepare 但 binlog 无 → 回滚
Redo 有 prepare 且有 binlog → 提交

为什么需要两阶段提交？如果不用会怎样？

Redo Log 和 Binlog 是两个独立的日志系统（Redo 属于 InnoDB 引擎层，Binlog 属于 Server 层）。如果不用 2PC 协调，崩溃时可能出现不一致：

假设不用 2PC，而是先写 Redo 再写 Binlog：

场景1: Redo 写成功，Binlog 写之前崩溃
  → 恢复后：InnoDB 通过 Redo 恢复了这条数据（主库有）
  → 但 Binlog 没有这条记录 → 从库不会执行这条更新
  → 结果：主从数据不一致 ❌

假设反过来，先写 Binlog 再写 Redo：

场景2: Binlog 写成功，Redo 写之前崩溃
  → 恢复后：InnoDB 没有 Redo → 数据回滚（主库没有）
  → 但 Binlog 有这条记录 → 从库会执行这条更新
  → 结果：主从数据不一致 ❌

两阶段提交如何解决：

  Redo(prepare) → Binlog(write+fsync) → Redo(commit)

  崩溃点 A（Redo prepare 后，Binlog 前）：
    恢复时发现 Redo 有 prepare 但 Binlog 无 → 回滚
    → 主从一致 ✅（都没有这条数据）

  崩溃点 B（Binlog 后，Redo commit 前）：
    恢复时发现 Redo 有 prepare 且 Binlog 有 → 提交
    → 主从一致 ✅（都有这条数据）

组提交（Group Commit）优化

每个事务都 fsync 两次（Redo + Binlog）太慢。组提交将多个事务的 fsync 合并：

多个事务的提交流程被拆为三个阶段，每个阶段有一个 leader：

Flush 阶段: 多个事务的 Binlog 写入 page cache（内存）
Sync 阶段:  leader 一次 fsync 将所有事务的 Binlog 刷盘
Commit 阶段: 所有事务的 Redo Log 标记为 commit

效果：10 个事务只需要 1 次 fsync（而非 10 次）
     IOPS 从 10000 → 1000，吞吐量提升 5-10 倍

6.5 刷盘参数

参数	作用	推荐
`innodb_flush_log_at_trx_commit`	Redo Log 刷盘策略	1 (最安全)
`sync_binlog`	Binlog 刷盘策略	1 (最安全)

七、Buffer Pool

┌────────────────────────────────────────────┐
│                Buffer Pool                  │
│  ┌──────┬──────────────────────────────┐   │
│  │ LRU  │  Free List                    │   │
│  │ List │  (空闲页)                     │   │
│  │      │                               │   │
│  │ old  │  mid-point (3/8)              │   │
│  │ page │  防止全表扫描污染 LRU         │   │
│  └──────┴──────────────────────────────┘   │
└────────────────────────────────────────────┘

参数	建议
`innodb_buffer_pool_size`	物理内存 60-70%
`innodb_buffer_pool_instances`	CPU 核数 (≥ 1GB 时 ≥ 4)

八、SQL 语句与性能

8.1 SELECT 执行顺序（逻辑）

sql

SELECT DISTINCT a, COUNT(b)     -- 6. SELECT
FROM t1 JOIN t2 ON t1.id=t2.id  -- 1. FROM + 2. JOIN + 3. ON
WHERE t1.x > 10                 -- 4. WHERE
GROUP BY a                      -- 5. GROUP BY
HAVING COUNT(b) > 5             -- 7. HAVING
ORDER BY a DESC                 -- 8. ORDER BY
LIMIT 10;                       -- 9. LIMIT

8.2 EXPLAIN 解读

字段	关注点
`type`	ALL(全表)→index(索引扫描)→range(范围)→ref(非唯一)→eq_ref(唯一)→const(常量)
`key`	实际使用的索引
`rows`	预估扫描行数
`Extra`	`Using index`(覆盖) / `Using temporary`(临时表) / `Using filesort`(文件排序)

8.3 常见 SQL 优化

分页优化（大 offset）：

sql

-- ❌ 慢: offset 越大越慢
SELECT * FROM t ORDER BY id LIMIT 100000, 20;

-- ✅ 快: 延迟关联
SELECT * FROM t
INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) tmp
USING (id);

-- ✅ 更快: 游标分页 (但要求连续递增)
SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20;

JOIN 优化：

sql

-- 小表驱动大表 (优化器会自动选)
-- NLJ (Index Nested-Loop Join): 被驱动表用上索引
-- BNL (Block Nested-Loop Join): 无索引时用 Join Buffer (8.0.20 被 Hash Join 替代)

Hash Join 工作原理（8.0.18+ 引入，8.0.20 替代 BNL）

当被驱动表没有可用索引时，MySQL 8.0 使用 Hash Join 替代了旧的 Block Nested-Loop：

Hash Join 两阶段：

Build 阶段（构建哈希表）：
  1. 选择较小的表作为"构建表"（build table）
  2. 扫描构建表，对 join 列计算哈希值
  3. 将所有行存入内存哈希表

Probe 阶段（探测）：
  1. 扫描较大的表（probe table）
  2. 对每一行的 join 列计算哈希值
  3. 在哈希表中查找匹配行 → O(1)

示例：SELECT * FROM orders o JOIN users u ON o.user_id = u.id
  Build: users 表（小）→ 构建 hash(id) → row 的映射
  Probe: orders 表（大）→ 每行用 hash(user_id) 去查哈希表

性能对比（orders 100万行，users 1万行，user_id 无索引）：
  BNL:       O(100万 × 1万 / join_buffer_size) ≈ 数十秒
  Hash Join: O(100万 + 1万) ≈ 1-2 秒（线性！）
  NLJ+索引:  O(100万 × log(1万)) ≈ 0.5 秒（最快，但需要索引）

内存不足时的溢出处理：

如果构建表太大，哈希表放不进 join_buffer_size（默认 256KB）：
  → MySQL 使用 Grace Hash Join（分区溢出到磁盘）
  → 将两个表按哈希值分成多个分区
  → 每个分区独立做 Hash Join
  → 类似外部排序的思想

优化器 Trace — 看懂 MySQL 为什么选了这个执行计划

sql

-- 开启优化器 trace（会话级，用完关闭）
SET optimizer_trace = "enabled=on";

-- 执行你的查询
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' ORDER BY created_at LIMIT 10;

-- 查看优化器的决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关闭
SET optimizer_trace = "enabled=off";

Trace 输出的关键部分：

json

{
  "rows_estimation": [
    {
      "table": "orders",
      "range_analysis": {
        "table_scan": { "rows": 1000000, "cost": 102345.6 },
        "potential_range_indexes": [
          { "index": "idx_user_id", "usable": true },
          { "index": "idx_status", "usable": true }
        ],
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            { "index": "idx_user_id", "rows": 500, "cost": 601.2, "chosen": true },
            { "index": "idx_status", "rows": 200000, "cost": 240123.4, "chosen": false }
          ]
        }
      }
    }
  ],
  "considered_execution_plans": [
    { "plan": "idx_user_id + filesort", "cost": 1205.6, "chosen": true },
    { "plan": "idx_status + no sort", "cost": 240500.0, "chosen": false }
  ]
}

读 trace 的关键：

cost 越小越好——优化器选 cost 最低的方案
rows 是估算值——如果估算不准（统计信息过期），优化器可能选错
chosen: false 的方案告诉你"为什么没选它"

sql

-- 如果优化器选错了，可以用 hint 强制
SELECT /*+ INDEX(orders idx_status) */ * FROM orders WHERE ...;

-- 或者更新统计信息让优化器重新评估
ANALYZE TABLE orders;

GROUP BY/ORDER BY：

sql

-- 利用索引有序性, 避免 Using filesort 或 Using temporary
-- 索引 idx(a, b): 可优化 ORDER BY a, b / GROUP BY a, b

8.4 慢查询分析

slow_query_log = 1
long_query_time = 1.0          -- 超过 1 秒记录
log_queries_not_using_indexes = 1

分析工具：

bash

mysqldumpslow -s t -t 10 slow.log   # Top 10 耗时
pt-query-digest slow.log            # Percona Toolkit 详细分析

九、高可用架构

9.1 主从复制

          Master
         ┌──────┐
         │ binlog│──→ Dump Thread → 网络
         └──────┘
                      ↓
          Slave
         ┌──────────┐
         │ IO Thread│ → relay log
         │ SQL Thread│ → 回放 relay log
         └──────────┘

复制模式：

模式	特点
异步复制	Master 不等 Slave，可能丢数据
半同步复制 (5.5+)	至少一个 Slave 收到 binlog 才返回

半同步复制的"幻读"与 Lossless Semi-Sync

传统半同步复制（AFTER_COMMIT）的漏洞：

mermaid

sequenceDiagram
    participant C as Client
    participant M as Master
    participant S as Slave

    C->>M: COMMIT
    M->>M: 1. 写入 binlog
    M->>M: 2. 存储引擎提交（InnoDB COMMIT）
    M->>S: 3. 发送 binlog 给 Slave
    M-->>M: 💥 Master 在等待 ACK 时宕机！

    Note over C: Client 收到连接断开 → 认为提交失败
    Note over S: Slave 没收到 binlog

    Note over M,S: 切换后：Slave 提升为新 Master
    Note over C: Client 查询新 Master → 数据"不存在"
    Note over C: 但旧 Master 重启后 → 磁盘上有这条已提交的数据！
    Note over C: 🔴 Client 先被告知失败，后又看到数据存在 → "幻读"

问题根因：AFTER_COMMIT 模式下，存储引擎提交发生在等待 Slave ACK 之前。Master 宕机后，已提交的数据留在了磁盘上，但 Slave 没收到 binlog——导致数据在主从之间出现了分歧。

Lossless Semi-Sync（AFTER_SYNC）的修复——MySQL 5.7 引入：

mermaid

sequenceDiagram
    participant C as Client
    participant M as Master
    participant S as Slave

    C->>M: COMMIT
    M->>M: 1. 写入 binlog
    M->>S: 2. 发送 binlog 给 Slave
    S-->>M: ACK（收到 binlog）
    M->>M: 3. 存储引擎提交（InnoDB COMMIT）
    M-->>C: OK

    Note over M: 如果 Master 在步骤 2 等待 ACK 时宕机：
    Note over M: → 存储引擎还没提交
    Note over M: → 数据在 binlog 中有，但 InnoDB 中没有
    Note over M: → 从库提升为主库后，数据一致 ✅
    Note over M: → Master 重启后从 binlog 恢复该事务 → 数据也一致 ✅

text

关键区别:
  AFTER_COMMIT: binlog → COMMIT → 等 ACK
                Master 宕机 → 已 COMMIT 但 Slave 可能没有 → 不一致

  AFTER_SYNC:   binlog → 等 ACK → COMMIT
                Master 宕机 → 没 COMMIT，Slave 可能有 binlog
                → 新主库有数据，旧主库重启后也能从 binlog 恢复 → 一致 ✅
                但 Slave 收到了 binlog 意味着数据一定在集群中 → 不会丢失 ✅

配置：

sql

-- MySQL 5.7+ Lossless Semi-Sync
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;  -- 至少 1 个 Slave 确认
SET GLOBAL rpl_semi_sync_master_wait_point = AFTER_SYNC;   -- 🔑 关键参数

-- 超时后退化为异步复制（防止 Slave 故障阻塞 Master）
SET GLOBAL rpl_semi_sync_master_timeout = 10000;  -- 10 秒

参数	值	含义
`rpl_semi_sync_master_wait_point`	`AFTER_SYNC`	binlog 同步后、存储引擎提交前等待 ACK（MySQL 5.7 默认）
`rpl_semi_sync_master_wait_point`	`AFTER_COMMIT`	存储引擎提交后、返回客户端前等待 ACK（MySQL 5.6 行为）
`rpl_semi_sync_master_timeout`	`10000` (ms)	超时后退化为异步，避免 Slave 故障拖死 Master
组复制 MGR (5.7+)	Paxos 协议，多主/单主

9.2 分库分表

当单表数据量过亿或写入 QPS 超过单库承载能力时，需要分库分表。

拆分策略

垂直拆分 — 按业务模块拆分:
┌──────────┐   ┌──────────┐   ┌──────────┐
│  用户库    │   │  订单库    │   │  商品库    │
│ user_0..N │   │ order_0..N│   │ goods_0..N│
└──────────┘   └──────────┘   └──────────┘

水平拆分 (Sharding) — 按数据行拆分:
┌─────────────────────────────────────────┐
│ user_0 │ user_1 │ user_2 │ user_3       │
│ id%4=0 │ id%4=1 │ id%4=2 │ id%4=3      │
└─────────────────────────────────────────┘

维度	垂直拆分	水平拆分
拆分方式	按业务模块（表不同）	按数据行（相同表不同库）
解决什么	单库表太多、业务耦合	单表数据量/写入量过大
实现难度	较低（自然边界）	较高（路由、JOIN、事务）
典型场景	微服务拆分	订单表/日志表达到千万级

分片键 (Sharding Key) 选择

text

分片键直接影响数据分布的均匀性和查询效率：

1. 哈希取模: user_id % N
   优点: 分布均匀
   缺点: 扩容需数据迁移 (N → 2N 时约 50% 数据需移动)

2. 一致性哈希: hash(user_id) → 虚拟节点环
   优点: 扩容只需迁移 ~1/N 的数据
   缺点: 实现复杂，热点需手动处理

3. 范围分片: 按时间/地域/数字范围
   优点: 扩容简单，范围查询友好
   缺点: 热点不均匀 (新数据集中写入)

4. 复合分片: 如 (user_id % N) + 时间范围
   优点: 兼顾均匀性与查询效率

选型原则：以最频繁的查询模式为准。80% 的查询都带 user_id，就用 user_id 做分片键；如果既有 user_id 查询又有 order_id 查询，需要冗余或路由表。

分库分表带来的问题

问题	描述	解决方案
跨分片 JOIN	两个表数据不在同一库	应用层组装、冗余字段、ES 宽表
跨分片事务	分散在多个库的数据需要一致性	分布式事务 (Seata TCC/Saga) 或最终一致性
全局唯一 ID	自增 ID 在不同库会冲突	Snowflake、Leaf-Segment、UUID v7
跨分片排序/分页	`ORDER BY ... LIMIT 10, 20` 语义错乱	应用层归并排序（修改每个分片 offset 为 0, limit+offset）
扩容数据迁移	分片数从 N 变 M 需要迁移	双写 + 灰度切流、一致性哈希
聚合统计	COUNT/SUM/AVG 需要跨分片	定时汇总到统计表、ES/ClickHouse

// 跨分片分页的归并排序处理
func shardedPagination(userID int64, page, pageSize int) ([]Order, error) {
    // 每个分片都查 pageSize 条（因为无法确定分页偏移）
    // 实际上需要查 offset+pageSize 条然后归并
    return queryAndMerge(userID, 0, (page+1)*pageSize, pageSize)
}

中间件对比

中间件	类型	特点	适用场景
ShardingSphere-Proxy	独立代理	JDBC 兼容、功能全、社区活跃	Java 生态
Vitess	独立代理	YouTube 出品、MySQL 原生协议	大规模 MySQL 集群
MyCat 2	独立代理	国产、MySQL 协议兼容	中小规模
TiDB	NewSQL	原生分布式、无需中间件	不想自己运维分片的场景

什么时候不需要分库分表

text

✅ 需要分库分表的信号:
  - 单表 > 2000 万行 且 无法通过索引/归档优化
  - 单库写入 QPS > 3000 且 无法通过读写分离/缓存解决
  - 磁盘/内存成为瓶颈

❌ 不需要分库分表的信号:
  - 单表 < 1000 万行 → 先优化索引/SQL
  - 读多写少 → 先加缓存 (Redis/本地缓存)
  - 数据可归档 → 冷热分离（定期迁移老数据）

中间件：ShardingSphere-Proxy、Vitess、MyCat。

9.3 Online DDL — 不锁表的表结构变更

生产环境最怕的操作之一就是 ALTER TABLE。理解 Online DDL 的原理能帮你判断"这个 DDL 会不会锁表"。

Online DDL 的三种算法

算法	锁级别	原理	适用
INSTANT (8.0.12+)	无锁	只修改元数据，不动数据文件	加列（末尾）、改默认值
INPLACE	短暂 MDL 锁	在原表上原地修改	加索引、改列类型（部分）
COPY	长时间锁表	创建临时表 → 拷贝数据 → 重命名	改主键、改字符集

sql

-- 指定算法（如果不支持会报错，而非静默降级）
ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_user(user_id), ALGORITHM=INPLACE, LOCK=NONE;

INPLACE 的内部流程

ALTER TABLE orders ADD INDEX idx_status(status);

1. 获取 MDL 写锁（极短暂，毫秒级）
2. 创建临时的 .frm 文件（新表结构）
3. 释放 MDL 写锁 → 降级为 MDL 读锁（此时 DML 可以正常执行）
4. 扫描原表数据，构建新索引（这一步最耗时，但不阻塞 DML）
5. 期间的 DML 操作记录到 Online DDL Log（row log）
6. 再次获取 MDL 写锁（极短暂）
7. 将 row log 中的增量变更应用到新索引
8. 替换旧的 .frm → 完成
9. 释放 MDL 写锁

关键：步骤 4 期间表可以正常读写！只有步骤 1 和 6 有短暂锁。

哪些操作真的不锁表？

操作	8.0 算法	是否阻塞 DML
加列（末尾）	INSTANT	❌ 不阻塞
加列（中间）	INPLACE	❌ 不阻塞
删列	INPLACE	❌ 不阻塞
加索引	INPLACE	❌ 不阻塞
删索引	INPLACE	❌ 不阻塞
改列类型（如 INT→BIGINT）	COPY	✅ 阻塞！
改主键	COPY	✅ 阻塞！
改字符集	COPY	✅ 阻塞！
改列名	INPLACE	❌ 不阻塞

生产中的 DDL 工具

即使 Online DDL 不锁表，大表的 DDL 仍然可能：

占用大量磁盘 I/O（扫描全表构建索引）
导致主从延迟（从库需要重放 DDL）

生产推荐使用第三方工具：

bash

# pt-online-schema-change（Percona）
# 原理：创建新表 → 触发器同步增量 → 原子 rename
pt-osc --alter "ADD INDEX idx_status(status)" D=mydb,t=orders

# gh-ost（GitHub）
# 原理：创建新表 → 通过 binlog 同步增量 → 原子 rename（无触发器）
gh-ost --alter "ADD INDEX idx_status(status)" --database=mydb --table=orders --execute

工具	原理	优势	劣势
原生 Online DDL	INPLACE	无额外依赖	大表仍有 I/O 压力
pt-osc	触发器	成熟稳定	触发器有性能开销
gh-ost	Binlog 解析	无触发器，可暂停/限速	需要 binlog ROW 格式

十、关键参数速查

参数	建议值	说明
`innodb_buffer_pool_size`	内存的 60-70%	核心参数
`innodb_flush_log_at_trx_commit`	1	崩溃安全
`sync_binlog`	1	binlog 安全
`innodb_log_file_size`	1-4G（8.0.29-）	Redo Log 单文件大小
`innodb_redo_log_capacity`	1-4G（8.0.30+）	Redo Log 总容量（动态调整，替代旧参数）
`innodb_io_capacity`	SSD→2000+	后台 I/O 能力
`max_connections`	按需 (500-2000)	注意内存
`innodb_thread_concurrency`	0 (不限制)	或 CPU核数×2
`innodb_flush_method`	O_DIRECT	绕过 OS 缓存

十一、客户端工具与诊断

11.1 mysql 命令行常用操作

bash

# === 连接 ===
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -h 127.0.0.1 -u root -p -D mydb          # 指定数据库
mysql -h 127.0.0.1 -u root -p -e "SELECT 1"      # 执行单条语句

# === 导入导出 ===
mysqldump -h 127.0.0.1 -u root -p mydb > dump.sql         # 导出
mysqldump --single-transaction --master-data=2 mydb > dump.sql  # 一致性备份
mysql -h 127.0.0.1 -u root -p mydb < dump.sql              # 导入
mysql -h 127.0.0.1 -u root -p -e "SOURCE /path/to/file.sql" # 交互式导入

# === 执行模式 ===
mysql -t      # 表格输出
mysql -v      # 详细模式 (打印每条SQL)
mysql -H      # HTML 输出
mysql --tee=/tmp/query.log   # 同时输出到文件

# === 安全连接 ===
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=ca.pem

11.2 诊断命令

SHOW — 状态与元信息

sql

-- === 系统状态 ===
SHOW STATUS LIKE '%conn%';              -- 连接统计
SHOW STATUS LIKE '%innodb%';            -- InnoDB 状态
SHOW STATUS LIKE 'Threads%';            -- 线程统计
SHOW STATUS LIKE '%handler_read%';      -- 索引读统计 (判断全表扫描)

-- === 进程列表 ===
SHOW PROCESSLIST;                        -- 当前连接与执行的 SQL
SHOW FULL PROCESSLIST;                   -- 显示完整 SQL (不截断)

-- === 变量 ===
SHOW VARIABLES LIKE '%buffer_pool%';     -- 查看参数
SHOW GLOBAL VARIABLES LIKE '%timeout%';  -- 全局参数

-- === 数据库对象 ===
SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE t;                     -- 建表语句
SHOW INDEX FROM t;                       -- 索引信息
SHOW TABLE STATUS LIKE 't'\G             -- 表统计(行数/大小/行格式/引擎)

-- === 锁信息 (8.0+) ===
SHOW ENGINE INNODB STATUS\G;             -- InnoDB 引擎状态 (含死锁)
SELECT * FROM performance_schema.data_locks;      -- 当前持有的锁
SELECT * FROM performance_schema.data_lock_waits; -- 锁等待

INFORMATION_SCHEMA 与 PERFORMANCE_SCHEMA

sql

-- === INFORMATION_SCHEMA (元数据) ===
-- 查看所有表大小
SELECT table_schema, table_name,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       ROUND((data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY total_mb DESC;

-- 无主键表
SELECT table_schema, table_name FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys')
  AND table_name NOT IN (SELECT DISTINCT table_name
                         FROM information_schema.columns
                         WHERE column_key = 'PRI');

-- === PERFORMANCE_SCHEMA (运行时性能) ===
-- Top 10 耗时 SQL (按总等待时间)
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec,
       AVG_TIMER_WAIT/1e9 AS avg_ms, SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 当前运行的 SQL
SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO, TIMER_WAIT/1e9 AS ms
FROM performance_schema.events_statements_current
WHERE PROCESSLIST_INFO IS NOT NULL;

-- 表 I/O 统计
SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_READ, COUNT_WRITE,
       SUM_TIMER_READ/1e9 AS read_ms, SUM_TIMER_WRITE/1e9 AS write_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','performance_schema')
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

慢查询日志

sql

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

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

-- 查看慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

bash

# 命令行分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log     # Top 10 耗时
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log     # Top 10 次数
mysqldumpslow -a -s t -t 10 /var/log/mysql/slow.log  # 显示真实值(不抽象)
pt-query-digest /var/log/mysql/slow.log               # Percona 详细分析报告

错误日志

bash

# 错误日志位置
mysql -e "SHOW VARIABLES LIKE 'log_error';"

# 查看最近错误
tail -100 $(mysql -N -e "SELECT @@log_error")

# 实时监控错误
tail -f $(mysql -N -e "SELECT @@log_error") | grep -i "error\|warning\|corrupt"

11.3 常见错误排查

症状	排查命令	常见原因
连接慢/超时	`SHOW PROCESSLIST` + DNS 检查	`skip-name-resolve` 未设, DNS 慢
Too many connections	`SHOW VARIABLES LIKE 'max_connections'`	连接数不够/连接泄漏
锁等待超时	`SHOW ENGINE INNODB STATUS\G` → LATEST DETECTED DEADLOCK	死锁/长事务未提交
慢查询增多	`SHOW FULL PROCESSLIST` + 慢查询日志	新 SQL 没索引/统计信息过期
CPU 高	`SHOW PROCESSLIST` + `top -H -p <pid>`	并发高/排序多/无索引
Crash / 数据损坏	错误日志 + `innodb_force_recovery`	磁盘满/硬件故障
主从延迟	`SHOW SLAVE STATUS\G` → Seconds_Behind_Master	从库负载/大事务/DDL
磁盘空间满	`du -sh /var/lib/mysql` + binlog 清理	binlog 堆积/表膨胀

快速诊断三板斧：

bash

# 1. 看连接和运行 SQL
mysql -e "SHOW FULL PROCESSLIST;"

# 2. 看 InnoDB 状态
mysql -e "SHOW ENGINE INNODB STATUS\G" > innodb_status.txt

# 3. 看慢查询 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

十二、SQL 语句全览

12.1 DDL（数据定义）

sql

-- === 库操作 ===
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
DROP DATABASE mydb;
ALTER DATABASE mydb CHARACTER SET utf8mb4;

-- === 表操作 ===
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL DEFAULT '',
    email VARCHAR(100) DEFAULT NULL,
    age TINYINT UNSIGNED DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    KEY idx_name (name),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD INDEX idx_phone (phone);
ALTER TABLE users DROP INDEX idx_phone;
ALTER TABLE users RENAME TO customers;

-- 截断/删除表
TRUNCATE TABLE users;   -- 快速清空 (DDL, 不记录 undo)
DROP TABLE users;

12.2 DML（数据操作）

sql

-- === INSERT ===
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com');
INSERT INTO users SET name='David', email='david@example.com';              -- 另一种语法
INSERT INTO users (name, email) SELECT name, email FROM tmp_users;          -- INSERT SELECT
INSERT IGNORE INTO users (id, name) VALUES (1, 'Eve');                      -- 忽略冲突(不报错)
REPLACE INTO users (id, name, email) VALUES (1, 'Eve', 'eve@example.com');  -- 存在则替换(Delete+Insert)

-- === INSERT ON DUPLICATE KEY UPDATE === (upsert)
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'new@example.com')
ON DUPLICATE KEY UPDATE name=VALUES(name), email=VALUES(email);

-- === UPDATE ===
UPDATE users SET name='Alice Updated' WHERE id = 1;
UPDATE users SET age=age+1 WHERE id > 10;                          -- 表达式
UPDATE users u JOIN orders o ON u.id=o.user_id SET u.status='vip' WHERE o.amount > 1000;  -- 联表更新

-- === DELETE ===
DELETE FROM users WHERE id = 1;
DELETE u FROM users u JOIN orders o ON u.id=o.user_id WHERE o.status='cancelled';  -- 联表删除

12.3 DQL（数据查询）

基础查询

sql

SELECT [DISTINCT] col1, col2, ...
FROM table1
[JOIN table2 ON condition]
[WHERE conditions]
[GROUP BY col1, col2]
[HAVING aggregate_condition]
[ORDER BY col1 [ASC|DESC], col2]
[LIMIT offset, count]
[FOR UPDATE | LOCK IN SHARE MODE];

JOIN 类型

sql

-- INNER JOIN: 两表匹配的行
SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id=o.user_id;

-- LEFT JOIN: 左表全部 + 右表匹配
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id=o.user_id;

-- RIGHT JOIN: 右表全部 + 左表匹配 (MySQL 支持但少用)
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id=o.user_id;

-- CROSS JOIN: 笛卡尔积
SELECT * FROM users CROSS JOIN departments;

-- SELF JOIN: 自连接
SELECT e1.name employee, e2.name manager FROM users e1 LEFT JOIN users e2 ON e1.manager_id=e2.id;

子查询

sql

-- 标量子查询 (结果 = 单个值)
SELECT name FROM users WHERE id = (SELECT MAX(id) FROM users);

-- IN 子查询
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- EXISTS 子查询 (关联子查询, 逐行判断)
SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.amount>100);

-- FROM 子查询 (派生表)
SELECT avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount FROM orders GROUP BY user_id) t
WHERE avg_amount > 500;

-- 8.0+ WITH (CTE, 公共表表达式)
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, COALESCE(uo.cnt, 0) AS order_count FROM users u LEFT JOIN user_orders uo ON u.id=uo.user_id;

-- 8.0+ 递归 CTE (树形结构)
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id, 1 AS level FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, cte.level+1 FROM categories c
    JOIN cte ON c.parent_id=cte.id
)
SELECT * FROM cte;

12.4 聚合函数

函数	说明
`COUNT(*)` / `COUNT(col)` / `COUNT(DISTINCT col)`	计数
`SUM(col)`	求和
`AVG(col)`	平均值
`MIN(col)` / `MAX(col)`	最小值/最大值
`GROUP_CONCAT(col [ORDER BY] [SEPARATOR])`	分组拼接字符串
`JSON_ARRAYAGG(col)` / `JSON_OBJECTAGG(key, val)`	聚合成 JSON (8.0+)

12.5 窗口函数（8.0+）

sql

-- 基础窗口: ROW_NUMBER / RANK / DENSE_RANK
SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_num
FROM employees;

-- 统计窗口
SELECT name, salary,
    SUM(salary) OVER (ORDER BY salary) AS running_total,            -- 累计求和
    AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg3  -- 滑动均值
FROM employees;

-- 偏移窗口
SELECT name, salary,
    LAG(salary, 1)  OVER (ORDER BY salary) AS prev_salary,         -- 前一行
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary,         -- 后一行
    FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS top_earner,
    NTILE(4) OVER (ORDER BY salary) AS quartile                     -- 分桶
FROM employees;

12.6 常用内置函数

字符串函数：

函数	说明
`CONCAT(s1, s2, ...)`	拼接
`CONCAT_WS(sep, s1, s2, ...)`	带分隔符拼接
`SUBSTRING(s, pos, len)`	截取 (pos 从 1 开始)
`LENGTH(s)` / `CHAR_LENGTH(s)`	字节长度 / 字符长度
`TRIM(s)` / `LTRIM(s)` / `RTRIM(s)`	去空格
`UPPER(s)` / `LOWER(s)`	大小写转换
`REPLACE(s, from, to)`	替换
`LOCATE(sub, s)` / `INSTR(s, sub)`	查找位置
`LEFT(s, n)` / `RIGHT(s, n)`	左/右截取
`LPAD(s, len, pad)` / `RPAD(s, len, pad)`	左/右填充

日期时间函数：

函数	说明
`NOW()` / `CURRENT_TIMESTAMP`	当前日期时间
`CURDATE()` / `CURRENT_DATE`	当前日期
`DATE_FORMAT(d, fmt)`	格式化日期
`STR_TO_DATE(s, fmt)`	字符串转日期
`DATE_ADD(d, INTERVAL n UNIT)`	日期加
`DATE_SUB(d, INTERVAL n UNIT)`	日期减
`DATEDIFF(d1, d2)`	日期差 (天数)
`TIMESTAMPDIFF(unit, d1, d2)`	时间差 (任意单位)
`UNIX_TIMESTAMP(d)`	转 Unix 时间戳
`FROM_UNIXTIME(ts)`	Unix 时间戳转日期

数学函数：ROUND、CEIL/CEILING、FLOOR、ABS、MOD、RAND()。

条件函数：

sql

IF(condition, true_val, false_val)
IFNULL(val, default)                          -- val 为 NULL 时用 default
COALESCE(val1, val2, ..., default)            -- 返回第一个非 NULL 值
CASE WHEN cond1 THEN val1 WHEN cond2 THEN val2 ELSE default END
NULLIF(val1, val2)                            -- val1==val2 返回 NULL, 否则返回 val1

十三、数据类型与存储

13.1 数值类型

类型	字节	范围（有符号）	说明
`TINYINT`	1	-128 ~ 127	微整数，如 age / status
`SMALLINT`	2	-32768 ~ 32767	小整数
`MEDIUMINT`	3	-838 万 ~ 838 万	中等整数
`INT` / `INTEGER`	4	-21 亿 ~ 21 亿	常规整数
`BIGINT`	8	-9×10¹⁸ ~ 9×10¹⁸	主键首选（自增不会耗尽）
`FLOAT(p)`	4	~7 位精度	单精度浮点 (不推荐, 精度丢失)
`DOUBLE`	8	~15 位精度	双精度浮点 (不推荐, 精度丢失)
`DECIMAL(M,D)`	变长	精确	金额专用（定点数, M=总位数, D=小数位）
`BIT(M)`	(M+7)/8	0 ~ 2^M-1	位字段

选型建议：

sql

age TINYINT UNSIGNED              -- 年龄 0-255
price DECIMAL(10, 2)              -- 金额 (整数 8 位 + 2 位小数)
status TINYINT NOT NULL DEFAULT 0 -- 状态码枚举 (比 ENUM 更灵活)
view_count BIGINT UNSIGNED        -- 大计数器
PRIMARY KEY BIGINT UNSIGNED AUTO_INCREMENT  -- 主键

浮点数陷阱：FLOAT 和 DOUBLE 是近似存储，0.1 + 0.2 ≠ 0.3。所有涉及钱的场景必须用 DECIMAL。

13.2 字符串类型

类型	存储	最大长度	说明
`CHAR(N)`	定长 N 字节	255	固定长度（如 MD5、手机号）
`VARCHAR(N)`	变长 + 1-2B 前缀	65535 字节	通用字符串首选
`TINYTEXT`	变长 + 1B 前缀	256B	短文本
`TEXT`	变长 + 2B 前缀	64KB	长文本（文章内容）
`MEDIUMTEXT`	变长 + 3B 前缀	16MB	超长文本
`LONGTEXT`	变长 + 4B 前缀	4GB	极长文本
`BLOB` 系列	同 TEXT	同 TEXT	二进制（图片/文件, 不推荐存 DB）
`ENUM('a','b','c')`	1-2B	65535 个值	枚举（字符串映射为整数）
`SET('a','b','c')`	1-8B	64 个值	集合（位图存储）
`JSON`	变长	1GB	JSON 文档（8.0+, 二进制优化存储）

CHAR vs VARCHAR 存储对比：

CHAR(10) 存 "hi":     [h][i][ ][ ][ ][ ][ ][ ][ ][ ]  ← 10 字节, 末尾空格补全
VARCHAR(10) 存 "hi":  [2][h][i]                        ← 3 字节 (1B 长度 + 2B 数据)
VARCHAR(255) 存 "hi": [2][h][i]                        ← 3 字节 (1B 长度)
VARCHAR(256) 存 "hi": [2][0][h][i]                     ← 4 字节 (2B 长度, 因为>255)

选型建议：

sql

md5_hash CHAR(32) NOT NULL                 -- 固定长度, CHAR 效率更高
mobile CHAR(11) DEFAULT NULL               -- 固定长度
name VARCHAR(50) NOT NULL DEFAULT ''        -- 变长, 最长 50 字
description VARCHAR(500) DEFAULT NULL       -- 短描述
content TEXT                               -- 长内容 (文章正文)
tags JSON                                  -- 8.0+ JSON, 灵活查询

TEXT vs VARCHAR：TEXT 数据存 off-page（溢出页），大字段查询/排序不走内存临时表（会落盘），性能比 VARCHAR 差。能 VARCHAR 就别 TEXT。

13.3 日期时间类型

类型	字节	范围	时区	精度	说明
`DATE`	3	1000-01-01 ~ 9999-12-31	❌	天	日期
`TIME`	3	-838:59:59 ~ 838:59:59	❌	微秒	时间/时长
`YEAR`	1	1901 ~ 2155	❌	年	年份 (8.0 已废弃)
`DATETIME`	5 (旧) / 5+小数	1000-01-01 00:00:00 ~ 9999-12-31	❌	微秒	推荐
`TIMESTAMP`	4 + 小数	1970-01-01 00:00:01 ~ 2038-01-19	✅	微秒	自动转时区

DATETIME vs TIMESTAMP：

维度	DATETIME	TIMESTAMP
存储	5 字节+小数	4 字节+小数
时区	无感知 (存什么读什么)	自动转换 (存 UTC, 读按 `time_zone`)
2038 问题	❌ 无	⚠️ 有 (上限 2038-01-19)
默认值	`DEFAULT CURRENT_TIMESTAMP`	同
适用	固定时间(生日/合同日)	跨时区业务

选型建议：

sql

birthday DATE NOT NULL                         -- 纯日期
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP          -- 创建时间（推荐）
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新
meeting_time TIMESTAMP                         -- 跨时区场景 (如国际化应用)
duration TIME                                  -- 时长

13.4 空间占用估算

定长部分（每行固定）：

组成部分	大小
DB_ROW_ID (无主键时)	6B
DB_TRX_ID	6B
DB_ROLL_PTR	7B
行头 (Record Header)	5B (COMPACT)

变长部分（每行）：

组成部分	大小
变长字段长度列表	1-2B / 变长字段
NULL 位图	1 bit / 可 NULL 列 (向上取整到字节)

计算示例：

sql

-- 表: id BIGINT, name VARCHAR(50), age TINYINT, email VARCHAR(100), created_at DATETIME
-- 一行数据:
-- 定长: 主键(8B) + TRX_ID(6B) + ROLL_PTR(7B) + age(1B) + created_at(5B) + 行头(5B) = 32B
-- 变长: name(50字 ~ 150B UTF8MB4) + email(100字 ~ 300B UTF8MB4)
-- 变长字段长度: 2×2B = 4B (两个VARCHAR)
-- NULL位图: 1B (5个可NULL列)
-- 总计: ~487B/行

存储选型技巧：

1. 主键越小越好 → 二级索引叶子存主键, 主键大 = 所有索引膨胀
2. 能用 TINYINT 不用 INT, 能用 INT 不用 BIGINT
3. 固定长度用 CHAR (如 md5), 变长用 VARCHAR
4. 避免使用 ENUM (ALTER 代价大, 排序按内部值)
5. TEXT/BLOB 存 off-page, 影响排序/分组性能
6. 金额用 DECIMAL, 绝不用 FLOAT/DOUBLE

十四、工程实践：MySQL 真正难的是慢查询背后的资源竞争

14.0 一眼看懂：MySQL 慢通常慢在资源排队

mermaid

flowchart LR
    A["SQL 进入执行"] --> B["索引 / 扫描 / 回表"]
    B --> C["锁等待 / 事务竞争"]
    C --> D["刷盘 / checkpoint / I/O"]
    D --> E["连接堆积 / 应用超时"]

现象	优先看什么	常见误判
用了索引还慢	`rows examined`、回表、filesort	只看“有没有索引”
RT 抖动大	锁、脏页、flush、磁盘 util	只怪查询语法复杂
从库延迟	SQL 线程、回放成本、从库负载	只怪复制机制差
连接堆积	长事务、慢 SQL、池等待	只调连接数

14.1 MySQL 变慢，通常不是因为“SQL 语法复杂”，而是资源在底层某一层开始排队

一个查询慢下来，背后可能卡在很多位置：

没走对索引，扫描行数暴涨
回表太多，随机读成本高
排序/临时表过重
锁等待
Buffer Pool 命中不足
刷脏页和 checkpoint 压力
下游磁盘或复制线程跟不上

所以线上判断 MySQL 慢，关键不是先看 SQL 长得难不难，而是先定位：时间到底耗在扫描、等待、锁、I/O，还是提交路径上。

14.2 `rows examined` 远比“有没有索引”更能解释真实性能

很多查询明明“用了索引”，但依然慢，原因通常是：

索引选择性差
走了索引但回表很多
范围过宽
排序和分组没有利用索引顺序
优化器估计失真导致选错计划

所以判断查询质量时，更应该关注：

扫描多少行
返回多少行
是否临时表
是否 filesort
是否出现大量随机回表

14.3 长事务和锁等待，经常比慢 SQL 本身更伤系统

慢 SQL 通常只影响自己，但长事务和锁等待会让问题扩散：

undo 不能及时清理
history list length 上升
purge 跟不上
行锁/间隙锁把后续事务也拖住
最终变成 RT 抖动、连接堆积、死锁增加

mermaid

flowchart LR
    A["长事务未提交"] --> B["Undo 无法及时回收"]
    B --> C["Purge 压力上升 / 历史版本堆积"]
    C --> D["锁等待与查询成本上升"]
    D --> E["应用超时、连接堆积"]

14.4 Buffer Pool 和刷脏页决定的是“稳定性”，不只是平均性能

MySQL 看起来“平时挺快，偶尔突然抖一下”，常见原因之一就是：

脏页比例高
checkpoint age 接近上限
后台刷盘追不上前台写入
某一时刻被迫加速刷脏页
提交、查询、复制都一起被波及

这类问题的关键特征往往是：

平均 QPS 还行
P99 / P999 明显变差
磁盘 util 抬高
应用偶发超时

14.5 二级索引和主键设计，会把写放大到整个表结构

主键如果：

太长
随机
经常变更

带来的问题不只是主键本身，而是：

聚簇索引页分裂增加
所有二级索引叶子都更胖
回表成本更高
缓存命中率变差
写入放大和碎片加重

所以主键设计其实是 “全表索引布局设计”，不是一个字段命名问题。

14.6 主从延迟本质上是“从库消费速度跟不上主库生产速度”

主从延迟常见来源：

主库大事务
主库 DDL
从库 SQL 线程执行慢
从库查询压力过高
磁盘/网络瓶颈
行格式、索引和热点更新导致回放成本高

所以 Seconds_Behind_Master 只是表象，更关键的是看：

哪一段落后
是网络接收慢，还是 relay log 回放慢
是 SQL 线程堵了，还是资源被查询抢走了

14.7 一个典型故障链：`app -> mysql`

mermaid

sequenceDiagram
    participant A as App
    participant M as MySQL
    participant D as Disk

    A->>M: SQL 请求
    Note over M: 计划变差 / 锁等待 / 大量回表
    M->>D: 随机读写增加
    Note over D: 刷盘 / checkpoint 压力上升
    D-->>M: I/O 延迟上升
    M-->>A: RT 变高
    Note over A: 连接占用时间变长，线程池/连接池堆积

14.8 常见误判

现象	容易误判为	实际可能是
用了索引还慢	MySQL 不稳定	索引选择性差/回表太多
CPU 不高但 RT 高	数据库不忙	锁等待、I/O 等待、刷脏页
从库延迟	复制机制差	大事务、从库负载高、磁盘慢
QPS 下降	应用问题	MySQL 内部排队或计划退化
磁盘忙	单纯 I/O 差	查询模式或 checkpoint 压力导致

14.9 排障顺序

现象	优先看什么
单条 SQL 慢	`EXPLAIN`, `rows examined`, 是否回表/排序
大面积 RT 抖动	`SHOW ENGINE INNODB STATUS`, 磁盘、锁等待、flush 压力
连接堆积	`SHOW FULL PROCESSLIST`, 长事务、慢 SQL
死锁/锁超时	`data_locks`, `data_lock_waits`, InnoDB STATUS
从库延迟	`SHOW SLAVE STATUS\G` 或复制状态、SQL 线程负载

14.10 一个实战原则

text

先判断慢在“扫描、锁、刷盘、复制”哪一层，
再决定是改 SQL、改索引、改参数还是改架构；
不要一上来就调参数。

MySQL 深度解析 ​

一、架构与进程/线程模型 ​

1.1 整体架构 ​

1.2 线程模型 ​

1.3 版本演进：5.6 → 5.7 → 8.0 ​

5.6 → 5.7：性能与复制革命 ​

5.7 → 8.0：现代化与性能飞跃 ​

8.0 → 8.4 LTS：稳定化与可靠性 ​

MySQL 版本演进全景 ​

核心变化的旧机制 vs 新机制 ​

1. GTID（5.6 实验 → 5.7 持久化） ​

2. 数据字典（5.7 frm → 8.0 事务型DD） ​

3. 原子 DDL（5.7 非原子 → 8.0 原子） ​

4. Instant DDL（5.7 COPY → 8.0 INSTANT） ​

5. Redo Log（8.0.29- 固定文件 → 8.0.30+ 动态容量） ​

6. 半同步复制（5.6 AFTER_COMMIT → 5.7 AFTER_SYNC） ​

二、InnoDB 文件分布 ​

2.1 磁盘文件布局 ​

2.2 InnoDB 页结构 ​

2.3 InnoDB 行格式 ​

三、索引 ​

3.1 B+ 树结构 ​

3.2 聚簇索引 vs 二级索引 ​

页分裂（Page Split）为什么是性能杀手？ ​

3.3 覆盖索引 ​

3.4 最左前缀原则 ​

3.5 索引下推 ICP（5.6+） ​

3.6 Change Buffer（写缓冲） ​

3.7 自适应哈希索引（Adaptive Hash Index, AHI） ​

四、事务与 MVCC ​

4.1 ACID 与 InnoDB ​

4.2 隔离级别 ​

4.3 MVCC 原理 ​

4.4 Undo Log 深入 ​

4.5 Doublewrite Buffer（双写缓冲） ​

五、锁 ​

5.1 锁粒度 ​

5.2 行锁模式 ​

5.3 间隙锁与 Next-Key Lock ​

5.4 死锁 ​

六、Redo Log 与 Binlog ​

6.1 WAL (Write-Ahead Logging) ​

6.2 Redo Log ​

6.3 Binlog ​

6.4 两阶段提交（2PC） ​

为什么需要两阶段提交？如果不用会怎样？ ​

组提交（Group Commit）优化 ​

6.5 刷盘参数 ​

七、Buffer Pool ​

八、SQL 语句与性能 ​

8.1 SELECT 执行顺序（逻辑） ​

8.2 EXPLAIN 解读 ​

8.3 常见 SQL 优化 ​

Hash Join 工作原理（8.0.18+ 引入，8.0.20 替代 BNL） ​

优化器 Trace — 看懂 MySQL 为什么选了这个执行计划 ​

8.4 慢查询分析 ​

九、高可用架构 ​

9.1 主从复制 ​

半同步复制的"幻读"与 Lossless Semi-Sync ​

9.2 分库分表 ​

拆分策略 ​

分片键 (Sharding Key) 选择 ​

分库分表带来的问题 ​

中间件对比 ​

什么时候不需要分库分表 ​

9.3 Online DDL — 不锁表的表结构变更 ​

Online DDL 的三种算法 ​

INPLACE 的内部流程 ​

哪些操作真的不锁表？ ​

生产中的 DDL 工具 ​

十、关键参数速查 ​

十一、客户端工具与诊断 ​

11.1 mysql 命令行常用操作 ​

11.2 诊断命令 ​

SHOW — 状态与元信息 ​

INFORMATION_SCHEMA 与 PERFORMANCE_SCHEMA ​

慢查询日志 ​

错误日志 ​

11.3 常见错误排查 ​

十二、SQL 语句全览 ​

MySQL 深度解析

一、架构与进程/线程模型

1.1 整体架构

1.2 线程模型

1.3 版本演进：5.6 → 5.7 → 8.0

5.6 → 5.7：性能与复制革命

5.7 → 8.0：现代化与性能飞跃

8.0 → 8.4 LTS：稳定化与可靠性

MySQL 版本演进全景

核心变化的旧机制 vs 新机制

1. GTID（5.6 实验 → 5.7 持久化）

2. 数据字典（5.7 frm → 8.0 事务型DD）

3. 原子 DDL（5.7 非原子 → 8.0 原子）

4. Instant DDL（5.7 COPY → 8.0 INSTANT）

5. Redo Log（8.0.29- 固定文件 → 8.0.30+ 动态容量）

6. 半同步复制（5.6 AFTER_COMMIT → 5.7 AFTER_SYNC）

二、InnoDB 文件分布

2.1 磁盘文件布局

2.2 InnoDB 页结构

2.3 InnoDB 行格式

三、索引

3.1 B+ 树结构

3.2 聚簇索引 vs 二级索引

页分裂（Page Split）为什么是性能杀手？

3.3 覆盖索引

3.4 最左前缀原则

3.5 索引下推 ICP（5.6+）

3.6 Change Buffer（写缓冲）

3.7 自适应哈希索引（Adaptive Hash Index, AHI）

四、事务与 MVCC

4.1 ACID 与 InnoDB

4.2 隔离级别

4.3 MVCC 原理

4.4 Undo Log 深入

4.5 Doublewrite Buffer（双写缓冲）

五、锁

5.1 锁粒度

5.2 行锁模式

5.3 间隙锁与 Next-Key Lock

5.4 死锁

六、Redo Log 与 Binlog

6.1 WAL (Write-Ahead Logging)

6.2 Redo Log

6.3 Binlog

6.4 两阶段提交（2PC）

为什么需要两阶段提交？如果不用会怎样？

组提交（Group Commit）优化

6.5 刷盘参数

七、Buffer Pool

八、SQL 语句与性能

8.1 SELECT 执行顺序（逻辑）

8.2 EXPLAIN 解读

8.3 常见 SQL 优化

Hash Join 工作原理（8.0.18+ 引入，8.0.20 替代 BNL）

优化器 Trace — 看懂 MySQL 为什么选了这个执行计划

8.4 慢查询分析

九、高可用架构

9.1 主从复制

半同步复制的"幻读"与 Lossless Semi-Sync

9.2 分库分表

拆分策略

分片键 (Sharding Key) 选择

分库分表带来的问题

中间件对比

什么时候不需要分库分表

9.3 Online DDL — 不锁表的表结构变更

Online DDL 的三种算法

INPLACE 的内部流程

哪些操作真的不锁表？

生产中的 DDL 工具

十、关键参数速查

十一、客户端工具与诊断

11.1 mysql 命令行常用操作

11.2 诊断命令

SHOW — 状态与元信息

INFORMATION_SCHEMA 与 PERFORMANCE_SCHEMA

慢查询日志

错误日志

11.3 常见错误排查

十二、SQL 语句全览