PostgreSQL學習手冊(表的繼承和分割槽)

一、表的繼承：

    這個概念對於很多已經熟悉其他資料庫程式設計的開發人員而言會多少有些陌生，然而它的實現方式和設計原理卻是簡單易懂，現在就讓我們從一個簡單的例子開始吧。
    1. 第一個繼承表：
    CREATE TABLE cities (   --父表
        name        text,
        population float,
        altitude     int
    );
    CREATE TABLE capitals ( --子表
        state      char(2)
    ) INHERITS (cities);
    capitals表繼承自cities表的所有屬性。在PostgreSQL裡，一個表可以從零個或多個其它表中繼承屬性，而且一個查詢既可以引用父表中的所有行，也可以引用父表的所有行加上其所有子表的行，其中後者是預設行為。
    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)

    如果希望只從父表中提取資料，則需要在SQL中加入ONLY關鍵字，如：
    MyTest=# SELECT name,altitude FROM ONLY cities WHERE altitude > 500;
       name     | altitude
    -----------+----------
     Las Vegas |     2174
     Mariposa   |     1953
    (2 rows)
    上例中cities前面的"ONLY"關鍵字表示該查詢應該只對cities進行查詢而不包括繼承級別低於cities的表。許多我們已經討論過的命令--SELECT，UPDATE和DELETE--支援這個"ONLY"符號。
    在執行整表資料刪除時，如果直接truncate父表，此時父表和其所有子表的資料均被刪除，如果只是truncate子表，那麼其父表的資料將不會變化，只是子表中的資料被清空。
    MyTest=# TRUNCATE TABLE cities;  --父表和子表的資料均被刪除。
    TRUNCATE TABLE
    MyTest=# SELECT * FROM capitals;
     name | population | altitude | state
    ------+------------+----------+-------
    (0 rows)
    
    2. 確定資料來源：
    有時候你可能想知道某條記錄來自哪個表。在每個表裡我們都有一個系統隱含欄位tableoid，它可以告訴你表的來源：
    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)
    以上的結果只是給出了tableoid，僅僅通過該值，我們還是無法看出實際的表名。要完成此操作，我們就需要和系統表pg_class進行關聯，以通過tableoid欄位從該表中提取實際的表名，見以下查詢：
    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)
    
    3. 資料插入的注意事項：
    繼承並不自動從INSERT或者COPY中向繼承級別中的其它表填充資料。在我們的例子裡，下面的INSERT語句不會成功：
    INSERT INTO cities (name, population, altitude, state) VALUES ('New York', NULL, NULL, 'NY');
    我們可能希望資料被傳遞到capitals表裡面去，但是這是不會發生的：INSERT總是插入明確宣告的那個表。
    
    4. 多表繼承：
    一個表可以從多個父表繼承，這種情況下它擁有父表們的欄位的總和。子表中任意定義的欄位也會加入其中。如果同一個欄位名出現在多個父表中，或者同時出現 在父表和子表的定義裡，那麼這些欄位就會被"融合"，這樣在子表裡面就只有一個這樣的欄位。要想融合，欄位必須是相同的資料型別，否則就會丟擲一個錯誤。 融合的欄位將會擁有它所繼承的欄位的所有約束。
    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);

    5. 繼承和許可權：
    表訪問許可權並不會自動繼承。因此，一個試圖訪問父表的使用者還必須具有訪問它的所有子表的許可權，或者使用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,
    );   
    2). 建立幾個"子"表，每個都從主表上繼承。通常，這些"子"表將不會再增加任何欄位。我們將把子表稱作分割槽，儘管它們就是普通的PostgreSQL表。
    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);
    上面建立的子表，均已年、月的形式進行範圍劃分，不同年月的資料將歸屬到不同的子表內。這樣的實現方式對於清空分割槽資料而言將極為方便和高效，即直接執 行DROP TABLE語句刪除相應的子表，之後在根據實際的應用考慮是否重建該子表(分割槽)。相比於直接DROP子表，PostgreSQL還提供了另外一種更為方 便的方式來管理子表：
    ALTER TABLE measurement_yy06mm01 NO INHERIT measurement;
    和直接DROP相比，該方式僅僅是使子表脫離了原有的主表，而儲存在子表中的資料仍然可以得到訪問，因為此時該表已經被還原成一個普通的資料表了。這樣對 於資料庫的DBA來說，就可以在此時對該表進行必要的維護操作，如資料清理、歸檔等，在完成諸多例行性的操作之後，就可以考慮是直接刪除該表(DROP TABLE)，還是先清空該表的資料(TRUNCATE TABLE)，之後再讓該表重新繼承主表，如：
    ALTER TABLE measurement_yy06mm01 INHERIT measurement;
    3). 給分割槽表增加約束，定義每個分割槽允許的健值。同時需要注意的是，定義的約束要確保在不同的分割槽裡不會有相同的鍵值。因此，我們需要將上面"子"表的定義修改為以下形式：
    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);   
    4). 儘可能基於鍵值建立索引。如果需要，我們也同樣可以為子表中的其它欄位建立索引。
    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);   
    5). 定義一個規則或者觸發器，把對主表的修改重定向到適當的分割槽表。
    如果資料只進入最新的分割槽，我們可以設定一個非常簡單的規則來插入資料。我們必須每個月都重新定義這個規則，即修改重定向插入的子表名，這樣它總是指向當前分割槽。
    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);
    其中NEW是關鍵字，表示新資料欄位的集合。這裡可以通過點(.)操作符來獲取集合中的每一個欄位。
    我們可能想插入資料並且想讓伺服器自動定位應該向哪個分割槽插入資料。我們可以用像下面這樣的更復雜的規則集來實現這個目標。
    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);    
    請注意每個規則裡面的WHERE子句正好匹配其分割槽的CHECK約束。
    可以看出，一個複雜的分割槽方案可能要求相當多的DDL。在上面的例子裡我們需要每個月建立一次新分割槽，因此寫一個指令碼自動生成需要的DDL是明智的。除 此之外，我們還不難推斷出，分割槽表對於新資料的批量插入操作有一定的抑制，這一點在Oracle中也同樣如此。 
    除了上面介紹的通過Rule的方式重定向主表的資料到各個子表，我們還可以通過觸發器的方式來完成此操作，相比於基於Rule的重定向方法，基於觸發器的 方式可能會帶來更好的插入效率，特別是針對非批量插入的情況。然而對於批量插入而言，由於Rule的額外開銷是基於表的，而不是基於行的，因此效果會好於 觸發器方式。另一個需要注意的是，copy操作將會忽略Rules，如果我們想要通過COPY方法來插入資料，你只能將資料直接copy到正確的子表，而 不是主表。這種限制對於觸發器來說是不會造成任何問題的。基於Rule的重定向方式還存在另外一個問題，就是當插入的資料不在任何子表的約束中 時，PostgreSQL也不會報錯，而是將資料直接保留在主表中。
    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;
    7). 確保postgresql.conf裡的配置引數constraint_exclusion是開啟的。沒有這個引數，查詢不會按照需要進行優化。這裡我們需要做的是確保該選項在配置檔案中沒有被註釋掉。
    /> pwd
    /opt/PostgreSQL/9.1/data
    /> cat postgresql.conf | grep "constraint_exclusion"
    constraint_exclusion = partition        # on, off, or partition

    3. 分割槽和約束排除：
    約束排除(Constraint exclusion)是一種查詢優化技巧，它改進了用上面方法定義的表分割槽的效能。比如：
    SET constraint_exclusion = on;
    SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
    如果沒有約束排除，上面的查詢會掃描measurement表中的每一個分割槽。開啟了約束排除之後，規劃器將檢查每個分割槽的約束然後再檢視證明該分割槽不 需要被掃描，因為它不能包含任何符合WHERE子句條件的資料行。如果規劃器可以證明這個，它就把該分割槽從查詢規劃裡排除出去。
    你可以使用EXPLAIN命令顯示一個規劃在constraint_exclusion開啟和關閉情況下的不同。用上面方法設定的表的典型的預設規劃是：    
    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)

    從上面的查詢計劃中可以看出，PostgreSQL掃描了所有分割槽。下面我們再看一下開啟約束排除之後的查詢計劃：
    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)
    請注意，約束排除只由CHECK約束驅動，而不會由索引驅動。
    目前版本的PostgreSQL中該配置的預設值是partition，該值是介於on和off之間的一種行為方式，即規劃器只會將約束排除應用於基於分割槽表的查詢，而on設定則會為所有查詢都進行約束排除，那麼對於普通資料表而言，也將不得不承擔由該機制而產生的額外開銷。
    
    約束排除在使用時有以下幾點注意事項：
    1). 約束排除只是在查詢的WHERE子句包含約束的時候才生效。一個引數化的查詢不會被優化，因為在執行時規劃器不知道該引數會選擇哪個分割槽。因此像CURRENT_DATE這樣的函式必須避免。把分割槽鍵值和另外一個表的欄位連線起來也不會得到優化。
    2). 在CHECK約束裡面要避免跨資料型別的比較，因為目前規劃器會無法證明這樣的條件為假。比如，下面的約束會在x是整數字段的時候可用，但是在x是一個bigint的時候不能用：
    CHECK (x = 1)
    對於bigint欄位，我們必須使用類似下面這樣的約束：
    CHECK (x = 1::bigint)
    這個問題並不僅僅侷限於bigint資料型別，它可能會發生在任何約束的預設資料型別與其比較的欄位的資料型別不匹配的場合。在提交的查詢裡的跨資料型別的比較通常是OK的，只是不能在CHECK條件裡。
    3). 在主表上的UPDATE和DELETE命令並不執行約束排除。
    4). 在規劃器進行約束排除時，主表上的所有分割槽的所有約束都將會被檢查，因此，大量的分割槽會顯著增加查詢規劃的時間。
    5). 在執行ANALYZE語句時，要為每一個分割槽都執行該命令，而不是僅僅對主表執行該命令。