資料庫效能調優設計方案

本文首先討論了基於第三正規化的資料庫表的基本設計，著重論述了建立主鍵和索引的策略和方案，然後從資料庫表的擴充套件設計和庫表物件的放置等角度概述了資料庫管理系統的優化方案。

　　1 引言
　　資料庫優化的目標無非是避免磁碟I/O瓶頸、減少CPU利用率和減少資源競爭。為了便於讀者閱讀和理解，筆者參閱了Sybase、Informix和Oracle等大型資料庫系統參考資料，基於多年的工程實踐經驗，從基本表設計、擴充套件設計和資料庫表物件放置等角度進行討論，著重討論瞭如何避免磁碟I/O瓶頸和減少資源競爭，相信讀者會一目瞭然。

　　2 基於第三正規化的基本表設計

　　在基於表驅動的資訊管理系統（MIS）中，基本表的設計規範是第三正規化（3NF）。第三正規化的基本特徵是非主鍵屬性只依賴於主鍵屬性。基於第三正規化的資料庫表設計具有很多優點：一是消除了冗餘資料，節省了磁碟儲存空間；二是有良好的資料完整性限制，即基於主外來鍵的參照完整限制和基於主鍵的實體完整性限制，這使得資料容易維護，也容易移植和更新；三是資料的可逆性好，在做連線（Join）查詢或者合併表時不遺漏、也不重複；四是因消除了冗餘資料（冗餘列）， 在查詢（Select）時每個資料頁存的資料行就多，這樣就有效地減少了邏輯I/O，每個Cash存的頁面就多，也減少物理I/O；五是對大多數事務(Transaction)而言，執行效能好；六是物理設計(Physical Design)的機動性較大，能滿足日益增長的使用者需求。

　　在基本表設計中，表的主鍵、外來鍵、索引設計佔有非常重要的地位，但系統設計人員往往只注重於滿足使用者要求，而沒有從系統優化的高度來認識和重視它們。實際上，它們與系統的執行效能密切相關。現在從系統資料庫優化角度討論這些基本概念及其重要意義：

　　（1）主鍵(Primary Key)：主鍵被用於複雜的SQL語句時，頻繁地在資料訪問中被用到。一個表只有一個主鍵。主鍵應該有固定值（不能為Null或預設值，要有相對穩定性），不含程式碼資訊，易訪問。把常用（眾所周知）的列作為主鍵才有意義。短主鍵最佳（小於25bytes），主鍵的長短影響索引的大小，索引的大小影響索引頁的大小，從而影響磁碟I/O。主鍵分為自然主鍵和人為主鍵。自然主鍵由實體的屬性構成，自然主鍵可以是複合性的，在形成複合主鍵時，主鍵列不能太多，複合主鍵使得Join*作複雜化、也增加了外來鍵表的大小。人為主鍵是，在沒有合適的自然屬性鍵、或自然屬性複雜或靈敏度高時，人為形成的。人為主鍵一般是整型值（滿足最小化要求），沒有實際意義，也略微增加了表的大小；但減少了把它作為外來鍵的表的大小。

　　（2）外來鍵（Foreign Key）：外來鍵的作用是建立關係型資料庫中表之間的關係（參照完整性），主鍵只能從獨立的實體遷移到非獨立的實體，成為後者的一個屬性，被稱為外來鍵。

　　（3）索引(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命令）快速批量匯入，在匯入資料庫表時，可先刪除相應庫表的索引，這有利於加快匯入速度，減少匯入時間。在匯入後再重建索引以便優化查詢。

　　（4）鎖：鎖是並行處理的重要機制，能保持資料併發的一致性，即按事務進行處理；系統利用鎖，保證資料完整性。因此，我們避免不了死鎖，但在設計時可以充分考慮如何避免長事務，減少排它鎖時間，減少在事務中與使用者的互動，杜絕讓使用者控制事務的長短；要避免批量資料同時執行，尤其是耗時並用到相同的資料表。鎖的徵用：一個表同時只能有一個排它鎖，一個使用者用時，其它使用者在等待。若使用者數增加，則Server的效能下降，出現“假死”現象。如何避免死鎖呢？從頁級鎖到行級鎖，減少了鎖徵用；給小表增加無效記錄，從頁級鎖到行級鎖沒有影響，若在同一頁內競爭有影響，可選擇合適的聚族索引把資料分配到不同的頁面；建立冗餘表；保持事務簡短；同一批處理應該沒有網路互動。

　　（5）查詢優化規則：在訪問資料庫表的資料(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。當然最好利用系統的預設段。另外，利用段可以使備份和恢復資料更加靈活，使系統授權更加靈活。