Postgresql 31條資料庫開發規範

為了加強各系統軟體開發標準規範，進一步提高各系統的穩定性，因此對現有的資料庫開發規範進行了梳理，制定31條資料庫開發管理規範.此規範主要是指導開發人員在進行開發時編寫高效的SQL語句和以合適的方式建立表和索引，已達到系統在不斷更新和升級時仍能保持良好的穩定性。

1 大批次插入資料

如果同時執行大量的插入，建議使用多個值的INSERT語句(方法二)。這比使用分開INSERT語句快（方法一），一般情況下批次插入效率有幾倍的差別。

方法一：

insert into tablename values(1,2);

insert into tablename values(1,3);

insert into tablename values(1,4);

方法二：

Insert into tablename values(1,2),(1,3),(1,4);

選擇後一種方法的原因有二。

1.減少SQL語句解析的操作， polardb沒有類似Oracle的share pool，採用方法二，只需要解析一次就能進行資料的插入操作；

2.SQL語句較短，可以減少網路傳輸的IO。

2 避免出現select *

         select * 操作在任何型別資料庫中都不是一個好的SQL開發習慣。

         使用select * 取出全部列，會讓最佳化器無法完成索引覆蓋掃描這類最佳化，會影響最佳化器對執行計劃的選擇，也會增加網路頻寬消耗，更會帶來額外的I/O,記憶體和CPU消耗。

         建議評估業務實際需要的列數，指定列名以取代select *

         規範：

             Select col1,col2,col3… from t1;

         不規範：

    Select * from t1;

3 避免出現count(*)

使用 SELECT 1 FROM tbl WHERE xxx LIMIT 1 判斷是否存滿足條件的列，要比Count快。

可以使用 select exists(select * FROM app.sjqq where xxx limit 1) 將存在性結果轉換為布林值。

4 最佳化group by語句

預設情況下，Polardb排序所有 “GROUP BY col1，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1，col2，...;” 如果顯式包括一個包含相同的列的 ORDER BY子句，Polardb可以毫不減速地對它進行最佳化，儘管仍然進行排序。

如果查詢包括 GROUP BY 但你想要避免排序結果的消耗，你可以指定 ORDER BY NULL禁止排序。

例如：

SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY NULL;

5 最佳化order by語句

在某些情況中，Polardb 可以使用一個索引來滿足 ORDER BY 子句，而不需要額外的排序。where 條件和 order by 使用相同的索引，並且 order by 的順序和索引順序相同 ，並且 order by 的欄位都是升序或者都是降序。

例如：下列 SQL 可以使用索引。

SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以上覆合索引包含欄位key_part1，key_part2...

 

但是以下情況不使用索引：

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

以上由於order by 的欄位混合 ASC和 DESC

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1；

以上用於查詢行的關鍵字與 ORDER BY 中所使用的不相同

SELECT col1 FROM t1 ORDER BY key1, key2；

對不同的索引關鍵字使用 ORDER BY：

6 最佳化join語句

Polardb中可以透過子查詢來使用 SELECT 語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的 SQL 操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連線(JOIN)..替代。

例子：

假設要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：

     SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID

     FROM salesinfo )

如果使用連線(JOIN).. 來完成這個查詢工作，速度將會有所提升。尤其是當 salesinfo表中對 CustomerID 建有索引的話，效能將會更好，查詢如下：

     SELECT col1 FROM customerinfo

     LEFT JOIN salesinfoON ON customerinfo.CustomerID=salesinfo.CustomerID

     WHERE salesinfo.CustomerID IS NULL

7 最佳化or條件

對於 or 子句，如果要利用索引，則or 之間的每個條件列都必須用到索引；如果沒有索引，則應該考慮增加索引。

8 最佳化union查詢

Posgresql透過建立並填充臨時表的方式來執行union查詢。除非確實要消除重複的行，否則建議使用union all。原因在於如果沒有all這個關鍵詞，Polardb會給臨時表加上distinct選項，這會導致對整個臨時表的資料做唯一性校驗，這樣做的消耗相當高。

高效：

       SELECT COL1, COL2, COL3

        FROM TABLE

        WHERE COL1 = 10

UNION ALL

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效

       SELECT COL1, COL2, COL3

        FROM TABLE

        WHERE COL1 = 10

UNION

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

9 最佳化IN運算子

        ·          使用 EXISTS 子句代替 IN 運算子，效果更佳。

        ·          使用 =ANY(ARRAY[1,2,3,4]) 代替 IN (1,2,3,4) ，效果更佳。

10   不建議使用左模糊搜尋

        ·    左模糊搜尋WHERE col LIKE '%xxx'無法充分利用B樹索引，如有需要，可用reverse表示式函式索引。

11   使用truncate代替delete

當刪除全表中記錄時，使用delete語句的操作會被記錄到undo塊中，刪除記錄也記錄pg_wal日誌，當確認需要刪除全表時，會產生很大量的pg_wa並佔用大量的undo資料塊，此時既沒有很好的效率也佔用了大量的資源。使用truncate替代，不會記錄可恢復的資訊，資料不能被恢復。也因此使用truncate操作有其極少的資源佔用與極快的時間。另外，使用truncate可以回收表的水位。

12   使用合理的分頁方式以提高分頁效率

使用合理的分頁方式以提高分頁效率

針對展現等分頁需求，合適的分頁方式能夠提高分頁的效率

案例1：

     select * from t

     where thread_id = 10000

     and deleted = 0

     order by gmt_create asc limit 0, 15;

上述例子透過一次性根據過濾條件取出所有欄位進行排序返回。資料訪問開銷=索引IO+索引全部記錄結果對應的表資料IO。因此，該種寫法越翻到後面執行效率越差，時間越長，尤其表資料量很大的時候。

適用場景：當中間結果集很小（10000行以下）或者查詢條件複雜（指涉及多個不同查詢欄位或者多表連線）時適用。

 

案例2：

     select t.* from (

     select id from t

     where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t

     where a.id = t.id;

上述例子必須滿足t表主鍵是id列，且有覆蓋索引secondary key:(thread_id, deleted, gmt_create)。透過先根據過濾條件利用覆蓋索引取出主鍵id進行排序，再進行join操作取出其他欄位。資料訪問開銷=索引IO+索引分頁後結果（例子中是15行）對應的表資料IO。因此，該寫法每次翻頁消耗的資源和時間都基本相同，就像翻第一頁一樣。

適用場景：當查詢和排序欄位（即where子句和order by子句涉及的欄位）有對應覆蓋索引時，且中間結果集很大的情況時適用。

13   避免出現不確定結果的函式

  特定針對主從複製這類業務場景。由於原理上從庫複製的是主庫執行的語句，使用如now()、rand()、sysdate()、current_user()等不確定結果的函式很容易導致主庫與從庫相應的資料不一致。另外不確定值的函式,產生的SQL語句無法利用QUERY CACHE。

14 線上查詢必須有配套索引

        · 所有線上查詢必須針對其訪問模式設計相應索引，除極個別小表外不允許全表掃描。

        · 索引有代價，不允許建立不使用的索引。

15 禁止在大欄位上建立索引

        · 被索引欄位大小無法超過2KB（1/3的頁容量），原則上禁止超過64個字元。

        ·  如有大欄位索引需求，可以考慮對大欄位取雜湊，並建立函式索引。或使用其他型別的索引（GIN）

16 明確空值排序規則

        ·    如在可空列上有排序需求，需要在查詢與索引中明確指定 NULLS FIRST 還是 NULLS LAST 。

        ·   注意， DESC 排序的預設規則是 NULLS FIRST ，即空值會出現在排序的最前面，通常這不是期望行為。

        ·   索引的排序條件必須與查詢匹配，如： create index on tbl (id desc nulls last);

17 利用GiST索引應對近鄰查詢問題

        ·   傳統B樹索引無法提供對KNN問題的良好支援，應當使用GiST索引。

18 利用函式索引

         · 任何可以由同一行其他欄位推斷得出的冗餘欄位，可以使用函式索引替代。

         · 對於經常使用表示式作為查詢條件的語句，可以使用表示式或函式索引加速查詢。    

         ·   典型場景：建立大欄位上的雜湊函式索引，為需要左模糊查詢的文字列建立reverse函式索引。

19 字元編碼必須為UTF8

        ·   禁止使用其他任何字元編碼。

20 容量規劃

        ·   單表記錄過億，或超過10GB的量級，可以考慮開始進行分表。

        ·   單表容量超過1T，單庫容量超過2T。需要考慮分片。

21 不要濫用儲存過程

         ·   儲存過程適用於封裝事務，減少併發衝突，減少網路往返，減少返回資料量，執行少量自定義邏輯。

         · 儲存過程不適合進行復雜計算，不適合進行平凡/頻繁的型別轉換與包裝。

22 主鍵與身份列

         · 每個表都必須有身份列，原則上必須有主鍵，最低要求為擁有非空唯一約束。

         · 身份列用於唯一標識表中的任一元組，邏輯複製與諸多三方工具有賴於此。

23 外來鍵

      ·   不建議使用外來鍵，建議在應用層解決。使用外來鍵時，引用必須設定相應的動作： SET NULL , SET DEFAULT , CASCADE ，慎用級聯操作。

24 慎用寬表

         ·   欄位數目超過15個的表視作寬表，寬表應當考慮進行縱向拆分，透過相同的主鍵與主表相互引用。

         ·    因為MVCC機制，寬表的寫放大現象比較明顯，儘量減少對寬表的頻繁更新

25 配置合適的預設值

         · 有預設值的列必須新增 DEFAULT子句指定預設值。

         · 可以在預設值中使用函式，動態生成預設值（例如主鍵發號器）。

26 合理應對空值

        ·   欄位語義上沒有零值與空值區分的，不允許空值存在，須為列配置NOT NULL約束。

27 唯一約束透過資料庫強制

         · 唯一約束須由資料庫保證，任何唯一列須有唯一約束。

         · EXCLUDE 約束是泛化的唯一約束，可以在低頻更新場景下用於保證資料完整性。

28  主鍵型別

         ·   主鍵通常使用整型，建議使用BIGINT，允許使用不超過64位元組的字串。

         ·   主鍵允許使用Serial自動生成，建議使用Default next_id()發號器函式。

29   選擇合適的型別

         · 能使用專有型別的，不使用字串。（數值，列舉，網路地址，貨幣，JSON，UUID等）

         · 使用正確的資料型別，能顯著提高資料儲存，查詢，索引，計算的效率，並提高可維護性。

30   使用列舉型別

         · 較穩定的，取值空間較小（十幾個內）的欄位應當使用列舉型別，不要使用整型與字串表示。

         · 使用列舉型別有效能、儲存、可維護性上的優勢。

31   選擇合適的文字型別

         ·    PG的文字型別包括 char , char(n) , varchar ,varchar(n) , text 。除 char(n) 外並無本質儲存區別。

         ·    帶有 (n) 修飾符的型別會檢查字串長度，會導致微小的額外開銷，對字串長度有限制時應當使用 varchar(n) ，避免插入過長的髒資料。

         · 避免使用 char(n) ，為了與SQL標準相容，該型別存在不合直覺的行為表現，且並沒有儲存和效能優勢。