【云小課】EI第31課 不可不知的調優技巧-GaussDB(DWS)表結構優化

合理的表結構可以帶來穩定和高效的性能，但是反過來說，如果表結構不合理，就可能會引起比較大的性能問題或者額外的工作成本。

如何設計和優化一張表呢？GaussDB(DWS)帶你一起來學習調優表的關鍵技巧。

了解GaussDB(DWS)表設計的六個關鍵要素，就能很快發現調優表的技巧。

選擇存儲方式

表的存儲方式分為行存儲和列存儲。

左圖直觀的展示了存儲方式的差異，但是建表時怎么決定用行存儲還是列存儲呢？

用戶業務類型是決定表的存儲類型的主要因素，可以根據以下業務場景選擇：

點查詢(返回記錄少，基于索引的簡單查詢)

增、刪、改操作較多的場景

統計分析類查詢 (關聯、分組操作較多的場景)

即席查詢（查詢條件不確定，行存表掃描難以使用索引）

創建列存表

示例：創建表warehouse_t1，存儲方式為列存通過ORIENTATION參數指定。

CREATE TABLE warehouse_t1 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN);

選擇壓縮比

對于數據庫，IO 相對于CPU通常都是系統的性能瓶頸，合理的壓縮手段不僅能節省空間，也能減少IO，提高讀取性能。

針對行存和列存，在數據入庫的時候有不同的壓縮比，進而入庫的性能也會不同。對于I/O讀寫量大，CPU富足（計算相對?。┑膱鼍?，選擇高壓縮比；反之選擇低壓縮比。依據此原則進行不同壓縮下的測試和對比，以選擇符合自身業務情況的最優壓縮比。

壓縮比通過COMPRESSION參數指定，其中列存表取值為：YES/NO/LOW/MIDDLE/HIGH，默認值為LOW。

選擇壓縮比

壓縮級別

所適用的業務場景

LOW

系統CPU使用率高，存儲磁盤空間充足。

MIDDLE

系統CPU使用率適中，但存儲磁盤空間不是特別充足。

HIGH

系統CPU使用率低，磁盤空間不充裕。

創建使用壓縮比的表

示例：創建列存表warehouse_t2，壓縮比通過COMPRESSION參數指定。

CREATE TABLE warehouse_t2 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE);

選擇分布方式和分布列

---復制表（Replication）將表中的全量數據在集群的每一個DN實例上保留一份，即每個數據節點都有完整的表數據。

---哈希（Hash）表將表中某一個或幾個字段進行hash運算后，生成對應的hash值，通過映射，把數據分布到指定DN。對于Hash分布表，在讀/寫數據時可以利用各個節點的IO資源，大大提升表的讀/寫速度。

選擇分布方式

可根據以下業務場景選擇：

小表、維度表。

Hash分布表的分布列選取至關重要，需要滿足以下原則：

列值應比較離散，以便數據能夠均勻分布到各個DN。例如，考慮選擇表的主鍵為分布列，如在人員信息表中選擇身份證號碼為分布列。

在滿足第一條原則的情況下盡量不要選取存在常量filter的列。例如，表dwcjk相關的部分查詢中出現dwcjk的列zqdh存在常量的約束(例如zqdh=’000001’)，那么就應當盡量不用zqdh做分布列。

在滿足前兩條原則的情況，考慮選擇查詢中的連接條件為分布列，以便Join任務能夠下推到DN中執行，且減少DN之間的通信數據量。

對于Hash分表策略，如果分布列選擇不當，可能導致數據傾斜，查詢時出現部分DN的I/O短板，從而影響整體查詢性能。因此在采用Hash分表策略之后需對表的數據進行數據傾斜性檢查，以確保數據在各個DN上是均勻分布的。可以使用以下SQL檢查數據傾斜性：

select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc;

其中xc_node_id對應DN，一般來說，不同DN的數據量相差5%以上即可視為傾斜，如果相差10%以上就必須要調整分布列。

創建Replication/Hash分布表

示例：創建行存表warehouse_t3，Replication分布方式通過DISTRIBUTE BY參數指定。

CREATE TABLE warehouse_t3 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) DISTRIBUTE BY REPLICATION;

示例：創建列存表warehouse_t4，Hash分布方式通過DISTRIBUTE BY參數指定，分區鍵為W_WAREHOUSE_SK。

CREATE TABLE warehouse_t4 ( W_WAREHOUSE_SK INTEGER NOT NULL, W_WAREHOUSE_ID CHAR(16) NOT NULL, W_WAREHOUSE_NAME VARCHAR(20) , W_WAREHOUSE_SQ_FT INTEGER , W_STREET_NUMBER CHAR(10) , W_STREET_NAME VARCHAR(60) , W_STREET_TYPE CHAR(15) , W_SUITE_NUMBER CHAR(10) , W_CITY VARCHAR(60) , W_COUNTY VARCHAR(30) , W_STATE CHAR(2) , W_ZIP CHAR(10) , W_COUNTRY VARCHAR(20) , W_GMT_OFFSET DECIMAL(5,2) ) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH (W_WAREHOUSE_SK);

分區的設計

分區表是把邏輯上的一張表根據某種方案分成幾張物理塊進行存儲。這張邏輯上的表稱之為分區表，物理塊稱之為分區。

分區表是一張邏輯表，不存儲數據，數據實際是存儲在分區上的。分區表和普通表相比具有以下優點：

---改善查詢性能：對分區對象的查詢可以僅搜索自己關心的分區，提高檢索效率。

---增強可用性：如果分區表的某個分區出現故障，表在其他分區的數據仍然可用。

---方便維護：如果分區表的某個分區出現故障，需要修復數據，只修復該分區即可。

GaussDB(DWS)支持的分區表為范圍分區表：將數據基于范圍映射到每一個分區。這個范圍是由創建分區表時指定的分區鍵決定的。分區鍵經常采用日期，例如將銷售數據按照月份進行分區。

分區使用示例：

---創建分區表 CREATE TABLE demo_partition( id varchar(50), start_time timestamp without time zone ) with (orientation=column, compression=low) distribute by hash(id)Partition by range (start_time) ( partition p1 values less than ('2019-04-11 00:00:00'), partition p2 values less than ('2019-04-12 00:00:00'), partition p3 values less than ('2019-04-19 00:00:00'), partition p4 values less than (MAXVALUE) ); ---刪除最大分區pmx ALTER TABLE demo_partition DROP partition pmx; ---增加分區 ALTER TABLE demo_partition add partition p4 values less than('2019-04-20 00:00:00'); ---刪除某個分區中的數據 ALTER TABLE demo_partition truncate partition p1; ---查詢某個分區的行數 SELECT count(1) FROM demo_partition partition(p1); ---分割某個分區（目前只有行存支持，列存不支持此功能） ALTER TABLE demo_partition split partition p3 at('2019-04-15 00:00:00') into( partition p31, partition p32 ); ---合并分區 ALTER TABLE demo_partition merge partitions p1,p2 into partition p12;

選擇索引

索引可以用來提高數據查詢性能，但是不恰當的使用將導致數據性能下降。建議僅在匹配如下某條原則時創建索引：

經常執行查詢的字段

在連接條件上創建索引

Where子句的過濾條件上

經常出現在order by，group by和distinct字段

DWS索引支持：

Btree索引：使用一種類似B+樹的結構來存儲數據的鍵值，通過這種結構能夠快速的查找索引。Btree適合支持比較查詢和查詢范圍。但因為其本身的數據結構，當數據量非常大的時候，每條數據的入庫速度會變得越來越慢，占用的內存也會越來越大，嚴重的影響了入庫性能。

GIN索引是倒排索引，可以處理包含多個鍵的值（比如數組）。

Gist適用于幾何和地理等多維數據類型和集合數據類型。

Psort:針對列存表進行局部排序索引。

行存表支持的索引類型：btree（行存表缺省值）、gin、gist；列存表支持的索引類型：Psort（列存表缺省值）、btree、gin。

索引使用示例：

---在表上創建一個索引 CREATE INDEX test-index on test using btree(vid); ---在分區表上創建分區索引 CREATE INDEX test-index on test using btree(vid) local; ---重建索引 ALTER INDEX test-index rebuild; ---設置索引不可用 ALTER INDEX test-index unusable; ---刪除索引 DROP INDEX test-index;

選擇局部聚簇

局部聚簇（Partial Cluster Key）是列存下的一種技術。該技術可以通過min/max稀疏索引較快的事先基表掃描的filter過濾。局部聚簇可以指定多列，但是不建議超過2列，選擇原則如下：

---受基表的簡單表達式約束。這種約束一般形如col op const,其中col為列名，op為操作符=，<，>，<=，>=，const為常量值。

---盡量采用選擇度比較高（過濾更多數據）的簡單表達式的列。

---盡量把選擇度比較低的約束列放在局部聚簇中的前面。

---盡量把枚舉類型的列放在局部聚簇的前面。

局部聚簇使用示例：

---創建一個帶局部聚簇列的表 CREATE TABLE test(id int,volume text,partial cluster key (volume)) with (orientation=column, compression=low) distribute by hash (id); ---為普通表創建一個PCK，并使其生效 ALTER TABLE table_name add partial cluster key (column_name); VACUUM FULL table_name; ---刪除表中的局部聚簇列： ALTER TABLE table_name drop constraint partial_cluster_key_name;

基于這些關鍵要素的掌握，您可以改進表的分配，以達到您所期望的數據加載、存儲和查詢方面的效果。

了解更多華為云數據倉庫GaussDB(DWS)，請點擊這里。

EI企業智能 云小課 數據倉庫服務 GaussDB(DWS)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。