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比掃描表頁更有效。