李啟華

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

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

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A

0引言

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

1問(wèn)題產(chǎn)生的原因

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

2解決問(wèn)題的辦法

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

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

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

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

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

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

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

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

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

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

4結(jié)語(yǔ)

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

參考文獻(xiàn)

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