PostgreSQL教程(三)：表的继承和分区表详解
一、表的继承：
这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生，然而它的实现方式和设计原理却是简单易懂，现在就让我们从一个简单的例子开始吧。
1.第一个继承表：
CREATETABLEcities( --父表
name text,
populationfloat,
altitude int
);
CREATETABLEcapitals(--子表
state char(2)
)INHERITS(cities);
capitals表继承自cities表的所有属性。在PostgreSQL里，一个表可以从零个或多个其它表中继承属性，而且一个查询既可以引用父表中的所有行，也可以引用父表的所有行加上其所有子表的行，其中后者是缺省行为。
MyTest=#INSERTINTOcitiesvalues('LasVegas',1.53,2174); --插入父表
INSERT01
MyTest=#INSERTINTOcitiesvalues('Mariposa',3.30,1953); --插入父表
INSERT01
MyTest=#INSERTINTOcapitalsvalues('Madison',4.34,845,'WI');--插入子表
INSERT01
MyTest=#SELECTname,altitudeFROMcitiesWHEREaltitude>500;--父表和子表的数据均被取出。
name |altitude
-----------+----------
LasVegas| 2174
Mariposa | 1953
Madison | 845
(3rows)
MyTest=#SELECTname,altitudeFROMcapitalsWHEREaltitude>500;--只有子表的数据被取出。
name |altitude
---------+----------
Madison| 845
(1row)
如果希望只从父表中提取数据，则需要在SQL中加入ONLY关键字，如：
MyTest=#SELECTname,altitudeFROMONLYcitiesWHEREaltitude>500;
name |altitude
-----------+----------
LasVegas| 2174
Mariposa | 1953
(2rows)
上例中cities前面的"ONLY"关键字表示该查询应该只对cities进行查找而不包括继承级别低于cities的表。许多我们已经讨论过的命令--SELECT，UPDATE和DELETE--支持这个"ONLY"符号。
在执行整表数据删除时，如果直接truncate父表，此时父表和其所有子表的数据均被删除，如果只是truncate子表，那么其父表的数据将不会变化，只是子表中的数据被清空。
MyTest=#TRUNCATETABLEcities; --父表和子表的数据均被删除。
TRUNCATETABLE
MyTest=#SELECT*FROMcapitals;
name|population|altitude|state
------+------------+----------+-------
(0rows)
2.确定数据来源：
有时候你可能想知道某条记录来自哪个表。在每个表里我们都有一个系统隐含字段tableoid，它可以告诉你表的来源：
MyTest=#SELECTtableoid,name,altitudeFROMcitiesWHEREaltitude>500;
tableoid| name |altitude
----------+-----------+----------
16532|LasVegas| 2174
16532|Mariposa | 1953
16538|Madison | 845
(3rows)
以上的结果只是给出了tableoid，仅仅通过该值，我们还是无法看出实际的表名。要完成此操作，我们就需要和系统表pg_class进行关联，以通过tableoid字段从该表中提取实际的表名，见以下查询：
MyTest=#SELECTp.relname,c.name,c.altitudeFROMcitiesc,pg_classpWHEREc.altitude>500andc.tableoid=p.oid;
relname | name |altitude
----------+-----------+----------
cities |LasVegas| 2174
cities |Mariposa | 1953
capitals|Madison | 845
(3rows)
3.数据插入的注意事项：
继承并不自动从INSERT或者COPY中向继承级别中的其它表填充数据。在我们的例子里，下面的INSERT语句不会成功：
INSERTINTOcities(name,population,altitude,state)VALUES('NewYork',NULL,NULL,'NY');
我们可能希望数据被传递到capitals表里面去，但是这是不会发生的：INSERT总是插入明确声明的那个表。
4.多表继承：
一个表可以从多个父表继承，这种情况下它拥有父表们的字段的总和。子表中任意定义的字段也会加入其中。如果同一个字段名出现在多个父表中，或者同时出现在父表和子表的定义里，那么这些字段就会被"融合"，这样在子表里面就只有一个这样的字段。要想融合，字段必须是相同的数据类型，否则就会抛出一个错误。融合的字段将会拥有它所继承的字段的所有约束。
CREATETABLEparent1(FirstColinteger); CREATETABLEparent2(FirstColinteger,SecondColvarchar(20)); CREATETABLEparent3(FirstColvarchar(200)); --子表child1将同时继承自parent1和parent2表，而这两个父表中均包含integer类型的FirstCol字段，因此child1可以创建成功。 CREATETABLEchild1(MyColtimestamp)INHERITS(parent1,parent2); --子表child2将不会创建成功，因为其两个父表中均包含FirstCol字段，但是它们的类型不相同。 CREATETABLEchild2(MyColtimestamp)INHERITS(parent1,parent3); --子表child3同样不会创建成功，因为它和其父表均包含FirstCol字段，但是它们的类型不相同。 CREATETABLEchild3(FirstColvarchar(20))INHERITS(parent1);
5.继承和权限：
表访问权限并不会自动继承。因此，一个试图访问父表的用户还必须具有访问它的所有子表的权限，或者使用ONLY关键字只从父表中提取数据。在向现有的继承层次添加新的子表的时候，请注意给它赋予所有权限。
继承特性的一个严重的局限性是索引(包括唯一约束)和外键约束只施用于单个表，而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实，因此在上面的例子里，如果我们声明cities.name为UNIQUE或者是一个PRIMARYKEY，那么也不会阻止capitals表拥有重复了名字的cities数据行。并且这些重复的行缺省时在查询cities表的时候会显示出来。实际上，缺省时capitals将完全没有唯一约束，因此可能包含带有同名的多个行。你应该给capitals增加唯一约束，但是这样做也不会避免与cities的重复。类似，如果我们声明cities.nameREFERENCES某些其它的表，这个约束不会自动广播到capitals。在这种条件下，你可以通过手工给capitals增加同样的REFERENCES约束来做到这点。
二、分区表：
1.概述分区表：
分区的意思是把逻辑上的一个大表分割成物理上的几块儿，分区可以提供若干好处：
1).某些类型的查询性能可以得到极大提升。
2).更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问。
3).批量删除可以用简单地删除某个分区来实现。
4).将很少用的数据可以移动到便宜的、慢一些地存储介质上。
假设当前的数据库并不支持分区表，而我们的应用所需处理的数据量也非常大，对于这种应用场景，我们不得不人为的将该大表按照一定的规则，手工拆分成多个小表，让每个小表包含不同区间的数据。这样一来，我们就必须在数据插入、更新、删除和查询之前，先计算本次的指令需要操作的小表。对于有些查询而言，由于查询区间可能会跨越多个小表，这样我们又不得不将多个小表的查询结果进行union操作，以合并来自多个表的数据，并最终形成一个结果集返回给客户端。可见，如果我们正在使用的数据库不支持分区表，那么在适合其应用的场景下，我们就需要做很多额外的编程工作以弥补这一缺失。然而需要说明的是，尽管功能可以勉强应付，但是性能却和分区表无法相提并论。
目前PostgreSQL支持的分区形式主要为以下两种：
1).范围分区:表被一个或者多个键字字段分区成"范围"，在这些范围之间没有重叠的数值分布到不同的分区里。比如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。
2).列表分区:表是通过明确地列出每个分区里应该出现那些键字值实现的。
2.实现分区：
1).创建"主表"，所有分区都从它继承。
CREATETABLEmeasurement( --主表
city_id int NOTNULL,
logdate date NOTNULL,
peaktempint,
);
2).创建几个"子"表，每个都从主表上继承。通常，这些"子"表将不会再增加任何字段。我们将把子表称作分区，尽管它们就是普通的PostgreSQL表。
CREATETABLEmeasurement_yy04mm02()INHERITS(measurement); CREATETABLEmeasurement_yy04mm03()INHERITS(measurement); ... CREATETABLEmeasurement_yy05mm11()INHERITS(measurement); CREATETABLEmeasurement_yy05mm12()INHERITS(measurement); CREATETABLEmeasurement_yy06mm01()INHERITS(measurement);
上面创建的子表，均已年、月的形式进行范围划分，不同年月的数据将归属到不同的子表内。这样的实现方式对于清空分区数据而言将极为方便和高效，即直接执行DROPTABLE语句删除相应的子表，之后在根据实际的应用考虑是否重建该子表(分区)。相比于直接DROP子表，PostgreSQL还提供了另外一种更为方便的方式来管理子表：
ALTERTABLEmeasurement_yy06mm01NOINHERITmeasurement;
和直接DROP相比，该方式仅仅是使子表脱离了原有的主表，而存储在子表中的数据仍然可以得到访问，因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说，就可以在此时对该表进行必要的维护操作，如数据清理、归档等，在完成诸多例行性的操作之后，就可以考虑是直接删除该表(DROPTABLE)，还是先清空该表的数据(TRUNCATETABLE)，之后再让该表重新继承主表，如：
ALTERTABLEmeasurement_yy06mm01INHERITmeasurement;
3).给分区表增加约束，定义每个分区允许的健值。同时需要注意的是，定义的约束要确保在不同的分区里不会有相同的键值。因此，我们需要将上面"子"表的定义修改为以下形式：
CREATETABLEmeasurement_yy04mm02(
CHECK(logdate>=DATE'2004-02-01'ANDlogdate<DATE'2004-03-01')
)INHERITS(measurement);
CREATETABLEmeasurement_yy04mm03(
CHECK(logdate>=DATE'2004-03-01'ANDlogdate<DATE'2004-04-01')
)INHERITS(measurement);
...
CREATETABLEmeasurement_yy05mm11(
CHECK(logdate>=DATE'2005-11-01'ANDlogdate<DATE'2005-12-01')
)INHERITS(measurement);
CREATETABLEmeasurement_yy05mm12(
CHECK(logdate>=DATE'2005-12-01'ANDlogdate<DATE'2006-01-01')
)INHERITS(measurement);
CREATETABLEmeasurement_yy06mm01(
CHECK(logdate>=DATE'2006-01-01'ANDlogdate<DATE'2006-02-01')
)INHERITS(measurement);
4).尽可能基于键值创建索引。如果需要，我们也同样可以为子表中的其它字段创建索引。
CREATEINDEXmeasurement_yy04mm02_logdateONmeasurement_yy04mm02(logdate); CREATEINDEXmeasurement_yy04mm03_logdateONmeasurement_yy04mm03(logdate); ... CREATEINDEXmeasurement_yy05mm11_logdateONmeasurement_yy05mm11(logdate); CREATEINDEXmeasurement_yy05mm12_logdateONmeasurement_yy05mm12(logdate); CREATEINDEXmeasurement_yy06mm01_logdateONmeasurement_yy06mm01(logdate);
5).定义一个规则或者触发器，把对主表的修改重定向到适当的分区表。
如果数据只进入最新的分区，我们可以设置一个非常简单的规则来插入数据。我们必须每个月都重新定义这个规则，即修改重定向插入的子表名，这样它总是指向当前分区。
CREATEORREPLACERULEmeasurement_current_partitionAS ONINSERTTOmeasurement DOINSTEAD INSERTINTOmeasurement_yy06mm01VALUES(NEW.city_id,NEW.logdate,NEW.peaktemp);
其中NEW是关键字，表示新数据字段的集合。这里可以通过点(.)操作符来获取集合中的每一个字段。
我们可能想插入数据并且想让服务器自动定位应该向哪个分区插入数据。我们可以用像下面这样的更复杂的规则集来实现这个目标。
CREATERULEmeasurement_insert_yy04mm02AS ONINSERTTOmeasurementWHERE(logdate>=DATE'2004-02-01'ANDlogdate<DATE'2004-03-01') DOINSTEAD INSERTINTOmeasurement_yy04mm02VALUES(NEW.city_id,NEW.logdate,NEW.peaktemp); ... CREATERULEmeasurement_insert_yy05mm12AS ONINSERTTOmeasurementWHERE(logdate>=DATE'2005-12-01'ANDlogdate<DATE'2006-01-01') DOINSTEAD INSERTINTOmeasurement_yy05mm12VALUES(NEW.city_id,NEW.logdate,NEW.peaktemp); CREATERULEmeasurement_insert_yy06mm01AS ONINSERTTOmeasurementWHERE(logdate>=DATE'2006-01-01'ANDlogdate<DATE'2006-02-01') DOINSTEAD INSERTINTOmeasurement_yy06mm01VALUES(NEW.city_id,NEW.logdate,NEW.peaktemp);
请注意每个规则里面的WHERE子句正好匹配其分区的CHECK约束。
可以看出，一个复杂的分区方案可能要求相当多的DDL。在上面的例子里我们需要每个月创建一次新分区，因此写一个脚本自动生成需要的DDL是明智的。除此之外，我们还不难推断出，分区表对于新数据的批量插入操作有一定的抑制，这一点在Oracle中也同样如此。
除了上面介绍的通过Rule的方式重定向主表的数据到各个子表，我们还可以通过触发器的方式来完成此操作，相比于基于Rule的重定向方法，基于触发器的方式可能会带来更好的插入效率，特别是针对非批量插入的情况。然而对于批量插入而言，由于Rule的额外开销是基于表的，而不是基于行的，因此效果会好于触发器方式。另一个需要注意的是，copy操作将会忽略Rules，如果我们想要通过COPY方法来插入数据，你只能将数据直接copy到正确的子表，而不是主表。这种限制对于触发器来说是不会造成任何问题的。基于Rule的重定向方式还存在另外一个问题，就是当插入的数据不在任何子表的约束中时，PostgreSQL也不会报错，而是将数据直接保留在主表中。
6).添加新分区：
这里将介绍两种添加新分区的方式，第一种方法简单且直观，我们只是创建新的子表，同时为其定义新的检查约束，如：
CREATETABLEmeasurement_y2008m02(
CHECK(logdate>=DATE'2008-02-01'ANDlogdate<DATE'2008-03-01')
)INHERITS(measurement);
第二种方法的创建步骤相对繁琐，但更为灵活和实用。见以下四步：
/*创建一个独立的数据表(measurement_y2008m02)，该表在创建时以将来的主表(measurement)为模板，包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/
CREATETABLEmeasurement_y2008m02
(LIKEmeasurementINCLUDINGDEFAULTSINCLUDINGCONSTRAINTS);
/*为该表创建未来作为子表时需要使用的检查约束。*/
ALTERTABLEmeasurement_y2008m02ADDCONSTRAINTy2008m02
CHECK(logdate>=DATE'2008-02-01'ANDlogdate<DATE'2008-03-01');
/*导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后，如有可能，还可以做进一步的数据处理，如数据转换、过滤等。*/
\copymeasurement_y2008m02from'measurement_y2008m02'
/*在适当的时候，或者说在需要的时候，让该表继承主表。*/
ALTERTABLEmeasurement_y2008m02INHERITmeasurement;
7).确保postgresql.conf里的配置参数constraint_exclusion是打开的。没有这个参数，查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。
/>pwd /opt/PostgreSQL/9.1/data />catpostgresql.conf|grep"constraint_exclusion" constraint_exclusion=partition #on,off,orpartition
3.分区和约束排除：
约束排除(Constraintexclusion)是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。比如：
SETconstraint_exclusion=on; SELECTcount(*)FROMmeasurementWHERElogdate>=DATE'2006-01-01';
如果没有约束排除，上面的查询会扫描measurement表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束然后再视图证明该分区不需要被扫描，因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个，它就把该分区从查询规划里排除出去。
你可以使用EXPLAIN命令显示一个规划在constraint_exclusion打开和关闭情况下的不同。用上面方法设置的表的典型的缺省规划是：
SETconstraint_exclusion=off;
EXPLAINSELECTcount(*)FROMmeasurementWHERElogdate>=DATE'2006-01-01';
QUERYPLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=158.66..158.68rows=1width=0)
-> Append (cost=0.00..151.88rows=2715width=0)
-> SeqScanonmeasurement (cost=0.00..30.38rows=543width=0)
Filter:(logdate>='2006-01-01'::date)
-> SeqScanonmeasurement_yy04mm02measurement (cost=0.00..30.38rows=543width=0)
Filter:(logdate>='2006-01-01'::date)
-> SeqScanonmeasurement_yy04mm03measurement (cost=0.00..30.38rows=543width=0)
Filter:(logdate>='2006-01-01'::date)
...
-> SeqScanonmeasurement_yy05mm12measurement (cost=0.00..30.38rows=543width=0)
Filter:(logdate>='2006-01-01'::date)
-> SeqScanonmeasurement_yy06mm01measurement (cost=0.00..30.38rows=543width=0)
Filter:(logdate>='2006-01-01'::date)