摘要：表結構設計是資料庫建模的一個關鍵環節，表定義好壞直接決定了叢集的有效容量以及業務查詢效能，本文從產品架構、功能實現以及業務特徵的角度闡述在GaussDB(DWS)的中表定義時需要關注的一些關鍵因素。
前言
GaussDB(DWS)是企業級的大規模並行處理關係型資料庫，採用Shared-nothing架構的MPP(Massive Parallel Processing)系統，支援PB級別資料量的處理，適用於詳單查詢、資料倉儲、混合負載和大資料分析等場景。Shared-nothing架構天然支援資料打散分佈到各個資料節點(DataNode)以及多節點協同計算機制，同時這種機制對錶定義涉及提出了更高的訴求，表定義會直接影響叢集的有效容量以及業務查詢效能。本文從產品架構、功能實現以及業務特徵的角度闡述GaussDB(DWS)的中表定義需要關注的一些關鍵因素。
1 儲存方式設計
GaussDB(DWS)支援行儲存(row-based storage)和列儲存(column-based storage)兩種儲存方式，這兩種儲存格式分別適用不同的業務場景。通常來講典型的點查詢為主的場景推薦使用行儲存，典型的統計分析型業務推薦使用列儲存。
1.1 行儲存
行儲存模式下，一條資料的所有列組合在一起稱之為一個tuple多個tuple組成一個page，所有的page構成表的資料檔案。pages是行存資料存取的最小單元，一個page預設8KB。page的基本結構如下
行儲存模式下，所有資料列集中儲存在一個tuple中，所以行儲存的更新(UPDATE)、刪除(DELETE)、索引點查效能較好，但是當查詢列只涉及所有列的很少一部分的時候，所有列的資料也都會被讀取，導致大量的無效IO，因此推薦比較簡單點查場景或者存在頻繁的資料更新的業務採用行儲存。
1.2 列儲存
列儲存下把資料表中的每一列單獨儲存，每個列的 6w條資料組成一個CU，每個列的所有的CU構成一個列的資料檔案，每個列都會有單獨的資料檔案。CU的基本結構如下
列資料之間具有更高的相似度，所以列儲存的壓縮效能更好。當只查詢少量列且查詢資料量較大時，列儲存的IO效能收益很明顯。因為資料分列儲存，導致更新(UPDATE)、刪除(DELETE)、索引點查性的時候需要訪問或者重新整理更多的檔案，導致大量的隨機IO；因此相比行儲存，列儲存的更新、刪除、索引點查詢的效能較差。同時列儲存天然的可以跟向量化執行引擎對接，在表關聯、匯聚等重計算場景下可以使用向量化執行引擎提升計算效能，因此統計分析等重IO和重計算型業務推薦使用列儲存。
1.3 表儲存方式選擇
表的儲存型別是表定義設計的第一步，客戶業務屬性是表的儲存型別的決定性因素。根據以上我們對行儲存和列儲存原理的介紹，重查詢分析(大量的多表關聯、group by操作)場景推薦使用使用列存表，典型的有數倉場景；以點查詢為主的場景推薦使用行存表，典型的有詳單查詢場景。
GaussDB(DWS)支援單個database中同時儲存行儲存和列儲存型別的表，以更好的支援混合負載場景
1.4 表儲存方式定義
表的行/列儲存通過表定義的orientation屬性定義。當指定orientation屬性為row時，表為行儲存；當指定orientation屬性為column時，表為列儲存；如果不指定，預設為行儲存。
行儲存表定義方式如下：
CREATE TABLE storage ( c_id int, c_d_id int NOT NULL, c_w_id int NOT NULL, c_first varchar(16) NOT NULL )WITH(orientation=row) DISTRIBUTE BY HASH(c_d_id); 列儲存表定義方式如下： CREATE TABLE storage ( c_id int, c_d_id int NOT NULL, c_w_id int NOT NULL, c_first varchar(16) NOT NULL )WITH(orientation=column) DISTRIBUTE BY HASH(c_d_id);
2 資料分佈方式設計
GaussDB(DWS)的MPP架構，天然支援通過雜湊的方式進行水平分表，將業務資料表的元組打散儲存到各個資料節點(DataNode)上，通過並行利用各個資料節點的IO能力提升資料掃描的效率。為了優化高頻關聯小表的查詢效能，GuassDB(DWS)支援複製的資料分佈方式。表的分佈方式取決於表的業務屬性，事實表一般資料量較大，且資料增加或者變化很頻繁，建議使用雜湊分佈；維度表資料量較小，且資料一般不會變化，只有定期更新操作，建議使用複製分佈。
2.1 雜湊分佈
雜湊分佈是按照某種雜湊規則，把表資料map到指定的資料節點(DataNode)上進行儲存的方式。雜湊分佈可以利用各個節點的IO資源，提升各個資料節點的IO能力。GaussDB(DWS)中採用hash的雜湊策略，按照表定義時指定的分佈列組合，對一條記錄的某一個或幾個欄位進行hash運算後，生成對應的hash值，然後根據DN例項與雜湊值的對映關係獲得該元組的目標儲存位置。
對於雜湊分佈的表，分佈列的選擇非常重要。當分佈列選擇合理時，Hash雜湊策略可以大大減小計算節點之間的資料互動，大幅提升查詢效能；但是當hash分佈列選擇不合理時，會導致資料傾斜(某個或者某些DataNode的資料量嚴重超過其它DataNode的資料量)，因為短板效應導致叢集的有效容量下降。
雜湊主要使用於客戶業務表，這些表有資料量大、資料量逐漸增加的特徵，適用雜湊分佈可以有效的提升表查詢效能。
2.2 複製分佈
複製分佈(replication)策略將表中的全量資料在叢集的每一個DN例項上保留一份。在關聯操作中複製表可以避免資料重分佈操作，減小網路開銷，同時減少了plan segment(每個plan segment都會起對應的執行緒)的個數；但是複製分佈策略會導致比較嚴重的資料冗餘，因此只有小表才適合複製分佈策略。
實際生產上只有小資料量、查詢頻繁、更新(DELETE/INSERT/UFPATE)很少的表(基本都是維度表)才會定義replication分佈策略
2.3 分佈方式選擇
表資料分佈方式主要依據表的業務屬性和資料屬性決定，簡單總結如下
2.4 分佈列定義
表的複製分佈屬性可以通過表定義指定：
CREATE TABLE storage ( c_id int, c_d_id int NOT NULL, c_w_id int NOT NULL, c_first varchar(16) NOT NULL )WITH(orientation=row) DISTRIBUTE BY REPLICATRRION;
表的雜湊分分佈屬性可以通過表定義：
CREATE TABLE storage ( c_id int, c_d_id int NOT NULL, c_w_id int NOT NULL, c_first varchar(16) NOT NULL )WITH(orientation=row) DISTRIBUTE BY HASH(c_d_id);
3 分佈列設計
對於採取雜湊分佈策略的表，分佈列的選擇取決於表資料的特徵以及表相關的業務查詢特徵，推薦使用經常做關聯查詢的列、且資料分佈均勻的列作為分佈列。好的分佈列可以通過減少跨節點的資料計劃節省網路資源開銷，優化查詢效能。
3.1 分佈列選擇策略
Hash分佈表的分佈列選取至關重要，需要滿足以下原則：
a) 列值應比較離散，以便資料能夠均勻分佈到各個DN
分佈列值分佈不均勻會導致資料在資料節點分佈不均勻(某些DataNode上資料量大，某些DataNode上資料量小)，這會導致不同DataNode上資料掃面的計算量不均衡，從而拖慢整個表掃描的效能；同時會因為部分DataNode的磁碟容量提前爆滿，叢集只讀，導致叢集有效容量下降。通常情況下使用表的主鍵列或者唯一索引列作為表的分佈列是一個不錯的選擇
b) 考慮選擇查詢中的連線條件為分佈列
GaussDB(DWS)的雜湊策略是hash，根據GaussDB(DWS)的分散式查詢框架，當兩表等值關聯(join)列剛好是表的分佈列時(如果分佈列是多列，那麼要求所有列都存在等值關聯條件)，join任務可以不再資料重分佈的情況下直接Join，這樣可以省去資料重分佈的時間開銷和網路資源開銷，從而提升查詢計算效能。
c) 在滿足前面兩條原則的情況下儘量不要選取存在常量等值filter的列
GaussDB(DWS)會協調節點(Coordinator)上進行任務規劃，此時會根據表的過濾條件(Filter)進行掃面操作剪枝優化，以較小IO資源開銷。如果表dwcjk的分佈列是zqdh，且表dwcjk掃描時存在Filter條件zqdh=’000001’，而根據雜湊策略zqdh=’000001’的值都分佈在資料節點DN1上，那麼協調節點(Coordinator)上進行任務規劃時會對dwcjk表的掃描操作進行剪枝(指定只有在資料節點DN1對錶dwcjk進行資料掃描操作)。這樣對於表掃描的實際壓力會值落在節點DN1，導致不同資料節點的IO壓力不均衡。
注意此策略主要適用於統計分析類的重查詢場景，對於詳單查詢等以點查為主要場景的查詢類業務，在滿足前兩個約束的前提下，可以優選存在常量等值Filter約束列作為分佈列。因為這種場景在資料節點上使用索引加速查詢，查詢耗時往往以ms或者幾十ms計，通過剪枝把查詢任務map到具體的某個資料節點上執行，節省無效操作(不用連線到所有的資料節點上操作)，同時也會大大的提高併發能力
3.2 分佈列選擇的限制
GaussDB(DWS)的列儲存格式的表不支援主鍵和唯一約束，行儲存格式表支援主鍵和唯一約束。但是儲存格式表的主鍵和唯一約束的建立存在嚴格約束：分佈列的集合是主鍵列或者索引列的子集。
多個列作為分佈列時，分佈列的順序會影響資料分佈，即同一條記錄在distribute by hash(col1, col2)方式下，跟在distribute by hash(col2, col1)分佈方式下可能會map到不同的DataNode上進行儲存。
GaussDB(DWS)對分佈列的個數沒有限制，但是建議分佈列的個數儘量少，一方面可以減小資料map到不同DN的計算開銷，同時也可以更好的全匹配join條件，提升查詢效能。
3.3 分佈列離散性校驗
對於當前已建立並且匯入資料的表，可以使用如下SQL檢驗表資料分佈的離散型
-- 'public'是表的schema名稱，'storage'是表名 SELECT * FROM table_distribution('public.storage') ORDER BY dnsize;
對於已經建立並且匯入資料的表，如果我們認為當前的分佈列不夠離散，在修改為其它列之前，可嘗試使用如下SQL判斷目標分佈列的離散性
-- 'public'是表的schema名稱，'storage'是表名，c_id是要檢測的列名 SELECT * FROM table_skewness('public.storage', 'c_id') ORDER BY seqnum;
當確定目標分佈列之後，可以使用如下SQL實現分佈列的修改
-- 'public'是表的schema名稱，'storage'是表名，c_id是修改後的目標分佈列 ALTER TABLE public.storage DISTRIBUTE BY HASH(c_id);
4 表分割槽設計
通俗的講表，分割槽就是把一個大表按照條件分割為若干個小表，這種分割體現在資料庫內部的資料管理上，對錶資料的常規操作(UPDATE/DELETE/INSERT/SELECT)是透明的。一般對資料和查詢都有明顯區間段特徵的表使用分割槽策略，可通過減少不必要的資料掃描提升查詢效能。如下case中，使用分割槽表可以減少60%的資料掃描量，SQL查詢整體效能提升46左右。
4.1 分割槽表的優勢
分割槽表把邏輯上的一張表根據範圍分割槽策略分成幾張物理塊庫進行儲存，這張邏輯上的表稱之為分割槽表，物理塊稱之為分割槽。分割槽表是一張邏輯表，不儲存資料，資料實際是儲存在分割槽上的。分割槽表和普通表相比具有以下優點：
a) 改善查詢效能
對分割槽物件的查詢可以僅搜尋自己關心的分割槽，提高檢索效率
b) 增強可用性
如果分割槽表的某個分割槽出現故障，表在其他分割槽的資料仍然可用
c) 提升可維護性
對於需要週期性刪除的過期歷史資料，可以通過drop/truncate分割槽的方式快速高效處理
4.2 分割槽策略選擇
當表有以下特徵時，可以考慮使用表分割槽策略
a) 資料具有明顯區間性的欄位
分割槽表需要根據有明顯區間性欄位進行表分割槽。通常我們比如日期、區域、數值等欄位進行分割槽，時間欄位是最常見的分割槽欄位。
b) 業務查詢有明顯的區間範圍特徵
查詢資料可落到區間範圍指定的分割槽內，這樣才能通過分割槽剪枝，只掃描查詢需要的分割槽，從而提升資料掃描效率，降低資料掃描的IO開銷。
c) 表資料量比較大
小表掃描本身耗時不大，分割槽表的效能收益不明顯，因此只建議對大表採取分割槽策略。列儲存模式下因為每個列是單獨的檔案出處，且最小的儲存單元CU可儲存6w行資料，因此對於列存分割槽表，建議每個分割槽的資料不小於DN個數*6w
4.3 分割槽表定義
分割槽表策略定義分為兩種方式
a) 簡單區間切割
這種是最常見的通用的分割槽定義策略，適合所有的分割槽定義場景。
CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 VALUES LESS THAN(20161231), PARTITION p2017 VALUES LESS THAN(20171231), PARTITION p2018 VALUES LESS THAN(20181231), PARTITION p2019 VALUES LESS THAN(20191231), PARTITION pxxxx VALUES LESS THAN(maxvalue) );
b) 指定策略切割
此方式適用於分割槽間隔固定、批量建立分割槽的場景。當分割槽個數很多時，此方式可大大節省建立分割槽的工作量
CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk) PARTITION BY RANGE(wr_returned_date_sk) ( PARTITION p2016 START(20161231) END(20191231) EVERY(10000), PARTITION p0 END(maxvalue) );
4.4 分割槽鍵選擇限制
類似表分佈列的選擇，對於行儲存格式的表，如果表存在主鍵或者唯一約束，分割槽鍵應當是是主鍵列或者唯一約束的索引列的子集。
4.5 分割槽表查詢
只有查詢語句可以進行分割槽剪枝的時候，分割槽表查詢才會產生資料掃描上的效能收益。一般只有當分割槽鍵跟常量值存在直接的比較(>、<、=、<=、>=)操作時，分割槽表才可以正常剪枝。我們可以通過對查詢語句執行explain命令檢視分割槽剪枝的效果
有時我們期望編寫的SQL語句可以進行分割槽剪枝，但是實際上並沒有走到分割槽剪枝，這種預期外的行為往往是因為以下因素導致
a) 分割槽鍵上有函式
當分割槽鍵上存在函式呼叫時，分割槽表無法剪枝
b) 分割槽鍵欄位的存在隱式型別轉換
這種場景往往是因為分割槽鍵跟常量值的資料型別不一致，導致計劃規劃時分割槽鍵的資料型別發生隱式型別轉換，導致分割槽無法剪枝
5 欄位設計
表欄位設計時需要注意以下內容
a) 使用執行效率比較高的資料型別
一般來說整型資料的運算(包括=、＞、＜、≧、≦、≠等常規的比較運算，以及group by等運算)效率比字串、浮點數要高。能使用整型的場景儘量使用整型。
b) 使用短欄位的資料型別
長度較短的資料型別不僅可以減小資料檔案的大小，提升IO效能；同時也可以減小計算相關計算時的記憶體消耗，提升計算效能。比如我們需要一個整型資料，如果可以用smallint就儘量不用int，如果可以用int就儘量不用bigint。
c) 關聯列使用一致的資料型別
表關聯列儘量使用相同的資料型別。如果表關聯列資料型別不同，在執行時資料庫會動態地轉化為相同的資料型別進行比較，這種轉換會帶來一定的效能開銷，同時可能會因為型別轉換導致表關聯操作時發生資料重分佈，導致額外的效能和資源開銷。
6 約束設計
1) 非空(not null)約束
明確不存在null值的欄位加上not null約束。在特定場景下，優化器會對包含not null的查詢語句進行自動優化，提升查詢效率。
2) 主鍵/唯一約束
行儲存表支援唯一/主鍵約束，如果表是雜湊分佈，那麼約束列必須包含所有的分佈列；如果表做了分割槽，那麼約束列也必須包含所有的分割槽列。
3) 區域性聚簇約束
區域性聚簇(partial cluster key，簡稱PCK)是列儲存表一種區域性聚簇技術，這種技術可以讓表資料在批量入庫的時，先對錶進行區域性排序，然後再寫盤。這樣可以讓相同/相似的資料連續儲存，提高資料的壓縮比；同時在查詢時也可以依賴列儲存表的min/max稀疏索引實現表的CU過濾，從實現高效的資料過濾效果(參見《GaussDB(DWS)效能調優：列存表scan效能優化》)。一張表上只能建立一個PCK，一個PCK可以包含多列，但是一般不建議超過2列。通常我們使用經常出現的、過濾效果比較好的簡單表示式中的列作為PCK列，區域性聚簇約束的定義方式跟主鍵約束的定義方式類似
CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer NOT NULL, wr_refunded_customer_sk integer, PARTIAL CLUSTER KEY(wr_returned_date_sk) ) WITH (orientation = column) DISTRIBUTE BY HASH (wr_item_sk);
7 表定義總結
最後簡單總結下表定義流程
