目錄

第一章MW.........................................................................................................................2

1.1文檔目的...................................................................2

1.2預(yù)期讀者..................................................................2

1.3參考資料..................................................................2

第二章削規(guī)范...................................................................3

2.1數(shù)據(jù)庫(kù)對(duì)象數(shù)量............................................................3

2.2表創(chuàng)建規(guī)范................................................................3

2.3表結(jié)構(gòu)設(shè)計(jì).................................................................4

2.3.1字段命名................................................................4

2.3.2數(shù)據(jù)類型................................................................5

2.3.3數(shù)據(jù)分布................................................................6

2.3.4分區(qū)....................................................................8

2.3.5壓縮存儲(chǔ)...............................................................10

2.3.6索引設(shè)計(jì)...............................................................10

2.4其他數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì).......................................................13

2.4.1schema.....................................................................................................................................................13

2.4.2視圖...................................................................14

2.4.3暫時(shí)表和中間表.........................................................14

第三章SQL開(kāi)辟規(guī)范...........................................................15

3.1基本要求..................................................................15

3.2WHERE條件................................................................15

3.3分區(qū)字段使用...............................................................16

3.4表關(guān)聯(lián).....................................................................17

3.5排序語(yǔ)句...................................................................20

3.6嵌套子查詢.................................................................21

3.7UNION/UNIONALL..........................................................................................................................21

3.8高效SQL寫(xiě)法的建議........................................................23

第一章前言

1.1文檔目的

隨著Greenplum數(shù)據(jù)庫(kù)的正式上線使用。為了保證Greenplum數(shù)據(jù)倉(cāng)庫(kù)

系統(tǒng)平臺(tái)的平穩(wěn)運(yùn)行，保證系統(tǒng)的可靠性、穩(wěn)定性、可維護(hù)性和高性能。特制定

本開(kāi)辟規(guī)范，以規(guī)范基于Greenplum數(shù)據(jù)庫(kù)平臺(tái)的相關(guān)應(yīng)用開(kāi)辟，提高開(kāi)辟質(zhì)

量。

1.2預(yù)期讀者

Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)應(yīng)用的設(shè)計(jì)與開(kāi)辟人員;

Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)的系統(tǒng)管理人員和數(shù)據(jù)庫(kù)管理員；

Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)的運(yùn)行維護(hù)人員;

1.3參考資料

參考Greenplum4.3.x版本官方指引：

《GPDB43AdminGuide.pdf》

《GPDB43RefGuide.pdf》

《GPDB43UtilityGuide.pdf》

第二章設(shè)計(jì)規(guī)范

2.1數(shù)據(jù)庫(kù)對(duì)象數(shù)量

婁媚庫(kù)對(duì)象類型包鹿婉表、視圖、函數(shù)、序列、索引等等，在Greenplum

數(shù)據(jù)庫(kù)中，系統(tǒng)元數(shù)據(jù)同時(shí)保存在Master服務(wù)器和Segment服務(wù)器上，過(guò)多

的數(shù)據(jù)庫(kù)對(duì)象會(huì)造成系統(tǒng)元數(shù)據(jù)的膨脹,而過(guò)多的系統(tǒng)元數(shù)據(jù)造成系統(tǒng)運(yùn)彳璐步

變慢；同時(shí)，類似數(shù)據(jù)庫(kù)的備份、恢復(fù)、擴(kuò)容等較大型的操作都導(dǎo)致效率變慢。

因此，依據(jù)GreenplumDB產(chǎn)品的最佳時(shí)間，單個(gè)數(shù)據(jù)庫(kù)的對(duì)象數(shù)量，應(yīng)控制

在10萬(wàn)以內(nèi)。

GP數(shù)據(jù)庫(kù)的對(duì)象包括：表、視圖、索引、分區(qū)子表、外部表等。

如果數(shù)據(jù)表的數(shù)量太多，建議按應(yīng)用域進(jìn)行分庫(kù)，盡量將單個(gè)數(shù)據(jù)庫(kù)的表數(shù)

量控制在10萬(wàn)以內(nèi)，可以在一個(gè)集群中創(chuàng)建多個(gè)數(shù)據(jù)庫(kù)。

【備注】：在Greenplum數(shù)據(jù)庫(kù)中,一張分區(qū)表，在數(shù)據(jù)庫(kù)中存儲(chǔ)為一張

父表、每張分區(qū)子表都是一張獨(dú)立的庫(kù)表；例如：一張按月進(jìn)行分區(qū)的存儲(chǔ)一年

數(shù)據(jù)的表，如果含默認(rèn)分區(qū)，共14張表。

2.2表創(chuàng)建規(guī)范

為了避免數(shù)據(jù)庫(kù)表數(shù)量太多，避免單個(gè)數(shù)據(jù)表的數(shù)據(jù)量過(guò)大，給系統(tǒng)的運(yùn)行

和使用帶來(lái)艱難，在Greenplum數(shù)據(jù)庫(kù)中需遵循如下的表創(chuàng)建規(guī)范：

1、GP系統(tǒng)表中保存的表名稱都是以小寫(xiě)保存。通常SQL語(yǔ)句中表名對(duì)大

/」當(dāng)不敏感。但不允許在建表語(yǔ)句中使用雙引號(hào)（""）包括表名，這樣會(huì)影響系

統(tǒng)表中存儲(chǔ)的名稱,使得表名存在大/」當(dāng)或者特殊字符。表命名也不允許浮現(xiàn)中

文字。

2、單個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)表數(shù)量建議不要超過(guò)10萬(wàn)張；

3、禁止使用二級(jí)分區(qū)表，因?yàn)槎?jí)分區(qū)表會(huì)造成表對(duì)象數(shù)量的急劇膨脹；

4、由于過(guò)多的數(shù)據(jù)文件會(huì)導(dǎo)致操作系統(tǒng)對(duì)文件的操作效率降低，直接影響

到數(shù)據(jù)庫(kù)的管理效率。如果數(shù)據(jù)文件數(shù)量過(guò)多，建議增加多個(gè)表空間，把數(shù)據(jù)表

均勻分布到不同的表空間。每一個(gè)表空間目錄下的數(shù)據(jù)文件數(shù)量，應(yīng)控制在80

萬(wàn)以內(nèi)。文件數(shù)統(tǒng)計(jì)可以直接到某個(gè)Segment實(shí)例目錄下指定的表空間目錄

下統(tǒng)計(jì)。

5、創(chuàng)建數(shù)據(jù)表（DDL犯勺時(shí)候（不含暫時(shí)表和程序中使用的中間表），必

須使用tablespace子句指定用于存儲(chǔ)的表空間，而不是把所有表都存儲(chǔ)在默認(rèn)

表空間；例如：

Createtableemployee（idint,namevarchar）

TABLESPACEtpc_data_01distributedby（id）;

6、對(duì)于數(shù)據(jù)量超過(guò)1TB的大表，需從應(yīng)用設(shè)計(jì)方面，考慮對(duì)大表進(jìn)行優(yōu)化，

例如是否可劃分為歷史數(shù)據(jù)表和當(dāng)前數(shù)據(jù)表，并分開(kāi)存放；是否應(yīng)采用壓縮存儲(chǔ)

節(jié)省空間；是否合理分區(qū)；是否應(yīng)定期清理數(shù)據(jù)等等。

2.3表結(jié)構(gòu)的

2.3.1字段命名

表字段的命名，與表名類似。在GP系統(tǒng)表中保存的表名稱都是以/」當(dāng)保存。

通常SQL語(yǔ)句中字段名稱對(duì)大小寫(xiě)不敏感。但不允許在建表語(yǔ)句中使用雙引號(hào)

("")包括字段名，這樣會(huì)影響系統(tǒng)表中存儲(chǔ)的名稱，使得表名存在大〃號(hào)或者

特殊字符。字段命名也不允許浮現(xiàn)中文字。

2.3.2數(shù)據(jù)類型

婁攵據(jù)類型的定義與相關(guān)數(shù)據(jù)的加載和使用密切相關(guān)，數(shù)據(jù)類型的定義決定了

數(shù)據(jù)所占用的空間大小，因此，必須謹(jǐn)慎設(shè)計(jì)GP數(shù)據(jù)倉(cāng)庫(kù)數(shù)據(jù)表的字段類型。

數(shù)據(jù)倉(cāng)庫(kù)的數(shù)據(jù)來(lái)自于多個(gè)異構(gòu)的業(yè)務(wù)應(yīng)用系統(tǒng)，通常情況下，業(yè)務(wù)應(yīng)用系

統(tǒng)的字段類型選擇較為隨意，不同的業(yè)務(wù)系統(tǒng)數(shù)據(jù)類型定義存在多樣化，彼此之

間差異較大；因此，在數(shù)據(jù)倉(cāng)庫(kù)中，需在參考源系統(tǒng)字段類型定義的情況下，結(jié)

合Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)的特點(diǎn)和要求，對(duì)字段數(shù)據(jù)類型進(jìn)行設(shè)計(jì)。

Greenplum數(shù)據(jù)庫(kù)的數(shù)據(jù)類型定義需遵循以下原則：

L在滿足業(yè)務(wù)需求的條件下，盡可能選擇空間占用最小的數(shù)據(jù)類型；以節(jié)

省數(shù)據(jù)存儲(chǔ)空間；

2、在GP系統(tǒng)中，CHAR、VARCHAR和TEXT之間不存在性能差異，在其

他的DB系統(tǒng)中，可能CHAR會(huì)表現(xiàn)出最好的性能，但在GPDB中是不存在這

種性能優(yōu)勢(shì)的。在多數(shù)情況下，應(yīng)該選擇使用VARCHAR而不是CHAR;

3、定長(zhǎng)字符串類型使用varchar,而不使用char.

4、對(duì)于數(shù)值類型來(lái)說(shuō)，應(yīng)該盡量選擇更小的數(shù)據(jù)類型來(lái)適應(yīng)數(shù)據(jù)；比如，

選擇BIGINT類型來(lái)存儲(chǔ)SMALLINT類型范圍內(nèi)的數(shù)值，會(huì)造成空間的大量浪

費(fèi)。

5、用來(lái)做TableJoin的Column來(lái)說(shuō)，應(yīng)該考慮選擇相同的數(shù)據(jù)類型。如

果做Join的Column具有相同的數(shù)據(jù)類型（比如主鍵PrimaryKey與外鍵

ForeignKey）,其工作效率會(huì)更高。

6、普通情況下,應(yīng)盡量使用上述規(guī)范數(shù)據(jù)類型，避免浮現(xiàn)諸如：Address,

INET,ARRAY等特殊類型字段。

2.3.3數(shù)據(jù)分布

基于Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)的特點(diǎn),每張數(shù)據(jù)表都必須指定分布鍵DK,

Greenplum數(shù)據(jù)庫(kù)根據(jù)數(shù)據(jù)分布鍵（DistributedKey,簡(jiǎn)稱DK,后同）值來(lái)決

定記錄存儲(chǔ)在哪一個(gè)segment上,DK不僅決定了數(shù)據(jù)在集群節(jié)點(diǎn)上的分布，

還嚴(yán)重影響數(shù)據(jù)查詢和處理操作的執(zhí)行效率，需要非常謹(jǐn)慎的選擇數(shù)據(jù)表的分布

鍵。對(duì)于Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)，DK的選擇需要遵循以下原則：

1、數(shù)據(jù)均勻分布原則

為了盡可能達(dá)到最好的性能，所有的Instance應(yīng)該盡量?jī)?chǔ)存等量的數(shù)據(jù)。

若數(shù)據(jù)的分布不平衡或者傾斜，那些儲(chǔ)存了較多數(shù)據(jù)的Instance在處理自己那

部分?jǐn)?shù)據(jù)時(shí)將需要耗費(fèi)更多的工作量。為了實(shí)現(xiàn)數(shù)據(jù)的平整分布，可以考慮選

的

有惟一性的DK,如主鍵。

2、本地操作原則

在處理查詢時(shí)，不少處理如關(guān)聯(lián)、排序、聚合等若能夠在Instance本地完

成，其效率將遠(yuǎn)高于跨越系統(tǒng)級(jí)別（需在Instance之間交叉?zhèn)鬏敂?shù)據(jù)）的操作。

當(dāng)不同的Table使用相同的DK時(shí)，在DK上的關(guān)聯(lián)或者排序操作將會(huì)以最高效

的方式把絕大部份工作在Instance本地完成。

3、均衡的查詢負(fù)載原則

在一個(gè)查詢正被處理時(shí),我們希翼所有的Instance都能夠處理等量的工作

負(fù)載，從而盡可能達(dá)到最好的性能。通過(guò)合理的DK設(shè)計(jì)，盡量使得查詢處理的

負(fù)載均勻分布在每一個(gè)節(jié)點(diǎn)上，并且盡量保證where條件產(chǎn)生的結(jié)果集在各個(gè)

節(jié)點(diǎn)上也是均勻的。

4、關(guān)聯(lián)一致原則

當(dāng)表于表之間存在關(guān)聯(lián)時(shí)，各表應(yīng)選擇相同字段作為DK,并且做關(guān)聯(lián)查詢

時(shí)，使用DK作為連接字段，盡可能使連接包含全部DK字段；

5、DK-WI!!J

總分父子表的DK應(yīng)保持一致；中間過(guò)程表、暫時(shí)表的DK應(yīng)盡可能保持和

源表的DK一致；

6、DK精簡(jiǎn)原!U!J

DK字段不宜過(guò)多，DK字段越少越好。

基于以上原則，Greenplum數(shù)據(jù)倉(cāng)庫(kù)平臺(tái)的數(shù)據(jù)表DK設(shè)計(jì)規(guī)范如下：

/每一個(gè)數(shù)據(jù)表必須通過(guò)Distribiuted子句顯式指定分布鍵，不允許使用

默

認(rèn)DK的方式創(chuàng)建數(shù)據(jù)表;

/分布鍵字段原則上為1個(gè)，應(yīng)盡量不要超過(guò)3個(gè)；

/分區(qū)的父子表的分布鍵應(yīng)徹底一致；

/中間過(guò)程表、暫時(shí)表、派生表的DK應(yīng)盡可能保持^源表一致；

/具有關(guān)聯(lián)關(guān)系的數(shù)據(jù)表，應(yīng)盡可能使用關(guān)聯(lián)字段作為分布鍵；

/分布鍵字段不可執(zhí)行Update操作;

/為了保證數(shù)據(jù)分布均勻，在沒(méi)有合適字段作為分布鍵的情況下，應(yīng)選擇

數(shù)據(jù)表的主鍵作為分布鍵；

/對(duì)于沒(méi)有邏輯主鍵，又沒(méi)有其他合適字段作為分布鍵的數(shù)據(jù)表，才建議

設(shè)置其分布策略為DistributedRandomly,這只應(yīng)該為最后的選擇;

/隨機(jī)分布的適合使用場(chǎng)景：查詢時(shí)不需要和其它表關(guān)聯(lián)、或者只與小表

關(guān)

聯(lián)的數(shù)據(jù)表，使用隨機(jī)分布策略。

2.3.4分區(qū)

表分區(qū)用以解決特殊大的表的問(wèn)題，分區(qū)表在執(zhí)行給定的查詢語(yǔ)句時(shí)，掃描

相關(guān)的部份數(shù)據(jù)而不是全表的數(shù)據(jù)從而提高查詢性能。分區(qū)表對(duì)于數(shù)據(jù)庫(kù)的管理

也有匡助。并非任何數(shù)據(jù)表都適合做分區(qū),應(yīng)從如下幾個(gè)方面判斷是否應(yīng)進(jìn)行

分區(qū)：

1、表是否足夠大？

惟獨(dú)非常大的事實(shí)表才適合做表分區(qū)。若在一張表中有數(shù)億條記錄，從邏輯

上把表分成較小的分區(qū)將可以改善性能。而對(duì)于惟獨(dú)數(shù)萬(wàn)條或者更少記錄的表，

對(duì)分區(qū)預(yù)先進(jìn)行的管理開(kāi)消將遠(yuǎn)大于可以獲得的性能改善。

2、對(duì)目前的性能不滿意？

作為一種調(diào)優(yōu)方案，應(yīng)該在查詢性能低于預(yù)期時(shí)再考慮表分區(qū)。

3、查詢條件是否能匹配分區(qū)條件？

檢查查詢語(yǔ)句的WHERE條件是否與考慮分區(qū)的COLUMN一致。例如，如

果大部份的查詢使用日期條件，那末按照月或者周的日期分區(qū)設(shè)計(jì)也許很實(shí)用，

而如果查詢條件更多的是使用地區(qū)條件，可以考慮使用地區(qū)將表做列表類型的分

區(qū)。

4、按照某個(gè)規(guī)則數(shù)據(jù)是否可以被均勻的分拆？

應(yīng)該選擇盡量把數(shù)據(jù)均勻分拆的規(guī)則。若每一個(gè)分區(qū)儲(chǔ)存的數(shù)據(jù)量相當(dāng)，那

末查詢性能的改善將與分區(qū)的數(shù)量相關(guān)。例如，把一張表分為10個(gè)分區(qū)，命

中單個(gè)分區(qū)條件的查詢掃表性能將比未分區(qū)的情況下高10倍。

如果以上幾個(gè)方面的回答都是Yes,這樣的表可以通過(guò)分區(qū)策略來(lái)提高查詢

性能。如上面章節(jié)所述,在Greenplum中，每一個(gè)分區(qū)子表都對(duì)應(yīng)一張獨(dú)立的

數(shù)據(jù)表,系統(tǒng)通過(guò)父子表之間的繼承關(guān)系來(lái)維護(hù)分區(qū)定義信息。如果過(guò)多的數(shù)

據(jù)表進(jìn)行了分區(qū)，會(huì)造成表對(duì)象數(shù)量過(guò)多，系統(tǒng)元數(shù)據(jù)急劇膨脹，給系統(tǒng)的運(yùn)

行和維護(hù)帶來(lái)很大負(fù)擔(dān)。因此，還要綜合考慮系統(tǒng)的表數(shù)據(jù)量情況，才可決定

是否對(duì)數(shù)據(jù)表進(jìn)行分區(qū)。

基于以上原則，Greenplum數(shù)據(jù)庫(kù)數(shù)據(jù)分區(qū)的使用規(guī)范如下：

/在性能可以滿足的情況下，盡量不使用數(shù)據(jù)分區(qū)；

/因會(huì)造成表對(duì)象數(shù)量過(guò)多，增加執(zhí)行計(jì)劃生成的復(fù)雜性，禁止使用二級(jí)

分區(qū)；

/數(shù)據(jù)量在億級(jí)別以下，建議不要使用分區(qū)；

/表的數(shù)據(jù)在單個(gè)實(shí)例的數(shù)據(jù)量在100萬(wàn)級(jí)別以下，不需要分區(qū)；

/分區(qū)字段不可以UPDATE,需要用delete+insert或者truncate+

insert替代實(shí)現(xiàn)。

2.3.5壓縮存儲(chǔ)

Greenplum數(shù)據(jù)表分兩種類型：heap表和A0表(Append-optimized)o

在Greenplum數(shù)據(jù)庫(kù)中，需要對(duì)數(shù)據(jù)進(jìn)行壓縮，數(shù)據(jù)表則需要設(shè)置為A0表。

對(duì)數(shù)據(jù)表進(jìn)行壓縮，可以減少磁盤(pán)占用空間，同時(shí)也減少了對(duì)10資源的開(kāi)消(以

CPU資源換10資源)。特殊是在目前10資源不足的硬件環(huán)境下，數(shù)據(jù)庫(kù)設(shè)計(jì)應(yīng)

該盡可能多的使用A0表。建議在選擇壓縮儲(chǔ)存模式時(shí)，最好根據(jù)匕徽測(cè)試的結(jié)

果來(lái)確定。

綜合以上考慮，數(shù)據(jù)表壓縮的設(shè)計(jì)規(guī)范如下：

/數(shù)據(jù)量在百萬(wàn)級(jí)以下的小表，不建議使用壓縮存儲(chǔ)；

/不要在壓縮文件系統(tǒng)使用壓縮存儲(chǔ)；

/壓縮表建議統(tǒng)一使用zlib壓縮算法，壓縮級(jí)別為6(appendonly=true,

compresstype=zlib,compresslevel=6);,此壓縮設(shè)置滿足大多數(shù)的使

用場(chǎng)景。

/建議對(duì)數(shù)據(jù)倉(cāng)庫(kù)中的記錄數(shù)超過(guò)1億的事實(shí)表、歷史數(shù)據(jù)表采用壓縮存

儲(chǔ)；

/所有歷史數(shù)據(jù)表、備份表、歸檔表統(tǒng)一使用壓縮存儲(chǔ)；

2.3.6索引設(shè)計(jì)

在分布式數(shù)據(jù)庫(kù)GPDB中，應(yīng)盡量避免使用索引。GPDB中大部份應(yīng)用場(chǎng)

景是使用順序掃描。與傳統(tǒng)的OLTP數(shù)據(jù)庫(kù)不同的是，Greenplum中數(shù)據(jù)表的

數(shù)據(jù)是分布在多個(gè)節(jié)點(diǎn)上的。這意味著每一個(gè)節(jié)點(diǎn)都掃描全部數(shù)據(jù)的一小部份來(lái)

查找結(jié)果。如果使用了表分區(qū)，掃描的數(shù)據(jù)可能更少。通常，這種情況下使用

索引未必能提升性能。

索引更易于改善OLTP類型的工作負(fù)載，因其返回很少量的數(shù)據(jù)，當(dāng)情況合

適時(shí)查詢優(yōu)化器會(huì)把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。添加索

引會(huì)帶來(lái)一些數(shù)據(jù)庫(kù)開(kāi)消，其必然占用相當(dāng)?shù)拇鎯?chǔ)空間，并且表更新時(shí)需維護(hù)索

引。需確保索引的創(chuàng)建在查詢工作負(fù)載中真正被使用到。同時(shí)，需要檢查索弓I的

確對(duì)于查詢性能有顯著的改善（與順序掃描的性能相比）。

Greenplum支持B-tree索引和位圖（Bitmap）索弓|。因此，使用索引時(shí)，

需要綜合考慮以下問(wèn)題：

1、查詢工作負(fù)載類型：索引更適合于OLTP類型的工作負(fù)載，其返回很少

量的數(shù)據(jù)，對(duì)于OLAP類型的查詢負(fù)載，在GPDB中索引通常作用不大；

2、壓縮表：在查詢少量黝感勺情況下，索引能夠改善A0表上的查詢性能，

當(dāng)情況合適時(shí)查詢優(yōu)化器會(huì)把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。

對(duì)于壓縮數(shù)據(jù)來(lái)說(shuō)，索引訪問(wèn)數(shù)據(jù)的方法是解壓需要的記錄而不是全部解壓；

3、避免在頻繁更新的列上使用索引。在頻繁更新的列上創(chuàng)建索引，當(dāng)該列

被更新時(shí)，需要消耗大量的寫(xiě)磁盤(pán)資源和CPU計(jì)算資源；

4、在高選擇性的列適合使用B-tree索引，選擇性指的是列中DISTINCT

值的數(shù)量除以表中的記錄.例如，如果一張表中有1000行記錄且有800個(gè)

DISTINCT值，選擇性指數(shù)為0.8,這被認(rèn)為是良好的。惟一索引總是具備1.0

的選擇比，這是最好的情況;

5、低選擇性的列適合使用bitmap索引；

6、索引列用于關(guān)聯(lián)。時(shí)常關(guān)聯(lián)(JOIN)的COLUMN(比如夕隧)上建立索引或

者許可以改善JOIN的性能，因?yàn)槠淇梢钥镏樵円?guī)劃器使用其他的關(guān)聯(lián)方法;

7、索引列時(shí)常用在查詢條件中。對(duì)于大表來(lái)說(shuō)，查詢語(yǔ)句WHERE條件中

時(shí)常用到的列，可以考慮使用索引。

綜合以上情況，結(jié)合Greenplum平臺(tái)的特點(diǎn)，索引設(shè)計(jì)的規(guī)范如下：

/原則上，數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)表不建立索引。惟獨(dú)提供給外部用戶訪問(wèn)的

表，才考慮按用戶訪問(wèn)特性，針對(duì)常用查詢字段建立索引；

/對(duì)于跑批的中間表和暫時(shí)表，不允許創(chuàng)建索引；

/對(duì)于記錄數(shù)在百萬(wàn)級(jí)別以下的小表，建議不使用索引；

/創(chuàng)建組合索引時(shí)，必須將時(shí)常作為查詢條件且可選擇性最大的列設(shè)置為

索引的首列；

/不允許創(chuàng)建冗余索引；

/對(duì)于區(qū)別度高的索引，應(yīng)使用B-tree索引，例如賬號(hào)、合同號(hào)等等；

對(duì)于區(qū)別度低的索引，應(yīng)使用Bitm叩索引，例如機(jī)構(gòu)、產(chǎn)品類型等等;

/創(chuàng)建組合索引時(shí)，建議列數(shù)不要超過(guò)5列；

/每張數(shù)據(jù)表的索引數(shù)，建議不超過(guò)5個(gè)；

/在創(chuàng)建和更新索引后，必須執(zhí)行Analyze操作，更新索引的統(tǒng)計(jì)信息；

/在對(duì)大表進(jìn)行數(shù)據(jù)加載的時(shí)候，如果存在索引，建議先刪除索引，待數(shù)

據(jù)加載完成，再重新創(chuàng)建索引；

/對(duì)頻繁更新的數(shù)據(jù)表，應(yīng)定期對(duì)其執(zhí)行reindex操作，以重建索引；

/如果在分區(qū)表中使用了索引，不允許在子表上單獨(dú)創(chuàng)建和修改索引；通

常，刪除頂級(jí)分區(qū)的索引，系統(tǒng)會(huì)自動(dòng)刪除相關(guān)子表的索引，但如果子

表的索引有缺失，將不能自動(dòng)刪除子表的索引，需要—手動(dòng)刪除。

/再也不使用的索引必須刪除；

2.4其他數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)

2.4.1schema

模式(Schema)是在DB內(nèi)組織對(duì)象的一種邏輯結(jié)構(gòu)。模式可以允許用戶在

一個(gè)DB內(nèi)不同的模式之間使用相同Name的對(duì)象(比如Table).Schema命名

不允許浮現(xiàn)中文字。Schema的規(guī)劃與創(chuàng)建建議由系統(tǒng)管理員或者應(yīng)用設(shè)計(jì)人員

統(tǒng)T見(jiàn)劃和設(shè)計(jì)。

不允許在系統(tǒng)的Schema下創(chuàng)建用戶表；Greenplum的系統(tǒng)Schema如下:

序號(hào)Schema名稱說(shuō)明

1.gp_toolkit提供系統(tǒng)管理方面的視圖

2.Information_schema提供元數(shù)據(jù)信息的視圖

3.pg_catalog系統(tǒng)對(duì)象元數(shù)據(jù)表

4.pg_aosegAppendonly表的輔助元數(shù)據(jù)表

5.pg_toast大對(duì)象存儲(chǔ)

6.pg_bitmapindex位圖索引對(duì)象存儲(chǔ)

2.4.2視圖

視圖的設(shè)計(jì)規(guī)范建議如下：

/視圖命名不允許使用雙引號(hào)包括視圖名，視圖名稱不允許浮現(xiàn)中文字；

/在視圖中，不允許使用ORDERBY語(yǔ)句;

/對(duì)頻繁訪問(wèn)，具有多個(gè)大表關(guān)聯(lián)，并含有復(fù)雜計(jì)算或者排序的視圖，建

議

修改為物理表；

2.4.3暫時(shí)表和中間表

暫時(shí)表使用規(guī)范如下：

/對(duì)于每天定期執(zhí)行的后臺(tái)數(shù)據(jù)處理作業(yè)，建議不要使用暫時(shí)表，因?yàn)槭?/p>

用暫時(shí)表，會(huì)造成每天都進(jìn)行大量的數(shù)據(jù)表的創(chuàng)建和刪除，引起系統(tǒng)元

數(shù)據(jù)表的急劇膨脹，導(dǎo)致需要頻繁的進(jìn)行系統(tǒng)表的Vacuum操作，從而

影響系統(tǒng)的使用和穩(wěn)定性。

/暫時(shí)表和中間表定義時(shí)必須顯示指定分布鍵。

/暫時(shí)表和中間表，評(píng)估表數(shù)據(jù)量，建議大表統(tǒng)一采用壓縮表。

第三章SQL開(kāi)辟規(guī)范

3.1基本要求

L代碼行清晰、整齊、層次分明、結(jié)構(gòu)性強(qiáng)，易于閱讀；

2、代碼中應(yīng)具備必要的注釋以增強(qiáng)代碼的可讀性和可維護(hù)性；

3、代碼應(yīng)充分考慮執(zhí)行效率，保證代碼的高效性；

3.2WHERE條件

1、在Where條件過(guò)濾中，應(yīng)盡量將函數(shù)處理放在等式的右邊，以提高查

詢性能；

2、對(duì)于日期(date、timestamp等)類型的字段判斷,條件值可直接使用

字符串，GP會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。無(wú)需過(guò)多的使用類型轉(zhuǎn)換函數(shù)，如：to_date

使用：

WHEREcall_dt='2022-01-01';

不需要寫(xiě)成：

WHEREcall_dt=to_date('2022-01-01',,YYYY-MM-DD')；

3、在條件過(guò)濾中使用函數(shù)，不需要寫(xiě)select關(guān)鍵字。否則會(huì)影響執(zhí)行計(jì)劃

的準(zhǔn)確性：

錯(cuò)誤示例：

WHEREt.z_day=

(selectto_char(current_timestamp-interval'1minute','dd'))

andt.z_hours=

(selectto_char(current_timestamp-interval'1minute',*HH24'))

4、系統(tǒng)中不少采用日期分區(qū)的表，分區(qū)字段類型為數(shù)值型(integer)。等

式的左邊不要使用數(shù)值運(yùn)算，否則會(huì)影響執(zhí)行計(jì)劃對(duì)分區(qū)使用的準(zhǔn)確性。

問(wèn)題示例：

WHEREstatis_date/100=masadw.fn_get_llm_yyyymm(20220423)

可改寫(xiě)為：

WHEREstatis_datebetween20220401and20220430;

WHEREstatis_date>=20220401andstatis_date<=20220430;

5、在WHERE條件中錯(cuò)誤的添加1<>1的判斷，會(huì)導(dǎo)致執(zhí)行計(jì)劃混亂。

問(wèn)題語(yǔ)句：

SELECT

,2O22113O'::INTasstatic_date,

B.DVLPER_CODE,

A.CNTYJD,SUM(A.CALL_DUR)/60.0ASCALL_DUR

FROMmasamk.LS_GSM_TOL_DA,masamk.IU_USR_DB

WHERElolandA.statis_date=20221130ANDA.USRJD=B.USRJD

GROUPBYB.DVLPER_CODE,A.CNTY」D

3.3分區(qū)字段使用

如上述章節(jié)提到的分區(qū)表的使用原則，使用分期表是為了降低每次表掃描涉

及的數(shù)據(jù)量，已達(dá)到提升SQL處理效率的目的。如果SQL語(yǔ)句中沒(méi)有準(zhǔn)確的使

用分區(qū)字段就會(huì)導(dǎo)致遍歷所有分區(qū)，導(dǎo)致SQL執(zhí)行效率低下。

械在多個(gè)分區(qū)表關(guān)聯(lián)時(shí)，每一個(gè)分區(qū)表都需要制定分區(qū)字段的條件。除非

業(yè)務(wù)上有特殊要求必須要遍歷所有的（或者大部份的）子分區(qū)。

3.4表關(guān)聯(lián)

L表連接中的每一個(gè)表應(yīng)指定縮寫(xiě)的別名，別名的命名盡量清晰可辨別；

2、多表關(guān)聯(lián)的時(shí)候，建議所有的關(guān)聯(lián)寫(xiě)成JOIN的形式，例如：

SELECT

Al.sa_aect_no

rJU.3a&cn_ll

FROM

3?tapp_saacnacn_cAl

INNERJOINapp_fs.tapp_saacnacn_tA2ONAl.sa_acct_no-A2.aa_acct_ao

而不允許寫(xiě)成如下形式：

SEXECT

Al?sa_acct_no

rAl.saacn_ll

FROM|

app_fs.tapp-saacnacn_cAl

,app_fs,tapp_aaacnacn_xA2

WHEREAl?aa_acct_no.aa__acct_no

3、建議一個(gè)SQL語(yǔ)句中多表關(guān)聯(lián)的關(guān)聯(lián)表不要超過(guò)10張表；

4、幾個(gè)大小差不多的表做關(guān)聯(lián)時(shí)，過(guò)濾性較強(qiáng)的優(yōu)先做aJOIN;

5、在大/大/小三個(gè)表內(nèi)關(guān)聯(lián)時(shí)，避免先把兩個(gè)大表進(jìn)行JOIN，除非過(guò)濾性

非常強(qiáng)；例如：pg_namespace為小表，其他2個(gè)表為大表

一住?不要重耳關(guān)藁毗守

set]oiii_collapse_lL?it=l：

SELECT

a.attnane

ta.attnornull

FRCK

(pg_catalog.pg_namespacen

INNERJOINpg_catalo^.pg_classcONn.old-c.r€lnamespace>

INNZRJOINpg_catalog.pg__attributeaONa.artrelid?c.old

WHERE

a.at;t-auii>0AMDNOTa.at.tisdroppcd

andn.napname-'publlc,

andc.xelklnd*'!'

6、在大/小/小三個(gè)表內(nèi)聯(lián)時(shí)，優(yōu)先把兩個(gè)小表進(jìn)行JOIN:

SELECT*

FROM(smalltableAASAINNERJOINsmalltableBASBONA.key=B.key)

INNERJOINbigtableASCONC.key=A.key

7、在關(guān)聯(lián)大表的時(shí)候，擺布兩個(gè)連接表的關(guān)聯(lián)字段不能同時(shí)存在高重復(fù)值

的情況，以免因重復(fù)記錄關(guān)聯(lián)產(chǎn)生巨大的中間結(jié)果,造成磁盤(pán)占用比例的大幅增

長(zhǎng)；例如：如果一個(gè)100萬(wàn)的重復(fù)記錄表和一個(gè)1萬(wàn)的重復(fù)記錄表關(guān)聯(lián)，結(jié)果

會(huì)高達(dá)100萬(wàn)*1萬(wàn)=100億條記錄；

8、在使用小表LEFTJOIN超大表（記錄數(shù)過(guò)億）時(shí)，強(qiáng)烈建議把LEFTJOIN

修改為先INNERJOIN，再LEFTJION的方式實(shí)現(xiàn)。這樣既可以提高性能，也

能避免Greenplum產(chǎn)生大量的暫時(shí)文件；因?yàn)樵贕reenplum數(shù)據(jù)庫(kù)中，對(duì)于

LEFTJOIN語(yǔ)句，服務(wù)器會(huì)固定使用右表的記錄，構(gòu)造Hash表，然后用Hash

Join的方式實(shí)現(xiàn)關(guān)聯(lián)；如果右表非常大，會(huì)導(dǎo)致Hash表需要占用大量的內(nèi)存，

如果內(nèi)存超出限制，系統(tǒng)會(huì)把Hash表的內(nèi)容，寫(xiě)入到文件系統(tǒng)的暫時(shí)文件中，

如果右表是一個(gè)超大表，可能在執(zhí)行此語(yǔ)句的時(shí)候，系統(tǒng)會(huì)寫(xiě)入大量暫時(shí)文件,

造成系統(tǒng)占用空間大幅增加；

如果是INNERJOIN語(yǔ)句,系統(tǒng)會(huì)自動(dòng)選擇用小表建立Hash表。

例如：如下LEFTJOIN語(yǔ)句：

—leftjoin超大表

5ILECT

A.circust_no|

FA.p9_3plit_brftnch_cd

,B?ui_cuat_no

FROMbaae_fs.aoddc_ciccrccO_hASA

I￡FIJOIN.aoddc_Glcl￡ciO_hASB

ONA.cl_cust_ao?B.d_cu3t_no

AHDk.p^_split_bxanch_cd=B.p9__split_branch_cd

其執(zhí)行計(jì)劃如下：

制曜口

效需如扁反歷史

"[/L'〉二

?^^_一0

I>

aoddjctfdO」Hash

從執(zhí)行計(jì)劃可以看出，系統(tǒng)會(huì)掃描右表aoddc_cicifciO_h,對(duì)其所有數(shù)據(jù)

建立一個(gè)Hash表；

如果aoddc_cicifciO_h是一個(gè)超大表，那末LEFTJOIN可以改寫(xiě)如下：

--改百為先INKERJOIN再LEFTJOIN

WITHTEMP_INNER_RESULTAS(

SELECT

B.ci_cuat_no

,B.p9_spl1t_branoh_cd

FROMfcaae_f9.aoddc_cicczccO_hASA

INNER-JOINbase_fs.acxidc_cicifciO_hASB

OKA?ci_cust_no-B.ci_cust_n.o

ANDA.p9__split_branch_cd*B.p9_splxt_bzanch__cd

)^,?

select

A.ci-cu3t-no

,A?p9_3plit_branch_cd

,B.circust_no

frombase_fs.aoddc_ciccrccO_hk5A|

LEET-JOINTEMP-IN1(ER_RE5ULTASB

ONA.ci_cu3t_no?B.ci_cu3t_no

ANDA.p9_splic_brancb_cd?B.p9_split__l)ranch__c<l

9、表通過(guò)分布鍵關(guān)聯(lián)時(shí)，不要使用表達(dá)式字段的方式進(jìn)行關(guān)聯(lián)，否則會(huì)導(dǎo)

致數(shù)據(jù)重分布，舉例如下：

--錯(cuò)誤的關(guān)聯(lián)方式，導(dǎo)致數(shù)據(jù)重分布

Select*frombase_fs.aoddc_ciccrccO_hASA

LEFTJOINtemp_resultASBONtrim(A.ci_cust_no)=B.ci_cust_no

--正確的關(guān)聯(lián)方式

Select*frombase_fs.aoddc_ciccrccO_hASA

LEFTJOINtemp_resultASBONA.ci_cust_no=B.ci_cust_no

3.5排序語(yǔ)句

1、不要在視圖中使用OrderBy排序語(yǔ)句，在視圖中，排序語(yǔ)句會(huì)被忽略;

2、ORDERBY語(yǔ)句執(zhí)行成本很高，建議盡量避免使用；

3、不要在大的數(shù)據(jù)結(jié)果集上執(zhí)行排序操作;

4、PartitionBy.Union內(nèi)部實(shí)現(xiàn)需要對(duì)數(shù)據(jù)排序，在數(shù)據(jù)量在千萬(wàn)級(jí)另！J

下，差別不大，但如果數(shù)據(jù)量在億級(jí)別上，建議盡量使用groupby實(shí)現(xiàn)，盡量

避免orderby操作,舉例如下：

Selectcust_noxcust_namefromBigTableA

Union

Selectcust_no,cust_namefromBigTableB

建議改為groupby實(shí)現(xiàn)：

Selectcust.no^from

(

Selectcust_no,cust_namefromBigTableA

UnionALL

Selectcust_norcust_namefromBigTableB

)ASP

Groupbycust_nozcust_name

3.6嵌套子查詢

建議子查詢嵌套的層次不要超過(guò)4層；如果查詢過(guò)于復(fù)雜，應(yīng)對(duì)查詢進(jìn)行拆

分，分為多個(gè)較簡(jiǎn)單的執(zhí)行語(yǔ)句配合暫時(shí)表來(lái)實(shí)現(xiàn)；

3.7UNION/UNIONALL

1、UNION操作，如果不需要去重，請(qǐng)用UNIONALL替代。

例如，如下語(yǔ)句：

stzxn

aac?Hfccaasc

TMM

.MMO.SIScrseoJI

CVIOi

SCXT7

8jteref

new一

-MdAC.：isU

如口

ms#H3F)r

_aK_KTT/】_?

,■'[.<—.:”/■二

*MHc_dujcct)>jfiffirAMooartfrlS(上川；?*vtentKit)SectUhtoweGMMHMabarItfcl(tfct2：tt^nentxifi)

■，

aodoc.rcfiofl..

可替換為：

SELECT

ci_cusjno,p9._aplit_branch_cd

FROM

lXL3U_f，?AgdC._cicGrccO_h

UNIONALL

SELECT

cl_Cusjna,p9._jplX工auxh_8

FROM

ba3jf3?aoddc.^cicifciO_h

4；

代語(yǔ)口

故掘榆出#5日消息歷史

■■■■■I

n________KSffl,________K****^

——?>

-)_hj/AppendGatherMotion16:1(skel;segments:16)

ffl

aoddcoofdO_h

操作，對(duì)于不少?gòu)?fù)雜的子查詢，不建議超過(guò)個(gè)子句進(jìn)行如果大

5UNIONALLO

量結(jié)果集需要UNIONALL,可把所有結(jié)果集都插入到暫時(shí)表。這樣的效率比大

量的UNIONALL高。

3.8高效SQL寫(xiě)法的建議

1、在SQL語(yǔ)句的執(zhí)行計(jì)劃中，應(yīng)通過(guò)優(yōu)化執(zhí)行語(yǔ)句,盡量避免數(shù)據(jù)重分布

操作,可使用Explain命令檢查SQL語(yǔ)句是否存在redistributed,broadcast

等操作，并檢查操作是否合理；

例如:兩張表base_fs.aoddc_ciccrccO_h和base_fs.aoddc_cicifciO_h,

它們的分布鍵一致，定義如下：

DISTRIBUTEDBY(cl-cusr-no,p9_gplix_branch_cd);

SQL語(yǔ)句1寫(xiě)法如下：

-SQL1.關(guān)聯(lián)手板沒(méi)有使用包含分布制

StLECTcount(*)

FROMbase_fs.aoddc_clccrccO_hASA

INNERJOINbase_fs.aoddc_cidfclO_hASBONA.ci_cust_no=B.d_cust_no

其執(zhí)行計(jì)劃如下：

QUIRYPLAN

test

1加“13（cote-*!rcwr-1

->NocicaUt!<?Xlot9rlf|乂..“咖配22rtwl5（KA9）

3->A^gr?9?i4row*widthsJ

4

5RS1,Coadit>?olcutoe?e.Mcute09

■->NHIB14il<?egrwr.oit?>rt?w?-4SSJMwliJtWll

RamRf/ib.aiau4ttt

??>9^19c?nCfi?加eclclfcioYlt>?x）?C-9.Ce.S6cb

?>Baf（一一.490433.11f

10I

11R?thQ*?.alcuatno

12-?—ZFxtrtjaoMo.cerccCh??<=rvidth"?!l

在執(zhí)行計(jì)劃中，包含了RedistributeMotion操作，就需要在節(jié)點(diǎn)之間重

分布數(shù)據(jù)；可將SQL語(yǔ)句優(yōu)化，改寫(xiě)如下，把分布鍵包含進(jìn)關(guān)聯(lián)字段，可比較

數(shù)據(jù)重分布，改善性能：

—SQL2,關(guān)聯(lián)字發(fā)包含全部分存再

SELECTcountf*)

FROMbase-fs.aoddc_ciccrccO_hASA

INNEKJOINbase.fs.aoddc-cicifciO_hASB

ONA.cl.custngB.cicustnoANDA,p9splitbranchcd?B.p9splLtbranchcd

其執(zhí)行計(jì)劃如下：

QUERYPLAN

text

1JUcRtsate《-"，二能96.S1.jr”g6?62rcwi-1

2->M0C19C】，:；(tlxoel；9?9T?nC?t“)(3OrtM7l995.9?..<7tsn.49f：VALW13tftw?)

3->“gr*9As(coet?4789*9.32.3SrowealwldthaS)

4->RanhJan(coetal39137.n..4*70244"rcvwMUnvldtM)

SH8bCend：b?eioustno-a.eleuataoMDb.p9splitbranched-a.p92：

6>StqEeanoc*odasaicmxOnCicovt<.CO..?S；L48.€1row?T%2"vidth-L22)

7____________________2>m皿?二'xowfl-^ie^7kviath-^>____________________________

?->*<fataaaaoddeexocsceoba(oo?aG?ao..si::'.

2、在關(guān)聯(lián)字段中，盡量包含分布鍵作為關(guān)聯(lián)條件，避免數(shù)據(jù)重分布;

3、在Where條件中，盡量保證每一個(gè)節(jié)點(diǎn)的過(guò)濾后的結(jié)果集是均勻的,

避免數(shù)據(jù)傾斜；

4、對(duì)于大表的UNION操作，如果不需要去重，請(qǐng)用UNIONALL替代;

5、對(duì)于大表的UNION操作，如果需要去重,請(qǐng)用UNIONALL加之GROUP

BY替代，因?yàn)閁NION操作需要執(zhí)行SORT操作，執(zhí)行成本更高；

例子如下：