PostgreSQL教程（三）：该表的继承和分区表的详细解决方案

1。桌子的继承：

对于许多熟悉其他数据库编程的开发人员来说，这个概念有些陌生。然而，它的实现和设计原则简单易懂。现在让我们从一个简单的例子开始。

1。第一张继承表：

复制代码代码如下所示：

创建表城市（父表）

名称文本，

人口流动，

海拔int

）；

创建表的资本（--数据库）

状态字符（2）

继承（城市）；
大写字母表从城市表的所有属性。在PostgreSQL，一个表可以从零或多个其他表继承的属性，和一个查询可以引用父表中的所有行，也可以指所有行父表和子表的所有行，这是默认行为。

复制代码代码如下所示：

考试= #插入城市价值（'las拉斯维加斯，1.53，2174）；——将父表

插入01

考试= #插入城市价值（'mariposa '，3.301953）；——将父表

插入01

考试= #插入资本价值（'madison '，4.34845，装置）；——插入子表

插入01

考试= #选择的名字，从城市海拔＞500的高度；-父和子表中取出数据。

名称海拔|

----------- + ----------

拉斯维加斯2174 |

马里波萨| 1953

麦迪逊| 845

（3行）
考试= #选择的名字，从首府海拔＞500的高度；只有在子表的数据取出来。

名称海拔|

--------- + ----------

麦迪逊| 845

（1行）
如果只想从父表中提取数据，则需要在SQL中添加唯一关键字，如：

复制代码代码如下所示：

考试= #选择名字，只有城市，海拔高度＞500；

名称海拔|

----------- + ----------

拉斯维加斯2174 |

马里波萨| 1953

（2行）
在这个例子中，在唯一关键字前面的城市表明查询应该只搜索城市，而不包括城市表下面的继承级别。

在执行整个表数据删除时，如果截断父表是直接的，则父表及其所有子表的数据将被删除。如果它只是一个截断子表，父表的数据不会改变，但是子表中的数据将被清空。

复制代码代码如下所示：

考试= # TRUNCATETABLE城市；-母公司的数据和数据库被删除。

TRUNCATETABLE

考试= #选择*从资本；

名称人口高度状态| | |

------ + ------------ + ---------- + -------

（0行）
2。识别数据源：

有时你可能想知道这表记录的来源。在每个我们隐藏字段tableoid系统的形式，它可以告诉你表的来源：

复制代码代码如下所示：

考试= #选择tableoid，名字的高度，从城市高度> 500；

tableoid名称高度| |

---------- + ----------- + ----------

16532 Las Vegas 2174 | |

16532 |蝴蝶| 1953

16538 |麦迪逊| 845

（3行）
上述结果是tableoid，只有通过这样的价值，我们仍然无法看到实际的表的名称。为此，我们需要与系统表pg_class副提取实际的表的名字从表通过tableoid领域，看看下面的查询：

复制代码代码如下所示：

考试= #选择p.relname，c.name，从城市C c.altitude，pg_class P在c.altitude > 500。

relname名称高度| |

---------- + ----------- + ----------

城市拉斯维加斯| | 2174

城市蝴蝶| | 1953

首府麦迪逊| | 845

（3行）
三.数据插入注意事项：

继承不会自动从继承级别的其他表中从插入或复制中填充数据：

复制代码代码如下所示：

插入城市（名称、人口、海拔高度、状态）的值（新约克，零，零，）；
我们可能希望将数据传递到大写表中，但不会发生：插入总是插入已声明的表。
4。多表继承：

一个表可以从多个父表继承，在这种情况下，它的父表中的字段的总和。在子表中定义的任何领域也将被添加到它。如果相同的字段名出现在多个父表或在同一时间在定义父表和子表，这些领域将熔为了融合，该字段必须是相同的数据类型，否则将出现错误。融合领域的领域将继承所有的约束。

复制代码代码如下所示：

创建表parent1（firstcol整数）；

创建表parent2（firstcol整数，SecondCol varchar（20））；

创建表parent3（firstcol varchar（200））；

子表的孩子将继承parent1和parent2表在同一时间，这两个父表都包含整数型firstcol字段，这样孩子可以创造成功。

创建表的孩子（论文时间戳）继承（parent1，parent2）；

-数据库$child2不会创建成功，因为所有的父母两表包含firstcol领域，但是他们的类型是不同的。

创建表$child2（论文时间戳）继承（parent1，parent3）；

的子表child3不创造成功，因为它和它的父表包含firstcol领域，但是他们的类型是不同的。

创建表child3（FirstCol varchar（20））继承（parent1）；
5。继承权与权威性：

访问表不会自动继承。因此，一个用户试图访问父表也必须具有访问所有子表的权限，或使用唯一的关键字从父表中提取数据，对现有的继承层次中添加一个新的子表的时候，注意给它的所有权限。

继承的一个特点是，指数的严重缺陷（包括唯一的约束）和外键约束只应用于一个表，但不是子所继承。此为参考表或引用的表是真的。因此，在上面的例子中，如果我们说cities.name是唯一或主键，它不会阻止资金从城市名称表重复的数据行。这些重复行的默认值将显示在城市表查询。事实上，默认的资本根本没有独特的约束，所以它可能包含多个行具有相同的名字。你应该添加唯一约束资本，但这样做会避免重复的城市。同样，如果我们宣布cities.name引用其他表，这种约束不自动播放的首都。在这种情况下，你可以通过手动添加的资本同时参考约束。
两。分区表：

1。概述分区表：

其思想是将一个大表划分成逻辑分区，可以提供若干个物理分区：

1）。某些类型的查询性能可以大大提高。

2）。由于表的每个块的索引小于整个数据集上的索引，所以更新的性能也会得到改善。如果索引没有完全存储在内存中，则通过读取和写入索引生成更多的磁盘访问。

3）。可以通过简单删除分区来实现批处理删除。

4）。很少使用的数据可以被移动到廉价、慢速的存储介质中。

假设数据库不支持分区表，并通过我们的应用程序所需的处理数据量非常大，在这种情况下，我们不得不人为大表按照一定的规则，该手册分为几个小桌子，这样每个小表包含不同的间隔数据。这样，我们要计算的指令需要操作之前的数据插入，更新，删除，查询小表。对于某些查询，因为查询间隔可能跨多个小桌子，我们进行联合操作多个小的表的查询结果从多个表中的数据合并，并最后形成一个结果集返回到客户端，可以看到T如果我们所使用的数据库不支持分区表，我们需要做大量额外的编程工作，以弥补适用于其应用程序的场景中的不足之处，但是需要解释的是，虽然可以管理功能，但性能不能与分区表相比较。

支持PostgreSQL分区的两种主要类型如下：

1）。范围分区：通过一个或多个关键字字段将表划分为范围，并且这些区域之间没有重叠的分布，例如，我们可以根据数据范围或根据标识符的范围划分特定的业务对象。

2）列表分区：表是通过显式列出每个分区中应该出现的关键字值来完成的。

2。分区实施：

1）。创建一个主表

复制代码代码如下所示：

创建表测量（主表）

city_id INT NOT NULL，

logdate日期不为空，

peaktemp int，

）；
2）。创建多个孩子的表，每个从主表继承。通常，这些孩子表将不添加任何更多的领域。我们将句柄表称为分区，虽然他们在PostgreSQL是常见的表。

复制代码代码如下所示：

创建表measurement_yy04mm02（）继承（测量）；

创建表measurement_yy04mm03（）继承（测量）；

…

创建表measurement_yy05mm11（）继承（测量）；

创建表measurement_yy05mm12（）继承（测量）；

创建表measurement_yy06mm01（）继承（测量）；
上面创建的子已分年、月的形式，和不同年份的数据和月将被分配到不同的子表。这样的实现方式是清除分区数据非常方便和有效的。这是执行DROP TABLE语句直接删除对应的子表，然后再考虑是否重建子表根据实际应用。相比直接滴PostgreSQL数据库，还提供了另一种更方便的方式来管理子：

复制代码代码如下所示：

修改表measurement_yy06mm01没有继承测量；
与直接下降相比，方式是单独的子表从原来的主表和子表中存储的数据仍然可以访问，因为桌子已经恢复到一个共同的数据表。所以DBA数据库，你可以在这个时候表的维护操作是必要的，如数据清理、归档，许多常规操作完成后，可以直接删除表（表），或第一个空表数据（表），然后让表继承主表，如：

复制代码代码如下所示：

修改表measurement_yy06mm01继承测量；
3）。对分区表添加约束并定义每个分区允许的健康值。同时，必须注意，定义的约束确保不同分区中没有相同的键值。因此，我们需要将上面的表的定义修改为以下形式：

复制代码代码如下所示：

创建表（measurement_yy04mm02

检查（logdate日期'2004-02-01'and logdate <日期> = '2004-03-01）

继承（测量）；

创建表（measurement_yy04mm03

检查（logdate日期'2004-03-01'and logdate <日期> = '2004-04-01）

继承（测量）；

…

创建表（measurement_yy05mm11

检查（logdate日期'2005-11-01'and logdate <日期> = '2005-12-01）

继承（测量）；

创建表（measurement_yy05mm12

检查（logdate日期'2005-12-01'and logdate <日期> = '2006-01-01）

继承（测量）；

创建表（measurement_yy06mm01

检查（logdate日期'2006-01-01'and logdate <日期> = '2006-02-01）

继承（测量）；
4）。创建一个索引对核心价值观的基础上，尽可能多的。如果你需要，我们还可以创建在其他领域的数据库索引。

复制代码代码如下所示：

创建measurement_yy04mm02指数measurement_yy04mm02_logdate（logdate）；

创建measurement_yy04mm03指数measurement_yy04mm03_logdate（logdate）；

…

创建measurement_yy05mm11指数measurement_yy05mm11_logdate（logdate）；

创建measurement_yy05mm12指数measurement_yy05mm12_logdate（logdate）；

创建measurement_yy06mm01指数measurement_yy06mm01_logdate（logdate）；
5）。定义一个规则或触发重定向更改主表到相应的分区表。

如果数据是最新的分区，我们可以建立一个非常简单的规则来插入数据。我们必须重新定义这个规则，每一个月，即修改数据库名字插在重定向，它总是指向当前分区。

复制代码代码如下所示：

创建或替换规则measurement_current_partition作为

插入到测量

做的

插入measurement_yy06mm01值（new.city_id，new.logdate，新的。peaktemp）；
新是表示一个新数据字段集合的关键字。在这里，您可以通过点（。）运算符获取集合中的每个字段。

我们可能需要插入数据，并希望服务器自动定位哪个分区应该插入数据。

复制代码代码如下所示：

创建规则measurement_insert_yy04mm02作为

在测量其中的插入（logdate日期'2004-02-01'and logdate <日期> = '2004-03-01）

做的

插入measurement_yy04mm02值（new.city_id，new.logdate，新的。peaktemp）；

…

创建规则measurement_insert_yy05mm12作为

在测量其中的插入（logdate日期'2005-12-01'and logdate <日期> = '2006-01-01）

做的

插入measurement_yy05mm12值（new.city_id，new.logdate，新的。peaktemp）；

创建规则measurement_insert_yy06mm01作为

在测量其中的插入（logdate日期'2006-01-01'and logdate <日期> = '2006-02-01）

做的

插入measurement_yy06mm01值（new.city_id，new.logdate，新的。peaktemp）；
请注意，每个规则中的WHERE子句与其分区的检查约束相匹配。

你可以看到，一个复杂的分区方案可能需要相当数量的DDL。在上面的例子中，我们需要创建一个新分区，每一个月，所以这是写一个脚本自动生成所需的DDL明智的。此外，我们不难推断，在新的分区表数据批量插入一定的抑制，这是甲骨文一样。

除了以上描述的方式的规则将主表数据，每个子表，我们也可以触发来完成此操作，相比于基于规则的重定向方法基于触发的方法可能会导致更好的插入效率，特别是对于非批量插入的情况。然而，对于批量插入，因为规则的额外开销是基于表的，不是基于行的，效果会比触发模式。另一个要注意的是，复制操作将忽略规则。如果我们想通过拷贝插入数据，你只能直接拷贝数据到正确的子表，而不是主表。这个限制不会对触发器造成任何问题。另一个基于规则的重定向问题。当插入的数据不在任何子表的约束，PostgreSQL不会报告错误，但将直接保存数据在主表。

6）。添加新分区：

这里我们介绍两种添加新分区的方法。第一种方法简单直观。我们只是创建新的子表，并为它们定义新的检查约束。

复制代码代码如下所示：

创建表（measurement_y2008m02

检查（logdate日期'2008-02-01'and logdate <日期> = '2008-03-01）

继承（测量）；
第二种方法比较繁琐，但更灵活实用。请看以下四个步骤：

复制代码代码如下所示：

/ *创建一个单独的数据表（measurement_y2008m02），创建表在主表中的未来（测量）为模板，默认模板包含表的值（默认）和一致性约束（约束）。* /

创建表measurement_y2008m02

（包括包括默认值在内的度量值）；

*为表创建未来作为子表检查所使用的约束。

修改表measurement_y2008m02添加约束y2008m02

检查（logdate日期'2008-02-01'and logdate <日期> = '2008-03-01）；

将数据导入表。下面是导入数据的一个示例。在导入数据之后，如果可能的话，可以进行进一步的数据处理，如数据转换、过滤等。

◎measurement_y2008m02 from'measurement_y2008m02的副本

在适当的时间，或在需要的时候，让主表表继承。

修改表measurement_y2008m02继承测量；
7）。确保在postgresql.conf配置参数constraint_exclusion是开放的。如果没有这个参数，查询不会被优化的需要。我们需要做的是确保该选项不在配置文件中的注释。

复制代码代码如下所示：

PWD / >

/ / /选择PostgreSQL 9.1 /数据

猫postgresql.conf grep constraint_exclusion / |

constraint_exclusion =分区或分区，#。
三.划分与约束排除：

约束排除（约束排除）是一种查询优化技术，它提高了用上述方法定义的表分区的性能：

复制代码代码如下所示：

套上constraint_exclusion =；

选择计数测量，logdate（*）> =日期'2006-01-01；
如果没有约束的排斥，上面的查询将扫描测量表的每个分区。开业后的约束，计划者将检查每个分区的约束条件，然后证明分区不需要扫描，因为它不包含任何数据行符合WHERE子句的条件。如果规划师可以证明这一点，它不包括分区的查询计划。

你可以使用解释命令显示一个计划的差异时，constraint_exclusion打开和关闭，设置与上述方法的典型的默认计划表：

复制代码代码如下所示：

集constraint_exclusion =关闭；

从测量logdate解释select count（*）> =日期'2006-01-01；

查询计划

-----------------------------------------------------------------------------------------------

聚合（成本= 158.66….. 158.68行= 1宽度= 0）

>追加（成本= 0….. 151.88行= 2715宽度= 0）

扫描测量值（成本= 0….. 30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）

在measurement_yy04mm02测量->序列扫描（成本= 0。30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）

在measurement_yy04mm03测量->序列扫描（成本= 0。30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）

…

在measurement_yy05mm12测量->序列扫描（成本= 0。30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）

在measurement_yy06mm01测量->序列扫描（成本= 0。30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）
正如你可以看到从上面的查询计划，PostgreSQL已经扫描了所有的分区。让我们打开约束排除后需要在查询计划一下：

复制代码代码如下所示：

套上constraint_exclusion =；

从测量logdate解释select count（*）> =日期'2006-01-01；

查询计划

-----------------------------------------------------------------------------------------------

聚合（成本= 63.47….. 63.48行= 1宽度= 0）

>追加（成本= 0….. 60.75行= 1086宽度= 0）

扫描测量值（成本= 0….. 30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）

在measurement_yy06mm01测量->序列扫描（成本= 0。30.38行= 543宽度= 0）

过滤器：（logdate = '2006-01-01 '：：日期）
请注意，约束排除仅由检查约束驱动，而不是由索引驱动。

目前，对PostgreSQL值默认配置的版本划分，该值是一个行为之间断断续续，计划只会删除约束应用于分区基于集表查询，并将所有查询的限制排除在外，那么对于普通的数据表，也将要承担的机制所造成的额外成本。
约束排除在使用时有以下注意事项：

1）。约束排除是唯一有效的查询的WHERE子句中包含的限制。参数化查询不会因为运行时优化计划不知道哪个分区的参数选择等功能current_date必须避免。分区键的值与另一个表的字段之间的连接不会被优化。

2）。为了避免跨数据类型的比较，检查约束，因为目前的计划不会是虚假的这样一个条件。例如，下面的约束是当x是一个整数字段，但它无法使用时，X为bigint：

检查（x＝1）

为bigint字段，我们必须用这样的约束条件如下：

检查（X = 1：：bigint）

这个问题不限于bigint数据类型。在任何与默认数据类型不匹配的约束数据类型中都可能出现这种情况。在提交的查询中交叉数据类型的比较通常是可以的，但不能在检查条件下进行。

3）。主表上的更新和删除命令不执行约束排除。

4）。主表中所有分区的所有约束都将在计划约束和排除时检查。因此，大量的分区将大大增加查询计划时间。

5）。在执行分析语句时，命令是为每个分区执行的，而不仅仅是主表上的命令。