SQL最佳化--邏輯最佳化--外串連、嵌套串連與串連消除，sql嵌套

SQL最佳化--邏輯最佳化--外串連、嵌套串連與串連消除，sql嵌套

1）外串連消除

①外串連簡介

1）LEFT JOIN / LEFT OUTER JOIN：左外串連    

左向外串連的結果集包括：LEFT OUTER子句中指定的左表的所有行，而不僅僅是串連列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有挑選清單列均為空白值。       

2）RIGHT JOIN / RIGHT  OUTER  JOIN：右外串連     

右向外串連是左向外聯結的反向串連。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。       

3）FULL JOIN / FULL OUTER JOIN：全外串連

全外串連返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的挑選清單列包含空值。如果表之間有匹配行，則整個結果集行包含基表的資料值。 

②外串連消除

把外串連變為內串連   A OUTER JOIN B   變形為   A JOIN B

③外串連消除的意義

a）查詢最佳化工具在處理外串連操作時所需執行的操作和時間多於內串連。

b）外串連消除後，最佳化器在選擇多表串連順序時，可以有更多更靈活的選擇，從而可以選擇更好的表串連順序，加快查詢執行的速度。

c）表的一些串連演算法（如塊嵌套串連和索引迴圈串連等）在將規模小的或篩選條件最嚴格的表作為“外表”（放在串連順序的最前面，是多層迴圈體的外迴圈層），可以減少不必要的I/O開銷，能加快演算法執行的速度。

④外串連消除的條件

WHERE子句中的條件滿足“空值拒絕”（又稱為“reject-NULL”條件）。

WHERE條件可以保證從結果中排除外串連右側（右表）產生的值為NULL的行（即條件確保應用在右錶帶有空值的列對象上時，條件不滿足，條件的結果值為FLASE或UNKONOWEN，這樣右表就不會有值為NULL的行產生），所以能使該查詢在語義上等效於內串連。

explain  SELECT * FROM X LEFT JOIN Y ON (X.X_num=Y.Y_num) 

WHERE Y.Y_num IS NOT NULL;

⑤樣本

a）辨析ON和WHERE的差異

ON    t_1_id = t_2_id：t_1_id 和 t_2_id 進行串連

WHERE t_1_id = t_2_id：當t_1_id 和 t_2_id的值相等

⑥外串連消除總結

a）注意外串連與內串連的語義差別

b）外串連最佳化的條件：空值拒絕

c）外串連最佳化的本質：語義上是外串連，但WHER條件使得外串連可以蛻化為內串連

2）串連消除

①情況一（MySQL不支援）

唯一鍵/主鍵作為串連條件，三表內串連可以去掉中間表（中間表的列只作為串連條件）。

②情況二（MySQL不支援）

一些特殊形式，可以消除串連操作（可消除的表除了作為連線物件外，不出現在任何子句中）。

③情況三（MySQL不支援）

主外鍵關係的表進行的串連，可消除主鍵表，這不會影響對外鍵表的查詢。

④總結

a）注意串連消除與外串連消除的技術差別。

b）串連消除去掉的是被串連的某個對象。

c）外串連消除去掉的是外串連的語義，變形為內串連。

3）嵌套串連消除

①概念

串連存在多個層次，用括弧標識串連的優先次序。嵌套串連消除，就是消除嵌套的串連層次，把多個層次的串連減少為較少層次的串連，盡量“扁平化”。

②總結

a）嵌套串連消除的串連的層次,這是一種串連的語義順序的變化。

b）串連消除，消掉的是一些被串連的對象。

c）外串連消除，消掉的是外串連的語義，使得外串連變形為內串連。

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