SQL效能第2篇：查詢分析和訪問路徑制定

在SQL效能概述的第一部分中，我們研究了關係最佳化及其影響因素。在今天的文章中，我們將注意力轉向查詢分析以及SQL轉換為可執行程式碼的方式。

從上層看，最佳化過程包括四個步驟：

1. 接收並驗證SQL語句。

2. 分析環境，最佳化滿足SQL語句的方法。

3. 建立機器可讀的指令來執行最佳化的SQL。

4. 執行這些指令或將它們儲存起來以備將來執行。

需要做的第一件事是驗證SQL是否寫對了。這並不意味著它會做你希望它做的事情，只是它符合所需的語法。將對SQL進行分析和檢查。如果遇到任何錯誤，程式將停止，你必須修改SQL，直到它正確為止。在驗證SQL語法之後，下一步是檢查語義，例如資料型別、引用約束、檢查約束、檢視和觸發器。

這個過程的第二步是最有趣的。最佳化器如何決定如何執行可以按其方式傳送的大量SQL語句？此查詢分析步驟掃描SQL以確定其總體複雜性。SQL語句的表示式是決定最佳化器選擇的訪問路徑的一個重要因素。查詢的複雜性、謂詞的數量和型別、函式的存在以及排序子句的存在都將進入最佳化器計算的估計成本中。

SQL語句越複雜，查詢分析就必須做越多的工作來理解SQL語句。在查詢分析期間，最佳化器分析SQL語句和資料庫系統的各個方面，例如

• 需要哪些資料庫中的哪些表

• 是否需要將任何檢視分解為基礎表

• 是否需要表連線或子選擇

• 是否需要UNION、EXCEPT或INTERSECT

• 可以使用哪些索引(如果有的話)

• 必須滿足多少謂詞(WHERE子句)

• 必須執行哪些函式

• SQL是否使用OR或AND

• DBMS如何處理SQL語句的每個元件

• 為SQL語句中的表使用的資料快取分配了多少記憶體

• 如果查詢需要排序，有多少記憶體可用於排序

換句話說，查詢分析將SQL語句分解為必須執行的離散任務，以返回查詢結果。

現代關係最佳化器是基於成本的，這意味著最佳化過程總是試圖為每個查詢制定一個降低總體成本的訪問路徑。為了實現這一點，最佳化器應用查詢成本公式來評估和權衡每個潛在訪問路徑的多個因素：這些因素包括CPU成本、I/O操作、系統編目中的統計資訊以及實際的SQL語句程式碼。

最佳化器可以重寫查詢，將其轉換為等效的、但更容易編譯和最佳化的版本。謂詞下推和轉換可能在此時發生。然後最佳化SQL。將審查和分析多條訪問路徑，以選擇成本最低的選項。最後一步是建立實際的可執行程式碼。

關係最佳化器有許多建立SQL訪問路徑的選項。在較高的層次上，有訪問單個表中的資料的方法，也有組合兩個表中的資料的方法。可以將這些方法組合成一系列訪問方法，為SQL語句建立總體訪問路徑。

對於單表訪問，可以使用掃描或索引檢索資料。在最佳化器確定每個謂詞可用的索引之後，它將決定是使用單個索引、多個索引還是根本不使用索引。

大家很容易說索引訪問將優於掃描訪問，但事實並非總是如此。最佳化器必須評估必須訪問的資料量以及查詢的性質。例如，如果你正在建立一個包含表中每一行的報告，那麼使用索引可能比使用掃描讀取所有資料要慢。

表掃描是最簡單的資料訪問形式。表掃描是透過讀取表的每一行來執行的。根據DBMS的不同，可能存在另一種掃描型別，稱為表空間掃描。表空間掃描讀取表空間中的每個頁面，表空間可能包含多個表。顯然，表空間掃描將比表掃描執行得慢，因為可能會產生額外的I/O讀取不適用的資料。

另一種掃描形式是分割槽掃描。如果DBMS能夠確定要訪問的資料存在於多分割槽表（或表空間）的某些分割槽中，那麼它可以將掃描到的資料限制到適當的分割槽。分割槽掃描應該優於表掃描或表空間掃描，因為所需的I/O數量減少了。

通常，最佳化器會選擇掃描資料，原因如下之一：

• 使用索引無法滿足查詢，可能是因為沒有索引可用、謂詞與索引不匹配，或者謂詞妨礙索引的使用。

• 表中的行符合條件的百分比很高。在這種情況下，使用索引可能效率較低，因為無論如何都需要讀取大多數資料行。

• 具有匹配謂詞的索引具有較低的叢集比率，並且僅對少量資料有效。

• 表太小，使用索引實際上是有害的。對於小表，向表訪問新增索引訪問可能會導致額外的I/O，而不是更少的I/O。

為了幫助掃描的效能，最佳化器可以呼叫資料預取。資料預取會導致DBMS在請求資料頁之前，按順序將資料頁讀入資料快取。從本質上說，資料預取是一種讀前機制——當資料掃描開始請求資料時，它已經存在於記憶體中。Prefetch對於掃描特別有用，但是對於任何型別的順序資料訪問都是實用的。你應該瞭解特定DBMS如何以及為什麼預取資料。

大多數的訪問應該使用索引，這使我們可以選擇掃描或索引訪問。最佳化器必須首先發現是否存在索引。在編寫SQL來訪問列之前，不必定義索引—你可以查詢資料庫所知道的任何表的任何列。 

此外，必須在SQL語句中的可索引謂詞中引用至少一個索引列。DBMS不能為每個WHERE子句使用索引。您必須瞭解謂詞可以使用哪些型別的索引來確保為資料庫應用程式中的查詢建立適當的索引。每個資料庫管理系統都有一個不同的列表，其中列出了什麼是可索引的，什麼是不可索引的。此外，可索引的內容往往會隨著每個DBMS的版本而變化。

最佳化器可以選擇以許多不同的方式使用索引。第一個也是最簡單的索引訪問型別是直接索引查詢。為了使DBMS能夠執行直接索引查詢，必須為索引中的每一列提供值。為了執行直接索引查詢，DBMS將謂詞中請求的值與索引根頁中儲存的值進行比較。基於這種比較，DBMS將把索引遍歷到下一個頁面集。如果存在中間的非葉頁，則讀取適當的非葉頁，並比較該值以確定要訪問哪個葉頁。閱讀適當的頁；索引頁包含指向符合條件的行實際資料的指標。基於頁索引條目中的指標，DBMS讀取適當的表資料頁。

但是，假設SQL語句中沒有提供索引的所有列。不能選擇直接索引查詢，因為DBMS不能匹配完整的索引鍵。相反，可以選擇索引掃描。當一個索引掃描被呼叫時，索引的頁被依次讀取。

索引掃描有兩種基本型別：匹配索引掃描和不匹配索引掃描。匹配的索引掃描有時稱為絕對定位。匹配的索引掃描從索引的根頁開始，以與直接索引查詢相同的方式向下工作到葉頁。但是，由於索引的完整鍵不可用，DBMS必須掃描索引的頁，查詢可用的值，直到檢索到所有匹配的值。

要使用匹配的索引掃描，必須在索引鍵中指定高階列;即索引DDL中指定的第一列。高階列為DBMS從根頁面到適當的葉頁面遍歷索引結構提供了起點。

請考慮在查詢中不指定高階列的後果。DBMS可以部署不匹配的索引掃描，有時稱為相對定位。當由於索引鍵中的第一列未指定而無法確定起始點時，DBMS不能使用索引樹結構。但是，它可以掃描索引頁。不匹配的索引掃描從索引中的第一個頁開始，然後應用可用的謂詞順序掃描後續的頁。

不匹配的索引掃描可能比表或表空間掃描更有效，特別是如果必須訪問的資料頁是按叢集順序進行的。此外，請記住索引頁(或塊)包含的條目比表頁多，因為索引“行”比錶行短，從而使索引頁I/O比掃描表頁更有效。