一、表的继承:
这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生,然而它的实现方式和设计原理却是简单易懂,现在就让我们从一个简单的例子开始吧。
1. 第一个继承表:
CREATE TABLE cities ( --父表 name text, population float, altitude int ); CREATE TABLE capitals ( --子表 state char(2) ) INHERITS (cities);
MyTest=# INSERT INTO cities values('Las Vegas', 1.53, 2174); --插入父表 INSERT 0 1 MyTest=# INSERT INTO cities values('Mariposa',3.30,1953); --插入父表 INSERT 0 1 MyTest=# INSERT INTO capitals values('Madison',4.34,845,'WI');--插入子表 INSERT 0 1 MyTest=# SELECT name, altitude FROM cities WHERE altitude > 500; --父表和子表的数据均被取出。 name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows) MyTest=# SELECT name, altitude FROM capitals WHERE altitude > 500; --只有子表的数据被取出。 name | altitude ---------+---------- Madison | 845 (1 row)
MyTest=# SELECT name,altitude FROM ONLY cities WHERE altitude > 500; name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows)
MyTest=# TRUNCATE TABLE cities; --父表和子表的数据均被删除。 TRUNCATE TABLE MyTest=# SELECT * FROM capitals; name | population | altitude | state ------+------------+----------+------- (0 rows)
MyTest=# SELECT tableoid, name, altitude FROM cities WHERE altitude > 500; tableoid | name | altitude ----------+-----------+---------- 16532 | Las Vegas | 2174 16532 | Mariposa | 1953 16538 | Madison | 845 (3 rows)
MyTest=# SELECT p.relname, c.name, c.altitude FROM cities c,pg_class p WHERE c.altitude > 500 and c.tableoid = p.oid; relname | name | altitude ----------+-----------+---------- cities | Las Vegas | 2174 cities | Mariposa | 1953 capitals | Madison | 845 (3 rows)
INSERT INTO cities (name, population, altitude, state) VALUES ('New York', NULL, NULL, 'NY');
CREATE TABLE parent1 (FirstCol integer); CREATE TABLE parent2 (FirstCol integer, SecondCol varchar(20)); CREATE TABLE parent3 (FirstCol varchar(200)); --子表child1将同时继承自parent1和parent2表,而这两个父表中均包含integer类型的FirstCol字段,因此child1可以创建成功。 CREATE TABLE child1 (MyCol timestamp) INHERITS (parent1,parent2); --子表child2将不会创建成功,因为其两个父表中均包含FirstCol字段,但是它们的类型不相同。 CREATE TABLE child2 (MyCol timestamp) INHERITS (parent1,parent3); --子表child3同样不会创建成功,因为它和其父表均包含FirstCol字段,但是它们的类型不相同。 CREATE TABLE child3 (FirstCol varchar(20)) INHERITS(parent1);
表访问权限并不会自动继承。因此,一个试图访问父表的用户还必须具有访问它的所有子表的权限,或者使用ONLY关键字只从父表中提取数据。在向现有的继承层次添加新的子表的时候,请注意给它赋予所有权限。
继承特性的一个严重的局限性是索引(包括唯一约束)和外键约束只施用于单个表,而不包括它们的继承的子表。这一点不管对引用表还是被引用表都是事实,因此在上面的例子里,如果我们声明cities.name为UNIQUE或者是一个PRIMARY KEY,那么也不会阻止capitals表拥有重复了名字的cities数据行。 并且这些重复的行缺省时在查询cities表的时候会显示出来。实际上,缺省时capitals将完全没有唯一约束,因此可能包含带有同名的多个行。你应该给capitals增加唯一约束,但是这样做也不会避免与cities的重复。类似,如果我们声明cities.name REFERENCES某些其它的表,这个约束不会自动广播到capitals。在这种条件下,你可以通过手工给capitals 增加同样的REFERENCES约束来做到这点。
二、分区表:
1. 概述分区表:
分区的意思是把逻辑上的一个大表分割成物理上的几块儿,分区可以提供若干好处:
1). 某些类型的查询性能可以得到极大提升。
2). 更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问。
3). 批量删除可以用简单地删除某个分区来实现。
4). 将很少用的数据可以移动到便宜的、慢一些地存储介质上。
假设当前的数据库并不支持分区表,而我们的应用所需处理的数据量也非常大,对于这种应用场景,我们不得不人为的将该大表按照一定的规则,手工拆分成多个小表,让每个小表包含不同区间的数据。这样一来,我们就必须在数据插入、更新、删除和查询之前,先计算本次的指令需要操作的小表。对于有些查询而言,由于查询区间可能会跨越多个小表,这样我们又不得不将多个小表的查询结果进行union操作,以合并来自多个表的数据,并最终形成一个结果集返回给客户端。可见,如果我们正在使用的数据库不支持分区表,那么在适合其应用的场景下,我们就需要做很多额外的编程工作以弥补这一缺失。然而需要说明的是,尽管功能可以勉强应付,但是性能却和分区表无法相提并论。
目前PostgreSQL支持的分区形式主要为以下两种:
1). 范围分区: 表被一个或者多个键字字段分区成"范围",在这些范围之间没有重叠的数值分布到不同的分区里。比如,我们可以为特定的商业对象根据数据范围分区,或者根据标识符范围分区。
2). 列表分区: 表是通过明确地列出每个分区里应该出现那些键字值实现的。
2. 实现分区:
1). 创建"主表",所有分区都从它继承。
CREATE TABLE measurement ( --主表 city_id int NOT NULL, logdate date NOT NULL, peaktemp int, );
CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement); CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement); CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement); CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
ALTER TABLE measurement_yy06mm01 NO INHERIT measurement;
ALTER TABLE measurement_yy06mm01 INHERIT measurement;
CREATE TABLE measurement_yy04mm02 ( CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01') ) INHERITS (measurement); CREATE TABLE measurement_yy04mm03 ( CHECK (logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01') ) INHERITS (measurement); ... CREATE TABLE measurement_yy05mm11 ( CHECK (logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01') ) INHERITS (measurement); CREATE TABLE measurement_yy05mm12 ( CHECK (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01') ) INHERITS (measurement); CREATE TABLE measurement_yy06mm01 ( CHECK (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01') ) INHERITS (measurement);
CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate); CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate); ... CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate); CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate); CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
CREATE OR REPLACE RULE measurement_current_partition AS ON INSERT TO measurement DO INSTEAD INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);
CREATE RULE measurement_insert_yy04mm02 AS ON INSERT TO measurement WHERE (logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01') DO INSTEAD INSERT INTO measurement_yy04mm02 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp); ... CREATE RULE measurement_insert_yy05mm12 AS ON INSERT TO measurement WHERE (logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01') DO INSTEAD INSERT INTO measurement_yy05mm12 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp); CREATE RULE measurement_insert_yy06mm01 AS ON INSERT TO measurement WHERE (logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01') DO INSTEAD INSERT INTO measurement_yy06mm01 VALUES (NEW.city_id, NEW.logdate, NEW.peaktemp);
6). 添加新分区:
这里将介绍两种添加新分区的方式,第一种方法简单且直观,我们只是创建新的子表,同时为其定义新的检查约束,如:
CREATE TABLE measurement_y2008m02 ( CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ) ) INHERITS (measurement);
/* 创建一个独立的数据表(measurement_y2008m02),该表在创建时以将来的主表(measurement)为模板,包含模板表的缺省值(DEFAULTS)和一致性约束(CONSTRAINTS)。*/ CREATE TABLE measurement_y2008m02 (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); /* 为该表创建未来作为子表时需要使用的检查约束。*/ ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02 CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01'); /* 导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后,如有可能,还可以做进一步的数据处理,如数据转换、过滤等。*/ \copy measurement_y2008m02 from 'measurement_y2008m02' /* 在适当的时候,或者说在需要的时候,让该表继承主表。*/ ALTER TABLE measurement_y2008m02 INHERIT measurement;
/> pwd /opt/PostgreSQL/9.1/data /> cat postgresql.conf | grep "constraint_exclusion" constraint_exclusion = partition # on, off, or partition
SET constraint_exclusion = on; SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
SET constraint_exclusion = off; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=158.66..158.68 rows=1 width=0) -> Append (cost=0.00..151.88 rows=2715 width=0) -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date) -> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date) -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date) ... -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date) -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date)
SET constraint_exclusion = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; QUERY PLAN ----------------------------------------------------------------------------------------------- Aggregate (cost=63.47..63.48 rows=1 width=0) -> Append (cost=0.00..60.75 rows=1086 width=0) -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date) -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0) Filter: (logdate >= '2006-01-01'::date)