华为云openGauss分区表设计：亿级数据查询性能优化实战

⏱ 预计阅读时间：15 分钟

🎯 背景：单表 2 亿行的痛

订单中心上线两年后，orders 表数据量突破了 2 亿行。当时的情况：

最严重的问题：索引虽然建了，但索引本身太大了（主键索引约 12GB），缓存放不下，大量走索引扫描变成了"伪全表扫描"。

🏗️ 分区方案设计

分区策略选型

最终方案：范围分区（按天） + 哈希子分区（16 个）。先按时间范围做一级裁剪，再按用户哈希做二级裁剪。

建表语句

-- 创建分区表
CREATE TABLE orders_partitioned (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    
    PRIMARY KEY (id, created_at, user_id)  -- 分区键必须包含在主键中
) PARTITION BY RANGE (created_at)
  SUBPARTITION BY HASH (user_id)
  SUBPARTITION TEMPLATE (
    SUBPARTITION p0 VALUES (0),
    SUBPARTITION p1 VALUES (1),
    SUBPARTITION p2 VALUES (2),
    SUBPARTITION p3 VALUES (3),
    SUBPARTITION p4 VALUES (4),
    SUBPARTITION p5 VALUES (5),
    SUBPARTITION p6 VALUES (6),
    SUBPARTITION p7 VALUES (7),
    SUBPARTITION p8 VALUES (8),
    SUBPARTITION p9 VALUES (9),
    SUBPARTITION p10 VALUES (10),
    SUBPARTITION p11 VALUES (11),
    SUBPARTITION p12 VALUES (12),
    SUBPARTITION p13 VALUES (13),
    SUBPARTITION p14 VALUES (14),
    SUBPARTITION p15 VALUES (15)
  )
(
    -- 按天创建分区，提前创建 60 天
    PARTITION p20250117 VALUES LESS THAN ('2025-01-18'),
    PARTITION p20250118 VALUES LESS THAN ('2025-01-19'),
    PARTITION p20250119 VALUES LESS THAN ('2025-01-20'),
    -- ... 后续分区通过脚本自动创建
    PARTITION p20250317 VALUES LESS THAN ('2025-03-18'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)  -- 兜底分区
);

分区键选择的约束

注意：openGauss 的分区表要求 主键必须包含所有分区键。这是一个常见的坑 —— 业务上 id 是唯一的，但由于分区键 created_at 和 user_id 也必须加入主键，主键变成了联合主键。

-- ❌ 这个主键不行（缺少分区键）
PRIMARY KEY (id)

-- ✅ 必须包含所有分区键
PRIMARY KEY (id, created_at, user_id)

-- 或者用唯一索引代替主键
CREATE UNIQUE INDEX idx_orders_id_unique ON orders_partitioned(id);

🔬 分区裁剪验证

分区的核心价值在于分区裁剪（Partition Pruning）—— 查询时只扫描相关分区，而不是全表。

-- 验证分区裁剪是否生效
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders_partitioned 
WHERE user_id = 12345 
  AND created_at >= '2025-03-01' 
  AND created_at < '2025-03-08';

-- 期望的执行计划（只扫描 1 天分区 × 1 个哈希子分区 = 1/32 的数据）
Append  (cost=0.00..12.34 rows=5 width=128)
  Subplans Removed: 30         -- 32 个子分区中跳过 30 个
  ->  Index Scan using orders_p20250301_p3_idx on orders_p20250301_p3
        Index Cond: (user_id = 12345)
        (actual time=0.012..0.034 rows=2 loops=1)

关键指标看 Subplans Removed—— 跳过的分区越多，裁剪越有效。

💥 踩坑：条件写法不对导致分区裁剪失效

-- ❌ 分区裁剪失效（函数包裹了分区键）
SELECT * FROM orders_partitioned 
WHERE DATE(created_at) = '2025-03-17';

-- ✅ 分区裁剪生效（直接使用分区键）
SELECT * FROM orders_partitioned 
WHERE created_at >= '2025-03-17' AND created_at < '2025-03-18';

-- ❌ 分区裁剪失效（隐式类型转换）
SELECT * FROM orders_partitioned 
WHERE created_at = '2025-03-17';  -- 字符串自动转 timestamp，但优化器不确定

-- ✅ 分区裁剪生效（显式类型）
SELECT * FROM orders_partitioned 
WHERE created_at = '2025-03-17'::timestamp;

🧊 冷热数据分离

订单数据有典型的冷热特征：3 个月内的数据被频繁访问（80% 的查询），3 个月前的数据极少访问。

分区移动 + 表空间分离

-- 创建冷数据表空间（慢速归档存储）
CREATE TABLESPACE ts_orders_cold 
OWNER omm 
LOCATION '/data/opengauss/cold_data';

-- 将 3 个月前的分区移动到冷表空间
ALTER TABLE orders_partitioned 
    MOVE PARTITION p20241017 TABLESPACE ts_orders_cold;
ALTER TABLE orders_partitioned 
    MOVE PARTITION p20241018 TABLESPACE ts_orders_cold;
-- ... 可以用脚本批量执行

冷热数据迁移脚本

#!/bin/bash
# move_cold_partitions.sh — 每月 1 号执行

PGHOST="localhost"
PGUSER="omm"
PGDB="order_db"
COLD_DATE=$(date -d "3 months ago" +%Y%m%d)

gsql -h $PGHOST -U $PGUSER -d $PGDB <<EOF
-- 查询所有分区
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) 
FROM pg_stat_user_tables 
WHERE relname LIKE 'orders_partitioned_p%'
ORDER BY relname;

-- 批量移动 3 个月前的分区到冷表空间
DO \$\$
DECLARE
    part_name TEXT;
BEGIN
    FOR part_name IN 
        SELECT relname FROM pg_stat_user_tables 
        WHERE relname LIKE 'orders_partitioned_p%'
          AND SUBSTRING(relname FROM 'p(\d{8})')::INTEGER < $COLD_DATE
    LOOP
        EXECUTE format(
            'ALTER TABLE orders_partitioned MOVE PARTITION %s TABLESPACE ts_orders_cold;',
            part_name
        );
        RAISE NOTICE 'Moved % to cold storage', part_name;
    END LOOP;
END;
\$\$;
EOF

效果

⏰ 分区自动管理

每天创建新分区，否则下个月的数据会全部落到 p_max 兜底分区，失去分区优势。

#!/bin/bash
# create_partitions.sh — 每天凌晨 1:00 通过 crontab 执行

PGHOST="localhost"
PGUSER="omm"
PGDB="order_db"

# 提前创建未来 60 天的分区
DAYS_AHEAD=60
TODAY=$(date +%Y%m%d)

gsql -h $PGHOST -U $PGUSER -d $PGDB <<EOF
DO \$\$
DECLARE
    d DATE;
    part_name TEXT;
    date_str TEXT;
    next_date_str TEXT;
    count_existing INT;
BEGIN
    FOR i IN 0..$DAYS_AHEAD LOOP
        d := CURRENT_DATE + i;
        date_str := TO_CHAR(d, 'YYYYMMDD');
        next_date_str := TO_CHAR(d + 1, 'YYYYMMDD');
        part_name := 'p' || date_str;
        
        -- 检查分区是否已存在
        SELECT COUNT(*) INTO count_existing
        FROM pg_partition
        WHERE parentid = 'orders_partitioned'::regclass
          AND relname = part_name;
        
        IF count_existing = 0 THEN
            EXECUTE format(
                'ALTER TABLE orders_partitioned SPLIT PARTITION p_max AT (
                    %L::timestamp
                ) INTO (
                    PARTITION %s,
                    PARTITION p_max
                );',
                (d + 1)::text,
                part_name
            );
            RAISE NOTICE 'Created partition: %', part_name;
        END IF;
    END LOOP;
END;
\$\$;
EOF

💥 踩坑：SPLIT PARTITION 对当前分区影响

第一次自动创建分区时，我们没想到 SPLIT PARTITION p_max 会短暂锁表。执行时正好赶上高峰期，业务侧几秒超时。

修复：把分区创建时间改到凌晨业务低谷期，并且一次性创建 60 天，不是每天只创建 1 个。

# crontab — 每天跑一次，但因为已存在的分区会被跳过，实际负载很低
0 1 * * * /opt/scripts/create_partitions.sh >> /var/log/partition.log 2>&1

📊 查询性能对比

-- 场景一：查询某用户最近 7 天的订单（最频繁的查询）
-- 非分区表
SELECT * FROM orders WHERE user_id = 12345 
  AND created_at >= now() - interval '7 days';
-- 耗时: ~1800ms (全表扫描)

-- 分区表（裁剪到 7 个分区 × 1 个子分区）
SELECT * FROM orders_partitioned WHERE user_id = 12345 
  AND created_at >= now() - interval '7 days';
-- 耗时: ~8ms

🧭 分区方案选择决策树

根据数据量、查询模式和维护策略，可以参考以下决策流程来选择最合适的分区方案：

💡 使用建议：先根据当前数据量 + 预估年增长率判断走哪条分支，再结合团队的运维自动化程度选择最终方案。混合查询场景下，范围-哈希复合分区是最推荐的一劳永逸方案，本文案例正是这种选型。

❓ 常见问题

Q1：已经存在的单表怎么转分区表？

三种方式：

-- 方式一：CREATE TABLE ... AS SELECT（适合维护窗口）
CREATE TABLE orders_new (LIKE orders INCLUDING ALL) 
PARTITION BY RANGE (created_at) ...;
INSERT INTO orders_new SELECT * FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

-- 方式二：使用 pg_repack 在线重建（零停机）
pg_repack -t orders --create-partition ...

-- 方式三：ETL 方式分批迁移（最安全）
CREATE TABLE orders_partitioned (... PARTITION BY RANGE ...);
-- 按天插入历史数据，每天一批
INSERT INTO orders_partitioned SELECT * FROM orders 
WHERE created_at >= '2025-01-17' AND created_at < '2025-01-18';

推荐方式三，风险最低。

Q2：子分区数量多少合适？

子分区数建议 = CPU 核数或 2 的幂（方便哈希分布）。我们 32 核鲲鹏选了 16 个子分区，每个分区数据均匀。不是越多越好——太多子分区会导致查询时遍历元数据开销增大。

Q3：分区表的全局索引有坑吗？

openGauss 5.0 支持分区表上的全局索引，但全局索引在分区维护（DROP/TRUNCATE）时会变为不可用：

-- TRUNCATE 分区后需要重建全局索引
ALTER TABLE orders_partitioned TRUNCATE PARTITION p20241017;
REINDEX INDEX idx_orders_user_id_global;

建议：尽量使用本地索引（分区内索引），避免维护麻烦。

Q4：分区键可以修改吗？

不可以。分区键在建表时固定。如果需要修改分区策略，只能重建表。

✅ 分区表设计检查清单

【设计阶段】
□ 分区键是否覆盖了核心查询条件（WHERE 条件前缀）？
□ 主键/唯一索引是否包含了所有分区键？
□ 子分区数是否合理（2^n，不大于 CPU 核数 × 2）？
□ 是否有 MAXVALUE 兜底分区？

【实施阶段】
□ 验证分区裁剪是否生效（EXPLAIN 看 Subplans Removed）
□ 查询条件写法是否导致裁剪失效（DATE() 包裹、隐式类型转换）
□ 冷热数据分离策略是否制定
□ 分区自动创建脚本是否就绪

【运维阶段】
□ 分区自动创建是否通过 crontab 定时执行
□ 冷数据迁移是否定期执行
□ 全局索引是否需要在分区维护后重建
□ 监控是否有数据写入 MAXVALUE 分区（意味着新分区创建失败）

📝 总结

分区表是解决亿级大表性能问题最有效的手段。我的策略很简单：

1. 热点查询必须能够被裁剪到 10 个以内分区，否则分区意义不大
2. 创建分区 + 索引，不要只创建分区，分区只是缩小了扫描范围，真正的点查还是要靠索引
3. 冷热分离 + 分区 是一对组合拳，一个管查询性能，一个管存储成本

数据量超过 5 千万行的表，如果没有分区，就该拉警报了。

下一篇文章讲 openGauss 主备高可用方案 —— Keepalived + 流式复制实现 RTO < 10 秒。

💬 互动：你们生产库上最大的表有多少行？用的是什么分区策略？欢迎评论区聊聊。