宜信的105條資料庫軍規

作為一家金融科技企業，宜信的大量業務都依賴於資料庫。如何提高公司整體資料庫應用水平，是對DBA的一大挑戰，也非常具有現實意義。筆者在宜信的多年工作中，與團隊一起總結整理了針對傳統關係型資料庫的使用規則，並藉助自研的資料庫稽核平臺落地，藉此幫助研發團隊評估資料庫開發質量，達到儘早發現問題、解決問題之目的。下圖正是這一系統的簡單原理圖。

如上圖所示，針對規則部分又可細分為如下分類（部分）。簡單描述如下，後面將逐一詳細說明。

一、Oracle規則（物件）

1.1 表、分割槽

【規則1】

規則說明：超過指定規模且沒有分割槽的表。

規則閾值：2GB（物理大小超過指定閥值）。

規則描述：表的規模過大，將影響表的訪問效率、增加維護成本等。常見的解決方案就是使用分割槽表，將大表轉換為分割槽表。

【規則2】

規則說明：單表或單分割槽記錄數量過大。

規則閾值：1000000（單表或單分割槽記錄數超過指定閥值）。

規則描述：控制單個表或單個分割槽的資料規模，提高單一物件的訪問效率。如記錄數過多，應考慮分庫、分表、分割槽等策略。

【規則3】

規則說明：大表過多。

規則閾值：自定義（超過2G的表的數量過多）。

規則描述：大表在所有表中所佔比例超過20%(OLTP)或95%(OLAP)。

【規則4】

規則說明：單表分割槽數量過多。

規則閾值：500（單表分割槽數量超過指定閥值）。

規則描述：分割槽表中分割槽數量過多，將導致整體維護成本過高，可調整分割槽粒度。

【規則5】

規則說明：分割槽表數量過多。

規則閾值：2000（分割槽表數量超過指定閥值）。

規則描述：分割槽表過多，常見原因是大表較多。因根據需求，考慮進行垂直拆分，減小單庫規模。

【規則6】

規則說明：複合分割槽數量過多。

規則閾值：5（複合分割槽數量超過指定閥值）。

規則描述：同上面分割槽表數量過多理由類似（含有複合分割槽表的數量）。

【規則7】

規則說明：存在啟用並行屬性的表。

規則閾值：1（表degree屬性不為1）。

規則描述：一般情況下不建議對錶設定並行屬性。

1.2 索引

【規則8】

規則說明：外來鍵沒有索引的表。

規則描述：外來鍵沒有索引會導致主子表關聯查詢時，關聯效率很低。

【規則9】

規則說明：組合索引數量過多或沒有索引。

規則描述：組合索引過多，將導致空間消耗較大、索引維護成本較高。應考慮構建戰略性索引結構，不要針對每個需求都通過建立索引解決。

【規則10】

規則說明：單表索引數量過多。

規則閾值：3（單表索引數量超過指定閥值）。

規則描述：索引可以提高訪問速度，但數量過多將導致空間消耗過大，且索引維護成本較高，影響DML效率等問題。應控制索引數量。

【規則11】

規則說明：存在7天內沒有使用的索引。

規則描述：在資料庫一段時間內，該索引沒有被任何SQL語句使用。請評估此索引的有效性。

【規則12】

規則說明：欄位重複索引。 規則描述：一個欄位被多個索引引用，請考慮構建策略，刪除不必要的索引。

【規則13】

規則說明：存在全域性分割槽索引。

規則描述：全域性分割槽索引，存在維護成本較高問題。當分割槽發生變化時，需要維護全域性索引的有效性。

【規則14】

規則說明：失效索引。 規則描述：索引狀態為INVALID、UNUSABLE。

【規則15】

規則說明：索引高度超過指定高度。

規則閾值：4。

規則描述：索引高度過高導致增加IO成本。

【規則16】

規則說明：存在點陣圖索引。

規則描述：OLTP環境中不建議使用點陣圖索引，如果表物件經查做DML操作，會在一定程度上阻塞相關操作。

【規則17】

規則說明：存在函式索引。

【規則18】

規則說明：存在啟用並行屬性的索引。

規則閾值：1（索引degree屬性不為1）。

規則描述：一般情況下不建議對索引設定並行屬性。

【規則19】

規則說明：存在聚簇因子過大的索引。

規則閾值：自定義。

規則描述：聚簇因子過大的索引，應該考慮優化。

1.3 約束

【規則20】

規則類別：約束。

規則說明：沒有主鍵的表。

規則描述：主鍵是關係型資料庫中唯一確定一條記錄的依據，沒有任何理由不定義主鍵。

【規則21】

規則類別：約束。

規則說明：使用外來鍵的表。

規則描述：不建議使用外來鍵約束，資料一致性通過應用端解決。

1.4 欄位

【規則22】

規則說明：表欄位過多。

規則閾值：100（欄位數量超過指定閥值）。

規則描述：欄位過多，會導致記錄長度過大。單個資料儲存單元將儲存的記錄數過少，影響訪問效率。

【規則23】

規則說明：包含有大欄位型別的表。

規則描述：大物件欄位是關係型資料庫中應儘量避免的。如有需要，可考慮在外部進行儲存。

【規則24】

規則說明：記錄長度定義過長。

規則描述：記錄定義長度與實際儲存長度差異過大，請考慮欄位型別定義是否合理，個別欄位過長是否可分表儲存。

【規則25】 規則說明：不包含時間戳欄位的表。

規則描述：時間戳，是獲取增量資料的一種方法。建議在表內增加建立時間、更新時間的時間戳欄位。命名方式為CREATE_TIME、UPDATE_TIME。

【規則26】

規則說明：表欄位型別不匹配。

規則描述：此規則會抽樣部分資料，分析其定義型別與儲存型別是否相符。常見問題如用數字、文字儲存日期等。

1.5 其他物件

【規則27】

規則說明：快取過小的序列。

規則閾值：100（序列cache值小於指定閥值）。

規則描述：系統預設會快取20，如過小將導致頻繁查詢資料字典，影響併發能力。

【規則28】

規則說明：存在儲存過程及函式度。

規則閾值：20（儲存過程和函式的數量超過指定閥值）。

規則描述：儲存過程及函式，將影響資料庫的異構遷移能力，並存在程式碼維護性較差等原因。

【規則29】

規則說明：存在觸發器。

規則閾值：20（觸發器數量超過指定閥值）。

規則描述：觸發器，將影響資料庫的異構遷移能力。如有資料一致性維護需求，請從應用端給予考慮。

【規則30】

規則說明：存在DBLINK。

規則描述：不建議在一個資料庫中訪問其他資料庫，請考慮在應用端解決。

二、Oracle規則（執行計劃）

2.1 繫結變數

【規則31】

規則說明：未使用繫結變數。

規則閾值：自定義（執行次數）。

規則描述：執行次數超過一定閥值的語句，謂詞右側存在常量值。

【規則32】

規則說明：繫結變數的數量過多。

規則閾值：自定義（繫結變數的個數）。

規則描述：繫結變數數量過多會增加變數替換時間，在一定程度上增加sql執行時間。

2.2 表間關聯

【規則33】

規則說明：笛卡爾積。

規則描述：缺少連線條件，導致表間關聯使用了笛卡爾積的連線方式，執行計劃中包含"CARTESIAN|"字樣。

【規則34】

規則說明：巢狀迴圈層次過深。

規則閾值：自定義（層次數）。

規則描述：巢狀迴圈層次過深，超過指定閥值。執行計劃中巢狀多層"NESTED LOOP"或"FILTER"字樣。

【規則35】 規則說明：巢狀迴圈內層表訪問方式為全表掃描。

規則描述：巢狀迴圈的內層表訪問方式為全表掃描，效率很低。

【規則36】 規則說明：排序合併連線中存在大結果集排序。

規則描述：排序合併中兩個結果集都要排序，應調整為其他連線方式。

【規則37】

規則說明：多表關聯。

規則閾值：自定義（表個數）。

規則描述：過多的表關聯，影響效能。

2.3 訪問路徑

【規則38】

規則說明：大表全表掃描。

規則閾值：自定義（表大小，單位GB）。

規則描述：對大表執行了全表掃描操作，執行計劃中包含"TABLE ACCESS FULL"字樣。

【規則39】

規則說明：大索引全掃描。

規則閾值：自定義（索引大小，單位GB）。

規則描述：對大索引執行了索引全掃描操作，執行計劃中包含"INDEX FULL SCAN"字樣。

【規則40】

規則說明：大索引快速全掃描。

規則閾值：自定義（索引大小，單位GB）。

規則描述：對大索引執行了索引快速全掃描操作，執行計劃中包含"INDEX FAST FULL SCAN"字樣。

【規則41】

規則說明：索引跳躍掃描。

規則描述：對索引執行跳躍掃描操作，執行計劃中包含"INDEX SKIP SCAN"字樣。

【規則42】

規則說明：分割槽全掃描。

規則描述：對分割槽表進行了全分割槽掃描，執行計劃中含有“PARTITION RANGE ALL”字樣。

【規則43】

規則說明：非連續分割槽掃描。

規則描述：非連續分割槽掃描，執行計劃中含有“PARTITION RANGE INLIST”或“PARTITION RANGE OR”字樣。

【規則44】

規則說明：跨分割槽掃描。

規則描述：連續的分割槽掃描，執行計劃中含有“PARTITION RANGE ITERATOR”字樣。

2.4 型別轉換

【規則45】

規則說明：存在隱式轉換。

規則描述：在條件判斷中使用了隱式資料型別轉換。

2.5 其他執行計劃

【規則46】

規則說明：存在大結果集排序操作。

規則描述：可考慮通過引入索引等操作避免排序。

【規則47】

規則說明：存在並行訪問特徵。

規則描述：並行很影響效能，一般情況下需要避免。

【規則48】

規則說明：存在檢視訪問。 規則描述：檢視操作一般可以合併、解巢狀等，如都不行應該排查檢視定義。

三、Oracle規則（執行特徵）

3.1 執行特徵

【規則49】

規則說明：掃描塊數與返回記錄數比例過低。

規則閾值：自定義（百分比）。

規則描述：掃描大量資料但返回記錄數很少，需要從邏輯上調整SQL語句。

【規則50】

規則說明：子游標過多。

規則閾值：自定義（子游標數）。

規則描述：子游標過多，可能存在執行計劃不穩定的情況。

【規則51】

規則說明：elapsed_time。

規則閾值：自定義。

【規則52】

規則說明：cpu_time

規則閾值：自定義

【規則53】

規則說明：buffer_gets

規則閾值：自定義

【規則54】

規則說明：disk_reads

規則閾值：自定義

【規則55】

規則說明：direct_writes

規則閾值：自定義

【規則56】

規則說明：executions

規則閾值：自定義

四、MySQL規則（物件）

4.1 表、分割槽

【規則57】

規則說明：超過指定規模且沒有分割槽的表。

規則閾值：自定義（表大小，GB）。

規則描述：表的規模過大，將影響表的訪問效率、增加維護成本等。常見的解決方案就是使用分割槽表，將大表轉換為分割槽表。

【規則58】

規則說明：單庫資料表過多。

規則閾值：自定義（表個數）。

規則描述：單庫資料表過多，將影響整體效能。必要時，進行業務邏輯的垂直拆分。

【規則59】

規則說明：單表(分割槽)資料量過大。

規則閾值：自定義（資料規模，記錄數）。

規則描述：單表(分割槽)資料表過多，將影響整體效能。必要時，進行分庫、分表或定期清理、歸檔資料。

4.2 索引

【規則60】

規則說明：單表索引數量過多。

規則閾值：自定義（索引數量）。

規則描述：單表索引數量過多，不僅維護成本高，而且佔用更多的空間。

【規則61】

規則說明：存在重複索引。

規則描述：索引能由另一個包含該字首的索引完全代替，是多餘索引。多餘的索引會浪費儲存空間，並影響資料更新效能。

【規則62】

規則說明：索引選擇率不高。

規則閾值：自定義（選擇率，百分比）。

規則描述：索引選擇率不高，將導致索引低效，請調整索引欄位。

4.3 約束

【規則63】

規則說明：表存在外來鍵。

規則描述：外來鍵資源將消耗資料庫的計算能力，建議通過應用層保證資料約束。

【規則64】

規則說明：表沒有定義主鍵。

規則描述：沒有定義主鍵，MySQL會自動建立主鍵。這不是一種好的設計方法。

4.4 欄位

【規則65】

規則說明：存在大物件欄位。

規則描述：大物件欄位將影響存取效能、耗費較多空間，建議在資料庫之外儲存。

【規則66】

規則說明：單表欄位數過多。

規則閾值：自定義（欄位數）。

規則描述：表欄位數過多，將造成記錄過長，單頁儲存記錄數減少。可考慮拆表處理。

【規則67】

規則說明：單表欄位定義長度過長。

規則閾值：自定義（欄位長度，單位位元組）。

規則描述：應控制單表定義長度，避免過長記錄。

【規則68】

規則說明：單表主鍵欄位定義長度過長。

規則閾值：自定義（欄位長度，單位位元組）。

規則描述：應控制主鍵欄位長度，過長的主鍵欄位會造成索引空間消耗過大。

【規則69】

規則說明：表沒有定義時間戳欄位。

規則描述：時間戳欄位是獲取增量資料的最佳方法，請為表定義時間戳欄位。

【規則70】

規則說明：欄位資料型別定義錯誤。

規則閾值：自定義（記錄數）。

規則描述：根據欄位儲存內容判斷，欄位型別定義異常，建議選擇適合的資料型別。

4.5 其他物件

【規則71】

規則說明：單表存在函式、儲存過程、觸發器。

規則描述：儲存過程、函式、觸發器等都將消耗資料庫的計算能力，建議通過應用層保證資料約束。

五、MySQL規則（執行計劃）

5.1 訪問路徑

【規則72】

規則說明：大表全表掃描。

規則閾值：自定義（表大小，單位GB）。

規則描述：對大表執行了全表掃描操作。

5.2 SELECT_TYPE

【規則73】

規則說明：DEPENDENT UNION

【規則74】

規則說明：SUBQUERY

【規則75】

規則說明：DEPENDENT SUBQUERY

【規則76】

規則說明：MATERIALIZED

【規則77】

規則說明：UNCACHEABLE SUBQUERY

【規則78】

規則說明：UNCACHEABLE UNION

5.3 ACCESS_TYPE

【規則79】

規則說明：fulltext

【規則80】

規則說明：index_merge

【規則81】

規則說明：unique_subquery

【規則82】

規則說明：all

【規則83】

規則說明：index range

5.4 其他執行計劃

【規則84】

規則說明：使用臨時表。

規則描述：執行過程中使用了臨時表，執行計劃中包括"using temporary"。

【規則85】 規則說明：使用磁碟排序。

規則描述：執行計劃中使用了磁碟排序，執行計劃中包含"using filesort"字樣。

六、MySQL規則（執行特徵）

6.1 執行特徵

【規則86】

規則說明：index_ratio

【規則87】

規則說明：lock_time_sum

七、Oracle+MySQL（語句級）

7.1 查詢類

【規則88】

規則說明：select *

規則描述：禁止使用select *，必須明確選擇所需的列。

【規則89】

規則說明：重複查詢子句。

規則描述：禁止使用重複的查詢子句，應使用with as替換子句(僅限Oracle)來提升SQL執行效率。

【規則90】

規則說明：查詢欄位引用函式。

規則描述：禁止在查詢欄位中引用函式(型別轉換函式、函式索引情況可忽略)。

【規則91】

規則說明：巢狀select子句。

規則描述：禁止出現select子句的巢狀子查詢，避免出現效能問題。

【規則92】

規則說明：出現union。

規則描述：防止出現不必要的排序動作。

【規則93】

規則說明：多個過濾條件通過or連線。

規則描述：防止優化器出現選擇異常。

【規則94】

規則說明：謂詞條件使用like '%xxx'

規則描述：無法使用索引。

【規則95】 規則說明：謂詞中存在負向操作符。

規則描述：!=,<>,!<,!>,not exists,not。

【規則96】

規則說明：存在子查詢情況。

規則描述：這個要區分位置(select、from、where、having等部分)。

【規則97】

規則說明：存在三個以上的表關聯。

【規則98】

規則說明：存在全連線或外連線。

規則描述：cross join或outer join情況。

7.2 變更類

【規則99】

規則說明：update中出現order by子句。

規則描述：防止更新過程中出現不必要的排序。

【規則100】

規則說明：update中必須出現where子句。

規則描述：防止出現意外的全部更新動作。

【規則101】 規則說明：更新主鍵。

規則描述：禁止出現更新主鍵的情況。

【規則102】

規則說明：delete中出現order by子句。

規則描述：防止刪除過程出現不必要的排序。

【規則103】

規則說明：delete中必須出現where子句。

規則描述：防止出現意外的全部刪除動作。

【規則104】

規則說明：新增SQL文字過長規則。

【規則105】

規則說明：新增IN List元素過多。

作者：韓鋒

首發於作者個人公號《韓鋒頻道》。

來源：宜信技術學院

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69918724/viewspace-2662677/，如需轉載，請註明出處，否則將追究法律責任。