宜信的105條資料庫軍規

宜信技術學院發表於2019-11-05

作為一家金融科技企業,宜信的大量業務都依賴於資料庫。如何提高公司整體資料庫應用水平,是對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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章