關系型數據庫邏輯設計規范研究

陳英達， 黃巨濤， 林強， 唐亮亮

(廣東電網有限責任公司信息中心，廣東 廣州 510080)

0 引言

在信息系統開發中普遍采用Oracle、MySQL等關系型數據庫系統，而數據庫的邏輯設計好壞影響著數據庫及其應用系統的整體性能，決定了數據的完整性、準確性和一致性能否得到保證。如果數據庫邏輯設計不合理，那么數據庫的調優對于數據庫的性能提升將十分有限。因此我們對數據庫的邏輯設計規范開展研究，通過對數據庫邏輯設計提出相應的要求與建議。遵照本文所制定的數據庫邏輯設計規范可以減少數據庫中數據冗余、提高數據庫存儲效率[1]。對數據定義恰當的約束條件，并能提升SQL語句執行效率，對信息系統的長期穩定運行具有重要的價值。

1 數據完整性設計規范

數據完整性需要使用不同的完整性約束條件來制約，數據設計的完整性約束主要包含以下四方面：域的完整性、實體完整性、參照完整性以及用戶定義完整性，其含義分別如下。

(1) 域的完整性：數據庫表中的字段必須滿足某種特定的數據類型或約束。約束包括取值范圍、精度等規定；

(2) 實體完整性：表中行主鍵的約束；

(3) 參照完整性：屬于表間規則，用于設計表間數據的完整性；

(4) 用戶定義完整性：對數據表中字段屬性的約束。

數據完整性設計規范有如下要求。

(1) 每張表必須定義主鍵；

(2) 某一字段如果為另一表的主鍵，則該字段應定義為外鍵；

(3) 表中字段的類型定義必須與其實際含義和可能的值匹配；

(4) 對表中的字段應根據實際使用要求對字段屬性進行約束，如非空、唯一性約束等；

(5) 如某一字段值需滿足特定要求，例如有特定的取值范圍等，則應為其定義合適的約束條件。

2 表邏輯設計規范

依托大量的數據庫分析與設計實踐，總結出表邏輯設計的規范如下：

(1) 應采用第三范式(3NF)的設計方法，最大化保證數據的完整性[2]。

(2) Oracle數據庫中，對周期性插入大批量數據操作的表或對周期性建立的大表(數據文件大于300M)，INITIAL EXTENT應設置成首次操作數據量的大小。具體如下。

建議把INITIAL EXTENT設置成等于NEXT EXTENT的大小；

表的EXTENTS次數盡量不超過200次；

對于不發生變化的表：PCTUSED設置為90，PCTFREE設置為5；

對于并非頻繁變化的表：PCTUSED設置為80，PCTFREE設置為5；

對于經常變化的表(插入與更新操作均較頻繁)：PCTUSED設置為60，PCTFREE設置為20；

對于僅插入操作頻繁但不經常修改原有記錄的表：PCTUSED設置為80，PCTFREE設置為10；

對于更新操作頻繁的表：PCTUSED設置為50，PCTFREE設置為40；

對于DB_BLOCK_SIZE>=8192的OLTP(聯機事務處理過程)數據庫信息系統，表及索引應設定為INITRANS>=4，MAXTRANS>=10。

(3) 對于頻繁使用的表(如字典表)，在內存空閑空間較多且數據量不大(如不超1000行)時，可采用緩存的方式保存，提升表的使用性能。

(4) 創建表時需指定到相應的數據表空間，確保不同數據存儲在不同的表空間。

(5) 主鍵字段個數不能過多，盡量不修改主鍵值。

(6) 在數據庫中實現數據完整性的校驗，避免在應用中對數據進行完整性校驗。

(7) 避免使用字符類型存放時間或日期類數據。

(8) 避免使用字符類型存放數值類型的數據。

(9) 避免表中字段數值類型直接使用INT型，應明確寫明字段的取值范圍，如NUMBER(8)。

(10) 應盡量減少使用大字段，如BLOB，CLOB，LONG，TEXT與IMAGE等。

3 表分區設計規范

隨著表中數據量不斷增大，數據查詢的速度會逐漸降低，從而導致應用系統的性能下降，此時應考慮對表進行分區操作。

表分區具有以下優點。

(1) 提升查詢性能：分區對象的查詢可以僅查詢與自己相關的分區，不用再對整表進行查詢，從而提高查詢效率；

(2) 減少故障損失：表的某個分區發生故障時不會影響該表其他分區的正常使用；

(3) 維護效率高：只需對故障分區進行修復，不用對整表修復；

表分區設計的規范與建議如下。

(4) 應對有需要的大表進行分區，以提高性能和可維護性；

(5) 當表的大小接近或超過4GB時，可考慮對其進行分區；

(6) 對于OLTP系統，當表的數據量非常龐大時，應考慮對表進行分區，對于硬件性能較好的服務器，可適度放寬要求；

(7) 對經常執行并行操作的表，建議對其進行分區。

數據庫分區表的基本類型可分如下4類：

范圍分區。范圍分區以表中分區字段的值的范圍來作為分區的劃分條件。不同的記錄將按照分區字段的值的不同，存放在對應在的范圍分區中。該分區方式最適用于查詢條件中對分區鍵值進行區間查詢的場景；

哈希分區。哈希分區將表中數據的存放位置依據分區字段的值進行特定的哈希計算后得到的結果來決定，該分區方式最適用于查詢條件中對分區字段采用等號進行比較的情況[3]；

列表分區。與范圍分區不同，列表分區必須指定分區字段的具體值而不僅僅是一個范圍。該分區的使用范圍比范圍分區和哈希分區小；

組合分區。通過在不同字段上，使用“范圍分區”、 “哈希分區”以及“列表分區”的不同組合方式，實現組合分區。該分區適用于數據量大的表以及對性能具有特殊要求的情況。

4 字段邏輯設計規范

對表中字段的邏輯設計規范如下。

(1) 應對通信地址等特定的信息采用多個字段來表示，增加靈活性；

(2) 應使用角色實體來定義關聯屬性，方便創建時間關聯關系；

(3) 數字類型與文本類型的字段長度應保證充足；

(4) 對刪除記錄的操作需用統一的特定字段標注，而非直接刪除記錄；

(5) 盡量避免使用大字段。

5 索引設計規范

在設計關系型系統的數據庫時，通常需要創建大量索引，良好的索引可以加快表與表之間的連接[4]，通常能顯著減少復雜的SQL查詢所花費的時間，從而提升數據庫系統的性能[5]。

創建索引應遵循如下規范。

(1) 不要索引大型字符字段；

(2) 不要索引常用的小型表；

(3) DML操作頻繁的表應盡量少建索引；

(4) 盡量不要將經常修改的字段作為索引字段；

(5) 選擇性高的字段適合建立索引；

(6) 如某一字段或字段組合經常在WHERE子句中使用并且滿足該字段或字段組合查詢條件的行數占表總行數的比例小于等于5%時，適合創建索引；

(7) 應在頻繁使用DISTINCT關鍵字查詢的字段上建立索引；

(8) 進行表連接時，應在連接字段上建立索引；

(9) 復合索引創建時應把最常用的字段放在第一位，而將不常用的字段排后；

(10) 當索引字段的記錄重復較多而DISTINCT記錄值又較少(一般少于30)時，應建立位圖索引；

(11) 盡量避免對OLTP系統的一張數據庫表創建過多索引，例如超過10個；

(12) 由于位圖索引會影響DML操作的速度，因此OLTP系統中盡量不使用位圖索引；

(13) 如查詢中需使用函數，且滿足該查詢條件的記錄數比例很小，建議創建相應的函數索引；

創建索引完畢后，正確使用索引才能使其發揮作用，使用索引的規范如下。

(1) 確認是否已使用索引。應盡量使用選擇率高的索引，避免全表掃描；

(2) 確認已使用的索引是否合理。錯誤的索引將導致性能的降低。索引的合理使用規范如下：

應避免在字段上進行類型轉換操作，否則無法使用該字段上的索引；

避免對索引字段進行任何計算操作，對索引字段的計算操作會引起索引的失效；

盡可能增加查詢的條件，限制全范圍的查詢

當索引效率很低時，應避免使用索引；

盡量避免模糊查詢，如必須使用模糊查詢時，盡量使用前端匹配的模糊查詢；

WHERE條件中對索引字段盡量使用等值“=”進行比較查詢；

盡量使用前導字段(復合索引的首字段)作為查詢條件；

WHERE條件中對索引字段的查詢條件應保證比較值的類型和字段類型一致。

索引在創建和維護過程中需注意如下事項。

(1) 創建索引時數據和索引應放在不同的表空間；

(2) 創建分區表索引時，盡量創建本地索引；

(3) 對于經常執行刪除操作的表上的索引應定期重建索引。由于索引重建時會阻塞DML操作，應選擇在業務空閑時進行，盡量減少其對業務產生影響；

(4) 對于鍵值頻繁更新的索引，也應定期進行重建；

(5) 刪除無用的索引，避免多余索引降低數據庫DML操作的執行速度。

6 總結

關系型數據庫是信息應用系統的核心組成部分，不合理的關系型數據庫設計會加大編程難度，引起操作繁瑣、性能降低、空間浪費等不良后果，甚至影響應用系統的安全與穩定，合理設計關系型數據庫十分有必要。而邏輯結構的設計是關系型數據庫設計過程中的重要環節，邏輯結構設計的好壞直接決定并影響了數據的完整性、準確性與一致性，因此在關系型數據庫邏輯設計過程中研究并提出相應的規范具有重大價值。

[1] 陶勇，丁維明. 數據庫中規范化與反規范化設計的比較與分析[J]. 計算機技術與發展, 2006,16(4):107-109.

[2] 丁智斌，石浩磊. 關系數據庫設計與規范化[J]. 計算機與數字工程, 2005,33(2):114-116.

[3] 韋平飛. 移動業務運營支撐系統數據庫性能優化的研究[D]. 廣州:華南理工大學, 2011.

[4] 王力等. 基于免疫遺傳算法的關系型數據庫查詢優化技術[J]. 微型電腦應用, 2008,24(3):45-47.

[5] 張若唯. 基于ORACLE的煉鋼—連鑄綜合優化系統信息平臺的設計與實現[D]. 沈陽:東北大學, 2012.