数据库容量规划

规划维度

• 存储容量：数据量、索引量、日志量
• 计算资源：CPU、内存、I/O
• 网络带宽：读写QPS、数据传输量
• 时间维度：短期(3个月)、中期(1年)、长期(3年)

业务分析框架

业务特征 → 数据模型 → 容量估算 → 资源规划 → 监控预警

容量规划实施步骤

1、业务调研

-- 1.收集业务指标
-- 当前用户量、日活跃用户
-- 核心功能使用频次
-- 数据增长趋势

-- 2.分析访问模式
-- 读写比例
-- 峰值时段分布
-- 季节性波动

-- 3.数据生命周期
-- 热数据：最近1个月，需要高性能访问
-- 温数据：1-12个月，中等性能访问
-- 冷数据：12个月以上，归档存储

2、容量计算公式

存储容量计算

-- 单表容量 = 行数 × 平均行长度 × (1 + 索引系数)
-- 数据库总容量 = Σ(单表容量) × (1 + 冗余系数)

-- 示例计算
-- 用户表：1000万行 × 300字节 × 1.3 = 3.9GB
-- 订单表：3650万行 × 200字节 × 1.3 = 9.5GB
-- 日志表：36.5亿行 × 100字节 × 1.3 = 475GB

性能容量计算

-- QPS计算
-- 读QPS = DAU × 平均查询次数 / 86400 × 峰值系数
-- 写QPS = 日写入量 / 86400 × 峰值系数

-- 硬件需求
-- CPU：每核心支持1000 QPS（简单查询）
-- 内存：buffer pool = 数据量 × 0.8
-- 磁盘：IOPS = QPS × 每查询平均IO次数

3、分层存储策略

数据分层模型

-- 第一层：内存缓存（Redis/Memcached）
-- 热点数据：用户会话、商品库存、实时统计
-- 容量：32GB，TTL：1小时

-- 第二层：SSD存储（主数据库）
-- 活跃数据：最近3个月的交易、用户信息
-- 容量：500GB，响应时间：< 10ms

-- 第三层：HDD存储（历史数据）
-- 历史数据：3个月以上的订单、日志
-- 容量：2TB，响应时间：< 100ms

-- 第四层：对象存储（归档数据）
-- 冷数据：1年以上的数据，仅用于审计
-- 容量：无限制，响应时间：秒级

数据生命周期管理

-- 自动归档策略
-- 创建归档表
CREATE TABLE orders_archive_2017 LIKE orders;

-- 定期归档任务
INSERT INTO orders_archive_2017 
SELECT * FROM orders 
WHERE created_at < '2017-01-01';

DELETE FROM orders 
WHERE created_at < '2017-01-01';

-- 分区表自动管理
ALTER TABLE orders_partitioned 
DROP PARTITION p201701;

4、监控与预警

容量监控指标

-- 存储使用率监控
SELECT 
    table_schema,
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) as size_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY size_mb DESC;

-- 增长率监控
SELECT 
    DATE(created_at) as date,
    COUNT(*) as daily_count,
    SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) as cumulative_count
FROM orders
WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY DATE(created_at);

性能监控

-- QPS监控
SELECT 
    DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') as minute,
    COUNT(*) as qps
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY minute
ORDER BY minute;

-- 慢查询监控
SELECT 
    query_time,
    lock_time,
    rows_examined,
    LEFT(sql_text, 100) as query_sample
FROM mysql.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC
LIMIT 10;

预警阈值设置

-- 容量预警
-- 存储使用率 > 80%
-- 单表数据量 > 5000万行
-- 日增长率 > 预期150%

-- 性能预警
-- 平均响应时间 > 100ms
-- QPS > 系统承载能力80%
-- 慢查询数量 > 100个/小时

5、扩容策略

垂直扩容

-- 硬件升级
-- CPU：8核 → 16核
-- 内存：32GB → 64GB
-- 磁盘：HDD → SSD → NVMe

-- 数据库配置优化
SET GLOBAL innodb_buffer_pool_size = 51539607552; -- 48GB
SET GLOBAL innodb_log_file_size = 1073741824;     -- 1GB
SET GLOBAL max_connections = 2000;

水平扩容

-- 读写分离
-- 主库：处理写操作
-- 从库：处理读操作
-- 负载均衡：根据读写比例分发

-- 分库分表
-- 用户维度：user_id % 100
-- 时间维度：按月/年分表
-- 业务维度：按地区/业务线分库

-- 示例：分表查询路由
-- 查询用户123的订单
SELECT * FROM orders_23 WHERE user_id = 123; -- 123 % 100 = 23

-- 查询2019年1月的订单
SELECT * FROM orders_201901 WHERE created_at >= '2019-01-01';

架构演进路径

单机MySQL → 主从复制 → 分库分表 → 分布式数据库 → 多活架构

6、成本优化

存储成本优化

-- 数据压缩
-- 使用压缩算法减少存储空间
ALTER TABLE orders ROW_FORMAT=COMPRESSED;
select @@innodb_default_row_format;

-- 字段优化
-- 使用合适的数据类型
-- BIGINT → INT（数据量允许时）
-- TEXT → VARCHAR(适当长度)
-- 时间戳优化：TIMESTAMP vs DATETIME

-- 索引优化
-- 删除重复索引
-- 合并相似索引
-- 使用部分索引

计算成本优化

-- 查询优化
-- 避免全表扫描
-- 使用覆盖索引
-- 合理使用缓存

-- 批处理优化
-- 批量插入代替逐条插入
-- 定时任务合并小事务
-- 异步处理非核心业务

7、常见误区

• 过度设计：初期就规划过大容量
• 忽视增长：未考虑业务发展趋势
• 单点关注：只关注存储忽视性能
• 静态规划：规划后不根据实际调整

典型业务场景分析

1、电商平台

业务特征

-- 核心业务实体
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200),
    description TEXT,
    price DECIMAL(10,2),
    images JSON,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    order_no VARCHAR(32),
    total_amount DECIMAL(10,2),
    status TINYINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    price DECIMAL(10,2)
);

容量估算模型

-- 假设业务规模：
-- 用户数：100万注册用户，10万日活
-- 商品数：50万SKU
-- 订单量：日均1万单，峰值3万单

-- 1. 存储容量估算
-- 用户表：1M用户 × 300字节/用户 = 300MB
-- 商品表：500K商品 × 2KB/商品 = 1GB
-- 订单表（年）：3.65M订单 × 200字节/订单 = 730MB
-- 订单明细（年）：7.3M明细 × 100字节/明细 = 730MB

-- 2. 索引容量估算（约为数据量的30%）
-- 总索引大小：(300MB + 1GB + 730MB + 730MB) × 0.3 = 822MB

-- 3. 日志和备份（约为数据量的50%）
-- 日志空间：2.76GB × 0.5 = 1.38GB

-- 年度存储总需求：2.76GB + 0.82GB + 1.38GB = 4.96GB ≈ 5GB

性能容量规划

-- QPS估算
-- 读操作：商品浏览、订单查询等
-- 峰值读QPS：10万DAU × 平均30次查询/天 ÷ 86400秒 × 3倍峰值 = 104 QPS

-- 写操作：下单、支付、状态更新等
-- 峰值写QPS：3万单/天 ÷ 86400秒 × 10倍峰值 = 3.5 QPS

-- 硬件资源估算
-- CPU：8核心（支持500+ QPS）
-- 内存：16GB（buffer pool 8GB + 系统开销）
-- 磁盘：SSD 100GB（含3年数据增长）
-- 网络：千兆带宽

2、社交媒体平台

业务特征

-- 用户关系网络
CREATE TABLE user_follows (
    follower_id BIGINT,
    followee_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, followee_id)
);

-- 内容发布
CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    content TEXT,
    images JSON,
    likes_count INT DEFAULT 0,
    comments_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 互动数据
CREATE TABLE post_likes (
    post_id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id)
);

CREATE TABLE comments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id BIGINT,
    user_id BIGINT,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

容量增长模型

-- 业务假设：
-- 用户：500万注册，50万日活
-- 内容：日均发布10万条动态
-- 互动：日均100万次点赞，20万条评论

-- 存储增长预测（年度）
-- 用户关系：5M用户 × 100个关注 × 16字节 = 8GB
-- 动态内容：36.5M动态 × 1KB/动态 = 36.5GB
-- 点赞数据：365M点赞 × 16字节 = 5.8GB
-- 评论数据：73M评论 × 500字节 = 36.5GB

-- 总存储（含索引）：(8 + 36.5 + 5.8 + 36.5) × 1.3 = 113GB

分库分表策略

-- 用户维度分片
CREATE TABLE posts_001 LIKE posts; -- user_id % 100 = 1
CREATE TABLE posts_002 LIKE posts; -- user_id % 100 = 2
-- ... 分100个表

-- 时间维度分片
CREATE TABLE posts_201901 LIKE posts; -- 按月分表
CREATE TABLE posts_201902 LIKE posts;

-- 分片路由示例
-- 查询用户动态：SELECT * FROM posts_001 WHERE user_id = 12345001
-- 查询最新动态：SELECT * FROM posts_201901 WHERE created_at > '2019-01-01'

3、金融交易系统 

业务特征

-- 交易流水
CREATE TABLE transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT,
    transaction_type TINYINT, -- 1:转入 2:转出 3:消费 4:退款
    amount DECIMAL(15,2),
    balance DECIMAL(15,2),
    counterparty VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 账户表
CREATE TABLE accounts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT,
    account_type TINYINT,
    balance DECIMAL(15,2),
    frozen_amount DECIMAL(15,2) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 风控记录
CREATE TABLE risk_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT,
    risk_type VARCHAR(50),
    risk_level TINYINT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

高并发容量规划

-- 业务量级：
-- 账户数：1000万
-- 日交易量：1000万笔
-- 峰值TPS：10000笔/秒

-- 存储容量估算
-- 交易流水（年）：36.5亿笔 × 200字节 = 730GB
-- 账户数据：1000万 × 100字节 = 1GB
-- 风控日志（年）：3.65亿条 × 300字节 = 109.5GB

-- 性能要求
-- 读QPS：50000（账户查询、交易查询）
-- 写QPS：10000（交易写入）
-- 响应时间：< 100ms

-- 硬件配置
-- CPU：32核心（支持高并发）
-- 内存：128GB（大内存缓存）
-- 磁盘：NVMe SSD 2TB（高IOPS）
-- 网络：10Gbps