簡論關系數據庫的查詢優化

沈海峰

（安徽廣播電視大學，安徽合肥 230022）

簡論關系數據庫的查詢優化

沈海峰

（安徽廣播電視大學，安徽合肥 230022）

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

關系數據庫；查詢優化；分析

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

1 數據庫的分類

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

2 關系數據庫查詢優化

2．1 查詢優化的必要性

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

2．2 查詢優化的原理

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

2．3 查詢優化的方法

2．3．1 有效利用索引

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

2．3．2 SQL語句的優化

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

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

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

2．3．3 視圖的使用

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

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

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

2．4 查詢優化須注意的問題

合理以上數據庫查詢優化方法會從不同方面，不同程度地提高查詢效率。但使用優化方法要考慮具體環境和數據狀態，以避免不適當地使用帶來的負面影響。例如，索引有助于提高檢索性能，但過多或不當的索引也會導致系統低效。因為用戶在表中每添加一個索引，數據庫就要做更多的工作，過多的索引甚至會導致索引碎片。另外，對于具體的數據結構，優化方法和重心也不盡相同。我們要合理使用優化策略，綜合考慮優化方法帶來的效率和影響，使數據庫的性能得到更好的發揮。

3 結束語

數據庫優化是在數據量增大的情況下必不可少的工作，本文是針對關系數據庫的優化方法進行的一些分析和總結，其中部分方法也使用于非關系數據庫領域，例如，對于面向對象數據庫，合理使用索引對于數據庫優化也起到很重要的作用，接下來的工作會具體研究關系數據庫的優化方法以及對于不同類型的數據庫，不同的服務器，對優化工作的具體要求，接著還要研究非關系數據庫的優化方法。

［1］ 薩師煊，王珊．數據庫系統概論：第3版［M］．北京：高等教育出版社，2000．

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

［3］ 賈素玲，王強．Oracle數據庫基礎［M］．北京：清華大學出版社，2007．

［4］ 李建中，等．數據庫系統原理：第2版［M］．北京：電子工業出版社，2005．

責任編輯：孟云玲

TP311．132．3

A

1671－8275（2011）01－0007－02

2010－11－12

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