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

1、目目 錄錄 第一章第一章前言前言 .2 1.1文檔目的.2 1.2預(yù)期讀者.2 1.3參考資料.2 第二章第二章設(shè)計(jì)規(guī)范設(shè)計(jì)規(guī)范.3 2.1數(shù)據(jù)庫對象數(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ù)類型.4 2.3.3數(shù)據(jù)分布.5 2.3.4分區(qū).7 2.3.5壓縮存儲(chǔ).8 2.3.6索引設(shè)計(jì).9 2.4其他數(shù)據(jù)庫對象設(shè)計(jì).10 2.4.1schema.10 2.4.2視圖.11 2.4.3臨時(shí)表和中間表 .11 第三章第三章SQL 開發(fā)規(guī)范開發(fā)規(guī)范.12 3.1基本要求.12 3.2WHERE 條件.12 3.3分區(qū)字段使用.13 3.4表關(guān)聯(lián).1

2、3 3.5排序語句.16 3.6嵌套子查詢.16 3.7UNION / UNION ALL.16 . . 3.8高效 SQL 寫法的建議.18 . . 第一章第一章前言前言 1.1 文檔目的文檔目的 隨著 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ù)期讀者預(yù)期讀者 Greenplum 數(shù)據(jù)倉庫平臺(tái)應(yīng)用的設(shè)計(jì)與開發(fā)人員； Greenplum 數(shù)據(jù)倉庫平臺(tái)的系統(tǒng)管理人員和數(shù)據(jù)庫管理員； Gre

3、enplum 數(shù)據(jù)倉庫平臺(tái)的運(yùn)行維護(hù)人員； 1.3 參考資料參考資料 參考 Greenplum4.3.x 版本官方指引： GPDB43AdminGuide.pdf GPDB43RefGuide.pdf GPDB43UtilityGuide.pdf . . 第二章第二章設(shè)計(jì)規(guī)范設(shè)計(jì)規(guī)范 2.1 數(shù)據(jù)庫對象數(shù)量數(shù)據(jù)庫對象數(shù)量 數(shù)據(jù)庫對象類型包括數(shù)據(jù)表、視圖、函數(shù)、序列、索引等等，在 Greenplum 數(shù)據(jù)庫中，系統(tǒng)元數(shù)據(jù)同時(shí)保存在 Master 服務(wù)器和 Segment 服務(wù) 器上，過多的數(shù)據(jù)庫對象會(huì)造成系統(tǒng)元數(shù)據(jù)的膨脹，而過多的系統(tǒng)元數(shù)據(jù)造成 系統(tǒng)運(yùn)行逐步變慢；同時(shí)，類似數(shù)據(jù)庫的備份、恢復(fù)、擴(kuò)

4、容等較大型的操作都 導(dǎo)致效率變慢。因此，依據(jù) GreenplumDB 產(chǎn)品的最佳時(shí)間，單個(gè)數(shù)據(jù)庫的對 象數(shù)量，應(yīng)控制在 10 萬以內(nèi)。 GP 數(shù)據(jù)庫的對象包括：表、視圖、索引、分區(qū)子表、外部表等。 如果數(shù)據(jù)表的數(shù)量太多，建議按應(yīng)用域進(jìn)行分庫，盡量將單個(gè)數(shù)據(jù)庫的表 數(shù)量控制在 10 萬以內(nèi)，可以在一個(gè)集群中創(chuàng)建多個(gè)數(shù)據(jù)庫。 【備注】：在 Greenplum 數(shù)據(jù)庫中，一張分區(qū)表，在數(shù)據(jù)庫中存儲(chǔ)為一張 父表、每張分區(qū)子表都是一張獨(dú)立的庫表；例如：一張按月進(jìn)行分區(qū)的存儲(chǔ)一 年數(shù)據(jù)的表，如果含默認(rèn)分區(qū)，共 14 張表。 2.2 表創(chuàng)建規(guī)范表創(chuàng)建規(guī)范 為了避免數(shù)據(jù)庫表數(shù)量太多，避免單個(gè)數(shù)據(jù)表的數(shù)據(jù)量過大

5、，給系統(tǒng)的運(yùn) 行和使用帶來困難，在 Greenplum 數(shù)據(jù)庫中需遵循如下的表創(chuàng)建規(guī)范： 1、GP 系統(tǒng)表中保存的表名稱都是以小寫保存。通常 SQL 語句中表名對 大小寫不敏感。但不允許在建表語句中使用雙引號(hào)（“” ）包括表名，這樣會(huì)影 響系統(tǒng)表中存儲(chǔ)的名稱，使得表名存在大小寫或特殊字符。表命名也不允許出 現(xiàn)中文字。 2、單個(gè)數(shù)據(jù)庫的數(shù)據(jù)表數(shù)量建議不要超過 10 萬張； 3、禁止使用二級(jí)分區(qū)表，因?yàn)槎?jí)分區(qū)表會(huì)造成表對象數(shù)量的急劇膨脹； 4、由于過多的數(shù)據(jù)文件會(huì)導(dǎo)致操作系統(tǒng)對文件的操作效率降低，直接影響 . . 到數(shù)據(jù)庫的管理效率。如果數(shù)據(jù)文件數(shù)量過多，建議增加多個(gè)表空間，把數(shù)據(jù) 表均勻分布到

6、不同的表空間。每個(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) TABLESPACETABLESPACE tpc_data_01tpc_data_01 distributed by (id); 6、對于數(shù)據(jù)量超過 1TB 的大表,需從應(yīng)用設(shè)計(jì)方面，考慮

7、對大表進(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ì)表結(jié)構(gòu)設(shè)計(jì) 2.3.1字段命名字段命名 表字段的命名，與表名類似。在 GP 系統(tǒng)表中保存的表名稱都是以小寫保 存。通常 SQL 語句中字段名稱對大小寫不敏感。但不允許在建表語句中使用雙 引號(hào)（“” ）包括字段名，這樣會(huì)影響系統(tǒng)表中存儲(chǔ)的名稱，使得表名存在大小 寫或特殊字符。字段命名也不允許出現(xiàn)中文字。 2.3.2數(shù)據(jù)類型數(shù)據(jù)類型 數(shù)據(jù)類型的定義與相關(guān)數(shù)據(jù)的加載和使用緊密相關(guān)，數(shù)據(jù)類型的定義決定 了數(shù)據(jù)所占用的空間大小，因此，必須慎重設(shè)計(jì)

8、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)和要求，對字段數(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)中，可能 CHAR

9、會(huì)表現(xiàn)出最好的性能，但在 GPDB 中是不存在 這種性能優(yōu)勢的。在多數(shù)情況下，應(yīng)該選擇使用 VARCHAR 而不是 CHAR； 3、定長字符串類型使用 varchar,而不使用 char. 4、對于數(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)盡

10、量使用上述規(guī)范數(shù)據(jù)類型，避免出現(xiàn)諸如： Address，INET，ARRAY 等特殊類型字段。 2.3.3數(shù)據(jù)分布數(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ù)表 的分布鍵。對于 Greenplum 數(shù)據(jù)倉庫平臺(tái)，DK 的選擇需要遵循以下原則： 1、數(shù)據(jù)均勻分布原則 為了盡可能達(dá)到最好的性能，所有的 Insta

11、nce 應(yīng)該盡量儲(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í)，我們希望

12、所有的 Instance 都能夠處理等量的工作 負(fù)載，從而盡可能達(dá)到最好的性能。通過合理的 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 一致原則 總分父子表的 DK 應(yīng)保持一致；中間過程表、臨時(shí)表的 DK 應(yīng)盡可能保持 和源表的 DK 一致； 6、DK 精簡原則 DK 字段不宜過多，DK 字段越少越好。 基于以上原則，Greenplum 數(shù)據(jù)倉庫平臺(tái)

13、的數(shù)據(jù)表 DK 設(shè)計(jì)規(guī)范如下： 每個(gè)數(shù)據(jù)表必須通過 Distribiuted 子句顯式指定分布鍵，不允許使用默 認(rèn) DK 的方式創(chuàng)建數(shù)據(jù)表； 分布鍵字段原則上為 1 個(gè)，應(yīng)盡量不要超過 3 個(gè)； 分區(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ù)表的主鍵作為分布鍵； 對于沒有邏輯主鍵，又沒有其他合適字段作為分布鍵的數(shù)據(jù)表，才建議 設(shè)置其分布策略為 Distributed Ran

14、domly， 這只應(yīng)該為最后的選擇； 隨機(jī)分布的適合使用場景：查詢時(shí)不需要和其它表關(guān)聯(lián)、或只與小表關(guān) 聯(lián)的數(shù)據(jù)表，使用隨機(jī)分布策略。 2.3.4分區(qū)分區(qū) 表分區(qū)用以解決特別大的表的問題，分區(qū)表在執(zhí)行給定的查詢語句時(shí)，掃 描相關(guān)的部分?jǐn)?shù)據(jù)而不是全表的數(shù)據(jù)從而提高查詢性能。分區(qū)表對于數(shù)據(jù)庫的 管理也有幫助。并不是任何數(shù)據(jù)表都適合做分區(qū)，應(yīng)從如下幾個(gè)方面判斷是否 應(yīng)進(jìn)行分區(qū)： 1、表是否足夠大？ 只有非常大的事實(shí)表才適合做表分區(qū)。若在一張表中有數(shù)億條記錄，從邏 輯上把表分成較小的分區(qū)將可以改善性能。而對于只有數(shù)萬條或者更少記錄的 表，對分區(qū)預(yù)先進(jìn)行的管理開銷將遠(yuǎn)大于可以獲得的性能改善。 2、對目前的

15、性能不滿意？ 作為一種調(diào)優(yōu)方案，應(yīng)該在查詢性能低于預(yù)期時(shí)再考慮表分區(qū)。 3、查詢條件是否能匹配分區(qū)條件？ 檢查查詢語句的 WHERE 條件是否與考慮分區(qū)的 COLUMN 一致。例如， 如果大部分的查詢使用日期條件，那么按照月或者周的日期分區(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 倍。 . . 如果以上幾

16、個(gè)方面的回答都是 Yes，這樣的表可以通過分區(qū)策略來提高查 詢性能。如上面章節(jié)所述，在 Greenplum 中，每個(gè)分區(qū)子表都對應(yīng)一張獨(dú)立 的數(shù)據(jù)表，系統(tǒng)通過父子表之間的繼承關(guān)系來維護(hù)分區(qū)定義信息。如果過多的 數(shù)據(jù)表進(jìn)行了分區(qū)，會(huì)造成表對象數(shù)量過多，系統(tǒng)元數(shù)據(jù)急劇膨脹，給系統(tǒng)的 運(yùn)行和維護(hù)帶來很大負(fù)擔(dān)。因此，還要綜合考慮系統(tǒng)的表數(shù)據(jù)量情況，才可決 定是否對數(shù)據(jù)表進(jìn)行分區(qū)。 基于以上原則，Greenplum 數(shù)據(jù)庫數(shù)據(jù)分區(qū)的使用規(guī)范如下： 在性能可以滿足的情況下，盡量不使用數(shù)據(jù)分區(qū)； 因會(huì)造成表對象數(shù)量過多，增加執(zhí)行計(jì)劃生成的復(fù)雜性，禁止使用二級(jí) 分區(qū)； 數(shù)據(jù)量在億級(jí)別以下，建議不要使用分區(qū)；

17、表的數(shù)據(jù)在單個(gè)實(shí)例的數(shù)據(jù)量在 100 萬級(jí)別以下，不需要分區(qū)； 分區(qū)字段不可以 UPDATE，需要用 delete + insert 或者 truncate + insert 替代實(shí)現(xiàn)。 2.3.5壓縮存儲(chǔ)壓縮存儲(chǔ) Greenplum 數(shù)據(jù)表分兩種類型：heap 表和 AO 表（Append-optimized） 。 在 Greenplum 數(shù)據(jù)庫中，需要對數(shù)據(jù)進(jìn)行壓縮，數(shù)據(jù)表則需要設(shè)置為 AO 表。 對數(shù)據(jù)表進(jìn)行壓縮，可以減少磁盤占用空間，同時(shí)也減少了對 IO 資源的開銷 （以 CPU 資源換 IO 資源） 。特別是在目前 IO 資源不足的硬件環(huán)境下，數(shù)據(jù)庫 設(shè)計(jì)應(yīng)該盡可能多的使用 AO 表

18、。建議在選擇壓縮儲(chǔ)存模式時(shí)，最好根據(jù)比較 測試的結(jié)果來確定。 綜合以上考慮，數(shù)據(jù)表壓縮的設(shè)計(jì)規(guī)范如下： 數(shù)據(jù)量在百萬級(jí)以下的小表，不建議使用壓縮存儲(chǔ)； 不要在壓縮文件系統(tǒng)使用壓縮存儲(chǔ)； 壓縮表建議統(tǒng)一使用 zlib 壓縮算法，壓縮級(jí)別為 6 (appendonly=true, compresstype=zlib, compresslevel=6);，此壓縮設(shè)置滿足大多數(shù)的使 . . 用場景。 建議對數(shù)據(jù)倉庫中的記錄數(shù)超過 1 億的事實(shí)表、歷史數(shù)據(jù)表采用壓縮存 儲(chǔ)； 所有歷史數(shù)據(jù)表、備份表、歸檔表統(tǒng)一使用壓縮存儲(chǔ)； 2.3.6索引設(shè)計(jì)索引設(shè)計(jì) 在分布式數(shù)據(jù)庫 GPDB 中，應(yīng)盡量避免使用索引。G

19、PDB 中大部分應(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ù)可能更少。通常，這種情況下使用 索引未必能提升性能。 索引更易于改善 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í)，需要檢 查索引的確對于查詢性能有顯著的改善(與順序掃描

20、的性能相比)。 Greenplum 支持 B-tree 索引和位圖（Bitmap）索引。因此，使用索引時(shí)， 需要綜合考慮以下問題： 1、查詢工作負(fù)載類型：索引更適合于 OLTP 類型的工作負(fù)載，其返回很 少量的數(shù)據(jù)，對于 OLAP 類型的查詢負(fù)載，在 GPDB 中索引通常作用不大； 2、壓縮表：在查詢少量數(shù)據(jù)的情況下，索引能夠改善 AO 表上的查詢性能， 當(dāng)情況合適時(shí)查詢優(yōu)化器會(huì)把索引作為獲取數(shù)據(jù)的選擇，而不是一味的全表掃 描。對于壓縮數(shù)據(jù)來說，索引訪問數(shù)據(jù)的方法是解壓需要的記錄而不是全部解 壓； 3、避免在頻繁更新的列上使用索引。在頻繁更新的列上創(chuàng)建索引，當(dāng)該列 被更新時(shí)，需要消耗大量的寫磁

21、盤資源和 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)。經(jīng)常關(guān)聯(lián)(JOIN)的 COLUMN(比如外鍵)上建立索引 或許可以改善 JOIN 的性能，因?yàn)槠淇梢詭椭樵円?guī)劃器使用其他的關(guān)聯(lián)方法； 7、索引列經(jīng)常用在查詢條件中。對于大表來說，查詢語句 WHERE 條件 中經(jīng)

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

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

24、a)是在 DB 內(nèi)組織對象的一種邏輯結(jié)構(gòu)。模式可以允許用戶在 一個(gè) 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 如下： 序號(hào)Schema 名稱說明 1.gp_toolkit 提供系統(tǒng)管理方面的視圖 2.Information_schema 提供元數(shù)據(jù)信息的視圖 3.pg_catalog 系統(tǒng)對象元數(shù)據(jù)表 4.pg_aoseg Append only 表的輔助元數(shù)據(jù)表 5.

25、pg_toast 大對象存儲(chǔ) 6.pg_bitmapindex 位圖索引對象存儲(chǔ) 2.4.2視圖視圖 視圖的設(shè)計(jì)規(guī)范建議如下： 視圖命名不允許使用雙引號(hào)包括視圖名，視圖名稱不允許出現(xiàn)中文字； 在視圖中，不允許使用 ORDER BY 語句； 對頻繁訪問，具有多個(gè)大表關(guān)聯(lián)，并含有復(fù)雜計(jì)算或排序的視圖，建議 修改為物理表； 2.4.3臨時(shí)表和中間表臨時(shí)表和中間表 . . 臨時(shí)表使用規(guī)范如下： 對于每天定期執(zhí)行的后臺(tái)數(shù)據(jù)處理作業(yè)，建議不要使用臨時(shí)表，因?yàn)槭?用臨時(shí)表，會(huì)造成每天都進(jìn)行大量的數(shù)據(jù)表的創(chuàng)建和刪除，引起系統(tǒng) 元數(shù)據(jù)表的急劇膨脹，導(dǎo)致需要頻繁的進(jìn)行系統(tǒng)表的 Vacuum 操作， 從而影響系統(tǒng)的

26、使用和穩(wěn)定性。 臨時(shí)表和中間表定義時(shí)必須顯示指定分布鍵。 臨時(shí)表和中間表，評估表數(shù)據(jù)量，建議大表統(tǒng)一采用壓縮表。 . . 第三章第三章SQL 開發(fā)規(guī)范開發(fā)規(guī)范 3.1 基本要求基本要求 1、代碼行清晰、整齊、層次分明、結(jié)構(gòu)性強(qiáng)，易于閱讀； 2、代碼中應(yīng)具備必要的注釋以增強(qiáng)代碼的可讀性和可維護(hù)性； 3、代碼應(yīng)充分考慮執(zhí)行效率，保證代碼的高效性； 3.2 WHERE 條件條件 1、在 Where 條件過濾中，應(yīng)盡量將函數(shù)處理放在等式的右邊，以提高查 詢性能； 2、對于日期（date、timestamp 等）類型的字段判斷，條件值可直接使用 字符串，GP 會(huì)自動(dòng)進(jìn)行轉(zhuǎn)換。無需過多的使用類型轉(zhuǎn)換函數(shù)，

27、如：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_day = (select to_char(current_timestamp - interval 1 minute, dd) ) and t.z_hours = (select to_char(current_timestamp - interval 1 minute, HH24

28、) ) 4、系統(tǒng)中很多采用日期分區(qū)的表，分區(qū)字段類型為數(shù)值型（integer） 。等 式的左邊不要使用數(shù)值運(yùn)算，否則會(huì)影響執(zhí)行計(jì)劃對分區(qū)使用的準(zhǔn)確性。 問題示例： WHERE statis_date/100 = masadw.fn_get_l1m_yyyymm(20150423) 可改寫為： WHERE statis_date between 20150401 and 20150430; WHERE statis_date = 20150401 and statis_date = 20150430; . . 5、在 WHERE 條件中錯(cuò)誤的添加 11 的判斷，會(huì)導(dǎo)致執(zhí)行計(jì)劃混亂。 問題語句：

29、SELECT 20141130:INT as static_date, B.DVLPER_CODE, A.CNTY_ID,SUM(A.CALL_DUR)/60.0 AS CALL_DUR FROM masamk.LS_GSM_TOL_D A,masamk.IU_USR_D B WHERE 11 and A.statis_date = 20141130 AND A.USR_ID=B.USR_ID GROUP BY B.DVLPER_CODE,A.CNTY_ID 3.3 分區(qū)字段使用分區(qū)字段使用 如上述章節(jié)提到的分區(qū)表的使用原則，使用分期表是為了降低每次表掃描 涉及的數(shù)據(jù)量，已達(dá)到提升 SQL 處

30、理效率的目的。如果 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)表關(guān)聯(lián) 1、表連接中的每個(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è)大表

31、進(jìn)行 JOIN，除非過濾 性非常強(qiáng)；例如：pg_namespace 為小表，其他 2 個(gè)表為大表 6、在大/小/小三個(gè)表內(nèi)聯(lián)時(shí)，優(yōu)先把兩個(gè)小表進(jìn)行 JOIN： 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)。這樣既可以提

32、高 性能，也能避免 Greenplum 產(chǎn)生大量的臨時(shí)文件；因?yàn)樵?Greenplum 數(shù)據(jù)庫 中，對于 LEFT 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)占用空間大幅增加； SELECT * FROM (smalltableA AS A INNER JOIN smalltableB AS B ON A.key=

33、B.key) INNER JOIN bigtable AS C ON C.key=A.key . . 如果是 INNER JOIN 語句，系統(tǒng)會(huì)自動(dòng)選擇用小表建立 Hash 表。 例如：如下 LEFT JOIN 語句： 其執(zhí)行計(jì)劃如下： 從執(zhí)行計(jì)劃可以看出，系統(tǒng)會(huì)掃描右表aoddc_cicifci0_h ,對其所有數(shù)據(jù) 建立一個(gè) Hash 表； 如果 aoddc_cicifci0_h 是一個(gè)超大表，那么 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 *

34、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_no 3.5 排序語句排序語句 1、不要在視圖中使用 Order By 排序語句，在視圖中，排序語句會(huì)被忽略； 2、ORDER BY 語句執(zhí)行成本很高，建議盡量避免使用； 3、不要在大的數(shù)據(jù)結(jié)果

35、集上執(zhí)行排序操作； 4、Partition By 、Union 內(nèi)部實(shí)現(xiàn)需要對數(shù)據(jù)排序，在數(shù)據(jù)量在千萬級(jí)別 下，差別不大，但如果數(shù)據(jù)量在億級(jí)別上，建議盡量使用 group by 實(shí)現(xiàn)，盡 量避免 order by 操作，舉例如下： Select cust_no,cust_name from BigTableA Union Select cust_no,cust_name from BigTableB 建議改為 group by 實(shí)現(xiàn)： Select cust_no,cust_name from ( Select cust_no,cust_name from BigTableA Union AL

36、L Select cust_no,cust_name from BigTableB ) AS P Group by cust_no,cust_name 3.6 嵌套子查詢嵌套子查詢 建議子查詢嵌套的層次不要超過 4 層；如果查詢過于復(fù)雜，應(yīng)對查詢進(jìn)行 拆分，分為多個(gè)較簡單的執(zhí)行語句配合臨時(shí)表來實(shí)現(xiàn)； . . 3.7 UNION / UNION ALL 1、UNION 操作，如果不需要去重，請用 UNION ALL 替代。 例如，如下語句： 可替換為： 從執(zhí)行計(jì)劃的差異上，可看出，UNION ALL 具有更好的性能，所以，如 果不需要去重，僅僅是合并數(shù)據(jù)集，應(yīng)使用 UNION ALL； 2、不建

37、議過多的使用 UNION ALL。除了簡單的少量記錄的 UNION ALL . . 操作，對于很多復(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、對于大表的 UNION 操作，如果不需要去重，請用 UNION