大型數(shù)據(jù)庫(kù)的優(yōu)化查詢

1、結(jié)合索引優(yōu)化SQL語(yǔ)句提高數(shù)據(jù)庫(kù)查詢效率任立群（聊城市人民醫(yī)院信息科，山東聊城252000）摘要在一個(gè)應(yīng)用系統(tǒng)中，對(duì)數(shù)據(jù)查詢及處理速度已成為衡量該系統(tǒng)成敗的標(biāo)準(zhǔn)，所以在對(duì)大型數(shù)據(jù)庫(kù)查詢時(shí)，應(yīng)注意數(shù)據(jù)查詢的工作效率，以免造成系統(tǒng)資源嚴(yán)重浪費(fèi)。本文根據(jù)以語(yǔ)法為基礎(chǔ)的查詢優(yōu)化器的工作原理，合理建立索引，書寫規(guī)范良好的SQL語(yǔ)句，使用合適的表達(dá)式或關(guān)鍵字，充分利用索引，避免全表掃描，提高查詢效率。關(guān)鍵詞數(shù)據(jù)庫(kù)，優(yōu)化查詢，索引，SQL語(yǔ)句數(shù)據(jù)庫(kù)系統(tǒng)是管理信息系統(tǒng)的核心，基于數(shù)據(jù)庫(kù)的聯(lián)機(jī)事務(wù)處理（OLTP）和聯(lián)機(jī)分析處理（OLAP）是各使用單位最為重要的計(jì)算機(jī)應(yīng)用之一。從大多數(shù)系統(tǒng)的應(yīng)用實(shí)例來(lái)看，查詢、分

2、析、統(tǒng)計(jì)是系統(tǒng)的最終應(yīng)用，而查詢、分析、統(tǒng)計(jì)操作所基于的SELECT語(yǔ)句在SQL語(yǔ)句中又是付出資源代價(jià)最大的語(yǔ)句。舉個(gè)具體的例子，比如一個(gè)數(shù)據(jù)庫(kù)表有上百萬(wàn)甚至上千萬(wàn)條記錄，全表掃描一次往往需要數(shù)十分鐘，甚至數(shù)小時(shí)。如果采用比全表掃描更好的查詢策略，往往可以使查詢時(shí)間降為幾分鐘甚至幾秒鐘，由此可見(jiàn)查詢優(yōu)化技術(shù)的重要性。本文以應(yīng)用實(shí)例為基礎(chǔ)，結(jié)合數(shù)據(jù)庫(kù)操作（以MSSQLServer為例），介紹優(yōu)化查詢技術(shù)在現(xiàn)實(shí)系統(tǒng)中的運(yùn)用。1大型數(shù)據(jù)庫(kù)查詢的工作原理一個(gè)好的查詢計(jì)劃往往可以使程序性能提高數(shù)十倍。查詢計(jì)劃是用戶所提交的SQL語(yǔ)句的集合，查詢規(guī)劃是經(jīng)過(guò)優(yōu)化處理之后所產(chǎn)生的語(yǔ)句集合。DBMS（數(shù)據(jù)庫(kù)管理

3、系統(tǒng)）處理查詢計(jì)劃的過(guò)程是這樣的：在做完查詢語(yǔ)句的詞法、語(yǔ)法檢查之后，將語(yǔ)句提交給DBMS的查詢優(yōu)化器，優(yōu)化器做完代數(shù)優(yōu)化和存取路徑的優(yōu)化之后，由預(yù)編譯模塊對(duì)語(yǔ)句進(jìn)行處理并生成查詢規(guī)劃，然后提交給系統(tǒng)處理執(zhí)行，最后將執(zhí)行結(jié)果返回給用戶。在實(shí)際的數(shù)據(jù)庫(kù)產(chǎn)品（如MSSQLServer）的高版本中都是采用“基于語(yǔ)法的查詢優(yōu)化器”和“基于開銷的查詢優(yōu)化器”?！盎谡Z(yǔ)法的查詢優(yōu)化器”為獲得對(duì)SQL查詢的應(yīng)答結(jié)果創(chuàng)建一個(gè)過(guò)程計(jì)劃，但是它選擇的特定計(jì)劃取決于查詢的確切語(yǔ)法及查詢中的子句順序。無(wú)論數(shù)據(jù)庫(kù)中記錄的數(shù)目或組合是否隨時(shí)間變化而更改，基于語(yǔ)法的查詢優(yōu)化器每次都執(zhí)行同樣的計(jì)劃。與基于開銷的查詢優(yōu)化器不同

4、，它不查看或維護(hù)數(shù)據(jù)庫(kù)的統(tǒng)計(jì)記錄。“基于開銷的查詢優(yōu)化器”在備選計(jì)劃中選擇應(yīng)答SQL查詢的計(jì)劃。選擇是基于對(duì)執(zhí)行特殊計(jì)劃的開銷估算（I/O操作數(shù)、CPU秒數(shù)，等等）而作出的。它通過(guò)記錄表或索引中記錄的數(shù)目和構(gòu)成的統(tǒng)計(jì)數(shù)字估算這些開銷。與基于語(yǔ)法的查詢優(yōu)化器不同，它不依賴于查詢的確切語(yǔ)法或查詢中的子句順序。雖然現(xiàn)在的數(shù)據(jù)庫(kù)產(chǎn)品在查詢優(yōu)化方面已經(jīng)做得越來(lái)越好，但由用戶提交的SQL語(yǔ)句是系統(tǒng)優(yōu)化的基礎(chǔ)，很難設(shè)想一個(gè)原本糟糕的查詢語(yǔ)句經(jīng)過(guò)系統(tǒng)的優(yōu)化之后會(huì)變得高效，因此用戶所寫語(yǔ)句的優(yōu)劣至關(guān)重要。“基于開銷的查詢優(yōu)化器”的優(yōu)化方法我們暫不討論，下面重點(diǎn)說(shuō)明“基于語(yǔ)法的查詢優(yōu)化器”的解決方案。2合理建立索

5、引提高查詢效率索引是數(shù)據(jù)庫(kù)中重要的數(shù)據(jù)結(jié)構(gòu)，它的根本目的就是為了提高查詢效率。采用索引來(lái)加快數(shù)據(jù)處理速度也成為廣大數(shù)據(jù)庫(kù)用戶一致接受的優(yōu)化方法。索引的使用要恰到好處，其使用原則如下：1.1 在經(jīng)常進(jìn)行連接，但是沒(méi)有指定為外鍵的列上建立索引。1.2 在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roupby或orderby操作）的列上建立索引。1.3 在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如在雇員表的性別”列上只有男“與女”兩個(gè)不同值，因此就沒(méi)有必要建立索引。如果建立索引不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低更新速度。1.4 如果待排序的列有多個(gè)，可以在這些列上建立復(fù)合

6、索引(compoundindex)。1.5 不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中。也就是說(shuō)如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能。1.6 對(duì)查詢型的表，建立多個(gè)索引會(huì)大大提高查詢速度，對(duì)更新型的表，如果索引過(guò)多，會(huì)增大開銷。在實(shí)際應(yīng)用中可以使用系統(tǒng)工具幫助分析建立索引。如MSSQLServer的查詢分析器。避免或簡(jiǎn)化排序(orderby)應(yīng)當(dāng)簡(jiǎn)化或避免對(duì)大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動(dòng)以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時(shí)，優(yōu)化器就避免了排序的步驟。以下是一些影響因素：索引中不包括一個(gè)或幾個(gè)待排序的列；groupby或orderby子句中列的次序與索引的次序不一

7、樣；排序的列來(lái)自不同的表。為了避免不必要的排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫(kù)表(盡管有時(shí)可能影響表的規(guī)范化,但相對(duì)于效率的提高是值得的)。如果排序不可避免，那么應(yīng)當(dāng)試圖簡(jiǎn)化它，如縮小排序的列的范圍等。使用連接避免對(duì)數(shù)據(jù)表的順序存取在嵌套查詢中，對(duì)表的順序存取可能會(huì)對(duì)查詢效率產(chǎn)生致命的影響。比如采用順序存取策略，一個(gè)嵌套3層的查詢，如果每層都查詢1000行，那么這個(gè)查詢就要查詢10億行數(shù)據(jù)。避免這種情況的主要方法就是對(duì)連接的列進(jìn)行索引。例如，兩個(gè)表：學(xué)生表(學(xué)號(hào)、姓名、年齡)和選課表(學(xué)號(hào)、課程號(hào)、成績(jī))。如果兩個(gè)表要做連接，就要在學(xué)號(hào)”這個(gè)連接字段上建立索引。還可以使用并集來(lái)避免順序存

8、取。盡管在所有的檢查列上都有索引，但某些形式的where子句強(qiáng)迫優(yōu)化器使用順序存取。下面的查詢將強(qiáng)迫對(duì)table1表執(zhí)行順序操作：SELECT*FROMtableiWHERE(user_num=104ANDuser_id>1001)ORuser_id=1008雖然在user_num和user_id上建有索引，但是在上面的語(yǔ)句中優(yōu)化器還是使用順序存取路徑掃描整個(gè)表。因?yàn)檫@個(gè)語(yǔ)句要檢索的是分離的行的集合，所以應(yīng)該改為如下語(yǔ)句：SELECT*FROMtable1WHEREuser_num=104ANDuser_id>1001UNIONSELECT*FROMtable1WHEREuser_

9、id=1008這樣就能利用索引路徑處理查詢。5避免困難的正規(guī)表達(dá)式某些關(guān)鍵字的應(yīng)用是正確的，技術(shù)上叫正規(guī)表達(dá)式，但有時(shí)搭配不當(dāng)會(huì)非常耗費(fèi)時(shí)間，特別是在大型數(shù)據(jù)表中體現(xiàn)的尤為突出，我們把這種正規(guī)表達(dá)式稱為困難的正規(guī)表達(dá)式。支持通配符的CHARINDEX和LIKE關(guān)鍵字。例如：SELECT*FROMtable1WHEREuser_idLIKE"98”即使在user_id字段上建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語(yǔ)句改為SELECT*FROMtable1WHEREuser_id>“98000”在執(zhí)行查詢時(shí)就會(huì)利用索引來(lái)查詢，顯然會(huì)大大提高速度。比如查找用戶名包含有

10、“c”的所有用戶，可以用SELECT*FROMtablelWHEREuser_nameLIKE"c%下面是完成上面功能的另一種寫法：SELECT*FROMtablelWHERECHARINDEX（"c”usame）>0這種方法理論上比上一種方法多了一個(gè)判斷語(yǔ)句，即>0,但這個(gè)判斷過(guò)程是最快的，我想信80%以上的運(yùn)算都是花在查找字符串及其它的運(yùn)算上。用這種方法也有好處，那就是對(duì)"、等在不能直接用LIKE查找到的字符中可以直接在這CHARINDEX中運(yùn)用，如下：SELECT*FROMtablelWHERECHARINDEX（"%',u_n

11、ame）>0少使用例如語(yǔ)句：SELECTCOUNT（*）FROMtablel這時(shí)用“*”和一個(gè)實(shí)際的列名得到的都是一個(gè)行數(shù)的結(jié)果，但是用“*”會(huì)統(tǒng)計(jì)所有列，顯然要比用一個(gè)實(shí)際的列名效率慢。同樣，盡管很多開發(fā)人員都習(xí)慣采用SELECT*FROMTBL”的模式進(jìn)行查詢，但是為了提高系統(tǒng)的效率，如果你只需要其中某幾個(gè)字段的值的話，最好把這幾個(gè)字段直接寫出來(lái)。盡量不要在WHERE子句中對(duì)字段使用函數(shù)或參與表達(dá)式計(jì)算，這樣會(huì)導(dǎo)致無(wú)法使用索引進(jìn)行全表掃描。不要使用NOT。查詢時(shí)可以在WHERE子句使用一些邏輯表達(dá)式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）

12、。NOT可用來(lái)對(duì)任何邏輯運(yùn)算符號(hào)取反。下面是一個(gè)NOT子句的例子：WHERENOT（col=VALID”）NOT運(yùn)算符包含在另外一個(gè)邏輯運(yùn)算符中，這就是不等于（<>）運(yùn)算符。換句話說(shuō)，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運(yùn)算符中，見(jiàn)下例：SELECT*FROMtablelWHEREuser_id<>3000;對(duì)這個(gè)查詢，可以改寫為不使用NOT:SELECT*FROMtablelWHEREuser_id<3000ORuser_id>3000;雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會(huì)比第一種查詢方案更快些。第二種查詢?cè)试S對(duì)user_i

13、d列使用索引，而第一種查詢則不能使用索引。IN和EXISTS。EXISTS要遠(yuǎn)比IN的效率高，里面關(guān)系到fulltablescan和rangescan。同時(shí)應(yīng)盡可能使用NOTEXISTS來(lái)代替NOTIN,盡管二者都使用了NOT（不能使用索引而降低速度），但是NOTEXISTS要比NOTIN查詢效率更高。慎用游標(biāo)。在某些必須使用游標(biāo)的場(chǎng)合，可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時(shí)表中，再對(duì)臨時(shí)表定義游標(biāo)進(jìn)行操作，這樣可使性能得到明顯提高。在海量查詢時(shí)盡量少用格式轉(zhuǎn)換。IN、OR子句常會(huì)使工作表的索引失效。如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開，拆開的子句中應(yīng)該包含索引。6.使用臨時(shí)表加速查詢把表的一個(gè)

14、子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作。例如：SELECT,table2.money,othercolumnsFROMtablel,table2WHEREtable1.user_id=table2.user_idANDtable2.sign>0ANDtablel.num>“98000”O(jiān)RDERBY如果這個(gè)查詢要被執(zhí)行多次，可以把所有sign>0的記錄找出來(lái)放在一個(gè)臨時(shí)文件中，并按name進(jìn)行排序：SELECT,table2.money,other

15、columnsFROMtable1,table2WHEREtable1.user_id=table2.user_idANDtable2.sign>0ORDERBYINTOTEMPtemp_table然后以下面的方式在臨時(shí)表中查詢：SELECT*FROMtemp_tableWHEREnum>“98000”臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O,所以查詢工作量可以得到大幅減少。注意：臨時(shí)表創(chuàng)建后不會(huì)反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。7小結(jié)20%的代碼用去了80%的時(shí)間，這是程序設(shè)計(jì)中的一個(gè)著名定律，在數(shù)據(jù)庫(kù)應(yīng)用程序中也同樣如此。對(duì)于數(shù)據(jù)庫(kù)應(yīng)用程序來(lái)說(shuō)，重點(diǎn)在于SQL的執(zhí)行效率，所謂優(yōu)化的重點(diǎn)環(huán)節(jié)即WHERE子句利用了索引，不可優(yōu)化即發(fā)生了全表掃描或額外開銷。經(jīng)驗(yàn)顯示，SQLServer性能的最大改進(jìn)得益于邏輯的數(shù)據(jù)庫(kù)設(shè)計(jì)、索引設(shè)計(jì)和查詢?cè)O(shè)計(jì)方面。反過(guò)來(lái)說(shuō)，最大的性能降低問(wèn)題常常是由這些方面中的不足引起的。其實(shí)SQL優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識(shí)別的語(yǔ)句，充份利用索引，減少表掃描的I/O次數(shù)，盡量避免全表搜