資料庫優化設計方案

本文首先讨論了基于第三範式的資料庫表的基本設計，着重論述了建立主鍵和索引的政策和方案，然後從資料庫表的擴充設計和庫表對象的放置等角度概述了資料庫管理系統的優化方案。 

　　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。當然最好利用系統的預設段。另外，利用段可以使備份和恢複資料更加靈活，使系統授權更加靈活。

本文轉自yonghu86 51CTO部落格，原文連結：http://blog.51cto.com/yonghu/1321390，如需轉載請自行聯系原作者