GaussDB(DWS)性能調(diào)優(yōu)系列實(shí)戰(zhàn)篇三：十八般武藝之好味道表定義

前言

GaussDB(DWS)是企業(yè)級的大規(guī)模并行處理關(guān)系型數(shù)據(jù)庫，采用采用Shared-nothing架構(gòu)的MPP(Massive Parallel Processing)系統(tǒng)，支持PB級別數(shù)據(jù)量的處理，適用于詳單查詢、數(shù)據(jù)倉庫、混合負(fù)載和大數(shù)據(jù)分析等場景。Shared-nothing架構(gòu)天然支持?jǐn)?shù)據(jù)打散分布到各個(gè)數(shù)據(jù)節(jié)點(diǎn)(DataNode)以及多節(jié)點(diǎn)協(xié)同計(jì)算機(jī)制，同時(shí)這種機(jī)制對表定義涉及提出了更高的訴求，表定義會直接影響集群的有效容量以及業(yè)務(wù)查詢性能。本文從產(chǎn)品架構(gòu)、功能實(shí)現(xiàn)以及業(yè)務(wù)特征的角度闡述GaussDB(DWS)的中表定義需要關(guān)注的一些關(guān)鍵因素。

1??????存儲方式設(shè)計(jì)

GaussDB(DWS)支持行存儲(row-based storage)和列存儲(column-based storage)兩種存儲方式，這兩種存儲格式分別適用不同的業(yè)務(wù)場景。通常來講典型的點(diǎn)查詢?yōu)橹鞯膱鼍巴扑]使用行存儲，典型的統(tǒng)計(jì)分析型業(yè)務(wù)推薦使用列存儲。

1.1??????行存儲

行存儲模式下，一條數(shù)據(jù)的所有列組合在一起稱之為一個(gè)tuple多個(gè)tuple組成一個(gè)page，所有的page構(gòu)成表的數(shù)據(jù)文件。pages是行存數(shù)據(jù)存取的最小單元，一個(gè)page默認(rèn)8KB。page的基本結(jié)構(gòu)如下

行存儲模式下，所有數(shù)據(jù)列集中存儲在一個(gè)tuple中，所以行存儲的更新(UPDATE)、刪除(DELETE)、索引點(diǎn)查性能較好，但是當(dāng)查詢列只涉及所有列的很少一部分的時(shí)候，所有列的數(shù)據(jù)也都會被讀取，導(dǎo)致大量的無效IO，因此推薦比較簡單點(diǎn)查場景或者存在頻繁的數(shù)據(jù)更新的業(yè)務(wù)采用行存儲

1.2??????列存儲

列存儲下把數(shù)據(jù)表中的每一列單獨(dú)存儲，每個(gè)列的?6w條數(shù)據(jù)組成一個(gè)CU，每個(gè)列的所有的CU構(gòu)成一個(gè)列的數(shù)據(jù)文件，每個(gè)列都會有單獨(dú)的數(shù)據(jù)文件。CU的基本結(jié)構(gòu)如下

列數(shù)據(jù)之間具有更高的相似度，所以列存儲的壓縮性能更好。當(dāng)只查詢少量列且查詢數(shù)據(jù)量較大時(shí)，列存儲的IO性能收益很明顯。

列存儲天然的可以跟向量化執(zhí)行引擎對接，在表關(guān)聯(lián)、匯聚等重計(jì)算場景下可以使用向量化執(zhí)行引擎提升計(jì)算性能，因此統(tǒng)計(jì)分析等重IO和重計(jì)算型業(yè)務(wù)推薦使用列存儲。

因?yàn)閿?shù)據(jù)按列存儲，導(dǎo)致更新(UPDATE)、刪除(DELETE)、索引點(diǎn)查性的時(shí)候需要訪問或者刷新更多的文件，導(dǎo)致大量的隨機(jī)IO；因此相比行存儲，列存儲的更新、刪除、索引點(diǎn)查詢的性能較差。

1.3??????表存儲方式選擇

表的存儲類型是表定義設(shè)計(jì)的第一步，客戶業(yè)務(wù)屬性是表的存儲類型的決定性因素。根據(jù)以上我們對行存儲和列存儲原理的介紹，重查詢分析(大量的多表關(guān)聯(lián)、group by操作)場景推薦使用使用列存表，典型的有數(shù)倉場景；以點(diǎn)查詢?yōu)橹鞯膱鼍巴扑]使用行存表，典型的有詳單查詢場景。

存儲類型

適用場景

行存

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

增刪改比較多的場景

列存

統(tǒng)計(jì)分析類查詢?(多表關(guān)聯(lián)+group by操作)

即席查詢?（查詢條件列不確定，無法明確索引）

GaussDB(DWS)支持單個(gè)database中同時(shí)存儲行存儲和列存儲類型的表，以更好的支持混合負(fù)載場景

1.4??????表存儲方式定義

表的行/列存儲通過表定義的orientation屬性定義。當(dāng)指定orientation屬性為row時(shí)，表為行存儲；當(dāng)指定orientation屬性為column時(shí)，表為列存儲；如果不指定，默認(rèn)為行存儲。

行存儲表定義方式如下：

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? ? 數(shù)據(jù)分布方式設(shè)計(jì)

GaussDB(DWS)的MPP架構(gòu)，天然支持通過散列的方式進(jìn)行水平分表，將業(yè)務(wù)數(shù)據(jù)表的元組打散存儲到各個(gè)數(shù)據(jù)節(jié)點(diǎn)(DataNode)上，通過并行利用各個(gè)數(shù)據(jù)節(jié)點(diǎn)的IO能力提升數(shù)據(jù)掃描的效率。為了優(yōu)化高頻關(guān)聯(lián)小表的查詢性能，GuassDB(DWS)支持復(fù)制的數(shù)據(jù)分布方式。表的分布方式取決于表的業(yè)務(wù)屬性，事實(shí)表一般數(shù)據(jù)量較大，且數(shù)據(jù)增加或者變化很頻繁，建議使用散列分布；維度表數(shù)據(jù)量較小，且數(shù)據(jù)一般不會變化，只有定期更新操作，建議使用復(fù)制分布。

2.1??????散列分布策略

散列分布是按照某種散列規(guī)則，把表數(shù)據(jù)map到指定的數(shù)據(jù)節(jié)點(diǎn)(DataNode)上進(jìn)行存儲的方式。散列分布可以利用各個(gè)節(jié)點(diǎn)的IO資源，提升各個(gè)數(shù)據(jù)節(jié)點(diǎn)的IO能力。GaussDB(DWS)中采用hash的散列策略，按照表定義時(shí)指定的分布列組合，對一條記錄的某一個(gè)或幾個(gè)字段進(jìn)行hash運(yùn)算后，生成對應(yīng)的hash值，然后根據(jù)DN實(shí)例與哈希值的映射關(guān)系獲得該元組的目標(biāo)存儲位置。

對于散列分布的表，分布列的選擇非常重要。當(dāng)分布列選擇合理時(shí)，Hash散列策略可以大大減小計(jì)算節(jié)點(diǎn)之間的數(shù)據(jù)交互，大幅提升查詢性能；但是當(dāng)hash分布列選擇不合理時(shí)，會導(dǎo)致數(shù)據(jù)傾斜(某個(gè)或者某些DataNode的數(shù)據(jù)量嚴(yán)重超過其它DataNode的數(shù)據(jù)量)，因?yàn)槎贪逍?yīng)導(dǎo)致集群的有效容量下降。

散列主要使用于客戶業(yè)務(wù)表，這些表有數(shù)據(jù)量大、數(shù)據(jù)量逐漸增加的特征，適用散列分布可以有效的提升表查詢性能。

2.2??????復(fù)制分布策略

復(fù)制分布(replication)策略將表中的全量數(shù)據(jù)在集群的每一個(gè)DN實(shí)例上保留一份。在關(guān)聯(lián)操作中復(fù)制表可以避免數(shù)據(jù)重分布操作，減小網(wǎng)絡(luò)開銷，同時(shí)減少了plan segment(每個(gè)plan segment都會起對應(yīng)的線程)的個(gè)數(shù)；但是復(fù)制分布策略會導(dǎo)致比較嚴(yán)重的數(shù)據(jù)冗余，因此只有小表才適合復(fù)制分布策略。

實(shí)際生產(chǎn)上只有小數(shù)據(jù)量、查詢頻繁、更新(DELETE/INSERT/UFPATE)很少的表(基本都是維度表)才會定義replication分布策略

2.3? ? ? 輪詢分布策略

輪詢(RoundRobin)分布是DWS最新支持的一種分布側(cè)策略，在這種分布策略下，當(dāng)數(shù)據(jù)導(dǎo)入的時(shí)候會把數(shù)據(jù)逐個(gè)分布到所有DN。

在這種分布策略下可以保證數(shù)據(jù)分布不會存在傾斜，但是因?yàn)閿?shù)據(jù)分布節(jié)點(diǎn)是隨機(jī)的，導(dǎo)致這類表在計(jì)算時(shí)會更大概率的觸發(fā)此表的重分布。各列傾斜都比較嚴(yán)重的大表推薦使用此種分布策略。

2.4??????分布方式選擇

表數(shù)據(jù)分布方式主要依據(jù)表的業(yè)務(wù)屬性和數(shù)據(jù)屬性決定，簡單總結(jié)如下

策略

適用場景

hash

數(shù)據(jù)量較大的事實(shí)表

replication

數(shù)據(jù)量小、查詢頻繁、更新(DELETE/INSERT/UFPATE)很少的維度表

roundrobin

如果創(chuàng)建表的時(shí)候沒有指定分布

1) 對于8.1.1之前版本的集群，或者從8.1.1之前版本升級到8.1.1之后(包含8.1.1)版本的集群，默認(rèn)選擇主鍵或者表的第一個(gè)支持hash分布策略的列作為分不列

2) 對于新安裝的8.1.1之后(包含8.1.1)版本的集群，默認(rèn)選擇表的分布方式為roundrobin

對于8.1.1之后的版本，可以通過修改default_distribution_mode切換上述兩種行為。

2.5??????分布列定義

表的復(fù)制分布策略可以通過表定義指定：

postgres=# CREATE TABLE storage postgres-# ( postgres(# c_id int, postgres(# c_d_id int NOT NULL, postgres(# c_w_id int NOT NULL, postgres(# c_first varchar(16) NOT NULL postgres(# )WITH(orientation=row) postgres-# DISTRIBUTE BY REPLICATION; CREATE TABLE

表的散列分布策略可以通過表定義：

postgres=# CREATE TABLE storage postgres-# ( postgres(# c_id int, postgres(# c_d_id int NOT NULL, postgres(# c_w_id int NOT NULL, postgres(# c_first varchar(16) NOT NULL postgres(# )WITH(orientation=row) postgres-# DISTRIBUTE BY HASH(c_d_id); CREATE TABLE

表的輪詢分布屬性可以通過表定義：

postgres=# CREATE TABLE storage postgres-# ( postgres(# c_id int, postgres(# c_d_id int NOT NULL, postgres(# c_w_id int NOT NULL, postgres(# c_first varchar(16) NOT NULL postgres(# )WITH(orientation=row) postgres-# DISTRIBUTE BY ROUNDROBIN; CREATE TABLE

3??????分布列設(shè)計(jì)

從性能角度上，除非是表各列數(shù)據(jù)傾斜都比較嚴(yán)重，否則不建議選擇輪詢分布策略。

對于采取散列分布策略的表，分布列的選擇取決于表數(shù)據(jù)的特征以及表相關(guān)的業(yè)務(wù)查詢特征，推薦使用經(jīng)常做關(guān)聯(lián)查詢的列、且數(shù)據(jù)分布均勻的列作為分布列。好的分布列可以通過減少跨節(jié)點(diǎn)的數(shù)據(jù)計(jì)劃節(jié)省網(wǎng)絡(luò)資源開銷，優(yōu)化查詢性能。

3.1??????分布列選擇策略

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

a)??列值應(yīng)比較離散，以便數(shù)據(jù)能夠均勻分布到各個(gè)DN

分布列值分布不均勻會導(dǎo)致數(shù)據(jù)在數(shù)據(jù)節(jié)點(diǎn)分布不均勻(某些DataNode上數(shù)據(jù)量大，某些DataNode上數(shù)據(jù)量小)，這會導(dǎo)致不同DataNode上數(shù)據(jù)掃面的計(jì)算量不均衡，從而拖慢整個(gè)表掃描的性能；同時(shí)會因?yàn)椴糠諨ataNode的磁盤容量提前爆滿，集群只讀，導(dǎo)致集群有效容量下降。通常情況下使用表的主鍵列或者唯一索引列作為表的分布列是一個(gè)不錯(cuò)的選擇

b)??考慮選擇查詢中的連接條件為分布列

GaussDB(DWS)的散列策略是hash，根據(jù)GaussDB(DWS)的分布式查詢框架，當(dāng)兩表等值關(guān)聯(lián)(join)列剛好是表的分布列時(shí)(如果分布列是多列，那么要求所有列都存在等值關(guān)聯(lián)條件)，join任務(wù)可以不再數(shù)據(jù)重分布的情況下直接Join，這樣可以省去數(shù)據(jù)重分布的時(shí)間開銷和網(wǎng)絡(luò)資源開銷，從而提升查詢計(jì)算性能。

c)??在滿足前面兩條原則的情況下盡量不要選取存在常量等值filter的列

GaussDB(DWS)會協(xié)調(diào)節(jié)點(diǎn)(Coordinator)上進(jìn)行任務(wù)規(guī)劃，此時(shí)會根據(jù)表的過濾條件(Filter)進(jìn)行掃面操作剪枝優(yōu)化，以較小IO資源開銷。如果表dwcjk的分布列是zqdh，且表dwcjk掃描時(shí)存在Filter條件zqdh=’000001’，而根據(jù)散列策略zqdh=’000001’的值都分布在數(shù)據(jù)節(jié)點(diǎn)DN1上，那么協(xié)調(diào)節(jié)點(diǎn)(Coordinator)上進(jìn)行任務(wù)規(guī)劃時(shí)會對dwcjk表的掃描操作進(jìn)行剪枝(指定只有在數(shù)據(jù)節(jié)點(diǎn)DN1對表dwcjk進(jìn)行數(shù)據(jù)掃描操作)。這樣對于表掃描的實(shí)際壓力會值落在節(jié)點(diǎn)DN1，導(dǎo)致不同數(shù)據(jù)節(jié)點(diǎn)的IO壓力不均衡。

注意此策略主要適用于統(tǒng)計(jì)分析類的重查詢場景，對于詳單查詢等以點(diǎn)查為主要場景的查詢類業(yè)務(wù)，在滿足前兩個(gè)約束的前提下，可以優(yōu)選存在常量等值Filter約束列作為分布列。因?yàn)檫@種場景在數(shù)據(jù)節(jié)點(diǎn)上使用索引加速查詢，查詢耗時(shí)往往以ms或者幾十ms計(jì)，通過剪枝把查詢?nèi)蝿?wù)map到具體的某個(gè)數(shù)據(jù)節(jié)點(diǎn)上執(zhí)行，節(jié)省無效操作(不用連接到所有的數(shù)據(jù)節(jié)點(diǎn)上操作)，同時(shí)也會大大的提高并發(fā)能力

3.2??????分布列選擇的限制

GaussDB(DWS)的列存儲格式的表不支持主鍵和唯一約束，行存儲格式表支持主鍵和唯一約束。但是存儲格式表的主鍵和唯一約束的創(chuàng)建存在嚴(yán)格約束：分布列的集合是主鍵列或者索引列的子集。

多個(gè)列作為分布列時(shí)，分布列的順序會影響數(shù)據(jù)分布，即同一條記錄在distribute by hash(col1, col2)方式下，跟在distribute by hash(col2, col1)分布方式下可能會map到不同的DataNode上進(jìn)行存儲。

GaussDB(DWS)對分布列的個(gè)數(shù)沒有限制，但是建議分布列的個(gè)數(shù)盡量少，一方面可以減小數(shù)據(jù)map到不同DN的計(jì)算開銷，同時(shí)也可以更好的全匹配join條件，提升查詢性能。

3.3??????分布列離散性校驗(yàn)

本節(jié)使用比較小的數(shù)據(jù)量說明離散分布的表檢驗(yàn)、選擇分布列已經(jīng)修改分布列的方法

1) 對于當(dāng)前已創(chuàng)建并且導(dǎo)入數(shù)據(jù)的表，可以使用如下SQL檢驗(yàn)表數(shù)據(jù)分布的離散型

postgres=# -- 'public'是表的schema名稱，'store_sales'是表名 postgres=# SELECT * FROM table_distribution('public.store_sales') ORDER BY dnsize::bigint; schemaname | tablename | nodename | dnsize ------------+-------------+--------------+---------- public | store_sales | dn_6005_6006 | 28991488 public | store_sales | dn_6001_6002 | 39043072 public | store_sales | dn_6007_6008 | 41140224 public | store_sales | dn_6003_6004 | 46710784 (4 rows)

2）發(fā)現(xiàn)此表存儲傾斜時(shí)，使用如下函數(shù)查詢表定義

postgres=# -- 'public'是表的schema名稱，'store_sales'是表名 postgres=# SELECT pg_get_tabledef('public.store_sales'); pg_get_tabledef ------------------------------------------------------------------------------------------------- SET search_path = public; + CREATE TABLE store_sales ( + ss_sold_date_sk integer, + ss_sold_time_sk integer, + ss_item_sk integer NOT NULL, + ss_customer_sk integer, + ss_cdemo_sk integer, + ss_hdemo_sk integer, + ss_addr_sk integer, + ss_store_sk integer, + ss_promo_sk integer, + ss_ticket_number bigint NOT NULL, + ss_quantity integer, + ss_wholesale_cost numeric(7,2), + ss_list_price numeric(7,2), + ss_sales_price numeric(7,2), + ss_ext_discount_amt numeric(7,2), + ss_ext_sales_price numeric(7,2), + ss_ext_wholesale_cost numeric(7,2), + ss_ext_list_price numeric(7,2), + ss_ext_tax numeric(7,2), + ss_coupon_amt numeric(7,2), + ss_net_paid numeric(7,2), + ss_net_paid_inc_tax numeric(7,2), + ss_net_profit numeric(7,2) + ) + WITH (orientation=column, compression=low, colversion=2.0, enable_delta=false) + DISTRIBUTE BY HASH(ss_quantity) + TO GROUP group_version1; (1 row)

3) 對表做analyze，收集最新統(tǒng)計(jì)信息，然后使用統(tǒng)計(jì)信息估算各列的distinct值(distinct_tuples)

postgres=# ANALYZE public.store_sales; ANALYZE postgres=# SELECT postgres-# n.nspname || '.' || c.relname AS tablename, postgres-# a.attname, postgres-# t.typname, postgres-# c.reltuples, postgres-# s.stanullfrac::numeric(5,4) AS null_frac, postgres-# (s.stanullfrac * reltuples)::bigint AS null_tuples, postgres-# s.stawidth AS avg_width, postgres-# (CASE WHEN s.stadistinct >= 0 THEN s.stadistinct ELSE ((s.stanullfrac - 1) * s.stadistinct * reltuples) END)::bigint AS distinct_tuples, postgres-# CASE postgres-# WHEN s.stakind1 = 1 THEN (s.stanumbers1[1:3]) postgres-# WHEN s.stakind2 = 1 THEN (s.stanumbers2[1:3]) postgres-# WHEN s.stakind3 = 1 THEN (s.stanumbers3[1:3]) postgres-# WHEN s.stakind4 = 1 THEN (s.stanumbers4[1:3]) postgres-# WHEN s.stakind5 = 1 THEN (s.stanumbers5[1:3]) postgres-# ELSE NULL::real[] postgres-# END::numeric(5,4)[] AS top3_mcf postgres-# FROM pg_class c postgres-# INNER JOIN pg_attribute a ON c.oid = a.attrelid postgres-# LEFT JOIN pg_statistic s ON a.attnum = s.staattnum AND c.oid = s.starelid AND s.starelkind = 'c'::"char" postgres-# INNER JOIN pg_type t ON t.oid = a.atttypid postgres-# LEFT JOIN pg_namespace n ON n.oid = c.relnamespace postgres-# WHERE a.attnum > 0 postgres-# AND c.reloptions::text NOT LIKE '%internal_mask%' postgres-# AND c.oid = 'public.store_sales'::regclass postgres-# AND distinct_tuples > 1 postgres-# ORDER BY distinct_tuples DESC; tablename | attname | typname | reltuples | null_frac | null_tuples | avg_width | distinct_tuples | top3_mcf --------------------+-----------------------+---------+-----------+-----------+-------------+-----------+-----------------+--------------------- public.store_sales | ss_ext_list_price | numeric | 2880404 | .0455 | 131021 | 7 | 255740 | {.0001,.0001,.0001} public.store_sales | ss_ticket_number | int8 | 2880404 | 0.0000 | 0 | 8 | 229397 | {.0001,.0001,.0001} public.store_sales | ss_cdemo_sk | int4 | 2880404 | .0440 | 126647 | 4 | 203382 | {.0001,.0001,.0001} public.store_sales | ss_net_paid_inc_tax | numeric | 2880404 | .0454 | 130753 | 6 | 192495 | {.0114,.0002,.0001} public.store_sales | ss_ext_wholesale_cost | numeric | 2880404 | .0437 | 125933 | 6 | 182328 | {.0002,.0002,.0002} public.store_sales | ss_net_profit | numeric | 2880404 | .0431 | 124059 | 6 | 158572 | {.0003,.0002,.0002} public.store_sales | ss_net_paid | numeric | 2880404 | .0432 | 124416 | 6 | 126704 | {.0116,.0002,.0002} public.store_sales | ss_ext_sales_price | numeric | 2880404 | .0436 | 125487 | 6 | 115758 | {.0096,.0002,.0002} public.store_sales | ss_customer_sk | int4 | 2880404 | .0431 | 124059 | 4 | 72964 | {.0002,.0002,.0002} public.store_sales | ss_addr_sk | int4 | 2880404 | .0451 | 129771 | 4 | 43412 | {.0002,.0002,.0002} public.store_sales | ss_sold_time_sk | int4 | 2880404 | .0451 | 130039 | 4 | 37755 | {.0002,.0002,.0002} public.store_sales | ss_ext_tax | numeric | 2880404 | .0447 | 128611 | 6 | 18922 | {.1081,.0008,.0008} public.store_sales | ss_list_price | numeric | 2880404 | .0453 | 130396 | 6 | 17100 | {.0003,.0003,.0003} public.store_sales | ss_item_sk | int4 | 2880404 | 0.0000 | 0 | 4 | 16616 | {.0004,.0003,.0003} public.store_sales | ss_sales_price | numeric | 2880404 | .0445 | 128165 | 6 | 11665 | {.0096,.0006,.0006} public.store_sales | ss_wholesale_cost | numeric | 2880404 | .0439 | 126380 | 6 | 9915 | {.0003,.0003,.0003} public.store_sales | ss_hdemo_sk | int4 | 2880404 | .0446 | 128343 | 4 | 7187 | {.0004,.0004,.0004} public.store_sales | ss_coupon_amt | numeric | 2880404 | .0434 | 125041 | 3 | 7133 | {.7706} public.store_sales | ss_ext_discount_amt | numeric | 2880404 | .0434 | 125041 | 3 | 7133 | {.7706} public.store_sales | ss_sold_date_sk | int4 | 2880404 | .0425 | 122363 | 4 | 1823 | {.0016,.0016,.0015} public.store_sales | ss_promo_sk | int4 | 2880404 | .0450 | 129503 | 4 | 300 | public.store_sales | ss_quantity | int4 | 2880404 | .0446 | 128432 | 4 | 100 | {.0107,.0103,.0103} public.store_sales | ss_store_sk | int4 | 2880404 | .0442 | 127272 | 4 | 6 | {.1643,.1621,.1588} (23 rows)

4) 選擇 null_tuples 盡量小 & distinct_tuples 盡量大 & distinct_tuples >> DN個(gè)數(shù)的列作為候選分布列，然后結(jié)合業(yè)務(wù)上表關(guān)聯(lián)需求，選擇最長用作關(guān)聯(lián)的列，此處選擇 ss_item_sk 作為最佳候選分布列

5) 用如下SQL進(jìn)一步去確認(rèn)目標(biāo)分布列的離散性

postgres=# -- 'public'是表的schema名稱，'store_sales'是表名，ss_item_sk是要檢測的列名 postgres=# SELECT * FROM table_skewness('public.store_sales', 'ss_item_sk') ORDER BY seqnum; seqnum | num | ratio --------+--------+--------- 0 | 713488 | 24.770% 1 | 694973 | 24.128% 2 | 729549 | 25.328% 3 | 742394 | 25.774% (4 rows)

6）當(dāng)確定目標(biāo)分布列之后，可以使用如下SQL實(shí)現(xiàn)分布列的修改

postgres=# -- 'public'是表的schema名稱，'store_sales'是表名，ss_item_sk是修改后的目標(biāo)分布列 postgres=# ALTER TABLE public.store_sales DISTRIBUTE BY HASH(ss_item_sk); ALTER TABLE

4??????表分區(qū)設(shè)計(jì)

通俗的講表，分區(qū)就是把一個(gè)大表按照條件分割為若干個(gè)小表，這種分割體現(xiàn)在數(shù)據(jù)庫內(nèi)部的數(shù)據(jù)管理上，對表數(shù)據(jù)的常規(guī)操作(UPDATE/DELETE/INSERT/SELECT)是透明的。一般對數(shù)據(jù)和查詢都有明顯區(qū)間段特征的表使用分區(qū)策略，可通過減少不必要的數(shù)據(jù)掃描提升查詢性能。如下case中，使用分區(qū)表可以減少60%的數(shù)據(jù)掃描量。

4.1??????分區(qū)表的優(yōu)勢

分區(qū)表把邏輯上的一張表根據(jù)范圍分區(qū)策略分成幾張物理塊庫進(jìn)行存儲，這張邏輯上的表稱之為分區(qū)表，物理塊稱之為分區(qū)。分區(qū)表是一張邏輯表，不存儲數(shù)據(jù)，數(shù)據(jù)實(shí)際是存儲在分區(qū)上的。分區(qū)表和普通表相比具有以下優(yōu)點(diǎn)：

a)??改善查詢性能

對分區(qū)對象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索效率

b)??增強(qiáng)可用性

如果分區(qū)表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用

c)??提升可維護(hù)性

對于需要周期性刪除的過期歷史數(shù)據(jù)，可以通過drop/truncate分區(qū)的方式快速高效處理

4.2??????分區(qū)策略選擇

當(dāng)表有以下特征時(shí)，可以考慮使用表分區(qū)策略

a)?數(shù)據(jù)具有明顯區(qū)間性的字段

分區(qū)表需要根據(jù)有明顯區(qū)間性字段進(jìn)行表分區(qū)。通常我們比如日期、區(qū)域、數(shù)值等字段進(jìn)行分區(qū)，時(shí)間字段是最常見的分區(qū)字段。

b)?業(yè)務(wù)查詢有明顯的區(qū)間范圍特征

查詢數(shù)據(jù)可落到區(qū)間范圍指定的分區(qū)內(nèi)，這樣才能通過分區(qū)剪枝，只掃描查詢需要的分區(qū)，從而提升數(shù)據(jù)掃描效率，降低數(shù)據(jù)掃描的IO開銷。

c)?表數(shù)據(jù)量比較大

小表掃描本身耗時(shí)不大，分區(qū)表的性能收益不明顯，因此只建議對大表采取分區(qū)策略。列存儲模式下因?yàn)槊總€(gè)列是單獨(dú)的文件出處，且最小的存儲單元CU可存儲6w行數(shù)據(jù)，因此對于列存分區(qū)表，建議每個(gè)分區(qū)的數(shù)據(jù)不小于DN個(gè)數(shù)*6w

4.3??????分區(qū)表定義

分區(qū)表策略定義分為兩種方式

a)???簡單區(qū)間切割

這種是最常見的通用的分區(qū)定義策略，適合所有的分區(qū)定義場景。

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)????指定策略切割

此方式適用于分區(qū)間隔固定、批量創(chuàng)建分區(qū)的場景。當(dāng)分區(qū)個(gè)數(shù)很多時(shí)，此方式可大大節(jié)省創(chuàng)建分區(qū)的工作量

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??????分區(qū)鍵選擇限制

類似表分布列的選擇，對于行存儲格式的表，如果表存在主鍵或者唯一約束，分區(qū)鍵應(yīng)當(dāng)是是主鍵列或者唯一約束的索引列的子集。

4.5??????分區(qū)表查詢

只有查詢語句可以進(jìn)行分區(qū)剪枝的時(shí)候，分區(qū)表查詢才會產(chǎn)生數(shù)據(jù)掃描上的性能收益。一般只有當(dāng)分區(qū)鍵跟常量值存在直接的比較(>、<、=、<=、>=)操作時(shí)，分區(qū)表才可以正常剪枝。我們可以通過對查詢語句執(zhí)行explain命令查看分區(qū)剪枝的效果

有時(shí)我們期望編寫的SQL語句可以進(jìn)行分區(qū)剪枝，但是實(shí)際上并沒有走到分區(qū)剪枝，這種預(yù)期外的行為往往是因?yàn)橐韵乱蛩貙?dǎo)致

a)??分區(qū)鍵上有函數(shù)

當(dāng)分區(qū)鍵上存在函數(shù)調(diào)用時(shí)，分區(qū)表無法剪枝

b)??分區(qū)鍵字段的存在隱式類型轉(zhuǎn)換

這種場景往往是因?yàn)榉謪^(qū)鍵跟常量值的數(shù)據(jù)類型不一致，導(dǎo)致計(jì)劃規(guī)劃時(shí)分區(qū)鍵的數(shù)據(jù)類型發(fā)生隱式類型轉(zhuǎn)換，導(dǎo)致分區(qū)無法剪枝

5??????字段設(shè)計(jì)

表字段設(shè)計(jì)時(shí)需要注意以下內(nèi)容

a)? ?使用執(zhí)行效率比較高的數(shù)據(jù)類型

一般來說整型數(shù)據(jù)的運(yùn)算(包括=、＞、＜、≧、≦、≠等常規(guī)的比較運(yùn)算，以及group by等運(yùn)算)效率比字符串、浮點(diǎn)數(shù)要高。能使用整型的場景盡量使用整型。

b)??使用短字段的數(shù)據(jù)類型

長度較短的數(shù)據(jù)類型不僅可以減小數(shù)據(jù)文件的大小，提升IO性能；同時(shí)也可以減小計(jì)算相關(guān)計(jì)算時(shí)的內(nèi)存消耗，提升計(jì)算性能。比如我們需要一個(gè)整型數(shù)據(jù)，如果可以用smallint就盡量不用int，如果可以用int就盡量不用bigint。

c)??關(guān)聯(lián)列使用一致的數(shù)據(jù)類型

表關(guān)聯(lián)列盡量使用相同的數(shù)據(jù)類型。如果表關(guān)聯(lián)列數(shù)據(jù)類型不同，在執(zhí)行時(shí)數(shù)據(jù)庫會動態(tài)地轉(zhuǎn)化為相同的數(shù)據(jù)類型進(jìn)行比較，這種轉(zhuǎn)換會帶來一定的性能開銷，同時(shí)會因?yàn)轭愋娃D(zhuǎn)換導(dǎo)致表關(guān)聯(lián)操作時(shí)發(fā)生數(shù)據(jù)重分布，導(dǎo)致額外的性能和資源開銷。

6??????約束設(shè)計(jì)

1)??非空(not null)約束

明確不存在null值的字段加上not null約束。在特定場景下，優(yōu)化器會對包含not null的查詢語句進(jìn)行自動優(yōu)化，提升查詢效率。

2)??主鍵/唯一約束

行存儲表支持唯一/主鍵約束，如果表是散列分布，那么約束列必須包含所有的分布列；如果表做了分區(qū)，那么約束列也必須包含所有的分區(qū)列。

3)??局部聚簇約束

局部聚簇(partial cluster key，簡稱PCK)是列存儲表一種局部聚簇技術(shù)，這種技術(shù)可以讓表數(shù)據(jù)在批量入庫的時(shí)，先對表進(jìn)行局部排序，然后再寫盤。這樣可以讓相同/相似的數(shù)據(jù)連續(xù)存儲，提高數(shù)據(jù)的壓縮比；同時(shí)在查詢時(shí)也可以依賴列存儲表的min/max稀疏索引實(shí)現(xiàn)表的CU過濾，從實(shí)現(xiàn)高效的數(shù)據(jù)過濾效果(參見《GaussDB(DWS)性能調(diào)優(yōu)：列存表scan性能優(yōu)化》)。

PCK的優(yōu)化原理如下：

一張表上只能建立一個(gè)PCK，一個(gè)PCK可以包含多列，但是一般不建議超過2列。通常我們使用經(jīng)常出現(xiàn)的、過濾效果比較好的簡單表達(dá)式中的列作為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??????表定義總結(jié)

最后簡單總結(jié)下表定義流程

EI企業(yè)智能 Gauss AP 數(shù)據(jù)倉庫服務(wù) GaussDB(DWS)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。