数据分区 #
一、分区概述 #
1.1 分区概念 #
text
数据分区
┌─────────────────────────────────────────────────────────────┐
│ │
│ 分区作用: │
│ ├── 提高查询性能 │
│ ├── 简化数据管理 │
│ ├── 支持数据归档 │
│ └── 优化数据放置 │
│ │
│ CockroachDB 分区特点: │
│ ├── 基于Range的分区 │
│ ├── 自动分区管理 │
│ ├── 分区与Zone配置结合 │
│ └── 支持多级分区 │
│ │
└─────────────────────────────────────────────────────────────┘
1.2 分区类型 #
| 类型 | 说明 |
|---|---|
| Range分区 | 按范围分区 |
| Hash分区 | 按哈希分区 |
| List分区 | 按列表分区 (通过Range模拟) |
二、Range分区 #
2.1 创建Range分区 #
sql
-- 创建分区表
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id BIGINT,
order_no VARCHAR(50),
amount DECIMAL(10, 2),
status VARCHAR(20),
created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- 创建分区
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
-- 默认分区
CREATE TABLE orders_default PARTITION OF orders
DEFAULT;
2.2 多列Range分区 #
sql
-- 多列分区
CREATE TABLE events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_type VARCHAR(50),
event_date DATE,
data JSONB
) PARTITION BY RANGE (event_type, event_date);
-- 创建分区
CREATE TABLE events_login_2024 PARTITION OF events
FOR VALUES FROM ('login', '2024-01-01') TO ('login', '2025-01-01');
CREATE TABLE events_click_2024 PARTITION OF events
FOR VALUES FROM ('click', '2024-01-01') TO ('click', '2025-01-01');
2.3 分区管理 #
sql
-- 添加新分区
CREATE TABLE orders_2026 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
-- 删除分区 (保留数据)
ALTER TABLE orders DETACH PARTITION orders_2023;
-- 删除分区和数据
DROP TABLE orders_2023;
-- 合并分区
-- 先分离，再删除，最后重新创建
ALTER TABLE orders DETACH PARTITION orders_2023;
ALTER TABLE orders DETACH PARTITION orders_2024;
-- 创建合并后的分区
CREATE TABLE orders_2023_2024 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2025-01-01');
三、Hash分区 #
3.1 创建Hash分区 #
sql
-- 创建Hash分区表
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100),
email VARCHAR(100),
region VARCHAR(50)
) PARTITION BY HASH (id);
-- 创建Hash分区
CREATE TABLE users_p0 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users
FOR VALUES WITH (MODULUS 4, REMAINDER 3);
3.2 Hash分区特点 #
text
Hash分区特点
┌─────────────────────────────────────────────────────────────┐
│ │
│ 优点: │
│ ├── 数据均匀分布 │
│ ├── 避免热点 │
│ └── 适合等值查询 │
│ │
│ 缺点: │
│ ├── 范围查询效率低 │
│ ├── 分区调整复杂 │
│ └── 不适合时间序列数据 │
│ │
│ 适用场景: │
│ ├── 用户数据 │
│ ├── 订单数据 │
│ └── 需要均匀分布的数据 │
│ │
└─────────────────────────────────────────────────────────────┘
四、分区与Zone配置 #
4.1 分区级别Zone配置 #
sql
-- 为分区配置Zone
ALTER TABLE orders_2023 CONFIGURE ZONE USING
num_replicas = 3,
gc.ttlseconds = 2592000; -- 30天
ALTER TABLE orders_2024 CONFIGURE ZONE USING
num_replicas = 5,
gc.ttlseconds = 7776000; -- 90天
-- 为分区配置放置策略
ALTER TABLE orders_2023 CONFIGURE ZONE USING
constraints = '[+region=archive]';
ALTER TABLE orders_2024 CONFIGURE ZONE USING
constraints = '[+region=us-east]';
4.2 数据生命周期管理 #
sql
-- 创建按时间分区的表
CREATE TABLE logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
log_level VARCHAR(10),
message TEXT,
created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- 创建分区并配置不同的TTL
CREATE TABLE logs_current PARTITION OF logs
FOR VALUES FROM (NOW() - INTERVAL '7 days') TO (NOW())
WITH (ttl = '7 days');
CREATE TABLE logs_recent PARTITION OF logs
FOR VALUES FROM (NOW() - INTERVAL '30 days') TO (NOW() - INTERVAL '7 days')
WITH (ttl = '30 days');
-- 归档旧数据
CREATE TABLE logs_archive PARTITION OF logs
FOR VALUES FROM (MINVALUE) TO (NOW() - INTERVAL '30 days')
WITH (ttl = '365 days');
五、分区查询 #
5.1 分区裁剪 #
sql
-- 分区裁剪: 只扫描相关分区
-- 查询特定时间范围的数据
SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-06-30';
-- 只扫描 orders_2024 分区
-- 查看执行计划
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-06-30';
-- 查看分区信息
SELECT * FROM pg_class WHERE relname LIKE 'orders%';
5.2 分区信息查询 #
sql
-- 查看表分区
SELECT
parent.relname AS table_name,
child.relname AS partition_name,
pg_get_expr(child.relpartbound, child.oid) AS partition_expr
FROM pg_class parent
JOIN pg_inherits i ON parent.oid = i.inhparent
JOIN pg_class child ON i.inhrelid = child.oid
WHERE parent.relname = 'orders';
-- 查看分区大小
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(table_name::regclass)) AS size
FROM (
SELECT child.relname AS table_name
FROM pg_class parent
JOIN pg_inherits i ON parent.oid = i.inhparent
JOIN pg_class child ON i.inhrelid = child.oid
WHERE parent.relname = 'orders'
) t;
六、分区最佳实践 #
6.1 分区设计原则 #
text
分区设计原则
┌─────────────────────────────────────────────────────────────┐
│ │
│ 1. 选择合适的分区键 │
│ ├── 时间序列数据: 时间列 │
│ ├── 用户数据: 用户ID │
│ └── 地理数据: 区域列 │
│ │
│ 2. 分区粒度 │
│ ├── 不宜过细: 管理复杂 │
│ ├── 不宜过粗: 性能差 │
│ └── 建议: 按月或按季度 │
│ │
│ 3. 分区数量 │
│ ├── 考虑数据量 │
│ ├── 考虑查询模式 │
│ └── 建议: 单表不超过100个分区 │
│ │
│ 4. 分区维护 │
│ ├── 定期创建新分区 │
│ ├── 归档或删除旧分区 │
│ └── 监控分区大小 │
│ │
└─────────────────────────────────────────────────────────────┘
6.2 分区示例 #
sql
-- 完整的分区表设计
CREATE TABLE transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id BIGINT NOT NULL,
transaction_type VARCHAR(20),
amount DECIMAL(20, 2),
status VARCHAR(20),
created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- 创建分区函数
CREATE OR REPLACE FUNCTION create_monthly_partition(
base_table TEXT,
start_date DATE,
end_date DATE
) RETURNS VOID AS $$
DECLARE
partition_name TEXT;
BEGIN
partition_name := base_table || '_' || to_char(start_date, 'YYYY_MM');
EXECUTE format(
'CREATE TABLE %I PARTITION OF %I
FOR VALUES FROM (%L) TO (%L)',
partition_name,
base_table,
start_date,
end_date
);
END;
$$ LANGUAGE plpgsql;
-- 创建分区
SELECT create_monthly_partition('transactions', '2024-01-01', '2024-02-01');
SELECT create_monthly_partition('transactions', '2024-02-01', '2024-03-01');
SELECT create_monthly_partition('transactions', '2024-03-01', '2024-04-01');
-- 配置Zone
ALTER TABLE transactions_2024_01 CONFIGURE ZONE USING
num_replicas = 3,
gc.ttlseconds = 7776000; -- 90天
-- 创建索引
CREATE INDEX idx_transactions_user_id ON transactions (user_id, created_at);
七、分区维护 #
7.1 自动分区管理 #
sql
-- 创建自动分区管理存储过程
CREATE OR REPLACE FUNCTION manage_partitions()
RETURNS VOID AS $$
DECLARE
partition_date DATE;
partition_name TEXT;
BEGIN
-- 创建下个月的分区
partition_date := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
partition_name := 'transactions_' || to_char(partition_date, 'YYYY_MM');
IF NOT EXISTS (
SELECT 1 FROM pg_class WHERE relname = partition_name
) THEN
EXECUTE format(
'CREATE TABLE %I PARTITION OF transactions
FOR VALUES FROM (%L) TO (%L)',
partition_name,
partition_date,
partition_date + INTERVAL '1 month'
);
END IF;
-- 可以添加删除旧分区的逻辑
END;
$$ LANGUAGE plpgsql;
-- 定期执行 (可以使用cron或其他调度工具)
SELECT manage_partitions();
7.2 分区监控 #
sql
-- 监控分区大小
SELECT
schemaname,
tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE tablename LIKE 'transactions_%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
-- 监控分区数据量
SELECT
tableoid::regclass AS partition,
COUNT(*) AS row_count
FROM transactions
GROUP BY tableoid
ORDER BY partition;
八、总结 #
分区要点：
| 特性 | 说明 |
|---|---|
| Range分区 | 按范围分区 |
| Hash分区 | 按哈希分区 |
| 分区裁剪 | 优化查询性能 |
| Zone配置 | 分区级别配置 |
| 自动管理 | 定期创建和清理 |
下一步，让我们学习数据放置！
最后更新：2026-03-27