簡論關(guān)系數(shù)據(jù)庫的查詢優(yōu)化

沈海峰

（安徽廣播電視大學(xué)，安徽合肥 230022）

簡論關(guān)系數(shù)據(jù)庫的查詢優(yōu)化

沈海峰

（安徽廣播電視大學(xué)，安徽合肥 230022）

數(shù)據(jù)庫的應(yīng)用日益廣泛，隨著數(shù)據(jù)量的增大，查詢效率越來越受人們關(guān)注。為了提高查詢的效率，在考慮處理器的速度、I／O速度、存儲器的容量、操作系統(tǒng)、采取何種的數(shù)據(jù)庫服務(wù)系統(tǒng)等方面的同時，對于特定服務(wù)器來說查詢的效率主要取決于DBA（數(shù)據(jù)庫管理員）所給定的查詢語句的優(yōu)化。

關(guān)系數(shù)據(jù)庫；查詢優(yōu)化；分析

隨著計算機(jī)應(yīng)用技術(shù)的不斷普及和發(fā)展，數(shù)據(jù)庫系統(tǒng)正越來越多的走進(jìn)人們的日常生活。在要求查詢結(jié)果正確無誤的同時，人們越來越關(guān)心查詢的效率問題。影響查詢效率的因素很多，諸如處理器的速度、I／O速度、存儲器的容量、操作系統(tǒng)、采取何種的數(shù)據(jù)庫服務(wù)系統(tǒng)等。但是對于特定服務(wù)器來說查詢的效率主要取決于DBA（數(shù)據(jù)庫管理員）所給定的查詢語句。就目前使用最為廣泛的關(guān)系數(shù)據(jù)庫而言，查詢優(yōu)化主要是針對SELECT語句的優(yōu)化。

1 數(shù)據(jù)庫的分類

數(shù)據(jù)模型是數(shù)據(jù)庫系統(tǒng)的核心和基礎(chǔ)，不同的數(shù)據(jù)庫具有不同的功能和數(shù)據(jù)模型。按照數(shù)據(jù)模型的特點可以將傳統(tǒng)數(shù)據(jù)庫系統(tǒng)分成網(wǎng)狀數(shù)據(jù)庫、層次數(shù)據(jù)庫、關(guān)系數(shù)據(jù)庫、面向?qū)ο髷?shù)據(jù)庫4類［1］。網(wǎng)狀模型：在網(wǎng)狀模型中，關(guān)系稱為“集”。每個集包括至少兩種記錄類型，一個相當(dāng)于層次模型中的父代的主記錄，一個相當(dāng)于層次模型中的子代的成員記錄。網(wǎng)狀模型的數(shù)據(jù)庫，對于尋找附屬于指定的對象的一組記錄時，效率非常高。層次模型：IBM為其使用的D／L語言的IMS大型數(shù)據(jù)庫系列產(chǎn)品開發(fā)了層次模型，開發(fā)層次模型是為了模擬現(xiàn)實生活中各種分層組織。對于描述一種簡單的“樹”型結(jié)構(gòu)，層次模型非常合適，并且這種模型對于包含大量數(shù)據(jù)的數(shù)據(jù)庫來說，效率很高。關(guān)系模型：關(guān)系模型的主要特點是表中的記錄由屬性之間的關(guān)系來進(jìn)行連接，在保證數(shù)據(jù)集之間的邏輯關(guān)系表達(dá)的同時，保持?jǐn)?shù)據(jù)集之間的獨立性。在關(guān)系模型中，數(shù)據(jù)存儲在由行和列組成的表中。使用關(guān)系數(shù)據(jù)庫模型可以節(jié)省程序員的時間，以便將注意力盡量放在數(shù)據(jù)庫的邏輯框架上，而不需要在物理框架方面花費太多精力。支持關(guān)系模型的數(shù)據(jù)庫系統(tǒng)稱為關(guān)系數(shù)據(jù)庫，它是目前最為成熟、使用最為廣泛的數(shù)據(jù)庫類型。目前在互聯(lián)網(wǎng)上使用的半結(jié)構(gòu)或非結(jié)構(gòu)化數(shù)據(jù)可以通過一定的轉(zhuǎn)化過程映射到關(guān)系數(shù)據(jù)庫。面向?qū)ο竽Ｐ停好嫦驅(qū)ο蟮臄?shù)據(jù)模型提供了一種類層次結(jié)構(gòu)。在面向?qū)ο髷?shù)據(jù)庫模式中，一組類可以形成一個類層次。一個面向?qū)ο髷?shù)據(jù)庫可能有多個類層次。在一個類層次中，一個類繼承其所有超類的全部屬性、方法和消息。面向?qū)ο蟮臄?shù)據(jù)庫系統(tǒng)在邏輯上和物理上從面向記錄上升為面向?qū)ο蟆⒚嫦蚩删哂袕?fù)雜結(jié)構(gòu)的一個邏輯整體。允許用自然的方法，并結(jié)合數(shù)據(jù)抽象機(jī)制在結(jié)構(gòu)和行為上對復(fù)雜對象建立模型，從而大幅度提高管理效率，降低用戶使用復(fù)雜性。

2 關(guān)系數(shù)據(jù)庫查詢優(yōu)化

2．1 查詢優(yōu)化的必要性

舉例來說，如果一個數(shù)據(jù)庫表信息積累到上百萬甚至是上千萬條記錄，全表掃描一次需要數(shù)十分鐘，甚至數(shù)小時；但如果采用比全表掃描更好的查詢策略，往往可以使查詢時間降為幾分鐘，由此可見查詢優(yōu)化技術(shù)的重要性。

2．2 查詢優(yōu)化的原理

查詢優(yōu)化一般可分為代數(shù)優(yōu)化和物理優(yōu)化，代數(shù)優(yōu)化是指關(guān)系代數(shù)表達(dá)式的優(yōu)化；物理優(yōu)化則是指存取路徑和低層操作算法的選擇。針對關(guān)系數(shù)據(jù)庫，查詢優(yōu)化的總體目標(biāo)是：選擇有效的優(yōu)化策略，計算給定關(guān)系表達(dá)式的值，使得查詢的執(zhí)行代價最小。在集中式數(shù)據(jù)庫中，查詢的執(zhí)行代價＝I／O代價＋CPU代價＋內(nèi)存代價，在粗略計算執(zhí)行代價時，主要指標(biāo)是I／O代價，CPU代價與內(nèi)存代價可不考慮。因此，查詢優(yōu)化的目的主要就在于盡可能地減少I／O操作的次數(shù)［2］。

2．3 查詢優(yōu)化的方法

2．3．1 有效利用索引

在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。在頻繁進(jìn)行排序或分組的列上建立索引。在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就無必要建立索引。假如建立索引不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。假如待排序的列有多個，可以在這些列上建立復(fù)合索引。下面給出一些通用的規(guī)則［4］：1）在查詢頻率較高或經(jīng)常用作過濾條件的字段上建立索引；2）在SQL語句中經(jīng)常進(jìn)行GROUP BY、ORDER BY的字段上建立索引；3）在外鍵上建立索引；4）在經(jīng)常存取的多個列上建立復(fù)合索引；5）在不同值較少的字段上不必要建立索引，如性別字段；6）對于經(jīng)常更新的列避免建立索引；7）不要對一些記錄內(nèi)容比較少的表建立索引。

2．3．2 SQL語句的優(yōu)化

雖然特定的數(shù)據(jù)庫服務(wù)器都會對輸入的查詢語句進(jìn)行一定的優(yōu)化操作，但是查詢效率主要取決于DBA所書寫的SQL語句的好壞。為確保編寫的SQL語句有較好的性能，應(yīng)考慮以下的優(yōu)化方法：1）盡量減少使用NOT、＜＞、?。降炔僮鞣驗樗鼈儠?dǎo)致全表掃描。可以把含有NOT、＜＞、！＝等負(fù)邏輯的條件表達(dá)式轉(zhuǎn)化為意思相當(dāng)?shù)恼壿嫛?）只查詢需要的字段，盡量少用“select＊”格式，以減少物理I／O操作。3）用EXISTS替代IN、用NOT EXISTS替代NOT IN：在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下，使用EXISTS（或NOT EXISTS）通常將提高查詢的效率。在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的（因為它對子查詢中的表執(zhí)行了一個全表遍歷）。為了避免使用NOT IN，我們可以把它改寫成外連接（Outer Joins）或NOT EXISTS。4）IS NULL與IS NOT NULL：不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。任何在WHERE子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。5）避免使用不兼容的數(shù)據(jù)類型：例如float和int、char和varchar、binary和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如：SELECT name FROM employee WHERE salary＞60000在這條語句中，如salary字段是money型的，則優(yōu)化器很難對其進(jìn)行優(yōu)化，因為60000是個整型數(shù)。我們應(yīng)當(dāng)在編程時將整型轉(zhuǎn)化成為貨幣型，而不要等到運行時轉(zhuǎn)化。6）盡量減少使用聯(lián)接字段而把所有的條件分列出來用and來進(jìn)行連接，可以充分的利用在某些字段上已經(jīng)存在的索引。select work－id from salary where work－salary｜｜work－dept＝’＄2000teacher’：如果把條件分開來寫成下面的格式，系統(tǒng)的查詢性能可以得到一定的提高。

select work－no from salary where work－salary＝＄2000and work－dept＝’teacher’；

7）避免相關(guān)子查詢：一個列的標(biāo)簽同時在主查詢和WHERE子句中的查詢中出現(xiàn)，那么很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。8）避免困難的正規(guī)表達(dá)式：MATCHES和LIKE關(guān)鍵字支持通配符匹配，技術(shù)上叫正規(guī)表達(dá)式。但這種匹配特別耗費時間。例如：SELECT＊FROM customer WHERE zipcode LIKE“98＿＿＿”即使在zipcode字段上建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為SELECT＊FROM customer WHERE zipcode＞“98000”，在執(zhí)行查詢時就會利用索引來查詢，顯然會大大提高速度。

2．3．3 視圖的使用

利用視圖不僅可以提高數(shù)據(jù)的保密性，方便的設(shè)置用戶的權(quán)限，而且也可以提高數(shù)據(jù)的精煉性［3］。在DBMS中有著許多不同的角色，他們對數(shù)據(jù)的要求是不同的，針對不同類別的用戶分別建立合適的視圖，可以在有效的條件下提高數(shù)據(jù)的有用性，提高系統(tǒng)對不同用戶的查詢響應(yīng)時間。此外用戶訪問數(shù)據(jù)庫一般要求得到的是最近的數(shù)據(jù)，比如查詢話費，最常用的數(shù)據(jù)是最近三個月的。因此在許多情況下，可以按照時間對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行水平分片，把最近一段時間的數(shù)據(jù)呈現(xiàn)給用戶。當(dāng)用戶需要查找“過期”數(shù)據(jù)時再把相應(yīng)的塊調(diào)進(jìn)來。由于這種情況極少發(fā)生，在一定的情況下，可以有效的減少數(shù)據(jù)量，縮小數(shù)據(jù)查找范圍。使用這種方法要注意分區(qū)數(shù)據(jù)的維護(hù)，因此一定要在權(quán)衡維護(hù)和查詢代價的基礎(chǔ)上確定是否要使用分片。如果經(jīng)常要訪問全庫數(shù)據(jù)進(jìn)行綜合對比的話，這種方法就不適用。

2．3．4 合理使用存儲過程

存儲過程由SQL語句和SPL語言的語句組成，創(chuàng)建后轉(zhuǎn)換為可執(zhí)行代碼，作為數(shù)據(jù)庫的一個對象存儲在數(shù)據(jù)庫中，存儲過程的代碼駐留在服務(wù)器端，因而執(zhí)行時不需要將應(yīng)用程序代碼向服務(wù)器端傳送，可以大大減輕網(wǎng)絡(luò)負(fù)載，加快系統(tǒng)響應(yīng)時間。同時，由于存儲過程已編譯為可執(zhí)行代碼，不需要每次執(zhí)行時進(jìn)行分析和優(yōu)化工作，從而減少了預(yù)處理所花費的時間，提高了系統(tǒng)的效率。在工程中，我們可以把經(jīng)常用到的查詢動作編寫成一個存儲過程，并利用參數(shù)實現(xiàn)動態(tài)查詢過程來響應(yīng)客戶的要求；可以實現(xiàn)在服務(wù)器端進(jìn)行批量數(shù)據(jù)處理等操作；可以使用存儲過程作為強(qiáng)制安全性工具；還可以利用系統(tǒng)為用戶定義的管理級別存儲過程實現(xiàn)數(shù)據(jù)的管理、配置和監(jiān)控等。合理使用存儲過程可以有效的提高系統(tǒng)效率。

2．4 查詢優(yōu)化須注意的問題

合理以上數(shù)據(jù)庫查詢優(yōu)化方法會從不同方面，不同程度地提高查詢效率。但使用優(yōu)化方法要考慮具體環(huán)境和數(shù)據(jù)狀態(tài)，以避免不適當(dāng)?shù)厥褂脦淼呢?fù)面影響。例如，索引有助于提高檢索性能，但過多或不當(dāng)?shù)乃饕矔?dǎo)致系統(tǒng)低效。因為用戶在表中每添加一個索引，數(shù)據(jù)庫就要做更多的工作，過多的索引甚至?xí)?dǎo)致索引碎片。另外，對于具體的數(shù)據(jù)結(jié)構(gòu)，優(yōu)化方法和重心也不盡相同。我們要合理使用優(yōu)化策略，綜合考慮優(yōu)化方法帶來的效率和影響，使數(shù)據(jù)庫的性能得到更好的發(fā)揮。

3 結(jié)束語

數(shù)據(jù)庫優(yōu)化是在數(shù)據(jù)量增大的情況下必不可少的工作，本文是針對關(guān)系數(shù)據(jù)庫的優(yōu)化方法進(jìn)行的一些分析和總結(jié)，其中部分方法也使用于非關(guān)系數(shù)據(jù)庫領(lǐng)域，例如，對于面向?qū)ο髷?shù)據(jù)庫，合理使用索引對于數(shù)據(jù)庫優(yōu)化也起到很重要的作用，接下來的工作會具體研究關(guān)系數(shù)據(jù)庫的優(yōu)化方法以及對于不同類型的數(shù)據(jù)庫，不同的服務(wù)器，對優(yōu)化工作的具體要求，接著還要研究非關(guān)系數(shù)據(jù)庫的優(yōu)化方法。

［1］ 薩師煊，王珊．?dāng)?shù)據(jù)庫系統(tǒng)概論：第3版［M］．北京：高等教育出版社，2000．

［2］ （美）HectorGarcia－Molina，JeffreyD．Ullman，and－JenniferWidomDatabaseSystemImplementation［M］．楊冬青，唐世渭，徐其鈞，等，譯．北京：機(jī)械工業(yè)出版社，2001．

［3］ 賈素玲，王強(qiáng)．Oracle數(shù)據(jù)庫基礎(chǔ)［M］．北京：清華大學(xué)出版社，2007．

［4］ 李建中，等．?dāng)?shù)據(jù)庫系統(tǒng)原理：第2版［M］．北京：電子工業(yè)出版社，2005．

責(zé)任編輯：孟云玲

TP311．132．3

A

1671－8275（2011）01－0007－02

2010－11－12

沈海峰（1977－），男，安徽合肥人，安徽廣播電視大學(xué)工程師。