1. 概述

本開(kāi)發(fā)設(shè)計(jì)建議約定數(shù)據(jù)庫(kù)建模和數(shù)據(jù)庫(kù)應(yīng)用程序開(kāi)發(fā)過(guò)程中，應(yīng)當(dāng)遵守的設(shè)計(jì)規(guī)范。依據(jù)這些規(guī)范進(jìn)行建模，能夠更好的契合 openGauss 的分布式處理架構(gòu)，輸出更高效的業(yè)務(wù) SQL 代碼。

本開(kāi)發(fā)設(shè)計(jì)建議中所陳述的 “建議” 和 “關(guān)注” 含義如下：

• 建議：用戶應(yīng)當(dāng)遵守的設(shè)計(jì)規(guī)則。遵守這些規(guī)則，能夠保證業(yè)務(wù)的高效運(yùn)行；違反這些規(guī)則，將導(dǎo)致業(yè)務(wù)性能的大幅下降或某些業(yè)務(wù)邏輯錯(cuò)誤。
• 關(guān)注：在業(yè)務(wù)開(kāi)發(fā)過(guò)程中客戶需要注意的細(xì)則。用于標(biāo)識(shí)容易導(dǎo)致客戶理解錯(cuò)誤的知識(shí)點(diǎn)（實(shí)際上遵守 SQL 標(biāo)準(zhǔn)的 SQL 行為），或者程序中潛在的客戶不易感知的默認(rèn)行為。

2. 數(shù)據(jù)庫(kù)對(duì)象命名

數(shù)據(jù)庫(kù)對(duì)象命名需要滿足約束：非時(shí)序表長(zhǎng)度不超過(guò) 63 個(gè)字節(jié)，時(shí)序表長(zhǎng)度不超過(guò) 53 個(gè)字符，以字母或下劃線開(kāi)頭，中間字符可以是字母、數(shù)字、下劃線、$、#。

• 【建議】避免使用保留或者非保留關(guān)鍵字命名數(shù)據(jù)庫(kù)對(duì)象。
• 說(shuō)明： 可以使用 select * from pg_get_keywords () 查詢 openGauss 的關(guān)鍵字，或者在關(guān)鍵字章節(jié)中查看。
• 【建議】避免使用雙引號(hào)括起來(lái)的字符串來(lái)定義數(shù)據(jù)庫(kù)對(duì)象名稱，除非需要限制數(shù)據(jù)庫(kù)對(duì)象名稱的大小寫。數(shù)據(jù)庫(kù)對(duì)象名稱大小寫敏感會(huì)使定位問(wèn)題難度增加。
• 【建議】數(shù)據(jù)庫(kù)對(duì)象命名風(fēng)格務(wù)必保持統(tǒng)一。
• 增量開(kāi)發(fā)的業(yè)務(wù)系統(tǒng)或進(jìn)行業(yè)務(wù)遷移的系統(tǒng)，建議遵守歷史的命名風(fēng)格。
• 建議使用多個(gè)單詞組成，以下劃線分割。
• 數(shù)據(jù)庫(kù)對(duì)象名稱建議能夠望文知意，盡量避免使用自定義縮寫（可以使用通用的術(shù)語(yǔ)縮寫進(jìn)行命名）。例如，在命名中可以使用具有實(shí)際業(yè)務(wù)含義的英文詞匯或漢語(yǔ)拼音，但規(guī)則應(yīng)該在數(shù)據(jù)庫(kù)實(shí)例范圍內(nèi)保持一致。
• 變量名的關(guān)鍵是要具有描述性，即變量名稱要有一定的意義，變量名要有前綴標(biāo)明該變量的類型。
• 【建議】表對(duì)象的命名應(yīng)該可以表征該表的重要特征。例如，在表對(duì)象命名時(shí)區(qū)分該表是普通表、臨時(shí)表還是非日志表：
• 普通表名按照數(shù)據(jù)集的業(yè)務(wù)含義命名。
• 臨時(shí)表以 “tmp_+ 后綴” 命名。
• 非日志表以 “ul_+ 后綴” 命名。
• 外表以 “f_+ 后綴” 命名。
• 不創(chuàng)建以 redis_為前綴的數(shù)據(jù)庫(kù)對(duì)象。
• 不創(chuàng)建以 mlog_和以 matviewmap_為前綴的數(shù)據(jù)庫(kù)對(duì)象。
• 【建議】非時(shí)序表對(duì)象命名建議不要超過(guò) 63 字節(jié)。如果過(guò)該長(zhǎng)度內(nèi)核會(huì)對(duì)表名進(jìn)行截?cái)啵瑥亩斐珊驮O(shè)置值不一致的現(xiàn)象。且在不同字符集下，可能造成字符被截?cái)?，出現(xiàn)預(yù)期外的字符。

3. 數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)

3.1Database 和 Schema 設(shè)計(jì)

openGauss 中可以使用 Database 和 Schema 實(shí)現(xiàn)業(yè)務(wù)的隔離，區(qū)別在于 Database 的隔離更加徹底，各個(gè) Database 之間共享資源極少，可實(shí)現(xiàn)連接隔離、權(quán)限隔離等，Database 之間無(wú)法直接互訪。Schema 隔離的方式共用資源較多，可以通過(guò) grant 與 revoke 語(yǔ)法便捷地控制不同用戶對(duì)各 Schema 及其下屬對(duì)象的權(quán)限。

• 從便捷性和資源共享效率上考慮，推薦使用 Schema 進(jìn)行業(yè)務(wù)隔離。
• 建議系統(tǒng)管理員創(chuàng)建 Schema 和 Database，再賦予相關(guān)用戶對(duì)應(yīng)的權(quán)限。

Database 設(shè)計(jì)建議

• 【規(guī)則】在實(shí)際業(yè)務(wù)中，根據(jù)需要?jiǎng)?chuàng)建新的 Database，不建議直接使用數(shù)據(jù)庫(kù)實(shí)例默認(rèn)的 postgres 數(shù)據(jù)庫(kù)。
• 【建議】一個(gè)數(shù)據(jù)庫(kù)實(shí)例內(nèi)，用戶自定義的 Database 數(shù)量建議不超過(guò) 3 個(gè)。
• 【建議】為了適應(yīng)全球化的需求，使數(shù)據(jù)庫(kù)編碼能夠存儲(chǔ)與表示絕大多數(shù)的字符，建議創(chuàng)建 Database 的時(shí)候使用 UTF-8 編碼。
• 【關(guān)注】創(chuàng)建 Database 時(shí)，需要重點(diǎn)關(guān)注字符集編碼（ENCODING）和兼容性（DBCOMPATIBILITY）兩個(gè)配置項(xiàng)。openGauss 支持 A、B、C 和 PG 四種兼容模式，分別表示兼容 O 語(yǔ)法、MY 語(yǔ)法、TD 語(yǔ)法和 POSTGRES 語(yǔ)法，不同兼容模式下的語(yǔ)法行為存在一定差異，默認(rèn)為 A 兼容模式。
• 【關(guān)注】Database 的 owner 默認(rèn)擁有該 Database 下所有對(duì)象的所有權(quán)限，包括刪除權(quán)限。刪除權(quán)限影響較大，請(qǐng)謹(jǐn)慎使用。

Schema 設(shè)計(jì)建議

• 【關(guān)注】如果該用戶不具有 sysadmin 權(quán)限或者不是該 Schema 的 owner，要訪問(wèn) Schema 下的對(duì)象，需要同時(shí)給用戶賦予 Schema 的 usage 權(quán)限和對(duì)象的相應(yīng)權(quán)限。
• 【關(guān)注】如果要在 Schema 下創(chuàng)建對(duì)象，需要授予操作用戶該 Schema 的 create 權(quán)限。
• 【關(guān)注】Schema 的 owner 默認(rèn)擁有該 Schema 下對(duì)象的所有權(quán)限，包括刪除權(quán)限。刪除權(quán)限影響較大，請(qǐng)謹(jǐn)慎使用

3.2 表設(shè)計(jì)

openGauss 是分布式架構(gòu)。數(shù)據(jù)分布在各個(gè) DN 上。總體上講，良好的表設(shè)計(jì)需要遵循以下原則：

• 【關(guān)注】將表數(shù)據(jù)均勻分布在各個(gè) DN 上。數(shù)據(jù)均勻分布，可以防止數(shù)據(jù)在部分 DN 上集中分布，從而導(dǎo)致因存儲(chǔ)傾斜造成數(shù)據(jù)庫(kù)實(shí)例有效容量下降。通過(guò)選擇合適的分布列，可以避免數(shù)據(jù)傾斜。
• 【關(guān)注】將表的掃描壓力均勻分散在各個(gè) DN 上。避免掃描壓力集中在部分 DN 上，而導(dǎo)致性能瓶頸。例如，在事實(shí)表上使用等值過(guò)濾條件時(shí)，將會(huì)導(dǎo)致掃描壓力不均勻。
• 【關(guān)注】減少需要掃描的數(shù)據(jù)量。通過(guò)分區(qū)表的剪枝機(jī)制可以大幅減少數(shù)據(jù)的掃描量。
• 【關(guān)注】盡量減少隨機(jī) I/O。通過(guò)聚簇 / 局部聚簇可以實(shí)現(xiàn)熱數(shù)據(jù)的連續(xù)存儲(chǔ)，將隨機(jī) I/O 轉(zhuǎn)換為連續(xù) I/O，從而減少掃描的 I/O 代價(jià)。
• 【關(guān)注】盡量避免數(shù)據(jù) shuffle。shuffle，是指在物理上，數(shù)據(jù)從一個(gè)節(jié)點(diǎn)，傳輸?shù)搅硪粋€(gè)節(jié)點(diǎn)。shuffle 占用了大量寶貴的網(wǎng)絡(luò)資源，減小不必要的數(shù)據(jù) shuffle，可以減少網(wǎng)絡(luò)壓力，使數(shù)據(jù)的處理本地化，提高數(shù)據(jù)庫(kù)實(shí)例的性能和可支持的并發(fā)度。通過(guò)對(duì)關(guān)聯(lián)條件和分組條件的仔細(xì)設(shè)計(jì)，能夠盡可能地減少不必要的數(shù)據(jù) shuffle。

選擇存儲(chǔ)方案

【建議】表的存儲(chǔ)類型是表定義設(shè)計(jì)的第一步，客戶業(yè)務(wù)類型是決定表的存儲(chǔ)類型的主要因素，表存儲(chǔ)類型的選擇依據(jù)請(qǐng)參考表 1。

表 1 表的存儲(chǔ)類型及場(chǎng)景

選擇分布方案

【建議】表的分布方式的選擇一般遵循以下原則：

表 2 表的分布方式及使用場(chǎng)景

選擇分區(qū)方案

當(dāng)表中的數(shù)據(jù)量很大時(shí)，應(yīng)當(dāng)對(duì)表進(jìn)行分區(qū)，一般需要遵循以下原則：

• 【建議】使用具有明顯區(qū)間性的字段進(jìn)行分區(qū)，比如日期、區(qū)域等字段上建立分區(qū)。
• 【建議】分區(qū)名稱應(yīng)當(dāng)體現(xiàn)分區(qū)的數(shù)據(jù)特征。例如，關(guān)鍵字 + 區(qū)間特征。
• 【建議】將分區(qū)上邊界的分區(qū)值定義為 MAXVALUE，以防止可能出現(xiàn)的數(shù)據(jù)溢出。

典型的分區(qū)表定義如下：

復(fù)制代碼CREATE TABLE staffS_p1( staff_ID NUMBER(6) not null, FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), EMAIL VARCHAR2(25), PHONE_NUMBER VARCHAR2(20), HIRE_DATE DATE, employment_ID VARCHAR2(10), SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(4,2), MANAGER_ID NUMBER(6), section_ID NUMBER(4))PARTITION BY RANGE (HIRE_DATE)( PARTITION HIRE_19950501 VALUES LESS THAN (‘1995-05-01 00:00:00’), PARTITION HIRE_19950502 VALUES LESS THAN (‘1995-05-02 00:00:00’), PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE));

選擇分布鍵

Hash 表的分布鍵選取至關(guān)重要，如果分布鍵選擇不當(dāng)，可能會(huì)導(dǎo)致數(shù)據(jù)傾斜，從而導(dǎo)致查詢時(shí)，I/O 負(fù)載集中在部分 DN 上，影響整體查詢性能。因此，在確定 Hash 表的分布策略之后，需要對(duì)表數(shù)據(jù)進(jìn)行傾斜性檢查，以確保數(shù)據(jù)的均勻分布。分布鍵的選擇一般需要遵循以下原則：

• 【建議】選作分布鍵的字段取值應(yīng)該比較離散，以便數(shù)據(jù)能在各個(gè) DN 上均勻分布。當(dāng)單個(gè)字段無(wú)法滿足離散條件時(shí)，可以考慮使用多個(gè)字段一起作為分布鍵。一般情況下，可以考慮選擇表的主鍵作為分布鍵。例如，在人員信息表中選擇證件號(hào)碼作為分布鍵。
• 【建議】在滿足第一條原則的情況下，盡量不要選取在查詢中存在常量過(guò)濾條件的字段作為分布鍵。例如，在表 dwcjk 相關(guān)的查詢中，字段 zqdh 存在常量過(guò)濾條件 “zqdh=’000001’”，那么就應(yīng)當(dāng)盡量不選擇 zqdh 字段做為分布鍵。
• 【建議】在滿足前兩條原則的情況，盡量選擇查詢中的關(guān)聯(lián)條件為分布鍵。當(dāng)關(guān)聯(lián)條件作為分布鍵時(shí)，join 任務(wù)的相關(guān)數(shù)據(jù)都分布在 DN 本地，將極大減少 DN 之間的數(shù)據(jù)流動(dòng)代價(jià)。

3.3 字段設(shè)計(jì)

選擇數(shù)據(jù)類型

字段設(shè)計(jì)時(shí)，基于查詢效率的考慮，一般遵循以下原則：

• 【建議】盡量使用高效數(shù)據(jù)類型。
• 選擇數(shù)值類型時(shí)，在滿足業(yè)務(wù)精度的情況下，選擇數(shù)據(jù)類型的優(yōu)先級(jí)從高到低依次為整數(shù)、浮點(diǎn)數(shù)、NUMERIC。
• 【建議】當(dāng)多個(gè)表存在邏輯關(guān)系時(shí)，表示同一含義的字段應(yīng)該使用相同的數(shù)據(jù)類型。
• 【建議】對(duì)于字符串?dāng)?shù)據(jù)，建議使用變長(zhǎng)字符串?dāng)?shù)據(jù)類型，并指定最大長(zhǎng)度。請(qǐng)務(wù)必確保指定的最大長(zhǎng)度大于需要存儲(chǔ)的最大字符數(shù)，避免超出最大長(zhǎng)度時(shí)出現(xiàn)字符截?cái)喱F(xiàn)象。除非明確知道數(shù)據(jù)類型為固定長(zhǎng)度字符串，否則，不建議使用 CHAR (n)、BPCHAR (n)、NCHAR (n)、CHARACTER (n)。
• 關(guān)于字符串類型的詳細(xì)說(shuō)明，請(qǐng)參見(jiàn)常用字符串類型介紹。

常用字符串類型介紹

在進(jìn)行字段設(shè)計(jì)時(shí)，需要根據(jù)數(shù)據(jù)特征選擇相應(yīng)的數(shù)據(jù)類型。字符串類型在使用時(shí)比較容易混淆，下表列出了 openGauss 中常見(jiàn)的字符串類型：

表 1 常用字符串類型

3.4 約束設(shè)計(jì)

DEFAULT 和 NULL 約束

• 【建議】如果能夠從業(yè)務(wù)層面補(bǔ)全字段值，那么，就不建議使用 DEFAULT 約束，避免數(shù)據(jù)加載時(shí)產(chǎn)生不符合預(yù)期的結(jié)果。
• 【建議】給明確不存在 NULL 值的字段加上 NOT NULL 約束，優(yōu)化器會(huì)在特定場(chǎng)景下對(duì)其進(jìn)行自動(dòng)優(yōu)化。
• 【建議】給可以顯式命名的約束顯式命名。除了 NOT NULL 和 DEFAULT 約束外，其他約束都可以顯式命名。

局部聚簇

Partial Cluster Key（局部聚簇，簡(jiǎn)稱 PCK）是列存表的一種局部聚簇技術(shù)，在 openGauss 中，使用 PCK 可以通過(guò) min/max 稀疏索引實(shí)現(xiàn)事實(shí)表快速過(guò)濾掃描。PCK 的選取遵循以下原則：

• 【關(guān)注】一張表上只能建立一個(gè) PCK，一個(gè) PCK 可以包含多列，但是一般不建議超過(guò) 2 列。
• 【建議】在查詢中的簡(jiǎn)單表達(dá)式過(guò)濾條件上創(chuàng)建 PCK。這種過(guò)濾條件一般形如 col op const，其中 col 為列名，op 為操作符 =、>、>=、<=、<，const 為常量值。
• 【建議】在滿足上面條件的前提下，選擇 distinct 值比較多的列上建 PCK。

唯一約束

• 【關(guān)注】行存表、列存表均支持唯一約束。
• 【建議】從命名上明確標(biāo)識(shí)唯一約束，例如，命名為 “UNI + 構(gòu)成字段”。

主鍵約束

• 【關(guān)注】行存表、列存表均支持主鍵約束。
• 【建議】從命名上明確標(biāo)識(shí)主鍵約束，例如，將主鍵約束命名為 “PK + 字段名”。

外鍵約束

• 【關(guān)注】行存表支持外鍵約束，列存表不支持外鍵約束。
• 【建議】從命名上明確標(biāo)識(shí)外鍵約束，例如，將外鍵約束命名為 “FK + 字段名”。

檢查約束

• 【關(guān)注】行存表支持檢查約束，而列存表不支持。
• 【建議】從命名上明確標(biāo)識(shí)檢查約束，例如，將檢查約束命名為 “CK + 字段名”。

3.5 視圖和關(guān)聯(lián)表設(shè)計(jì)

視圖設(shè)計(jì)

• 【建議】除非視圖之間存在強(qiáng)依賴關(guān)系，否則不建議視圖嵌套。
• 【建議】視圖定義中盡量避免排序操作。

關(guān)聯(lián)表設(shè)計(jì)

• 【建議】表之間的關(guān)聯(lián)字段應(yīng)該盡量少。
• 【建議】關(guān)聯(lián)字段的數(shù)據(jù)類型應(yīng)該保持一致。
• 【建議】關(guān)聯(lián)字段在命名上，應(yīng)該可以明顯體現(xiàn)出關(guān)聯(lián)關(guān)系。例如，采用同樣名稱來(lái)命名。

4.JDBC 配置

目前，openGauss 相關(guān)的第三方工具都是通過(guò) JDBC 進(jìn)行連接的，此部分將介紹工具配置時(shí)的注意事項(xiàng)。

連接參數(shù)

• 【關(guān)注】第三方工具通過(guò) JDBC 連接 openGauss 時(shí)，JDBC 向 openGauss 發(fā)起連接請(qǐng)求，會(huì)默認(rèn)添加以下配置參數(shù)，詳見(jiàn) JDBC 代碼 ConnectionFactoryImpl 類的實(shí)現(xiàn)。

復(fù)制代碼params = {{ “user”, user },{ “database”, database },{ “client_encoding”, “UTF8” },{ “DateStyle”, “ISO” },{ “extra_float_digits”, “2” },{ “TimeZone”, createPostgresTimeZone() },};

• 這些參數(shù)可能會(huì)導(dǎo)致 JDBC 客戶端的行為與 gsql 客戶端的行為不一致，例如，Date 數(shù)據(jù)顯示方式、浮點(diǎn)數(shù)精度表示、timezone 顯示。
• 如果實(shí)際期望和這些配置不符，建議在 java 連接設(shè)置代碼中顯式設(shè)定這些參數(shù)。
• 【建議】通過(guò) JDBC 連接數(shù)據(jù)庫(kù)時(shí)，應(yīng)該保證下面三個(gè)時(shí)區(qū)設(shè)置一致：
• JDBC 客戶端所在主機(jī)的時(shí)區(qū)。
• openGauss 數(shù)據(jù)庫(kù)實(shí)例所在主機(jī)的時(shí)區(qū)。
• openGauss 數(shù)據(jù)庫(kù)實(shí)例配置過(guò)程中時(shí)區(qū)。
• 說(shuō)明： 時(shí)區(qū)設(shè)置相關(guān)的操作，請(qǐng)參考《安裝指南》中 “企業(yè)版安裝> 安裝準(zhǔn)備 > 準(zhǔn)備軟硬件安裝環(huán)境 > 同步系統(tǒng)時(shí)間” 章節(jié)內(nèi)容。

fetchsize

【關(guān)注】在應(yīng)用程序中，如果需要使用 fetchsize，必須關(guān)閉 autocommit。開(kāi)啟 autocommit，會(huì)令 fetchsize 配置失效。

autocommit

【建議】在 JDBC 向 openGauss 申請(qǐng)連接的代碼中，建議顯式打開(kāi) autocommit 開(kāi)關(guān)。如果基于性能或者其它方面考慮，需要關(guān)閉 autocommit 時(shí)，需要應(yīng)用程序自己來(lái)保證事務(wù)的提交。例如，在指定的業(yè)務(wù) SQL 執(zhí)行完之后做顯式提交，特別是客戶端退出之前務(wù)必保證所有的事務(wù)已經(jīng)提交。

釋放連接

【建議】推薦使用連接池限制應(yīng)用程序的連接數(shù)。每執(zhí)行一條 SQL 就連接一次數(shù)據(jù)庫(kù)，是一種不好的 SQL 編寫習(xí)慣。

【建議】在應(yīng)用程序完成作業(yè)任務(wù)之后，應(yīng)當(dāng)及時(shí)斷開(kāi)和 openGauss 的連接，釋放資源。建議在任務(wù)中設(shè)置 session 超時(shí)時(shí)間參數(shù)。

【建議】使用 JDBC 連接池，在將連接釋放給連接池前，需要執(zhí)行以下操作，重置會(huì)話環(huán)境。否則，可能會(huì)產(chǎn)生因?yàn)闅v史會(huì)話信息導(dǎo)致的對(duì)象沖突。

• 如果在連接中設(shè)置了 GUC 參數(shù)，那么在將連接歸還連接池之前，必須使用 “SET SESSION AUTHORIZATION DEFAULT;RESET ALL;” 將連接的狀態(tài)清空。
• 如果使用了臨時(shí)表，那么在將連接歸還連接池之前，必須將臨時(shí)表刪除。

CopyManager

【建議】在不使用 ETL 工具，數(shù)據(jù)入庫(kù)實(shí)時(shí)性要求又比較高的情況下，建議在開(kāi)發(fā)應(yīng)用程序時(shí)，使用 openGauss JDBC 驅(qū)動(dòng)的 copyManger 接口進(jìn)行微批導(dǎo)入。

5.SQL 編寫

DDL

• 【建議】在 openGauss 中，建議 DDL（建表、comments 等）操作統(tǒng)一執(zhí)行，在批處理作業(yè)中盡量避免 DDL 操作。避免大量并發(fā)事務(wù)對(duì)性能的影響。
• 【建議】在非日志表（unlogged table）使用完后，立即執(zhí)行數(shù)據(jù)清理（truncate）操作。因?yàn)樵诋惓?chǎng)景下，openGauss 不保證非日志表（unlogged table）數(shù)據(jù)的安全性。
• 【建議】臨時(shí)表和非日志表的存儲(chǔ)方式建議和基表相同。當(dāng)基表為行存（列存）表時(shí)，臨時(shí)表和非日志表也推薦創(chuàng)建為行存（列存）表，可以避免行列混合關(guān)聯(lián)帶來(lái)的高計(jì)算代價(jià)。
• 【建議】索引字段的總長(zhǎng)度不超過(guò) 50 字節(jié)。否則，索引大小會(huì)膨脹比較嚴(yán)重，帶來(lái)較大的存儲(chǔ)開(kāi)銷，同時(shí)索引性能也會(huì)下降。
• 【建議】不要使用 DROP…CASCADE 方式刪除對(duì)象，除非已經(jīng)明確對(duì)象間的依賴關(guān)系，以免誤刪。

數(shù)據(jù)加載和卸載

• 【建議】在 insert 語(yǔ)句中顯式給出插入的字段列表。例如：

復(fù)制代碼INSERT INTO task(name,id,comment) VALUES (‘task1′,’100′,’第100個(gè)任務(wù)’);

• 【建議】在批量數(shù)據(jù)入庫(kù)之后，或者數(shù)據(jù)增量達(dá)到一定閾值后，建議對(duì)表進(jìn)行 analyze 操作，防止統(tǒng)計(jì)信息不準(zhǔn)確而導(dǎo)致的執(zhí)行計(jì)劃劣化。
• 【建議】如果要清理表中的所有數(shù)據(jù)，建議使用 truncate table 方式，不要使用 delete table 方式。delete table 方式刪除性能差，且不會(huì)釋放那些已經(jīng)刪除了的數(shù)據(jù)占用的磁盤空間。

類型轉(zhuǎn)換

• 【建議】在需要數(shù)據(jù)類型轉(zhuǎn)換（不同數(shù)據(jù)類型進(jìn)行比較或轉(zhuǎn)換）時(shí)，使用強(qiáng)制類型轉(zhuǎn)換，以防隱式類型轉(zhuǎn)換結(jié)果與預(yù)期不符。
• 【建議】在查詢中，對(duì)常量要顯式指定數(shù)據(jù)類型，不要試圖依賴任何隱式的數(shù)據(jù)類型轉(zhuǎn)換。
• 【關(guān)注】若 sql_compatibility 參數(shù)設(shè)置為 A，在導(dǎo)入數(shù)據(jù)時(shí)，空字符串會(huì)自動(dòng)轉(zhuǎn)化為 NULL。如果需要保留空字符串需要 sql_compatibility 參數(shù)設(shè)置為 C。

查詢操作

• 【建議】除 ETL 程序外，應(yīng)該盡量避免向客戶端返回大量結(jié)果集的操作。如果結(jié)果集過(guò)大，應(yīng)考慮業(yè)務(wù)設(shè)計(jì)是否合理。
• 【建議】使用事務(wù)方式執(zhí)行 DDL 和 DML 操作。例如，truncate table、update table、delete table、drop table 等操作，一旦執(zhí)行提交就無(wú)法恢復(fù)。對(duì)于這類操作，建議使用事務(wù)進(jìn)行封裝，必要時(shí)可以進(jìn)行回滾。
• 【建議】在查詢編寫時(shí)，建議明確列出查詢涉及的所有字段，不建議使用 “SELECT *” 這種寫法。一方面基于性能考慮，盡量減少查詢輸出列；另一方面避免增刪字段對(duì)前端業(yè)務(wù)兼容性的影響。
• 【建議】在訪問(wèn)表對(duì)象時(shí)帶上 schema 前綴，可以避免因 schema 切換導(dǎo)致訪問(wèn)到非預(yù)期的表。
• 【建議】超過(guò) 3 張表或視圖進(jìn)行關(guān)聯(lián)（特別是 FULL JOIN）時(shí)，執(zhí)行代價(jià)難以估算。建議使用 WITH TABLE AS 語(yǔ)句創(chuàng)建中間臨時(shí)表的方式增加 SQL 語(yǔ)句的可讀性。
• 【建議】盡量避免使用笛卡爾積和 FULL JOIN。這些操作會(huì)造成結(jié)果集的急劇膨脹，同時(shí)其執(zhí)行性能也很低。
• 【關(guān)注】NULL 值的比較只能使用 IS NULL 或者 IS NOT NULL 的方式判斷，其他任何形式的邏輯判斷都返回 NULL。例如：NULLNULL、NULL=NULL 和 NULL1 返回結(jié)果都是 NULL，而不是期望的布爾值。
• 【關(guān)注】需要統(tǒng)計(jì)表中所有記錄數(shù)時(shí)，不要使用 count (col) 來(lái)替代 count (*)。count (*) 會(huì)統(tǒng)計(jì) NULL 值（真實(shí)行數(shù)），而 count (col) 不會(huì)統(tǒng)計(jì)。
• 【關(guān)注】在執(zhí)行 count (col) 時(shí)，將 “值為 NULL” 的記錄行計(jì)數(shù)為 0。在執(zhí)行 sum (col) 時(shí)，當(dāng)所有記錄都為 NULL 時(shí)，最終將返回 NULL；當(dāng)不全為 NULL 時(shí)，“值為 NULL” 的記錄行將被計(jì)數(shù)為 0。
• 【關(guān)注】count (多個(gè)字段) 時(shí)，多個(gè)字段名必須用圓括號(hào)括起來(lái)。例如，count ( (col1,col2,col3) )。注意：通過(guò)多字段統(tǒng)計(jì)行數(shù)時(shí)，即使所選字段都為 NULL，該行也被計(jì)數(shù)，效果與 count (*) 一致。
• 【關(guān)注】count (distinct col) 用來(lái)計(jì)算該列不重復(fù)的非 NULL 的數(shù)量，NULL 將不被計(jì)數(shù)。
• 【關(guān)注】count (distinct (col1,col2,…)) 用來(lái)統(tǒng)計(jì)多列的唯一值數(shù)量，當(dāng)所有統(tǒng)計(jì)字段都為 NULL 時(shí)，也會(huì)被計(jì)數(shù)，同時(shí)這些記錄被認(rèn)為是相同的。
• 【建議】使用連接操作符 “||” 替換 concat 函數(shù)進(jìn)行字符串連接。因?yàn)?concat 函數(shù)生成的執(zhí)行計(jì)劃不能下推，導(dǎo)致查詢性能嚴(yán)重劣化。
• 【建議】使用下面時(shí)間相關(guān)的宏替換 now 函數(shù)來(lái)獲取當(dāng)前時(shí)間。因?yàn)?now 函數(shù)生成的執(zhí)行計(jì)劃無(wú)法下推，導(dǎo)致查詢性能嚴(yán)重劣化。
• 表 1 時(shí)間相關(guān)的宏

• 復(fù)制代碼openGauss=# select CURRENT_TIMESTAMP(6); timestamptz -—————————— 2018-02-02 00:39:55.231689+08 (1 row)
• 【建議】盡量避免標(biāo)量子查詢語(yǔ)句的出現(xiàn)。標(biāo)量子查詢是出現(xiàn)在 select 語(yǔ)句輸出列表中的子查詢，在下面例子中，下劃線部分即為一個(gè)標(biāo)量子查詢語(yǔ)句：

復(fù)制代碼SELECT id, (SELECT COUNT(*) FROM films f WHERE f.did = s.id) FROM staffs_p1 s;

• 標(biāo)量子查詢往往會(huì)導(dǎo)致查詢性能急劇劣化，在應(yīng)用開(kāi)發(fā)過(guò)程中，應(yīng)當(dāng)根據(jù)業(yè)務(wù)邏輯，對(duì)標(biāo)量子查詢進(jìn)行等價(jià)轉(zhuǎn)換，將其寫為表關(guān)聯(lián)。
• 【建議】在 where 子句中，應(yīng)當(dāng)對(duì)過(guò)濾條件進(jìn)行排序，把選擇讀較?。êY選出的記錄數(shù)較少）的條件排在前面。
• 【建議】where 子句中的過(guò)濾條件，盡量符合單邊規(guī)則。即把字段名放在比較條件的一邊，優(yōu)化器在某些場(chǎng)景下會(huì)自動(dòng)進(jìn)行剪枝優(yōu)化。形如 col op expression，其中 col 為表的一個(gè)列，op 為‘=’、‘>’的等比較操作符，expression 為不含列名的表達(dá)式。例如，

復(fù)制代碼SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHERE current_timestamp(6) – time < '1 days'::interval;

• 改寫為：

復(fù)制代碼SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time > current_timestamp(6) – ‘1 days’::interval;

• 【建議】盡量避免不必要的排序操作。排序需要耗費(fèi)大量的內(nèi)存及 CPU，如果業(yè)務(wù)邏輯許可，可以組合使用 ORDER BY 和 LIMIT，減小資源開(kāi)銷。openGauss 默認(rèn)按照 ASC & NULL LAST 進(jìn)行排序。
• 【建議】使用 ORDER BY 子句進(jìn)行排序時(shí)，顯式指定排序方式（ASC/DESC），NULL 的排序方式（NULL FIRST/NULL LAST）。
• 【建議】不要單獨(dú)依賴 limit 子句返回特定順序的結(jié)果集。如果部分特定結(jié)果集，可以將 ORDER BY 子句與 Limit 子句組合使用，必要時(shí)也可以使用 OFFSET 跳過(guò)特定結(jié)果。
• 【建議】在保障業(yè)務(wù)邏輯準(zhǔn)確的情況下，建議盡量使用 UNION ALL 來(lái)代替 UNION。
• 【建議】如果過(guò)濾條件只有 OR 表達(dá)式，可以將 OR 表達(dá)式轉(zhuǎn)化為 UNION ALL 以提升性能。使用 OR 的 SQL 語(yǔ)句經(jīng)常無(wú)法優(yōu)化，導(dǎo)致執(zhí)行速度慢。例如，將下面語(yǔ)句

復(fù)制代碼SELECT * FROM scdc.pub_menu WHERE (cdp= 300 AND inline=301) OR (cdp= 301 AND inline=302) OR (cdp= 302 ANDinline=301);

• 轉(zhuǎn)換為：

復(fù)制代碼SELECT * FROM scdc.pub_menu WHERE (cdp= 300 AND inline=301) union allSELECT * FROM scdc.pub_menu WHERE (cdp= 301 AND inline=302) union all SELECT * FROM tablename WHERE (cdp= 302 AND inline=301)

• 【建議】當(dāng) in (val1, val2, val3…) 表達(dá)式中字段較多時(shí)，建議使用 in (values (val1), (val2),(val3)…) 語(yǔ)句進(jìn)行替換。優(yōu)化器會(huì)自動(dòng)把 in 約束轉(zhuǎn)換為非關(guān)聯(lián)子查詢，從而提升查詢性能。
• 【建議】在關(guān)聯(lián)字段不存在 NULL 值的情況下，使用 (not) exist 代替 (not) in。例如，在下面查詢語(yǔ)句中，當(dāng) T1.C1 列不存在 NULL 值時(shí)，可以先為 T1.C1 字段添加 NOT NULL 約束，再進(jìn)行如下改寫。

復(fù)制代碼SELECT * FROM T1 WHERE T1.C1 NOT IN (SELECT T2.C2 FROM T2);

可以改寫為：

復(fù)制代碼 SELECT * FROM T1 WHERE NOT EXISTS (SELECT * FROM T1,T2 WHERE T1.C1=T2.C2);

說(shuō)明：

如果不能保證 T1.C1 列的值為 NOT NULL 的情況下，就不能進(jìn)行上述改寫。

如果 T1.C1 為子查詢的輸出，要根據(jù)業(yè)務(wù)邏輯確認(rèn)其輸出是否為 NOT NULL。

• 【建議】通過(guò)游標(biāo)進(jìn)行翻頁(yè)查詢，而不是使用 LIMIT OFFSET 語(yǔ)法，避免多次執(zhí)行帶來(lái)的資源開(kāi)銷。游標(biāo)必須在事務(wù)中使用，執(zhí)行完后務(wù)必關(guān)閉游標(biāo)并提交事務(wù)。

來(lái)源：https://my.oschina.net/gaussdb/blog/5486884

“做程序員，圈子和學(xué)習(xí)最重要”因?yàn)橛杏辛巳ψ涌梢宰屇闵僮邚澛?，擴(kuò)寬人脈，擴(kuò)展思路，學(xué)習(xí)他人的一些經(jīng)驗(yàn)及學(xué)習(xí)方法！同時(shí)在這分享一下是一直以來(lái)整理的Java后端進(jìn)階筆記文檔和學(xué)習(xí)資料免費(fèi)分享給大家！需要資料的朋友私信我扣【06】