MySQL如何簡化外部聯合

MySQL如何簡化外部聯合

在許多情況下，一個查詢的FROM子句的表的運算式可以簡化。

在分析階段，帶右外聯結操作的查詢被轉換為只包含左聯結操作的等效查詢。總的來說，根據以下原則進行轉換：

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =

(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

所有T1 INNER JOIN T2 ON P(T1,T2)形式的內聯結運算式被替換為T1,T2、P(T1,T2)，並根據WHERE條件(或嵌入串連的聯結條件，如果有)聯結為一個串連。

當最佳化器為用外聯結操作的聯結查詢評估方案時，它只考慮在訪問內表之前訪問外表的操作的方案。最佳化器選項受到限制，因為只有這樣的方案允許我們用嵌套環機制執行帶外聯結操作的查詢。

假定我們有一個下列形式的查詢：

SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)

  WHERE P(T1,T2) AND R(T2)

R(T2)大大減少了表T2中匹配的行數。如果我們這樣執行查詢，最佳化器將不會有其它選擇，只能在訪問表T2之前訪問表T1，從而導致執行方案非常低。

幸運的是，如果WHERE條件拒絕null，MySQL可以將此類查詢轉換為沒有外聯結操作的查詢。如果為該操作構建的NULL補充的行評估為FALSE或UNKNOWN，則該條件稱為對於某個外聯結操作拒絕null。

因此，對於該外聯結：

T1 LEFT JOIN T2 ON T1.A=T2.A

類似下面的條件為拒絕null：

T2.B IS NOT NULL,

T2.B > 3,

T2.C <= T1.C,

T2.B < 2 OR T2.C > 1

類似下面的條件不為拒絕null：

T2.B IS NULL,

T1.B < 3 OR T2.B IS NOT NULL,

T1.B < 3 OR T2.B > 3

檢查一個外聯結操作的條件是否拒絕null的總原則很簡單。以下情況下為拒絕null的條件：

·         形式為A IS NOT NULL，其中A是任何內表的一個屬性

·         包含內表引用的判斷式，當某個參量為NULL時評估為UNKNOWN

·         包含用於串連的拒絕null的條件的聯合

·         拒絕null的條件的邏輯和

一個條件可以對於一個查詢中的一個外聯結操作為拒絕null的而對於另一個不為拒絕null的。在下面的查詢中：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

                 LEFT JOIN T3 ON T3.B=T1.B

  WHERE T3.C > 0

WHERE條件對於第2個外聯結操作為拒絕null的但對於第1個不為拒絕null的。

如果WHERE條件對於一個查詢中的一個外聯結操作為拒絕null的，外聯結操作被一個內聯結操作代替。

例如，前面的查詢被下面的查詢代替：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

                 INNER JOIN T3 ON T3.B=T1.B

  WHERE T3.C > 0

對於原來的查詢，最佳化器將評估只與一個訪問順序T1、T2、T3相容的方案。在替換的查詢中，還考慮了訪問順序T3、T1、T2。

一個外聯結操作的轉化可以觸發另一個的轉化。這樣，查詢：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

                 LEFT JOIN T3 ON T3.B=T2.B

  WHERE T3.C > 0

將首先轉換為查詢：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A

                 INNER JOIN T3 ON T3.B=T2.B

  WHERE T3.C > 0

該查詢等效於查詢：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3

  WHERE T3.C > 0 AND T3.B=T2.B

現在剩餘的外聯結操作也可以被一個內聯結替換，因為條件T3.B=T2.B為拒絕null的，我們可以得到一個根本沒有外聯結的查詢：

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3

  WHERE T3.C > 0 AND T3.B=T2.B

有時我們可以成功替換嵌入的外聯結操作，但不能轉換嵌入的外聯結。下面的查詢：

SELECT * FROM T1 LEFT JOIN

              (T2 LEFT JOIN T3 ON T3.B=T2.B)

              ON T2.A=T1.A

  WHERE T3.C > 0

被轉換為：

SELECT * FROM T1 LEFT JOIN

              (T2 INNER JOIN T3 ON T3.B=T2.B)

              ON T2.A=T1.A

  WHERE T3.C > 0，

只能重新寫為仍然包含嵌入式外聯結操作的形式：

SELECT * FROM T1 LEFT JOIN

              (T2,T3)

              ON (T2.A=T1.A AND T3.B=T2.B)

  WHERE T3.C > 0。

如果試圖轉換一個查詢中的嵌入式外聯結操作，我們必須考慮嵌入式外聯結的聯結條件和WHERE條件。在下面的查詢中：

SELECT * FROM T1 LEFT JOIN

              (T2 LEFT JOIN T3 ON T3.B=T2.B)

              ON T2.A=T1.A AND T3.C=T1.C

  WHERE T3.D > 0 OR T1.D > 0

WHERE條件對於嵌入式外聯結不為拒絕null的，但嵌入式外聯結T2.A=T1.A AND T3.C=T1.C的聯結條件為拒絕null的。因此該查詢可以轉換為：

SELECT * FROM T1 LEFT JOIN

              (T2, T3)

              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B

  WHERE T3.D > 0 OR T1.D > 0

7.2.12. MySQL如何最佳化ORDER BY

在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。

即使ORDER BY不確切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有額外的ORDER BY 列為常數，就可以使用索引。下面的查詢使用索引來解決ORDER BY部分：

SELECT * FROM t1

    ORDER BY key_part1,key_part2,... ;

   

SELECT * FROM t1

    WHERE key_part1=constant

    ORDER BY key_part2;

   

SELECT * FROM t1

    ORDER BY key_part1 DESC, key_part2 DESC;

   

SELECT * FROM t1

    WHERE key_part1=1

    ORDER BY key_part1 DESC, key_part2 DESC;

在某些情況下，MySQL不能使用索引來解決ORDER BY，儘管它仍然使用索引來找到匹配WHERE子句的行。這些情況包括：

·         對不同的關鍵字使用ORDER BY：

·                SELECT * FROM t1 ORDER BY key1, key2；

·         對關鍵字的非連續元素使用ORDER BY：

·                SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2；

·         混合ASC和DESC：

·                SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

·         用於查詢行的關鍵字與ORDER BY中所使用的不相同：

·                SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

·         你正聯結許多表，並且ORDER BY中的列並不是全部來自第1個用於搜尋行的非常量表。(這是EXPLAIN輸出中的沒有const聯結類型的第1個表）。

·         有不同的ORDER BY和GROUP BY運算式。

·         使用的表索引的類型不能按順序儲存行。例如，對於HEAP表的HASH索引情況即如此。

通過EXPLAIN SELECT ...ORDER BY，可以檢查MySQL是否可以使用索引來解決查詢。如果Extra列內有Using filesort，則不能解決查詢。參見7.2.1節，“EXPLAIN文法（擷取關於SELECT的資訊）”。

檔案排序最佳化不僅用於記錄排序關鍵字和行的位置，並且還記錄查詢需要的列。這樣可以避免兩次讀取行。檔案排序演算法的工作象這樣：

1.    讀行匹配WHERE子句的行，如前面所示。

2.    對於每個行，記錄構成排序關鍵字和行位置的一系列值，並且記錄查詢需要的列。

3.    根據排序關鍵字排序元組

4.    按排序的順序檢索行，但直接從排序的元組讀取需要的列，而不是再一次訪問表。

該演算法比以前版本的Mysql有很大的改進。

為了避免速度變慢，該最佳化只用於排序元組中的extra列的總大小不超過max_length_for_sort_data系統變數值的時候。(將該變數設定得太高的的跡象是將看到硬碟活動太頻繁而CPU活動較低）。

如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試下面的策略：

·         增加sort_buffer_size變數的大小。

·         增加read_rnd_buffer_size變數的大小。

·         更改tmpdir指向具有大量空閑空間的專用檔案系統。該選項接受幾個使用round-robin(迴圈)模式的路徑。在Unix中路徑應用冒號(‘:’)區間開，在Windows、NetWare和OS/2中用分號(‘；’)。可以使用該特性將負載均分到幾個目錄中。注釋：路徑應為位於不同物理硬碟上的檔案系統的目錄，而不是同一硬碟的不同的分區。

預設情況下，MySQL排序所有GROUP BY col1，col2，...查詢的方法如同在查詢中指定ORDER BY col1，col2，...。如果顯式包括一個包含相同的列的ORDER BY子句，MySQL可以毫不減速地對它進行最佳化，儘管仍然進行排序。如果查詢包括GROUP BY但你想要避免排序結果的消耗，你可以指定ORDER BY NULL禁止排序。例如：

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;