innodb and heap table

昨日和同事探讨了一下pg与mysql，发现对于mysql的innodb存储引擎了解甚少，正好周末，深入学习了解了一下，整理一下这两天学到的东西。

概念介绍

InnoDB：MySQL 的主要存储引擎
heap table：postgres数据库P表在物理上的存储（Heap File）

数据存储方式

innodb 底层存储

innodb是一个基于聚簇索引（Clustered Index）的存储引擎。

数据存储方式

• 聚簇索引存储表数据
  • 表的主键索引就是数据本身的物理存储顺序。
  • 每个页（page）一般 16KB，页内数据按主键顺序排列。
  • 非主键列数据和行信息都在叶子节点。
• 二级索引
  • 非主键索引存储的是主键值而不是行指针。
  • 查询非主键列时，需要先通过二级索引拿到主键，再去主键聚簇索引里查数据（回表，row lookup）。

页与数据组织

• 数据按 B+ 树页组织。
• 插入/更新可能导致页分裂。

postgres heap table

数据存储方式

• 数据按插入顺序存放，没有聚簇索引。
• 行标识符 TID（tuple id） 用于指向表的页和行。
• 页（默认 8KB）内存储多行 tuple。
• 没有聚簇索引，非索引扫描可能会更慢，但插入非常快。

索引

• B+ 树、哈希、GIN、GiST 等索引都是附加结构，存储独立于表。
• 回表操作是通过 TID 定位行。

对比

特性	InnoDB	PostgreSQL 堆表
数据组织	聚簇索引（主键决定物理顺序）	堆表，顺序插入
主键访问	快（顺序扫描/范围查询）	需要 B+ 树索引或全表扫描
二级索引访问	回表（先索引后主键查行）	回表（索引 -> TID -> 行）
插入性能	如果主键顺序插入快，否则可能页分裂	快，顺序写入，几乎不分裂
更新/删除	支持 in-place 更新，但大行更新可能迁移	创建新行，旧行留存，需要 vacuum
MVCC	Undo log + hidden columns	xmin/xmax + heap tuple
表膨胀	自动管理页空间	容易膨胀，需要 vacuum
查询优化	聚簇索引优化范围查询	多依赖索引，或者全表扫描

因为pg的存储结构导致访问数据，多了许多随机io：从索引到数据文件。导致pg的查询性能不如innnodb

pg的一些优化

CLUSTER 命令（物理重排）

CLUSTER t USING idx_id;

把表物理顺序按照索引顺序重排 —— 效果类似 InnoDB 聚簇索引。
缺点：

• 是离线操作；
• 之后新插入的行会破坏顺序（除非周期性 recluster）

索引仅扫描（Index Only Scan）

如果查询的字段都在索引里且可见性检查通过（可见性 map 中标记为 all-visible），可以不访问堆表，直接从索引返回结果。

BRIN 索引

BRIN 全称是 Block Range INdex，是一种 非常轻量级的索引，设计理念与 B-Tree 不同：
不是存储每一行的索引它只存储堆表的一段范围（block range）内的最小值和最大值等摘要信息。每个 BRIN 索引条目覆盖 多个物理数据页（例如 128 个 8KB 页面 = 1MB 的行数据）。
通过这些范围信息，可以快速排除不可能匹配的块，再去 heap 查找具体 tuple。
简单理解：BRIN 是“粗粒度索引”，通过块范围（block range）而非单行建立索引
非常适合 大表 + 顺序或局部相关数据：大表 + 顺序或局部相关数据：