從范式優(yōu)化到檢索優(yōu)化數(shù)據(jù)庫優(yōu)化技術(shù)的分析與探討

從范式優(yōu)化到檢索優(yōu)化數(shù)據(jù)庫優(yōu)化技術(shù)的分析與探討

隨著數(shù)據(jù)庫技術(shù)的發(fā)展，計算商業(yè)數(shù)據(jù)分析越來越受到重視。但由于傳統(tǒng)的基于文件的數(shù)據(jù)存儲和檢索方法不靈活且難于管理,因而數(shù)據(jù)庫優(yōu)化設(shè)計成為人們關(guān)注的題。如何有效地組織和處理大型數(shù)據(jù)庫的海量數(shù)據(jù),使得人們能方便、準確、快捷地完成數(shù)據(jù)的存取操作,成為對數(shù)據(jù)庫的建設(shè)及使用需要解決的問題。數(shù)據(jù)庫的數(shù)據(jù)存取主要有四個不同的調(diào)整級別,第一級調(diào)整是操作系統(tǒng)級包括硬件平臺,第二級調(diào)整是DBMS級的調(diào)整,第三級是數(shù)據(jù)庫設(shè)計級的調(diào)整,最后一個調(diào)整級是SQL級。通常依此四級調(diào)整級別對數(shù)據(jù)庫進行調(diào)整、優(yōu)化,數(shù)據(jù)庫的整體性能會得到很大的改善。所以,數(shù)據(jù)庫的開發(fā)過程中,程序員能夠在數(shù)據(jù)庫設(shè)計階段,能夠根據(jù)數(shù)據(jù)庫實際使用需要,采用適當?shù)姆妒絻?yōu)化對基本表進行合理的設(shè)計和調(diào)整,同時在數(shù)據(jù)庫應用程序開發(fā)時,注重提高查詢語句的執(zhí)行效率,將會在很大程度提高數(shù)據(jù)庫性能。1建立數(shù)據(jù)庫表的必要性在數(shù)據(jù)庫開發(fā)過程中,需要利用范式對基本表進行規(guī)范化。一般情況下,基本表的設(shè)計要求達到第三范式,第三范式的特征是在消除函數(shù)依賴的基礎(chǔ)上消除傳遞依賴,從而使非主鍵的其他屬性只依賴于主鍵屬性。第三范式的使用消除了由于數(shù)據(jù)冗余帶來的插入、更新和刪除操作產(chǎn)生的不可預期的副作用,同時保證了良好的的參照完整限制和實體完整性限制,使得數(shù)據(jù)容易維護。將規(guī)范化作為數(shù)據(jù)庫開發(fā)過程的完善工具時,首先利用E-R圖建立概念數(shù)據(jù)模型,并使用轉(zhuǎn)換規(guī)則生成基本表,然后利用范式分析各個表,最終保證基本表及其字段之間的關(guān)系滿足第三范式?；诘谌妒皆O(shè)計的數(shù)據(jù)庫表雖然有其優(yōu)越性,但是,滿足第三范式的數(shù)據(jù)庫設(shè)計,往往不一定是最好的設(shè)計。作為一項設(shè)計標準,第三范式“消除冗余以避免更改異?！逼赜跀?shù)據(jù)庫的更改功能。在數(shù)據(jù)庫設(shè)計中,當分解表來符合范式要求時,表的數(shù)量會增加,雖然數(shù)據(jù)庫越容易更改,但查詢的難度會隨之增加。如果數(shù)據(jù)庫在實際應用中主要用于查詢,那么過多追求避免更改異常不見得是一個恰到好處的設(shè)計目標。另外,加入更多的表容易導致過程從多表獲取數(shù)據(jù)時引發(fā)大量的連接操作,占用額外的磁盤I/O操作和處理邏輯,因此減慢了系統(tǒng)速度。所以,在許多過程要頻繁訪問一個表、子集數(shù)據(jù)訪問、重復計算和冗余數(shù)據(jù)的情況下,需要對基本表進行結(jié)構(gòu)優(yōu)化和性能調(diào)整。1.1水平分割表或垂直分割表當一個表包含過大數(shù)據(jù)量,而主要過程反復訪問一個表中的部分行或者部分列而不需要訪問整個表時,可以水平分割表或者垂直分割表,可以將被頻繁訪問的列數(shù)據(jù)單獨存為一個子集表。這些操作都是在不用考慮磁盤的開銷的情況下,同時分割表也增加了維護數(shù)據(jù)完整性的代價。1.2加相應列計算對一些要做大量重復性計算的過程而言,可以考慮增加相應列存儲計算結(jié)果。如在庫存表中每個倉庫的庫存總量由產(chǎn)品數(shù)量相加所得,而庫存總量又頻繁被查詢,則有必要將庫存總量作為一個單獨列加入到表中。1.3增加了實體代碼的存儲規(guī)范化規(guī)則要求獨立存儲外鍵,以表示1:M關(guān)聯(lián)。如果外鍵表示代碼,而用戶需要同時得到代碼以及相應的名稱,則可以將相應列和代碼存儲在一起,減少一些連接操作。用戶如訂單報表的輸出需要訂單號、產(chǎn)品名、訂單金額等字段,而訂單號和產(chǎn)品名在不同表中,查詢時需要對這兩個表進行連接查詢,為了提高查詢速度,可以在訂單表中增加產(chǎn)品名字段。1.4增加訂單總量字段到訂單總量表的連接儲存派生數(shù)據(jù)可以減少表的連接操作,省去檢索數(shù)據(jù)以計算派生數(shù)據(jù)的麻煩。如訂單總量頻繁要求被輸出,而計算訂單總量的兩個數(shù)據(jù)訂單數(shù)量和產(chǎn)品單價分別在訂單表和產(chǎn)品表中,那么可以增加訂單總量字段到訂單總量表中,從而避免了兩次連接操作。雖然范式的級別越高,關(guān)系模式越不會出現(xiàn)冗余和更新異常等不合理情況,但是關(guān)系模式的數(shù)目在不斷的分解過程中將會不可避免的逐漸增加,必然會導致連接操作的增多,而連接操作是一個開銷非常大的運算,過多的關(guān)系連接必然加重數(shù)據(jù)庫系統(tǒng)的負擔,影響系統(tǒng)效率,所以說模式的分解切不可一味追求其規(guī)范化程度而忽略了實際的使用方便性,在應用當中我們需要結(jié)合實際情況酌情考慮,盡可能使二者達到一個比較均衡的狀態(tài)。2高效執(zhí)行策略查詢是數(shù)據(jù)庫系統(tǒng)中使用最頻繁、最基本的操作。對于一個給定的查詢,通常會有許多種可能的執(zhí)行策略,查詢優(yōu)化就是從眾多策略中找出高效執(zhí)行策略的處理過程,是DBMS實現(xiàn)的關(guān)鍵技術(shù),對系統(tǒng)性能有很大影響。一些大規(guī)模的系統(tǒng),數(shù)據(jù)量非常大,全表掃描無疑耗費大量時間,查詢操作所基于的SELECT語句在SQL語句中是代價最大的語句,由程序員提交的SQL語句是系統(tǒng)優(yōu)化的基礎(chǔ),如果能夠采用比全表掃描更好的查詢策略,則可以使查詢時間大大降低,提高查詢效率。2.1索引設(shè)置位置合理的使用索引可以加快數(shù)據(jù)檢索速度,從而大大提高查詢效率,其使用原則如下:1)在經(jīng)常進行搜索的列上、主鍵列上以及經(jīng)常進行連接但是沒有指定為外鍵的列上建立索引。2)在頻繁進行排序或分組(即進行g(shù)roupby或orderby操作)的列上,以及經(jīng)常需要根據(jù)范圍進行搜索的列上建立索引。3)對于那些只有很少數(shù)據(jù)值的列不應該建立索引,如性別列。取值很少的列結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例,建立索引并不能提高查詢效率。4)對于數(shù)據(jù)類型為text、image等這類數(shù)據(jù)量大取值通常很少的數(shù)據(jù)不必要建立索引。5)經(jīng)常執(zhí)行大量插入和刪除的表不應當包含很多索引。6)避免在組合列上使用索引,大多數(shù)優(yōu)化組件可在同一個表上使用多個索引,相比之下組合列上的索引不夠靈活。2.2文復合文1)SQL當中盡量不采用性能比較低的IN操作符,強烈避免使用NOTIN操作符。相應的語句操作符可以由EXISTS或NOTEXISTS代替。2)ISNULL或ISNOTNULL操作會導致全盤掃描而放棄使用索引,如果并非嚴格要求為NULL值,在使用時應修改為和某一個缺省值進行比較。如:aisnotnull改為a>0或a>’’(空值)3)UNION操作符在進行表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算,刪除重復的記錄再返回結(jié)果,在沒有重復的記錄的情況下可以使用只是簡單的將兩個結(jié)果合并后就返回的UNIONALL代替。4)避免使用SELECT*語句,盡量明確查詢字段,不要返回用不到的任何字段。5)盡量避免在可以使用索引的列上使用函數(shù),函數(shù)將排除使用索引的可能性。6)如果列上的數(shù)據(jù)類型與相關(guān)常量值不匹配,將發(fā)生隱式轉(zhuǎn)換,如year=‘2011’條件將導致year列隱性轉(zhuǎn)化為字符數(shù)據(jù)類型,從而使year列不能使用索引。2.3前后組合的連接WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會產(chǎn)生直接的影響,如語句一:SELECTSnameWHEREItem=’彩電’ANDShop.S#=SC.S#語句二:SELECTSnameWHEREShop.S#=SC.S#ANDItem=’彩電’語句一先連接后做選擇,執(zhí)行時間需要152.5秒,而語句二先選擇后做連接,執(zhí)行時間需要7.5秒,所以在可以的情況下,先做選擇后做連接,而不是先做連接后做選擇。同樣,可以最大程度過濾掉大數(shù)量記錄的條件要寫在where句子的末尾。如需要查詢庫存量大于1000的飲料產(chǎn)品,在實際數(shù)據(jù)中庫存量大于1000的產(chǎn)品占到80%,而飲料產(chǎn)品占到產(chǎn)品總量的5%,所以應將飲料產(chǎn)品這一條件放在where句子的末尾。2.4基礎(chǔ)表的形成在FROM后面列表的順序會對SQL語句的執(zhí)行性能產(chǎn)生影響,FROM最后面的表將會最先被處理,在有多個查詢表的情況下,應該選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個以上的表需要連接查詢,則選擇交叉表作為基礎(chǔ)表,以避免由于表的順序不對而產(chǎn)生的十分耗服務器資源的數(shù)據(jù)交叉。3對于數(shù)據(jù)庫的特征,要求變化的方案由此可見,在設(shè)計數(shù)據(jù)庫時,最初的概念模型設(shè)計階段要遵守第三范式,在具體數(shù)據(jù)庫物理設(shè)計階段要根據(jù)數(shù)據(jù)庫使用實際情況,適當降低范式要求,增加冗余,在數(shù)據(jù)庫工作效率和空間開銷上