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

在SQL性能概述的第一部分中，我們研究了關(guān)系優(yōu)化及其影響因素。在今天的文章中，我們將注意力轉(zhuǎn)向查詢分析以及SQL轉(zhuǎn)換為可執(zhí)行代碼的方式。

從上層看，優(yōu)化過程包括四個步驟：

1. 接收并驗證SQL語句。

2. 分析環(huán)境，優(yōu)化滿足SQL語句的方法。

3. 創(chuàng)建機器可讀的指令來執(zhí)行優(yōu)化的SQL。

4. 執(zhí)行這些指令或?qū)⑺鼈兇鎯ζ饋硪詡鋵韴?zhí)行。

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

這個過程的第二步是最有趣的。優(yōu)化器如何決定如何執(zhí)行可以按其方式發(fā)送的大量SQL語句？此查詢分析步驟掃描SQL以確定其總體復(fù)雜性。SQL語句的表達式是決定優(yōu)化器選擇的訪問路徑的一個重要因素。查詢的復(fù)雜性、謂詞的數(shù)量和類型、函數(shù)的存在以及排序子句的存在都將進入優(yōu)化器計算的估計成本中。

SQL語句越復(fù)雜，查詢分析就必須做越多的工作來理解SQL語句。在查詢分析期間，優(yōu)化器分析SQL語句和數(shù)據(jù)庫系統(tǒng)的各個方面，例如

• 需要哪些數(shù)據(jù)庫中的哪些表

• 是否需要將任何視圖分解為基礎(chǔ)表

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

• 是否需要UNION、EXCEPT或INTERSECT

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

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

• 必須執(zhí)行哪些函數(shù)

• SQL是否使用OR或AND

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

• 為SQL語句中的表使用的數(shù)據(jù)緩存分配了多少內(nèi)存

• 如果查詢需要排序，有多少內(nèi)存可用于排序

換句話說，查詢分析將SQL語句分解為必須執(zhí)行的離散任務(wù)，以返回查詢結(jié)果。

現(xiàn)代關(guān)系優(yōu)化器是基于成本的，這意味著優(yōu)化過程總是試圖為每個查詢制定一個降低總體成本的訪問路徑。為了實現(xiàn)這一點，優(yōu)化器應(yīng)用查詢成本公式來評估和權(quán)衡每個潛在訪問路徑的多個因素：這些因素包括CPU成本、I/O操作、系統(tǒng)編目中的統(tǒng)計信息以及實際的SQL語句代碼。

優(yōu)化器可以重寫查詢，將其轉(zhuǎn)換為等效的、但更容易編譯和優(yōu)化的版本。謂詞下推和轉(zhuǎn)換可能在此時發(fā)生。然后優(yōu)化SQL。將審查和分析多條訪問路徑，以選擇成本最低的選項。最后一步是創(chuàng)建實際的可執(zhí)行代碼。

關(guān)系優(yōu)化器有許多創(chuàng)建SQL訪問路徑的選項。在較高的層次上，有訪問單個表中的數(shù)據(jù)的方法，也有組合兩個表中的數(shù)據(jù)的方法。可以將這些方法組合成一系列訪問方法，為SQL語句創(chuàng)建總體訪問路徑。

對于單表訪問，可以使用掃描或索引檢索數(shù)據(jù)。在優(yōu)化器確定每個謂詞可用的索引之后，它將決定是使用單個索引、多個索引還是根本不使用索引。

大家很容易說索引訪問將優(yōu)于掃描訪問，但事實并非總是如此。優(yōu)化器必須評估必須訪問的數(shù)據(jù)量以及查詢的性質(zhì)。例如，如果你正在創(chuàng)建一個包含表中每一行的報告，那么使用索引可能比使用掃描讀取所有數(shù)據(jù)要慢。

表掃描是最簡單的數(shù)據(jù)訪問形式。表掃描是通過讀取表的每一行來執(zhí)行的。根據(jù)DBMS的不同，可能存在另一種掃描類型，稱為表空間掃描。表空間掃描讀取表空間中的每個頁面，表空間可能包含多個表。顯然，表空間掃描將比表掃描運行得慢，因為可能會產(chǎn)生額外的I/O讀取不適用的數(shù)據(jù)。

另一種掃描形式是分區(qū)掃描。如果DBMS能夠確定要訪問的數(shù)據(jù)存在于多分區(qū)表（或表空間）的某些分區(qū)中，那么它可以將掃描到的數(shù)據(jù)限制到適當(dāng)?shù)姆謪^(qū)。分區(qū)掃描應(yīng)該優(yōu)于表掃描或表空間掃描，因為所需的I/O數(shù)量減少了。

通常，優(yōu)化器會選擇掃描數(shù)據(jù)，原因如下之一：

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

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

• 具有匹配謂詞的索引具有較低的集群比率，并且僅對少量數(shù)據(jù)有效。

• 表太小，使用索引實際上是有害的。對于小表，向表訪問添加索引訪問可能會導(dǎo)致額外的I/O，而不是更少的I/O。

為了幫助掃描的性能，優(yōu)化器可以調(diào)用數(shù)據(jù)預(yù)取。數(shù)據(jù)預(yù)取會導(dǎo)致DBMS在請求數(shù)據(jù)頁之前，按順序?qū)?shù)據(jù)頁讀入數(shù)據(jù)緩存。從本質(zhì)上說，數(shù)據(jù)預(yù)取是一種讀前機制——當(dāng)數(shù)據(jù)掃描開始請求數(shù)據(jù)時，它已經(jīng)存在于內(nèi)存中。Prefetch對于掃描特別有用，但是對于任何類型的順序數(shù)據(jù)訪問都是實用的。你應(yīng)該了解特定DBMS如何以及為什么預(yù)取數(shù)據(jù)。

大多數(shù)的訪問應(yīng)該使用索引，這使我們可以選擇掃描或索引訪問。優(yōu)化器必須首先發(fā)現(xiàn)是否存在索引。在編寫SQL來訪問列之前，不必定義索引—你可以查詢數(shù)據(jù)庫所知道的任何表的任何列。 

此外，必須在SQL語句中的可索引謂詞中引用至少一個索引列。DBMS不能為每個WHERE子句使用索引。您必須了解謂詞可以使用哪些類型的索引來確保為數(shù)據(jù)庫應(yīng)用程序中的查詢創(chuàng)建適當(dāng)?shù)乃饕?。每個數(shù)據(jù)庫管理系統(tǒng)都有一個不同的列表，其中列出了什么是可索引的，什么是不可索引的。此外，可索引的內(nèi)容往往會隨著每個DBMS的版本而變化。

優(yōu)化器可以選擇以許多不同的方式使用索引。第一個也是最簡單的索引訪問類型是直接索引查找。為了使DBMS能夠執(zhí)行直接索引查找，必須為索引中的每一列提供值。為了執(zhí)行直接索引查找，DBMS將謂詞中請求的值與索引根頁中存儲的值進行比較。基于這種比較，DBMS將把索引遍歷到下一個頁面集。如果存在中間的非葉頁，則讀取適當(dāng)?shù)姆侨~頁，并比較該值以確定要訪問哪個葉頁。閱讀適當(dāng)?shù)捻摚凰饕摪赶蚍蠗l件的行實際數(shù)據(jù)的指針?；陧撍饕龡l目中的指針，DBMS讀取適當(dāng)?shù)谋頂?shù)據(jù)頁。

但是，假設(shè)SQL語句中沒有提供索引的所有列。不能選擇直接索引查找，因為DBMS不能匹配完整的索引鍵。相反，可以選擇索引掃描。當(dāng)一個索引掃描被調(diào)用時，索引的頁被依次讀取。

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

要使用匹配的索引掃描，必須在索引鍵中指定高階列;即索引DDL中指定的第一列。高階列為DBMS從根頁面到適當(dāng)?shù)娜~頁面遍歷索引結(jié)構(gòu)提供了起點。

請考慮在查詢中不指定高階列的后果。DBMS可以部署不匹配的索引掃描，有時稱為相對定位。當(dāng)由于索引鍵中的第一列未指定而無法確定起始點時，DBMS不能使用索引樹結(jié)構(gòu)。但是，它可以掃描索引頁。不匹配的索引掃描從索引中的第一個頁開始，然后應(yīng)用可用的謂詞順序掃描后續(xù)的頁。

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