11. 表的繼承和分區之介紹 · postgresql教程

# 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) 完結。