資料庫索引設計與優化（筆記）：第3章 SQL處理過程

謂詞

WHERE 字句由一個或多個“謂詞”組成。

WHERE SEX=’M’    AND  (WEIGHT>90 OR HEIGHT >190)

謂詞表達式是索引設計的主要入手點。

優化器及通路路徑

1. 優化器決定通路路徑。
2. 在查詢時，索引的一個窄的片段被順序掃描，相應的表行從表中讀取。是以，通路路徑的成本很大程度上取決于索引片的厚度，即謂詞表達式确定的值域範圍。

索引片越厚，則需要掃描的索引頁越多，需要處理的索引記錄也越多，而最大開銷還是來自于增加的對表的同步操作，每次表頁讀取需要10ms。

1. 另一種較為廣泛的描述索引片的方法是定義索引比對列的數量。如果WHERE中有多個列，而且這個列也在索引上，進而能使得多個列能夠一同定義一個更窄的索引片段。可有效減小索引的處理量，并減少對表的同步讀的次數。
2. 索引過濾及過濾列：并不是所有的索引列都能定義索引片的大小。不過這些列仍然能夠減小回表次數，還是有作用的，這些列稱為過濾列。下面是個例子：

如：索引列，A、B、C、D

查詢謂詞：WHERE A=:A AND B>:B AND C=:C

接下來分析：

謂詞A：索引A也是第一個，等值比對，A是比對列，可以用來定義索引片

謂詞B：B是一個範圍謂詞，它可以是比對列，但它之後的，就不能是比對列了。

謂詞C：基于上面一句，C不是比對列。它不能參與定義索引片大小，但能幫助避免不必要的回表通路。作為過濾列。

規律：比對列由範圍謂詞中斷，做不了比對列，還可以做過濾列。如果沒有B謂詞，那隻有列A是一個比對列，但C仍然可以用來做過濾。如果B的是等值表達式，則三個都可以作為比對列。如果沒有A謂詞，則B、C無法比對，索引片是整個索引，但B、C還是可以用來做過濾。

1. EXPLAIN：執行計劃。當發現一個慢SQL時，首先懷疑的應該是優化器，需要檢視執行計劃看通路方式是否正确。
2. 統計資訊：收集統計資訊，才能幫助優化器做更好的決策。收集資訊可包括基礎資訊（表記錄數、表頁數、葉子頁數、每個索引的聚簇綠、某些列或者列組的不同值得個數（基數），以及某些列的最大、最小值等）；還有一些可選統計資訊（如：列和列組值得分布情況）
3. 路徑選擇資源消耗不可忽略：每次SQL語句執行都進行一次通路路徑選擇要比僅做一次消耗更多資源。但并非綁定變量就一定好。比如WHERE SALARY>1000比WHERE SALARY >:SALARY更明确。
4. 過濾因子：即表中滿足謂詞條件的記錄行數占的比例，它主要依賴于列值的分布情況。

性别：過濾因子很大

CITY=:CITY使用平均過濾因子（1/不同CITY的個數）

評估一個索引是否合适時，最差情況下的過濾因子比平均過濾因子更重要，因為最差情況與最差輸入相關，此時查詢将消耗最長時間。

組合謂詞的過濾因子：如果組合謂詞之間沒有相關性，則過濾因子就是謂詞過濾因子的乘積。有相關性時，過濾因子會更低。

優化器在評估可選的通路路徑的成本時，必須先評估過濾因子。索引片大小就是比對組合謂詞的過濾因子*總行數。

一些教科書建議索引列順序按照基數的降序來排序。不完全對，就不用遵循了。

實體化結果

1. 實體化結果：執行必要的資料庫通路來建構結果集。最好情況，隻需要簡單地從資料庫緩沖池向應用程式傳回一條記錄、最差情況，需要發起大量的磁盤讀取。
2. 當SELECT語句隻查詢一條記錄時，優化器必須在SELECT請求被執行時就實體化記錄結果。
3. 當結果集可能有多條記錄而需要使用遊标時，有兩種選擇：

        第一：DBMS在OPEN CURSOR時物化整個結果集（或者至少在第一次FETCH時）

        第二：每次FETCH物化一條記錄。

1. 一次FETCH按照DCLARE CURSOR語句定義向應用程式傳回結果集的一條記錄。如果遊标聲明包含了綁定變量，則會在OPEN CURSOR調用之前，就把變量值傳遞進去。如果應用程式期望使用一個遊标擷取多個結果集，那麼首先需要執行一次CLOSE CURSOR調用，然後向綁定變量傳入新值，在執行OPEN CURSOR打開遊标。
2. 一般說法：DELARE CURSOR所定義的SQL語句總是在OPEN CURSOR調用的時刻傳值并組織結果集。但并非如此。方法1：DBMS盡可能晚地進行結果集物化。方法2：但如果先物化，那麼FETCH調用時，可從臨時表中檢索記錄，而在更新時，并不會更新臨時表中的記過。

方法1：一次FETCH調用物化一條記錄，條件

                   沒有排序需求

                    或者雖然有排序，但是：存在一個索引滿足結果集ORDER BY 排序需求、且優化器決定以傳統的方法使用這個索引：通路第一個索引并讀取相應的表，然後通路第二個。以此類推。

放法2：提前物化：主要就是有排序需求。對結果集排序，意味着即時隻提取第一條記錄，也必須物化整個結果集。