GaussDB分区表详解（一）

01. 分区表的核心概念与价值

分区表是一种数据库设计技术，它将逻辑上的一张表在物理上分割成多个更小、更易管理的部分，每个部分称为一个分区。这种技术通过将数据分布到不同的物理存储单元，实现了对海量数据的高效管理。

分区表与普通表相比，具有三个显著优势：

• 改善查询性能：对分区对象的查询可以仅搜索与查询条件匹配的分区数据，通过分区剪枝技术大幅减少需要扫描的数据量，提高检索效率。在理想情况下，分区表可以将查询性能提升10倍以上。
• 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。这种故障隔离机制保证了数据库服务的高可用性。
• 方便维护：针对大数据量的运维操作可以按分区进行，例如数据修复、备份恢复等操作可以只针对特定分区，大幅减少维护窗口时间。

02. GaussDB分区表的类型与原理

GaussDB支持丰富的一级分区表和二级分区表，满足不同业务场景的需求。

一级分区表

• 范围分区表：将数据基于范围映射到每一个分区，分区范围由创建分区表时指定的分区键决定。这种分区方式最为常用，特别适用于时间序列数据，如按月份存储销售数据。

sql

-- 创建范围分区表示例

CREATE TABLE orders (

    order_id BIGINT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    amount DECIMAL(12,2)

)

PARTITION BY RANGE (order_date)

(

    PARTITION p202301 VALUES LESS THAN ('2023-02-01'),

    PARTITION p202302 VALUES LESS THAN ('2023-03-01'),

    PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

create table student( sno int, sname varchar(20), score int, month int, cno int);  
score表示当月月考总分, 月考总分为NULL, 说明当月缺考
create table class(cno int, cname varchar(20));

输出每月月考总分都比学号为5的同学分数高的所有学生信息

select student.* from student join (select t1.sno,count(*) as count from student t1 join (select month,nvl(score,0) score from student where sno=5) t2 on t1.month=t2.month where t1.score>t2.score group by t1.sno having count=(select count(distinct month) from student) ) stu2 on student.sno=stu2.sno;

输出每次月考缺考的学生信息，要求打印姓名、班级编号和缺考次数

select month,sname,cno,count(1) as "缺考次数" from student where score is null group by month,sname,cno;

输出每次月考都和tom同时缺考的所有学生信息，要求打印学号、姓名和月考总分

select month,cno,sname,sum(nvl(score,0)) from student where month in (select month from student where sname='Tom' and score is NULL) and score is NULL and sname != 'Tom' group by month,cno,sname;

输出全校月考中位数分数

select avg(nvl(score,0)) from (select score from student order by nvl(score,0) limit (select ceil(count(1)/2)-1 from student),case when (select count(1)%2 from student)=0 then 2 else 1  end);

统计每个班每次月考的最高分数，要求打印班级名称，考试时间和月考分数

select cname,month,max(score) from student,class where student.cno=class.cno group by cname,month order by cname,month;

输出class1班级中比class2班级每月月考最低分还低的学生信息，要求打印学号、姓名和月考总分

select s.sno,s.sname,s.score from student s join 
(select month,min(nvl(score,0)) score from student where cno=2 group by month) s1 
on s.month = s1.month where nvl(s.score,0) < s1.score and s.cno = 1;

打印月考总分平均分最高的学生信息，输出学号，姓名和月考总分平均分

select avg(nvl(score,0)) as avgscore,sno,sname from student group by sno,sname order by avgscore desc limit 1;

select t1.* from (select sno,sname,round(avg(nvl(score,0)),2) score from student group by sno,sname) t1 join (select max(score) score from (select sno,sname,round(avg(nvl(score,0)),2) score from student group by sno,sname)) t2 on t1.score=t2.score;

输出每个学生月考平均分和最高月考平均分学生之间的分数差距，打印学号、姓名、月考平均分和差距分数

select sno,sname,round(avg(nvl(score,0)),2) avgscore,(select max(avgscore) from (select round(avg(nvl(score,0)),2) avgscore from student group by sno))-avgscore from student group by sno,sname;
select sno,sname,round(avg(nvl(score,0)),2) avgscore,(select max(avgscore) from (select round(avg(nvl(score,0)),2) avgscore from student group by sno))-avgscore from student group by sno,sname;

• 间隔分区表：一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。

sql

-- 创建间隔分区表示例

CREATE TABLE sales (

    prod_id NUMBER(6),

    time_id DATE

) PARTITION BY RANGE (time_id) INTERVAL('1 day')

(

    PARTITION p1 VALUES LESS THAN ('2019-02-01 00:00:00')

);

drop table if exists student;
create table student(id int,score int,month int);

查询月考平均成绩比学生编号5的大的学生

select id,round(avg(nvl(score,0)),2) avgscore from student group by id having avgscore >(select round(avg(nvl(score,0)),2) from student where id=5);

查询每次月考成绩大于平均成绩的学生

select * from (select s1.*,avgscore from student as s1 join (select month,round(avg(nvl(score,0)),2) as avgscore from student where score group by month) as s2 on s1.month=s2.month) where score>avgscore;

查询每次平均成绩差值

select month,avgscore,avgscore-lag(avgscore) over(order by month) as diff from (select month,round(avg(nvl(score,0)),2) as avgscore from student group by month order by month);

查看所有学生每次和平均成绩差值

select *,nvl(score,0)-avgscore from (select s1.*,avgscore from student as s1 join (select month,round(avg(nvl(score,0)),2) as avgscore from student where score group by month) as s2 on s1.month=s2.month) ;

输出命令，启动事务，事务隔离级别为读已提交，只读模式

START TRANSACTION;
SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;
COMMIT;

• 列表分区表：将数据基于各个分区内包含的键值映射到每一个分区，分区包含的键值在创建分区时指定。

sql

-- 创建列表分区表示例

CREATE TABLE test_list (

    col1 int,

    col2 int

) PARTITION BY LIST(col1)

(

    PARTITION p1 VALUES (2000),

    PARTITION p2 VALUES (3000),

    PARTITION p3 VALUES (4000)

);

计算学生总成绩，并基于总成绩排序

select id,sum(math+phy+art+music) all_score from stu group by id order by all_score desc;

art和music总分排名前5的总成绩加5分，查询最终的所有学生总成绩

(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music)+5 score from stu group by id order by score desc limit 5) stu2 where stu.id=stu2.id group by stu.id)
union all
(select stu.id,sum(score+math+phy) allscore from stu,(select id,sum(art+music) score from stu group by id order by score desc offset 5) stu2 where stu.id=stu2.id group by stu.id);

• 哈希分区表：将数据通过内部哈希算法映射到每一个分区，每一个分区中存储了具有相同哈希值的记录。

sql

-- 创建哈希分区表示例

CREATE TABLE test_hash (

    col1 int,

    col2 int

) PARTITION BY HASH(col1)

(

    PARTITION p1,

    PARTITION p2

);

create table scopes(student_id int,chinese int,math int,english int,music int);

计算每个学生的chinese和math总分，以及english和music总分，要求一条SQL语句实现，不能使用临时表

select student_id,sum(chinese+math),sum(english+music) from scopes group by student_id;

目前有一张权重表（各科有不同的权重，目前权重策略有2个），请算出每个学生结合权重计算后的成绩总和。要求一条SQL语句实现，不能使用临时表。每个学生都对应两个权重成绩

select student_id,weight_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) from scopes t1,weight t2 group by student_id,weight_id order by 1,2;

结合上面的结果，将一个学生对应的两个权重成绩，合到一行，要求一条SQL语句实现，不能使用临时表

select tb1.student_id,weight_sum1,weight_sum2 from
--计算权重1
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
--计算权重2
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
--表连接
on tb1.student_id=tb2.student_id order by 1;

按照两个权重成绩之和的大小，进行从大到小排序，且生成排序序号，要求生成连续排序序号，相同的值具有相同序号。一条SQL语句实现，不能使用临时表

select tb1.student_id,weight_sum1,dense_rank() over(order by weight_sum1 desc) weight_rank1,weight_sum2,dense_rank() over(order by weight_sum2 desc) weight_sum2 from
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum1 from scopes t1,weight t2 where weight_id=1 group by student_id) tb1
join
(select student_id,sum(t1.chinese*t2.chinese + t1.math*t2.math + t1.english*t2.english + t1.music*t2.music) weight_sum2 from scopes t1,weight t2 where weight_id=2 group by student_id) tb2
on tb1.student_id=tb2.student_id;

二级分区表

GaussDB还支持功能更强大的二级分区表，由范围分区、列表分区、哈希分区任意组合而成，共九种组合方式。二级分区表适用于数据量更大、数据分布更复杂的场景。

sql

-- 创建范围+哈希复合分区表

CREATE TABLE transactions (

    tx_id UUID PRIMARY KEY,

    account_id INT,

    transaction_time TIMESTAMP,

    amount NUMERIC(15,2)

)

PARTITION BY RANGE (transaction_time)

SUBPARTITION BY HASH(account_id)

(

    PARTITION p2023q1 VALUES LESS THAN ('2023-04-01')

    (

        SUBPARTITION sp1,

        SUBPARTITION sp2

    ),

    PARTITION p2023q2 VALUES LESS THAN ('2023-07-01')

    (

        SUBPARTITION sp3,

        SUBPARTITION sp4

    )

);

查看202202班级里面语文最低分是多少，要保障走索引

--收集统计信息
analyze test;
--获取推荐索引
select *  from gs_index_advise('select min(grade) from test where kemu=''语文'' and class_id=''202202''');
--创建索引
create index index_ke_cls on test(kemu,class_id);
--查询202202班级里面语文最低分
select /*+ indexscan(index_ke_cls) */ min(grafe) from test where kemu='语文' and class_id='202202';

查202202班级同一科目成绩比202201班级最高分的同学，根据以下SQL优化重写

原SQL：select * from test where score>(select max(score) from test where class_id=202201) and class_id = '202202';
select *  from test t1 join (select min(score) as min_score from test t2 where t1.class_id=202201) t3 on t1.score<t3.min_score where t1.class_id = 202202;

03. 分区表的设计与创建实践

分区键选择原则

分区表设计的核心是分区键的选择，应遵循以下原则：

• 选择具有明显区间性的字段作为分区键，如日期、区域等字段。
• 分区键应经常作为查询条件，这样才能充分利用分区剪枝的优势。
• 范围分区和列表分区最多支持16个分区键列，而哈希分区只支持单列分区键。
• 除哈希分区外，分区键不能插入空值，除非范围分区表定义了MAXVALUE分区或列表分区表定义了DEFAULT分区。

自动扩展分区

GaussDB提供了先进的分区自动扩展功能，特别适用于无法预知所有分区范围的场景：

sql

-- 支持一级分区自动扩展的列表分区表

CREATE TABLE autolist_range (

    c1 int,

    c2 int

)

PARTITION BY LIST (c1) AUTOMATIC

SUBPARTITION BY RANGE (c2)

(

    PARTITION p1 VALUES (1, 2, 3) (

        SUBPARTITION sp11 VALUES LESS THAN (5),

        SUBPARTITION sp12 VALUES LESS THAN (10)

    )

);

查202201班级和202202班级所有人语文成绩前十的记录，第一个查询要使用union

(select * from score1 where course='chinese' order by score limit 10)
union
(select * from score2 where course='chinese' order by score limit 10);

优化

(select * from score1 where course='chinese' order by score limit 10)
union all
(select * from score2 where course='chinese' order by score limit 10);

查看两个班级相同的科目，202201班在202202班中不存在的成绩，要求使用not in

select * from score1 where score not in (select score from score2 where score1.course=score2.course);

优化

select * from score1 where not exists (select score from score2 where score1.score=score2.score and score1.course=score2.course);

查询班级202201语文成绩最高的学生，要求先创建索引，并且能保证一定会使用索引

create index chi_index on score1(course);

select s1.* from student s1,(select sid,max(score) from score1 where course='chinese') s2 where s1.sid=s2.sid;

查询202201班级的学生的成绩比202202班级的学生最高成绩还要大的学生信息，对以下给出的SQL进行改写

SQL：select stu.sid,stu.sname,sum(score) sumscore from student stu,score1 s1 where stu.sid=s1.sid group by stu.id,stu.sname having sumscore > (select max(score) from (select sum(score) score from score2 group by sid));
优化
select stu.sid,stu.sname,sum(score) sumscore from studnet stu join score1 s1 on stu.sid=s1.sid cross join (select max(score) maxscore from (select sum(score) score from score2 group by id)) group by stu.sno,stu.sname,maxscore having sumscore>maxscore;

create table student(sno varchar(20),sname varchar(50),cno int);
create table class(cno int,cname varchar(50));
create table course(courid int,courname varchar(50));
create table score(sno varchar(20),courid int,score int);

 语文平均成绩大于80的所有成绩，输出班级名，学号（或班级号），平均成绩，要求使用两个where非相关子查询

select t1.sno,cname,avg(score) from score t1,student t2,class t3 where t1.sno=t2.sno and t2.cno=t3.cno and t1.sno in (select sno from score where courid in (select courid from course where courname='语文') group by sno having avg(score)>80) and courid in (select courid from course where courname='语文') group by t1.sno,t3.cname;

在上一题基础上，使用from查询优化

select t1.sno,cname,avgscore from (select t3.sno,t3.cno,avg(score) avgscore from score t1,course t2,student t3 where t1.sno=t3.sno and t1.courid=t2.courid and courname='语文' group by t3.sno,t3.cno having avgscore>80) t1,class t2 where t1.cno=t2.cno;

在上一题基础上，使用父查询（消除子查询）

select t3.cno,t3.cname,avg(score) avgscore from score t1,student t2,class t3,course t4 where t1.sno=t2.sno and t2.cno=t3.cno and t1.courid=t4.courid and courname='语文' group by t1.sno,t3.cno,t3.cname having avgscore>80;

-- 插入数据时自动创建新分区

INSERT INTO autolist_range VALUES (9, 0);  -- 自动创建处理值9的新分区

二级分区表的列表分区自动扩展行为受AUTOMATIC关键字的位置影响，可以分别控制一级分区和二级分区的自动扩展行为。

分区约束与限制

在设计分区表时，需要注意以下约束：

• 分区数最大值为1048575个，但出于性能考虑，建议单表分区数不超过2000。
• 唯一约束和主键约束的约束键必须包含所有分区键，否则将创建GLOBAL索引而非LOCAL索引。
• 分区表使用内存大致为分区数 * 3 / 1024MB，需要确保不超过local_syscache_threshold参数的值。

04. 分区表的管理与维护操作

分区表的生命周期管理是保证系统持续高效运行的关键。

日常分区维护

• 添加新分区

sql

-- 向现有分区表添加新分区

ALTER TABLE orders

ADD PARTITION p202312 VALUES LESS THAN ('2024-01-01');

• 合并分区

sql

-- 合并相邻分区（适用于时间序列数据）

ALTER TABLE sales_data

MERGE PARTITIONS p202210, p202211;

• 删除过期分区

sql

-- 删除不再需要的历史分区

ALTER TABLE log_table

DROP PARTITION p202001;

分区数据管理

• 数据重分布

sql

-- 重分布分区数据（跨节点均衡）

ALTER TABLE user_behavior

REDISTRIBUTE PARTITION BY hash(user_id);

• 分区索引优化

sql

-- 在分区表创建复合索引

CREATE INDEX idx_orders_date_customer

ON orders (order_date, customer_id)

LOCAL TO PARTITIONS p202301, p202302;

监控与统计信息收集

• 分区级统计信息收集

sql

-- 手动更新分区统计信息（提升查询优化器准确性）

ANALYZE TABLE orders

TABLESAMPLE BERNOULLI(5)

PARTITION p202312;

• 监控分区健康状态

sql

-- 查询分区表空间分布

SELECT

    partition_name,

    tablespace_name,

    size_mb,

    row_count,

    active_writers

FROM pg_catalog.pg_partitions

WHERE tablename = 'orders';

05. 分区表的查询优化与性能调优

分区剪枝技术

分区剪枝是分区表最核心的性能优化技术，它通过跳过不满足查询条件的分区，大幅减少需要扫描的数据量。GaussDB支持三种分区策略的剪枝机制：

• 范围分区基于二分binary-search实现，复杂度为O(logN)。
• 哈希分区和列表分区基于key-partOid哈希表实现，复杂度为O(1)。

sql

-- 使用分区裁剪提示

SELECT /*+ PARTITION(p202310,p202311) */ *

FROM sales

WHERE order_date BETWEEN '2023-10-01' AND '2023-11-30';

执行计划分析

通过分析查询执行计划，可以验证分区剪枝是否生效：

sql

-- 检查执行计划中的分区裁剪

EXPLAIN ANALYZE

SELECT * FROM orders

WHERE order_date > '2023-12-01';

 

-- 确保启用分区裁剪

SET enable_partition_pruning = ON;

在执行计划中，Partition Iterator算子显示了分区剪枝的详细信息：

• Iterations：分区迭代算子对一级分区的迭代次数。
• Sub Iterations：分区迭代算子对二级分区的迭代次数。
• Selected Partitions：一级分区剪枝的结果。
• Selected Subpartitions：二级分区剪枝的结果。

典型故障排查

• 分区查询性能下降

sql

-- 检查分区数据分布是否均衡

SELECT partition_name, pg_size_pretty(size)

FROM pg_catalog.pg_partitions

WHERE tablename = 'user_logs';

 

-- 如果数据分布不均，执行数据重分布

ALTER TABLE user_logs

REDISTRIBUTE PARTITION BY HASH(user_id);

06. 企业级最佳实践与实战案例

金融交易对账场景

在金融交易场景中，可以结合范围分区和哈希分区的优势：

sql

-- 创建时间范围+哈希复合分区表

CREATE TABLE transactions (

    tx_id UUID PRIMARY KEY,

    account_id INT,

    transaction_time TIMESTAMP,

    amount NUMERIC(15,2),

    status CHAR(20)

)

PARTITION BY RANGE (transaction_time)

SUBPARTITION BY HASH(account_id)

(

    PARTITION p2023q1 VALUES LESS THAN ('2023-04-01'),

    PARTITION p2023q2 VALUES LESS THAN ('2023-07-01')

)

WITH (

    PARTITION.AUTO_EXPAND = TRUE,

    PARTITION.TOLERATE_UNBALANCE = 50

);

最佳实践：

• 启用AUTO_EXPAND自动扩展分区。
• 设置TOLERATE_UNBALANCE允许一定负载不均衡。
• 每月执行ANALYZE收集统计信息。

IoT设备监控数据管理

对于百万级设备的监控数据，哈希分区可以确保数据均匀分布：

sql

-- 创建哈希分区表处理百万级设备数据

CREATE TABLE iot_data (

    device_id VARCHAR(50),

    sensor_type INT,

    timestamp TIMESTAMP,

    value FLOAT

)

PARTITION BY HASH(device_id)

(

    PARTITION p_device_0,

    PARTITION p_device_1

);

最佳实践：

• 采用哈希分区实现设备数据均匀分布。
• 设置分区阈值告警（当某个分区大小超过GB级时触发）。
• 定期执行VACUUM回收存储空间。

07. 分区表管理命令速查

操作类型	SQL命令示例	核心参数
创建分区表	CREATE TABLE ... PARTITION BY ...	分区类型，子分区策略
添加分区	ALTER TABLE ... ADD PARTITION ...	分区界限
删除分区	ALTER TABLE ... DROP PARTITION ...	分区名称
合并分区	ALTER TABLE ... MERGE PARTITIONS ...	分区列表
数据重分布	ALTER TABLE ... REDISTRIBUTE ...	分区策略
统计信息收集	ANALYZE TABLE ...	表名，分区采样比例

结语

GaussDB的分区表功能为处理海量数据提供了强大而灵活的工具。通过合理设计分区策略、有效实施分区管理和持续优化，企业可以构建出高性能、高可用的数据存储解决方案。

关键在于根据业务特征选择合适的partitioning策略，设计自动扩展的分区生命周期管理方案，并实施细粒度的分区级性能监控与优化，从而充分发挥分布式数据库的优势，满足日益增长的数据存储与查询需求。

分区表技术不是银弹，但当你面对的是海量数据场景时，它无疑是你数据库架构工具箱中最值得深入理解和掌握的工具之一。