垂直分表 - Vertical Partitioning详解

定义

垂直分表 (Vertical Partitioning) 是一种数据库表设计优化技术，它将一张包含大量列的宽表，按照列的访问频率和业务关联性，拆分成多张较窄的表。拆分后的每张表都保留主键列，通过主键进行关联查询。

核心特征

特征	说明
拆分维度	按列(Column)拆分，而非按行
主键保留	所有子表都包含主键列
数据完整性	通过主键JOIN可恢复完整记录
适用场景	列数>30、列大小差异大、访问频率不均
拆分原则	常用列与非常用列分离、大字段独立存储

拆分前后对比

拆分前: 单张宽表 users (50列)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
┌──────────────────────────────────────────────────────┐
│ users 表                │
│                  │
│ 主键列: id               │
│ 常用列: username, email, phone, status (高频访问)   │
│ 不常用列: bio, address, birthday (低频访问)     │
│ 大对象列: avatar, resume, certificate (BLOB/TEXT)  │
│                  │
│ 问题:                 │
│ - 每次查询都加载50列，即使只需要3-5列       │
│ - BLOB字段占用大量Buffer Pool         │
│ - 每行记录可能超过8KB，导致页溢出       │
│ - 索引效率低下             │
└──────────────────────────────────────────────────────┘

拆分后: 三张窄表
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

表1: users_core (核心信息，高频访问)
┌─────────────────────────────────────┐
│ id (PK) | username | email | phone  │
├─────────┼──────────┼───────┼────────┤
│ 1   | alice  | a@x.c | 138..  │
│ 2   | bob  | b@x.c | 139..  │
└─────────────────────────────────────┘
• 行数相同，但每行很窄
• 单页可容纳更多记录
• Buffer Pool利用率高


表2: users_profile (扩展信息，低频访问)
┌──────────────────────────────────────────────┐
│ id (PK) | bio | address | birthday | gender  │
├─────────┼─────┼─────────┼──────────┼─────────┤
│ 1   | ... | Beijing | 1990-01  | F   │
│ 2   | ... | Shanghai| 1992-05  | M   │
└──────────────────────────────────────────────┘
• 包含不常用的文本字段
• 只在需要时才JOIN查询


表3: users_media (大对象，极少访问)
┌──────────────────────────────────────────────┐
│ id (PK) | avatar (BLOB) | resume (TEXT)  │
├─────────┼───────────────┼───────────────────┤
│ 1   | <binary>  | <long text>    │
│ 2   | <binary>  | <long text>    │
└──────────────────────────────────────────────┘
• 独立存储大字段
• 避免污染Buffer Pool
• 可通过CDN或对象存储替代

为什么需要垂直分表?

问题1: 宽表IO效率低

场景:

sql

-- 用户表有50列，但90%的查询只需要5列

-- 典型查询: 登录验证
SELECT id, username, password_hash, email, status
FROM users
WHERE username = 'alice';

-- 实际执行:
-- 1. 读取整个数据页(包含50列)
-- 2. 只使用5列，其他45列被浪费
-- 3. 如果每行1KB，一页16行，实际有效数据只有 5/50 = 10%

没有垂直分表的影响:

Buffer Pool (8GB):
┌─────────────────────────────────────┐
│ 每个数据页 16KB        │
│ 每行记录 1KB (50列)      │
│ 每页容纳 16 行         │
│             │
│ 假设表有100万行:       │
│ - 需要 62,500 页        │
│ - 实际只需5列，浪费了 90% 空间   │
│ - Buffer Pool只能缓存部分页    │
│ - 磁盘IO频繁         │
└─────────────────────────────────────┘

垂直分表后:

users_core 表 (5列):
┌─────────────────────────────────────┐
│ 每个数据页 16KB        │
│ 每行记录 100字节 (5列)     │
│ 每页容纳 160 行        │ ← 提升10倍
│             │
│ 100万行:           │
│ - 只需 6,250 页       │ ← 减少90%
│ - Buffer Pool可缓存更多热数据    │
│ - 查询性能提升 5-10 倍      │
└─────────────────────────────────────┘

问题2: 大字段影响缓存效率

场景:

sql

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),   -- 常用
  author_id INT,      -- 常用
  created_at DATETIME,    -- 常用
  content MEDIUMTEXT,   -- 非常大，平均10KB
  summary TEXT,     -- 中等，平均500字节
  tags VARCHAR(500),    -- 偶尔使用
  view_count INT      -- 常用
);

-- 列表查询(不需要content)
SELECT id, title, author_id, created_at, view_count
FROM articles
ORDER BY created_at DESC
LIMIT 20;

-- 问题: 即使不查询content字段
-- InnoDB也会将整个行加载到Buffer Pool
-- content字段占用大量缓存空间

影响分析:

单行大小估算:
- title: 50字节
- author_id: 4字节
- created_at: 8字节
- content: 10,000字节 (10KB) ⚠️
- summary: 500字节
- tags: 100字节
- view_count: 4字节
- 总计: ~10.7KB

Buffer Pool污染:
- 16KB页只能容纳 1 行记录
- content占用了 93% 的空间
- 但该字段在列表查询中根本不用
- 缓存命中率极低

问题3: 页溢出(Page Overflow)

场景:

sql

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  description TEXT,   -- 可能超过8KB
  spec_json TEXT,     -- 可能超过8KB
  images JSON     -- 可能超过8KB
);

-- InnoDB默认页大小: 16KB
-- 单行最大限制: 约8KB (页的一半)
-- 如果description + spec_json > 8KB，触发页溢出

页溢出机制:

正常页:
┌────────────────────────────────┐
│ Row1 | Row2 | Row3 | ...  │ ← 多行在一个页内
└────────────────────────────────┘

页溢出行:
┌────────────────────────────────┐
│ Row1 (含指针)       │ ← 主记录
└────────────────────────────────┘
   ↓ 指针
┌────────────────────────────────┐
│ Overflow Page (实际数据)    │ ← 额外IO
└────────────────────────────────┘

性能影响:
- 正常查询: 1次IO
- 溢出查询: 2次IO (主记录 + 溢出页)
- 如果多个字段溢出: N次IO

垂直分表设计原则

原则1: 按访问频率拆分

sql

-- 示例: 电商用户表

-- 表1: 核心表(每次请求都用)
CREATE TABLE users_core (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email VARCHAR(100),
  phone VARCHAR(20),
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  last_login_at DATETIME,
  INDEX idx_username (username),
  INDEX idx_email (email)
) ENGINE=InnoDB COMMENT='用户核心信息';

-- 表2: 资料表(个人中心使用)
CREATE TABLE users_profile (
  user_id BIGINT PRIMARY KEY,
  nickname VARCHAR(50),
  avatar_url VARCHAR(255),
  gender ENUM('M','F','O') DEFAULT 'O',
  birthday DATE,
  province VARCHAR(50),
  city VARCHAR(50),
  address_detail VARCHAR(500),
  bio TEXT,
  FOREIGN KEY (user_id) REFERENCES users_core(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户扩展资料';

-- 表3: 设置表(极少访问)
CREATE TABLE users_settings (
  user_id BIGINT PRIMARY KEY,
  notification_email BOOLEAN DEFAULT TRUE,
  notification_sms BOOLEAN DEFAULT FALSE,
  privacy_level TINYINT DEFAULT 0,
  theme VARCHAR(20) DEFAULT 'light',
  language VARCHAR(10) DEFAULT 'zh-CN',
  extra_config JSON,
  FOREIGN KEY (user_id) REFERENCES users_core(id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='用户设置';

访问模式分析:

登录接口:
  SELECT * FROM users_core WHERE username = ?
  → 只访问 core 表 ✓

列表展示:
  SELECT id, username, avatar_url FROM users_core c
  LEFT JOIN users_profile p ON c.id = p.user_id
  → 大部分场景只需 core，偶尔 JOIN profile ✓

个人中心:
  SELECT * FROM users_core WHERE id = ?
  SELECT * FROM users_profile WHERE user_id = ?
  SELECT * FROM users_settings WHERE user_id = ?
  → 分开查询，按需加载 ✓

原则2: 按业务领域拆分

sql

-- 示例: 商品表

-- 表1: 商品基本信息
CREATE TABLE products_basic (
  id BIGINT PRIMARY KEY,
  spu_name VARCHAR(200) NOT NULL,
  category_id INT NOT NULL,
  brand_id INT,
  market_price DECIMAL(10,2),
  selling_price DECIMAL(10,2),
  stock_quantity INT DEFAULT 0,
  status TINYINT DEFAULT 1,
  INDEX idx_category (category_id),
  INDEX idx_status (status)
) COMMENT='商品基本信息';

-- 表2: 商品详情(富文本)
CREATE TABLE products_detail (
  product_id BIGINT PRIMARY KEY,
  description_html MEDIUMTEXT,
  spec_params JSON,
  packaging_list TEXT,
  after_service TEXT,
  FOREIGN KEY (product_id) REFERENCES products_basic(id)
) COMMENT='商品详情描述';

-- 表3: 商品SEO信息
CREATE TABLE products_seo (
  product_id BIGINT PRIMARY KEY,
  seo_title VARCHAR(200),
  seo_keywords VARCHAR(500),
  seo_description VARCHAR(1000),
  canonical_url VARCHAR(255),
  FOREIGN KEY (product_id) REFERENCES products_basic(id)
) COMMENT='商品SEO优化';

-- 表4: 商品销售统计(更新频繁)
CREATE TABLE_products_stats (
  product_id BIGINT PRIMARY KEY,
  view_count INT DEFAULT 0,
  favorite_count INT DEFAULT 0,
  order_count INT DEFAULT 0,
  last_order_at DATETIME,
  update_time DATETIME,
  FOREIGN KEY (product_id) REFERENCES products_basic(id)
) COMMENT='商品销售统计';

拆分优势:

基本表: 稳定，很少修改结构
详情表: 大字段独立，不影响列表查询
SEO表: 营销需求，与技术数据分离
统计表: 高频更新，独立避免锁竞争

原则3: 大字段独立存储

sql

-- 判断标准: 字段平均大小 > 500字节

-- 常见的大字段类型:
-- 1. TEXT/MEDIUMTEXT/LONGTEXT
-- 2. BLOB/MEDIUMBLOB/LONGBLOB
-- 3. JSON (复杂结构)
-- 4. VARCHAR(5000+)

-- 示例: 博客文章

CREATE TABLE posts_basic (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id BIGINT NOT NULL,
  category_id INT,
  status TINYINT DEFAULT 0,
  published_at DATETIME,
  view_count INT DEFAULT 0,
  like_count INT DEFAULT 0,
  comment_count INT DEFAULT 0,
  INDEX idx_author (author_id),
  INDEX idx_published (published_at)
) COMMENT='文章基本信息';

CREATE TABLE posts_content (
  post_id BIGINT PRIMARY KEY,
  content_md MEDIUMTEXT,  -- Markdown原文
  content_html MEDIUMTEXT,  -- 渲染后的HTML
  word_count INT,
  reading_time INT,
  FOREIGN KEY (post_id) REFERENCES posts_basic(id)
) COMMENT='文章内容';

CREATE TABLE posts_attachments (
  id BIGINT PRIMARY KEY,
  post_id BIGINT NOT NULL,
  file_type ENUM('image','video','file'),
  file_url VARCHAR(500),
  file_size INT,
  sort_order INT,
  INDEX idx_post (post_id),
  FOREIGN KEY (post_id) REFERENCES posts_basic(id)
) COMMENT='文章附件';

存储优化建议:

对于超大字段(>1MB):
1. 考虑存储到对象存储(S3/OSS)
2. 数据库只存URL
3. 通过CDN加速访问

示例:
CREATE TABLE posts_media (
  post_id BIGINT,
  media_url VARCHAR(255),  -- https://cdn.example.com/xxx.jpg
  media_type ENUM('image','video'),
  thumbnail_url VARCHAR(255),
  cdn_provider VARCHAR(20)
);

实际应用案例

案例1: 社交平台用户系统

背景:

用户表从10列增长到80列
登录接口响应时间从10ms增加到200ms
Buffer Pool命中率从95%降到60%

优化方案:

sql

-- Step 1: 分析列访问频率
SELECT 
  column_name,
  access_count,
  AVG(data_length) AS avg_size
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY access_count DESC;

-- Step 2: 设计拆分方案
-- 核心表(99%请求使用)
CREATE TABLE users_core LIKE users;
ALTER TABLE users_core 
  DROP COLUMN bio,
  DROP COLUMN avatar,
  DROP COLUMN preferences,
  DROP COLUMN social_links;

-- 档案表(50%请求使用)
CREATE TABLE users_profile AS
SELECT id, bio, avatar, nickname, birthday, location
FROM users;

-- 设置表(10%请求使用)
CREATE TABLE users_settings AS
SELECT id, preferences, privacy, notifications
FROM users;

-- Step 3: 迁移数据
INSERT INTO users_core (...) SELECT ... FROM users;
INSERT INTO users_profile (...) SELECT ... FROM users;
INSERT INTO users_settings (...) SELECT ... FROM users;

-- Step 4: 添加外键
ALTER TABLE users_profile 
  ADD CONSTRAINT fk_profile_user 
  FOREIGN KEY (id) REFERENCES users_core(id);

效果对比:

指标	优化前	优化后	提升
登录响应时间	200ms	15ms	13倍
Buffer Pool命中率	60%	96%	+36%
QPS	500	5000	10倍
磁盘占用	50GB	12GB	-76%

案例2: CMS内容管理系统

问题: 文章表包含30列，列表查询慢

优化前:

sql

CREATE TABLE articles (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  slug VARCHAR(200),
  excerpt VARCHAR(500),
  content MEDIUMTEXT,    -- 平均5KB
  content_html MEDIUMTEXT, -- 平均8KB
  cover_image VARCHAR(255),
  author_id BIGINT,
  category_id INT,
  tags JSON,       -- 平均200字节
  status TINYINT,
  published_at DATETIME,
  view_count INT,
  like_count INT,
  comment_count INT,
  seo_title VARCHAR(200),
  seo_desc VARCHAR(500),
  is_featured BOOLEAN,
  is_top BOOLEAN,
  sort_order INT,
  created_at DATETIME,
  updated_at DATETIME,
  -- ... 还有10列
);

-- 列表查询
SELECT id, title, slug, excerpt, cover_image, 
   author_id, published_at, view_count
FROM articles
WHERE status = 1
ORDER BY published_at DESC
LIMIT 20;
-- 耗时: 500ms (扫描大量大字段)

优化后:

sql

-- 基础表(列表查询)
CREATE TABLE articles_base (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  slug VARCHAR(200),
  excerpt VARCHAR(500),
  cover_image VARCHAR(255),
  author_id BIGINT,
  category_id INT,
  status TINYINT,
  published_at DATETIME,
  view_count INT,
  like_count INT,
  comment_count INT,
  is_featured BOOLEAN,
  is_top BOOLEAN,
  sort_order INT,
  INDEX idx_status_pub (status, published_at),
  INDEX idx_category (category_id)
);

-- 内容表(详情页)
CREATE TABLE_articles_content (
  article_id BIGINT PRIMARY KEY,
  content_md MEDIUMTEXT,
  content_html MEDIUMTEXT,
  word_count INT,
  reading_time INT,
  toc_json JSON,
  FOREIGN KEY (article_id) REFERENCES articles_base(id)
);

-- SEO表(运营使用)
CREATE TABLE articles_seo (
  article_id BIGINT PRIMARY KEY,
  seo_title VARCHAR(200),
  seo_desc VARCHAR(500),
  seo_keywords VARCHAR(500),
  og_image VARCHAR(255),
  FOREIGN KEY (article_id) REFERENCES articles_base(id)
);

-- 元数据表(管理后台)
CREATE TABLE articles_meta (
  article_id BIGINT PRIMARY KEY,
  tags JSON,
  custom_fields JSON,
  revision_count INT,
  last_editor_id BIGINT,
  created_at DATETIME,
  updated_at DATETIME,
  FOREIGN KEY (article_id) REFERENCES articles_base(id)
);

-- 列表查询优化后
SELECT id, title, slug, excerpt, cover_image,
   author_id, published_at, view_count
FROM articles_base
WHERE status = 1
ORDER BY published_at DESC
LIMIT 20;
-- 耗时: 5ms ✓ (提升100倍)

垂直分表的缺点与注意事项

缺点1: JOIN查询复杂度增加

sql

-- 优化前: 单表查询
SELECT * FROM users WHERE id = 1;

-- 优化后: 需要JOIN或多查询
-- 方案A: JOIN
SELECT c.*, p.*, s.*
FROM users_core c
LEFT JOIN users_profile p ON c.id = p.user_id
LEFT JOIN users_settings s ON c.id = s.user_id
WHERE c.id = 1;

-- 方案B: 多次查询(推荐)
SELECT * FROM users_core WHERE id = 1;
SELECT * FROM users_profile WHERE user_id = 1;
SELECT * FROM users_settings WHERE user_id = 1;

建议:

应用层组装数据
使用缓存(Redis)减少JOIN
避免跨表事务

缺点2: 事务处理复杂

sql

-- 问题: 跨表事务需要保证一致性
START TRANSACTION;

UPDATE users_core SET status = 0 WHERE id = 1;
UPDATE users_profile SET nickname = 'new_name' WHERE user_id = 1;
UPDATE users_settings SET privacy_level = 1 WHERE user_id = 1;

COMMIT;

-- 风险: 长事务持有多个表的锁

解决方案:

python

def update_user_profile(user_id, profile_data):
  """使用分布式事务或最终一致性"""
  
  # 方案1: 本地消息表
  with transaction.atomic():
    # 更新主表
    UserCore.objects.filter(id=user_id).update(status=0)
    
    # 写入消息表
    Message.objects.create(
    type='profile_update',
    payload=json.dumps(profile_data),
    status='pending'
    )
  
  # 异步处理扩展表
  async_update_profile.delay(user_id, profile_data)
  
  # 方案2: Saga模式
  # 1. 更新core表
  # 2. 调用profile服务
  # 3. 调用settings服务
  # 4. 失败时补偿回滚

缺点3: 外键约束性能

sql

-- 外键会增加写入开销
ALTER TABLE users_profile
  ADD CONSTRAINT fk_profile_user
  FOREIGN KEY (user_id) REFERENCES users_core(id)
  ON DELETE CASCADE;

-- 每次INSERT/DELETE都要检查外键
-- 高并发场景建议去掉外键，应用层保证一致性

最佳实践

1. 拆分决策树

是否需要垂直分表?
│
├─ 列数 > 30? ────→ 是 → 考虑拆分
│
├─ 存在TEXT/BLOB字段? ──→ 是 → 独立存储
│
├─ 列访问频率差异大? ──→ 是 → 按频率拆分
│ ├─ 高频(>80%): 核心表
│ ├─ 中频(20-80%): 扩展表
│ └─ 低频(<20%): 归档表
│
├─ 单行大小 > 1KB? ──→ 是 → 大字段分离
│
└─ 以上都不满足 → 不分表

2. 命名规范

sql

-- 推荐命名方式:
{table}_core  -- 核心信息
{table}_base  -- 基础信息
{table}_profile -- 档案资料
{table}_detail  -- 详细描述
{table}_content -- 内容数据
{table}_meta  -- 元数据
{table}_stats   -- 统计数据
{table}_settings  -- 配置信息
{table}_seo   -- SEO优化
{table}_media   -- 媒体文件

3. 缓存策略

python

import redis
import json

class UserProfileService:
  def __init__(self):
    self.redis = redis.Redis()
  
  def get_full_profile(self, user_id):
    """获取完整用户资料(带缓存)"""
    
    # 1. 尝试从缓存获取
    cache_key = f"user:full:{user_id}"
    cached = self.redis.get(cache_key)
    if cached:
    return json.loads(cached)
    
    # 2. 分别查询各表
    core = self.get_user_core(user_id)
    profile = self.get_user_profile(user_id)
    settings = self.get_user_settings(user_id)
    
    # 3. 组装数据
    full_profile = {
    **core,
    **profile,
    **settings
    }
    
    # 4. 写入缓存(TTL 1小时)
    self.redis.setex(cache_key, 3600, json.dumps(full_profile))
    
    return full_profile
  
  def update_profile(self, user_id, data):
    """更新资料(删除缓存)"""
    
    # 更新数据库
    self.update_user_profile_db(user_id, data)
    
    # 删除缓存
    cache_key = f"user:full:{user_id}"
    self.redis.delete(cache_key)

参考资料

MySQL官方文档

技术文章

Percona: Vertical partitioning best practices
Facebook: Scaling user profile database
美团技术团队: 数据库垂直分表实践

版本历史:

2026-04-12: 初始版本，全面讲解垂直分表原理与实践

垂直分表 - Vertical Partitioning详解 ​

定义 ​

核心特征 ​

拆分前后对比 ​

为什么需要垂直分表? ​

问题1: 宽表IO效率低 ​

问题2: 大字段影响缓存效率 ​

问题3: 页溢出(Page Overflow) ​

垂直分表设计原则 ​

原则1: 按访问频率拆分 ​

原则2: 按业务领域拆分 ​

原则3: 大字段独立存储 ​

实际应用案例 ​

案例1: 社交平台用户系统 ​

案例2: CMS内容管理系统 ​

垂直分表的缺点与注意事项 ​

缺点1: JOIN查询复杂度增加 ​

缺点2: 事务处理复杂 ​

缺点3: 外键约束性能 ​

最佳实践 ​

1. 拆分决策树 ​

2. 命名规范 ​

3. 缓存策略 ​

参考资料 ​

MySQL官方文档 ​

相关术语 ​

技术文章 ​