基于SQL SERVER的數(shù)據(jù)庫查詢優(yōu)化淺析

1、基于SQL SERVER的數(shù)據(jù)庫查詢優(yōu)化淺析論文導(dǎo)讀：在數(shù)據(jù)庫的眾多應(yīng)用實例中，相對于數(shù)據(jù)庫的其他操作，查詢操作所占的比重最大，根據(jù)的統(tǒng)計資料，數(shù)據(jù)庫管理系統(tǒng)以上的工作是用于數(shù)據(jù)的查詢和檢索。因此，查詢速度的快慢直接影響到數(shù)據(jù)庫系統(tǒng)的性能。為了優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能，除了可以通過改進數(shù)據(jù)庫使用的硬件條件、軟件環(huán)境和規(guī)范化設(shè)計外，數(shù)據(jù)查詢的優(yōu)化，提高查詢的響應(yīng)時間對于改善性能也至關(guān)重要。關(guān)鍵詞：SQLSERVER，數(shù)據(jù)庫，查詢，優(yōu)化1 概述在數(shù)據(jù)庫的眾多應(yīng)用實例中，相對于數(shù)據(jù)庫的其他操作，查詢操作所占的比重最大，根據(jù) 的統(tǒng)計資料， 數(shù)據(jù)庫管理系統(tǒng)以上的工作是用于數(shù)據(jù)的查詢和檢索。因此，查詢速度的快

2、慢直接影響到數(shù)據(jù)庫系統(tǒng)的性能。為了優(yōu)化數(shù)據(jù)庫系統(tǒng)的性能，除了可以通過改進數(shù)據(jù)庫使用的硬件條件、軟件環(huán)境和規(guī)范化設(shè)計外，數(shù)據(jù)查詢的優(yōu)化，提高查詢的響應(yīng)時間對于改善 性能也至關(guān)重要。2 邏輯結(jié)構(gòu)設(shè)計關(guān)系的規(guī)范化是一種根據(jù)關(guān)系的屬性間函數(shù)相關(guān)性的規(guī)則把數(shù)據(jù)分布到多個表的技術(shù)。規(guī)范化程度較高，進行查詢時就容易會產(chǎn)生占用較高系統(tǒng)資源和高I/O的代價，會降低系統(tǒng)響應(yīng)時間。為了提高數(shù)據(jù)庫的性能，可使用含有一定數(shù)據(jù)冗余的較低范式的關(guān)系，當然這種樣做會使系統(tǒng)產(chǎn)生數(shù)據(jù)不一致性問題。因此在數(shù)據(jù)庫的邏輯設(shè)計時，我們需要在數(shù)據(jù)冗余度和響應(yīng)時間上進行權(quán)衡考慮，靈活的采用規(guī)范化和非規(guī)范化相結(jié)合的策略進行數(shù)據(jù)庫設(shè)計。2.1減

3、少連接運算連接運算是開銷很大的操作，參與連接的關(guān)系越多、越大，開銷也越大。必須進行連接時，一般要先使用選擇、投影等操作把不需要的數(shù)據(jù)都去掉，以減少連接的負擔。我們數(shù)據(jù)庫設(shè)計時，規(guī)范化設(shè)計一般是將非規(guī)范化的實體分割成較小的列和更多的表，從而減少數(shù)據(jù)的冗余，消除更新、刪除異常。但關(guān)系劃分的越細，我們在查詢時就會涉及到多張表，要使用多的連接，從而就會大大降低查詢的效率。所以我們數(shù)據(jù)庫設(shè)計時，并非達到的范式越高越好，為了保證查詢性能，有時不得不犧牲規(guī)范化的要求選擇較低的范式。2.2 分割關(guān)系關(guān)系的大小對查詢的速度影響頗大，有時候我們會將一個很大的關(guān)系分割成幾個小的關(guān)系來提高查詢。（1）從垂直方向分割關(guān)

4、系。若把一個關(guān)系中常用的屬性和很少使用的屬性分成兩個關(guān)系，則可提高常用查詢的速度，要注意的是分割時，每個關(guān)系中都必須含有首要關(guān)鍵字。（2）水平分割關(guān)系。例如關(guān)于學(xué)生的數(shù)據(jù)，可以把全校學(xué)生的數(shù)據(jù)放在一個關(guān)系中，也可按系建立學(xué)生關(guān)系。前者對全校范圍的查詢是方便的，后者可以顯著提高一個系范圍內(nèi)的查詢速度。如果一般的查詢都由系里的人員進行，查詢的是本系內(nèi)的內(nèi)容，則按系建立學(xué)生關(guān)系可以提高性能。如果數(shù)據(jù)庫系統(tǒng)有多個磁盤驅(qū)動器，則可把水平分割的關(guān)系分布在不同的磁盤組上，可以并行訪問，提高數(shù)據(jù)庫的性能。3 物理結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)設(shè)計的目的是使數(shù)據(jù)庫中數(shù)據(jù)以合理的存儲結(jié)構(gòu)和存儲方法存儲在物理設(shè)備上。（1）當進行

5、數(shù)據(jù)存儲時把頻繁被訪問的數(shù)據(jù)或記錄同較少被訪問的數(shù)據(jù)或記錄分開，分別放到高速和低速存儲設(shè)備上。（2）把text、image和大二進制類型字段列存放在一個單獨的物理設(shè)備上可以提高訪問的性能。（3）選擇合適的字段類型，特別是對于被索引的列更是如此。比如能使用tinyint類型就不要用bigint類型，這樣的關(guān)系可以減少I/O操作。4 合理使用索引當SQL Server需要定位某一行記錄或某一范圍的記錄時，首先確定表中是否存在索引:如果不存在有效的索引，則對整個表進行逐行的掃描；如果存在索引，則使用索引對數(shù)據(jù)進行訪問。索引的作用即相當于書的日錄，通過索引可快速定位所需要查找的內(nèi)容，避免全表搜索的發(fā)生

6、。但使用索引也存在如下的問題:一是索引需要占用額外的存儲空間；二是在數(shù)據(jù)進行更新時，索引也要作出相應(yīng)的更新，因此會增加許多額外的開銷。因此，索引的使用要恰到好處?？梢钥紤]建立索引的列：（1）主鍵通常，檢索、存取表是通過主鍵來進行的。因此，應(yīng)該考慮在主鍵上建立索引。（2） 連接中頻繁使用的列用于連接的列若按順序存放，系統(tǒng)可以很快地執(zhí)行連接。如外鍵，除用于實現(xiàn)參照完整性外，還經(jīng)常用于進行表的連接。（3） 在某一范圍內(nèi)頻繁搜索的列和按排序順序頻繁檢索的列。不考慮創(chuàng)建索引的列：（1） 很少或從來不在查詢中引用的列，因為系統(tǒng)很少或從來不根據(jù)這個列的值去查找數(shù)據(jù)行。（2）只有兩個或很少幾個值的列（如性別，

7、只有兩個值“男”或“女”），以這樣的列創(chuàng)建索引并不能得到建立索引的好處。（3）以bit、text、image數(shù)據(jù)類型定義的列。（4）數(shù)據(jù)行數(shù)很少的小表一般也沒有必要創(chuàng)建索引。表上的索引過多會影響更新、插入、刪除的性能，因為所有的索引都須做相應(yīng)的調(diào)整；另外，所有的分頁操作都被記錄在日志中，這也會增加I/O操作。索引應(yīng)根據(jù)需要而定，不要盲目多建。5 查詢語句優(yōu)化用戶所寫的查詢語句的優(yōu)劣對于查詢效率至關(guān)重要。5.1 簡化排序應(yīng)簡化或避免對大型表進行重復(fù)的排序。如果排序不可避免，則應(yīng)盡量簡化它，如縮小排序的列的范圍等。5.2 使用exist謂詞代替in子查詢子查詢含有exists謂詞時，子查詢都只要判

8、斷邏輯的真假。這樣DBMS的優(yōu)化器就僅根據(jù)索引就可以完成工作。因此使用exists謂詞查詢效率能比In子查詢效率高。此外盡可能用not exists代替not in也可提高查詢效能。5.3盡量避免相關(guān)子查詢。SQL中的嵌套查詢分為兩種相關(guān)子查詢和非相關(guān)子查詢。相關(guān)子查詢是一個列的標簽同時在主查詢和where子句中的查詢中出現(xiàn)，執(zhí)行時子查詢的查詢條件要依賴于主查詢所訪問的一個記錄行。當主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應(yīng)當盡量避免這種相關(guān)子查詢，如不能避免時，那么要在子查詢中過濾掉盡可能多的行。5.4盡可能使用“=”代替like子句LIKE關(guān)鍵字支持

9、模糊匹配，但這種模糊匹配耗時間。如果可以替換，那么使用“=”操作符的WHERE子句性能最好。5.5優(yōu)化SELECT子句SELECT*請求返回的是查詢表中所有的列。如果在SELECT子句中只選擇必要的列，那相對于SELECT *子句可減少客服之間的通信量，當客服連接距離比較遠時更明顯。5.6盡量不用游標因為游標會引起頁級和表級鎖,且消耗網(wǎng)絡(luò)資源，又有較多的處理指令，所以除非必要，盡量不用游標，而采用等價的語句，即使語句會涉及到多個表掃描，也會更好在某些必須使用游標的場合，可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時表中，再對臨時表定義游標進行操作，這樣可使性能得到明顯提高5.7存儲過程的使用存儲過程是 服務(wù)器上一組預(yù)先定義并編譯好的 語句，當一個存儲過程被第一次運行時， 將該存儲過程放入內(nèi)存并完全編譯。在該存儲過程被再次調(diào)用時可以被馬上處理， 沒有任何額外開銷。充分利用存儲過程，可以減少網(wǎng)絡(luò)流量，加快執(zhí)行速度，提高查詢性能，同時也減少程序員的編程工作量。6 結(jié)束語SQL優(yōu)化的實質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識別的語句，充分利用索引，減少表掃描的/次數(shù)，盡量避免表搜索的發(fā)生的性能優(yōu)化是一個復(fù)雜的過程。上述這些只是涉及應(yīng)用層次的一些設(shè)計方法，深入研究還會涉及數(shù)據(jù)庫層的資源配置!網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層的總體設(shè)計。參考文獻1 楊學(xué)全,SQL SERVER實例教程（