數(shù)據(jù)庫管理系統(tǒng)中查詢優(yōu)化

1、數(shù)據(jù)庫管理系統(tǒng)中查詢優(yōu)化摘要：查詢在數(shù)據(jù)庫操作中有著廣泛的應(yīng)用，查詢 語句的優(yōu)劣直接影響數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能。隨著數(shù)據(jù)庫技 術(shù)越來越廣泛的應(yīng)用，理解關(guān)系數(shù)據(jù)查詢優(yōu)化的方法，書寫 合理高效的查詢語句，可以使數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能得到很 大程度的提升，進而提高人們的工作效率。本文主要討論了 sql查詢語句的優(yōu)化，從而指出了查詢優(yōu)化在數(shù)據(jù)庫管理系 統(tǒng)中的重要作用及優(yōu)化技巧。關(guān)鍵詞：關(guān)系數(shù)據(jù)庫；查詢優(yōu)化；sql語句引言查詢優(yōu)化是數(shù)據(jù)庫管理系統(tǒng)設(shè)計和實現(xiàn)所采用的一項 重要技術(shù)，也是影響數(shù)據(jù)庫性能的關(guān)鍵因素。為了優(yōu)化數(shù)據(jù) 庫的性能，除了在數(shù)據(jù)庫的物理設(shè)計，關(guān)系規(guī)范化等方面進 行改進外，還有一個簡單有效的方法

2、就是通過設(shè)計高效合理 的查詢計劃提高查詢響應(yīng)速度。數(shù)據(jù)庫管理系統(tǒng)處理查詢計劃的方式是：在做完查詢語 句的詞法、語法檢查之后，將語句提交給數(shù)據(jù)庫管理系統(tǒng)的 查詢優(yōu)化器進行代數(shù)優(yōu)化和存取路徑的優(yōu)化，之后由預(yù)編譯 模塊處理語句并生成查詢規(guī)劃，然后在合適的時間交由系統(tǒng) 處理執(zhí)行，最后將執(zhí)行結(jié)果返回給用戶。sql語句是對數(shù)據(jù) 庫進行操作的唯一途徑，應(yīng)用程序的執(zhí)行最終要歸結(jié)為sql 語句的執(zhí)行，sql語句的效率對數(shù)據(jù)庫系統(tǒng)的性能起到了決 定性作用。因此對查詢語句的優(yōu)化是提高查詢效率的根本。一、sql查詢語句的優(yōu)化策略影響數(shù)據(jù)庫系統(tǒng)性能的因素很多，包括數(shù)據(jù)庫連接方 式、應(yīng)用系統(tǒng)架構(gòu)、數(shù)據(jù)庫設(shè)計、管理等。其中

3、最本質(zhì)又至 關(guān)重要的是數(shù)據(jù)庫管理系統(tǒng)本身的查詢優(yōu)化技術(shù)。通過分析 關(guān)系代數(shù)表達式的等價變換準(zhǔn)則及查詢代價，給定的sql查 詢與關(guān)系代數(shù)表達式對應(yīng)關(guān)系，研究出基于關(guān)系代數(shù)等價變 換規(guī)則的sql查詢優(yōu)化。此外，優(yōu)化器的優(yōu)化策略還與所查 詢表的內(nèi)容和其他一些與服務(wù)器有關(guān)的因素有關(guān)。因此對優(yōu) 化器來講，最為重要的選擇就是使用什么索引和采用何種表 的連接手段。如果用戶提交的sql語句和系統(tǒng)的索引體系是 一個不合理的查詢計劃，僅僅經(jīng)過系統(tǒng)優(yōu)化器的優(yōu)化，根本 不可能變得高效。因此用戶應(yīng)合理適當(dāng)?shù)慕⑺饕w系。二、sql查詢的優(yōu)化方法 設(shè)計高性能的sql語句的前提是：熟悉你所用的優(yōu)化器的優(yōu)化策略并深入理解數(shù)據(jù)

4、庫中的數(shù)據(jù)，以及透徹的分析用 戶的需求。在此基礎(chǔ)上，才能嘗試編寫效率最高的sql語句, 優(yōu)化程序設(shè)計。以下所舉實例中使用的數(shù)據(jù)表來自工作實際 中的學(xué)生信息表（students ）、教師信息表（teachers）以 及學(xué)生成績表（sc）。1 合理使用常規(guī)優(yōu)化查詢觀察下面兩組查詢語句，它們查詢的結(jié)果都是一樣，但 是查詢的速度卻有著明顯的區(qū)別。第 1 組：select*from teachers where salary/25 =1000第 2 組：select*fr0m teachers where salary =1000 * 25第2組的語句，在經(jīng)過了優(yōu)化后，查詢的速度減少了數(shù) 十倍。因為，數(shù)

5、據(jù)庫優(yōu)化器在面對第2組的sa.lary=1000* 25 時，會轉(zhuǎn)換成salary=25000進行查詢，卻不能將第1組中 的salary/251000進行轉(zhuǎn)換。鑒于此，應(yīng)盡量寫用常量跟 字段比較檢索的表達式，而不要將字段置于表達式當(dāng)中，否 則就沒有辦法進行優(yōu)化。2.拆分子句優(yōu)化查詢使用了 in, or子句的查詢語句，常會使工作表中索引 失效。在不會產(chǎn)生大量重復(fù)值的前提下，可以考慮把子句拆 分開，要求拆分的子句中應(yīng)該包含索引。在where子句中的 “in”在邏輯上相當(dāng)于” 0r”，所以數(shù)據(jù)庫優(yōu)化器會將sno in (' 01' ,'02')轉(zhuǎn)化為 sno二'

6、;01' or sno二'02'來執(zhí)行。這里采用了 “or策略”，就是將滿足每個0r子句 的行取出，存入臨時數(shù)據(jù)庫的工作表中，再建立唯一索引去 掉重復(fù)行，最后從這個臨時表中計算結(jié)果。因此，實際過程 中并沒有利用sno的索引，這樣還會因tempdb數(shù)據(jù)庫性能 的不同而影響查詢時間。假設(shè)在一個表table中有二十萬行，其中no字段上有 非群集索引，則執(zhí)行下面這條語句的速度將會非常慢：select*from table where no in (' o' , '1')當(dāng)這個表中的行越來越多，速度也將越來越慢。如果我 們將這個or子句分開，變成：

7、select*from table where no二'o'select*from table where no= '1'然后將得到的結(jié)果進行uinio運算，執(zhí)行的時間只需短 短數(shù)秒，因為這里的每句都使用了索引，由此提高了查詢速 度。3使用存儲過程優(yōu)化查詢存儲過程是用sql語句和數(shù)據(jù)庫編程語言編寫的，完成 一定的數(shù)據(jù)訪問功能或提供一定的服務(wù)過程，預(yù)先進行編譯 和優(yōu)化后，存儲在服務(wù)器中，客戶程序可以通過遠程調(diào)用的 模式調(diào)用它們，因此在執(zhí)行時不需要將應(yīng)用程序代碼向服務(wù) 器端傳送，可以大大減輕網(wǎng)絡(luò)負載。同時，由于存儲過程已 編譯為可執(zhí)行代碼，不需要每次執(zhí)行時進行分析和

8、優(yōu)化工 作，從而減少了預(yù)處理的時間，提高了效率。另一方面，使 用存儲過程還易于維護，且表的結(jié)構(gòu)改變時，不影響客戶端 的應(yīng)用程序。4.通過查詢語句優(yōu)化提高查詢速度避免使用不兼容的數(shù)據(jù)類型查詢的問題之一是在where子句中對有不同類型的列的 比較企圖。例如 float 和 int； char 和 v ar char； binar y 和 varbinary是不兼容的。因此要求where子句中表達式的數(shù) 據(jù)類型是兼容的，數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行 一些本來可以進行的優(yōu)化操作。避免或簡化排序應(yīng)當(dāng)簡化或避免對海量數(shù)據(jù)表進行重復(fù)的排序。當(dāng)能夠 利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時，優(yōu)化器就避免了排

9、 序的步驟。以下是一些影響因素，索引中不包括一個或幾個 待排序的列；group by或order by子句中列的次序與索引 的次序不一樣；排序的列來自不同的表。為了避免不必要的 排序，就要正確地增建索引，合理地合并數(shù)據(jù)庫表（盡管有 時可能影響表的規(guī)范化，但相對于效率的提高是值得的）。 如果排序不可避免，那么應(yīng)當(dāng)試圖簡化它，如縮小排序的列 的范圍等。避免相關(guān)子查詢?nèi)糇硬樵冎幸猛鈱硬樵兊闹?，就?gòu)成相關(guān)子查詢。執(zhí) 行時就不能象無關(guān)子查詢那樣，先單獨處理子查詢，再處理 外層查詢，而需要外層查詢和子查詢交叉進行。先用外層查 詢定位一行數(shù)據(jù)，這樣子查詢的引用就有了確定值，然后執(zhí) 行子查詢，得到結(jié)果后判定

10、外層查詢定位的那行數(shù)據(jù)是否滿 足條件，再取下一條記錄，再執(zhí)行子查詢直到外層查詢涉及的數(shù)據(jù)全部檢查完為止。例如一個列的標(biāo)簽同時在主查 詢和where子句中的查詢中出現(xiàn)，那么很可能當(dāng)主查詢中的 列值改變之后，子查詢必須重新查詢一次。這樣的查詢嵌套 層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。三、結(jié)論以上所述只是對sql查詢進行優(yōu)化的方法，查詢優(yōu)化的重點環(huán)節(jié)是使得數(shù)據(jù)庫服務(wù)器少從磁盤中讀數(shù)據(jù),同時盡量避免非順序讀頁。要想實現(xiàn)sql的性能優(yōu)化，還需要深人研 究數(shù)據(jù)庫層的資源配置、網(wǎng)絡(luò)層的流量控制以及操作系統(tǒng)層 的總體設(shè)計等。為了能更好地實現(xiàn)數(shù)據(jù)庫應(yīng)用系統(tǒng)優(yōu)化，平 時需要多積累一些sql語句的優(yōu)化技巧，充分利用硬件性能 和軟件方法來解決。參考文獻1 劉亞欣數(shù)據(jù)庫查詢優(yōu)化技術(shù)研究及其應(yīng)用d大連理工大學(xué)，2006, (12)2 郭玉棟、左金