原創(chuàng)文章数初，首發(fā)自作者個人博客Jason's Blog。
　　轉(zhuǎn)載請務(wù)必在文章開頭處注明出自Jason's Blog作煌，并給出原文鏈接http://www.jasongj.com/2015/12/13/SQL3_partition/

典型使用場景

隨著使用時間的增加掘殴，數(shù)據(jù)庫中的數(shù)據(jù)量也不斷增加，因此數(shù)據(jù)庫查詢越來越慢最疆。
　　加速數(shù)據(jù)庫的方法很多，如添加特定的索引努酸，將日志目錄換到單獨的磁盤分區(qū)服爷，調(diào)整數(shù)據(jù)庫引擎的參數(shù)等。這些方法都能將數(shù)據(jù)庫的查詢性能提高到一定程度笼踩。

對于許多應(yīng)用數(shù)據(jù)庫來說挟冠，許多數(shù)據(jù)是歷史數(shù)據(jù)并且隨著時間的推移它們的重要性逐漸降低肋僧。如果能找到一個辦法將這些可能不太重要的數(shù)據(jù)隱藏辫诅，數(shù)據(jù)庫查詢速度將會大幅提高泥栖。可以通過DELETE來達到此目的，但同時這些數(shù)據(jù)就永遠不可用了。
　　因此趾盐，需要一個高效的把歷史數(shù)據(jù)從當前查詢中隱藏起來并且不造成數(shù)據(jù)丟失的方法久窟。本文即將介紹的數(shù)據(jù)庫表分區(qū)即能達到此效果稀颁。

數(shù)據(jù)庫表分區(qū)介紹

數(shù)據(jù)庫表分區(qū)把一個大的物理表分成若干個小的物理表，并使得這些小物理表在邏輯上可以被當成一張表來使用窜锯。

Table partitioning architecture

數(shù)據(jù)庫表分區(qū)術(shù)語介紹

• 主表 / 父表 / Master Table　該表是創(chuàng)建子表的模板馁启。它是一個正常的普通表妖啥，但正常情況下它并不儲存任何數(shù)據(jù)怀读。
• 子表 / 分區(qū)表 / Child Table / Partition Table　這些表繼承并屬于一個主表菜枷。子表中存儲所有的數(shù)據(jù)枫耳。主表與分區(qū)表屬于一對多的關(guān)系狐史，也就是說楼咳，一個主表包含多個分區(qū)表，而一個分區(qū)表只從屬于一個主表

數(shù)據(jù)庫表分區(qū)的優(yōu)勢

• 在特定場景下缕陕，查詢性能極大提高铐然，尤其是當大部分經(jīng)常訪問的數(shù)據(jù)記錄在一個或少數(shù)幾個分區(qū)表上時自点。表分區(qū)減小了索引的大小嫁怀，并使得常訪問的分區(qū)表的索引更容易保存于內(nèi)存中。
• 當查詢或者更新訪問一個或少數(shù)幾個分區(qū)表中的大部分數(shù)據(jù)時橡淆，可以通過順序掃描該分區(qū)表而非使用大表索引來提高性能。
• 可通過添加或移除分區(qū)表來高效的批量增刪數(shù)據(jù)。如可使用ALTER TABLE NO INHERIT可將特定分區(qū)從主邏輯表中移除（該表依然存在，并可單獨使用揍移，只是與主表不再有繼承關(guān)系并無法再通過主表訪問該分區(qū)表）燃少，或使用DROP TABLE直接將該分區(qū)表刪除。這兩種方式完全避免了使用DELETE時所需的VACUUM額外代價铃在。
• 很少使用的數(shù)據(jù)可被遷移到便宜些的慢些的存儲介質(zhì)中

以上優(yōu)勢只有當表非常大的時候才能體現(xiàn)出來阵具。一般來說，當表的大小超過數(shù)據(jù)庫服務(wù)器的物理內(nèi)存時以上優(yōu)勢才能體現(xiàn)出來

PostgreSQL表分區(qū)

現(xiàn)在PostgreSQL支持通過表繼承來實現(xiàn)表的分區(qū)定铜。父表是普通表并且正常情況下并不存儲任何數(shù)據(jù)阳液，它的存在只是為了代表整個數(shù)據(jù)集。PostgreSQL可實現(xiàn)如下兩種表分區(qū)

• 范圍分區(qū)　每個分區(qū)表包含一個或多個字段組合的一部分揣炕，并且每個分區(qū)表的范圍互不重疊帘皿。比如可近日期范圍分區(qū)
• 列表分區(qū)　分區(qū)表顯示列出其所包含的key值

表分區(qū)在PostgreSQL上的實現(xiàn)

在PostgreSQL中實現(xiàn)表分區(qū)的步驟

1. 創(chuàng)建主表。不用為該表定義任何檢查限制畸陡，除非需要將該限制應(yīng)用到所有的分區(qū)表中鹰溜。同樣也無需為該表創(chuàng)建任何索引和唯一限制虽填。

CREATE TABLE almart
(
    date_key date,
    hour_key smallint,
    client_key integer,
    item_key integer,
    account integer,
    expense numeric
);

2. 創(chuàng)建多個分區(qū)表。每個分區(qū)表必須繼承自主表曹动，并且正常情況下都不要為這些分區(qū)表添加任何新的列斋日。

CREATE TABLE almart_2015_12_10 () inherits (almart);
CREATE TABLE almart_2015_12_11 () inherits (almart);
CREATE TABLE almart_2015_12_12 () inherits (almart);
CREATE TABLE almart_2015_12_13 () inherits (almart);

3. 為分區(qū)表添加限制。這些限制決定了該表所能允許保存的數(shù)據(jù)集范圍墓陈。這里必須保證各個分區(qū)表之間的限制不能有重疊恶守。

ALTER TABLE almart_2015_12_10
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-10'::date);
ALTER TABLE almart_2015_12_11
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-11'::date);
ALTER TABLE almart_2015_12_12
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-12'::date);
ALTER TABLE almart_2015_12_13
ADD CONSTRAINT almart_2015_12_10_check_date_key
CHECK (date_Key = '2015-12-13'::date);

4. 為每一個分區(qū)表，在主要的列上創(chuàng)建索引贡必。該索引并不是嚴格必須創(chuàng)建的兔港，但在大部分場景下，它都非常有用赊级。

CREATE INDEX almart_date_key_2015_12_10
ON almart_2015_12_10 (date_key);
CREATE INDEX almart_date_key_2015_12_11
ON almart_2015_12_11 (date_key);
CREATE INDEX almart_date_key_2015_12_12
ON almart_2015_12_12 (date_key);
CREATE INDEX almart_date_key_2015_12_13
ON almart_2015_12_13 (date_key);

5. 定義一個trigger或者rule把對主表的數(shù)據(jù)插入操作重定向到對應(yīng)的分區(qū)表押框。

--創(chuàng)建分區(qū)函數(shù)
CREATE OR REPLACE FUNCTION almart_partition_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_key = DATE '2015-12-10'
    THEN
        INSERT INTO almart_2015_12_10 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-11'
    THEN
        INSERT INTO almart_2015_12_11 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-12'
    THEN
        INSERT INTO almart_2015_12_12 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-13'
    THEN
        INSERT INTO almart_2015_12_13 VALUES (NEW.*);
    ELSIF NEW.date_key = DATE '2015-12-14'
    THEN
        INSERT INTO almart_2015_12_14 VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
--掛載分區(qū)Trigger
CREATE TRIGGER insert_almart_partition_trigger
BEFORE INSERT ON almart
FOR EACH ROW EXECUTE PROCEDURE almart_partition_trigger();

6. 確保postgresql.conf中的constraint_exclusion配置項沒有被disable。這一點非常重要理逊，如果該參數(shù)項被disable橡伞，則基于分區(qū)表的查詢性能無法得到優(yōu)化，甚至比不使用分區(qū)表直接使用索引性能更低晋被。

表分區(qū)如何加速查詢優(yōu)化

當constraint_exclusion為on或者partition時兑徘，查詢計劃器會根據(jù)分區(qū)表的檢查限制將對主表的查詢限制在符合檢查限制條件的分區(qū)表上，直接避免了對不符合條件的分區(qū)表的掃描羡洛。
　　為了驗證分區(qū)表的優(yōu)勢挂脑，這里創(chuàng)建一個與上文創(chuàng)建的almart結(jié)構(gòu)一樣的表almart_all，并為其date_key創(chuàng)建索引欲侮，向almart和almart_all中插入同樣的9000萬條數(shù)據(jù)（數(shù)據(jù)的時間跨度為2015-12-01到2015-12-30）崭闲。

CREATE TABLE almart_all
(
    date_key date,
    hour_key smallint,
    client_key integer,
    item_key integer,
    account integer,
    expense numeric
);

插入隨機測試數(shù)據(jù)到almart_all

INSERT INTO
    almart_all
select
    (select
        array_agg(i::date)
    from
        generate_series(
        '2015-12-01'::date,
        '2015-12-30'::date,
        '1 day'::interval) as t(i)
    )[floor(random()*4)+1] as date_key,
    floor(random()*24) as hour_key,
    floor(random()*1000000)+1 as client_key,
    floor(random()*100000)+1 as item_key,
    floor(random()*20)+1 as account,
    floor(random()*10000)+1 as expense
from
    generate_series(1,300000000,1);

插入同樣的測試數(shù)據(jù)到almart

INSERT INTO almart SELECT * FROM almart_all;

在almart和slmart_all上執(zhí)行同樣的query，查詢2015-12-15日不同client_key的平均消費額威蕉。

\timing
explain analyze
select
    avg(expense)
from
    (select
        client_key,
        sum(expense) as expense
    from
        almart
    where
        date_key = date '2015-12-15'
    group by 1
    )刁俭；
                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19449.05..19449.06 rows=1 width=32) (actual time=9474.203..9474.203 rows=1 loops=1)
   ->  HashAggregate  (cost=19196.10..19308.52 rows=11242 width=36) (actual time=8632.592..9114.973 rows=949825 loops=1)
         ->  Append  (cost=0.00..19139.89 rows=11242 width=36) (actual time=4594.262..6091.630 rows=2997704 loops=1)
               ->  Seq Scan on almart  (cost=0.00..0.00 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=1)
                     Filter: (date_key = '2015-12-15'::date)
               ->  Bitmap Heap Scan on almart_2015_12_15  (cost=299.55..19139.89 rows=11241 width=36) (actual time=4594.258..5842.708 rows=2997704 loops=1)
                     Recheck Cond: (date_key = '2015-12-15'::date)
                     ->  Bitmap Index Scan on almart_date_key_2015_12_15  (cost=0.00..296.74 rows=11241 width=0) (actual time=4587.582..4587.582 rows=2997704 loops=1)
                           Index Cond: (date_key = '2015-12-15'::date)
 Total runtime: 9506.507 ms
(10 rows)

Time: 9692.352 ms

explain analyze
select
    avg(expense)
from
    (select
        client_key,
        sum(expense) as expense
    from
        almart_all
    where
        date_key = date '2015-12-15'
    group by 1
    ) foo；
                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=770294.11..770294.12 rows=1 width=32) (actual time=62959.917..62959.917 rows=1 loops=1)
   ->  HashAggregate  (cost=769549.54..769880.46 rows=33092 width=9) (actual time=61694.564..62574.385 rows=949825 loops=1)
         ->  Bitmap Heap Scan on almart_all  (cost=55704.56..754669.55 rows=2975999 width=9) (actual time=919.941..56291.128 rows=2997704 loops=1)
               Recheck Cond: (date_key = '2015-12-15'::date)
               ->  Bitmap Index Scan on almart_all_date_key_index  (cost=0.00..54960.56 rows=2975999 width=0) (actual time=677.741..677.741 rows=2997704 loops=1)
                     Index Cond: (date_key = '2015-12-15'::date)
 Total runtime: 62960.228 ms
(7 rows)

Time: 62970.269 ms

由上可見韧涨，使用分區(qū)表時牍戚，所需時間為9.5秒，而不使用分區(qū)表時虑粥，耗時63秒如孝。
　　使用分區(qū)表，PostgreSQL跳過了除2015-12-15日分區(qū)表以外的分區(qū)表娩贷，只掃描2015-12-15的分區(qū)表第晰。而不使用分區(qū)表只使用索引時，數(shù)據(jù)庫要使用索引掃描整個數(shù)據(jù)庫。另一方面但荤，使用分區(qū)表時罗岖，每個表的索引是獨立的，即每個分區(qū)表的索引都只針對一個小的分區(qū)表腹躁。而不使用分區(qū)表時，索引是建立在整個大表上的南蓬。數(shù)據(jù)量越大纺非，索引的速度相對越慢。

管理分區(qū)

從上文分區(qū)表的創(chuàng)建過程可以看出赘方，分區(qū)表必須在相關(guān)數(shù)據(jù)插入之前創(chuàng)建好烧颖。在生產(chǎn)環(huán)境中，很難保證所需的分區(qū)表都已經(jīng)被提前創(chuàng)建好窄陡。同時為了不讓分區(qū)表過多炕淮，影響數(shù)據(jù)庫性能，不能創(chuàng)建過多無用的分區(qū)表跳夭。

周期性創(chuàng)建分區(qū)表

在生產(chǎn)環(huán)境中涂圆，經(jīng)常需要周期性刪除和創(chuàng)建一些分區(qū)表。一個經(jīng)典的做法是使用定時任務(wù)币叹。比如使用cronjob每天運行一次润歉，將1年前的分區(qū)表刪除，并創(chuàng)建第二天分區(qū)表（該表按天分區(qū)）颈抚。有時為了容錯踩衩，會將之后一周的分區(qū)表全部創(chuàng)建出來。

動態(tài)創(chuàng)建分區(qū)表

上述周期性創(chuàng)建分區(qū)表的方法在絕大部分情況下有效贩汉，但也只能在一定程度上容錯驱富。另外，上文所使用的分區(qū)函數(shù)匹舞，使用IF語句對date_key進行判斷褐鸥，需要為每一個分區(qū)表準備一個IF語句。
　　如插入date_key分別為2015-12-10到2015-12-14的5條記錄策菜，前面4條均可插入成功晶疼，因為相應(yīng)的分區(qū)表已經(jīng)存在，但最后一條數(shù)據(jù)因為相應(yīng)的分區(qū)表不存在而插入失敗又憨。

INSERT INTO almart(date_key) VALUES ('2015-12-10');
INSERT 0 0
INSERT INTO almart(date_key) VALUES ('2015-12-11');
INSERT 0 0
INSERT INTO almart(date_key) VALUES ('2015-12-12');
INSERT 0 0
INSERT INTO almart(date_key) VALUES ('2015-12-13');
INSERT 0 0
INSERT INTO almart(date_key) VALUES ('2015-12-14');
ERROR:  relation "almart_2015_12_14" does not exist
LINE 1: INSERT INTO almart_2015_12_14 VALUES (NEW.*)
                    ^
QUERY:  INSERT INTO almart_2015_12_14 VALUES (NEW.*)
CONTEXT:  PL/pgSQL function almart_partition_trigger() line 17 at SQL statement

SELECT * FROM almart;
  date_key  | hour_key | client_key | item_key | account | expense
------------+----------+------------+----------+---------+---------
 2015-12-10 |          |            |          |         |
 2015-12-11 |          |            |          |         |
 2015-12-12 |          |            |          |         |
 2015-12-13 |          |            |          |         |
(4 rows)

針對該問題翠霍，可使用動態(tài)SQL的方式進行數(shù)據(jù)路由，并通過獲取將數(shù)據(jù)插入不存在的分區(qū)表產(chǎn)生的異常消息并動態(tài)創(chuàng)建分區(qū)表的方式保證分區(qū)表的可用性蠢莺。

CREATE OR REPLACE FUNCTION almart_partition_trigger()
RETURNS TRIGGER AS $$
DECLARE date_text TEXT;
DECLARE insert_statement TEXT;
BEGIN
    SELECT to_char(NEW.date_key, 'YYYY_MM_DD') INTO date_text;
    insert_statement := 'INSERT INTO almart_'
        || date_text
        ||' VALUES ($1.*)';
    EXECUTE insert_statement USING NEW;
    RETURN NULL;
    EXCEPTION
    WHEN UNDEFINED_TABLE
    THEN
        EXECUTE
            'CREATE TABLE IF NOT EXISTS almart_'
            || date_text
            || '(CHECK (date_key = '''
            || date_text
            || ''')) INHERITS (almart)';
        RAISE NOTICE 'CREATE NON-EXISTANT TABLE almart_%', date_text;
        EXECUTE
            'CREATE INDEX almart_date_key_'
            || date_text
            || ' ON almart_'
            || date_text
            || '(date_key)';
        EXECUTE insert_statement USING NEW;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

使用該方法后寒匙，再次插入date_key為2015-12-14的記錄時，對應(yīng)的分區(qū)表不存在，但會被自動創(chuàng)建锄弱。

INSERT INTO almart VALUES('2015-12-13'),('2015-12-14'),('2015-12-15');
NOTICE:  CREATE NON-EXISTANT TABLE almart_2015_12_14
NOTICE:  CREATE NON-EXISTANT TABLE almart_2015_12_15
INSERT 0 0
SELECT * FROM almart;
  date_key  | hour_key | client_key | item_key | account | expense
------------+----------+------------+----------+---------+---------
 2015-12-10 |          |            |          |         |
 2015-12-11 |          |            |          |         |
 2015-12-12 |          |            |          |         |
 2015-12-13 |          |            |          |         |
 2015-12-13 |          |            |          |         |
 2015-12-14 |          |            |          |         |
 2015-12-15 |          |            |          |         |
(7 rows)

移除分區(qū)表

雖然如上文所述考蕾，分區(qū)表的使用可以跳過掃描不必要的分區(qū)表從而提高查詢速度。但由于服務(wù)器磁盤的限制会宪，不可能無限制存儲所有數(shù)據(jù)肖卧，經(jīng)常需要周期性刪除過期數(shù)據(jù)，如刪除5年前的數(shù)據(jù)掸鹅。如果使用傳統(tǒng)的DELETE塞帐，刪除速度慢，并且由于DELETE只是將相應(yīng)數(shù)據(jù)標記為刪除狀態(tài)巍沙，不會將數(shù)據(jù)從磁盤刪除，需要使用VACUUM釋放磁盤句携，從而引入額外負載榔幸。
　　而在使用分區(qū)表的條件下矮嫉，可以通過直接DROP過期分區(qū)表的方式快速方便地移除過期數(shù)據(jù)敞临。如

DROP TABLE almart_2014_12_15;

另外态辛，無論使用DELETE還是DROP，都會將數(shù)據(jù)完全刪除挺尿，即使有需要也無法再次使用奏黑。因此還有另外一種方式，即更改過期的分區(qū)表编矾，解除其與主表的繼承關(guān)系熟史，如。

ALTER TABLE almart_2015_12_15 NO INHERIT almart;

但該方法并未釋放磁盤窄俏。此時可通過更改該分區(qū)表蹂匹，使其屬于其它TABLESPACE，同時將該TABLESPACE的目錄設(shè)置為其它磁盤分區(qū)上的目錄凹蜈，從而釋放主表所在的磁盤限寞。同時，如果之后還需要再次使用該“過期”數(shù)據(jù)仰坦，只需更改該分區(qū)表履植，使其再次與主表形成繼承關(guān)系。

CREATE TABLESPACE cheap_table_space LOCATION '/data/cheap_disk';
ALTER TABLE almart_2014_12_15 SET TABLESPACE cheap_table_space;

PostgreSQL表分區(qū)的其它方式

除了使用Trigger外悄晃，可以使用Rule將對主表的插入請求重定向到對應(yīng)的子表玫霎。如

CREATE RULE almart_rule_2015_12_31 AS
ON INSERT TO almart
WHERE
    date_key = DATE '2015-12-31'
DO INSTEAD
    INSERT INTO almart_2015_12_31 VALUES (NEW.*);

與Trigger相比，Rule會帶來更大的額外開銷，但每個請求只造成一次開銷而非每條數(shù)據(jù)都引入一次開銷庶近，所以該方法對大批量的數(shù)據(jù)插入操作更具優(yōu)勢翁脆。然而，實際上在絕大部分場景下鼻种，Trigger比Rule的效率更高反番。

同時，COPY操作會忽略Rule叉钥，而可以正常觸發(fā)Trigger恬口。

另外，如果使用Rule方式沼侣，沒有比較簡單的方法處理沒有被Rule覆蓋到的插入操作。此時該數(shù)據(jù)會被插入到主表中而不會報錯歉秫，從而無法有效利用表分區(qū)的優(yōu)勢蛾洛。

除了使用表繼承外，還可使用UNION ALL的方式達到表分區(qū)的效果雁芙。

CREATE VIEW almart AS
SELECT * FROM almart_2015_12_10
UNION ALL
SELECT * FROM almart_2015_12_11
UNION ALL
SELECT * FROM almart_2015_12_12
...
UNION ALL
SELECT * FROM almart_2015_12_30;

當有新的分區(qū)表時轧膘，需要更新該View。實踐中兔甘，與使用表繼承相比谎碍，一般不推薦使用該方法。

總結(jié)

• 如果要充分使用分區(qū)表的查詢優(yōu)勢洞焙，必須使用分區(qū)時的字段作為過濾條件
• 分區(qū)字段被用作過濾條件時蟆淀，WHERE語句只能包含常量而不能使用參數(shù)化的表達式，因為這些表達式只有在運行時才能確定其值澡匪，而planner在真正執(zhí)行query之前無法判定哪些分區(qū)表應(yīng)該被使用
• 跳過不符合條件分區(qū)表是通過planner根據(jù)分區(qū)表的檢查限制條件實現(xiàn)的熔任，而非通過索引
• 必須將constraint_exclusion設(shè)置為ON或Partition，否則planner將無法正常跳過不符合條件的分區(qū)表唁情，也即無法發(fā)揮表分區(qū)的優(yōu)勢
• 除了在查詢上的優(yōu)勢疑苔，分區(qū)表的使用，也可提高刪除舊數(shù)據(jù)的性能
• 為了充分利用分區(qū)表的優(yōu)勢甸鸟，應(yīng)該保證各分區(qū)表的檢查限制條件互斥惦费，但目前并無自動化的方式來保證這一點。因此使用代碼造化創(chuàng)建或者修改分區(qū)表比手工操作更安全
• 在更新數(shù)據(jù)集時抢韭，如果使得partition key column(s)變化到需要使某些數(shù)據(jù)移動到其它分區(qū)薪贫，則該更新操作會因為檢查限制的存在而失敗。如果一定要處理這種情景篮绰，可以使用更新Trigger后雷，但這會使得結(jié)構(gòu)變得復(fù)雜。
• 大量的分區(qū)表會極大地增加查詢計劃時間。表分區(qū)在多達幾百個分區(qū)表時能很好地發(fā)揮優(yōu)勢臀突，但不要使用多達幾千個分區(qū)表勉抓。

SQL優(yōu)化系列

• SQL優(yōu)化（一） Merge Join vs. Hash Join vs. Nested Loop
• SQL優(yōu)化（二） 快速計算Distinct Count
• SQL優(yōu)化（三） PostgreSQL Table Partitioning
• SQL優(yōu)化（四） Postgre Sql存儲過程