MySQL 与 PostgreSQL 数据存储结构与字段变更机制深度解析
MySQL InnoDB 与 PostgreSQL 在底层存储架构、数据组织方式以及 DDL 变更机制上存在显著差异。MySQL 采用聚簇索引架构,数据按主键顺序存储,适合主键查询密集型场景;PostgreSQL 采用堆表架构,通过独立的 t_ctid 版本链管理数据更新,适合高频更新场景。深入理解两者的存储结构与字段变更逻辑,对于数据库选型及大表运维至关重要。
01核心存储架构对比
| 对比维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 页面大小 | 固定 16KB | 默认 8KB (可调整) |
| 页面布局 | Header + Records + Directory + Trailer | Header + ItemIdData[] + FreeSpace + Items[] |
| 记录组织 | 聚簇索引,按主键有序 | 堆表,按插入顺序无序 |
| 主索引结构 | 数据存储在主键 B+ 树叶子节点 | 堆表 + 独立主键 B+ 树索引 |
| 索引叶子存储 | 存储完整记录数据 | 存储索引键 + CTID 指针 |
| 行标识 | 主键值 | CTID (Page 号 + 偏移量) |
| MVCC 实现 | roll_pointer 指向 Undo Log | t_xmin/t_xmax + t_ctid 版本链 |
MySQL 与 PostgreSQL 核心架构差异
02MySQL InnoDB 存储机制详解
InnoDB 的 16KB 数据页是其物理存储的基本单元,页面内部通过 Page Directory 机制实现高效的记录查找。记录以单向链表形式串联,通过二分查找定位槽位,再进行组内扫描,将查找复杂度从 O(n) 降低至 O(log n)。在行格式上,MySQL 8.0 默认采用 DYNAMIC 格式,对长字段(如 VARCHAR)采用完全溢出策略,仅在数据页存储 20 字节的溢出指针,从而提升索引扫描效率。
| 页面组件 | 关键参数 | 功能说明 |
|---|---|---|
| Page Header | 38 bytes | 存储页号、LSN、前后页指针、记录数等元信息 |
| User Records | 不定长 | 实际行数据,通过 next_record 指针串联 |
| Page Directory | 槽数组 | 每 4-8 条记录为一个槽,支持二分查找 |
| 溢出处理 | 完全溢出 | DYNAMIC 格式下,超长列数据仅存储指针 |
InnoDB 数据页核心组件
03PostgreSQL 存储机制与版本链
PostgreSQL 的 8KB 页面采用了独特的“指针与数据分离”布局。ItemIdData 数组从页头向下生长,存储指向实际元组(Items)的指针;实际元组数据则从页尾向上生长。这种设计为 MVCC 实现提供了基础,通过 t_ctid 字段构建版本链。索引永远指向数据的初始版本(V1),更新操作产生新版本时,旧版本的 t_ctid 指向新版本,查询时通过链式追溯获取最新可见数据。
- ItemIdData 结构 (4 bytes)
包含 lp_off (偏移量)、lp_len (长度) 及 lp_flags (状态位),提供稳定的间接寻址层。 - HeapTupleHeader 结构 (23+ bytes)
包含 t_xmin (创建事务)、t_xmax (删除事务) 及 t_ctid (当前位置指针)。 - t_ctid 版本链追溯
查询从索引指向的 V1 开始,检查 t_ctid 是否自引用;若非自引用,则跳转至下一版本,直到找到 t_xmax=0 的最新版本。
04DDL 字段变更机制深度解析
在处理表结构变更(DDL)时,MySQL 和 PostgreSQL 采用了截然不同的策略。MySQL 构建了三层算法体系(INSTANT、INPLACE、COPY),试图在保证数据一致性的同时减少锁表时间;PostgreSQL 则基于 MVCC 原理,通过元数据标记或渐进式重写来实现非阻塞变更。
DDL 变更机制对比
三层算法体系
INSTANT (毫秒级):仅修改元数据,适用于末尾追加字段,无需触碰数据页。
INPLACE (在线):避免全表复制,通过“在线日志”记录 DDL 期间的 DML,最后重放日志。
COPY (阻塞):创建临时表并复制数据,会阻塞写操作,适用于复杂变更。


元数据与版本控制
ADD COLUMN:若有默认值或允许 NULL,仅更新元数据,旧元组访问时补全默认值。
DROP COLUMN:仅标记 attisdropped=true,物理数据保留,通过 VACUUM 延迟回收。
UPDATE 开销:索引永不更新,仅修改 t_ctid 指针,极大降低了字段变更的维护成本。
05大表 DDL 工程实践指南
针对生产环境中的大表操作,直接执行 DDL 存在锁表风险或主从延迟过高的问题。需要根据数据库类型及变更场景,选择合适的工具与策略。
- MySQL 策略
优先利用 8.0 的 INSTANT 算法;若需添加索引或修改列类型,评估使用 INPLACE。对于超大表(>10GB)或必须锁表的场景,建议使用 pt-osc(基于触发器)或 gh-ost(基于 Binlog)进行无锁变更。 - PostgreSQL 策略
遵循“先加后改”原则:先添加可空字段(立即完成),再分批 UPDATE 填充默认值,最后设置 NOT NULL 约束。对于需要回收空间或整理碎片的场景,使用 pg_repack 工具进行在线重建。 - 执行前检查
确认磁盘空间是否充足(MySQL COPY 算法需 2x 空间),评估主从复制延迟风险,并选择业务低峰期执行。
当前文章无评论,是时候发表评论了