論數據庫訪問中的查詢優化

李啟華

摘 要 本文講述了在日常的數據庫應用開發中如何對查詢語句進行優化以達到改進程序執行效率的目的，文中列舉7種最常用的優化規則以及附測試環境中的執行耗時數據比較，印證幾條優化規則的有效性。

關鍵詞 SQL 查詢優化 查詢語句 效率

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

0引言

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

1問題產生的原因

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

2解決問題的辦法

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

3查詢優化的幾種常用規則

以下是我在日常應用程序開發中總結出來的最常用的幾條優化規則，并附部分本人親自在數據庫環境下測試耗時數據，供大家參考：

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

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

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

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

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

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

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

4結語

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

參考文獻

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