如何建立和使用分區表 - AnalyticDB

將大表定義為分區表，從而將其分成較小的儲存單元，根據查詢條件，會只掃描滿足條件的分區而避免全表掃描，從而顯著提升查詢效能。

分區表使用

AnalyticDB PostgreSQL支援的分區表類型包括範圍（Range）分區、值（List）分區和多級分區表，下圖為一個多級分區表設計執行個體，一級分區採用按月的區間（Range）分區，二級分區採用按地區的值（List）分區設計。

分區表使用

使用情境

是否使用分區表，可以通過以下幾個方面進行考慮：

表資料量是否足夠大：通常對於大的事實表，比如資料量有幾千萬或者過億，可以考慮使用分區表，但資料量大小並沒有一個絕對的標準可以使用，一般是根據經驗，以及對目前效能是否滿意。
表是否有合適的分區欄位：如果資料量足夠大了，這個時候需要看下是否有合適的欄位能夠用來分區，通常如果資料有時間維度，比如按天，按月等，是比較理想的分區欄位。
表內資料是否具有生命週期：通常數倉中的資料不可能一直存放，一般都會有一定的生命週期，比如最近一年等，這裡就涉及到對舊資料的管理，如果有分區表，就很容易刪除舊的資料。
查詢語句中是否含有分區欄位：如果對一個表做了分區，但是所有的查詢都不帶分區欄位，這不僅無法提高效能反而會使效能下降，因為所有的查詢都會掃描所有的分區表。

建立範圍（RANGE）分區表

您可以通過給出一個START值、一個END值以及一個定義分區增量值的子句讓資料庫自動產生分區。預設情況下，START值總是被包括在內而END值總是被排除在外，例如：

CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
   END (date '2017-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 day') );

也可以建立一個按數字定界分割的表，使用單個數字資料類型列作為分區鍵列，例如：

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1), 
  DEFAULT PARTITION extra );

建立值（LIST）分區表

一個按列表分區的表可以使用任意允許等值比較的資料類型列作為它的分區鍵列。對於列表分區，您必須為每一個使用者想要建立的分區（列表值）聲明一個分區說明，例如：

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int ) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'), 
  PARTITION boys VALUES ('M'), 
  DEFAULT PARTITION other );

建立多級分區表

支援建立多級的分區表。下述建表語句建立了具有三級表分區的表。一級分區在month欄位上做RANGE分區，二級分區在region上做了LIST分區。

CREATE TABLE sales
  (id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (month)
  SUBPARTITION BY LIST (region)
    SUBPARTITION TEMPLATE (
    SUBPARTITION usa VALUES ('usa'),
    SUBPARTITION europe VALUES ('europe'),
    SUBPARTITION asia VALUES ('asia'),
    DEFAULT SUBPARTITION other_regions)
(START (1) END (13) EVERY (1), 
DEFAULT PARTITION other_months );

分區表查詢最佳化

分區粒度
通常分區表的定義都涉及到粒度問題，比如按時間分區，究竟是按天，按周，按月等。粒度越細，每張表的資料就越少，但是分區的數量就越多，反之亦然。關於分區的數量，沒有絕對的標準，一般分區的數量在200左右已經算是比較多了。分區表數目過多，會有多方面的影響，比如查詢最佳化工具產生執行計畫較慢，同時很多維護工作也會變慢，比如VACUUM等。
對於多級分區表來說，分區檔案的數量可能會增長得非常快。例如，如果一個表被按照月和城市劃分並且有24個月以及100個城市，那麼表分區的總數就是2400。特別對於列存表，會把每一列存在一個物理表中，因此如果這個表有100個列，系統就需要為該表管理十多萬個檔案。因此，在分區表設計之初首先需要考慮未來分區的總數，進而選擇合理的分區定義。
分區裁剪
雲原生資料倉儲AnalyticDB PostgreSQL版支援分區表的分區裁剪功能。詳細資料，請參見分區裁剪。

分區表維護

分區表支援多種分區管理操作，包括新增分區，刪除分區，重新命名分區，清空分區，交換分區，分裂分區等，下面舉例說明主要操作。

新增分區
如果存在default partition，則不能新增分區，只能split default partition。
```
ALTER TABLE test_partition_range ADD partition p2 start ('2017-02-01') end ('2017-02-28');
```

刪除分區

ALTER TABLE test_partition_range DROP partition p2;

重新命名分區

ALTER TABLE test_partition_range RENAME PARTITION p2 TO Feb17;

清空分區

ALTER TABLE test_range_partition TRUNCATE PARTITION p1;

交換分區

ALTER TABLE test_range_partition EXCHANGE PARTITION p2 WITH  TABLE {cos_table_name} ;

分裂分區

-- 將分區p2 在 '2017-02-20' 左右切分成兩塊
ALTER TABLE test_partition_range SPLIT partition p2 at ('2017-02-20') into (partition p2, partition p3);