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

1、結(jié)合索引優(yōu)化 SQL 語(yǔ)句提高數(shù)據(jù)庫(kù)查詢(xún)效率任立群(聊城市人民醫(yī)院 信息科,山東 聊城 252000摘 要 在一個(gè)應(yīng)用系統(tǒng)中,對(duì)數(shù)據(jù)查詢(xún)及處理速度已成為衡量該系統(tǒng)成敗的標(biāo)準(zhǔn),所以在對(duì)大型數(shù) 據(jù)庫(kù)查詢(xún)時(shí),應(yīng)注意數(shù)據(jù)查詢(xún)的工作效率,以免造成系統(tǒng)資源嚴(yán)重浪費(fèi)。本文根據(jù)以語(yǔ)法為基礎(chǔ)的查詢(xún)優(yōu) 化器的工作原理, 合理建立索引, 書(shū)寫(xiě)規(guī)范良好的 SQL 語(yǔ)句, 使用合適的表達(dá)式或關(guān)鍵字, 充分利用索引, 避免全表掃描,提高查詢(xún)效率。關(guān)鍵詞 數(shù)據(jù)庫(kù),優(yōu)化查詢(xún),索引, 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)用之一

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

3、交的 SQL 語(yǔ)句的集合, 查詢(xún) 規(guī)劃是經(jīng)過(guò)優(yōu)化處理之后所產(chǎn)生的語(yǔ)句集合。 DBMS (數(shù)據(jù)庫(kù)管理系統(tǒng)處理查詢(xún)計(jì)劃的過(guò)程是這樣的:在做完查詢(xún)語(yǔ)句的詞法、 語(yǔ)法檢查之后, 將語(yǔ)句提交給 DBMS 的查詢(xún)優(yōu)化器, 優(yōu)化器做完代數(shù)優(yōu)化和存取 路徑的優(yōu)化之后,由預(yù)編譯模塊對(duì)語(yǔ)句進(jìn)行處理并生成查詢(xún)規(guī)劃,然后提交給系統(tǒng)處理執(zhí)行,最后將執(zhí)行 結(jié)果返回給用戶(hù)。在實(shí)際的數(shù)據(jù)庫(kù)產(chǎn)品 (如 MS SQLServer的高版本中都是采用“基于語(yǔ)法的查詢(xún)優(yōu)化器” 和“基于開(kāi)銷(xiāo)的查詢(xún)優(yōu)化器”?！盎谡Z(yǔ)法的查詢(xún)優(yōu)化器”為獲得對(duì) SQL 查詢(xún)的應(yīng)答結(jié)果創(chuàng)建一個(gè)過(guò)程 計(jì)劃,但是它選擇的特定計(jì)劃取決于查詢(xún)的確切語(yǔ)法及查詢(xún)中的子句順

4、序。無(wú)論數(shù)據(jù)庫(kù)中記錄的數(shù)目或組 合是否隨時(shí)間變化而更改, 基于語(yǔ)法的查詢(xún)優(yōu)化器每次都執(zhí)行同樣的計(jì)劃。 與基于開(kāi)銷(xiāo)的查詢(xún)優(yōu)化器不同, 它不查看或維護(hù)數(shù)據(jù)庫(kù)的統(tǒng)計(jì)記錄?！盎陂_(kāi)銷(xiāo)的查詢(xún)優(yōu)化器”在備選計(jì)劃中選擇應(yīng)答 SQL 查詢(xún)的計(jì)劃。 選擇是基于對(duì)執(zhí)行特殊計(jì)劃的開(kāi)銷(xiāo)估算(I/O 操作數(shù)、 CPU 秒數(shù),等等而作出的。它通過(guò)記錄表或索引 中記錄的數(shù)目和構(gòu)成的統(tǒng)計(jì)數(shù)字估算這些開(kāi)銷(xiāo)。與基于語(yǔ)法的查詢(xún)優(yōu)化器不同,它不依賴(lài)于查詢(xún)的確切語(yǔ) 法或查詢(xún)中的子句順序。 雖然現(xiàn)在的數(shù)據(jù)庫(kù)產(chǎn)品在查詢(xún)優(yōu)化方面已經(jīng)做得越來(lái)越好, 但由用戶(hù)提交的 SQL 語(yǔ)句是系統(tǒng)優(yōu)化的基礎(chǔ),很難設(shè)想一個(gè)原本糟糕的查詢(xún)語(yǔ)句經(jīng)過(guò)系統(tǒng)的優(yōu)化之后

5、會(huì)變得高效,因此用戶(hù)所 寫(xiě)語(yǔ)句的優(yōu)劣至關(guān)重要。“基于開(kāi)銷(xiāo)的查詢(xún)優(yōu)化器”的優(yōu)化方法我們暫不討論,下面重點(diǎn)說(shuō)明“基于語(yǔ)法 的查詢(xún)優(yōu)化器”的解決方案。2合 理 建 立 索 引 提 高 查 詢(xún) 效 率索引是數(shù)據(jù)庫(kù)中重要的數(shù)據(jù)結(jié)構(gòu),它的根本目的就是為了提高查詢(xún)效率。采用索引來(lái)加快數(shù)據(jù)處理速 度也成為廣大數(shù)據(jù)庫(kù)用戶(hù)一致接受的優(yōu)化方法。索引的使用要恰到好處,其使用原則如下:2.1 在經(jīng)常進(jìn)行連接,但是沒(méi)有指定為外鍵的列上建立索引。2.2 在頻繁進(jìn)行排序或分組(即進(jìn)行 group by或 order by操作的列上建立索引。2.3 在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引,在不同值少的列上不要建立索引

6、。比 如在雇員表的 “ 性別 ” 列上只有 “ 男 ” 與 “ 女 ” 兩個(gè)不同值,因此就沒(méi)有必要建立索引。如果建立索引不但不會(huì) 提高查詢(xún)效率,反而會(huì)嚴(yán)重降低更新速度。2.4 如果待排序的列有多個(gè),可以在這些列上建立復(fù)合索引(compound index。2.5 不能用 null 作索引,任何包含 null 值的列都將不會(huì)被包含在索引中。也就是說(shuō)如果某列存在 空值,即使對(duì)該列建索引也不會(huì)提高性能。2.6 對(duì)查詢(xún)型的表,建立多個(gè)索引會(huì)大大提高查詢(xún)速度,對(duì)更新型的表,如果索引過(guò)多,會(huì)增大開(kāi) 銷(xiāo)。在實(shí)際應(yīng)用中可以使用系統(tǒng)工具幫助分析建立索引。如 MS SQL Server的查詢(xún)分析器。3 避 免 或

7、 簡(jiǎn) 化 排 序 (order by應(yīng)當(dāng)簡(jiǎn)化或避免對(duì)大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動(dòng)以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時(shí),優(yōu)化器就 避免了排序的步驟。以下是一些影響因素:3.1 索引中不包括一個(gè)或幾個(gè)待排序的列;3.2 group by或 order by子句中列的次序與索引的次序不一樣;3.3 排序的列來(lái)自不同的表。為了避免不必要的排序, 就要正確地增建索引, 合理地合并數(shù)據(jù)庫(kù)表 (盡管有時(shí)可能影響表的規(guī)范化, 但相對(duì)于效率的提高是值得的。如果排序不可避免,那么應(yīng)當(dāng)試圖簡(jiǎn)化它,如縮小排序的列的范圍等。 4 使 用 連 接 避 免 對(duì) 數(shù) 據(jù) 表 的 順 序 存 取在嵌套查詢(xún)中,對(duì)表的順序存取可能會(huì)

8、對(duì)查詢(xún)效率產(chǎn)生致命的影響。比如采用順序存取策略,一個(gè)嵌 套 3層的查詢(xún),如果每層都查詢(xún) 1000行,那么這個(gè)查詢(xún)就要查詢(xún) 10億行數(shù)據(jù)。避免這種情況的主要方法 就是對(duì)連接的列進(jìn)行索引。例如,兩個(gè)表:學(xué)生表(學(xué)號(hào)、姓名、年齡 和選課表(學(xué)號(hào)、課程號(hào)、 成績(jī) 。 如果兩個(gè)表要做連接, 就要在 “ 學(xué)號(hào) ” 這個(gè)連接字段上建立索引。 還可以使用并集來(lái)避免順序存取。 盡管在所有的檢查列上都有索引,但某些形式的 where 子句強(qiáng)迫優(yōu)化器使用順序存取。下面的查詢(xún)將強(qiáng)迫 對(duì) table1表執(zhí)行順序操作:SELECT * FROM table1 WHERE (user_num=104 AND user_id

9、>1001 OR user_id =1008雖然在 user_num和 user_id上建有索引,但是在上面的語(yǔ)句中優(yōu)化器還是使用順序存取路徑掃描整個(gè) 表。因?yàn)檫@個(gè)語(yǔ)句要檢索的是分離的行的集合,所以應(yīng)該改為如下語(yǔ)句:SELECT * FROM table1 WHERE user_num =104 AND user_id>1001UNIONSELECT * FROM table1 WHERE user_id=1008這樣就能利用索引路徑處理查詢(xún)。5 避 免 困 難 的 正 規(guī) 表 達(dá) 式某些關(guān)鍵字的應(yīng)用是正確的,技術(shù)上叫正規(guī)表達(dá)式,但有時(shí)搭配不當(dāng)會(huì)非常耗費(fèi)時(shí)間,特別是在大型 數(shù)據(jù)表中

10、體現(xiàn)的尤為突出,我們把這種正規(guī)表達(dá)式稱(chēng)為困難的正規(guī)表達(dá)式。5.1 支持通配符的 CHARINDEX 和 LIKE 關(guān)鍵字。例如:SELECT * FROM table1 WHERE user_id LIKE “98_ _ _”即使在 user_id字段上建立了索引,在這種情況下也還是采用順序掃描的方式。如果把語(yǔ)句改為 SELECT * FROM table1 WHERE user_id >“98000”在執(zhí)行查詢(xún)時(shí)就會(huì)利用索引來(lái)查詢(xún),顯然會(huì)大大提高速度。比如查找用戶(hù)名包含有“ c ”的所有用戶(hù),可以用SELECT * FROM table1 WHERE user_name LIKE “%

11、c%”下面是完成上面功能的另一種寫(xiě)法:SELECT * FROM table1 WHERE CHARINDEX (“c”user _name>0這種方法理論上比上一種方法多了一個(gè)判斷語(yǔ)句,即 >0, 但這個(gè)判斷過(guò)程是最快的, 我想信 80%以 上的運(yùn)算都是花在查找字符串及其它的運(yùn)算上。 用這種方法也有好處, 那就是對(duì) “ %” 、 “ |” 等在不能直接用 LIKE 查找到的字符中可以直接在這 CHARINDEX 中運(yùn)用, 如下:SELECT * FROM table1 WHERE CHARINDEX (“%”,user _name>05.2 少使用“ *”。例如語(yǔ)句:SEL

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

13、 or (或以及 not (非。 NOT 可用來(lái)對(duì)任何邏輯運(yùn)算符號(hào)取反。下面 是一個(gè) NOT 子句的例子:WHERE NOT (col =“ V ALID”NOT 運(yùn)算符包含在另外一個(gè)邏輯運(yùn)算符中,這就是不等于(<>運(yùn)算符。換句話(huà)說(shuō),即使不在查詢(xún) where 子句中顯式地加入 NOT 詞, NOT 仍在運(yùn)算符中,見(jiàn)下例:SELECT * FROM table1 WHERE user_id<>3000;對(duì)這個(gè)查詢(xún),可以改寫(xiě)為不使用 NOT :SELECT * FROM table1 WHERE user_id <3000 OR user_id >3000;雖然

14、這兩種查詢(xún)的結(jié)果一樣,但是第二種查詢(xún)方案會(huì)比第一種查詢(xún)方案更快些。第二種查詢(xún)?cè)试S對(duì) user_id列使用索引,而第一種查詢(xún)則不能使用索引。5.5 IN 和 EXISTS 。 EXISTS 要遠(yuǎn)比 IN 的效率高,里面關(guān)系到 full table scan和 range scan。 同時(shí)應(yīng)盡 可能使用 NOT EXISTS來(lái)代替 NOT IN, 盡管二者都使用了 NOT (不能使用索引而降低速度 , 但是 NOT EXISTS 要比 NOT IN查詢(xún)效率更高 。5.6 慎用游標(biāo)。在某些必須使用游標(biāo)的場(chǎng)合,可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時(shí)表中,再對(duì)臨時(shí)表 定義游標(biāo)進(jìn)行操作,這樣可使性能得到明顯提高

15、。5.7 在海量查詢(xún)時(shí)盡量少用格式轉(zhuǎn)換。5.8 IN 、 OR 子句常會(huì)使工作表的索引失效。如果不產(chǎn)生大量重復(fù)值,可以考慮把子句拆開(kāi),拆開(kāi)的 子句中應(yīng)該包含索引。6. 使 用 臨 時(shí) 表 加 速 查 詢(xún)把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表,有時(shí)能加速查詢(xún)。它有助于避免多重排序操作,而且在其他 方面還能簡(jiǎn)化優(yōu)化器的工作。例如:SELECT , table2.money , other columnsFROM table1, table2WHERE table1.user_id = table2.user_idAND table2.sign>0AND table1.num

16、>“98000”O(jiān)RDER BY 如果這個(gè)查詢(xún)要被執(zhí)行多次,可以把所有 sign>0的記錄找出來(lái)放在一個(gè)臨時(shí)文件中,并按 name 進(jìn)行 排序:SELECT , table2.money , other columnsFROM table1, table2WHERE table1.user_id = table2.user_idAND table2.sign >0ORDER BY INTO TEMP temp_table然后以下面的方式在臨時(shí)表中查詢(xún):SELECT * FROM temp_tableWHERE

17、num >“98000”臨時(shí)表中的行要比主表中的行少,而且物理順序就是所要求的順序,減少了磁盤(pán) I/O,所以查詢(xún)工作 量可以得到大幅減少。注意:臨時(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ā)生了全表掃描或額外開(kāi)銷(xiāo)。 經(jīng)驗(yàn)顯示, SQL Server性能的最大改進(jìn)得益于邏輯的數(shù)據(jù)庫(kù)設(shè)計(jì)、 索引設(shè)計(jì)和查詢(xún)?cè)O(shè)計(jì)方面。反過(guò)來(lái)說(shuō),最大的性能降低問(wèn)題常常是由這些方面中的不足引起的。其實(shí) SQL 優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下, 用優(yōu)化器可以識(shí)別的語(yǔ)句, 充份利用索引, 減少表掃描的 I/O次數(shù), 盡量避免全表搜索的發(fā)生。 其實(shí) SQ