未 培

（安徽工商職業(yè)學(xué)院；電子信息系，合肥 230041）

1 、引言

查詢是數(shù)據(jù)庫的核心操作，查詢效率的高低直接影響到整個數(shù)據(jù)庫系統(tǒng)的性能[1-3]。對于數(shù)據(jù)量比較小的系統(tǒng)，查詢語句的執(zhí)行效率的影響并不是那么明顯，但當(dāng)數(shù)據(jù)量增加到一定程度，查詢執(zhí)行效率對整個系統(tǒng)的影響就會凸顯出來。目前隨著信息技術(shù)和網(wǎng)絡(luò)技術(shù)的發(fā)展，我們已經(jīng)處于大數(shù)據(jù)時代，海量數(shù)據(jù)存儲隨處可見[4]，本文以SQL SERVER數(shù)據(jù)庫為版本，基于某中型數(shù)據(jù)庫，通過具體的實(shí)例，研究查詢語句的優(yōu)化方案。

2 、優(yōu)化查詢策略

2.1 利用數(shù)據(jù)庫引擎優(yōu)化顧問及SQL SERVER PROFILER工具

SQL SERVER提供的性能工具數(shù)據(jù)庫引擎優(yōu)化顧問可以優(yōu)化數(shù)據(jù)庫，檢查指定數(shù)據(jù)庫中處理查詢的方式，然后建議如何通過修改物理設(shè)計(jì)結(jié)構(gòu)（例如索引、索引視圖和分區(qū)）來改善查詢處理性能[5-6]。SQL SERVER PROFILER是SQL SERVER跟蹤的圖形用戶界面，用于創(chuàng)建和管理跟蹤并分析和重播跟蹤結(jié)果[7-8]。我們可以根據(jù)數(shù)據(jù)庫引擎優(yōu)化顧問給出的建議對查詢進(jìn)行優(yōu)化，通過SQL SERVER PROFILER監(jiān)視查詢語句的執(zhí)行速度，對查詢進(jìn)行進(jìn)一步優(yōu)化。

（1）創(chuàng)建合適的索引

索引是以表列為基礎(chǔ)的數(shù)據(jù)對象，它保存了表中排序的索引列，并且記錄了索引列在數(shù)據(jù)表中的物理存儲位置，實(shí)現(xiàn)了表中數(shù)據(jù)的邏輯排序，創(chuàng)建索引的主要目的是提高Sql Server系統(tǒng)的性能，加快數(shù)據(jù)的查詢速度以及減少系統(tǒng)的響應(yīng)時間[5-6,9,10]。

一般來說，系統(tǒng)訪問數(shù)據(jù)庫中數(shù)據(jù)使用表掃描或索引查找。在SQL SERVER中，當(dāng)訪問表中的數(shù)據(jù)時，由數(shù)據(jù)庫管理系統(tǒng)（DBMS）確定該表中是否有合適的索引存在，如果對正在執(zhí)行的查詢有恰當(dāng)?shù)乃饕嬖冢屯ㄟ^索引的順序查找數(shù)據(jù)，效率會比較高；如果沒有索引，那么只有使用表掃描的方法訪問表中的數(shù)據(jù)，當(dāng)需要掃描的表中數(shù)據(jù)很多時，查詢數(shù)據(jù)就需要很長的時間[11]。

通過索引不僅可以加快數(shù)據(jù)檢索速度，還可以通過創(chuàng)建唯一索引，保證行記錄的唯一性。不過，建立索引也是有代價的，首先，創(chuàng)建有索引的表在數(shù)據(jù)庫中會占據(jù)更多的存儲空間；而且系統(tǒng)也會為維護(hù)索引花費(fèi)一定時間，特別是當(dāng)數(shù)據(jù)增刪頻繁時，維護(hù)時間更多。因此在創(chuàng)建索引時應(yīng)該仔細(xì)考慮在哪些列上適合創(chuàng)建索引，在哪些列上不適合創(chuàng)建索引。

通常按以下原則創(chuàng)建索引[5-6,9]：

①在經(jīng)常需要搜索的列上創(chuàng)建索引；

②在經(jīng)常用在連接的列上創(chuàng)建索引；

③對于那些在查詢中很少使用或者很少唯一數(shù)據(jù)值的列盡量不創(chuàng)建索引；

④當(dāng)列的修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時不考慮創(chuàng)建索引。

建立索引的T-SQL語句：

（2）通過優(yōu)化顧問進(jìn)行實(shí)例優(yōu)化

現(xiàn)以某高校技能考核管理系統(tǒng)數(shù)據(jù)庫為例，進(jìn)行查詢優(yōu)化驗(yàn)證，此數(shù)據(jù)庫為中型數(shù)據(jù)庫，以下例子中所用到的兩個表表結(jié)構(gòu)如下：

其中scourse表有208128條記錄，下面執(zhí)行select查詢語句，通過SQL SERVER PROFILER進(jìn)行跟蹤，對比查詢優(yōu)化前后的耗時情況。

在sql server management stdio窗口中執(zhí)行下面查詢語句，并打開SQL SERVER PROFILER對語句自行情況進(jìn)行跟蹤。

通過跟蹤得到，上面查詢語句執(zhí)行耗時為987毫秒，下面將剛才執(zhí)行的查詢語句保存為sql文件，并打開數(shù)據(jù)庫引擎優(yōu)化顧問，對該負(fù)荷文件進(jìn)行優(yōu)化，執(zhí)行后得到如下圖1的優(yōu)化建議。

通過圖1可以看出，系統(tǒng)建議在執(zhí)行此查詢語句前建立索引：

索引創(chuàng)建后，再次執(zhí)行上面查詢語句，通過SQL SERVER PROFILER跟蹤發(fā)現(xiàn)耗時僅12毫秒，優(yōu)化效率提高了98.8%。通過此實(shí)例可以看出，在對中大型數(shù)據(jù)庫進(jìn)行數(shù)據(jù)訪問時，建立恰當(dāng)?shù)乃饕行У靥岣吡瞬樵冃省?/p>

2.2 其它優(yōu)化建議

通過SQL SERVER提供的數(shù)據(jù)庫引擎優(yōu)化顧問對查詢語句進(jìn)行優(yōu)化,給出的優(yōu)化建議有時候并不是最合適的,偶爾查詢優(yōu)化器作出的選擇反而降低了查詢的效率,或者在某些情況下,程序有特殊的需求,這時我們需要研究其它優(yōu)化策略。

（1）優(yōu)化 where 條件

在寫查詢語句時，一個條件往往有多種寫法,where條件的表示對查詢的效率有很大的影響。如果語句中使用了！=、like、in等表示條件，均會引起表掃描，索引將不起作用，查詢的效率將比較低。所以在寫where子句時應(yīng)該注意以下幾個方面:

①盡量避免使用負(fù)邏輯運(yùn)算符:如<>、not in、not exists等；

②盡量避免使用通配符，如like’%市’；

③盡量避免在條件中使用公式和函數(shù)。

比如以下查詢語句均會引起表掃描，應(yīng)盡量避免使用：

（2）優(yōu)化多表查詢

在遇到多表查詢時，盡量避免超過5個以上的表進(jìn)行連接查詢，因?yàn)閰⑴c連接的表越多，編譯的時間越長，性能越不穩(wěn)定。需要很多表連接的查詢，可以借助臨時表分步驟去完成，另外為了提高查詢速度，建議在連接字段上建立索引。

（3）盡量避免使用子查詢

當(dāng)一個查詢?yōu)榱硪粋€查詢的條件時，稱為子查詢。當(dāng)一個字段既出現(xiàn)在主查詢中，又出現(xiàn)在子查詢中時，如果主查詢中列值發(fā)生改變，子查詢必須重新執(zhí)行一次，查詢嵌套的層數(shù)越多，查詢的效率就會越低，所以要盡量避免使用子查詢。如下面子查詢語句：

可以寫成如下語句，通過SQL SERVER PROFILER跟蹤發(fā)現(xiàn)執(zhí)行效率更高。

3 、小結(jié)

本文利用SQL SERVER性能工具對查詢優(yōu)化技術(shù)進(jìn)行了研究，但查詢語句執(zhí)行的效率與電腦硬件配置、服務(wù)器配置、當(dāng)前軟件環(huán)境等多方面有關(guān)，所以以上研究數(shù)據(jù)內(nèi)容僅供參考，目前我們處于大數(shù)據(jù)時代，研究如何提高數(shù)據(jù)查詢速度具有實(shí)際意義。

[1]彭勇.SQLServer查詢性能分析與查詢效率提高[J].西南民族大學(xué)學(xué)報(bào)(自然科學(xué)版).2012,38(3):483-486

[2]石劍平,蔡光程.SQL Server2005查詢優(yōu)化技術(shù)的研究與實(shí)現(xiàn)[J].信息系統(tǒng)工程.2010(04)：78-79

[3]衛(wèi)國標(biāo),林勇.SQL Server查詢優(yōu)化技術(shù)分析與實(shí)現(xiàn)[J].電腦知識與技術(shù).2013,9(16):3671-3673

[4]劉維學(xué).SQL Server查詢優(yōu)化器原理與優(yōu)化實(shí)例分析[J].計(jì)算機(jī)技術(shù)與發(fā)展.2013

[5]Mackin J C,Hotek M.SQL Server 2005數(shù)據(jù)庫服務(wù)器架構(gòu)設(shè)計(jì)[M].北京:清華大學(xué)出版社,2007

[6]北京阿博泰克北大青鳥信息技術(shù)有公司數(shù)據(jù)庫應(yīng)用與性能優(yōu)化「M].北京:科學(xué)技術(shù)出版社,2009

[7]丁向朝.SQL Server數(shù)據(jù)庫查詢語句優(yōu)化的研究[J].華章.2012(15):269

[8]李紅麗.SQL Server數(shù)據(jù)庫的查詢優(yōu)化探析[J].長春教育學(xué)院學(xué)報(bào).2013,29(7):54,60

[9]魏琦，于林林，宋旭東.關(guān)系數(shù)據(jù)庫查詢優(yōu)化策略研究[J].電腦知識與技術(shù).2010,6(31):8845-8847

[10]荊立夏.關(guān)系數(shù)據(jù)庫的查詢優(yōu)化[J].鄭州航空工業(yè)管理學(xué)院學(xué)報(bào).2003,21(1):104-106

[11]張燕琴.基于SQL Server數(shù)據(jù)庫的查詢優(yōu)化[J].軟件導(dǎo)刊.2012,11(8):133-134