論數(shù)據(jù)庫訪問中的查詢優(yōu)化

李啟華

摘 要 本文講述了在日常的數(shù)據(jù)庫應用開發(fā)中如何對查詢語句進行優(yōu)化以達到改進程序執(zhí)行效率的目的，文中列舉7種最常用的優(yōu)化規(guī)則以及附測試環(huán)境中的執(zhí)行耗時數(shù)據(jù)比較，印證幾條優(yōu)化規(guī)則的有效性。

關鍵詞 SQL 查詢優(yōu)化 查詢語句 效率

中圖分類號：TP311 文獻標識碼：A

0引言

小關是我大學時期的師弟，有一天他打電話給我告訴我他自己寫的一個數(shù)據(jù)庫應用程序剛開始投產時運行很流暢，但在使用一段時間后卻變得很慢，客戶進行了投訴，他檢查過客戶的機器并沒有中病毒，機器系統(tǒng)資源充足而且程序也沒有什么問題，他想了很久也找不到解決辦法。我了解到出問題的程序是一個倉庫管理程序，客戶每天都需要導入大量的數(shù)據(jù)到系統(tǒng)中并經常要進行一些數(shù)據(jù)查詢的操作，根據(jù)這些情況q 一下子便想到了問題所在--隨著應用系統(tǒng)數(shù)據(jù)量增多而逐漸突顯出來的數(shù)據(jù)查詢效率問題。

1問題產生的原因

數(shù)據(jù)庫查詢的效率問題是在應用開發(fā)中最容易被忽略的部分，由于應用程序開發(fā)時期系統(tǒng)測試數(shù)據(jù)庫庫數(shù)據(jù)量往往較少，查詢結果的返回時間往往較短，不容易凸顯出因數(shù)據(jù)查詢效率而導致的程序運行過慢的問題。但是當應用程序投產使用后隨著數(shù)據(jù)庫數(shù)據(jù)量的日益增多，數(shù)據(jù)查詢的耗時將會呈幾何級的增長，這樣將大大影響程序的運行速度，成為程序中的一大瓶頸。

2解決問題的辦法

同一查詢結果的數(shù)據(jù)庫查詢語句寫法千百，但各語句的執(zhí)行效率卻不同，在壞的查詢語句耗費掉幾分鐘的查詢時間的同時好的查詢語句卻只需要幾十秒的查詢時間，所以在進行應用程序開發(fā)的時候是有必要進行數(shù)據(jù)查詢語句的優(yōu)化，以保證程序的持續(xù)運行效率。

3查詢優(yōu)化的幾種常用規(guī)則

以下是我在日常應用程序開發(fā)中總結出來的最常用的幾條優(yōu)化規(guī)則，并附部分本人親自在數(shù)據(jù)庫環(huán)境下測試耗時數(shù)據(jù)，供大家參考：

（1）在查詢語句中避免使用“select *”語句，只返回必要的結果項，去除不必要的結果項。在查詢語句中應只返回必要的結果項，如在一個應用中只需要數(shù)據(jù)表中的field1及field2字段，則在查詢語句應寫成“select field1，field2”，另外“select *”這種懶惰的寫法應該摒棄掉。在一個數(shù)據(jù)量為50萬，數(shù)據(jù)項為20的數(shù)據(jù)表中使用“select *”寫法查詢耗時為6500毫秒，而使用“select field1”只查詢其中的一項的查詢耗時為900毫秒，后者比前者縮短了6倍多。

（2） 在查詢語句的搜索條件中將最具限制性的條件放在最前面，盡量在最前面的搜索條件中減少搜索得到的結果，例如在一個含有日期的數(shù)據(jù)表中要搜索“1995-01-01”到“2013-05-31”日期內的數(shù)據(jù)，而數(shù)據(jù)表中的日期記錄大多在2013年到2016年之間，那么將“dateField<='2013-05-31'”條件放在“dateField>='1995-01-01'”條件之前會大大縮短搜索的時長。因為查詢應用執(zhí)行查詢語句時是按由左至右的順序執(zhí)行的，前面的查詢條件若可大量減少搜索得的結果將大大減少后面查詢條件的搜索量，從而縮短查詢時間。

（3） 查詢條件的排列順序應該應該與索引字段的順序盡量保持一致，例如表table1中的索引字段按順序為field1、field2、field3，那么在搜索條件中若出現(xiàn)兩個以上索引字段，則搜索條件應按索引順序由左至右排列，可以大大地增強查詢的效率。因為數(shù)據(jù)表在建立索引時表里的數(shù)據(jù)也是按索引的排列順序排列的，查詢條件按索引字段順序排列可讓數(shù)據(jù)庫引擎更快地找到所需的數(shù)據(jù)。

（4）在查詢條件中盡量使用“>=”和“<=”運算符來代替“>”和“<”運算符，例如在一個含有日期的數(shù)據(jù)表中要搜索日期大于2015-01-01的數(shù)據(jù)，則使用查詢條件“dateField>='1995-01-02'”要比查詢條件“dateField>'1995-01-01'”耗時要短，因為“>=” 和“<=”運算符可以快速地定位索引。在一個數(shù)據(jù)量為50萬的數(shù)據(jù)表中使用“>”運算符查詢耗時為100毫秒，而使用“>=”運算符查詢耗時為75毫秒，后者比前者耗時縮短了25%。

（5）查詢條件中盡量不要使用函數(shù)，例如你在一個帶交易金額的數(shù)據(jù)表中要查詢交易金額除以4后等于50的數(shù)據(jù)，那么直觀的查詢條件為“amountField/4=50”，但是這種查詢條件非常的耗時，因為它會導致查詢程序不使用索引，此時你應該機智地轉換一下寫法，將查詢條件改為“amountField=200”，這樣將大大縮短查詢的時間。在一個數(shù)據(jù)量為130萬的數(shù)據(jù)表中使用“/”函數(shù)查詢耗時為840毫秒，而經轉換后直接使用“=”運算符查詢耗時為78毫秒，后者比前者耗時縮短了66%。

（6）查詢條件中若需要使用like運算符應盡量避免在查詢關鍵字前使用通配符“%”，例如你需要在一個含有卡片號的數(shù)據(jù)表中查詢含有“13654””的所有記錄，你可能會直觀地使用“cardNoField like '%13654%'”這種表達式，但這種表達式中的第一個通配符會令搜索程序不使用索引，從而降低查詢效率，如果你對數(shù)據(jù)表中的數(shù)據(jù)進行分析后可以確認“13654”只出現(xiàn)在字段的開頭，那么使用“cardNoField like '13654%' ”這種表達式將大大提升效率。在一個數(shù)據(jù)量為50萬的數(shù)據(jù)表中使用“l(fā)ike '%data%'”表達式查詢耗時為4200毫秒，而若使用“l(fā)ike 'data%'”表達式查詢耗時為3900毫秒，后者比前者耗時縮短了7%。

（7）在往數(shù)據(jù)表插入數(shù)據(jù)時，盡量不要插入空值（NULL）到字段中，應該插入一個約定的默認值，因為空值（NULL）在數(shù)據(jù)表中不存儲到索引中，這樣若需要查詢出這些空值的數(shù)據(jù)時將大大增加查詢的耗時。在一個數(shù)據(jù)量為10萬的數(shù)據(jù)表需要查詢出所有“field1 is null”的數(shù)據(jù)耗時為1150毫秒，而將這些null的數(shù)據(jù)改為一個約定的默認值后再查詢出這個默認值的記錄耗時為1040，后者比前者耗時縮短了10%。

4結語

以上便是我對數(shù)據(jù)庫查詢優(yōu)化的幾個歸納及建議，希望能對大家的應用開發(fā)提供參考及幫助，同時亦歡迎大家對我文章中存在的問題及不足提出建議及指正。

參考文獻

[1] 魏銀珍，陳征兵.SQL Server數(shù)據(jù)庫的查詢優(yōu)化策略研究[J].電腦知識與技術，2011，07（29）.