MySQL如何優化

7.2.9. MySQL如何優化LEFT JOIN和RIGHT JOIN

在MySQL中，A LEFT JOIN B join_condition執行過程如下：

·         根據表A和A依賴的所有表設置表B。

·         根據LEFT JOIN條件中使用的所有表(除了B)設置表A。

·         LEFT JOIN條件用於確定如何從表B搜索行。(換句話說，不使用WHERE子句中的任何條件）。

·         可以對所有標准聯接進行優化，只是只有從它所依賴的所有表讀取的表例外。如果出現循環依賴關系，MySQL提示出現一個錯誤。

·         進行所有標准WHERE優化。

·         如果A中有一行匹配WHERE子句，但B中沒有一行匹配ON條件，則生成另一個B行，其中所有列設置為NULL。

·         如果使用LEFT JOIN找出在某些表中不存在的行，並且進行了下面的測試：WHERE部分的col_name IS NULL，其中col_name是一個聲明為 NOT NULL的列，MySQL找到匹配LEFT JOIN條件的一個行後停止(為具體的關鍵字組合)搜索其它行。

RIGHT JOIN的執行類似LEFT JOIN，只是表的角色反過來。

聯接優化器計算表應聯接的順序。LEFT JOIN和STRAIGHT_JOIN強制的表讀順序可以幫助聯接優化器更快地工作，因為檢查的表交換更少。請注意這說明如果執行下面類型的查詢，MySQL進行全掃描b，因為LEFT JOIN強制它在d之前讀取：

SELECT *

    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

    WHERE b.key=d.key;

在這種情況下修復時用a的相反順序，b列於FROM子句中：

SELECT *

    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

    WHERE b.key=d.key;

MySQL可以進行下面的LEFT JOIN優化：如果對於產生的NULL行，WHERE條件總為假，LEFT JOIN變為普通聯接。

例如，在下面的查詢中如果t2.column1為NULL，WHERE 子句將為false：

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，可以安全地將查詢轉換為普通聯接：

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

這樣可以更快，因為如果可以使查詢更佳，MySQL可以在表t1之前使用表t2。為了強制使用表順序，使用STRAIGHT_JOIN。

7.2.10. MySQL如何優化嵌套Join

表示聯接的語法允許嵌套聯接。下面的討論引用了13.2.7.1節，“JOIN語法”中描述的聯接語法。

同SQL標准比較，table_factor語法已經擴展了。後者只接受table_reference，而不是括號內所列的。

table_reference項列表內的每個逗號等價於內部聯接，這是一個保留擴展名。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

等價於：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在MySQL中，CROSS JOIN語法上等價於INNER JOIN (它們可以彼此代替。在標准SQL中，它們不等價。INNER JOIN結合ON子句使用；CROSS JOIN 用於其它地方。

總的來說，在只包含內部聯接操作的聯接表達式中可以忽略括號。刪除括號並將操作組合到左側後，聯接表達式：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)

   ON t1.a=t2.a

轉換為表達式：

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3

    ON t2.b=t3.b OR t2.b IS NULL

但是這兩個表達式不等效。要說明這點，假定表t1、t2和t3有下面的狀態：

·         表t1包含行{1}、{2}

·         表t2包含行{1,101}

·         表t3包含行{101}

在這種情況下，第1個表達式返回包括行{1,1,101,101}、{2,NULL,NULL,NULL}的結果，第2個表達式返回行{1,1,101,101}、{2,NULL,NULL,101}：

mysql> SELECT *

    -> FROM t1

    ->      LEFT JOIN

    ->      (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)

    ->      ON t1.a=t2.a;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL | NULL |

+------+------+------+------+

mysql> SELECT *

    -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)

    ->      LEFT JOIN t3

    ->      ON t2.b=t3.b OR t2.b IS NULL;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL |  101 |

+------+------+------+------+

在下面的例子中，外面的聯接操作結合內部聯接操作使用：

t1 LEFT JOIN (t2，t3) ON t1.a=t2.a

該表達式不能轉換為下面的表達式：

t1 LEFT JOIN t2 ON t1.a=t2.a，t3.

對於給定的表狀態，第1個表達式返回行{1,1,101,101}、{2,NULL,NULL,NULL}，第2個表達式返回行{1,1,101,101}、{2,NULL,NULL,101}：

mysql> SELECT *

    -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL | NULL |

+------+------+------+------+

mysql> SELECT *

    -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;

+------+------+------+------+

| a    | a    | b    | b    |

+------+------+------+------+

|    1 |    1 |  101 |  101 |

|    2 | NULL | NULL |  101 |

+------+------+------+------+

因此，如果我們忽略聯接表達式中的括號連同外面的聯接操作符，我們會改變原表達式的結果。

更確切地說，我們不能忽視左外聯接操作的右操作數和右聯接操作的左操作數中的括號。換句話說，我們不能忽視外聯接操作中的內表達式中的括號。可以忽視其它操作數中的括號(外部表的操作數)。

對於任何表t1、t2、t3和屬性t2.b和t3.b的任何條件P，下面的表達式：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

等價於表達式

t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b)

如果聯接表達式(join_table)中的聯接操作的執行順序不是從左到右，我們則應討論嵌套的聯接。這樣，下面的查詢：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a

  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

聯接表：

t2 LEFT JOIN t3 ON t2.b=t3.b

t2, t3

認為是嵌套的。第1個查詢結合左聯接操作則形成嵌套的聯接，而在第二個查詢中結合內聯接操作形成嵌套聯接。

在第1個查詢中，括號可以忽略：聯接表達式的語法結構與聯接操作的執行順序相同。但對於第2個查詢，括號不能省略，盡管如果沒有括號，這裡的聯接表達式解釋不清楚。(在外部擴展語法中，需要第2個查詢的(t2，t3)的括號，盡管從理論上對查詢分析時不需要括號：這些查詢的語法結構將仍然不清楚，因為LEFT JOIN和ON將充當表達式(t2,t3)的左、右界定符的角色)。

前面的例子說明了這些點：

·         對於只包含內聯接(而非外聯接)的聯接表達式，可以刪除括號。你可以移除括號並從左到右評估(或實際上，你可以按任何順序評估表)。

·         總的來說，對外聯接卻不是這樣。去除括號可能會更改結果。

·         總的來說，對外聯接和內聯接的結合，也不是這樣。去除括號可能會更改結果。

含嵌套外聯接的查詢按含內聯接的查詢的相同的管道方式執行。更確切地說，利用了嵌套環聯接算法。讓我們回憶嵌套環聯接執行查詢時采用什麼算法。

假定我們有一個如下形式的表T1、T2、T3的聯接查詢：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)

                 INNER JOIN T3 ON P2(T2,T3)

  WHERE P(T1,T2,T3).

這裡，P1(T1,T2)和P2(T3,T3)是一些聯接條件(表達式)，其中P(t1,t2,t3)是表T1、T2、T3的列的一個條件。

嵌套環聯接算法將按下面的方式執行該查詢：

 

FOR each row t1 in T1 {

  FOR each row t2 in T2 such that P1(t1,t2) {

    FOR each row t3 in T3 such that P2(t2,t3) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

符號t1||t2||t3表示“連接行t1、t2和t3的列組成的行”。在下面的一些例子中，出現行名的NULL表示NULL用於行的每個列。例如，t1||t2||NULL表示“連接行t1和t2的列以及t3的每個列的NULL組成的行”。

現在讓我們考慮帶嵌套的外聯接的查詢：

SELECT * FROM T1 LEFT JOIN

              (T2 LEFT JOIN T3 ON P2(T2,T3))

              ON P1(T1,T2)

  WHERE P(T1,T2,T3)。

對於該查詢我們修改嵌套環模式可以得到：

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t2 in T2 such that P1(t1,t2) {

    BOOL f2:=FALSE;

    FOR each row t3 in T3 such that P2(t2,t3) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f2=TRUE;

      f1=TRUE;

    }

    IF (!f2) {

      IF P(t1,t2,NULL) {

        t:=t1||t2||NULL; OUTPUT t;

      }

      f1=TRUE;

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

總的來說，對於外聯接操作中的第一個內表的嵌套環，引入了一個標志，在環之前關閉並且在環之後打開。如果對於外部表的當前行，如果匹配表示內操作數的表，則標志打開。如果在循環結尾處標志仍然關閉，則對於外部表的當前行，沒有發現匹配。在這種情況下，對於內表的列，應使用NULL值補充行。結果行被傳遞到輸出進行最終檢查或傳遞到下一個嵌套環，但只能在行滿足所有嵌入式外聯接的聯接條件時。

在我們的例子中，嵌入了下面表達式表示的外聯接表：

(T2 LEFT JOIN T3 ON P2(T2,T3))

請注意對於有內聯接的查詢，優化器可以選擇不同的嵌套環順序，例如：

FOR each row t3 in T3 {

  FOR each row t2 in T2 such that P2(t2,t3) {

    FOR each row t1 in T1 such that P1(t1,t2) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

對於有外聯接的查詢，優化器可以只選擇這樣的順序：外表的環優先於內表的環。這樣，對於有外聯接的查詢，只可能有一種嵌套順序。在下面的查詢中，優化器將評估兩個不同的嵌套：

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)

  WHERE P(T1,T2,T3)

嵌套為：

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t2 in T2 such that P1(t1,t2) {

    FOR each row t3 in T3 such that P2(t1,t3) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f1:=TRUE

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

和

FOR each row t1 in T1 {

  BOOL f1:=FALSE;

  FOR each row t3 in T3 such that P2(t1,t3) {

    FOR each row t2 in T2 such that P1(t1,t2) {

      IF P(t1,t2,t3) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f1:=TRUE

    }

  }

  IF (!f1) {

    IF P(t1,NULL,NULL) {

      t:=t1||NULL||NULL; OUTPUT t;

    }

  }

}

在兩個嵌套中，必須在外環中處理T1，因為它用於外聯接中。T2和T3用於內聯接中，因此聯接必須在內環中處理。但是，因為該聯接是一個內聯接，T2和T3可以以任何順序處理。

當討論內聯接嵌套環的算法時，我們忽略了部分詳情，可能對查詢執行的性能的影響會很大。我們沒有提及所謂的“下推”條件。假定可以用連接公式表示我們的WHERE條件P(T1,T2,T3)：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)。

在這種情況下，MySQL實際使用了下面的嵌套環方案來執行帶內聯接得到查詢：

FOR each row t1 in T1 such that C1(t1) {

  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {

    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {

      IF P(t1,t2,t3) {

         t:=t1||t2||t3; OUTPUT t;

      }

    }

  }

}

你會看見每個連接 C1(T1)，C2(T2)，C3(T3)被從最內部的環內推出到可以對它進行評估的最外的環中。如果C1(T1)是一個限制性很強的條件，下推條件可以大大降低從表T1傳遞到內環的行數。結果是查詢大大加速。

對於有外聯接的查詢，只有查出外表的當前的行可以匹配內表後，才可以檢查WHERE條件。這樣，對內嵌套環下推的條件不能直接用於帶外聯接的查詢。這裡我們必須引入有條件下推前提，由遇到匹配後打開的標志保護。

對於帶下面的外聯接的例子

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

使用受保護的下推條件的嵌套環方案看起來應為：

FOR each row t1 in T1 such that C1(t1) {

  BOOL f1:=FALSE;

  FOR each row t2 in T2

      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {

    BOOL f2:=FALSE;

    FOR each row t3 in T3

        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {

      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {

        t:=t1||t2||t3; OUTPUT t;

      }

      f2=TRUE;

      f1=TRUE;

    }

    IF (!f2) {

      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {

        t:=t1||t2||NULL; OUTPUT t;

      }

      f1=TRUE;

    }

  }

  IF (!f1 && P(t1,NULL,NULL)) {

      t:=t1||NULL||NULL; OUTPUT t;

  }

}

總的來說，可以從聯接條件（例如P1(T1,T2)和P(T2,T3)）提取下推前提。在這種情況下，下推前提也受一個標志保護，防止檢查由相應外聯接操作所產生的NULL-補充的行的斷言。

請注意如果從判斷式的WHERE條件推導出，根據從一個內表到相同嵌套聯接的另一個表的關鍵字進行的訪問被禁止。(在這種情況下，我們可以使用有條件關鍵字訪問，但是該技術還未用於MySQL 5.1中）。