通过合理的设计,可以选择一定的规则将大表分成多个子表,不重不缺。这就是传说中的分区。比如我们可以按时间划分,每天一个子表。比如我们可以用其他一些字段来划分,简而言之,我们可以把它分成几个部分,提高查询的效率。
PostgreSQL有一个很有用的函数,分区表,或者说分区。当一个表中有很多记录,几千万甚至更多的时候,我们实际上需要把它分成子表。一张巨大的桌子,就像一个水果仓库,无数的苹果、桃子、橘子杂乱地堆放着,不方便寻找,性能降低。把仓库分成三个分区,苹果、桃子、橙子分别放在不同的桌子上,这样比较合理。一个大桌子变成了三个小桌子的集合。
通过合理的设计,可以选择一定的规则将大表分成多个子表,不重不缺。这就是传说中的分区。比如我们可以按时间划分,每天一个子表。比如,我们可以根据其他领域来划分。简而言之就是分成几部分,提高查询效率。
这个分区表的功能是怎么实现的?
1建立大表。
2创建分区继承
3定义规则还是触发器?
下面根据一个简单的例子来描述这个过程。我们按照60分以下和不低于60分把学生分成两个子表。
1 建立大表
创建表student (student_id bigserial,name varchar(32),score smallint)
2 创建分区继承。
创建表student _ qualified(CHECK(score=60))INHERITS(student);
创建表student_nqualified (CHECK(分数60))INHERITS(student);
创建了两个分区表student_qualified和student _ qualified,它们继承了大表student的所有字段,并设置了约束,即检查条件。
3 定义Rule或者Trigger。
虽然我们定义了检查条件,但是在向student中插入数据时,PostgreSQL无法根据分数是否低于60插入正确的子表。原因是您没有定义这个规则来告诉数据这样做。我们需要定义规则或触发器来将数据插入正确的分区表。
先看规则的定义:
创建或替换规则insert_student_qualified
学生插页上的
其中得分=60
改为做
插入student_qualified值(新。*);
创建或替换规则insert_student_nqualified
学生插页上的
其中得分60
改为做
插入到student_nqualified值(新。*);
这两个规则告诉PostgreSQL,在向通用表中插入数据时,如果分数为60,则插入student_qualified,如果分数为60,则插入student _ qualified。注意这个划分一定要准确。如果我们不小心把=60条件的“=”丢掉了,等于60分的记录就会被录入大表学生,这个是没有的。
我们插入一些记录:
插入学生(姓名,分数)值(' Jim ',77);
插入学生(姓名,分数)值(' Frank ',56);
插入到学生(姓名,分数)值(' Bean ',88);
插入学生(姓名,分数)值(' John ',47);
插入学生(姓名,分数)值(' Albert ',' 87 ');
插入到学生(姓名,分数)值中(“Joey”,“60”);
我们来看看数据分布,是否分布到正确的分区表:
SELECT p.relname,c.tableoid,c.*
来自学生c,pg_class p
其中c.tableoid=p.oid
输出如下所示:
我们可以看到,虽然我们插入的是一个大表,但是数据有对应的分区子表。满足我们的期望。同时也不影响查询。
Rule是一种导流方式,TRIGGER也可以让正确的数据流向正确的分区子表。
我们先定义一个函数。
创建或替换函数student_insert_trigger()
将触发器返回为
$$
开始
IF(NEW.score=60)然后
插入student_qualified值(新。*);
其他
插入到student_nqualified值(新。*);
结束IF;
返回NULL
结束;
$$
语言plpgsql
然后定义触发器,该触发器将在插入学生时触发:
创建触发器insert_student
在插入学生之前
对于每一行
执行过程学生_插入_触发器();
我们首先通过删除桌生,测试下引发方式。
下降表学生级联
创建表学生(student_id bigserial,name varchar(32),score smallint);
创建表student _ qualified(CHECK(分数=60))INHERITS(学生);
创建表学生_不合格(检查(分数60))继承(学生);
然后执行定义功能和定义引发的语句。就可以查看了。
为了确认我们的触发器的确触发了,我们打开存储过程的统计开关:
在postgresql.conf中,找到轨道_功能,改成全部
轨道_功能=全部
插入之前先看下函数学生_插入_触发器的统计信息:
执行插入:
插入学生(姓名,分数)值(‘吉姆’,77);
插入学生(姓名,分数)值(‘弗兰克’,56);
插入到学生(姓名,分数)值(‘豆’,88);
插入学生(姓名,分数)值(‘约翰’,47);
插入学生(姓名,分数)值(‘艾伯特’,‘87’);
插入到学生(姓名,分数)值中(《乔伊》,《60》);
插入后,看下函数学生_插入_触发器的统计信息
我们看到引发触发了6次。
执行下查询:
SELECT p.relname,c.tableoid,c.*
来自学生丙、pg _级
其中c.tableoid=p.oid
输出如下:
参考文献
一个一种数据库系统文档
郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。