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;