PostgreSQL技術(shù)大講堂 - 第50講：PG分區(qū)表管理

文章來(lái)源：更新時(shí)間:2024/4/28 9:58:13

在線老師點(diǎn)擊咨詢：

最新學(xué)訊：近期OCP認(rèn)證正在報(bào)名中，因考試人員較多請(qǐng)盡快報(bào)名獲取最近考試時(shí)間，報(bào)名費(fèi)用請(qǐng)聯(lián)系在線老師，甲骨文官方認(rèn)證，報(bào)名從速！

我要咨詢

PostgreSQL從小白到專家，是從入門(mén)逐漸能力提升的一個(gè)系列教程，內(nèi)容包括對(duì)PG基礎(chǔ)的認(rèn)知、包括安裝使用、包括角色權(quán)限、包括維護(hù)管理、、等內(nèi)容，希望對(duì)熱愛(ài)PG、學(xué)習(xí)PG的同學(xué)們有幫助，歡迎持續(xù)關(guān)注CUUG PG技術(shù)大講堂。

第50講：PG分區(qū)表管理

內(nèi)容1：分區(qū)表特點(diǎn)

內(nèi)容2：范圍分區(qū)介紹

內(nèi)容3：list分區(qū)介紹

內(nèi)容4：hash分區(qū)介紹

內(nèi)容5：混合分區(qū)介紹

分區(qū)表特點(diǎn)

分而治之是分區(qū)表最大的特點(diǎn)，將表數(shù)據(jù)分成更小的物理分片，減少搜索范圍，以此可以查詢提高性能。分區(qū)表是關(guān)系型數(shù)據(jù)庫(kù)中比較常見(jiàn)的對(duì)大表的優(yōu)化方式，數(shù)據(jù)庫(kù)管理系統(tǒng)一般都提供了分區(qū)管理，而業(yè)務(wù)可以直接訪問(wèn)分區(qū)表而不需要調(diào)整業(yè)務(wù)架構(gòu)，當(dāng)然好的性能需要合理的分區(qū)訪問(wèn)方式。

分區(qū)的具體好處是：改善查詢性能、增強(qiáng)可用性、維護(hù)方便、均衡I/O。

PostgreSQL分區(qū)表特點(diǎn)

PG數(shù)據(jù)庫(kù)表分區(qū)表的結(jié)構(gòu)是由主表(父表)與分區(qū)表(子表)組成，主表是創(chuàng)建子表的模板，它是一個(gè)正常的普通表，正常情況下它并不儲(chǔ)存任何數(shù)據(jù);分區(qū)表繼承并屬于一個(gè)主表，分區(qū)表中存儲(chǔ)所有的數(shù)據(jù)，主表與分區(qū)表屬于一對(duì)多的關(guān)系，也就是說(shuō)，一個(gè)主表包含多個(gè)分區(qū)表，而一個(gè)分區(qū)表只從屬于一個(gè)主表

官方聲明分區(qū)實(shí)現(xiàn)方式：聲明式分區(qū)、繼承分區(qū);除此之外還支持其它的第三方分區(qū)管理方式，比如pathman擴(kuò)展等

聲明分區(qū)也叫原生分區(qū)，從PG10版本開(kāi)始支持，相當(dāng)于“官方支持”的分區(qū)表，也是最為推薦的分區(qū)方式。雖然與繼承分區(qū)不一樣，但是其內(nèi)部也是用繼承表實(shí)現(xiàn)的。聲明式分區(qū)支持：范圍分區(qū)、list分區(qū)、hash分區(qū)

范圍分區(qū)表

范圍分區(qū)表一般指的一個(gè)分區(qū)的范圍，然后把滿足條件的行存放在該分區(qū)中，最常見(jiàn)的是以日期做為分區(qū)條件，根據(jù)時(shí)間段分為不同的分區(qū)，存放不同時(shí)間段的數(shù)據(jù)。

范圍分區(qū)實(shí)現(xiàn)：1、創(chuàng)建主表

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(id,saledate);\d+ part_range

2、創(chuàng)建分區(qū)表

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 p2_202402 partition of part_range for values from ('2024-02-01 00:00:00') to ('2024-03-01 00:00:00');\d+ p1_202401

3、插入數(shù)據(jù)

INSERT INTO part_range SELECT random() * 10000, md5(g::text),g FROM generate_series('2024-01-01'::date, '2024-02-28'::date, '1 minute') as g;

4、查看數(shù)據(jù)

select tableoid::regclass,count(*) FROM part_range group by tableoid::regclass;

在某些情況下，需要知道特定行來(lái)自哪個(gè)表，每個(gè)表中都有一個(gè)名為 tableoid 的系統(tǒng)列，使用 regclass 別名類型，它將象征性地打印表 OID，可以列出行的原始表。

訪問(wèn)分區(qū)表：

1、通過(guò)主表訪問(wèn)

explain select * from part_range where saledate ='2024-02-05';

2、通過(guò)分區(qū)表訪問(wèn)

explain select from p2_202402 where order_id=100;

list分區(qū)表

list分區(qū)以指定的分區(qū)值將數(shù)據(jù)存放到對(duì)應(yīng)的分區(qū)上，然后把滿足條件的行存放在該分區(qū)中，最常見(jiàn)的是以某列值為分區(qū)條件，根據(jù)不同的列值存放在不同的分區(qū)。

list分區(qū)實(shí)現(xiàn)：

1、創(chuàng)建主表

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);\d+ part_range

2、創(chuàng)建分區(qū)表

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');

3、插入數(shù)據(jù)

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',20);

3、查看數(shù)據(jù)

SELECT tableoid::regclass,* FROM part_list;

4、查看執(zhí)行計(jì)劃

explain select * from part_list where name='fujian';

hash分區(qū)表

hash分區(qū)將數(shù)據(jù)散列存儲(chǔ)在各個(gè)分區(qū)上，以打散熱點(diǎn)數(shù)據(jù)存放到對(duì)應(yīng)的分區(qū)上，然后把滿足條件的行存放在該分區(qū)中，最常見(jiàn)的是平均的把數(shù)據(jù)放在不同的分區(qū)。

hash分區(qū)實(shí)現(xiàn)：

1、創(chuàng)建主表

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

2、創(chuàng)建分區(qū)表

CREATE TABLE p1_hash PARTITION OF part_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); 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);\d+ p1_hash

3、插入數(shù)據(jù)

insert into part_hash values(generate_series(1,10000),'a');

3、查詢數(shù)據(jù)

SELECT tableoid::regclass,count(*) FROM part_hash group by tableoid::regclass;

4、查看執(zhí)行計(jì)劃

explain select * from part_hash where order_id=1000;

混合分區(qū)表

PG分區(qū)下面也可以建立子分區(qū)構(gòu)成級(jí)聯(lián)模式，子分區(qū)可以有不同的分區(qū)方式，這樣的分區(qū)稱為混合分區(qū)

混合分區(qū)表實(shí)現(xiàn)

1、創(chuàng)建主表

create table part_hunhe (id int not null,name varchar(20),saledate timestamp) partition by range(saledate);\d+ part_hunhe

2、創(chuàng)建分區(qū)表

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) ;\d+ part_2001

3、創(chuàng)建子分區(qū)表

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');\d+ part_3001

3、插入數(shù)據(jù)

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');\d+ part_3001

4、查看數(shù)據(jù)

SELECT tableoid::regclass,* FROM part_hunhe;