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

1、第一章、/»、 刖言1.1文檔目的1.2預(yù)期讀者1.3參考資料第二章設(shè)計(jì)規(guī)范2.1數(shù)據(jù)庫對象數(shù)量2.2表創(chuàng)建規(guī)范2.3表結(jié)構(gòu)設(shè)計(jì)2.3.1字段命名2.3.2數(shù)據(jù)類型2.3.3數(shù)據(jù)分布2.3.4分區(qū).2.3.5壓縮存儲2.3.6索引設(shè)計(jì)2.4其他數(shù)據(jù)庫對象設(shè)計(jì)101113241 schema.132.4.2視圖.142.4.3臨時(shí)表和中間表.15第三章SQL幵發(fā)規(guī)范163.1基本要求163.2 WHER條件163.3分區(qū)字段使用18183.5排序語句.3.6嵌套子查詢表關(guān)聯(lián)3.4212222233.7 UNION/ UNIONALL3.8高效SQL寫法的建議1.2預(yù)期讀者第一章前言1.

2、1 文檔目的隨著Greenplum數(shù)據(jù)庫的正式上線使用。為了保證Greenplum數(shù)據(jù)倉庫系統(tǒng)平臺的平穩(wěn)運(yùn)行，保證系統(tǒng)的可靠性、穩(wěn)定性、可維護(hù)性和高性能。特制定本幵發(fā)規(guī)范,以規(guī)范基于 Greenplum數(shù)據(jù)庫平臺的相關(guān)應(yīng)用幵發(fā)，提高幵發(fā)質(zhì)量。Greenplum數(shù)據(jù)倉庫平臺應(yīng)用的設(shè)計(jì)與幵發(fā)人員;Greenplum數(shù)據(jù)倉庫平臺的系統(tǒng)管理人員和數(shù)據(jù)庫管理員;Greenplum數(shù)據(jù)倉庫平臺的運(yùn)行維護(hù)人員;1.3參考資料參考Greenplum4.3.x版本官方指引:GP DB43Admi nGuide .pdfGP DB43RefGuide. pdfGP DB43UtilityGuide .p df 第

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

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

5、使用二級分區(qū)表，因?yàn)槎壏謪^(qū)表會造成表對象數(shù)量的急劇膨脹;直接影響到數(shù)4、由于過多的數(shù)據(jù)文件會導(dǎo)致操作系統(tǒng)對文件的操作效率降低,據(jù)庫的管理效率。如果數(shù)據(jù)文件數(shù)量過多，建議增加多個表空間，把數(shù)據(jù)表均勻分布到不同的表空間。每個表空間目錄下的數(shù)據(jù)文件數(shù)量，應(yīng)控制在80萬以內(nèi)。文件數(shù)統(tǒng)計(jì)可以直接到某個 Segme nt實(shí)例目錄下指定的表空間目錄下統(tǒng)計(jì)。5、創(chuàng)建數(shù)據(jù)表（DDL的時(shí)候（不含臨時(shí)表和程序中使用的中間表），必須使用tables pace 子句指定用于存儲的表空間，而不是把所有表都存儲在默認(rèn)表空間；例 如:Create table empio yee ( id in t, name varcha

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

7、義與相關(guān)數(shù)據(jù)的加載和使用緊密相關(guān)，數(shù)據(jù)類型的定義決定了數(shù)據(jù)所占用的空間大小，因此，必須慎重設(shè)計(jì)GP數(shù)據(jù)倉庫數(shù)據(jù)表的字段類型。數(shù)據(jù)倉庫的數(shù)據(jù)來自于多個異構(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ù)倉庫平臺的特點(diǎn)和要求，對字段數(shù)據(jù)類型進(jìn)行設(shè)計(jì)。Greenplum數(shù)據(jù)庫的數(shù)據(jù)類型定義需遵循以下原則:1、在滿足業(yè)務(wù)需求的條件下，盡可能選擇空間占用最小的數(shù)據(jù)類型；以節(jié)省數(shù)據(jù)存儲空間;2、在GP系統(tǒng)中，CHAR VARCHA和 TEXT之間不存

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

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

10、斜，那些儲存了較多數(shù)據(jù)的Instanee在處理自己那部分?jǐn)?shù)據(jù)時(shí) 將需要耗費(fèi)更多的工作量。為了實(shí)現(xiàn)數(shù)據(jù)的平坦分布，可以考慮選擇具有唯一性的DK如主鍵。2、本地操作原則在處理查詢時(shí)，很多處理如關(guān)聯(lián)、排序、聚合等若能夠在Instanee本地完成,其效率將遠(yuǎn)高于跨越系統(tǒng)級別（需在Instanee之間交叉?zhèn)鬏敂?shù)據(jù)）的操作。當(dāng)不同的Table使用相同的DK時(shí)，在DK上的關(guān)聯(lián)或者排序操作將會以最高效的方式把絕大部分工作在Instanee本地完成。3、均衡的查詢負(fù)載原則在一個查詢正被處理時(shí)，我們希望所有的Instanee都能夠處理等量的工作負(fù)載, 從而盡可能達(dá)到最好的性能。 通過合理的DK設(shè)計(jì)，盡量使得查詢處

11、理的負(fù)載均勻分 布在每個節(jié)點(diǎn)上，并且盡量保證where條件產(chǎn)生的結(jié)果集在各個節(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字段越少越好。基于以上原則，Gree np lum數(shù)據(jù)倉庫平臺的數(shù)據(jù)表 DK設(shè)計(jì)規(guī)范如下:每個數(shù)據(jù)表必須通過Distributed子句顯式指定分布鍵，不允許使用默認(rèn)DK的方式創(chuàng)建數(shù)據(jù)表;分布鍵字段原則上為1個，應(yīng)盡量不要超過 3個;分

12、區(qū)的父子表的分布鍵應(yīng)完全一致;中間過程表、臨時(shí)表、派生表的 DK應(yīng)盡可能保持和源表一致;具有關(guān)聯(lián)關(guān)系的數(shù)據(jù)表，應(yīng)盡可能使用關(guān)聯(lián)字段作為分布鍵;分布鍵字段不可執(zhí)行Up date操作;應(yīng)選擇數(shù)據(jù)才建議設(shè)置為了保證數(shù)據(jù)分布均勻，在沒有合適字段作為分布鍵的情況下, 表的主鍵作為分布鍵;對于沒有邏輯主鍵，又沒有其他合適字段作為分布鍵的數(shù)據(jù)表,其分布策略為Distributed Randomly，這只應(yīng)該為最后的選擇;隨機(jī)分布的適合使用場景：查詢時(shí)不需要和其它表關(guān)聯(lián)、或只與小表關(guān)聯(lián)的 數(shù)據(jù)表，使用隨機(jī)分布策略。234分區(qū)表分區(qū)用以解決特別大的表的問題，分區(qū)表在執(zhí)行給定的查詢語句時(shí)，掃描相關(guān)的部分?jǐn)?shù)據(jù)而不是

13、全表的數(shù)據(jù)從而提高查詢性能。分區(qū)表對于數(shù)據(jù)庫的管理也有 幫助。并不是任何數(shù)據(jù)表都適合做分區(qū)，應(yīng)從如下幾個方面判斷是否應(yīng)進(jìn)行分區(qū):1、表是否足夠大?只有非常大的事實(shí)表才適合做表分區(qū)。若在一張表中有數(shù)億條記錄，從邏輯上把表分成較小的分區(qū)將可以改善性能。而對于只有數(shù)萬條或者更少記錄的表，對分 區(qū)預(yù)先進(jìn)行的管理幵銷將遠(yuǎn)大于可以獲得的性能改善。2、對目前的性能不滿意?作為一種調(diào)優(yōu)方案，應(yīng)該在查詢性能低于預(yù)期時(shí)再考慮表分區(qū)。3、查詢條件是否能匹配分區(qū)條件?檢查查詢語句的 WHER條件是否與考慮分區(qū)的 COLUM一致。例如，如果大部分的查詢使用日期條件，那么按照月或者周的日期分區(qū)設(shè)計(jì)也許很有用，而如果查詢

14、條件更多的是使用地區(qū)條件，可以考慮使用地區(qū)將表做列表類型的分區(qū)。4、按照某個規(guī)則數(shù)據(jù)是否可以被均勻的分拆?應(yīng)該選擇盡量把數(shù)據(jù)均勻分拆的規(guī)則。若每個分區(qū)儲存的數(shù)據(jù)量相當(dāng)，那么查詢性能的改善將與分區(qū)的數(shù)量相關(guān)。例如，把一張表分為10個分區(qū)，命中單個分區(qū)條件的查詢掃表性能將比未分區(qū)的情況下高10倍。如果以上幾個方面的回答都是Y es，這樣的表可以通過分區(qū)策略來提高查詢性能。如上面章節(jié)所述，在Greenplum中，每個分區(qū)子表都對應(yīng)一張獨(dú)立的數(shù)據(jù)表,系統(tǒng)通過父子表之間的繼承關(guān)系來維護(hù)分區(qū)定義信息。如果過多的數(shù)據(jù)表進(jìn)行了分 區(qū)，會造成表對象數(shù)量過多，系統(tǒng)元數(shù)據(jù)急劇膨脹，給系統(tǒng)的運(yùn)行和維護(hù)帶來很大 負(fù)擔(dān)。

15、因此，還要綜合考慮系統(tǒng)的表數(shù)據(jù)量情況， 才可決定是否對數(shù)據(jù)表進(jìn)行分區(qū)。基于以上原則，Gree nplum數(shù)據(jù)庫數(shù)據(jù)分區(qū)的使用規(guī)范如下:在性能可以滿足的情況下，盡量不使用數(shù)據(jù)分區(qū);因會造成表對象數(shù)量過多，增加執(zhí)行計(jì)劃生成的復(fù)雜性，禁止使用二級分區(qū);數(shù)據(jù)量在億級別以下，建議不要使用分區(qū);表的數(shù)據(jù)在單個實(shí)例的數(shù)據(jù)量在 100萬級別以下,不需要分區(qū);分區(qū)字段不可以UPDATE需要用delete + insert或者 truncate + insert替代實(shí)現(xiàn)。235壓縮存儲Greenplum數(shù)據(jù)表分兩種類型：heap表和AO表(Append-optimized )。在Greenplum數(shù)據(jù)庫中，需要對

16、數(shù)據(jù)進(jìn)行壓縮，數(shù)據(jù)表則需要設(shè)置為A0表。對數(shù)據(jù)表進(jìn)行壓縮，可以減少磁盤占用空間，同時(shí)也減少了對IO資源的幵銷(以CPU資源換10資源)。特別是在目前IO資源不足的硬件環(huán)境下，數(shù)據(jù)庫設(shè)計(jì)應(yīng)該盡可能多的使用AO表。建議在選擇壓縮儲存模式時(shí)，最好根據(jù)比較測試的結(jié)果來確定。綜合以上考慮，數(shù)據(jù)表壓縮的設(shè)計(jì)規(guī)范如下:數(shù)據(jù)量在百萬級以下的小表，不建議使用壓縮存儲;不要在壓縮文件系統(tǒng)使用壓縮存儲;壓縮表建議統(tǒng)一使用zlib壓縮算法，壓縮級別為6 (appen do nl y=true,comp resst yp e=zlib,com pr esslevel=6);，此壓縮設(shè)置滿足大多數(shù)的使用場景。建議對數(shù)據(jù)倉

17、庫中的記錄數(shù)超過 1億的事實(shí)表、歷史數(shù)據(jù)表采用壓縮存儲;所有歷史數(shù)據(jù)表、備份表、歸檔表統(tǒng)一使用壓縮存儲;236索引設(shè)計(jì)在分布式數(shù)據(jù)庫 GPDB中，應(yīng)盡量避免使用索引。 GPDB中大部分應(yīng)用場景是使用順序掃描。與傳統(tǒng)的 OLTP數(shù)據(jù)庫不同的是，Greenplum中數(shù)據(jù)表的數(shù)據(jù)是分布在多個節(jié)點(diǎn)上的。這意味著每個節(jié)點(diǎn)都掃描全部數(shù)據(jù)的一小部分來查找結(jié)果。如果使 用了表分區(qū)，掃描的數(shù)據(jù)可能更少。通常，這種情況下使用索引未必能提升性能。索引更易于改善OLTP類型的工作負(fù)載，因其返回很少量的數(shù)據(jù)，當(dāng)情況合適時(shí) 查詢優(yōu)化器會把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。添加索引會帶 來一些數(shù)據(jù)庫幵銷，其必定

18、占用相當(dāng)?shù)拇鎯臻g，并且表更新時(shí)需維護(hù)索引。需確 保索引的創(chuàng)建在查詢工作負(fù)載中真正被使用到。同時(shí)，需要檢查索引的確對于查詢 性能有顯著的改善（與順序掃描的性能相比）。Greenplum支持B-tree索引和位圖（Bitmap ）索引。因此，使用索引時(shí)，需要 綜合考慮以下問題:1、查詢工作負(fù)載類型：索引更適合于OLT P類型的工作負(fù)載，其返回很少量的數(shù)據(jù)，對于OLAF類型的查詢負(fù)載，在 GPD即索引通常作用不大;2、壓縮表：在查詢少量數(shù)據(jù)的情況下，索引能夠改善AO表上的查詢性能，當(dāng)情況合適時(shí)查詢優(yōu)化器會把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃描。對 于壓縮數(shù)據(jù)來說，索引訪問數(shù)據(jù)的方法是解壓需

19、要的記錄而不是全部解壓;3、避免在頻繁更新的列上使用索引。在頻繁更新的列上創(chuàng)建索引，當(dāng)該列被更新時(shí)，需要消耗大量的寫磁盤資源和CPU計(jì)算資源;4、在高選擇性的列適合使用B-tree索引，選擇性指的是列中DISTINCT值的數(shù)量除以表中的記錄.例如，如果一張表中有1000行記錄且有800個DISTINCT值，選 擇性指數(shù)為0.8，這被認(rèn)為是良好的。唯一索引總是具備 1.0的選擇比，這是最好的情況;5、低選擇性的列適合使用 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)

20、常用在查詢條件中。對于大表來說，查詢語句WHER條件中經(jīng)常用到的列，可以考慮使用索引。綜合以上情況，結(jié)合 Greenplum平臺的特點(diǎn)，索引設(shè)計(jì)的規(guī)范如下:原則上，數(shù)據(jù)倉庫中的數(shù)據(jù)表不建立索引。只有提供給外部用戶訪問的表,才考慮按用戶訪問特性，針對常用查詢字段建立索引;對于跑批的中間表和臨時(shí)表，不允許創(chuàng)建索引;對于記錄數(shù)在百萬級別以下的小表，建議不使用索引;創(chuàng)建組合索引時(shí)，必須將經(jīng)常作為查詢條件且可選擇性最大的列設(shè)置為索引 的首列;不允許創(chuàng)建冗余索引;對于區(qū)別度高的索引，應(yīng)使用B-tree索引，例如賬號、合同號等等；對于區(qū)別度低的索引，應(yīng)使用 Bitmap索引，例如機(jī)構(gòu)、產(chǎn)品類型等等;創(chuàng)建組合

21、索引時(shí)，建議列數(shù)不要超過 5列;每張數(shù)據(jù)表的索引數(shù)，建議不超過 5個;在創(chuàng)建和更新索引后，必須執(zhí)行Analyze操作，更新索引的統(tǒng)計(jì)信息;在對大表進(jìn)行數(shù)據(jù)加載的時(shí)候，如果存在索引，建議先刪除索引，待數(shù)據(jù)加 載完成，再重新創(chuàng)建索引;對頻繁更新的數(shù)據(jù)表，應(yīng)定期對其執(zhí)行rein dex操作，以重建索引;如果在分區(qū)表中使用了索引，不允許在子表上單獨(dú)創(chuàng)建和修改索引；通常, 刪除頂級分區(qū)的索引，系統(tǒng)會自動刪除相關(guān)子表的索引， 但如果子表的索引 有缺失，將不能自動刪除子表的索引，需要一一手動刪除。不再使用的索引必須刪除;2.4其他數(shù)據(jù)庫對象設(shè)計(jì)241 schema模式(Schema)是在DB內(nèi)組織對象的一種

22、邏輯結(jié)構(gòu)。模式可以允許用戶在一個DB內(nèi)不同的模式之間使用相同Name的對象(比如Table)。Schema命名不允許出現(xiàn)中文字。Schema的規(guī)劃與創(chuàng)建建議由系統(tǒng)管理員或應(yīng)用設(shè)計(jì)人員統(tǒng)一規(guī)劃和設(shè)計(jì)。不允許在系統(tǒng)的Schema下創(chuàng)建用戶表；Greenplum的系統(tǒng)Schema如下:序號Schema名稱說明gp_toolkit提供系統(tǒng)管理方面的視圖In formatio n_schema提供元數(shù)據(jù)信息的視圖p g_catalog系統(tǒng)對象元數(shù)據(jù)表pg_aosegAppend only表的輔助兀數(shù)據(jù)表P g_toast大對象存儲p g_bitma pin dex位圖索引對象存儲242視圖視圖的設(shè)計(jì)規(guī)范

23、建議如下:視圖命名不允許使用雙引號包括視圖名，視圖名稱不允許出現(xiàn)中文字;在視圖中，不允許使用 ORDER B語句;對頻繁訪問，具有多個大表關(guān)聯(lián)，并含有復(fù)雜計(jì)算或排序的視圖，建議修改 為物理表;243臨時(shí)表和中間表臨時(shí)表使用規(guī)范如下:對于每天定期執(zhí)行的后臺數(shù)據(jù)處理作業(yè)，建議不要使用臨時(shí)表，因?yàn)槭褂门R時(shí)表，會造成每天都進(jìn)行大量的數(shù)據(jù)表的創(chuàng)建和刪除，引起系統(tǒng)元數(shù)據(jù)表的急劇膨脹，導(dǎo)致需要頻繁的進(jìn)行系統(tǒng)表的Vacuum操作，從而影響系統(tǒng)的使用和穩(wěn)定性。臨時(shí)表和中間表定義時(shí)必須顯示指定分布鍵。臨時(shí)表和中間表，評估表數(shù)據(jù)量，建議大表統(tǒng)一采用壓縮表。第三章SQL幵發(fā)規(guī)范3.1基本要求1、代碼行清晰、整齊、層次

24、分明、結(jié)構(gòu)性強(qiáng)，易于閱讀;2、代碼中應(yīng)具備必要的注釋以增強(qiáng)代碼的可讀性和可維護(hù)性;3、代碼應(yīng)充分考慮執(zhí)行效率，保證代碼的高效性;3.2WHER條件1、在 Where條件過濾中，應(yīng)盡量將函數(shù)處理放在等式的右邊，以提高查詢性厶匕 冃能;2、對于日期（date、timestamp等）類型的字段判斷，條件值可直接使用字符串,GP會自動進(jìn)行轉(zhuǎn)換。無需過多的使用類型轉(zhuǎn)換函數(shù)，如:to date使用:Where caii dt 二'2015-01-01：不需要寫成:Where call dt 二 to date('2015-01-01',' YY YY-MM-DD')

25、;3、在條件過濾中使用函數(shù)，不需要寫select關(guān)鍵字。否則會影響執(zhí)行計(jì)劃的準(zhǔn)確性:錯誤示例:Where t.z_day =in terval'1 min ute',in terval'1 min ute',( selectto char(curre nt timesta mp dd')and t.z hours =( selectto char(curre nt timesta mpHH24')4、系統(tǒng)中很多采用日期分區(qū)的表，分區(qū)字段類型為數(shù)值型( integer )。等式的左邊不要使用數(shù)值運(yùn)算，否則會影響執(zhí)行計(jì)劃對分區(qū)使用的準(zhǔn)確性。問題示例:

26、WHEREtatis_date/100 二 masadw.fn_get_l1m_yyyymm(20150423)可改寫為:Where statis date between 20150401 and 20150430;WHERE statis_date >= 20150401 and statis_date <= 20150430;5、在WHER條件中錯誤的添加1<>1的判斷，會導(dǎo)致執(zhí)行計(jì)劃混亂。問題語句:SELECT'2O14113O':INT as static date,B.DVL PER_CODE,A.CNTY_ID,SUM(A.CAL LDUR

27、)/60.0 AS CALL_DURFrom masamk丄S_GSM_TOL_D A,masamk.lU_UsR_D BWhER<>1 and A.statis_date = 20141130 AND A.USR_ID=B.USR_IDGrOU P by B.DVL PER_CODE,A.CNtY_ID3.3分區(qū)字段使用如上述章節(jié)提到的分區(qū)表的使用原則，使用分期表是為了降低每次表掃描涉及 的數(shù)據(jù)量，已達(dá)到提升 SQL處理效率的目的。如果 SQL語句中沒有準(zhǔn)確的使用分區(qū)字段就會導(dǎo)致遍歷所有分區(qū)，導(dǎo)致SQL執(zhí)行效率低下。除非業(yè)務(wù)特別在多個分區(qū)表關(guān)聯(lián)時(shí)，每個分區(qū)表都需要制定分區(qū)字段的條

28、件。上有特殊要求必須要遍歷所有的（或大部分的）子分區(qū) 。3.4表關(guān)聯(lián)1、表連接中的每個表應(yīng)指定縮寫的別名，別名的命名盡量清晰可辨別;2、多表關(guān)聯(lián)的時(shí)候，建議所有的關(guān)聯(lián)寫成JOIN的形式，例如:而不允許寫成如下形式:3、建議一個SQL語句中多表關(guān)聯(lián)的關(guān)聯(lián)表不要超過 10張表;4、幾個大小差不多的表做關(guān)聯(lián)時(shí)，過濾性較強(qiáng)的優(yōu)先做aJOIN；5、在大/大/小三個表內(nèi)關(guān)聯(lián) 時(shí)，避免先把兩個大表進(jìn)行JOIN，除非過濾性非常強(qiáng)；例如：Pg_namespace為小表，其他2個表為大表6、在大/小/小三個表內(nèi)聯(lián)時(shí)，優(yōu)先把兩個小表進(jìn)行 JOIN：SELECT *FROM (smalltableA AS A INN

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

30、的臨時(shí)文件；因?yàn)樵贕reenplum數(shù)據(jù)庫中，對于LEFT JOIN語句，服務(wù)器會固定使用右表的記錄，構(gòu)造Hash表，然后用Hash Join的方式實(shí)現(xiàn)關(guān)聯(lián)；如果右表非常大，會導(dǎo)致 Hash表需要占用大量的內(nèi)存，如果內(nèi)存超出限制,系統(tǒng)會把Hash表的內(nèi)容，寫入到文件系統(tǒng)的臨時(shí)文件中，如果右表是一個超大表, 可能在執(zhí)行此語句的時(shí)候，系統(tǒng)會寫入大量臨時(shí)文件，造成系統(tǒng)占用空間大幅增加;如果是INNER JOIN語句，系統(tǒng)會自動選擇用小表建立 Hash表。例如：如下LEFT JOIN語句:其執(zhí)行計(jì)劃如下:從執(zhí)行計(jì)劃可以看出，系統(tǒng)會掃描右表aoddc_cicifciO_h ,對其所有數(shù)據(jù)建 立一個Has

31、h表;如果aoddc_cicifci0_h是一個超大表，那么 LEFT JOIN可以改寫如下：9、表通過分布鍵關(guān)聯(lián)時(shí)，不要使用表達(dá)式字段的方式進(jìn)行關(guān)聯(lián)，否則會導(dǎo)致數(shù)據(jù)重分布，舉例如下:-錯誤的關(guān)聯(lián)方式，導(dǎo)致數(shù)據(jù)重分布Select * from base fs.aoddc ciccrccO h AS ALEFTJOINtemp_resultASONtrim(A.ci_cust_ no)=B.ci_cust_ no-正確的關(guān)聯(lián)方式Select * from base fs.aoddc ciccrccO h AS ALEFT JOIN temp_result AS B ON A.ci_cust_no

32、=B.ci_cust_no3.5排序語句1、2、不要在視圖中使用 Order By排序語句，在視圖中，排序語句會被忽略;ORDER BY語句執(zhí)行成本很高，建議盡量避免使用;3、不要在大的數(shù)據(jù)結(jié)果集上執(zhí)行排序操作;4、Partition By、Union內(nèi)部實(shí)現(xiàn)需要對數(shù)據(jù)排序，在數(shù)據(jù)量在千萬級別下,差別不大，但如果數(shù)據(jù)量在億級別上， 建議盡量使用group by實(shí)現(xiàn)，盡量避免order by操作，舉例如下:Select cust_ no ,cust_ name from BigTableAUnionSelect cust_ no ,cust_ name from BigTableB建議改為gro

33、up by實(shí)現(xiàn):Select cust_ no ,cust_ name fromSelect cust_ no ,cust_ name from BigTableAUn io n ALLSelect cust_ no ,cust_ name from BigTableB)AS PGroup by cust_ no ,cust_ name3.6嵌套子查詢建議子查詢嵌套的層次不要超過 4層;如果查詢過于復(fù)雜，應(yīng)對查詢進(jìn)行拆分,分為多個較簡單的執(zhí)行語句配合臨時(shí)表來實(shí)現(xiàn);3.7 UNION / UNION ALL1、UNION操作，如果不需要去重，請用 UNION ALL替代。例如，如下語句:可替換為

34、:從執(zhí)行計(jì)劃的差異上，可看出，UNION ALL具有更好的性能，所以，如果不需2、不建議過多的使用 UNION ALL除了簡單的少量記錄的 UNION ALL操作，對于很多復(fù)雜的子查詢，不建議超過5個子句進(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_ciccrccO_h 和 base_fs.aoddc_cicifciO_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條件中，盡量保證每個節(jié)點(diǎn)的過濾后的結(jié)果集是均勻的，避免數(shù)據(jù)傾斜;4、對于大表的 UNION操作，如果不需要去重，請用 UNION ALL替代;5、對于大表的