数据库分区设计

SunnyFan大约 15 分钟约 4357 字

数据库分区设计

简介

数据库分区（Partitioning）通过把一张大表拆分成多个逻辑分区或物理分片，来减少单次查询扫描范围、提高维护效率，并帮助管理冷热数据。它适合超大表、时间序列表、日志表和历史归档场景，但如果分区键选错、分区数量失控，反而会让查询计划更复杂、性能更差。

分区与分库分表的区别

特性	分区（Partitioning）	分库分表（Sharding）
层级	数据库引擎内部	应用层或中间件
透明性	对应用完全透明	需要应用配合
分布	同一数据库实例	多个数据库实例
扩展	受单机资源限制	可水平扩展
适合规模	单表 5000 万 - 5 亿行	单表 5 亿行以上
复杂度	低（DDL 即可）	高（中间件 + 路由）

分区策略详解

范围分区（RANGE）

范围分区是最常用的分区方式，尤其适合按时间组织的数据。

-- MySQL 范围分区
CREATE TABLE orders (
    id              BIGINT NOT NULL,
    customer_id     BIGINT NOT NULL,
    order_amount    DECIMAL(18,2) NOT NULL,
    created_at      DATETIME NOT NULL,
    status          VARCHAR(20) NOT NULL,
    PRIMARY KEY (id, created_at)  -- 分区键必须包含在主键中
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- 命中分区裁剪的查询（只扫描 2024 年 2 月的分区）
EXPLAIN PARTITIONS
SELECT *
FROM orders
WHERE created_at >= '2024-02-01'
  AND created_at <  '2024-03-01'
  AND status = 'paid';
-- partitions: p202402（只扫描一个分区）

-- 未命中分区裁剪的查询（扫描所有分区）
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE status = 'paid';
-- partitions: p202401,p202402,p202403,p202404,pmax（全部分区！）

-- 新月份到来时增加分区（避免写入 pmax）
ALTER TABLE orders
REORGANIZE PARTITION pmax INTO (
    PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 删除旧分区（比 DELETE 快 100-1000 倍）
ALTER TABLE orders DROP PARTITION p202401;
-- 直接删除文件，不需要逐行 DELETE

哈希分区（HASH）

哈希分区通过哈希函数将数据均匀分布到指定数量的分区中，适合分布均匀的数据。

-- MySQL 哈希分区
CREATE TABLE user_events (
    event_id         BIGINT NOT NULL,
    user_id          BIGINT NOT NULL,
    event_type       VARCHAR(50) NOT NULL,
    payload          JSON,
    created_at       DATETIME NOT NULL,
    PRIMARY KEY (event_id, user_id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;
-- 数据根据 user_id 的 HASH 值分配到 8 个分区
-- 适合按 user_id 查询且不需要按时间范围归档的场景

-- 增加分区（MySQL 8.0 不支持在线增加 HASH 分区，需要重建）
ALTER TABLE user_events ADD PARTITION PARTITIONS 12;

-- 哈希分区的局限：
-- 1. 不支持按范围查询裁剪
-- 2. 不支持按分区删除旧数据
-- 3. 增加分区的代价大（需要重新哈希所有数据）

列表分区（LIST）

列表分区适合枚举型数据，如区域、租户、状态码等。

-- MySQL 列表分区
CREATE TABLE sales_region (
    sale_id          BIGINT NOT NULL,
    region_code      VARCHAR(10) NOT NULL,
    amount           DECIMAL(18,2) NOT NULL,
    created_at       DATETIME NOT NULL,
    PRIMARY KEY (sale_id, region_code)
)
PARTITION BY LIST COLUMNS(region_code) (
    PARTITION p_east  VALUES IN ('SH', 'HZ', 'NJ', 'SU'),
    PARTITION p_north VALUES IN ('BJ', 'TJ', 'SJZ'),
    PARTITION p_south VALUES IN ('GZ', 'SZ', 'ZH'),
    PARTITION p_west  VALUES IN ('CD', 'CQ', 'KM'),
    PARTITION p_other VALUES IN (DEFAULT)
);

-- 列表分区适合按区域隔离查询
EXPLAIN PARTITIONS
SELECT * FROM sales_region WHERE region_code = 'SH';
-- partitions: p_east

-- 列表分区也支持 NULL 值
PARTITION BY LIST COLUMNS(status) (
    PARTITION p_active VALUES IN ('active', 'pending'),
    PARTITION p_closed VALUES IN ('closed', 'cancelled'),
    PARTITION p_null VALUES IN (NULL)
);

复合分区（子分区）

MySQL 支持在范围分区的基础上再做子分区：

-- 先按时间范围分区，再按哈希子分区
CREATE TABLE log_entries (
    id          BIGINT NOT NULL,
    app_id      VARCHAR(50) NOT NULL,
    level       VARCHAR(10) NOT NULL,
    message     TEXT,
    created_at  DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at))
SUBPARTITION BY HASH(app_id)
SUBPARTITIONS 4 (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

PostgreSQL 声明式分区

PostgreSQL 10+ 支持声明式分区，语法更简洁，功能更灵活：

-- PostgreSQL 范围分区
CREATE TABLE metrics (
    metric_id        BIGSERIAL,
    device_id        BIGINT NOT NULL,
    metric_name      TEXT NOT NULL,
    metric_value     NUMERIC(12,4) NOT NULL,
    collected_at     TIMESTAMP NOT NULL,
    PRIMARY KEY (metric_id, collected_at)
) PARTITION BY RANGE (collected_at);

-- 创建分区
CREATE TABLE metrics_2024_01 PARTITION OF metrics
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE metrics_2024_02 PARTITION OF metrics
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE metrics_2024_03 PARTITION OF metrics
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- 默认分区（PostgreSQL 11+）
CREATE TABLE metrics_default PARTITION OF metrics DEFAULT;

-- 为每个分区创建索引
CREATE INDEX idx_metrics_2024_01_device_time
ON metrics_2024_01(device_id, collected_at DESC);
CREATE INDEX idx_metrics_2024_02_device_time
ON metrics_2024_02(device_id, collected_at DESC);

-- 分离分区（归档）
ALTER TABLE metrics DETACH PARTITION metrics_2024_01;
-- 分离后 metrics_2024_01 变成普通表，可以做备份或迁移

-- 删除旧分区（比 DELETE 快得多）
DROP TABLE metrics_2024_01;

-- PostgreSQL 分区 vs MySQL 分区的差异
-- PostgreSQL 支持默认分区（DEFAULT）
-- PostgreSQL 支持分区分离（DETACH）后变普通表
-- PostgreSQL 分区间不共享主键自增序列
-- PostgreSQL 分区支持外键约束（但有限制）

分区设计决策流程

是否需要分区？
    |
    ├── 表数据量 < 1000 万行 → 不需要分区，优化索引即可
    |
    └── 表数据量 >= 1000 万行 → 继续评估
        |
        ├── 主要瓶颈是扫描范围大 → 分区可能有效
        |
        ├── 主要瓶颈是缺少索引 → 先加索引，不考虑分区
        |
        └── 需要按时间归档/清理 → 分区最有效

选择分区策略：
    |
    ├── 数据有自然时间维度 → 范围分区（按月/按日）
    |
    ├── 数据按枚举维度查询多 → 列表分区（按区域/租户）
    |
    ├── 数据分布均匀且无时间维度 → 哈希分区
    |
    └── 需要多维度 → 复合分区（先范围后哈希）

性能对比

-- 不分区 vs 分区的查询性能对比

-- 场景：orders 表有 1 亿行数据，查询 2024 年 2 月的订单

-- 不分区（全表扫描 + 索引过滤）
EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- 预估扫描 1 亿行（索引过滤后实际返回 1000 万行）
-- 耗时：约 5-10 秒

-- 按月分区（分区裁剪）
EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01';
-- 预估扫描 1000 万行（只扫描 2 月分区）
-- 耗时：约 0.5-1 秒
-- 性能提升约 10 倍

-- 删除旧数据对比
-- DELETE 方式：逐行删除，产生大量 Undo Log，锁表
DELETE FROM orders WHERE created_at < '2023-01-01';
-- 1000 万行删除可能需要 10-30 分钟

-- DROP PARTITION 方式：直接删除文件
ALTER TABLE orders DROP PARTITION p202301;
-- 耗时：毫秒级
-- 性能差距 1000-10000 倍

分区维护实战

自动化分区管理

-- MySQL 自动创建未来分区的存储过程
DELIMITER //
CREATE PROCEDURE create_monthly_partition(IN table_name VARCHAR(64))
BEGIN
    DECLARE next_month DATE;
    DECLARE partition_name VARCHAR(20);
    DECLARE partition_value INT;
    DECLARE sql_text TEXT;

    SET next_month = DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01');
    SET partition_name = CONCAT('p', DATE_FORMAT(next_month, '%Y%m'));
    SET partition_value = TO_DAYS(next_month);

    SET sql_text = CONCAT(
        'ALTER TABLE `', table_name,
        '` REORGANIZE PARTITION pmax INTO (',
        'PARTITION `', partition_name, '` VALUES LESS THAN (', partition_value, '),',
        'PARTITION pmax VALUES LESS THAN MAXVALUE)'
    );

    SET @sql = sql_text;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT CONCAT('Created partition: ', partition_name) AS result;
END //
DELIMITER ;

-- crontab 每月 25 号执行
-- 0 0 25 * * mysql -u admin -p'xxx' -e "CALL create_monthly_partition('orders')"

-- PostgreSQL 自动创建分区（pg_partman 扩展）
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.metrics',
    p_control := 'collected_at',
    p_type := 'range',
    p_interval := '1 month',
    p_premake := 3  -- 提前创建 3 个月的分区
);

-- 自动维护：pg_partman 的 bgw 维护进程自动创建和删除分区

分区数据迁移

-- MySQL 分区交换
-- 场景：将旧分区数据迁移到归档表

-- 1. 创建归档表（结构与原表相同）
CREATE TABLE orders_archive_2023 (
    LIKE orders
);

-- 2. 交换分区（元数据操作，秒级完成）
ALTER TABLE orders EXCHANGE PARTITION p202301 WITH TABLE orders_archive_2023;

-- 3. 将归档表迁移到其他存储或导出
mysqldump -u root -p your_db orders_archive_2023 > archive_2023.sql

-- 4. 删除原分区
ALTER TABLE orders DROP PARTITION p202301;

-- PostgreSQL 分区分离与归档
-- 1. 分离分区
ALTER TABLE metrics DETACH PARTITION metrics_2023_01;

-- 2. 分离后的表变成普通表，可以独立操作
VACUUM ANALYZE metrics_2023_01;

-- 3. 导出归档
pg_dump -t metrics_2023_01 your_db > metrics_2023_01.sql

-- 4. 删除归档表
DROP TABLE metrics_2023_01;

分区重建与优化

-- MySQL 分区重建（碎片整理）
ALTER TABLE orders REBUILD PARTITION p202402;

-- MySQL 分区检查
ALTER TABLE orders CHECK PARTITION p202402;

-- MySQL 分区优化
ALTER TABLE orders OPTIMIZE PARTITION p202402;

-- MySQL 分区分析（更新统计信息）
ANALYZE TABLE orders;

-- MySQL 分区修复
ALTER TABLE orders REPAIR PARTITION p202402;

-- PostgreSQL 分区 VACUUM
VACUUM ANALYZE metrics_2024_02;

-- PostgreSQL 为分区单独设置参数
ALTER TABLE metrics_2024_02 SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE metrics_2024_02 SET (autovacuum_analyze_scale_factor = 0.02);

分区与索引策略

分区表索引设计

-- MySQL 分区表索引注意事项
-- 1. 每个分区维护自己的索引（局部索引）
-- 2. 全局唯一索引不支持（MySQL 限制）

-- 正确的分区表主键设计
-- 分区键必须包含在主键中
CREATE TABLE orders (
    id          BIGINT NOT NULL,
    created_at  DATETIME NOT NULL,
    user_id     BIGINT NOT NULL,
    order_no    VARCHAR(32) NOT NULL,
    PRIMARY KEY (id, created_at)  -- 分区键 created_at 必须在主键中
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    -- 分区定义...
);

-- 为每个分区创建索引
-- MySQL 自动在每个分区上创建局部索引
CREATE INDEX idx_user_id ON orders(user_id);
-- 等价于在每个分区上分别创建 idx_user_id

-- 查看分区索引信息
SELECT
    partition_name,
    index_name,
    table_rows
FROM information_schema.partitions
WHERE table_name = 'orders';

-- PostgreSQL 分区表索引
-- PostgreSQL 支持在父表上创建索引，自动应用到所有分区
CREATE INDEX idx_metrics_device_time ON metrics(device_id, collected_at DESC);

-- 也可以为特定分区单独创建索引
CREATE INDEX idx_metrics_2024_01_name ON metrics_2024_01(metric_name);

分区裁剪验证

-- 验证分区裁剪是否生效

-- MySQL
EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
-- 检查 partitions 列：应只包含 p202402

-- 常见分区裁剪失效场景：
-- 1. 条件中使用了函数
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- 全表扫描所有分区！

-- 2. 条件中使用了 OR
EXPLAIN PARTITIONS
SELECT * FROM orders
WHERE created_at = '2024-02-01' OR user_id = 100;
-- 全表扫描所有分区！

-- 3. 隐式类型转换
EXPLAIN PARTITIONS
SELECT * FROM orders WHERE created_at = 20240201;
-- 可能全表扫描

-- 正确做法：
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- PostgreSQL 分区裁剪验证
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM metrics
WHERE collected_at >= '2024-02-01' AND collected_at < '2024-03-01';
-- 应该只扫描 metrics_2024_02 分区

分区限制与注意事项

MySQL 分区限制

-- MySQL 分区的限制清单

-- 1. 主键/唯一键必须包含分区键
-- 差：主键只有 id
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    created_at DATETIME
) PARTITION BY RANGE (TO_DAYS(created_at)) (...);
-- ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function

-- 好：主键包含分区键
CREATE TABLE orders (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (...);

-- 2. 所有分区必须使用相同的存储引擎
-- 3. 分区键必须是整数或返回整数的表达式
-- 4. 不支持外键约束（分区表不能被外键引用）
-- 5. 分区数上限：MySQL 8.0 默认 8192 个
-- 6. 不支持全文索引（MySQL 5.6 之前）
-- 7. 不支持空间索引（MySQL 5.7 之前）
-- 8. 不支持 INSERT DELAYED

-- 查看分区限制
SHOW VARIABLES LIKE 'max_partitions';

PostgreSQL 分区限制

-- PostgreSQL 分区限制

-- 1. 不支持唯一约束包含分区键以外的列
-- 差：唯一约束不包含分区键
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);
-- ERROR: unique constraint on partitioned table must include the partition key

-- 好：唯一约束包含分区键
CREATE UNIQUE INDEX uk_order_no ON orders(order_no, created_at);

-- 2. 不支持跨分区外键引用
-- 3. 分区键不能是表达式，必须是列
-- 4. 不支持在分区表上创建行级触发器（可在外部表创建）
-- 5. 不支持在分区表上创建行级安全策略（可在分区上创建）

-- PostgreSQL 11+ 改进：
-- - 支持默认分区
-- - 支持主键包含分区键
-- - 支持 ON CONFLICT（upsert）
-- - 改进分区裁剪

SQL Server 表分区

SQL Server 分区方案

-- SQL Server 分区需要先创建分区函数和分区方案

-- 1. 创建分区函数
CREATE PARTITION FUNCTION pf_orders_by_month(DATETIME)
AS RANGE RIGHT FOR VALUES (
    '2024-01-01', '2024-02-01', '2024-03-01',
    '2024-04-01', '2024-05-01', '2024-06-01'
);
-- RIGHT: 边界值属于右侧分区

-- 2. 创建分区方案
CREATE PARTITION SCHEME ps_orders_by_month
AS PARTITION pf_orders_by_month
TO (fg1, fg2, fg3, fg4, fg5, fg6, fg7);
-- fg1-fg7 可以是不同的文件组

-- 3. 创建分区表
CREATE TABLE orders (
    id          BIGINT IDENTITY(1,1),
    order_no    NVARCHAR(32) NOT NULL,
    user_id     BIGINT NOT NULL,
    amount      DECIMAL(18,2) NOT NULL,
    created_at  DATETIME NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (id, created_at)
) ON ps_orders_by_month(created_at);

-- 4. 查看分区信息
SELECT partition_id, partition_number, rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('orders');

-- 5. 添加新分区
ALTER PARTITION SCHEME ps_orders_by_month NEXT USED fg8;
ALTER PARTITION FUNCTION pf_orders_by_month()
SPLIT RANGE ('2024-07-01');

-- 6. 切换分区（归档）
CREATE TABLE orders_archive_2023 (LIKE orders) ON fg_archive;
ALTER TABLE orders SWITCH PARTITION 1 TO orders_archive_2023;

-- SQL Server 分区优势：
-- - 支持不同文件组（可放在不同磁盘上）
-- - 支持 SWITCH PARTITION 快速迁移
-- - 支持在分区上创建对齐索引
-- - 分区对应用透明

分库分表简介

什么时候需要分库分表

分区不够用时，考虑分库分表：
    |
    ├── 单表超过 5 亿行 → 考虑分表
    |
    ├── 单库 QPS 超过 5000 → 考虑分库
    |
    ├── 单库存储超过 500GB → 考虑分库
    |
    └── 需要跨机房部署 → 考虑分库

常用分片键选择：
    - 用户 ID（用户维度数据）
    - 订单 ID（订单维度数据）
    - 租户 ID（SaaS 多租户）
    - 时间（日志/指标类数据）

分片算法：
    - Hash 取模：hash(key) % N
    - 范围分片：按 ID 或时间范围
    - 一致性 Hash：减少扩容数据迁移

分库分表中间件

常见分库分表中间件：
    |
    ├── ShardingSphere（Apache）— 功能最全，Java 生态
    |   - 支持 MySQL / PostgreSQL
    |   - 支持读写分离、分库分表、数据加密
    |   - 支持 DistSQL 管理
    |
    ├── MyCat — 老牌中间件
    |   - 基于 Cobar 演进
    |   - 支持多种分片策略
    |
    ├── Vitess — YouTube 开源
    |   - 基于 Kubernetes 部署
    |   - 适合超大规模（YouTube 级别）
    |   - MySQL 协议兼容
    |
    └── 应用层分片
        - 代码中实现路由逻辑
        - 最灵活但开发成本高
        - 适合规则简单的场景

分库分表带来的挑战：
    - 跨分片 JOIN 变复杂
    - 全局唯一 ID 生成（Snowflake）
    - 分布式事务（Saga、TCC）
    - 数据迁移和扩容
    - 跨分片聚合查询
    - 全局排序和分页