数据库核心术语知识库

定义

行溢出 (Row Overflow) 是指当一行数据的总大小超过单个数据页的容量时,数据库引擎将部分列(通常是大字段如 TEXT、BLOB、VARCHAR(MAX)等)存储到单独的溢出页(Overflow Page)中,而在原始页中仅保留一个指针指向溢出页的机制。

行溢出是数据库处理超大行的技术手段,但会导致额外的 I/O 开销和性能下降。

详细笔记

核心原理

为什么需要行溢出?

InnoDB 默认页大小: 16KB = 16384 字节

场景:
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),  -- 约 200 字节
  content TEXT,    -- 可能 100KB+
  author VARCHAR(50)   -- 约 50 字节
);

问题:
- 一行总大小可能超过 16KB
- 无法放入单个数据页

解决方案:
- 将 content 存储到溢出页
- 原页只保留指针(20 字节)

InnoDB 的行溢出机制

InnoDB 页结构

InnoDB 16KB 页布局:
┌──────────────────────────────┐
│  File Header (38 bytes)  │
│  Page Header (56 bytes)  │
│  Infimum + Supremum (26 bytes)│
│          │
│  User Records (可变)    │  ← 数据存储区
│          │
│  Free Space (可变)    │
│          │
│  Page Directory (可变)    │
│  File Trailer (8 bytes)  │
└──────────────────────────────┘

可用空间: 约 16000 字节

行格式演进

一、REDUNDANT 和 COMPACT (旧格式)

sql

-- MySQL 5.6 及之前
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT
) ROW_FORMAT=COMPACT;

-- 存储方式:
-- 1. 前 768 字节存储在数据页
-- 2. 剩余部分存储在溢出页
-- 3. 数据页中保留 20 字节指针

示例:
content = 'ABC...'(100KB)

数据页:
[id: 4字节] [title: 200字节] [content前768字节] [指针: 20字节]
                ↓
溢出页:
[content剩余部分: 99KB+]

缺点:
- 即使只读取前 100 字节,也可能需要读溢出页
- 效率低

二、DYNAMIC (MySQL 5.7+ 默认)

sql

-- MySQL 5.7+ 默认格式
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT
) ROW_FORMAT=DYNAMIC;

-- 存储方式:
-- 1. 所有可变长度列完全存储在溢出页
-- 2. 数据页只保留 20 字节指针
-- 3. 只有访问大字段时才读溢出页

示例:
content = 'ABC...'(100KB)

数据页:
[id: 4字节] [title: 200字节] [指针: 20字节]
          ↓
溢出页:
[content完整内容: 100KB]

优点:
- 数据页更紧凑
- 不访问大字段时无额外 I/O
- 性能更好

三、COMPRESSED (压缩格式)

sql

CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT
) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- 特点:
-- 1. 数据页压缩为 8KB、4KB 或 2KB
-- 2. 更容易触发行溢出
-- 3. 节省存储空间,但 CPU 开销增加

源码分析

InnoDB 行溢出的核心逻辑:

cpp

// storage/innobase/rem/rem0rec.cc

/**
 * 判断是否需要将列存储到溢出页
 * 
 * @param col   列信息
 * @param data  列数据
 * @param length  数据长度
 * @return    是否溢出
 */
bool rec_should_store_off_page(
  const dict_col_t* col,
  const byte*   data,
  ulint     length)
{
  // 获取行格式
  row_format_t format = dict_table_get_format(col->table);
  
  if (format == ROW_FORMAT_DYNAMIC || format == ROW_FORMAT_COMPRESSED) {
    // DYNAMIC/COMPRESSED 格式:
    // 所有外部存储列(TEXT/BLOB)都溢出
    return dict_col_is_external_storage(col);
  } else {
    // COMPACT/REDUNDANT 格式:
    // 只有超过 768 字节才溢出
    return length > 768;
  }
}

/**
 * 创建溢出页并存储数据
 */
page_no_t rec_store_off_page(
  const byte* data,
  ulint   length,
  mtr_t*  mtr)
{
  // 1. 分配溢出页
  page_no_t off_page_no = fsp_alloc_free_page(mtr);
  
  // 2. 写入数据到溢出页
  page_t* off_page = buf_page_get(off_page_no, mtr);
  memcpy(page_data(off_page), data, length);
  
  // 3. 返回溢出页号
  return off_page_no;
}

检测行溢出

查看行格式

sql

-- 查看表的行格式
SELECT 
  TABLE_NAME,
  ROW_FORMAT,
  CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'articles';

-- 输出:
-- +------------+------------+------------------+
-- | TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS |
-- +------------+------------+------------------+
-- | articles | Dynamic  |      |
-- +------------+------------+------------------+

监控溢出页

sql

-- InnoDB 指标
SELECT 
  NAME,
  COUNT,
  STATUS
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%lob%' 
 OR NAME LIKE '%overflow%';

-- 输出:
-- +----------------------------------+--------+----------+
-- | NAME           | COUNT  | STATUS |
-- +----------------------------------+--------+----------+
-- | innodb_lob_index_pages     | 1234 | enabled  |
-- | innodb_lob_data_pages    | 5678 | enabled  |
-- | innodb_lob_undo_log_pages    | 100  | enabled  |
-- +----------------------------------+--------+----------+

估算溢出比例

sql

-- 查看表的数据大小
SHOW TABLE STATUS LIKE 'articles'\G

-- 输出:
-- Data_length:  16777216 (16MB,数据页)
-- Index_length: 8388608  (8MB,索引)

-- 实际数据量:
SELECT SUM(LENGTH(content)) AS total_content_size
FROM articles;
-- 结果: 536870912 (512MB)

-- 溢出比例:
-- 溢出数据 / 数据页大小 = 512MB / 16MB = 32 倍
-- 说明大部分数据在溢出页

行溢出的性能影响

负面影响

1. 额外的 I/O 操作

sql

-- 查询包含大字段的记录
SELECT id, title, content FROM articles WHERE id = 1;

-- 无溢出:
-- - 读取 1 个数据页
-- - I/O: 1 次

-- 有溢出(content 在溢出页):
-- - 读取 1 个数据页
-- - 读取 N 个溢出页
-- - I/O: 1 + N 次

-- 性能下降: N 倍

2. Buffer Pool 污染

Buffer Pool: 8GB

无溢出:
- 可缓存 50 万个数据页
- 覆盖大量记录

有溢出:
- 数据页: 10 万个
- 溢出页: 40 万个
- 溢出页占用 80% Buffer Pool
- 有效数据缓存减少

命中率下降: 50% → 20%

3. 范围查询效率低

sql

-- 范围查询
SELECT id, content FROM articles WHERE id BETWEEN 1 AND 100;

-- 每行都需要:
-- 1. 读取数据页(获取 id)
-- 2. 读取溢出页(获取 content)
-- 3. 随机 I/O,无法预读

-- 如果 content 很大:
-- - 100 行 × 10 个溢出页/行 = 1000 次 I/O
-- - 执行时间: 数秒

4. 排序和分组慢

sql

-- ORDER BY 大字段
SELECT * FROM articles ORDER BY content;

-- 问题:
-- 1. 需要读取所有溢出页
-- 2. 内存中排序困难
-- 3. 可能使用临时表

-- GROUP BY 大字段
SELECT LEFT(content, 100), COUNT(*) 
FROM articles 
GROUP BY LEFT(content, 100);

-- 同样需要读取所有溢出页

优化行溢出的策略

策略一:分离大字段到独立表

sql

-- ❌ 原始设计:大字段在主表
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,      -- 经常溢出
  author VARCHAR(50),
  create_time DATETIME
);

-- ✅ 优化设计:垂直分表
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  author VARCHAR(50),
  create_time DATETIME
);

CREATE TABLE article_contents (
  article_id INT PRIMARY KEY,
  content TEXT,
  FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 查询优化:
-- 1. 列表查询(不需要 content)
SELECT id, title, author FROM articles WHERE ...;
-- 无溢出,速度快

-- 2. 详情查询(需要 content)
SELECT a.*, c.content 
FROM articles a
JOIN article_contents c ON a.id = c.article_id
WHERE a.id = 1;
-- 按需读取溢出页

效果:

列表查询性能提升 10 倍
Buffer Pool 利用率提高
只在需要时读取大字段

策略二:使用前缀索引

sql

-- 如果需要搜索大字段
CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT
);

-- ❌ 无法直接对 TEXT 建索引
-- CREATE INDEX idx_content ON articles(content);  -- 错误!

-- ✅ 使用前缀索引
CREATE INDEX idx_content_prefix ON articles(content(100));

-- 查询:
SELECT * FROM articles 
WHERE content LIKE '关键词%';

-- 优势:
-- - 利用前缀索引过滤
-- - 减少需要读取的溢出页数量

策略三:使用 FULLTEXT 索引

sql

-- 全文搜索
CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT
);

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

-- 优势:
-- - 无需读取所有溢出页
-- - 倒排索引高效搜索
-- - 支持相关性排序

策略四:调整 innodb_default_row_format

sql

-- MySQL 5.7+
SET GLOBAL innodb_default_row_format = 'DYNAMIC';

-- 确保新表使用 DYNAMIC 格式
CREATE TABLE articles (
  id INT PRIMARY KEY,
  content TEXT
);

-- 验证
SHOW CREATE TABLE articles;
-- ROW_FORMAT=DYNAMIC ✅

策略五:压缩大字段

sql

-- 应用层压缩
INSERT INTO articles (id, content) 
VALUES (1, COMPRESS('很长的内容...'));

-- 查询时解压
SELECT id, UNCOMPRESS(content) FROM articles WHERE id = 1;

-- 或使用 MySQL 内置函数
INSERT INTO articles (id, content) 
VALUES (1, COMPRESS('很长的内容...'));

SELECT id, UNCOMPRESS(content) FROM articles;

-- 效果:
-- - 存储空间减少 60-80%
-- - 溢出页数量减少
-- - CPU 开销增加

策略六:使用外部存储

sql

-- 将大字段存储到文件系统
CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content_path VARCHAR(255)  -- 存储文件路径
);

-- 插入
INSERT INTO articles (id, title, content_path) 
VALUES (1, '标题', '/data/articles/1.txt');

-- 应用层读取文件
-- file_get_contents('/data/articles/1.txt');

-- 优势:
-- - 数据库只存路径,无溢出
-- - 文件系统更适合大文件
-- - 便于 CDN 加速

-- 缺点:
-- - 事务一致性难保证
-- - 备份复杂

不同数据库的行溢出

MySQL InnoDB

阈值:
- COMPACT: 768 字节
- DYNAMIC: 所有外部存储列

溢出页大小: 16KB(与数据页相同)

指针大小: 20 字节

PostgreSQL

TOAST (The Oversized-Attribute Storage Technique):

阈值: 2KB

策略:
1. 压缩
2. 如果仍超过 2KB,存储到 TOAST 表
3. TOAST 表单独管理

优势:
- 自动压缩
- 透明访问
- 高效的 chunk 管理

SQL Server

ROW_OVERFLOW_DATA:

阈值: 8060 字节(单页最大行大小)

策略:
1. 固定长度列必须在页内
2. 可变长度列超出时移动到 ROW_OVERFLOW 页
3. 原位置保留 24 字节指针

限制:
- 单行不能超过 8060 字节(不包括 LOB)
- TEXT/NTEXT/IMAGE 类型存储在 LOB 页

实际案例

案例一:博客系统性能优化

sql

-- 问题:文章列表查询很慢

-- 原始设计
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,      -- 平均 50KB
  summary VARCHAR(500),
  author_id INT,
  create_time DATETIME
);

-- 列表查询
SELECT id, title, summary, create_time 
FROM posts 
ORDER BY create_time DESC 
LIMIT 20;

-- 问题:
-- 1. 虽然不查 content,但行溢出导致每行占用多个页
-- 2. 扫描效率低
-- 3. Buffer Pool 被溢出页占用

-- 优化:垂直分表
CREATE TABLE posts (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  summary VARCHAR(500),
  author_id INT,
  create_time DATETIME
);

CREATE TABLE post_contents (
  post_id INT PRIMARY KEY,
  content TEXT,
  FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- 迁移数据
INSERT INTO posts (id, title, summary, author_id, create_time)
SELECT id, title, summary, author_id, create_time FROM old_posts;

INSERT INTO post_contents (post_id, content)
SELECT id, content FROM old_posts;

-- 效果:
-- - 列表查询:从 2 秒降至 50ms
-- - Buffer Pool 命中率:从 30% 提升至 85%
-- - 存储空间:减少 40%(更好的压缩)

案例二:电商商品描述

sql

-- 问题:商品详情页加载慢

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(200),
  description TEXT,    -- HTML 格式,平均 100KB
  price DECIMAL(10, 2),
  stock INT
);

-- 优化方案:
-- 1. 短描述和长描述分离
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(200),
  short_desc VARCHAR(500), -- 列表页显示
  price DECIMAL(10, 2),
  stock INT
);

CREATE TABLE product_details (
  product_id INT PRIMARY KEY,
  description TEXT,    -- 详情页显示
  specifications JSON,   -- 规格参数
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 2. 使用 CDN 存储图片
-- description 中只存图片 URL
-- 图片上传到 OSS/CDN

-- 效果:
-- - 列表页:只需读取 short_desc,无溢出
-- - 详情页:按需加载 description
-- - 页面加载时间:从 3 秒降至 500ms

最佳实践

优先使用 DYNAMIC 行格式(MySQL 5.7+)
垂直分表:将大字段分离到独立表
**避免 SELECT ***:只查询需要的列
使用前缀索引或 FULLTEXT:优化大字段搜索
考虑外部存储:超大文件存文件系统或 OSS
监控溢出比例:定期检查 innodb_lob_* 指标
合理设置页大小:大页可减少溢出,但增加内存压力

关联术语

[[页分裂]]
[[碎片]]
[[堆表]]
[[垂直分表]]

参考资料

MySQL 官方文档: InnoDB Row Formats
InnoDB 源码: storage/innobase/rem/rem0rec.cc
PostgreSQL 文档: TOAST
Microsoft Docs: Row-Overflow Data

定义 ​

详细笔记 ​

核心原理 ​

为什么需要行溢出? ​

InnoDB 的行溢出机制 ​

InnoDB 页结构 ​

行格式演进 ​

源码分析 ​

检测行溢出 ​

查看行格式 ​

监控溢出页 ​

估算溢出比例 ​

行溢出的性能影响 ​

负面影响 ​

优化行溢出的策略 ​

策略一:分离大字段到独立表 ​

策略二:使用前缀索引 ​

策略三:使用 FULLTEXT 索引 ​

策略四:调整 innodb_default_row_format ​

策略五:压缩大字段 ​

策略六:使用外部存储 ​

不同数据库的行溢出 ​

MySQL InnoDB ​

PostgreSQL ​

SQL Server ​

实际案例 ​

案例一:博客系统性能优化 ​

案例二:电商商品描述 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义