南大通用GBase 8c 分区表实践探秘

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

当数据库中一张表的数据量过大时，会显著影响数据的查询和操作效率。南大通用GBase8c 数据库的解决方式是通过分区表功能，将一张大表从逻辑上划分为多个较小的分区，从而避免一次性处理大量数据，显著提升数据处理和查询性能。分区表尤其适用于时序数据、日志数据、大规模业务数据等场景。

GBase 8c 数据库支持以下四种分区类型：

• 范围分区表：根据一个或多个列的取值范围划分分区，每个分区存储特定范围内的数据。适用于有明显范围特征的数据，如按时间存储的销售记录、日志数据等。
• 列表分区表：依据某个列的离散值划分分区，每个分区对应一个值列表。适用于如地区、门店、类型等具有明确枚举值的场景。
• 间隔分区表：一种特殊的范围分区，可自动根据间隔创建新分区。特别适合按时间自动扩展的场景，如按月度、年度自动分区。
• 哈希分区表：通过哈希函数将数据均匀分布到不同分区，适用于没有明显范围或列表特征，但需均匀分布数据的场景。

本文将针对这四种分区类型分别阐述其功能、效果及常用使用场景。

1. 范围分区表

范围分区表按照划分范围的方式，分为以下类别：

• VALUES LESS THAN：通过指定每个分区的上限值来定义分区范围，适用于已知明确上限的情况。
• START END：通过指定分区的起点、终点或间隔等方式灵活定义分区，支持多种组合形式。

1.1 VALUES LESS THAN范围分区

适用场景

这种分区方式特别适合以下场景：

时间序列数据，如按年、月、日的销售数据

数值范围明确的数据，如按收入区间划分的客户数据

语法格式

CREATE TABLE partition_table_name
( [column_name data_type ]
 [, ... ]
)
    PARTITION BY RANGE (partition_key) 
        (
        PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE)
        [, ... ]
        );

SQL示例

drop table if exists sales_lessthan ;
create table sales_lessthan
(
   order_no integer not null,
   goods_name varchar(30) not null,
   sales_date date not null,
   sales_volume integer,
   sales_store varchar(30)
)
partition by range(sales_date)
(
   partition s1 values less than('2021-04-01 00:00:00'),
   partition s2 values less than('2021-07-01 00:00:00'),
   partition s3 values less than('2021-10-01 00:00:00'),
   partition s4 values less than(maxvalue)
);
INSERT INTO sales_lessthan (order_no, goods_name, sales_date, sales_volume, sales_store) VALUES
(1, 'Blue T-shirt', '2021-01-15', 100, 'Fashion Store A'),
(2, 'Black Jeans', '2021-03-20', 150, 'Clothing Store B'),
(3, 'Running Shoes', '2021-05-10', 120, 'Sports Shop C'),
(4, 'Smartphone Case', '2021-08-05', 80, 'Electronics Store D'),
(5, 'Headphones', '2021-11-30', 200, 'Tech Accessories Store E'),
(6, 'Backpack', '2021-02-25', 90, 'Outdoor Gear Store F'),
(7, 'Sunglasses', '2021-06-15', 180, 'Eyewear Shop G'),
(8, 'Wrist Watch', '2021-09-20', 60, 'Watch Store H'),
(9, 'Gaming Mouse', '2021-12-10', 140, 'Gaming Store I'),
(10, 'Coffee Maker', '2021-04-05', 110, 'Home Appliances Store J');
-- 查询数据
SELECT * FROM sales_lessthan ;
-- 查询分区为 指定的值所在的分区
SELECT * FROM sales_lessthan PARTITION FOR ('2021-3-21 00:00:00');
-- 查询分区为 s4 的数据
SELECT * FROM sales_lessthan PARTITION (s4);
-- 删除分区
alter table sales_lessthan drop partition s2;
-- 分裂分区(B模式不支持):s1分割为s0,s1a;s4分割为s5,s6
ALTER TABLE sales_lessthan SPLIT PARTITION s1 AT('2021-02-01 00:00:00') INTO (PARTITION s0,PARTITION s1a);
ALTER TABLE sales_lessthan SPLIT PARTITION s4 AT('2021-11-01 00:00:00') INTO (PARTITION s5,PARTITION s6);
-- 合并分区(B模式不支持):将s1a、s3合并为s4
ALTER TABLE sales_lessthan MERGE PARTITIONS s1a,s3 INTO PARTITION s4;
-- 重命名分区:将s0改成s1
ALTER TABLE sales_lessthan rename PARTITION s0 to s1;
-- 查看分区表信息
SELECT relname, boundaries::varchar FROM pg_partition p where p.parentid='sales_lessthan'::regclass order by 1;

1.2 START END范围分区

适用场景

START END 分区方式适用于：

需要自动生成多个均匀间隔分区的场景

分区范围需要更灵活定义的场景

数据分布较为均匀的时间序列或数值数据

语法格式

START END范围分区表有多种表达方式，而且这些方式可以在一个分区表内组合使用。

方式一：START(partition_value) END (partition_value | MAXVALUE)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
 [, ... ]
)
    PARTITION BY RANGE (partition_key) 
        (
        PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
        [, ... ]
        ); 

方式二：START(partition_value)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
   [, ... ]
)
    PARTITION BY RANGE (partition_key)  
        (
        PARTITION partition_name START(partition_value) 
        [, ... ]  
        );  

方式三：END(partition_value | MAXVALUE)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
   [, ... ]
)
    PARTITION BY RANGE (partition_key)  
        (
        PARTITION partition_name END(partition_value | MAXVALUE) 
        [, ... ]  
        );  

方式四：START(partition_value) END (partition_value) EVERY (interval_value)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
   [, ... ]
)
    PARTITION BY RANGE (partition_key)  
        (
        PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
        [, ... ]  
        );

SQL示例

drop table if exists record_start_end;
create table record_start_end  
(  
   number integer,  
   name varchar(20),  
   class varchar(20), 
   classno varchar(1),
   grade integer
)  
PARTITION BY RANGE(grade)  
(
   partition nopass START(10) END(60),
   -- 每隔 10 建立一个分区,将自动作为第一个实际分区pass_1
   PARTITION pass START(60) END(90) EVERY (10),  
   PARTITION excellent START(90) END(MAXVALUE)
);
INSERT INTO record_start_end (number, name, class, classno, grade) VALUES
(1, 'Alice', 'Class A', 1, 55),
(2, 'Bob', 'Class B', 2, 35),
(3, 'Charlie', 'Class D', 4, 75),
(4, 'David', 'Class A', 1, 45),
(5, 'Eve', 'Class B', 2, 65),
(6, 'Frank', 'Class C', 3, 85),
(7, 'Grace', 'Class A', 1, 70),
(8, 'Hannah', 'Class D', 4, 80),
(9, 'Ivy', 'Class C', 3, 95),
(10, 'Jack', 'Class D', 4, 50),
(11, 'Kate', 'Class B', 2, 60),
(12, 'Leo', 'Class C', 3, 20),
(13, 'Mike', 'Class D', 4, 72),
(14, 'Nancy', 'Class B', 2, 82),
(15, 'Olivia', 'Class C', 3, 92),
(16, 'Peter', 'Class A', 1, 62),
(17, 'Queen', 'Class D', 4, 5),
(18, 'Ryan', 'Class C', 3, 100),
(19, 'Sara', 'Class A', 1, 79),
(20, 'Tom', 'Class B', 2, 89);
-- 查询数据
SELECT * FROM record_start_end ;
-- 查询分区为 指定的值所在的分区
SELECT * FROM record_start_end PARTITION FOR (45);
-- 查询分区为 pass_1 的数据
SELECT * FROM record_start_end PARTITION (pass_1);
-- 删除分区
alter table record_start_end drop partition pass_2;
-- 分裂分区(B模式不支持):nopass_1分割为nopass_1_1,nopass_1_2;s4分割为s5,s6
ALTER TABLE record_start_end SPLIT PARTITION nopass_1 AT(50) INTO (PARTITION nopass_1_1,PARTITION nopass_1_2);
ALTER TABLE record_start_end SPLIT PARTITION pass_3 AT(85) INTO (PARTITION pass_3_1,PARTITION pass_3_2);
-- 合并分区(B模式不支持):将pass_1、pass_3_1,pass_3_2合并为pass
ALTER TABLE record_start_end MERGE PARTITIONS pass_1,pass_3_1,pass_3_2 INTO PARTITION pass;
-- 重命名分区:将excellent改成great
ALTER TABLE record_start_end rename PARTITION excellent to great;
-- 查看分区表信息
SELECT relname, boundaries::varchar FROM pg_partition p where p.parentid='record_start_end'::regclass order by 1;

2. 列表分区表

适用场景

列表分区非常适合以下场景：

• 数据具有明确的类别属性，如地区、部门、状态等
• 需要按离散值快速查询和管理的场景
• 数据分布不均匀但需要按特定值组织的场景

语法格式

CREATE TABLE partition_table_name
( [column_name data_type ]
 [, ... ]
)
    PARTITION BY LIST (partition_key) 
        (
        PARTITION partition_name VALUES (list_values_clause)
        [, ... ]
        );

SQL示例

LIST 分区表不支持合并分区

drop table if exists record_list;
create table record_list  
(  
   number integer,  
   name varchar(20),  
   class varchar(20),
   classno varchar(1),
   grade integer
)    
partition by list(classno)  
(
     partition class_01 values ('1'),  
     partition class_02 values ('2'),
     partition class_03 values ('3'),
     partition class_04 values ('4')
);
INSERT INTO record_list (number, name, class, classno, grade) VALUES
(1, 'Alice', 'Class A', 1, 55),
(2, 'Bob', 'Class B', 2, 35),
(3, 'Charlie', 'Class D', 4, 75),
(4, 'David', 'Class A', 1, 45),
(5, 'Eve', 'Class B', 2, 65),
(6, 'Frank', 'Class C', 3, 85),
(7, 'Grace', 'Class A', 1, 70),
(8, 'Hannah', 'Class D', 4, 80),
(9, 'Ivy', 'Class C', 3, 95),
(10, 'Jack', 'Class D', 4, 50),
(11, 'Kate', 'Class B', 2, 60),
(12, 'Leo', 'Class C', 3, 20),
(13, 'Mike', 'Class D', 4, 72),
(14, 'Nancy', 'Class B', 2, 82),
(15, 'Olivia', 'Class C', 3, 92),
(16, 'Peter', 'Class A', 1, 62),
(17, 'Queen', 'Class D', 4, 5),
(18, 'Ryan', 'Class C', 3, 100),
(19, 'Sara', 'Class A', 1, 79),
(20, 'Tom', 'Class B', 2, 89);
-- 查询数据
SELECT * FROM record_list ;
-- 查询分区为 指定的值所在的分区
SELECT * FROM record_list PARTITION FOR (3);
-- 查询分区为 class_01 的数据
SELECT * FROM record_list PARTITION (class_01);
-- 删除分区
alter table record_list drop partition class_02;
-- 新增分区
alter table record_list add partition class_05 values ('5');
-- 重命名分区:将 class_05 改成 class_06
ALTER TABLE record_list rename PARTITION class_05 to class_06;
-- 查看分区表信息
SELECT relname, boundaries::varchar FROM pg_partition p where p.parentid='record_list'::regclass order by 1;

3. 哈希分区表

哈希分区通过哈希函数将数据均匀分布到不同分区，适用于负载均衡场景。

适用场景

哈希分区适用于：

• 需要均匀分布数据以避免热点的场景
• 没有明显范围或列表特征的数据
• 高并发插入和查询场景

语法格式

CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
    PARTITION BY HASH (partition_key) 
       (PARTITION partition_name )
       [, ... ]
   ;

SQL示例

注：HASH 分区表不支持新建/删除/合并分区

drop table if exists record_hash;
create table record_hash  
(  
   number integer,  
   name varchar(20),  
   class varchar(20),
   classno varchar(1),
   grade integer
)    
partition by hash(classno)  
( 
     partition class_01 ,
     partition class_02 ,
     partition class_03
);
INSERT INTO record_hash (number, name, class, classno, grade) VALUES
(1, 'Alice', 'Class A', 1, 55),
(2, 'Bob', 'Class B', 2, 35),
(3, 'Charlie', 'Class D', 4, 75),
(4, 'David', 'Class A', 1, 45),
(5, 'Eve', 'Class B', 2, 65),
(6, 'Frank', 'Class C', 3, 85),
(7, 'Grace', 'Class A', 1, 70),
(8, 'Hannah', 'Class D', 4, 80),
(9, 'Ivy', 'Class C', 3, 95),
(10, 'Jack', 'Class D', 4, 50),
(11, 'Kate', 'Class B', 2, 60),
(12, 'Leo', 'Class C', 3, 20),
(13, 'Mike', 'Class D', 4, 72),
(14, 'Nancy', 'Class B', 2, 82),
(15, 'Olivia', 'Class C', 3, 92),
(16, 'Peter', 'Class A', 1, 62),
(17, 'Queen', 'Class D', 4, 5),
(18, 'Ryan', 'Class C', 3, 100),
(19, 'Sara', 'Class A', 1, 79),
(20, 'Tom', 'Class B', 2, 89);
-- 查询数据
SELECT * FROM record_hash ;
-- 查询分区为 指定的值所在的分区
SELECT * FROM record_hash PARTITION FOR (1);
-- 查询分区为 class_01 的数据
SELECT * FROM record_hash PARTITION (class_03);
-- 重命名分区:将 class_05 改成 class_06
ALTER TABLE record_hash rename PARTITION class_03 to class_04;
-- 查看分区表信息
SELECT relname, boundaries::varchar FROM pg_partition p where p.parentid='record_hash'::regclass order by 1;

4. 间隔分区表

间隔分区是范围分区的扩展，支持自动创建新分区。

适用场景

间隔分区特别适合：

• 按时间自动增长的数据，如日志、监控数据
• 无法预先确定分区范围的场景
• 需要减少分区维护工作量的应用

语法格式

方式一：START(partition_value) END (partition_value | MAXVALUE)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
    PARTITION BY RANGE (partition_key) 
        (
          INTERVAL ('interval_expr') 
          PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
          [, ... ]
        ); 

方式二：START(partition_value) END (partition_value) EVERY (interval_value)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
    PARTITION BY RANGE (partition_key)  
        (
          PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
          [, ... ]  
        ); 

方式三：START(partition_value)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
    PARTITION BY RANGE (partition_key)  
        (
          INTERVAL ('interval_expr') 
          PARTITION partition_name START(partition_value) 
          [, ... ]  
        ); 

方式四：END(partition_value | MAXVALUE)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
[, ... ]
)
    PARTITION BY RANGE (partition_key)  
        INTERVAL ('interval_expr') 
        (
          PARTITION partition_name END(partition_value | MAXVALUE) 
          [, ... ]  
        );

SQL示例

drop table if exists t_auto_create_partition;
create table t_auto_create_partition (
   r_id varchar(32) not null,
   s_id varchar(200),
   r_ratio varchar(100),
   create_time timestamp without time zone default localtimestamp not null
) partition by range ("create_time") interval ('1 month') 
(
   partition p1 values less than('2020-01-01 00:00:00')
);
-- 插入测试数据
INSERT INTO t_auto_create_partition (r_id, s_id, r_ratio, create_time)
VALUES
   ('1', 's1', 'ratio1', '2020-01-01 10:00:00'),
   ('2', 's2', 'ratio2', '2021-02-01 10:00:00'),
   ('22', 's22', 'ratio2', '2021-02-03 10:00:00'),
   ('3', 's3', 'ratio3', '2022-03-01 10:00:00'),
   ('4', 's4', 'ratio4', '2023-04-01 10:00:00'),
   ('5', 's5', 'ratio5', '2024-05-01 10:00:00'),
   ('6', 's6', 'ratio6', '2025-06-01 10:00:00'),
   ('7', 's7', 'ratio7', '2020-03-06 10:00:00'),
   ('8', 's8', 'ratio8', '2019-07-04 10:00:00')
;
-- 查询数据
SELECT * FROM t_auto_create_partition ;
-- 查询分区数据
select * from t_auto_create_partition partition(p1);
select * from t_auto_create_partition partition(sys_p7);
-- 删除分区
alter table t_auto_create_partition drop partition sys_p3;
-- 分裂分区(B模式不支持): sys_p5 分割为 sys_p5_1,sys_p5_2
ALTER TABLE t_auto_create_partition SPLIT PARTITION sys_p5 AT('2024-05-15 00:00:00') INTO (PARTITION sys_p5_1,PARTITION sys_p5_2);
-- 合并分区(B模式不支持):将sys_p5_1,sys_p5_2 合并为sys_p5_3
ALTER TABLE t_auto_create_partition MERGE PARTITIONS sys_p5_1,sys_p5_2 INTO PARTITION sys_p5_3;
-- 重命名分区:将sys_p7 改成 sys_p8
ALTER TABLE t_auto_create_partition rename PARTITION sys_p7 to sys_p8;
-- 查看分区表信息
SELECT relname, boundaries::varchar FROM pg_partition p where p.parentid='t_auto_create_partition'::regclass order by 1;

5. 实际应用建议

分区键选择原则:1) 选择经常作为查询条件的列；2) 选择数据分布均匀的列；3) 避免选择频繁更新的列；4) 考虑业务数据的自然边界。

为了提升性能，本文总结了一些优化建议：

• 结合分区键建立本地索引
• 定期检查和维护分区统计信息
• 移除不再需要的过期分区
• 监控分区数据分布均匀性

原文链接：www.gbase.cn/community/p…
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。