• 售前

  • 售后

热门帖子
入门百科

PostgreSQL LIST、RANGE 表分区的实现方案

[复制链接]
徐殿军 显示全部楼层 发表于 2021-10-26 13:23:44 |阅读模式 打印 上一主题 下一主题
简 介

PG分区:就是把逻辑上的一个大表分割成物理上的几块。
分区的长处

1. 某些范例的查询性能得到提升
2. 更新的性能也可以得到提升,由于某块的索引要比在整个数据集上的索引要小。
3. 批量删除可以通过简朴的删除某个分区来实现。
4. 可以将很少用的数据移动到自制的、转速慢的存储介质上。
分区实现原理

10.x版本之前PG表分区的实现原理:PG中是通过表的继承来实现的,创建一个主表,里面是空的,然后每个分区去继承它。无论何时,该主表里面都必须是空的
官网发起:只有当表本身大小高出了呆板物理内存的现实大小时,才思量分区。
原分区用法
以继承表的方式实现:
  1. create table tbl( a int, b varchar(10) );
  2. create table tbl_1 ( check ( a <= 1000 ) ) INHERITS (tbl);
  3. create table tbl_2 ( check ( a <= 10000 and a >1000 ) ) INHERITS (tbl);
  4. create table tbl_3 ( check ( a <= 100000 and a >10000 ) ) INHERITS (tbl);
复制代码
再通过创建触发器或者规则,实现数据分发,只需要向子表插入数据则会自动分配到子表中
  1. CREATE OR REPLACE FUNCTION tbl_part_tg()
  2. RETURNS TRIGGER AS $$
  3. BEGIN
  4. IF ( NEW. a <= 1000 ) THEN
  5. INSERT INTO tbl_1 VALUES (NEW.*);
  6. ELSIF ( NEW. a > 1000 and NEW.a <= 10000 ) THEN
  7. INSERT INTO tbl_2 VALUES (NEW.*);
  8. ELSIF ( NEW. a > 10000 and NEW.a <= 100000 ) THEN
  9. INSERT INTO tbl_3 VALUES (NEW.*);
  10. ELSIF ( NEW. a > 100000 and NEW.a <= 1000000 ) THEN
  11. INSERT INTO tbl_4 VALUES (NEW.*);
  12. ELSE RAISE EXCEPTION 'data out of range!';
  13. END IF;
  14. RETURN NULL;
  15. END;
  16. $$
  17. LANGUAGE plpgsql;
  18. CREATE TRIGGER insert_tbl_part_tg
  19.   BEFORE INSERT ON tbl
  20. FOR EACH ROW EXECUTE PROCEDURE tbl_part_tg();
复制代码
分区创建成功
如何实现分区过滤?

对于分区表来说,如果有50个分区表,对于某个条件的值如果能确定,那么很大概直接过滤掉49个分区,大大提高扫描速率,当然分区表也能放在差别的物理盘上,提高IO速率。
对于查询是怎么实现分区表过滤呢?
约束扫除 是否使用约束扫除通过postgresql.conf中参数constraint_exclusion 来控制,
只有三个值
  1. constraint_exclusion = on
复制代码
on:所有情况都会进行约束扫除查抄
off:关闭,所有约束都不生效
partition:对分区表或者继承表进行约束排查,默以为partition
如:
  1. select *from tbl where a = 12345;
复制代码
起首找到主表tbl,然后通过tbl找到它的子表,找到后再对再拿着谓词条件a = 12345对一个个子表约束进行查抄,不符合条件表就去掉不扫描,实现分区表过滤,下面简朴先容下约束扫除源码逻辑。
如何实现数据分发?

基于规则的话,会在查询重写阶段按时替换规则天生新的插入语句,基于触发器会在insert主表前触发别的一个insert利用,这两个逻辑都比较简朴,干系代码不再先容。
错误描述:在新建分区主表时提示以下错误信息

错误缘故原由:在本地postgresql.conf 设置了 search_path = ‘$user' ,所以在使用的时候需要先创建当前用户对应的schema,如果不存在,则会提示错误
解决方法:在创建表时指定创建的schemal,即可成功。

PostgreSQL 10.x LIST分区方案
  1. postgres=# CREATE TABLE list_parted (
  2. postgres(# a int
  3. postgres(# ) PARTITION BY LIST (a);
  4. CREATE TABLE
  5. postgres=# CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN (1);
  6. CREATE TABLE
  7. postgres=# CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
  8. CREATE TABLE
  9. postgres=# CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES IN (3);
  10. CREATE TABLE
  11. postgres=# CREATE TABLE part_4 PARTITION OF list_parted FOR VALUES IN (4);
  12. CREATE TABLE
  13. postgres=# CREATE TABLE part_5 PARTITION OF list_parted FOR VALUES IN (5);
  14. CREATE TABLE
  15. postgres=#
  16. postgres=# insert into list_parted values(32); --faled
  17. ERROR: no partition of relation "list_parted" found for row
  18. DETAIL: Failing row contains (32).
  19. postgres=# insert into part_1 values(1);
  20. INSERT 0 1
  21. postgres=# insert into part_1 values(2);--faled
  22. ERROR: new row for relation "part_1" violates partition constraint
  23. DETAIL: Failing row contains (2).
  24. postgres=# explain select *from list_parted where a =1;
  25.        QUERY PLAN
  26. -----------------------------------------------------------------
  27. Append (cost=0.00..41.88 rows=14 width=4)
  28. -> Seq Scan on list_parted (cost=0.00..0.00 rows=1 width=4)
  29.    Filter: (a = 1)
  30. -> Seq Scan on part_1 (cost=0.00..41.88 rows=13 width=4)
  31.    Filter: (a = 1)
  32. (5 rows)
复制代码
上面是LIST分区表,建表是先建主表,再建子表,子表以 PARTITION OF 方式阐明和主表关系,约束条件应该就是后面的in里面。
Explain 执行sql分析计划

cost:数据库自定义的斲丧单位,通过统计信息来估计SQL斲丧。(查询分析是根据analyze的固执天生的,天生之后按照这个查询计划执行,执行过程中analyze是不会变的。所以如果估值和真是情况差别较大,就会影响查询计划的天生。)
rows:根据统计信息估计SQL返回效果集的行数。
width:返回效果集每一行的长度,这个长度值是根据pg_statistic表中的统计信息来盘算的。

PostgreSQL 10.x RANGE分区

创建RANGE分区
  1. postgres=# CREATE TABLE range_parted (
  2. postgres(# a int
  3. postgres(# ) PARTITION BY RANGE (a);
  4. CREATE TABLE
  5. postgres=# CREATE TABLE range_parted1 PARTITION OF range_parted FOR VALUES from (1) TO (1000);
  6. CREATE TABLE
  7. postgres=# CREATE TABLE range_parted2 PARTITION OF range_parted FOR VALUES FROM (1000) TO (10000);
  8. CREATE TABLE
  9. postgres=# CREATE TABLE range_parted3 PARTITION OF range_parted FOR VALUES FROM (10000) TO (100000);
  10. CREATE TABLE
  11. postgres=#
  12. postgres=# insert into range_parted1 values(343);
  13. INSERT 0 1
  14. postgres=#
  15. postgres=# explain select *from range_parted where a=32425;
  16.         QUERY PLAN
  17. ---------------------------------------------------------------------
  18. Append (cost=0.00..41.88 rows=14 width=4)
  19. -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
  20.    Filter: (a = 32425)
  21. -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
  22.    Filter: (a = 32425)
  23. (5 rows)
  24. postgres=# set constraint_exclusion = off;
  25. SET
  26. postgres=# explain select *from range_parted where a=32425;
  27.         QUERY PLAN
  28. ---------------------------------------------------------------------
  29. Append (cost=0.00..125.63 rows=40 width=4)
  30. -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
  31.    Filter: (a = 32425)
  32. -> Seq Scan on range_parted1 (cost=0.00..41.88 rows=13 width=4)
  33.    Filter: (a = 32425)
  34. -> Seq Scan on range_parted2 (cost=0.00..41.88 rows=13 width=4)
  35.    Filter: (a = 32425)
  36. -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
  37.    Filter: (a = 32425)
  38. (9 rows)
复制代码
上述利用中的 a的取值范围为【0,1000)即插入值若为1000边界值,则会生存在第二个分区表中和LIST差不多,就是语法略有差别,范围表值是一个一连的范围,LIST表是单点或多点的聚集。
从上面例子可以看到,显然照旧走的约束扫除过滤子表的方式。
constraint_exclusion = “on ,off,partition ”; 该参数为postgresql.conf中的参数
  1. on
复制代码
表示所有的查询都会执行约束扫除
  1. off
复制代码
关闭,所有的查询都不会执行约束扫除
  1. partition
复制代码
:表示只对分区的表进行约束扫除
分区列的范例必须支持btree索引接口(险些涵盖所有范例, 后面会说到查抄方法)。
更新后的数据如果超出了所在分区的范围,则会报错

PostgreSQL 分区留意事项

语法
1、创建主表
  1. [ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
复制代码
2、创建分区
  1. PARTITION OF parent_table [ (
  2. { column_name [ column_constraint [ ... ] ]
  3. | table_constraint }
  4. [, ... ]
  5. ) ] FOR VALUES partition_bound_spec
  6. and partition_bound_spec is:
  7. { IN ( expression [, ...] ) -- list分区
  8. |
  9. FROM ( { expression | UNBOUNDED } [, ...] ) TO ( { expression | UNBOUNDED } [, ...] ) } -- range分区, unbounded表示无限小或无限大
复制代码
语法表明
  1. partition by
复制代码
指定分区表的范例range或list指定分区列,或表达式作为分区键。
  1. range
复制代码
分区表键:支持指定多列、或多表达式,支持混合(键,非表达式中的列,会自动添加not null的约束)
  1. list
复制代码
分区表键:支持单个列、或单个表达式
分区键必须有对应的btree索引方法的ops(可以检察体系表得到)
  1. select typname from pg_type where oid in (select opcintype from pg_opclass);
复制代码
主表不会有任何数据,数据会根据分区规则进入对应的分区表
如果插入数据时,分区键的值没有匹配的分区,会报错
不支持全局的unique, primary key, exclude, foreign key约束,只能在对应的分区创建这些约束
分区表和主表的 列数量,定义 必须完全一致,(包罗OID也必须一致,要么都有,要么都没有)
可以为分区表的列单独增长Default值,或约束。
用户还可以对分区表增长表级约束
如果新增的分区表check约束,名字与主表的约束名一致,则约束内容必须与主表一致
当用户往主表插入数据库时,纪录被自动路由到对应的分区,如果没有合适的分区,则报错
如果更新数据,并且更新后的KEY导致数据需要移动到另一分区,则会报错,(意思是分区键 可以更新,但是不支持更新后的数据移出到别的分区表)
修改主表的字段名,字段范例时,会自动同时修改所有的分区
TRUNCATE 主表时,会扫除所有继承表分区的纪录(如果有多级分区,则会一直扫除到所有的直接和间接继承的分区)
如果要扫除单个分区,请对分区进行利用
如果要删除分区表,可以使用DROP TABLE的DDL语句,留意这个利用会对主表也加access exclusive lock。
增补:对PostgreSQL语法分析中 targetlist 的理解
在 gram.y 中:
  1. simple_select:               
  2.       SELECT  opt_distinct  target_list         
  3.       into_clause   from_clause   where_clause         
  4.       group_clause   having_clause  window_clause         
  5.         {        
  6.           SelectStmt *n = makeNode(SelectStmt);      
  7.           n->distinctClause = $2;      
  8.           n->targetList = $3;      
  9.           n->intoClause = $4;      
  10.           n->fromClause = $5;      
  11.           n->whereClause = $6;      
  12.           n->groupClause = $7;      
  13.           n->havingClause = $8;      
  14.           n->windowClause = $9;      
  15.           $$ = (Node *)n;      
  16.         }        
  17. ……   
复制代码
把它修改一下,增长:
  1. simple_select:               
  2.       SELECT  opt_distinct  target_list         
  3.       into_clause   from_clause   where_clause         
  4.       group_clause   having_clause  window_clause         
  5.         {        
  6.           SelectStmt *n = makeNode(SelectStmt);      
  7.           n->distinctClause = $2;      
  8.           n->targetList = $3;      
  9.           n->intoClause = $4;      
  10.           n->fromClause = $5;      
  11.           n->whereClause = $6;      
  12.           n->groupClause = $7;      
  13.           n->havingClause = $8;      
  14.           n->windowClause = $9;      
  15.           $$ = (Node *)n;
  16.           fprintf(stderr,"length of list: %d\n", n->targetList->length);      
  17.         }      
  18. ……   
复制代码
psql 中执行:
  1. select id, name from a8;
复制代码
配景出现:
  1. length of list: 2
复制代码
以上为个人履历,渴望能给各人一个参考,也渴望各人多多支持脚本之家。如有错误或未思量完全的地方,望不吝见教。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作