postgresql12 分区表 hash 算法

转载

云端梦想家 2024-07-15 06:12:45

文章标签 postgresql hash分区分区数量数据库数据 文章分类 云原生云计算

声明

完全基于我对数据库粗浅认识的分析，而没有充分依据。

背景

数据量：大表10T，小表1T。服务器配置：8cpu,16G内存。

原理思考

pg11版本开启了内置hash分区表，面对以上数据该如何确定分区数量呢？

首先，hash分区建分区的语句如下

MODULUS=20 代表分区数量
MODULUS =n 代表第几个分区

create table 	test_p1 	 partition oftest for values WITH (MODULUS 20, REMAINDER 0);  
create table 	test_p2	     partition oftest for values WITH (MODULUS 20, REMAINDER 1);
create table 	test_p3	 	 partition oftest for values WITH (MODULUS 20, REMAINDER 2);
create table 	test_p4	 	 partition oftest for values WITH (MODULUS 20, REMAINDER 3);
。。。。。
create table 	test_p18	 partition oftest for values WITH (MODULUS 20, REMAINDER 17);
create table 	test_p19	 partition oftest for values WITH (MODULUS 20, REMAINDER 18);
create table 	test_p20	 partition oftest for values WITH (MODULUS 20, REMAINDER 19);

插入数据过程

当一个id=‘aaaadadfasdfasdfasdfasdf’数据插入到数据库时，首先会计算该字符串的hashcode(id)得到一个整数。
随后我们队这个整数求MODULUS =20的余数，如果余数是0就进入第一个分区。以下类推，余数是19就进入第20个分区。

重点1

保证插入速度
保证数据的均匀分布。（在oracle中hash分区数建议是2的幂，因为如果不是2的幂数会导致数据量的分布不均匀）

查询

当我们通过Where id=“sdfasdfsdfasdfasd”查询的时候，首选pgsql会计算数据时在哪个分区，过程同上。然后我们在分区（总数据大小的1/20）范围内，按照索引查询。数据量少了20倍，理论上查询的速度会提升，但是并不可能达到20倍。需要考虑到计算分区的时间和索引查询的算法。

重点2

分区数量增加，能够减少分区数据量，可以提升查询速度。

多表关联

根据pgsql11的特性，可以对hash分区开启智能分区连接-聚合的特性。同时可以并行。name实际关联的过程可能如下。
我们把大表和小表的20个分区一一对接，在各自分区操作完毕后在汇集起来。加上并行，速度会提升很多。

->  Hash Join
               Hash Cond: (t2.b = t1.a)
               ->  Seq Scan on prt2_p1 t2
               ->  Hash
                     ->  Seq Scan on prt1_p1 t1
                           Filter: (b = 0)
         ->  Hash Join
               Hash Cond: (t2_1.b = t1_1.a)
               ->  Seq Scan on prt2_p2 t2_1
               ->  Hash
                     ->  Seq Scan on prt1_p2 t1_1
                           Filter: (b = 0)
         ->  Hash Join
               Hash Cond: (t2_2.b = t1_2.a)
               ->  Seq Scan on prt2_p3 t2_2
               ->  Hash
                     ->  Seq Scan on prt1_p3 t1_2
                           Filter: (b = 0)