PostgreSQL逻辑结构管理(二)

最新推荐文章于 2023-08-02 08:57:55 发布

运维小曜老师

最新推荐文章于 2023-08-02 08:57:55 发布

阅读量197

点赞数

本文链接：https://blog.csdn.net/weixin_55990817/article/details/119716426

版权

表继承

表继承是PG中特有的概念。字表会继承父表中的所有字段。
查询数据时，查询父表会带出子表数据，反之不行。如果只希望查询父表数据，可以使用ONLY关键字
如 select * from ONLY parent;

注：1.父表的检查约束和非空约束会被子表继承。其他约束（如唯一约束，主键，外键）则不会被继承。
2.一个子表可以从多个父表继承，当同名字段出现在多个父表中（或者父表和子表中），这些字段会被融合（此时字段类型必须相同，否则会抛出一个错误）,融合的字段拥有所有父字段所有的检查约束。
3.使用SELECT ,UPDATE ,DELETE 命令访问或修改一张父表的时候，也是也会作用于相应的子表。使用ALTER TABLE 对父表做修改是，一般也会修改字表，但是 REINDEX 和 VACUUM 命令不会影响到字表。

分区表

PostgreSQL是通过表继承来实现分区表的。表分区就是把逻辑上的一个达标切割成物理上的几个小块。分区如下有优点:

删除历史数据时更快，如果是按照时间分区的，在删除历史数据是，直接删除历史分区即可。如果没有分区，通过DELETE删除历史数据会很慢，还容易导致VACUUM超载。
某些类型的查询性能得到极大提升，特别是表中访问率较高的行位于一个单独分区或者少数几个分区上的情况下。比如按时间分区的表中，大多数查询发生在时间最近的的几个分区，而最早时间的分区则较少查询。那么在建分区后，各分区会有各自的索引，使用率较高的分区表的索引就完全可能缓存在内存中，效率会高很多。
当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是使用索引离散地访问整个表，可以获得巨大的性能提升。
很少使用的历史数据可以使用表空间的技术移动到一些便宜一些的慢速存储介质上。因为使用分区表可以将不同的分区安置在不同的介质上。

是否使用分区表

根据应用情况而定，但基本原则是表的大小超过了数据库服务器的物理内存大小时应该使用。在使用继承实现的分区表时，一般会让父表为空，数据都存储在字表中。
如何创建分区表

创建父表，所有的分区表都从它继承。这个表中没有数据，不要再此表上定义任何检查约束，除非希望约束所有分区表。同样在其上定义任何索引或唯一性约束也没有意义。
创建子表，每个都从主表上继承。这些子表实际上是普通的PG表。
给分区表增加约束、定义键值等
对分区字段创建索引
定义一个规则或者触发器，对把主表的数据插入重定向到合适分分区表
确保postgresql.conf 里constraint_exclusion 的配置参数是打开的。打开后如果查询中Where字句的过滤条件与分区的约束条件匹配，那么查询会匹配到正确的分区。

使用规则（RULE）插入数据的缺点

相比较触发器，规则会有显著的开销，每次检查都有此开销。但是在批量插入的情况下会比触发器开销更少。
COPY不会触发RULE,但是会触发触发器。
如果插入数据在规则设置范围之外的，会直接插入到主表。

分区的优化技巧

打开约束排除（constraint_exclusion）,是一种查询优化，它改进了用上面方法定义的表分区的性能。在PG9.2中constraint_exclusion 默认就是 partition，在SQL查询中将where语句的过滤条件与表上的CHECK条件进行比较久可以过滤掉不需要扫描的分区从而提高性能。