1
2
3
4
5

id  | name   | age | balance
----|--------|-----|--------
1   | Alice  | 28  | 1000
2   | Bob    | 35  | 2500
3   | Carol  | 22  | 800

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

朴素方案：一行一行存
  - 一行 100 字节，磁盘扇区 4KB
  - 读一行实际读了 4KB（40 倍浪费）
  - 写一行也要刷 4KB

正确方案：把行打包成页（如 16KB 一个）
  - 一次 I/O 读 / 写一整页
  - 内部按行寻址（offset）
  - 缓冲池管理也是以页为单位
  - 局部性原理：相邻行经常一起访问

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

行存（Row-Based）：每一行连续存储
  [id1, name1, age1, bal1][id2, name2, age2, bal2]...
  适合：SELECT * FROM t WHERE id=1  （整行取出）
       UPDATE t SET age=age+1 WHERE id=1  （改一行）
  → OLTP 场景

列存（Columnar）：每一列连续存储
  id 列:  [1, 2, 3, ...]
  name 列: [Alice, Bob, Carol, ...]
  age 列: [28, 35, 22, ...]
  适合：SELECT AVG(age) FROM t  （只读 age 列）
       按列压缩率高（同质数据）
  → OLAP 场景

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

                       Tablespace（表空间）
                              │
                       Segment（段）
                       一个表 / 索引 = 一个或多个段
                              │
                       Extent（区）
                       连续多个 Page 组成
                              │
                       Page / Block（页 / 块）
                       磁盘 I/O 最小单位
                              │
                       Row / Tuple（行 / 元组）
                       实际的数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

一个 8KB Oracle Block 内部：

  ┌─────────────────────────────┐
  │   Block Header（块头）       │  ~100 字节
  │   - 类型（data/index/undo）  │
  │   - SCN、事务槽 (ITL)       │
  ├─────────────────────────────┤
  │   Row Directory（行目录）    │  每行 2~4 字节偏移
  │   指向下面的行实际位置         │
  ├─────────────────────────────┤
  │   Free Space（空闲区）        │  从中间向两边生长
  ├─────────────────────────────┤
  │   Row Data（行数据）          │  实际行 + 列值
  └─────────────────────────────┘

关键：ITL（Interested Transaction List）
  - 每个被修改的 block 记录涉及的事务
  - 是 Oracle MVCC 实现的关键（详见第 5 篇）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

一个 16KB InnoDB Page 内部：

  ┌─────────────────────────────┐
  │  File Header（38B）          │
  │  - 页号、表空间、前后页指针    │
  ├─────────────────────────────┤
  │  Page Header（56B）          │
  │  - 索引 ID、记录数、空闲指针   │
  ├─────────────────────────────┤
  │  Infimum + Supremum Records │  系统伪行（最小/最大）
  ├─────────────────────────────┤
  │  User Records                │  实际行记录
  │  （从下往上增长）              │
  ├─────────────────────────────┤
  │  Free Space                  │
  ├─────────────────────────────┤
  │  Page Directory（槽位）       │  二分查找索引
  ├─────────────────────────────┤
  │  File Trailer（8B）          │  校验和、LSN
  └─────────────────────────────┘

每行结构（COMPACT 格式）：
  [变长字段长度][NULL 位图][记录头][事务ID][回滚指针][列1][列2]...
                                   ↑          ↑
                              DB_TRX_ID   DB_ROLL_PTR
                              （用于 MVCC，详见第 5 篇）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

一个 8KB PostgreSQL Page：

  ┌─────────────────────────────┐
  │  Page Header（24B）          │
  │  - LSN、校验和、行数          │
  ├─────────────────────────────┤
  │  ItemId Array（行指针）       │  4B/项
  ├─────────────────────────────┤
  │  Free Space                  │
  ├─────────────────────────────┤
  │  Tuples（实际行）             │
  │  （从下往上生长）              │
  └─────────────────────────────┘

每个 Tuple 头（23B 起）：
  [xmin][xmax][cid][ctid][infomask][...][columns]
   ↑     ↑                ↑
  创建  删除/更新         当前物理位置（用于 HOT 更新）
  事务  事务

1
2
3
4
5

观察：
  - 三家结构非常相似：Header + Directory + Free + Data
  - InnoDB / PG 都把事务信息（MVCC）直接放在行头
  - Oracle 用独立的 ITL 槽（块级别）记录事务
  - PG 8KB 是写死的（编译时定），其他三家可配

1
2
3
4

关键差异：
  - SQL Server 用 TempDB 统一承载，是性能调优重点
  - MySQL 8.0 之前临时表都在 ibdata1，导致系统表空间膨胀
  - PG 临时表和普通表共用存储，但通过 relpersistence 区分

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

堆表（HEAP）：
  数据行无序堆放在数据页中
  主键只是"另一个二级索引"
  索引指向行的物理位置（rowid / ctid / RID）

  ┌────────┐         ┌──────────────────┐
  │ 主键索引 │ ──────→│ Heap Page         │
  │ B+树    │         │ [row3][row1][row2]│  ← 无序
  └────────┘         └──────────────────┘

  特点：
  - 主键索引和数据分离
  - 主键查询要"两次 B+ 树查找"（先找索引，再读数据页）
  - 二级索引可以指向 rowid（短）
  - 代表：PostgreSQL、Oracle（默认）、SQL Server（默认）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

索引组织表（IOT / Clustered Table）：
  数据行直接按主键有序存放在 B+ 树的叶子节点
  "主键索引就是数据本身"

       主键 B+ 树（叶子就是数据）
              │
              ▼
  ┌─────────────────────────────┐
  │ [PK=1, row1][PK=2, row2]... │  ← 主键有序
  └─────────────────────────────┘

  特点：
  - 没有独立的"堆"，整张表就是一棵 B+ 树
  - 主键查询只需走一棵树
  - 二级索引指向主键值（长，且需要二次查找）
  - 代表：MySQL InnoDB、SQL Server（Clustered）、Oracle IOT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

InnoDB 中：
  - 每张表 = 一棵按主键排序的 B+ 树（Clustered Index）
  - 这棵树的叶子节点直接存"完整行"
  - 主键之外的索引叫 Secondary Index，叶子节点存的是"主键值"

  表 accounts(id PK, name, balance):

  Clustered Index (按 id):
                       [10│20]               ← 内部节点
                      /       \
              [1│2│...10]   [11│...│20]      ← 叶子 = 整行数据
              ↓               ↓
              id=1 的完整行   id=11 的完整行

  Secondary Index (name):
              [Alice│Bob]                ← 内部节点
              /         \
        [Alice→1][Bob→2]   [Carol→3]     ← 叶子 = name + 主键值
                                       （查到主键后，回 Clustered 树再查一次）

1
2
3
4
5
6

-- 一次查询，两种索引路径差异
SELECT balance FROM accounts WHERE id = 1;
-- 直接走 Clustered 树，1 次 I/O

SELECT balance FROM accounts WHERE name = 'Alice';
-- 走 Secondary 树找到主键 id=1 → 回 Clustered 树找到 balance → 2 次 I/O（"回表"）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

PostgreSQL 中：
  - 表数据存在 Heap（堆）中，无序
  - 所有索引（包括主键）都是"二级索引"
  - 索引指向 ctid（物理位置：页号 + 行偏移）

  Heap File:
  Page 0:  [row(id=1)][row(id=3)][row(id=2)]   ← 任意顺序
  Page 1:  [row(id=5)][row(id=4)][row(id=6)]

  Primary Key Index (id):
              [2│4│6]
              /   |   \
      [1→(0,1)] [2→(0,3)] [3→(0,2)]
                  叶子 = id + ctid

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 看看实际的 ctid
SELECT ctid, id, name FROM accounts;
-- ctid   id  name
-- (0,1)  1   Alice
-- (0,2)  3   Carol
-- (0,3)  2   Bob
-- 物理顺序 ≠ 主键顺序

-- 主键查询也要两步：索引找 ctid → heap 取数据
SELECT balance FROM accounts WHERE id = 1;
-- 走 PK 索引找到 ctid=(0,1) → 回 heap 读 → 2 次 I/O

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

实战含义：
  - InnoDB 中改主键 = 灾难（所有二级索引失效重建）
    → 所以 InnoDB 主键推荐：自增 BIGINT，永不变
  - PG 中改主键 = 改个约束（索引重建，但 heap 不动）
    → PG 改主键代价相对小

  - InnoDB 二级索引查询性能 < PG（需要"回主键树"）
  - 但 InnoDB 主键范围查询（按时间、按 ID 范围）> PG

这就是为什么 MySQL 表设计强调"主键设计"，PG 强调"索引设计"。

1
2
3
4
5
6
7
8

没有缓冲池：
  读：每次 SELECT 都从磁盘读页 → 极慢
  写：每次 UPDATE 都直接刷盘 → 极慢 + 磁盘磨损

有缓冲池：
  读：先查 Buffer Pool，命中直接返回；未命中则从磁盘读入
  写：直接改 Buffer Pool 中的页（变成"脏页" dirty page）
       后台异步刷盘（详见第 7 篇 Checkpoint）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

改进版 LRU（中点插入策略）：
  把 LRU 链分成两段：

  [Old sublist（冷区）] | [New sublist（热区）]
              ↑                   ↑
           midpoint           midpoint move threshold
              ↑
       新读入的页插这里
       （而不是直接进热区）

  真正热的数据：在冷区停留超过 innodb_old_blocks_time（默认 1 秒）
                才会被推入热区
  → 全表扫描的页只会短暂留在冷区，被快速淘汰，不会冲掉热区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- Oracle
ALTER SYSTEM SET db_cache_size = 8G;  -- Buffer Cache 大小
ALTER SYSTEM SET db_keep_cache_size = 1G;  -- 热点小表保留

-- SQL Server
-- "max server memory" 控制整个 Buffer Pool 上限
EXEC sp_configure 'max server memory (MB)', 16384;
RECONFIGURE;

-- MySQL
[mysqld]
innodb_buffer_pool_size = 8G       -- 最重要参数，建议物理内存 60~70%
innodb_buffer_pool_instances = 8   -- 多实例，减少锁竞争
innodb_old_blocks_time = 1000      -- 冷区停留时间（ms）

-- PostgreSQL
shared_buffers = 2GB               -- 推荐 25% 物理内存
effective_cache_size = 8GB         -- 仅给优化器参考，不实际分配

1
2
3
4
5

调优经验：
  - MySQL：innodb_buffer_pool_size 是 OLTP 性能第一参数，通常设为物理内存 70%
  - PostgreSQL：shared_buffers 一般不超过物理内存 25%（PG 还要用 OS page cache）
  - SQL Server：max server memory 通常留 4~8GB 给 OS
  - Oracle：SGA（含 Buffer Cache）+ PGA 总和 ≈ 物理内存 80%

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SQL Server 2012+ 引入 Columnstore Index：
  - 一张表可以有 Columnstore Index（聚集或非聚集）
  - 行组（Rowgroup）：每 100 万行为一组，按列编码存储
  - 列段（Column Segment）：每个列段存储一组数据
  - 配合 Batch Mode 执行（向量化）

CREATE CLUSTERED COLUMNSTORE INDEX cci ON big_table;
-- 整表变成列存

CREATE NONCLUSTERED COLUMNSTORE INDEX ncci ON big_table(col1, col2);
-- 局部列存（2014+）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

Oracle 12.1.0.2+ 引入 In-Memory Column Store（IMCS）：
  - 数据在 SGA 中以"行存 + 列存"双格式存储
  - 列存叫 In-Memory Compression Unit（IMCU）
  - OLTP 走行存（Buffer Cache），OLAP 走列存（IMCS）

ALTER TABLE big_table INMEMORY;
-- 该表的列被加载到 IMCS

特点：
  - 行/列双写：内存占用大
  - 透明：优化器自动选择
  - 商业版高级特性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

1. cstore_fdw（早期，Citus 出品）
   - 用 FDW（Foreign Data Wrapper）实现
   - 已基本停更

2. Citus（已并入 PG 生态）
   - 列式压缩 + 分布式

3. TimescaleDB（时序场景）
   - 时序数据自动按时间分区 + 压缩

4. Greenplum（PG 分支）
   - MPP 数据库，原生列存支持

1
2
3
4

MySQL 8.x 没有列存。
- HeatWave（MySQL Cloud 在 OCI 上的扩展）有列存加速
- 但开源 MySQL 没有列存
- 分析查询需要走 OLAP 引擎（如外接 Doris / ClickHouse）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SHOW ENGINES;

-- 主要引擎：
-- InnoDB     默认，事务、行锁、MVCC
-- MyISAM     老引擎，表锁，崩溃恢复弱
-- Memory     内存表，重启丢失
-- Archive    高压缩归档，只支持插入
-- NDB        分布式集群（MySQL Cluster）
-- Blackhole  什么都不存（用于复制中转）
-- 第三方：RocksDB（MyRocks）、TokuDB、TiDB

1
2
3
4

设计：
  每张表可以选不同引擎
  CREATE TABLE orders (...) ENGINE=InnoDB;
  CREATE TABLE logs (...) ENGINE=Archive;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

Oracle 不是"多引擎"，而是统一引擎 + 多种段类型：

  段类型：
  - Heap Table（默认堆表）
  - Index-Organized Table（IOT，索引组织表）
  - Partitioned Table（分区表）
  - Cluster Table（聚簇表）
  - External Table（外部表，HDFS 文件等）

  压缩选项：
  - OLTP Table Compression
  - Advanced Compression
  - Hybrid Columnar Compression（HCC，Exadata 专属）

1
2
3
4
5
6
7
8
9

SQL Server 一张表的状态：
  - 堆表（Heap）：没有 Clustered Index
  - Clustered Table：有 Clustered Index（按主键组织）

  CREATE TABLE t (id INT PRIMARY KEY, ...);
  -- PRIMARY KEY 隐式创建 Clustered Index → 表变成 Clustered

  ALTER TABLE t DROP CONSTRAINT pk_t;
  -- 删主键后，表变回 Heap

1
2
3
4

内存优化表（In-Memory OLTP）：
  - SQL Server 2014+ 引入
  - 完全在内存中，无锁（用 MVCC）
  - 用于极高性能 OLTP

1
2
3
4
5
6
7
8

PG 12 引入 Table Access Method (TAM) 抽象：
  - 12 之前：所有表都是 Heap
  - 12+：可以插件化实现新表类型
  - 目前主流还是 heap TAM

  扩展：
  - zheap（PG 社区研发，目标减少表膨胀，未发布）
  - 其他自定义 TAM

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

OLTP 工作负载特征：
  - 大量短事务
  - 按主键 / 索引点查
  - 修改单行多列（UPDATE）

OLAP 工作负载特征：
  - 少量长查询
  - 大范围扫描
  - 按列聚合（GROUP BY + SUM/AVG）

行存优势（OLTP）：
  - 一次 I/O 读出整行（修改单行多列只需 1 次页 I/O）
  - 局部性好（同时访问同一行的多列）
  - 事务实现简单（锁单行）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

InnoDB 表设计：
  1. 必须有主键（推荐 BIGINT AUTO_INCREMENT）
     → 没有 PK 的话会选唯一键，再没有就生成隐藏 6 字节 ROW_ID
  2. 主键尽量短（让二级索引小）
  3. 主键不要变更（否则所有二级索引重建）
  4. 主键单调递增（减少 B+ 树页分裂）

PostgreSQL 表设计：
  1. 主键选 UUID 或 SERIAL/BIGSERIAL 都行（不影响 heap）
  2. 关键在索引设计
  3. 注意 fillfactor（默认 100，频繁更新降到 80~90 留 HOT 空间）

SQL Server 表设计：
  1. 选择 Clustered Index 列（默认主键）
  2. Clustered 列要短、单调、不变
  3. 大表考虑分区 + Columnstore 索引（混合）

Oracle 表设计：
  1. 默认 Heap Table，分析场景考虑 IOT 或分区表
  2. 用 Sequence 而非 AUTO（Oracle 12c+ 才有 IDENTITY）
  3. HCC 压缩用于 Exadata 上的数仓

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

1. MySQL InnoDB：UUID 主键
   问题：UUID 无序 → B+ 树频繁页分裂 → 插入性能差 + 索引膨胀
   方案：用 BIGINT AUTO_INCREMENT 或有序 UUID

2. PostgreSQL：高更新场景
   问题：MVCC 写新 tuple + 旧 tuple 不删 → 表膨胀
   方案：autovacuum 配置、fillfactor 降低、pg_repack 重整

3. SQL Server：Clustered Index 选错
   问题：用 GUID 当 Clustered Key → 页分裂 + 缓存碎片
   方案：Clustered 用 IDENTITY INT/BIGINT，GUID 用 ROWGUIDCOL + NonClustered

4. Oracle：HCC 压缩误用
   问题：在 OLTP 表上用 HCC → 锁升级 + 性能崩溃
   方案：HCC 仅用于只读 / 批量加载场景

1
2
3
4

记住三句话：
  1. 物理存储 = 页为单位的 B+ 树 / 堆文件
  2. InnoDB 主键就是数据，PG 主键只是另一个索引
  3. 修改是先改内存页，后台异步刷盘（这就是"事务持久性"实现的基础）