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

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

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

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

4、序。無論數(shù)據(jù)庫中記錄的數(shù)目或組 合是否隨時間變化而更改, 基于語法的查詢優(yōu)化器每次都執(zhí)行同樣的計劃。 與基于開銷的查詢優(yōu)化器不同, 它不查看或維護數(shù)據(jù)庫的統(tǒng)計記錄。“基于開銷的查詢優(yōu)化器”在備選計劃中選擇應(yīng)答 SQL 查詢的計劃。 選擇是基于對執(zhí)行特殊計劃的開銷估算(I/O 操作數(shù)、 CPU 秒數(shù),等等而作出的。它通過記錄表或索引 中記錄的數(shù)目和構(gòu)成的統(tǒng)計數(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)化之后

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

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

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

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

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

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

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

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

13、 or (或以及 not (非。 NOT 可用來對任何邏輯運算符號取反。下面 是一個 NOT 子句的例子:WHERE NOT (col =“ V ALID”NOT 運算符包含在另外一個邏輯運算符中,這就是不等于(<>運算符。換句話說,即使不在查詢 where 子句中顯式地加入 NOT 詞, NOT 仍在運算符中,見下例:SELECT * FROM table1 WHERE user_id<>3000;對這個查詢,可以改寫為不使用 NOT :SELECT * FROM table1 WHERE user_id <3000 OR user_id >3000;雖然

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

15、。5.7 在海量查詢時盡量少用格式轉(zhuǎn)換。5.8 IN 、 OR 子句常會使工作表的索引失效。如果不產(chǎn)生大量重復(fù)值,可以考慮把子句拆開,拆開的 子句中應(yīng)該包含索引。6. 使 用 臨 時 表 加 速 查 詢把表的一個子集進行排序并創(chuàng)建臨時表,有時能加速查詢。它有助于避免多重排序操作,而且在其他 方面還能簡化優(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 如果這個查詢要被執(zhí)行多次,可以把所有 sign>0的記錄找出來放在一個臨時文件中,并按 name 進行 排序:SELECT , table2.money , other columnsFROM table1, table2WHERE table1.user_id = table2.user_idAND table2.sign >0ORDER BY INTO TEMP temp_table然后以下面的方式在臨時表中查詢:SELECT * FROM temp_tableWHERE

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