一、儲存格式介紹

 

Greenplum（以下簡稱 GP）有2種儲存格式，Heap 表和 AO 表（AORO 表，AOCO 表）。

• Heap 表：這種儲存格式是從 PostgreSQL 繼承而來的，目前是 GP 預設的表儲存格式，只支援行儲存。

• AO 表：  AO 表最初設計是隻支援 append 的（就是隻能 insert ），因此全稱是Append-Only，在4.3之後進行了優化，目前已經可以 update 和 delete 了，全稱也改為 Append-Optimized。AO 支援行儲存（AORO）和列儲存（AOCO）。

 

二、Heap表

 

Heap 表是從 PostgreSQL 繼承而來，使用 MVCC 來實現一致性。如果你在建立表的時候沒有指定任何儲存格式，那麼 GP 就會使用 Heap 表。

Heap 表支援分割槽表，只支援行存，不支援列存和壓縮。需要注意的是在處理 update 和 delete 的時候，Heap 表並沒有真正刪除資料，而只是依靠 version 資訊遮蔽老的資料，因此如果你的表有大量的 update 或者 delete，表佔用的物理空間會不斷增大，這個時候需要依靠 vacuum 來清理老資料。

Heap 表不支援邏輯增量備份，因此如果要對 Heap 表做快照，每次都需要匯出全量資料。

建表語句

CREATE TABLE heap(
	a int,
	b varchar(32)
) DISTRIBUTED BY (a);

 

最佳實踐：

• 如果該表是一張小表，比如數倉中的維度表，或者資料量在百萬以下，推薦使用 Heap 表。

• 如果該表的使用場景是 OLTP 的，比如有較多的 update 和 delete，查詢多是帶索引的點查詢等，推薦使用 Heap 表。

•  

三、AO表

 

AO 表是 GP 特有的，設計的目的就是為了數倉中大型的事實表。AO 表支援行存和列存，並且也支援對資料進行壓縮。

AO 表無論是在表的邏輯結構還是物理結構上，都與 Heap 表有很大的不同。比如上文所述 Heap 表使用 MVCC 控制 update 和 delete 之後資料的可見性，而 AO 表則使用一個附加的 bitmap 表來實現，這個表的的內容就是表示 AO 表中哪些資料是可見的。

對於有大量 update 和 delete 的 AO 表，同樣需要 vacuum 進行維護，不過在 AO 表中， vacuum 需要對 bitmap 進行重置並壓縮物理檔案，因此通常比 Heap 的 vacuum 要慢。

 

AORO表

AORO 就是行存的 AO 表，同時行存也是 AO 表的預設儲存方式。

AORO 支援表級別的壓縮，不支援列級別的壓縮。

建表語句如下，重點是 with 後的 appendonly=true，由於 AO 表預設是行存，因此 orientation=row 也可以不要，後面的 compresstype=zlib, compresslevel=4 都是壓縮相關選項。

CREATE TABLE aoro(
	a int,
	b int,
	c varchar(32),
	d varchar(32)
)
WITH (appendonly=true, orientation=row, compresstype=zlib, compresslevel=4)
DISTRIBUTED BY (a)

 

壓縮選項：

• compresstype ：壓縮格式，開源版本的 AORO 表只支援 zlib。

• compresslevel ：壓縮級別，從1-9，簡單說來，級別越低（1最低），壓縮比越低，但是壓縮與解壓消耗的 cpu 資源就越少。預設壓縮級別是1。

 

最佳實踐____________________：

• AO 表主要是針對大表，比如數倉中的事實表。

• AO 表支援邏輯增量備份，對於比較大的表，如果需要定期做快照，建議使用 AO 表，否則每次都要匯出全量資料。

• 如果該表是大表，使用場景偏 OLTP 並且 update 和 delete 頻率不高，可以考慮使用 AORO 表。

• 如果該表是大表，並且查詢通常都需要掃描大多數列比如查詢明細（最典型的就是 SELECT * FROM ），可以考慮使用 AORO 表。

• 在設定壓縮級別的時候，通常對於資料倉儲使用者，設定到 4 或者 5 是比較折中的一個選擇。

 

AOCO表

AOCO 表就是列存的 AO 表。

AOCO 不僅支援表級別的壓縮，同時也支援列級別的壓縮。

建表語句如下，這裡還加入了分割槽特性，關於分割槽可以參見上一篇：Greenplum 效能優化之路 --（一）分割槽表：

CREATE TABLE aoco(
	a int  ENCODING (compresstype=zlib, compresslevel=5),
	b int  ENCODING (compresstype=none),
	c varchar(32) ENCODING (compresstype=RLE_TYPE, blocksize=32768),
	d varchar(32),
	fdate date
)
WITH (appendonly=true, orientation=column, compresstype=zlib, compresslevel=6, blocksize=65536)
DISTRIBUTED BY (a)
PARTITION BY RANGE(fdate) 
(
	PARTITION pn START ('2018-11-01'::date) END ('2018-11-10'::date) EVERY ('1 day'::interval),
	DEFAULT PARTITION pdefault
);

壓縮選項：

• compresstype：支援2種壓縮格式，zlib 和 RLE_TYPE，其中 RLE_TYPE（Run-length Encoding）對於有較多重複值的列壓縮比很高，因為它會將多個重複值儲存為一個值，從而大大降低儲存量，比如日期，性別，年齡等欄位。

• compresslevel：compresstype 如果是 zlib，compresslevel 在1-9，compresstype 如果是 RLE_TYPE，compresslevel 在1-4。

• 列壓縮與表壓縮：AOCO 表除了支援表級別的壓縮外，還支援列級別的壓縮，列級別的壓縮配置會覆蓋表級別的壓縮配置，比如上述語法中4個欄位，每個欄位都採用了不用的壓縮方式，d 列沒有定義，則會預設使用表級別的壓縮方式。

• 分割槽壓縮：在使用分割槽表的時候，每個分割槽表也可以設定不同的壓縮配置，這個常用於對資料進行冷熱分離，比如對於非常老的資料，由於訪問頻率較低，可以考慮採用較大的壓縮比，減少儲存量。

 

BLOCKSIZE：

• 表的儲存塊大小，通常表資料對應的物理檔案就是按 blocksize 的粒度增加，也就是初始就是 blocksize 大，並且保持 blocksize 的倍數。

• blocksize 大小在8192和2097152之間，必須是 8192 的倍數，預設是 32768。

• 在 AOCO 表中，每一列也可以設定自己的 blocksize，列的配置會覆蓋表的配置。

 

物理檔案：

• AOCO 表之所以能夠按照列來設定壓縮等引數，本質原因在於 AOCO 表中每一列的資料都會單獨儲存在一個檔案中。因此不同檔案之間可以按不同的引數進行儲存，互不影響。

• 對於 AOCO 表，如果使用了分割槽，那麼對於每一個分割槽的每一列都會有一個檔案，如果一個表的分割槽很多，又是一張大寬表，那麼產生的檔案就會很多，也會對效能有一些影響。

 

最佳實踐：

• AOCO 表通常用於數倉中的核心事實表，這種表欄位多，資料量大，主要是用於 OLAP 場景，也就是查詢的過程不會 SELECT * FROM，而是對其中部分欄位進行讀取和聚合。

• 由於 AOCO 表一般用於大表，因此經常搭配壓縮和分割槽，以減少表的實際儲存量來提升效能。

• 一般情況下，壓縮格式選擇 zlib，壓縮級別可以採用折中的 4 或者 5，但是對於有大量重複值的欄位，記得要採用 RLE_TYPE 壓縮格式。

• blocksize 不要設定過大，特別是對於分割槽表，GP 對於每個分割槽的每個欄位都會維護一個 buffer，blocksize 過大，會導致消耗的記憶體過大，通常就採用預設值 32768 即可。

•  

四、修改表結構

 

單獨列出這一節是因為 Heap，AORO 和 AOCO 這3種表在修改表結構時表現是不一樣的，這也是大家容易忽視的地方。

對於不同的表型別，同樣的修改語法耗時可能會差異很多，主要原因在於對於有些修改操作會導致表重寫，而表重寫的時間就取決於表本身的資料量。

以下列出了不同的表結構，在不同的 ALTER 語法下的行為，其中 YES 代表需要重寫表，NO代表不需要重寫表。

操作	Heap	AORO	AOCO
ADD COLUMN	NO	YES	NO
DROP COLUMN	NO	NO	NO
ALTER COLUMN TYPE	YES	YES	YES
ADD COLUMN DEFAULT NULL	YES	YES	NO
ADD COLUMN DEFAULT VALUE	YES	YES	NO

可以看出 AOCO 表由於每個列都是單獨一個檔案，因此在修改列結構時影響最小，這也是 AOCO 表的一個優勢。

 

五、混合儲存

 

一張表是否可以同時使用多種儲存方式呢？對於分割槽表，是可以的。

混合儲存一般用於這樣的場景，對於一張按時間分割槽的表，通常對於不同時間點的資料行為是不一樣的，比如對於最近的資料，會有較多的明細查詢，而對於比較老的資料，則是以分析為主。同時由於業務可能要儲存較長時間的資料，為了節約成本，較老的資料會考慮使用壓縮比較大的儲存方式。

混合儲存的關鍵就是使用到了 GP 的交換分割槽語法，也就是將一張獨立的表與自己的一個分割槽表進行交換，當然這裡前提是新表的結構是一樣，並且交換的過程沒有新資料進入。

 

流程如下：

1.建立一張分割槽表(1到5月份，每月一張表)，採用 Heap 儲存

CREATE TABLE hyper_storage (
	a int,
	b varchar(32),
	fdate date
) DISTRIBUTED BY (a)
PARTITION BY RANGE(fdate) 
(
	PARTITION pn START ('2018-01-01'::date) END ('2018-06-01'::date) EVERY ('1 month'::interval),
	DEFAULT PARTITION pdefault
);

storage=# \d
                           List of relations
 Schema |             Name             | Type  |    Owner     | Storage
--------+------------------------------+-------+--------------+---------
 public | hyper_storage                | table | test | heap
 public | hyper_storage_1_prt_pdefault | table | test | heap
 public | hyper_storage_1_prt_pn_1     | table | test | heap
 public | hyper_storage_1_prt_pn_2     | table | test | heap
 public | hyper_storage_1_prt_pn_3     | table | test | heap
 public | hyper_storage_1_prt_pn_4     | table | test | heap
 public | hyper_storage_1_prt_pn_5     | table | test | heap

2.現在要對1月份的表修改儲存格式，因此建立一張新的 AOCO 表

CREATE TABLE exchange_table(
	a int,
	b varchar(32),
	fdate date
) WITH (appendonly=true, ORIENTATION=column, compresstype=zlib, compresslevel=6) 
DISTRIBUTED BY (a)

3.將1月份的資料匯入新表

INSERT INTO exchange_table SELECT * FROM hyper_storage_1_prt_pn_1;

4.交換分割槽

ALTER TABLE hyper_storage EXCHANGE PARTITION pn_1 WITH TABLE exchange_table;

注：pn_1是1月份的分割槽表的 partitionname，可以從 pg_partitions 中查詢得到

5.檢視結果

storage=# \d
                                  List of relations
 Schema |             Name             | Type  |    Owner     |       Storage
--------+------------------------------+-------+--------------+----------------------
 public | hyper_storage                | table | test | heap
 public | hyper_storage_1_prt_pdefault | table | test | heap
 public | hyper_storage_1_prt_pn_1     | table | test | append only columnar
 public | hyper_storage_1_prt_pn_2     | table | test | heap
 public | hyper_storage_1_prt_pn_3     | table | test | heap
 public | hyper_storage_1_prt_pn_4     | table | test | heap
 public | hyper_storage_1_prt_pn_5     | table | test | heap
public | exchange_table  | table | test | heap

這樣1月份的分割槽表就變成了 AOCO 表，而其他分割槽表仍然是 Heap 表

 

六、對比測試

 

各型別表佔用空間比較

選取 Heap，AORO，AOCO 三種表，分別採用壓縮和不壓縮2種方式（Heap表不支援壓縮，AO 表壓縮採用zlib格式，壓縮級別設定為6），插入5億條隨機資料，然後使用

select pg_size_pretty(pg_relation_size('{tablename}'));

檢視錶所佔大小，結果如下：

各型別表大小比較.png

 

說明：可以看出 Heap 表佔用空間更大，即使 AO 表不採用壓縮。AOCO 表由於是按列進行儲存，所以相比行存的 AORO 表壓縮比更大。當然這三者的差距取決於資料的實際情況，一般生產環境中 Heap 表不會和 AO 表有如此大的差距。

各級別壓縮率比較

使用 AOCO 表，zlib 壓縮格式，選取不同的壓縮級別，比較資料寫入時間和表所佔大小，由於 zlib 支援9個級別，這裡選取1,6,9 三個級別進行比較，體現出趨勢即可，結果如下：

各壓縮級別比較.png

 

說明：實際生產環境中不同壓縮級別的資料，壓縮比的差距可能會更大。但可以看出，越高的壓縮級別，在插入的時候越耗時，其它 SQL，類似 SELECT，UPDATE 等也都是一樣。

 

 

寫在最後

切記，從其它系統遷移資料到 GP 上來，第一件事情就是給每張表選擇合適的儲存格式，特別是核心表。

 

---

 

 

關注“騰訊雲大資料”公眾號，技術交流、最新活動、服務專享一站Get~