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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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