定义

堆表 (Heap Table) 是一种数据存储方式,其中数据行以无序的方式存储在数据页中,没有聚集索引(Clustered Index)来组织数据的物理顺序。新插入的行通常被放置在表的末尾或第一个有足够空间的页中。

在堆表中,数据的物理存储顺序与任何键值无关,完全取决于插入顺序和空间可用性。

详细笔记

核心原理

堆表 vs 聚集索引表

堆表 (Heap Table):

数据页分布:
Page 1: [记录5, 记录2, 记录8]   ← 无序
Page 2: [记录1, 记录9, 记录3]   ← 无序
Page 3: [记录7, 记录4, 记录6]   ← 无序

特点:
- 数据物理顺序与插入顺序相关
- 没有任何列决定存储位置
- 需要 ROWID 或 RID 定位记录

聚集索引表 (Clustered Index Table):

数据页分布(按主键排序):
Page 1: [记录1, 记录2, 记录3]   ← 有序
Page 2: [记录4, 记录5, 记录6]   ← 有序
Page 3: [记录7, 记录8, 记录9]   ← 有序

特点:
- 数据按聚集索引键排序
- 叶子节点就是数据本身
- 通过主键快速定位

不同数据库的实现

SQL Server:真正的堆表

SQL Server 支持真正的堆表(没有聚集索引的表):

-- 创建堆表(没有聚集索引)
CREATE TABLE heap_users (
  user_id INT,
  name VARCHAR(50),
  email VARCHAR(100)
);
-- 没有 PRIMARY KEY 或 CLUSTERED INDEX

-- 查看存储结构
SELECT 
  t.name AS table_name,
  i.type_desc AS index_type
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id
WHERE t.name = 'heap_users';

-- 输出:
-- +------------+------------+
-- | table_name | index_type |
-- +------------+------------+
-- | heap_users | HEAP   |  ← 堆表
-- +------------+------------+

堆表的行定位器 (RID):

RID (Row ID) 结构:
{
  file_id: 2,    -- 文件 ID
  page_id: 150,  -- 页 ID
  slot_id: 5   -- 槽位 ID
}

二级索引存储:
索引键值 + RID → 直接定位到数据行

MySQL InnoDB:不支持堆表

MySQL InnoDB 不支持真正的堆表,所有表都必须有聚集索引:

-- MySQL:即使不指定主键,也会自动创建
CREATE TABLE users (
  name VARCHAR(50),
  age INT
);

-- InnoDB 会自动创建一个隐藏的聚集索引:
-- DB_ROW_ID (6字节) + DB_TRX_ID (6字节) + DB_ROLL_PTR (7字节)

-- 查看表结构
SHOW CREATE TABLE users;

-- 实际存储:
-- 隐藏的 DB_ROW_ID 作为聚集索引
-- 数据按 DB_ROW_ID 排序存储

为什么 InnoDB 必须有聚集索引?

InnoDB 的设计理念:
1. 数据必须有序存储 → B+Tree 结构要求
2. MVCC 需要 → DB_TRX_ID 和 DB_ROLL_PTR 依赖聚集索引
3. 二级索引需要 → 通过主键回表

如果没有显式主键:
  1. 使用第一个 UNIQUE NOT NULL 索引
  2. 如果也没有,自动生成 DB_ROW_ID

PostgreSQL:堆表实现

PostgreSQL 使用堆组织表 (Heap-Organized Table):

-- PostgreSQL 默认就是堆表
CREATE TABLE users (
  user_id INT,
  name VARCHAR(50)
);

-- 数据存储:
-- - 无特定顺序
-- - 使用 TID (Tuple Identifier) 定位
-- - TID = (block_number, offset)

PostgreSQL 的 HOT (Heap-Only Tuple) 优化:

-- 更新操作可能产生新版本
UPDATE users SET name = '新名' WHERE user_id = 1;

-- 如果满足条件:
-- 1. 更新的列不在任何索引中
-- 2. 同一页有空间
-- 则创建 HOT,避免更新索引

-- 优势:
-- - 减少索引更新开销
-- - 提高更新性能

堆表的优缺点

优点

1. 插入速度快

-- 堆表插入:无需维护排序
INSERT INTO heap_table VALUES (...);

-- 操作:
-- 1. 找到有空闲空间的页
-- 2. 直接插入
-- 3. 无需调整其他记录位置

-- 性能:
-- - 无页分裂(除非页满)
-- - 无排序开销
-- - 插入速度比聚集索引表快 20-30%

2. 无页分裂 (相对较少)

聚集索引表:
  INSERT INTO orders (order_id, ...) VALUES (50, ...);
  → 需要插入到中间位置
  → 可能触发页分裂

堆表:
  INSERT INTO heap_orders VALUES (50, ...);
  → 插入到有空闲空间的页
  → 很少触发页分裂

3. 适合频繁插入的场景

-- 日志系统:大量顺序写入
CREATE TABLE logs_heap (
  log_time DATETIME,
  message TEXT
) ENGINE=MyISAM;  -- MyISAM 使用堆表

-- 优势:
-- - 插入速度快
-- - 无聚集索引维护开销

缺点

1. 查询性能差 (无聚集索引)

-- 堆表:范围查询需要全表扫描
SELECT * FROM heap_users WHERE user_id BETWEEN 100 AND 200;

-- 执行过程:
-- 1. 扫描所有数据页
-- 2. 逐行过滤 user_id
-- 3. 无法利用物理顺序优化

-- 聚集索引表:
SELECT * FROM clustered_users WHERE user_id BETWEEN 100 AND 200;
-- 1. 直接定位到 user_id=100 的页
-- 2. 顺序扫描到 user_id=200
-- 3. 利用物理顺序,只需读取相关页

-- 性能差异:
-- 堆表:扫描 10000 页
-- 聚集索引表:扫描 100 页
-- 差距:100 倍

2. 无法利用物理顺序

-- ORDER BY 查询
SELECT * FROM heap_users ORDER BY user_id;

-- 堆表:
-- 1. 扫描所有数据
-- 2. 内存排序(filesort)
-- 3. 返回结果

-- 聚集索引表:
SELECT * FROM clustered_users ORDER BY user_id;
-- 1. 从第一个页开始顺序扫描
-- 2. 天然有序,无需排序
-- 3. 返回结果

-- 性能差异:
-- 堆表:需要 filesort,O(n log n)
-- 聚集索引表:顺序扫描,O(n)

3. 二级索引回表效率低

堆表二级索引:
  索引键值 + RID(file_id, page_id, slot_id)
  
  回表过程:
  1. 通过索引找到 RID
  2. 根据 file_id 定位文件
  3. 根据 page_id 定位页
  4. 根据 slot_id 定位槽位
  5. 读取数据
  
  问题:
  - RID 是物理位置,数据移动后失效
  - 随机 I/O,无法预读
  - 缓存命中率低

聚集索引表二级索引:
  索引键值 + 主键值
  
  回表过程:
  1. 通过索引找到主键
  2. 在聚集索引中查找主键
  3. 读取数据
  
  优势:
  - 主键逻辑值,永不过期
  - B+Tree 搜索高效
  - 相邻主键可能在同一页,可预读

4. 空间碎片严重

-- 堆表删除数据
DELETE FROM heap_users WHERE user_id = 50;

-- 结果:
-- Page 2: [记录1, 空闲空间, 记录3]
--    ↑
--  留下空洞

-- 长期运行后:
-- - 大量空洞
-- - 空间利用率低
-- - 需要定期重建

-- 聚集索引表:
-- 删除后可能触发页合并
-- 自动回收空间

堆表的适用场景

适合使用堆表的场景

场景一:临时表

-- SQL Server:临时表使用堆表
CREATE TABLE #temp_results (
  id INT,
  value VARCHAR(100)
);

-- 优势:
-- - 快速插入
-- - 生命周期短,无需优化
-- - 用完即删

场景二:ETL 中间表

-- 数据仓库 ETL 过程
CREATE TABLE staging_data (
  raw_data TEXT,
  load_time DATETIME
);

-- 步骤:
-- 1. 快速加载原始数据(堆表插入快)
-- 2. 清洗转换
-- 3. 插入到目标表(有索引)
-- 4. 清空 staging 表

TRUNCATE TABLE staging_data;  -- 瞬间完成

场景三:日志归档

-- 只追加写入,很少查询
CREATE TABLE archive_logs (
  log_time DATETIME,
  level TINYINT,
  message TEXT
) ENGINE=MyISAM;  -- MyISAM 堆表

-- 优势:
-- - 插入速度快
-- - 存储空间紧凑
-- - 适合批量读取

不适合使用堆表的场景

❌ OLTP 系统

-- 电商订单系统(不适合堆表)
CREATE TABLE orders_heap (
  order_id INT,
  user_id INT,
  amount DECIMAL(10, 2)
);

-- 问题:
-- 1. 查询订单需要全表扫描
-- 2. 范围查询效率低
-- 3. 并发性能差

-- 正确做法:使用聚集索引
CREATE TABLE orders (
  order_id INT PRIMARY KEY,  -- 聚集索引
  user_id INT,
  amount DECIMAL(10, 2)
);

❌ 需要频繁范围查询

-- 时间范围查询
SELECT * FROM events_heap 
WHERE event_time BETWEEN '2024-01-01' AND '2024-01-31';

-- 堆表:全表扫描
-- 聚集索引:直接定位,范围扫描

❌ 需要 ORDER BY 优化

-- 排序查询
SELECT * FROM products_heap ORDER BY price;

-- 堆表:每次都要 filesort
-- 聚集索引:如果按 price 聚簇,无需排序

堆表 vs 聚集索引表对比

特性	堆表	聚集索引表
数据存储	无序	按聚集索引键有序
插入性能	快(无需排序)	中等(可能页分裂)
范围查询	慢(全表扫描)	快(直接定位)
ORDER BY	需要 filesort	可能利用物理顺序
二级索引回表	通过 RID,随机 I/O	通过主键,B+Tree 搜索
空间碎片	严重	较轻(页合并)
适用场景	临时表、ETL、日志	OLTP、查询密集型
代表数据库	SQL Server(可选)	MySQL InnoDB(强制)

堆表的监控与维护

SQL Server:检测堆表

-- 查找所有堆表
SELECT 
  t.name AS table_name,
  p.rows AS row_count,
  SUM(a.total_pages) * 8 AS total_space_kb,
  SUM(a.used_pages) * 8 AS used_space_kb,
  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unused_space_kb
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE i.type = 0  -- Heap
GROUP BY t.name, p.rows
ORDER BY total_space_kb DESC;

-- 输出:
-- +------------+-----------+----------------+--------------+-----------------+
-- | table_name | row_count | total_space_kb | used_space_kb| unused_space_kb |
-- +------------+-----------+----------------+--------------+-----------------+
-- | temp_data  | 1000000 | 512000   | 256000   | 256000    |
-- +------------+-----------+----------------+--------------+-----------------+
-- 未使用空间占比 50%,碎片严重!

堆表重建

-- SQL Server:重建堆表,消除碎片
ALTER TABLE heap_users REBUILD;

-- 效果:
-- 1. 重新组织数据页
-- 2. 回收空闲空间
-- 3. 压缩数据

-- 或使用
DBCC CLEANTABLE ('your_database', 'heap_users');

PostgreSQL:VACUUM

-- PostgreSQL:清理死元组
VACUUM users;

-- 完全重建(锁表)
VACUUM FULL users;

-- 或使用 pg_repack(在线重建)
pg_repack -d your_database -t users

实际案例

案例一:SQL Server 堆表性能问题

-- 问题:查询越来越慢

-- 检查表类型
SELECT 
  t.name AS table_name,
  i.type_desc AS index_type
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1)
WHERE t.name = 'orders';

-- 输出: HEAP

-- 检查碎片
SELECT 
  avg_fragmentation_in_percent,
  page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('orders'), 0, NULL, 'DETAILED');

-- 输出: 65% 碎片率

-- 解决方案:添加聚集索引
ALTER TABLE orders 
ADD CONSTRAINT PK_orders PRIMARY KEY CLUSTERED (order_id);

-- 效果:
-- - 查询性能提升 10 倍
-- - 碎片率降至 5%
-- - 空间利用率提高 40%

案例二:MySQL 隐式堆表陷阱

-- 问题:忘记设置主键,性能差

CREATE TABLE logs (
  log_time DATETIME,
  level TINYINT,
  message TEXT
);
-- 没有主键!

-- InnoDB 自动创建隐藏的 DB_ROW_ID
-- 但查询无法利用

-- 检查
SHOW CREATE TABLE logs;
-- 看不到主键

-- 解决方案:添加主键
ALTER TABLE logs ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST;

-- 效果:
-- - 查询可以使用主键索引
-- - 二级索引回表更高效
-- - 便于维护和理解

最佳实践

1. MySQL:始终显式定义主键

-- ✅ 推荐
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ...
);

-- ❌ 避免
CREATE TABLE users (
  name VARCHAR(50),
  ...
);

2. SQL Server:大多数表使用聚集索引

-- ✅ 推荐
CREATE TABLE orders (
  order_id INT PRIMARY KEY CLUSTERED,
  ...
);

-- ⚠️ 仅特殊情况使用堆表
CREATE TABLE staging_data (
  ...
);  -- ETL 临时表

3. PostgreSQL:合理使用堆表

• 默认就是堆表,无需特殊配置
• 定期 VACUUM 清理死元组
• 考虑使用 CLUSTER 命令物理排序

4. 临时表可使用堆表

• 生命周期短
• 插入频繁
• 查询简单

5. 监控堆表碎片

• 定期检查碎片率
• 超过 30% 考虑重建
• 建立自动化维护计划

关联术语

• [[聚集索引]]
• [[索引组织表]]
• [[页分裂]]
• [[碎片]]

参考资料

• Microsoft Docs: Heap Tables
• MySQL 官方文档: InnoDB Table Structures
• PostgreSQL 文档: Table Storage
• 《SQL Server 内部架构》第 3 章:堆与索引