Greenplum數(shù)據(jù)庫設(shè)計(jì)開發(fā)規(guī)范

1、目 錄第一章前言21.1文檔目的21.2預(yù)期讀者21.3參考資料2第二章設(shè)計(jì)規(guī)范32.1數(shù)據(jù)庫對(duì)象數(shù)量32.2表創(chuàng)建規(guī)范32.3表結(jié)構(gòu)設(shè)計(jì)42.3.1字段命名42.3.2數(shù)據(jù)類型42.3.3數(shù)據(jù)分布52.3.4分區(qū)72.3.5壓縮存儲(chǔ)82.3.6索引設(shè)計(jì)92.4其他數(shù)據(jù)庫對(duì)象設(shè)計(jì)102.4.1schema102.4.2視圖112.4.3臨時(shí)表和中間表11第三章SQL開發(fā)規(guī)范123.1基本要求123.2WHERE條件123.3分區(qū)字段使用133.4表關(guān)聯(lián)133.5排序語句163.6嵌套子查詢163.7UNION / UNION ALL163.8高效SQL寫法的建議18第一章 前言1.1 文檔目的

2、隨著Greenplum數(shù)據(jù)庫的正式上線使用。為了保證Greenplum 數(shù)據(jù)倉庫系統(tǒng)平臺(tái)的平穩(wěn)運(yùn)行，保證系統(tǒng)的可靠性、穩(wěn)定性、可維護(hù)性和高性能。特制定本開發(fā)規(guī)范，以規(guī)范基于Greenplum數(shù)據(jù)庫平臺(tái)的相關(guān)應(yīng)用開發(fā)，提高開發(fā)質(zhì)量。1.2 預(yù)期讀者Greenplum數(shù)據(jù)倉庫平臺(tái)應(yīng)用的設(shè)計(jì)與開發(fā)人員；Greenplum 數(shù)據(jù)倉庫平臺(tái)的系統(tǒng)管理人員和數(shù)據(jù)庫管理員；Greenplum 數(shù)據(jù)倉庫平臺(tái)的運(yùn)行維護(hù)人員；1.3 參考資料參考Greenplum4.3.x版本官方指引：GPDB43AdminGuide.pdfGPDB43RefGuide.pdfGPDB43UtilityGuide.pdf第二章 設(shè)

3、計(jì)規(guī)范2.1 數(shù)據(jù)庫對(duì)象數(shù)量數(shù)據(jù)庫對(duì)象類型包括數(shù)據(jù)表、視圖、函數(shù)、序列、索引等等，在Greenplum數(shù)據(jù)庫中，系統(tǒng)元數(shù)據(jù)同時(shí)保存在Master 服務(wù)器和Segment服務(wù)器上，過多的數(shù)據(jù)庫對(duì)象會(huì)造成系統(tǒng)元數(shù)據(jù)的膨脹，而過多的系統(tǒng)元數(shù)據(jù)造成系統(tǒng)運(yùn)行逐步變慢；同時(shí)，類似數(shù)據(jù)庫的備份、恢復(fù)、擴(kuò)容等較大型的操作都導(dǎo)致效率變慢。因此，依據(jù)GreenplumDB產(chǎn)品的最佳時(shí)間，單個(gè)數(shù)據(jù)庫的對(duì)象數(shù)量，應(yīng)控制在10萬以內(nèi)。GP數(shù)據(jù)庫的對(duì)象包括：表、視圖、索引、分區(qū)子表、外部表等。如果數(shù)據(jù)表的數(shù)量太多，建議按應(yīng)用域進(jìn)行分庫，盡量將單個(gè)數(shù)據(jù)庫的表數(shù)量控制在10萬以內(nèi)，可以在一個(gè)集群中創(chuàng)建多個(gè)數(shù)據(jù)庫。【備注】：在

4、Greenplum數(shù)據(jù)庫中，一張分區(qū)表，在數(shù)據(jù)庫中存儲(chǔ)為一張父表、每張分區(qū)子表都是一張獨(dú)立的庫表；例如：一張按月進(jìn)行分區(qū)的存儲(chǔ)一年數(shù)據(jù)的表，如果含默認(rèn)分區(qū)，共14張表。2.2 表創(chuàng)建規(guī)范為了避免數(shù)據(jù)庫表數(shù)量太多，避免單個(gè)數(shù)據(jù)表的數(shù)據(jù)量過大，給系統(tǒng)的運(yùn)行和使用帶來困難，在Greenplum數(shù)據(jù)庫中需遵循如下的表創(chuàng)建規(guī)范：1、GP系統(tǒng)表中保存的表名稱都是以小寫保存。通常SQL語句中表名對(duì)大小寫不敏感。但不允許在建表語句中使用雙引號(hào)（“”）包括表名，這樣會(huì)影響系統(tǒng)表中存儲(chǔ)的名稱，使得表名存在大小寫或特殊字符。表命名也不允許出現(xiàn)中文字。2、單個(gè)數(shù)據(jù)庫的數(shù)據(jù)表數(shù)量建議不要超過10萬張；3、禁止使用二級(jí)分

5、區(qū)表，因?yàn)槎?jí)分區(qū)表會(huì)造成表對(duì)象數(shù)量的急劇膨脹；4、由于過多的數(shù)據(jù)文件會(huì)導(dǎo)致操作系統(tǒng)對(duì)文件的操作效率降低，直接影響到數(shù)據(jù)庫的管理效率。如果數(shù)據(jù)文件數(shù)量過多，建議增加多個(gè)表空間，把數(shù)據(jù)表均勻分布到不同的表空間。每個(gè)表空間目錄下的數(shù)據(jù)文件數(shù)量，應(yīng)控制在80萬以內(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)表空間；例如：Create table employee ( id int,name varchar) TABLESPA

6、CE tpc_data_01 distributed by (id);6、對(duì)于數(shù)據(jù)量超過1TB的大表,需從應(yīng)用設(shè)計(jì)方面，考慮對(duì)大表進(jìn)行優(yōu)化，例如是否可劃分為歷史數(shù)據(jù)表和當(dāng)前數(shù)據(jù)表，并分開存放；是否應(yīng)采用壓縮存儲(chǔ)節(jié)省空間；是否合理分區(qū)；是否應(yīng)定期清理數(shù)據(jù)等等。2.3 表結(jié)構(gòu)設(shè)計(jì)2.3.1 字段命名表字段的命名，與表名類似。在GP系統(tǒng)表中保存的表名稱都是以小寫保存。通常SQL語句中字段名稱對(duì)大小寫不敏感。但不允許在建表語句中使用雙引號(hào)（“”）包括字段名，這樣會(huì)影響系統(tǒng)表中存儲(chǔ)的名稱，使得表名存在大小寫或特殊字符。字段命名也不允許出現(xiàn)中文字。2.3.2 數(shù)據(jù)類型數(shù)據(jù)類型的定義與相關(guān)數(shù)據(jù)的加載和使用緊

7、密相關(guān)，數(shù)據(jù)類型的定義決定了數(shù)據(jù)所占用的空間大小，因此，必須慎重設(shè)計(jì)GP數(shù)據(jù)倉庫數(shù)據(jù)表的字段類型。數(shù)據(jù)倉庫的數(shù)據(jù)來自于多個(gè)異構(gòu)的業(yè)務(wù)應(yīng)用系統(tǒng)，通常情況下，業(yè)務(wù)應(yīng)用系統(tǒng)的字段類型選擇較為隨意，不同的業(yè)務(wù)系統(tǒng)數(shù)據(jù)類型定義存在多樣化，彼此之間差異較大；因此，在數(shù)據(jù)倉庫中，需在參考源系統(tǒng)字段類型定義的情況下，結(jié)合Greenplum 數(shù)據(jù)倉庫平臺(tái)的特點(diǎn)和要求，對(duì)字段數(shù)據(jù)類型進(jìn)行設(shè)計(jì)。Greenplum數(shù)據(jù)庫的數(shù)據(jù)類型定義需遵循以下原則：1、在滿足業(yè)務(wù)需求的條件下，盡可能選擇空間占用最小的數(shù)據(jù)類型；以節(jié)省數(shù)據(jù)存儲(chǔ)空間；2、在GP系統(tǒng)中，CHAR、VARCHAR和TEXT之間不存在性能差異，在其他的DB系統(tǒng)

8、中，可能CHAR會(huì)表現(xiàn)出最好的性能，但在GPDB中是不存在這種性能優(yōu)勢的。在多數(shù)情況下，應(yīng)該選擇使用VARCHAR而不是CHAR；3、定長字符串類型使用varchar,而不使用char.4、對(duì)于數(shù)值類型來說，應(yīng)該盡量選擇更小的數(shù)據(jù)類型來適應(yīng)數(shù)據(jù)；比如，選擇BIGINT類型來存儲(chǔ)SMALLINT類型范圍內(nèi)的數(shù)值，會(huì)造成空間的大量浪費(fèi)。5、用來做Table Join的Column來說，應(yīng)該考慮選擇相同的數(shù)據(jù)類型。如果做Join的Column具有相同的數(shù)據(jù)類型(比如主鍵PrimaryKey與外鍵ForeignKey)，其工作效率會(huì)更高。6、一般情況下，應(yīng)盡量使用上述規(guī)范數(shù)據(jù)類型，避免出現(xiàn)諸如：Add

9、ress，INET，ARRAY等特殊類型字段。2.3.3 數(shù)據(jù)分布基于Greenplum 數(shù)據(jù)倉庫平臺(tái)的特點(diǎn)，每張數(shù)據(jù)表都必須指定分布鍵DK，Greenplum 數(shù)據(jù)庫根據(jù)數(shù)據(jù)分布鍵（Distributed Key,簡稱DK，后同）值來決定記錄存儲(chǔ)在哪一個(gè)segment 上，DK不僅決定了數(shù)據(jù)在集群節(jié)點(diǎn)上的分布，還嚴(yán)重影響數(shù)據(jù)查詢和處理操作的執(zhí)行效率，需要非常慎重的選擇數(shù)據(jù)表的分布鍵。對(duì)于Greenplum 數(shù)據(jù)倉庫平臺(tái)，DK的選擇需要遵循以下原則：1、數(shù)據(jù)均勻分布原則為了盡可能達(dá)到最好的性能，所有的Instance應(yīng)該盡量儲(chǔ)存等量的數(shù)據(jù)。若數(shù)據(jù)的分布不平衡或傾斜，那些儲(chǔ)存了較多數(shù)據(jù)的Inst

10、ance在處理自己那部分?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á)到最好的性能。通過合理的DK設(shè)計(jì)，盡量使得查詢處理的負(fù)載均勻分布在每個(gè)節(jié)點(diǎn)上，并且盡

11、量保證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一致原則總分父子表的DK應(yīng)保持一致；中間過程表、臨時(shí)表的DK應(yīng)盡可能保持和源表的DK一致；6、DK精簡原則DK字段不宜過多，DK字段越少越好。基于以上原則，Greenplum 數(shù)據(jù)倉庫平臺(tái)的數(shù)據(jù)表DK 設(shè)計(jì)規(guī)范如下：ü 每個(gè)數(shù)據(jù)表必須通過Distribiuted子句顯式指定分布鍵，不允許使用默認(rèn)DK 的方式創(chuàng)建數(shù)據(jù)表；ü 分布鍵字段原則上為1個(gè)，應(yīng)盡量不要超過3個(gè)；ü

12、; 分區(qū)的父子表的分布鍵應(yīng)完全一致；ü 中間過程表、臨時(shí)表、派生表的DK應(yīng)盡可能保持和源表一致；ü 具有關(guān)聯(lián)關(guān)系的數(shù)據(jù)表，應(yīng)盡可能使用關(guān)聯(lián)字段作為分布鍵；ü 分布鍵字段不可執(zhí)行Update操作；ü 為了保證數(shù)據(jù)分布均勻，在沒有合適字段作為分布鍵的情況下，應(yīng)選擇數(shù)據(jù)表的主鍵作為分布鍵；ü 對(duì)于沒有邏輯主鍵，又沒有其他合適字段作為分布鍵的數(shù)據(jù)表，才建議設(shè)置其分布策略為Distributed Randomly， 這只應(yīng)該為最后的選擇；ü 隨機(jī)分布的適合使用場景：查詢時(shí)不需要和其它表關(guān)聯(lián)、或只與小表關(guān)聯(lián)的數(shù)據(jù)表，使用隨機(jī)分布策略。2.3.4

13、分區(qū)表分區(qū)用以解決特別大的表的問題，分區(qū)表在執(zhí)行給定的查詢語句時(shí)，掃描相關(guān)的部分?jǐn)?shù)據(jù)而不是全表的數(shù)據(jù)從而提高查詢性能。分區(qū)表對(duì)于數(shù)據(jù)庫的管理也有幫助。并不是任何數(shù)據(jù)表都適合做分區(qū)，應(yīng)從如下幾個(gè)方面判斷是否應(yīng)進(jìn)行分區(qū)：1、表是否足夠大？只有非常大的事實(shí)表才適合做表分區(qū)。若在一張表中有數(shù)億條記錄，從邏輯上把表分成較小的分區(qū)將可以改善性能。而對(duì)于只有數(shù)萬條或者更少記錄的表，對(duì)分區(qū)預(yù)先進(jìn)行的管理開銷將遠(yuǎn)大于可以獲得的性能改善。 2、對(duì)目前的性能不滿意？作為一種調(diào)優(yōu)方案，應(yīng)該在查詢性能低于預(yù)期時(shí)再考慮表分區(qū)。 3、查詢條件是否能匹配分區(qū)條件？檢查查詢語句的WHERE條件是否與考慮分區(qū)的COLUMN一致。

14、例如，如果大部分的查詢使用日期條件，那么按照月或者周的日期分區(qū)設(shè)計(jì)也許很有用，而如果查詢條件更多的是使用地區(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，這樣的表可以通過分區(qū)策略來提高查詢性能。如上面章節(jié)所述，在Greenplum 中，每個(gè)分區(qū)子表都對(duì)應(yīng)一張獨(dú)立的數(shù)據(jù)表，系統(tǒng)通過父子表之間的繼承關(guān)系來維護(hù)分區(qū)定義信息。如果過多的數(shù)據(jù)表

15、進(jìn)行了分區(qū)，會(huì)造成表對(duì)象數(shù)量過多，系統(tǒng)元數(shù)據(jù)急劇膨脹，給系統(tǒng)的運(yùn)行和維護(hù)帶來很大負(fù)擔(dān)。因此，還要綜合考慮系統(tǒng)的表數(shù)據(jù)量情況，才可決定是否對(duì)數(shù)據(jù)表進(jìn)行分區(qū)?；谝陨显瓌t，Greenplum 數(shù)據(jù)庫數(shù)據(jù)分區(qū)的使用規(guī)范如下：ü 在性能可以滿足的情況下，盡量不使用數(shù)據(jù)分區(qū)；ü 因會(huì)造成表對(duì)象數(shù)量過多，增加執(zhí)行計(jì)劃生成的復(fù)雜性，禁止使用二級(jí)分區(qū)；ü 數(shù)據(jù)量在億級(jí)別以下，建議不要使用分區(qū)；ü 表的數(shù)據(jù)在單個(gè)實(shí)例的數(shù)據(jù)量在100萬級(jí)別以下，不需要分區(qū)；ü 分區(qū)字段不可以UPDATE，需要用delete + insert或者truncate + insert替

16、代實(shí)現(xiàn)。2.3.5 壓縮存儲(chǔ)Greenplum 數(shù)據(jù)表分兩種類型：heap表和AO表（Append-optimized）。在Greenplum 數(shù)據(jù)庫中，需要對(duì)數(shù)據(jù)進(jìn)行壓縮，數(shù)據(jù)表則需要設(shè)置為AO表。對(duì)數(shù)據(jù)表進(jìn)行壓縮，可以減少磁盤占用空間，同時(shí)也減少了對(duì)IO資源的開銷（以CPU資源換IO資源）。特別是在目前IO資源不足的硬件環(huán)境下，數(shù)據(jù)庫設(shè)計(jì)應(yīng)該盡可能多的使用AO表。建議在選擇壓縮儲(chǔ)存模式時(shí)，最好根據(jù)比較測試的結(jié)果來確定。綜合以上考慮，數(shù)據(jù)表壓縮的設(shè)計(jì)規(guī)范如下：ü 數(shù)據(jù)量在百萬級(jí)以下的小表，不建議使用壓縮存儲(chǔ)；ü 不要在壓縮文件系統(tǒng)使用壓縮存儲(chǔ)；ü 壓縮表建議統(tǒng)一

17、使用zlib壓縮算法，壓縮級(jí)別為6 (appendonly=true, compresstype=zlib, compresslevel=6);，此壓縮設(shè)置滿足大多數(shù)的使用場景。ü 建議對(duì)數(shù)據(jù)倉庫中的記錄數(shù)超過1億的事實(shí)表、歷史數(shù)據(jù)表采用壓縮存儲(chǔ)；ü 所有歷史數(shù)據(jù)表、備份表、歸檔表統(tǒng)一使用壓縮存儲(chǔ)；2.3.6 索引設(shè)計(jì)在分布式數(shù)據(jù)庫GPDB中，應(yīng)盡量避免使用索引。GPDB中大部分應(yīng)用場景是使用順序掃描。與傳統(tǒng)的OLTP數(shù)據(jù)庫不同的是，Greenplum中數(shù)據(jù)表的數(shù)據(jù)是分布在多個(gè)節(jié)點(diǎn)上的。這意味著每個(gè)節(jié)點(diǎn)都掃描全部數(shù)據(jù)的一小部分來查找結(jié)果。如果使用了表分區(qū)，掃描的數(shù)據(jù)可能更少

18、。通常，這種情況下使用索引未必能提升性能。索引更易于改善OLTP類型的工作負(fù)載，因其返回很少量的數(shù)據(jù)，當(dāng)情況合適時(shí)查詢優(yōu)化器會(huì)把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。添加索引會(huì)帶來一些數(shù)據(jù)庫開銷，其必定占用相當(dāng)?shù)拇鎯?chǔ)空間，并且表更新時(shí)需維護(hù)索引。需確保索引的創(chuàng)建在查詢工作負(fù)載中真正被使用到。同時(shí)，需要檢查索引的確對(duì)于查詢性能有顯著的改善(與順序掃描的性能相比)。Greenplum 支持B-tree索引和位圖（Bitmap）索引。因此，使用索引時(shí)，需要綜合考慮以下問題：1、查詢工作負(fù)載類型：索引更適合于OLTP類型的工作負(fù)載，其返回很少量的數(shù)據(jù)，對(duì)于OLAP類型的查詢負(fù)載，在GPDB中索

19、引通常作用不大；2、壓縮表：在查詢少量數(shù)據(jù)的情況下，索引能夠改善AO表上的查詢性能，當(dāng)情況合適時(shí)查詢優(yōu)化器會(huì)把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。對(duì)于壓縮數(shù)據(jù)來說，索引訪問數(shù)據(jù)的方法是解壓需要的記錄而不是全部解壓；3、避免在頻繁更新的列上使用索引。在頻繁更新的列上創(chuàng)建索引，當(dāng)該列被更新時(shí)，需要消耗大量的寫磁盤資源和CPU計(jì)算資源；4、在高選擇性的列適合使用B-tree索引，選擇性指的是列中DISTINCT值的數(shù)量除以表中的記錄.例如，如果一張表中有1000行記錄且有800個(gè)DISTINCT值，選擇性指數(shù)為0.8，這被認(rèn)為是良好的。唯一索引總是具備1.0的選擇比，這是最好的情況；5、低

20、選擇性的列適合使用bitmap 索引；6、索引列用于關(guān)聯(lián)。經(jīng)常關(guān)聯(lián)(JOIN)的COLUMN(比如外鍵)上建立索引或許可以改善JOIN的性能，因?yàn)槠淇梢詭椭樵円?guī)劃器使用其他的關(guān)聯(lián)方法；7、索引列經(jīng)常用在查詢條件中。對(duì)于大表來說，查詢語句WHERE條件中經(jīng)常用到的列，可以考慮使用索引。綜合以上情況，結(jié)合Greenplum平臺(tái)的特點(diǎn)，索引設(shè)計(jì)的規(guī)范如下：ü 原則上，數(shù)據(jù)倉庫中的數(shù)據(jù)表不建立索引。只有提供給外部用戶訪問的表，才考慮按用戶訪問特性，針對(duì)常用查詢字段建立索引；ü 對(duì)于跑批的中間表和臨時(shí)表，不允許創(chuàng)建索引；ü 對(duì)于記錄數(shù)在百萬級(jí)別以下的小表，建議不使用索引；

21、ü 創(chuàng)建組合索引時(shí)，必須將經(jīng)常作為查詢條件且可選擇性最大的列設(shè)置為索引的首列；ü 不允許創(chuàng)建冗余索引；ü 對(duì)于區(qū)別度高的索引，應(yīng)使用B-tree索引，例如賬號(hào)、合同號(hào)等等；對(duì)于區(qū)別度低的索引，應(yīng)使用Bitmap索引，例如機(jī)構(gòu)、產(chǎn)品類型等等；ü 創(chuàng)建組合索引時(shí)，建議列數(shù)不要超過5列；ü 每張數(shù)據(jù)表的索引數(shù)，建議不超過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ì)

22、其執(zhí)行reindex 操作，以重建索引；ü 如果在分區(qū)表中使用了索引，不允許在子表上單獨(dú)創(chuàng)建和修改索引；通常，刪除頂級(jí)分區(qū)的索引，系統(tǒng)會(huì)自動(dòng)刪除相關(guān)子表的索引，但如果子表的索引有缺失，將不能自動(dòng)刪除子表的索引，需要一一手動(dòng)刪除。ü 不再使用的索引必須刪除；2.4 其他數(shù)據(jù)庫對(duì)象設(shè)計(jì)2.4.1 schema模式(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)一規(guī)劃和設(shè)計(jì)。不允許在系統(tǒng)的Schema下創(chuàng)建

23、用戶表；Greenplum的系統(tǒng)Schema如下：序號(hào)Schema 名稱說明1.gp_toolkit提供系統(tǒng)管理方面的視圖2.Information_schema提供元數(shù)據(jù)信息的視圖3.pg_catalog系統(tǒng)對(duì)象元數(shù)據(jù)表4.pg_aosegAppend only 表的輔助元數(shù)據(jù)表5.pg_toast大對(duì)象存儲(chǔ) 6.pg_bitmapindex位圖索引對(duì)象存儲(chǔ)2.4.2 視圖視圖的設(shè)計(jì)規(guī)范建議如下：ü 視圖命名不允許使用雙引號(hào)包括視圖名，視圖名稱不允許出現(xiàn)中文字；ü 在視圖中，不允許使用ORDER BY語句；ü 對(duì)頻繁訪問，具有多個(gè)大表關(guān)聯(lián)，并含有復(fù)雜計(jì)算或排序的

24、視圖，建議修改為物理表；2.4.3 臨時(shí)表和中間表臨時(shí)表使用規(guī)范如下：ü 對(duì)于每天定期執(zhí)行的后臺(tái)數(shù)據(jù)處理作業(yè)，建議不要使用臨時(shí)表，因?yàn)槭褂门R時(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開發(fā)規(guī)范3.1 基本要求1、代碼行清晰、整齊、層次分明、結(jié)構(gòu)性強(qiáng)，易于閱讀；2、代碼中應(yīng)具備必要的注釋以增強(qiáng)代碼的可讀性和可維護(hù)性；3、代碼應(yīng)充分考慮執(zhí)行效率，保證代

25、碼的高效性；3.2 WHERE條件1、在Where 條件過濾中，應(yīng)盡量將函數(shù)處理放在等式的右邊，以提高查詢性能；2、對(duì)于日期（date、timestamp等）類型的字段判斷，條件值可直接使用字符串，GP會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。無需過多的使用類型轉(zhuǎn)換函數(shù)，如：to_date使用：WHERE call_dt = '2015-01-01'不需要寫成：WHERE call_dt = to_date('2015-01-01','YYYY-MM-DD');3、在條件過濾中使用函數(shù)，不需要寫select關(guān)鍵字。否則會(huì)影響執(zhí)行計(jì)劃的準(zhǔn)確性：錯(cuò)誤示例：WHERE t.z_

26、day = (select to_char(current_timestamp - interval '1 minute', 'dd') ) and t.z_hours = (select to_char(current_timestamp - interval '1 minute', 'HH24') )4、系統(tǒng)中很多采用日期分區(qū)的表，分區(qū)字段類型為數(shù)值型（integer）。等式的左邊不要使用數(shù)值運(yùn)算，否則會(huì)影響執(zhí)行計(jì)劃對(duì)分區(qū)使用的準(zhǔn)確性。問題示例：WHERE statis_date/100 = masadw.fn_get_l1m

27、_yyyymm(20150423)可改寫為：WHERE statis_date between 20150401 and 20150430;WHERE statis_date >= 20150401 and statis_date <= 20150430;5、在WHERE條件中錯(cuò)誤的添加1<>1的判斷，會(huì)導(dǎo)致執(zhí)行計(jì)劃混亂。問題語句：SELECT '20141130':INT as static_date, B.DVLPER_CODE, A.CNTY_ID,SUM(A.CALL_DUR)/60.0 AS CALL_DURFROM masamk.LS_GSM

28、_TOL_D A,masamk.IU_USR_D BWHERE 1<>1 and A.statis_date = 20141130 AND A.USR_ID=B.USR_ID GROUP BY B.DVLPER_CODE,A.CNTY_ID3.3 分區(qū)字段使用如上述章節(jié)提到的分區(qū)表的使用原則，使用分期表是為了降低每次表掃描涉及的數(shù)據(jù)量，已達(dá)到提升SQL處理效率的目的。如果SQL語句中沒有準(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)1

29、、表連接中的每個(gè)表應(yīng)指定縮寫的別名，別名的命名盡量清晰可辨別；2、多表關(guān)聯(lián)的時(shí)候，建議所有的關(guān)聯(lián)寫成JOIN的形式，例如：而不允許寫成如下形式：3、建議一個(gè)SQL語句中多表關(guān)聯(lián)的關(guān)聯(lián)表不要超過10張表；4、幾個(gè)大小差不多的表做關(guān)聯(lián)時(shí)，過濾性較強(qiáng)的優(yōu)先做aJOIN；5、在大/大/小三個(gè)表內(nèi)關(guān)聯(lián)時(shí)，避免先把兩個(gè)大表進(jìn)行JOIN，除非過濾性非常強(qiáng)；例如：pg_namespace為小表，其他2個(gè)表為大表6、在大/小/小三個(gè)表內(nèi)聯(lián)時(shí)，優(yōu)先把兩個(gè)小表進(jìn)行JOIN：SELECT * FROM (smalltableA AS A INNER JOIN smalltableB AS B ON A.key=B.k

30、ey)INNER JOIN bigtable AS C ON C.key=A.key 7、在關(guān)聯(lián)大表的時(shí)候，左右兩個(gè)連接表的關(guān)聯(lián)字段不能同時(shí)存在高重復(fù)值的情況，以免因重復(fù)記錄關(guān)聯(lián)產(chǎn)生巨大的中間結(jié)果，造成磁盤占用比例的大幅增長；例如：如果一個(gè)100萬的重復(fù)記錄表和一個(gè)1萬的重復(fù)記錄表關(guān)聯(lián)，結(jié)果會(huì)高達(dá)100萬*1萬=100億條記錄；8、在使用小表 LEFT JOIN 超大表（記錄數(shù)過億）時(shí)，強(qiáng)烈建議把LEFT JOIN 修改為先INNER JOIN，再 LEFT JION 的方式實(shí)現(xiàn)。這樣既可以提高性能，也能避免Greenplum 產(chǎn)生大量的臨時(shí)文件；因?yàn)樵贕reenplum數(shù)據(jù)庫中，對(duì)于LEFT

31、 JOIN 語句，服務(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)容，寫入到文件系統(tǒng)的臨時(shí)文件中，如果右表是一個(gè)超大表，可能在執(zhí)行此語句的時(shí)候，系統(tǒng)會(huì)寫入大量臨時(shí)文件，造成系統(tǒng)占用空間大幅增加；如果是INNER JOIN 語句，系統(tǒng)會(huì)自動(dòng)選擇用小表建立Hash 表。例如：如下LEFT JOIN 語句：其執(zhí)行計(jì)劃如下：從執(zhí)行計(jì)劃可以看出，系統(tǒng)會(huì)掃描右表aoddc_cicifci0_h ,對(duì)其所有數(shù)據(jù)建立一個(gè)Hash表；如果aoddc_cicifci0_h 是一個(gè)超大表

32、，那么LEFT JOIN 可以改寫如下 ：9、表通過分布鍵關(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 * from base_fs.aoddc_ciccrcc0_h AS A LEFT JOIN temp_result AS B ON trim(A.ci_cust_no)=B.ci_cust_no-正確的關(guān)聯(lián)方式Select * from base_fs.aoddc_ciccrcc0_h AS A LEFT JOIN temp_result AS B ON A.ci_cust_no=B.ci_cust_no3.5 排序

33、語句1、不要在視圖中使用Order By 排序語句，在視圖中，排序語句會(huì)被忽略；2、ORDER BY 語句執(zhí)行成本很高，建議盡量避免使用；3、不要在大的數(shù)據(jù)結(jié)果集上執(zhí)行排序操作；4、Partition By 、Union內(nèi)部實(shí)現(xiàn)需要對(duì)數(shù)據(jù)排序，在數(shù)據(jù)量在千萬級(jí)別下，差別不大，但如果數(shù)據(jù)量在億級(jí)別上，建議盡量使用group by 實(shí)現(xiàn)，盡量避免order by 操作，舉例如下：Select cust_no,cust_name from BigTableAUnion Select cust_no,cust_name from BigTableB建議改為group by 實(shí)現(xiàn)：Select cust

34、_no,cust_name from (Select cust_no,cust_name from BigTableAUnion ALLSelect cust_no,cust_name from BigTableB) AS PGroup by cust_no,cust_name3.6 嵌套子查詢建議子查詢嵌套的層次不要超過4層；如果查詢過于復(fù)雜，應(yīng)對(duì)查詢進(jìn)行拆分，分為多個(gè)較簡單的執(zhí)行語句配合臨時(shí)表來實(shí)現(xiàn)；3.7 UNION / UNION ALL1、UNION操作，如果不需要去重，請(qǐng)用UNION ALL替代。例如，如下語句：可替換為：從執(zhí)行計(jì)劃的差異上，可看出，UNION ALL 具有更好的性

35、能，所以，如果不需要去重，僅僅是合并數(shù)據(jù)集，應(yīng)使用UNION ALL；2、不建議過多的使用UNION ALL。除了簡單的少量記錄的UNION ALL操作，對(duì)于很多復(fù)雜的子查詢，不建議超過5個(gè)子句進(jìn)行UNION ALL。如果大量結(jié)果集需要UNION ALL，可把所有結(jié)果集都插入到臨時(shí)表。這樣的效率比大量的UNION ALL高。3.8 高效SQL寫法的建議1、在SQL語句的執(zhí)行計(jì)劃中，應(yīng)通過優(yōu)化執(zhí)行語句，盡量避免數(shù)據(jù)重分布操作，可使用Explain 命令檢查SQL語句是否存在redistributed ，broadcast等操作，并檢查操作是否合理；例如：兩張表base_fs.aoddc_ciccrcc0_h 和base_fs.aoddc_cicifci0_h ,它們的分布鍵一致，定義如下：SQL語句1寫法如下：其執(zhí)行計(jì)劃如下：在執(zhí)行計(jì)劃中，包含了Redistribute Motion 操作，就需要在節(jié)點(diǎn)之間重分布數(shù)據(jù)；可將SQL語句優(yōu)化，改寫如下，把分布鍵包含進(jìn)關(guān)聯(lián)字段，可比較數(shù)據(jù)重分布，改善性能：其執(zhí)行計(jì)劃如下：2、在關(guān)聯(lián)字段中，盡量包含分布鍵作為關(guān)聯(lián)條件，避免數(shù)據(jù)重分布；3、在Where 條件中，盡量保證每個(gè)節(jié)點(diǎn)的過濾后的結(jié)果集是均勻的，避免數(shù)據(jù)傾斜；4、對(duì)于大表