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

1、基于SQL SERVER的數(shù)據(jù)庫查詢優(yōu)化淺析論文導讀：在數(shù)據(jù)庫的眾多應用實例中，相對于數(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ù)據(jù)查詢的優(yōu)化，提高查詢的響應時間對于改善性能也至關重要。關鍵詞：SQLSERVER，數(shù)據(jù)庫，查詢，優(yōu)化1 概述在數(shù)據(jù)庫的眾多應用實例中，相對于數(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ù)據(jù)查詢的優(yōu)化，提高查詢的響應時間對于改善 性能也至關重要。2 邏輯結構設計關系的規(guī)范化是一種根據(jù)關系的屬性間函數(shù)相關性的規(guī)則把數(shù)據(jù)分布到多個表的技術。規(guī)范化程度較高，進行查詢時就容易會產(chǎn)生占用較高系統(tǒng)資源和高I/O的代價，會降低系統(tǒng)響應時間。為了提高數(shù)據(jù)庫的性能，可使用含有一定數(shù)據(jù)冗余的較低范式的關系，當然這種樣做會使系統(tǒng)產(chǎn)生數(shù)據(jù)不一致性問題。因此在數(shù)據(jù)庫的邏輯設計時，我們需要在數(shù)據(jù)冗余度和響應時間上進行權衡考慮，靈活的采用規(guī)范化和非規(guī)范化相結合的策略進行數(shù)據(jù)庫設計。2.1減

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

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

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

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

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

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

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