數據庫優化設計注意事項
本文首先討論了基于第三范式的數據庫表的基本設計，著重論述了建立主鍵和索引的策略和方案，然后從數據庫表的擴展設計和庫表對象的放置等角度概述了數據庫管理系統的優化方案。
1 引言
數據庫優化的目標無非是避免磁盤I/O瓶頸、減少CPU利用率和減少資源競爭。為了便于讀者閱讀和理解，筆者參閱了Sybase、Informix和Oracle等大型數據庫系統參考資料，基于多年的工程實踐經驗，從基本表設計、擴展設計和數據庫表對象放置等角度進行討論，著重討論了如何避免磁盤I/O瓶頸和減少資源競爭，相信讀者會一目了然。
2 基于第三范式的基本表設計
在基于表驅動的信息管理系統（MIS）中，基本表的設計規范是第三范式（3NF）。第三范式的基本特征是非主鍵屬性只依賴于主鍵屬性。基于第三范式的數據庫表設計具有很多優點：
- 消除了冗余數據，節省了磁盤存儲空間；
- 有良好的數據完整性限制，即基于主外鍵的參照完整限制和基于主鍵的實體完整性限制，這使得數據容易維護，也容易移植和更新；
- 數據的可逆性好，在做連接（Join）查詢或者合并表時不遺漏、也不重復；
- 因消除了冗余數據（冗余列），在查詢（Select）時每個數據頁存的數據行就多，這樣就有效地減少了邏輯I/O，每個Cash存的頁面就多，也減少物理I/O；
- 對大多數事務(Transaction)而言，運行性能好；
- 物理設計(Physical Design)的機動性較大，能滿足日益增長的用戶需求。
在基本表設計中，表的主鍵、外鍵、索引設計占有非常重要的地位，但系統設計人員往往只注重于滿足用戶要求，而沒有從系統優化的高度來認識和重視它們。實際上，它們與系統的運行性能密切相關。現在從系統數據庫優化角度討論這些基本概念及其重要意義：
主鍵(Primary Key)：主鍵被用于復雜的SQL語句時，頻繁地在數據訪問中被用到。一個表只有一個主鍵。主鍵應該有固定值（不能為Null或缺省值，要有相對穩定性），不含代碼信息，易訪問。把常用（眾所周知）的列作為主鍵才有意義。短主鍵最佳（小于25bytes），主鍵的長短影響索引的大小，索引的大小影響索引頁的大小，從而影響磁盤I/O。主鍵分為自然主鍵和人為主鍵。自然主鍵由實體的屬性構成，自然主鍵可以是復合性的，在形成復合主鍵時，主鍵列不能太多，復合主鍵使得Join*作復雜化、也增加了外鍵表的大小。人為主鍵是，在沒有合適的自然屬性鍵、或自然屬性復雜或靈敏度高時，人為形成的。人為主鍵一般是整型值（滿足最小化要求），沒有實際意義，也略微增加了表的大小；但減少了把它作為外鍵的表的大小。
外鍵（Foreign Key）：外鍵的作用是建立關系型數據庫中表之間的關系（參照完整性），主鍵只能從獨立的實體遷移到非獨立的實體，成為后者的一個屬性，被稱為外鍵。
索引(Index)：利用索引優化系統性能是顯而易見的，對所有常用于查詢中的Where子句的列和所有用于排序的列創建索引，可以避免整表掃描或訪問，在不改變表的物理結構的情況下，直接訪問特定的數據列，這樣減少數據存取時間；利用索引可以優化或排除耗時的分類*作；把數據分散到不同的頁面上，就分散了插入的數據；主鍵自動建立了唯一索引，因此唯一索引也能確保數據的唯一性（即實體完整性）；索引碼越小，定位就越直接；新建的索引效能最好，因此定期更新索引非常必要。索引也有代價：有空間開銷，建立它也要花費時間，在進行Insert、Delete和Update*作時，也有維護代價。索引有兩種：聚族索引和非聚族索引。一個表只能有一個聚族索引，可有多個非聚族索引。使用聚族索引查詢數據要比使用非聚族索引快。在建索引前，應利用數據庫系統函數估算索引的大小。
- 聚族索引（Clustered Index）：聚族索引的數據頁按物理有序儲存，占用空間小。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢或高度重復的列（連續磁盤掃描）；被用于連接Join*作的列；被用于Order by和Group by子句的列。聚族索引不利于插入*作，另外沒有必要用主鍵建聚族索引。
- 非聚族索引（Nonclustered Index）：與聚族索引相比，占用空間大，而且效率低。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢（在沒有聚族索引時）、主鍵或外鍵列、點（指針類）或小范圍（返回的結果域小于整表數據的20%）查詢；被用于連接Join*作的列、主鍵列（范圍查詢）；被用于Order by和Group by子句的列；需要被覆蓋的列。對只讀表建多個非聚族索引有利。
索引也有其弊端，一是創建索引要耗費時間，二是索引要占有大量磁盤空間，三是增加了維護代價（在修改帶索引的數據列時索引會減緩修改速度）。
那么，在哪種情況下不建索引呢？對于小表（數據小于5頁）、小到中表（不直接訪問單行數據或結果集不用排序）、單值域（返回值密集）、索引列值太長（大于20bitys）、容易變化的列、高度重復的列、Null值列，對沒有被用于Where子語句和Join查詢的列都不能建索引。另外，對主要用于數據錄入的，盡可能少建索引。當然，也要防止建立無效索引，當Where語句中多于5個條件時，維護索引的開銷大于索引的效益，這時，建立臨時表存儲有關數據更有效。
批量導入數據時的注意事項：在實際應用中，大批量的計算（如電信話單計費）用C語言程序做，這種基于主外鍵關系數據計算而得的批量數據（文本文件），可利用系統的自身功能函數（如Sybase的BCP命令）快速批量導入，在導入數據庫表時，可先刪除相應庫表的索引，這有利于加快導入速度，減少導入時間。在導入后再重建索引以便優化查詢。
鎖：鎖是并行處理的重要機制，能保持數據并發的一致性，即按事務進行處理；系統利用鎖，保證數據完整性。因此，我們避免不了死鎖，但在設計時可以充分考慮如何避免長事務，減少排它鎖時間，減少在事務中與用戶的交互，杜絕讓用戶控制事務的長短；要避免批量數據同時執行，尤其是耗時并用到相同的數據表。鎖的征用：一個表同時只能有一個排它鎖，一個用戶用時，其它用戶在等待。若用戶數增加，則Server的性能下降，出現“假死”現象。如何避免死鎖呢？從頁級鎖到行級鎖，減少了鎖征用；給小表增加無效記錄，從頁級鎖到行級鎖沒有影響，若在同一頁內競爭有影響，可選擇合適的聚族索引把數據分配到不同的頁面；創建冗余表；保持事務簡短；同一批處理應該沒有網絡交互。
查詢優化規則：在訪問數據庫表的數據(Access Data)時，要盡可能避免排序（Sort）、連接(Join)和相關子查詢*作。經驗告訴我們，在優化查詢時，必須做到：
- 盡可能少的行；
- 避免排序或為盡可能少的行排序，若要做大量數據排序，最好將相關數據放在臨時表中*作；用簡單的鍵（列）排序，如整型或短字符串排序；
- 避免表內的相關子查詢；
- 避免在Where子句中使用復雜的表達式或非起始的子字符串、用長字符串連接；
- 在Where子句中多使用“與”（And）連接，少使用“或”(Or)連接；
- 利用臨時數據庫。在查詢多表、有多個連接、查詢復雜、數據要過濾時，可以建臨時表（索引）以減少I/O。但缺點是增加了空間開銷。
除非每個列都有索引支持，否則在有連接的查詢時分別找出兩個動態索引，放在工作表中重新排序。
3 基本表擴展設計
基于第三范式設計的庫表雖然有其優越性（見本文第一部分），然而在實際應用中有時不利于系統運行性能的優化：如需要部分數據時而要掃描整表，許多過程同時競爭同一數據，反復用相同行計算相同的結果，過程從多表獲取數據時引發大量的連接*作，當數據來源于多表時的連接*作；這都消耗了磁盤I/O和CPU時間。
尤其在遇到下列情形時，我們要對基本表進行擴展設計：許多過程要頻繁訪問一個表、子集數據訪問、重復計算和冗余數據，有時用戶要求一些過程優先或低的響應時間。
如何避免這些不利因素呢？根據訪問的頻繁程度對相關表進行分割處理、存儲冗余數據、存儲衍生列、合并相關表處理，這些都是克服這些不利因素和優化系統運行的有效途徑。
3.1 分割表或儲存冗余數據
分割表分為水平分割表和垂直分割表兩種。分割表增加了維護數據完整性的代價。
水平分割表：一種是當多個過程頻繁訪問數據表的不同行時，水平分割表，并消除新表中的冗余數據列；若個別過程要訪問整個數據，則要用連接*作，這也無妨分割表；典型案例是電信話單按月分割存放。另一種是當主要過程要重復訪問部分行時，最好將被重復訪問的這些行單獨形成子集表（冗余儲存），這在不考慮磁盤空間開銷時顯得十分重要；但在分割表以后，增加了維護難度，要用觸發器立即更新、或存儲過程或應用代碼批量更新，這也會增加額外的磁盤I/O開銷。
垂直分割表（不破壞第三范式），一種是當多個過程頻繁訪問表的不同列時，可將表垂直分成幾個表，減少磁盤I/O（每行的數據列少，每頁存的數據行就多，相應占用的頁就少），更新時不必考慮鎖，沒有冗余數據。缺點是要在插入或刪除數據時要考慮數據的完整性，用存儲過程維護。另一種是當主要過程反復訪問部分列時，最好將這部分被頻繁訪問的列數據單獨存為一個子集表（冗余儲存），這在不考慮磁盤空間開銷時顯得十分重要；但這增加了重疊列的維護難度，要用觸發器立即更新、或存儲過程或應用代碼批量更新，這也會增加額外的磁盤I/O開銷。垂直分割表可以達到最大化利用Cache的目的。
總之，為主要過程分割表的方法適用于：各個過程需要表的不聯結的子集，各個過程需要表的子集，訪問頻率高的主要過程不需要整表。在主要的、頻繁訪問的主表需要表的子集而其它主要頻繁訪問的過程需要整表時則產生冗余子集表。
注意，在分割表以后，要考慮重新建立索引。
3.2 存儲衍生數據
對一些要做大量重復性計算的過程而言，若重復計算過程得到的結果相同（源列數據穩定，因此計算結果也不變），或計算牽扯多行數據需額外的磁盤I/O開銷，或計算復雜需要大量的CPU時間，就考慮存儲計算結果（冗余儲存）。現予以分類說明：
若在一行內重復計算，就在表內增加列存儲結果。但若參與計算的列被更新時，必須要用觸發器更新這個新列。
若對表按類進行重復計算，就增加新表（一般而言，存放類和結果兩列就可以了）存儲相關結果。但若參與計算的列被更新時，就必須要用觸發器立即更新、或存儲過程或應用代碼批量更新這個新表。
若對多行進行重復性計算（如排名次），就在表內增加列存儲結果。但若參與計算的列被更新時，必須要用觸發器或存儲過程更新這個新列。
總之，存儲冗余數據有利于加快訪問速度；但違反了第三范式，這會增加維護數據完整性的代價，必須用觸發器立即更新、或存儲過程或應用代碼批量更新，以維護數據的完整性。
3.3 消除昂貴結合
對于頻繁同時訪問多表的一些主要過程，考慮在主表內存儲冗余數據，即存儲冗余列或衍生列（它不依賴于主鍵），但破壞了第三范式，也增加了維護難度。在源表的相關列發生變化時，必須要用觸發器或存儲過程更新這個冗余列。當主要過程總同時訪問兩個表時可以合并表，這樣可以減少磁盤I/O*作，但破壞了第三范式，也增加了維護難度。對父子表和1：1關系表合并方法不同：合并父子表后，產生冗余表；合并1：1關系表后，在表內產生冗余數據。
4 數據庫對象的放置策略
數據庫對象的放置策略是均勻地把數據分布在系統的磁盤中，平衡I/O訪問，避免I/O瓶頸。
- 訪問分散到不同的磁盤，即使用戶數據盡可能跨越多個設備，多個I/O運轉，避免I/O競爭，克服訪問瓶頸；分別放置隨機訪問和連續訪問數據。
- 分離系統數據庫I/O和應用數據庫I/O。把系統審計表和臨時庫表放在不忙的磁盤上。
- 把事務日志放在單獨的磁盤上，減少磁盤I/O開銷，這還有利于在障礙后恢復，提高了系統的安全性。
- 把頻繁訪問的“活性”表放在不同的磁盤上；把頻繁用的表、頻繁做Join*作的表分別放在單獨的磁盤上，甚至把把頻繁訪問的表的字段放在不同的磁盤上，把訪問分散到不同的磁盤上，避免I/O爭奪；
- 利用段分離頻繁訪問的表及其索引（非聚族的）、分離文本和圖像數據。段的目的是平衡I/O，避免瓶頸，增加吞吐量，實現并行掃描，提高并發度，最大化磁盤的吞吐量。利用邏輯段功能，分別放置“活性”表及其非聚族索引以平衡I/O。當然最好利用系統的默認段。另外，利用段可以使備份和恢復數據更加靈活，使系統授權更加靈活。
上述便是筆者根據自身經驗給出的在數據庫管理中的優化方案。筆者沒有給出具體使用的軟件，大家可以根據自身情況來嘗試。
原文鏈接：http://www.cnblogs.com/jhabb/archive/2011/03/04/1970605.html
【編輯推薦】
