目录
一、分区表特点
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';
六、总结
1、pg不支持interval分区，没有自带的自动新增分区功能
2、分区表的分区本身也是表，主表不存储数据，分区表存储数据
3、truncate、vacuum、analyze主表会执行所有分区。truncateonly不能在主表上执行，但可以在存数据的分区表上执行，仅清除这个分区表
4、range、hash分区的分区键可以有多个列，list分区键只能是单个列或表达式
5、default分区表会接收不在声明的范围中的数据；如果没有default分区，插入范围外的数据会直接报错
//创建default子表：CREATE TABLE tbl_name_default PARTITION OF tbl_log DEFAULT;
6、如果要新增分区，需要注意default分区中是否有这个新增分区的数据，如果有，则会发生冲突导致报错（不会自动迁移过去）。
7、partition of创建的分区会自动创建主表上定义的索引、约束、行级触发器
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
