PostgreSQL11-Hash哈希分区数量的设定标准

刺骨的言语ヽ痛彻心扉 2022-02-26 01:46 609阅读 0赞

## 声明 ##

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

## 背景 ##

数据量：大表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 ####

1.  保证插入速度
2.  保证数据的均匀分布。（在oracle中hash分区数建议是2的 幂，因为如果不是2的幂数会导致数据量的分布不均匀）  
    [https://www.cnblogs.com/wengshuhang/articles/9867176.html][https_www.cnblogs.com_wengshuhang_articles_9867176.html]

## 查询 ##

当我们通过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)

### 重点3 ###

分区数量增多，每个分区内部计算量越小，可以提升多表联合的速度。

## 结论 ##

1.  分区的数量建议选择2的N次方，这样可以让数据均匀分布。
2.  分区数量的增加，能够提升查询速度和分析速度。（前提，硬件性能无限好的状态）
3.  分区数量超过某一个阈值时，会极大消耗服务器cpu和内存资源。比如：同一个时刻有非常多的works并行工作。  
    这种情况，有可能造成资源阻塞，反而导致性能下降。

那么多少分区数量是2的N次方，这个N多少比较合适呢？在不考虑服务器性能的前提下是很难给出准确的答案。但通过上面，我们可以在心底给出参考。首先，如果可以我们会尽可能建立多个分区。其次，如果sql语句中存在多表分区关联这种并发，或者其他情况，要避免资源过度消耗。

> 最后，我声明我的意见仅仅建立在我对数据库粗浅认识上的思考。后面有机会我会通过测试的方式去评估这个大小。  
> 欢迎各位高手，给出宝贵的经验和意见。

[https_www.cnblogs.com_wengshuhang_articles_9867176.html]: https://www.cnblogs.com/wengshuhang/articles/9867176.html