ThinkChat🤖让你学习和工作更高效,注册即送10W Token,即刻开启你的AI之旅 广告
# 11. 表的继承和分区之介绍 #### 1. 介绍 PostgreSQL的分区是建立在继承的基础上的,所以先来讲讲继承。 #### 2. 继承 [继承](http://www.postgresql.org/docs/9.4/static/tutorial-inheritance.html)指的是表的继承,就是一个表继承自另一个表,字段也继承自父表,跟面向对象的概念差不多。因为有时候几张表就是具有差不多的属性或字段,唯一有区别的就是其中一两个字段,这个时候可以用继承来简化操作和管理。 比如,如果不用继承,会像下面这样处理的。 ``` CREATE TABLE capitals ( name text, population real, altitude int, -- (in ft) state char(2) ); CREATE TABLE non_capitals ( name text, population real, altitude int -- (in ft) ); CREATE VIEW cities AS SELECT name, population, altitude FROM capitals UNION SELECT name, population, altitude FROM non_capitals; ``` 要查找那两张表就得使用union语句。 而使用继承就是这样处理的。 ``` CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities); ``` 这样就创建了两张表,插入(insert)数据之后就可以用select来查询的。 #### 3. 分区 [PostgreSQL-partitioning](http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html)对分区作了完整的描述。 分区是数据库的一种设计实现方法。我们知道,当一张表的数据越来越多时,假如到了上亿条或几十亿条记录,对这张表的操作都会比较慢,比如,查询,更改等。而分区技术就是把这一张大表分成几个逻辑分片。分区之后有很多好处: - 单个分区表的索引和表都变小了,可以保持在内存里面,适合把热数据从大表拆分出来的场景; - 对于大范围的查询,大表可以通过索引来避免全表扫描,但是如果分区的话,可以使用分区的全表扫描; - 大批量的数据导入或删除,删除大量的数据使用DELETE会很慢,可是如果使用分区表,直接drop或truncate整个分区表即可; 而分区技术就是基于上面所提的继承技术来实现的。 PostgreSQL实现了两种分区。 - `Range Partitioning`:比如数值范围,时间范围等。 - `List Partitioning`: 按照固定的值。 #### 4. 实战分区 其中一种实现分区的方法是基于继承并配合触发器来实现。 先创建母表,它其实是一张只有数据结构的表。 ``` CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ); ``` 创建分区表,用时间范围来分区。 ``` CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement); ``` check指定的是约束条件,按照时间来规定范围。 按照需要可以添加索引。 ``` CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); ... CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate); ``` 当执行`INSERT INTO measurement ...`时,为了让数据插入到正确的分区表上,我们需要创建触发器来实现这个逻辑。 ``` CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ... ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; ``` ``` CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger(); ``` 这样就OK了。 另外来实现同样插入逻辑的方式是用rule(规则)。 ``` CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) DO INSTEAD INSERT INTO measurement_y2006m02 VALUES (NEW.*); ... CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement WHERE ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) DO INSTEAD INSERT INTO measurement_y2008m01 VALUES (NEW.*); ``` 具体的详细可以阅读[官方文档](http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html)。 下一篇: [PostgreSQL的表的继承和分区之pg\_partman(二)](http://www.rails365.net/articles/2015-10-14-postgresql-biao-de-ji-cheng-fen-qu-pg-partman-zhi-pg_partman-er) 完结。