SQL最佳化--邏輯最佳化--非SPJ最佳化，sqlspj

SQL最佳化--邏輯最佳化--非SPJ最佳化，sqlspj

1）GROUP BY分組轉換（MySQL不支援）

①分組操作下移

GROUPBY 操作可能較大幅度減小關係元組的個數，如果能夠對某個關係先進行分組操作，然後再進行表之間的串連，很可能提高串連效率。這種最佳化方式是把分組操作提前執行。下移的含義，是在查詢樹上，讓分組操作盡量靠近葉子結點，使得分組操作的結點低於一些選擇操作。

②分組操作上移

如果串連操作能夠過濾掉大部分元組，則先進行串連後進行GROUPBY 操作，可能提高分組操作的效率。這種最佳化方式是把分組操作置後執行。上移的含義，和下移正好相反。

對於帶有 GROUPBY 等操作的非 SPJ 格式的 SQL 陳述式，在本節之前提及的技術都適用，只是結合了 GROUPBY 操作的語義進行分組操作。因為 GROUPBY 操作下移或上移均不能保證重寫後的查詢效率一定更好，所以，要在查詢最佳化工具中採用基於代價的方式來估算某幾種路徑的優劣。

③MySQL的GROUP BY最佳化

MySQL 對於 GROUPBY 的處理，通常採用的方式是掃描整個表、建立一個暫存資料表用以執行分組操作。查詢執行計畫中出現“ Usingtemporary” 字樣表示 MySQL 採用了常規的處理方式。對於 GROUPBY 的最佳化，則盡量利用索引。

2）ORDER BY 最佳化

①排序消除（ Order By Elimination ， OBYE ）

最佳化器在產生執行計畫前，將語句中沒有必要的排序操作消除（如利用索引），避免在執行計畫中出現排序操作或由排序導致的操作（如在索引列上排序，可以利用索引消除排序操作）。

②排序下推（ Sort push down ）

把排序操作盡量下推到基表中，有序的基表進行串連後的結果符合排序的語義，這樣能避免在最終的大的串連結果集上執行排序操作。

③MySQL的GROUP BY最佳化

利用索引的條件是：分組子句中的列對象源自同一個 btree 索引（不支援利用Hash 索引進行最佳化）的全部或首碼部分的部分有序的鍵（分組使用的索引列與索引建立的順序不匹配則不能使用索引）。

主要的方式有 :

a）Loose IndexScan 

直接用索引完成分組操作中對分組列的檢索，不必考慮索引的全部鍵滿足 WHERE 子句，只要有部分匹配 WHERE 中的列對象即可（ loose ，利用索引中部分列為“鬆散”）。

b）Tight IndexScan 

索引中的全部鍵與 WHERE 子句中的列對象匹配（ tight ，利用索引中的全部列為“嚴密”）。

3）DISTINCT 最佳化

①DISTINCT 消除（ Distinct Elimination ）（MySQL支援）

如果表中存在主鍵、唯一約束、索引等，則可以消除查詢語句中的 DISTINCT （這種最佳化方式，在語義最佳化中也涉及，本質上是語義最佳化研究的範疇）。

②DISTINCT 推入（ Distinct Push Down ）（MySQL不支援）

產生含 DISTINCT 的反半串連查詢執行計畫時，先進行反半串連再進行 DISTICT 操作；也許先執行 DISTICT 操作然後再執行反半串連，可能更優；這是利用串連語義上確保唯一功能特性進行 DISTINCT 的最佳化。

③DISTINCT 遷移（ Distinct Placement ）（MySQL不支援）

對串連操作的結果執行 DISTINCT ，可能把 DISTINCT 移到一個子查詢中優先進行（有的書籍把這項技術稱為“ DISTINCT 配置”）。

4）LIMIT最佳化

①MySQL的LIMIT最佳化

a）LIMIT對單表掃描的影響：如果索引掃描可用且花費低於全表掃描，則用索引掃描實現 LIMIT （ LIMIT 取很少量的行，否則最佳化器更傾向於使用全表掃描）。

b）LIMIT對排序的影響：如果 LIMIT 和 ORDERBY子句協同使用，當取到 LIMIT設定個數的有序元組數後，後續的排序操作將不再進行。

c）LIMIT對去重的影響：如果 LIMIT 和 DISTINCT子句協同使用，當取到 LIMIT設定個數的唯一的元組數後，後續的去重操作將不再進行。

d）LIMIT受分組的影響：如果 LIMIT 和 GROUPBY子句協同使用， GROUPBY 按索引有序計算每個組的總數的過程中，LIMIT 操作不必計數直到下一個分組開始計算。

e）LIMIT 0 ：直接返回空結果集。

f）MySQL 支援對不帶 HAVING 子句的 LIMIT 進行最佳化。

5）集合操作最佳化

①MySQL 的集合操作最佳化

a）MySQL 文法：

SELECT …

UNION [ALL |DISTINCT]SELECT …

[UNION [ALL |DISTINCT]SELECT …]

b）查詢重寫規則： OR 重寫並集規則  ---MySQL 不支援。

c）需要引入代價估算的去評估重寫後的代價，比較複雜。

6）總結

①常見的啟發學習法規則

a）嵌套串連消除 : 如果都是內串連 , 則可以把表示嵌套關係的括弧去掉。

A join (B join C) == A join B join C

b）選擇操作下推。

c）投影操作下推 。

②常見的經驗規則

a）在索引鍵上執行排序操作 , 通常利用索引的有序性按序讀取資料而不進行排序。

b）選擇率低於 10% 時 , 利用索引的效果通常比讀表資料的效果好。

c）當表的資料量較少時 , 全表掃描可能優於其它方式 ( 如利用索引的方式 )。

摘自《資料庫查詢最佳化工具的藝術》一書