SQL入門經典：第五部分 SQL性能調整_斯蒂芬森

第16章利用索引改善性能

第17章改善數據庫性能

第16章利用索引改善性能

本章的重點包括：

索引如何工作

如何創建索引

不同類型的索引

何時使用索引

何時不使用索引

本章介紹如何通過創建和使用索引來改善 SQL語句的性能，首先介紹 CREATE INDEX命令，然後介紹如何使用表裡的索引。

16.1 什麼是索引

簡單來說，索引就是一個指針，指向表裡的數據。數據庫裡的索引與圖書中的索引十分類似。舉例來說，如果想查閱書中關於某個主題的內容，我們首先會查看索引，其中會以字母順序列出全部主題，告訴我們一個或多個特定的書頁號碼。索引在數據庫裡也起到這樣的作用，指向數據在表裡的準確物理位置。實際上，我們被引導到數據在數據庫底層文件裡的位置，但從表面上來看，我們是在引用一個表。

在查找信息時，逐頁尋找快呢，還是查看索引來瞭解準確頁碼快呢？當然，使用索引是最有效的方法。當書很厚時，這樣做會節省大量時間。假設書只有幾頁，那麼直接查找信息可能會比先看索引再返回到某頁更快一些。當數據庫沒有索引時，它所進行的操作通常被稱為全表掃瞄，就像是逐頁翻看一本書。關於全表掃瞄的具體介紹請見第17章。

索引通常與相應的表是分開保存的，其主要目的是提高數據檢索的性能。索引的創建與刪除不會影響到數據本身，但會影響數據檢索的速度。索引也會佔據物理存儲空間，而且可能會比表本身還大。因此在考慮數據庫的存儲空間時，需要考慮索引要佔用的空間。

16.2 索引是如何工作的

索引在創建之後，用於記錄與被索引字段相關聯的位置值。當表裡添加新數據時，索引裡也會添加新項。當數據庫執行查詢，而且WHERE條件裡指定的字段已經設置了索引時，數據庫會首先在索引裡搜索WHERE子句裡指定的值。如果在索引裡找到了這個值，索引就可以返回被搜索數據在表裡的實際位置。圖16.1展示了索引的工作過程。

假設執行了如下查詢：

圖16.1 使用索引訪問表

如圖16.1所示，這裡引用了索引NAME來尋找『SMITH』的位置；在找到了位置之後，數據就能迅速地從表裡檢索出來。在索引裡，數據（本例中是姓名）是按字母順序排序的。

注意：索引的不同創建方式

在某些實現裡，可以在創建表的過程中創建索引。但大多數實現提供了一個單獨的命令來創建索引，其詳細語法請參考具體的文檔。

如果表裡沒有索引，在執行同樣這個查詢時，數據庫就會進行全表掃瞄，也就是說表裡的每行數據都會被讀取來獲取NAME字段等於『SMITH』的記錄。

索引通常以一種樹形結構保存信息，因此速度比較快。假設我們對一個書名列表設置了索引，這個索引具有一個根節點，也就是每個查詢的起始點。根節點具有分支，在本例中可以有兩個分支，一個代表字母A到L，另一個代表字母M到Z。如果要查詢以字母M開頭的書名，我們就會從根節點進入索引，並且立即轉到包含字母M到Z的分支。這種方式可以消除大約一半的可能性，從而用更短的時間找到準確的書名。

16.3 CREATE INDEX命令

像SQL裡的其他語句一樣，創建索引的語句在不同關係型數據庫實現裡也是不同的，大多數實現使用CREATE INDEX語句：

不同廠商的CREATE INDEX語句在選項方面有不少差別，有些實現允許指定存儲子句（像CREATE TABLE語句）、允許排序（DESC||ASC）、允許使用簇。詳細語法請查看具體實現的文檔。

16.4 索引的類型

數據庫裡的表可以創建多種類型的索引，它們的目標是一樣的：通過提高數據檢索速度來改善數據庫性能。本章介紹單字段索引、組合索引和唯一索引。

16.4.1 單字段索引

提示：最有效的單字段索引

如果某個字段經常在WHERE子句作為單獨的查詢條件，它的單字段索引是最有效的。適合作為單字段索引的值有個人標識號碼、序列號或系統指派的鍵值。

對單個字段的索引是索引中最簡單、最常見的形式。顯然，單字段索引是基於一個字段創建的，其基本語法如下所示：

舉例來說，如果想對表EMPLOYEE_TBL裡僱員的姓創建索引，相應的命令如下所示：

16.4.2 唯一索引

唯一索引用於改善性能和保證數據完整性。唯一索引不允許表裡具有重複值，除此之外，它與普通索引的功能一樣。其語法如下所示：

如果想對表EMPLOYEE_TBL裡僱員的姓創建唯一索引，相應的命令如下所示：

這個索引唯一需要注意的問題是，表EMPLOYEE_TBL裡每個人的姓都必須是唯一的，這通常是不現實的。但是，像個人社會保險號碼這樣的字段可以設置為唯一索引，因為每個人的這個號碼都是唯一的。

有人也許會問，如果僱員的社會保險號碼是表的主鍵，那應該怎麼辦呢？當我們定義表的主鍵時，一個默認的索引就會被創建。但是，公司會使用自己編製的號碼作為僱員ID，同時使用僱員的SSN用於納稅。通常我們會對這個字段設置索引，確保它在每條記錄裡都具有唯一的值。

對於類似索引這種對象，一個比較可取的方法是，在創建數據庫結構的同時，基於空白表來創建索引。這樣做可以確保後續輸入的數據完全滿足用戶的要求。如果要在既有數據中創建索引，就必須進行相應的分析工作，來確定是否需要調整數據以便符合索引的要求。

16.4.3 組合索引

組合索引是基於一個表裡兩個或多個字段的索引。在創建組合索引時，我們要考慮性能的問題，因為字段在索引裡的次序對數據檢索速度有很大的影響。一般來說，最具有限制的值應該排在前面，從而得到最好的性能。但是，總是會在查詢裡指定的字段應該放在首位。組合索引的語法如下所示：

組合索引的範例如下所示：

在這個範例裡，我們基於表ORDERS_TBL裡的兩個字段（CUST_ID和PROD_ID）創建組合索引。這是因為我們認為這兩個字段經常會在查詢的WHERE子句裡聯合使用。

注意：唯一索引的相關規則

唯一索引只能用於在表裡沒有重複值的字段。換句話說，如果現有表已經包含被索引關鍵字的記錄，就不能再對它創建唯一索引了。此外，允許NULL值的字段上也不能創建唯一索引。如果不滿足上述規則，那麼創建語句就無法運行成功。

在選擇是使用單字段索引還是組合索引時，要考慮在查詢的WHERE子句裡最經常使用什麼字段。如果經常只使用一個字段，單字段索引就是最適合的；如果經常使用兩個或多個字段，組合索引就是最好的索引。

16.4.4 隱含索引

隱含索引是數據庫服務程序在創建對像時自動創建的。比如，數據庫會為主鍵約束和唯一性約束自動創建索引。

為什麼給這些約束自動創建索引？從一個數據庫服務程序的角度來看，當用戶向數據庫添加一個新產品時，產品標識是表裡的主鍵，表示它必須是唯一值。為了有效地檢查新值在數以百計甚至是數以千計的記錄裡是唯一的，表裡的產品標識必須被索引。因此，在創建主鍵或唯一性約束時，數據庫會自動為它們創建索引。

提示：最有效的組合索引

對於經常在查詢的WHERE子句裡共同使用的字段，組合索引是最有效的。

16.5 何時考慮使用索引

唯一索引隱含地與主鍵共同實現主鍵的功能。外鍵經常用於與父表的結合，所以也適合設置索引。一般來說，大多數用於表結合的字段都應該設置索引。

經常在ORDER BY和GROUP BY裡引用的字段也應該考慮設置索引。舉例來說，如果根據個人姓名進行排序，對姓名字段設置索引會大有好處。它會對每個姓名自動按字母順序排序，簡化了實際的排序操作，提高了輸出結果的速度。

另外，具有大量唯一值的字段，或是在WHERE子句裡會返回很小部分記錄的字段，都可以考慮設置索引。這主要是為了測試或避免錯誤。就像代碼和數據庫結構在投入使用之前需要反覆進行測試一樣，索引也是如此。我們應該用一些時間來嘗試不同的索引組合、沒有索引、單字段索引和組合索引。索引的使用沒有什麼固定的規則，需要對表的關係、查詢和事務需求、數據本身有透徹的瞭解才能最有效地使用索引。

16.6 何時應該避免使用索引

注意：要有事先規劃

表和索引都應該進行事先的規劃。不要認為使用索引就能解決所有的性能問題，索引可能根本不會改善性能（甚至可能降低性能）而只是佔據磁盤空間。

雖然使用索引的初衷是提高數據庫性能，但有時也要避免使用它們。下面是使用索引的方針。

索引不應該用於小規模的表。因為查詢索引會增加額外的查詢時間。對於小規模的表，讓搜索發動機進行全表搜索，往往比先查詢索引的速度更快。

當字段用於WHERE子句作為過濾器會返回表裡的大部分記錄時，該字段就不適合設置索引。舉例來說，圖書裡的索引不會包括像the或and這樣的單詞。

經常會被批量更新的表可以具有索引，但批量操作的性能會由於索引而降低。對於經常會被加載或批量操作的表來說，可以在執行批量操作之前去除索引，在完成操作之後再重新創建索引。這是因為當表裡插入數據時，索引也會被更新，從而增加了額外的開銷。

不應該對包含大量NULL值的字段設置索引。索引對在不同記錄中包含不同數據的字段特別有效。字段中過多的NULL值會嚴重影響索引的運行效率。

經常被操作的字段不應該設置索引，因為對索引的維護會變得很繁重。

從圖16.2可以看出，像性別這樣的字段設置索引就沒有什麼好處。舉例來說，向數據庫提交如下查詢：

從圖16.2 可以看出，在運行上述這個查詢時，表與索引之間有一個持續的行為。由於WHERE GENDER = 『FEMALE』（或『MALE』）子句會返回大量記錄，數據庫服務程序必須持續地讀取索引、然後讀取表的內容、再讀取索引、再讀取表，如此反覆。在這個範例裡，由於表裡的大部分數據肯定是要被讀取的，所以使用全表掃瞄可能會效率更高。

圖16.2 低效索引的例子

警告：索引也會帶來運行問題

對於特別長的關鍵字創建索引時要十分謹慎，因為大量I/O開銷會不可避免地降低數據庫性能。

一般來說，當字段作為查詢裡的條件會返回表裡的大部分數據時，我們不會對它設置索引。換句話說，不要對像性別這樣只包含很少不同值的字段設置索引。這通常被稱為字段的基數，或數據的唯一性。高基數意味著很高的唯一性，比如像身份號碼這樣的數據。低基數的唯一性不高，比如像性別這樣的字段。

16.7 修改索引

創建索引後，也可以對其進行修改。其語法結構與CREATE INDEX類似。能夠修改的內容在不同的數據庫實現中有所不同，但基本上修改的都是字段、順序等內容。其語法如下所示：

對生產系統進行修改時需要特別小心。大部分情況下，對索引進行的修改操作會被馬上執行，引起系統資源的額外消耗。此外，大部分數據庫實現在進行索引修改的時候無法進行查詢操作，從而會對系統的運行產生影響。

16.8 刪除索引

刪除索引的方法相當簡單，具體語法請參考相應的文檔，但大多數實現使用DROP命令。在刪除索引時要謹慎，因為性能可能會嚴重降低（或提高！）。其語法如下所示：

MySQL中的語法結構稍有不同，需要同時指定創建索引的表格：

刪除索引的最常見原因是嘗試改善性能。記住，在刪除索引之後，我們還可以重新創建它。有時重建索引是為了減少碎片。在探索如何讓數據庫具有最佳性能時，調整索引是個必要的過程，其中可能包括創建索引、刪除它、最後再重新創建它（經過修改或不修改）。

提示：小心使用索引

索引對於提高性能大有幫助，但在有些情況下也會降低性能。我們應該避免對只包含很少不同值的字段創建索引，比如性別、州名等。

注意：刪除索引的語法差異

MySQL使用ALTER TABLE命令刪除索引。也可以使用DROP INDEX命令， MySQL會將其映射為適當的ALTER TABLE命令。再次提醒，不同的SQL實現在語法方面可能會有所不同，特別是在處理索引和數據存儲的時候。

16.9 小結

索引可以用於改善查詢和事務的整體性能。數據庫索引（有點像圖書裡的索引）可以迅速地從表裡引用特定的數據。創建索引的最常用方法是使用CREATE INDEX命令。在不同的實現裡有多種不同類型的索引，包括單字段索引、唯一索引和組合索引。在判斷使用什麼類型的索引時需要考慮多方面的因素，才能讓它最好地滿足數據庫的需要。有效地使用索引通常需要有一定的經驗、全面瞭解表的關係和數據，以及一點實踐，設置索引時的一點點耐心可能會為以後的工作節約幾分鐘、幾小時，甚至幾天的時間。

16.10 問與答

問：索引是否像表一樣佔據實際的空間？

答：是的。索引在數據庫裡佔據物理空間。實際上，索引可能比所在的表更大。

問：如果為了讓批處理工作更快地完成而刪除了索引，需要多長時間才能重新創建索引？

答：這取決於多個因素，比如索引的大小、CPU利用率和計算機的性能。

問：全部索引都必須是唯一索引嗎？

答：不是。唯一索引不允許存在重複值，而在表裡有時是需要有重複值的。

16.11 實踐

下面的內容包含一些測試問題和實戰練習。這些測試問題的目的在於檢驗對學習內容的理解程度。實戰練習有助於把學習的內容應用於實踐，並且鞏固對知識的掌握。在繼續學習之前請先完成測試與練習，答案請見附錄C。

16.11.1 測驗

1．使用索引的主要缺點是什麼？

2．組合索引裡的字段順序為什麼很重要？

3．具有大量NULL值的字段是否應該設置索引？

4．索引的主要作用是去除表裡的重複數據嗎？

5．判斷正誤：使用組合索引主要是為了在索引裡使用匯聚函數。

6．基數是什麼含義？什麼樣的字段可以被看作是高基數的？

16.11.2 練習

1．判斷在下列情況下是否應該使用索引，如果是，請選擇索引的類型。

a．字段很多，但表的規模相對較小。

b．中等規模的表，不允許有重複值。

c．多個字段，大規模的表，多個字段用在WHERE子句作為過濾器。

d．大規模表，很多字段，大量數據操作。

2．編寫 SQL 語句，為表 EMPLOYEE_PAY_TBL 的 POSITION 字段創建名為 EP_POSITION的索引。

3．修改練習2所創建的索引，將其變成唯一索引。要為SALARY字段創建唯一索引，需要做些什麼？編寫並依次運行這些命令。

4．研究本書裡使用的表，根據用戶可能對表進行的檢索方式，判斷哪些字段適合設置索引。

5．在表 ORDERS_TBL 上創建一個多字段索引，包含下列字段：CUST_ID、PROD_ID和ORD_DATE。

6．在表裡創建其他一些索引。

第17章改善數據庫性能

本章的重點包括：

什麼是SQL語句調整

數據庫調整與SQL語句調整

格式化SQL語句

適當地結合表

最嚴格的條件

全表掃瞄

使用索引

避免使用OR和HAVING

避免大規模排序操作

本章介紹如何使用一些非常簡單的方法調整SQL語句來獲得最好的性能。

17.1 什麼是SQL語句調整

SQL語句調整是優化生成SQL語句的過程，從而以最有效和最高效的方式獲得結果。首先是查詢裡元素的基本安排，因為簡單的格式化過程就能夠在語句優化中發揮很大作用。

SQL語句調整主要涉及調整語句的FROM和WHERE子句，因為數據庫服務程序主要根據這兩個子句執行查詢。前面的課程已經介紹了FROM和WHERE子句的基礎知識，現在就來介紹如何細緻地調整它們來獲得更好的結果，讓用戶更加滿意。

17.2 數據庫調整與SQL語句調整

在繼續介紹SQL語句調整之前，先要理解數據庫調整與SQL語句調整之間的差別。

數據庫調整是調整實際數據庫的過程，包括分配內存、磁盤、CPU、I/O 和底層數據庫進程，還涉及數據庫結構本身的管理與操作，比如表和索引的設計與佈局。另外，數據庫調整通常會包括調整數據庫體系來優化硬件的使用。實際上，在調整數據庫時還要考慮其他很多因素，但這些任務通常是由數據庫管理員（DBA）與系統管理員合作完成的。數據庫調整的目標是確保數據庫的設計能夠最好地滿足用戶對數據庫操作的需要。

SQL調整是調整訪問數據庫的SQL語句，這些語句包括數據庫查詢和事務操作，比如插入、更新和刪除。SQL語句調整的目標是利用數據庫和系統資源、索引，針對數據庫的當前狀態進行最有效的訪問，從而減少對數據庫執行查詢所需的開銷。

注意：兩種調整缺一不可

為了在訪問數據庫時達到優化結果，數據庫調整和SQL語句調整都需要進行。一個調整很差的數據庫會極大地抵消SQL調整所付出的努力，反之亦然。在理想狀態下，最好首先調整數據庫，確保必要的字段都具有索引，然後再調整SQL代碼。

17.3 格式化SQL語句

格式化SQL語句聽上去是個很顯然的事情，但也值得一提。一個新手在構造SQL語句時很可能會忽略很多方面，下面的小節將進行討論，它們有些是很明顯的，有些則不是。

為提高可讀性格式化SQL語句。

FROM子句裡表的順序。

最嚴格條件在WHERE子句裡的位置。

結合條件在WHERE子句裡的位置。

17.3.1 為提高可讀性格式化SQL語句

注意：一切以最優化為目的

大多數關係型數據庫實現裡有一個名為「SQL 優化器」的東西，它可以執行SQL語句，並且基於SQL語句的構成方式和數據庫裡可用的索引來判斷執行語句的最佳方式。這些優化器並不是都相同，具體情況請查看相應的文檔，或是聯繫數據庫管理員來瞭解優化器如何讀取SQL代碼。理解優化器的工作方式有助於有效地調整SQL語句。

為提高可讀性格式化SQL語句是件很顯然的事情，但很多SQL語句的書寫方式並不那麼整潔。雖然語句的整潔程度並不會影響實際的性能（數據庫並不關心語句的外觀是否整潔），但仔細地使用格式是調整語句的第一步。當我們以調整的眼光看待一個SQL語句時，讓它具有很好的可讀性總是首先要考慮的。如果語句很難看清，又如何能夠判斷它是否正確呢？

讓語句具有良好可讀性的基本規則如下所示。

每個子句都以新行開始。舉例來說，讓FROM子句位於與SELECT子句不同的行裡，讓WHERE子句位於與FROM子句不同的行裡，以此類推。

當子句裡的參數超過一行長度需要換行時，利用製表符（TAB）或空格來形成縮進。

以一致的方式使用製表符和空格。

當語句裡使用多個表時，使用表的別名。在這種語句裡使用表的全名來限定每個字段會讓語句迅速變得冗長，讓可讀性降低。

如果SQL實現裡允許使用註釋，應該在語句裡有節制地使用。註釋是很好的文檔，但過多的註釋會讓語句臃腫。

如果在SELECT語句裡要使用多個字段，就讓每個字段都從新行開始。

如果在FROM子句裡要使用多個表，就讓每個表名都從新行開始。

讓WHERE子句裡每個條件都以新行開始，這樣就可以清晰地看到語句的所有條件及其次序。

下面是一個可讀性很差的SQL語句：

下面是格式化之後的語句，可讀性明顯提高：

這兩個語句完全一樣，但第二個語句具有更好的可讀性。通過使用表的別名（在FROM子句裡定義），第二個語句得到了極大的簡化。同時使用空格對齊每個子句裡的元素，讓每個子句十分明顯。

注意：在使用多個表的同時確保性能

當 FROM 子句裡列出了多個表時，請查看具體實現的文檔來瞭解有關提高性能的技巧。

再強調一次，雖然提高語句的可讀性並不會直接改善它的性能，但這樣會幫助我們更方便地修改和調整很長和很複雜的語句。現在我們可以輕鬆地看到被選擇的字段、所使用的表、所執行的表結合和查詢的條件。

17.3.2 FROM子句裡的表

FROM子句裡表的安排或次序對性能有很大影響，取決於優化器如何讀取SQL語句。舉例來說，把較小的表列在前面，把較大的表列在後面，就會獲得更好的性能。有些經驗豐富的用戶發現把較大的表列在FROM子句的最後面可以得到更好的效率。

下面是FROM子句的一個範例：

注意：創建編碼標準

在多人編程環境裡，創建編碼標準是特別重要的。如果全部代碼具有一致的格式，就可以更好地管理共享代碼及修改代碼。

17.3.3 結合條件的次序

第 13 章曾經介紹過，大多數結合使用一個基表鏈接到具有一個或多個共有字段的其他表。基表是主表，查詢裡的大多數或全部表都與它結合。在WHERE子句裡，來自基表的字段一般放到結合操作的右側，要被結合的表通常按照從小到大的次序排列，就像FROM子句裡表的排列順序一樣。

如果沒有基表，那表就應該從小到大排列，讓最大的表位於WHERE子句裡結合操作的右側。結合條件應該位於WHERE子句的最前面，其後才是過濾條件，如下所示：

提示：嚴格限制結合操作的條件

由於結合操作通常會從表裡返回大部分數據，所以結合條件應該在更嚴格的條件之後再生效。

在這個範例裡，TABLE3是基表，TABLE1和TABLE2結合到TABLE3。

17.3.4 最嚴格條件

最嚴格條件通常是 SQL 查詢達到最優性能的關鍵因素。什麼是最嚴格的條件？它是WHERE子句裡返回最少記錄的條件。與之相反，最寬鬆的條件就是語句裡返回最多記錄的條件。在這裡我們重點關注最嚴格的條件，因為它對查詢返回的數據進行了最大限度的過濾。

我們應該讓SQL優化器首先計算最嚴格條件，因為它會返回最小的數據子集，從而減小查詢的開銷。最嚴格條件的位置取決於優化器的工作方式，有時優化器從WHERE子句的底部開始讀取，因此需要把最嚴格條件放到WHERE子句的末尾，從而讓優化器首先讀取它。下面的例子展示了如何根據約束條件來構造 WHERE 子句，以及如何根據表的體積來構造FROM子句。

提示：對WHERE子句進行測試

如果不知道具體實現的SQL優化器如何工作、DBA也不知情、也沒有足夠的文檔資料，我們可以執行一個需要一定時間的大型查詢，然後重新排列WHERE子句裡的條件，記錄每次查詢執行所需的時間。採取這種方法，不用幾次測試就可以判斷出優化器讀取WHERE子句的方向。為了在測試中獲得更準確的結果，最好在測試時關閉數據庫緩存。

下面是一個虛構表的測試範例：

下面是第一個查詢：

下面是第二個查詢：

假設第一個查詢用了20秒，第二個查詢用10秒。由於第二個查詢速度比較快，而且在它的WHERE子句裡，最嚴格條件位於最後的位置，所以我們可以認為優化器從WHERE子句的底部開始讀取條件。

注意：使用索引字段

從實踐總結出來的經驗表明，最好使用具有索引的字段作為查詢裡的最嚴格條件。索引通常會改善查詢的性能。

17.4 全表掃瞄

在沒有使用索引時，或是SQL語句所使用的表沒有索引時，就會發生全表掃瞄。一般來說，全表掃瞄返回數據的速度要明顯比使用索引慢。表越大，全表掃瞄返回數據的速度就越慢。查詢優化器會決定在執行SQL語句時是否使用索引，而大多數情況會使用索引（如果存在）。

有些實現具有複雜的查詢優化器，可以決定是否應該使用索引。這種判斷基於從數據庫對像上收集的統計信息，比如對象的規模、索引字段在指定條件下返回的記錄數量等。關於優化器的這種判決能力請查看具體實現的文檔。

在讀取大規模的表時，應該避免進行全表掃瞄。舉例來說，當讀取沒有索引的表時，就會發生全表掃瞄，這通常會需要較長的時間才能返回數據。對於大多數大型表來說，應該考慮設置索引。而對於小型表來說，就像前面已經說過的，即使表裡有索引，優化器也可能會選擇全表掃瞄而不是使用索引。對於具有索引的小型表來說，可以考慮刪除索引，從而釋放索引所佔據的空間，使其可以用於數據庫的其他對象。

提示：簡單方法避免全表掃瞄

除了確保表裡存在索引之外，避免全表掃瞄的最簡單、最明顯方法是在查詢的WHERE子句裡設置條件來過濾返回的數據。

下面是應該被索引的數據：

作為主鍵的字段；

作為外鍵的字段；

在結合表裡經常使用的字段；

經常在查詢裡作為條件的字段；

大部分值是唯一值的字段。

注意：全表掃瞄也有好處

有時全表掃瞄也是好的。對小型表進行的查詢，或是會返回表裡大部分記錄的查詢應該執行全表掃瞄。強制執行全表掃瞄的最簡單方式是不給表創建索引。

17.5 其他性能考慮

在調整SQL語句裡還有其他一些性能考慮，後面的小節將討論如下概念：

使用LIKE操作符和通配符；

避免OR操作符；

避免HAVING子句；

避免大規模排序操作；

使用存儲過程；

在批加載時關閉索引。

17.5.1 使用LIKE操作符和通配符

LIKE 操作符是個很有用的工具，它能夠以靈活的方式為查詢設置條件。在查詢裡使用通配符能夠消除很多可能返回的記錄。對於搜索類似數據（不等於特定值的數據）的查詢來說，通配符是非常靈活的。

假設我們要編寫一個查詢，從表EMPOYEE_TBL裡選擇字段EMP_ID、LAST_NAME、FIRST_NAME和STATE，獲得姓為Stevens的僱員ID、姓名和所在的州。下面3個範例使用了不同的通配符。

第一個查詢：

第二個查詢：

下面是第三個查詢：

這些SQL語句並不是必須返回同樣的結果。更可能的情況是，查詢1利用了索引的優勢，返回的記錄比其他兩個查詢少。查詢2和查詢3沒有明確指定要返回的數據，其檢索速度要比查詢1慢。另外，查詢3應該比查詢2更快，因為它指定了搜索字符串的開頭字符（而且字段LAST_NAME很可能具有索引），因此它能夠利用索引。

注意：說明數據存在的差別

查詢1可能會返回姓為Stevens的全部僱員，但難道Stevens不能有其他拼寫方式了嗎？查詢2會返回姓為Stevens及其他拼寫方式的全部僱員。查詢3返回姓以St開頭的全部僱員，這是確保獲取全部姓Stevens（或Stephens）的記錄的唯一方式。

17.5.2 避免使用OR操作符

在SQL語句裡用謂詞IN代替OR操作符能夠提高數據檢索速度。SQL實現裡有計時工具或其他檢查工具，可以反應出OR操作符與謂詞IN之間的性能差別。下面的一個範例將展示如何用IN代替OR來重新構造SQL語句。

注意：如何使用OR和IN

關於OR操作符和謂詞IN請參見第8章。

下面是使用OR操作符的查詢：

下面是同一個查詢，使用了謂詞IN：

這兩個SQL返回完全相同的數據，但通過測試可以發現，用IN代替OR後，檢索數據的速度明顯提高了。

17.5.3 避免使用HAVING子句

HAVING子句是很有用的，可以減少GROUP BY子句返回的數據，但使用它也要付出代價。HAVING子句會讓SQL優化器進行額外的工作，也就需要額外的時間。這樣的查詢既要對返回的結果集進行分組，又要根據HAVING子句的限制條件對結果集進行分析。看下面的例子：

在這個例子中，我們需要找到對某個產品的總計消費超過 25 元的客戶。這個查詢很簡單，而且我們的示例數據庫也很小，但HAVING子句的使用仍然增加了額外的工作，尤其當HAVING子句包含了複雜的邏輯而又應用於大量數據的時候。在可能的情況下，盡量不要在SQL語句中使用HAVING子句，如果需要使用，則最好盡可能地使其中的限制條件簡單化。

17.5.4 避免大規模排序操作

大規模排序操作意味著使用ORDER BY、GROUP BY和HAVING子句。無論何時執行排序操作，都意味著數據子集必須要保存到內存或磁盤裡（當已分配的內存空間不足時）。數據是經常需要排序的，排序的主要問題是會影響SQL語句的響應時間。由於大規模排序操作不是總可以避免的，所以最好把大規模排序在批處理過程裡，在數據庫使用的非繁忙期運行，從而避免影響大多數用戶進程的性能。

17.5.5 使用存儲過程

我們可以為經常運行的SQL語句（特別是大型事務或查詢）創建存儲過程。所謂存儲過程就是經過編譯的、以可執行格式永久保存在數據庫裡的SQL語句。

一般情況下，當SQL語句被提交給數據庫時，數據庫必須檢查它的語法，並且把語句轉化為可以在數據庫裡執行的格式（稱為解析）。語句被解析之後就保存在內存裡，但這並不是持久的。也就是說，當其他操作需要使用內存時，語句就會被從內存裡釋放。而在使用存儲過程時，SQL語句總是處於可執行格式，並且一直會保存在數據庫裡，直到像別的數據庫對像一樣被刪除。關於存儲過程的詳細介紹請見第22章。

17.5.6 在批加載時關閉索引

當用戶向數據庫提交一個事務時（INSERT、UPDATE或DELETE），表和與這個表相關聯的索引裡都會有數據變化。這意味著如果表EMPLOYEE裡有一個索引，而用戶更新了表EMPLOYEE，那麼相關索引也會被更新。在事務環境裡，雖然對表的每次寫入都會導致索引也被寫入，但一般不會產生什麼問題。

然而在批量加載時，索引可能會嚴重地降低性能。批加載可能包含數百、數千或數百萬操作語句或事務，由於規模較大，批加載需要較長的時間才能完成，而且通常安排在非高峰期使用，一般是在週末或夜晚。為了優化批加載的性能——需要12小時完成的批加載可能縮短為6小時——最好在加載過程中關閉相應表的索引。當相應的索引被刪除之後，對表所做的修改會在更短的時間內完成，整個操作也會更快地完成。當批加載結果之後，我們可以重建索引。在索引的重建過程中，表裡適當的數據會被填充到索引。雖然對於大型表來說，創建索引需要一定的時間，但從整體來看，先刪除索引再重建它所需要的時間要更少一些。

在批加載操作的前後刪除並重建索引的方法還有另一個優點，就是可以減少索引裡的碎片。當數據庫不斷增長時，記錄被添加、刪除和更新，就會產生碎片。對於不斷增長的數據庫來說，最好定期地刪除和重建索引。當索引被重建時，構成索引的物理空間數量減少了，也就減少了讀取索引所需的磁盤I/O，用戶就會更快地得到結果，皆大歡喜。

17.6 基於成本的優化

用戶可能經常會遇到需要進行SQL語句調整的數據庫。這類系統在任何一個時間點上往往都有數千條SQL語句正在執行。要優化進行調整所花費的時間，需要首先確定需要調整的查詢類型。這就是我們所關注的，基於成本的優化試圖確定什麼樣的查詢造成了系統資源的額外消耗。例如，如果我們用運行時間來作為衡量標準的話，如下兩個查詢會獲得相應的運行時間：

簡單來看，第1條語句似乎就是我們需要進行優化的查詢。但是，如果第2條語句每小時執行1000次，而第1條語句每小時僅執行10次，情況又怎麼樣呢？結果完全相反。

基於成本的優化根據資源消耗量對SQL語句進行排序。根據查詢的衡量方法（如執行時間、讀庫次數等）以及給定時間段內的執行次數，可以方便地確定資源消耗量：

總計資源消耗 = 衡量方法×執行次數

使用這種方法，可以最大程度地獲得調整收益。在上面的例子中，如果我們能夠將每條語句的運行時間減半，就可以很方便地看出所節省的時間：

這樣就很容易理解，為什麼要把寶貴的時間花在第2條語句上了。這不僅優化了數據庫，也同時優化了用戶的時間。

17.7 性能工具

很多關係型數據庫具有內置的工具用於SQL語句和數據庫性能調整。舉例來說，Oracle有一個名為EXPLAIN PLAN的工具，可以向用戶顯示SQL語句的執行計劃。還有一個工具是TKPROF，它可以測量SQL語句的實際執行時間。在SQL Server裡有一個Query Analyzer，可以向用戶提供估計的執行計劃或已執行查詢的統計參數。關於可以使用的工具請詢問DBA或查看相應的文檔。

17.8 小結

本章介紹了在關係型數據庫裡調整SQL語句的含義，介紹了兩種基本的調整類型：數據庫調整和SQL語句調整，它們對於提高語句的執行效率都是很重要的。它們具有同等的重要性，只調整一個無法達到優化目的。

本章介紹了調整SQL語句的方法，首先是語句的可讀性，雖然它不能直接改善性能，但有助於程序員開發和管理語句。SQL語句性能中一個重要因素是索引的使用，有時需要使用， 有時則需要避免。對於任何用於改善SQL語句性能的方法來說，最重要的是要理解數據本身、數據庫設計和關係以及用戶的需求。

17.9 問與答

問：通過遵循本章所介紹的規則，以數據檢索時間來說，在實際應用中能夠獲得多大的性能提升呢？

答：在實際應用中，檢索時間可能縮短幾分之一秒，或是幾分鐘、幾小時，甚至是幾天。

問：如何測試SQL語句的性能？

答：每個SQL實現都應該有一個工具或系統來測試性能。本書中使用了Oracle7來測試SQL語句，它有多個工具可以測試性能，包括EXPLAIN PLAN、TKPROF和SET命令。每個實現裡的具體工具及其使用請參考相應的文檔。

17.10 實踐

17.10.1 測驗

1．在小規模表上使用唯一索引會帶來什麼好處嗎？

2．當執行查詢時，如果優化器決定不使用表上的索引，會發生什麼呢？