PostgreSQL（二十六）分区表管理

一、分区表特点

1、概念：

2、好处：

3、特点：

二、范围分区介绍

1、简介

2、范围分区实验：

三、list分区介绍

1、简介

2、list分区表实验

四、hash分区介绍

1、简介

2、hash分区表实验

五、混合分区介绍

1、简介

2、混合分区表实验

六、总结

一、分区表特点

1、概念：

分区表的核心是分而治之。将表数据分成更小的物理分片，减少搜索范围，以此可以查询提高性能。

分区表是关系型数据库中比较常见的对大表的优化方式，数据库管理系统一般都提供了分区管理，而业务可以直接访问分区表而不需要调整业务架构，当然好的性能需要合理的分区访问方式。

2、好处：

（1）改善查询性能

（2）增强可用性：单个小分区表损坏，不影响其他分区表的使用

（3）维护方便

（4）均衡I/O：PG的一个表只能放在一个表空间下，一个表空间只能在一个磁盘上。但是分区表是很多表，可以存储到不同的磁盘上，以达到均衡I/O的目的

3、特点：

（1）pg数据库表分区表的结构：

由主表（父表）与分区表（子表）组成。

主表是创建子表的模板，它是一个正常的普通表，正常情况下它并不储存任何数据；

分区表继承并属于一个主表，分区表中存储所有的数据；

主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表。

（2）官方声明的分区实现方式：

a.继承分区：PG10版本以前都是用的继承分区（后续版本也可使用），但是管理相对麻烦。创建主表、分区表之后，还要创建存储过程、触发器等。继承分区的数据存储是依靠触发器，来把数据分发到不同的分区表内。

b.声明式分区：也叫原生分区，从PG10版本开始支持，相当于"官方支持"的分区表，也是最为推荐的分区方式。虽然与继承分区不一样，但是其内部也是用继承表实现的，只是不需要用户手动干预，使用起来更方便。

声明式分区支持：范围分区，list分区，hash分区。

本文章主要介绍这个分区方式。

c.第三方分区管理方式，比如pathman扩展等。

pathman可以让分区表的管理更丝滑。例如：新插入的数据，其值不在分区表范围内时，会插入失败。但是使用pathman，会自动创建一个default分区，将不满足分区范围的数据插入到default分区中，避免插入失败。

二、范围分区介绍

1、简介

范围分区表一般指的一个分区的范围，然后把满足条件的行存放在该分区中。

最常见的是以日期做为分区条件，根据时间段分为不同的分区，存放不同时间段的数据。

2、范围分区实验：

# 创建主表
create table part_range(
order_id int,
name varchar(50)null,
saledate timestamp not null default now())
partition by range(saledate) ;# 在主表上创建一个主键约束后，子表上就会自动创建一样的主键和索引
alter table part_range add primary key(order_id,saledate);\d+ part_range# 创建分区表（子表）
CREATE table p1_202401    //子表名字
PARTITION OF part_range     //基于（继承）哪个主表
FOR values FROM ('2024-01-01 00:00:00') TO ('2024-02-01 00:00:00');    //分区范围create table p1_202402 partition of part_range for values from ('2024-02-01 00:00:00') to ('2024-01 00:00:00:00');
create table p1_202403 partition of part_range for values from ('2024-03-01 00:00:00') to ('2024-04-01 00:00:00');
create table p1_202404 partition of part_range for values from ('2024-04-01 00:00:00') to ('2024-05-01 00:00:00');
create table p1_202405 partition of part_range for values from ('2024-05-01 00:00:00') to ('2024-06-01 00:00:00');
create table p1_202406 partition of part_range for values from ('2024-06-01 00:00:00') to ('2024-07-01 00:00:00');# 创建默认分区表，这样有不属于上面分区表的数据插入之后，就会进到默认表中
CREATE TABLE pn_default PARTITION OF part_range DEFAULT;     # 插入数据
insert into part_range select random()*10000,md5(g::text),g
from generate_series('2024-01-01'::date,'2024-06-30'::date,'1 minute') as g;# 查看数据
select tableoid::regclass,count(*)
from part_range 
group by tableoid::regclass;select tableoid::regclass,* from part_range limit 10;

Tip：在某些情况下，需要知道特定行来自哪个表，每个表中都有一个名为tableoid的系统列，使用regclass别名类型，它将象征性地打印表oid，可以列出行的原始表。

# 通过主表访问分区表（强烈建议条件列是分区列，否则分区表无意义，数据库会把所有分区表都遍历一遍，大大降低性能）explain select * from part_range where saledate='2024-05-02';# 通过子表访问（通过子表访问时，如果访问列不是分区列，但是索引列，也可以使用索引来提高一部分性能）
explain select * from p1_202401 where order_id=100;
explain select from p1_202401 where order_id=100;

三、list分区介绍

1、简介

list分区以指定的分区值将数据存放到对应的分区上，然后把满足条件的行存放在该分区中，最常见的是以某列值为分区条件，根据不同的列值存放在不同的分区。

2、list分区表实验

# 创建主表
create table part_list(
city_id int not null,
name varchar(30),
population int)
partition by list(name) ;create index part_list_idx on part_list (name);    //list:分区方式；name:分区列\d+ part_list# 创建分区表
create table p1_list partition of part_list for values in ('fujian','zhejiang');
create table p2_list partition of part_list for values in ('shandong','jiangxi');# 插入数据
insert into part_list(city_id,name,population) values(1,'fujian',10);
insert into part_list(city_id,name,population) values(2,'zhejiang',20);
insert into part_list(city_id,name,population) values(3,'shandong',10);
insert into part_list(city_id,name,population) values(4,'jiangxi',30);# 查看数据
select tableoid::regclass,* from part_list;# 查看执行计划
explain select * from part_list where name='fujian';

四、hash分区介绍

1、简介

hash分区将数据散列存储在各个分区上，以打散热点数据存放到对应的分区上，然后把满足条件的行存放在该分区中，最常见的是平均的把数据放在不同的分区。

2、hash分区表实验

# 创建主表
create table part_hash
(order_id int,
name varchar(10))
partition by hash (order_id);create index part_hash_idx on part_hash (order_id);\d+ part_hash# 创建子表
create table p1_hash partition of part_hash for values with (modulus 3,remainder 0 );   //modules:除数；remainder:取模（余数）create table p2_hash partition of part_hash for values with (modulus 3,remainder 1);
create table p3_hash partition of part_hash for values with (modulus 3,remainder 2);# 插入数据
insert into part_hash values(generate_series(1,10000),'a');# 查询数据
select tableoid::regclass,count(1) from part_hash group by tableoid::regclass;# 查看执行计划
explain select * from part_hash where order_id=1000;

五、混合分区介绍

1、简介

PG分区下面也可以建立子分区构成联级模式，子分区可以有不同的分区方式，这样的分区成为混合分区。

即：主表 --> 子表（分区表）--> 子分区的联级关系。

当分区表的数据倾斜比较大时，就可以通过给大数据分区表创建子分区的方式平衡倾斜。

子分区的分区列可以和分区表的分区列一样，也可以不一样，在主表中指定子表的分区列，在子表中指定子分区的分区列。

例如下面这个例子，分区表以sale_date分区，子分区以category：

2、混合分区表实验

1、创建主表
create table part_hunhe(
id int not null,
name varchar(20),
saledate timestamp)
partition by range(saledate) ;    //在主表指定，分区表使用哪一列进行分区。同样的，应该在分区表指定，子分区表使用哪一列进行分区。\d+ part_hunhe# 创建分区表
create table part_2001 partition of part_hunhe for values from ('2023-01-01 00:00:00') to ('2023-02-01 00:00:00')
partition by list(name);     //在分区表创建时，指定子分区使用什么列进行分区create table part_2002 partition of part_hunhe for values from('2023-02-01 00:00:00') to ('2023-03-01 00:00:00')
partition by list(name);create table part_2003 partition of part_hunhe for values from('2023-03-01 00:00:00') to ('2023-04-01 00:00:00')
partition by list(name);create table part_2004 partition of part_hunhe for values from('2023-04-01 00:00:00') to ('2023-05-01 00:00:00')
partition by list(saledate);create table part_2005 partition of part_hunhe for values from('2023-05-01 00:00:00') to ('2023-06-01 00:00:00')
partition by range(saledate);\d+ part_2001# 创建子分区表
create table part_3001 partition of part_2001 for values in ('abc');
create table part_3002 partition of part_2001 for values in ('def');
create table part_3003 partition of part_2001 for values in ('jkl');
create table part_3004 partition of part_2004 for values in ('2023-04-01 00:00:00');
create table part_3005 partition of part_2005 for values from ('2023-05-01 00:00:00') to ('2023-05-10 00:00:00');\d+ part_3001# 插入数据
insert into part_hunhe values(random()*10000,'abc','2023-01-01 08:00:00');
insert into part_hunhe values(random()*10000,'def','2023-01-01 08:00:00');# 查询数据
select tableoid::regclass,* from part_hunhe;# 查看执行计划
explain select * from part_hunhe where name='adc';