什么值得买App下载 App ›
连续签到专享现金活动

MySQL 与 PostgreSQL 数据存储结构与字段变更机制深度解析

MySQL InnoDB 与 PostgreSQL 在底层存储架构、数据组织方式以及 DDL 变更机制上存在显著差异。MySQL 采用聚簇索引架构,数据按主键顺序存储,适合主键查询密集型场景;PostgreSQL 采用堆表架构,通过独立的 t_ctid 版本链管理数据更新,适合高频更新场景。深入理解两者的存储结构与字段变更逻辑,对于数据库选型及大表运维至关重要。
01核心存储架构对比
对比维度MySQL InnoDBPostgreSQL
页面大小固定 16KB默认 8KB (可调整)
页面布局Header + Records + Directory + TrailerHeader + ItemIdData[] + FreeSpace + Items[]
记录组织聚簇索引,按主键有序堆表,按插入顺序无序
主索引结构数据存储在主键 B+ 树叶子节点堆表 + 独立主键 B+ 树索引
索引叶子存储存储完整记录数据存储索引键 + CTID 指针
行标识主键值CTID (Page 号 + 偏移量)
MVCC 实现roll_pointer 指向 Undo Logt_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 Header38 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 存在锁表风险或主从延迟过高的问题。需要根据数据库类型及变更场景,选择合适的工具与策略。
  1. MySQL 策略
    优先利用 8.0 的 INSTANT 算法;若需添加索引或修改列类型,评估使用 INPLACE。对于超大表(>10GB)或必须锁表的场景,建议使用 pt-osc(基于触发器)或 gh-ost(基于 Binlog)进行无锁变更。
  2. PostgreSQL 策略
    遵循“先加后改”原则:先添加可空字段(立即完成),再分批 UPDATE 填充默认值,最后设置 NOT NULL 约束。对于需要回收空间或整理碎片的场景,使用 pg_repack 工具进行在线重建。
  3. 执行前检查
    确认磁盘空间是否充足(MySQL COPY 算法需 2x 空间),评估主从复制延迟风险,并选择业务低峰期执行。

评论

评论区空态插画

当前文章无评论,是时候发表评论了