MySQL JOIN查詢

文章目錄

•    1. MySQL SELECT處理流程
•    2. MySQL最佳化規則
•    3. 建議兼總結

    一般而言，如果要設計一個小型資料庫（指代碼量少），但又要適應海量資料及訪問的效能需求，最有效方法莫過於針對主要應用情境選擇一個或幾個效能優異的核心演算法作為引擎，然後努力將一些非主要應用情境作為該演算法的特例或變種植入到引擎當中。
    MySQL、PostgreSQL 等就是這麼做的。 在 MySQL 的 SELECT 查詢當中，其核心演算法就是 JOIN 查詢演算法。其他的查詢語句都相應向 JOIN 靠攏：單表查詢被當作 JOIN 的特例；子查詢被盡量轉換為 JOIN 查詢……
    這裡將從 MySQL 5.0 的原始碼入手，簡要分析 MySQL 處理 JOIN 查詢的流程和思路。

   1. MySQL SELECT處理流程

    是一個 SELECT SQL 傳到 MySQL 服務端以後經過的主要函數流程圖。圖上每個小矩形框內代表一個函數，箭頭的起點為調用者，終點為被調函數。箭頭指向一個大框，則表示調用者調用了一組函數，順序基本是從上向下。
    註：這裡所說的 SQL 都不包含 UNION 子句，因為 MySQL 用了單獨的 UNION 引擎來處理對應  SQL，而對於一般開發而言我們也很少使用 UNION 查詢。 

    在上面所有函數中，mysql_excute_command() 函數是 MySQL 處理各類 SQL 陳述式的統一入口。SQL 陳述式在經過簡單的文法解析以後，送到這裡，由該函數作進一步分析，並調用相應的handle介面作後續處理。對於 SELECT 相關的語句，主要調用 handle_select() 和 mysql_select() 兩個函數。
    handle_select() 可以處理 SELECT 中含有 UNION 子句的情況。在不含 UNION 的簡單結構中，也會直接調用 mysql_select() 函數。
    mysql_select() 函數就是不帶 UNION 子句的 SELECT 語句的進入點函數。通常狀況下，每次調用以後，它會依次調用 JOIN 類的 prepare() 、optimize() 、exec() 三個函數來完成 SELECT 語句的預先處理、最佳化、執行和結果輸出功能。
    JOIN::prepare() 是一個前處理函數。主要進行參數合法性檢查、文法分析並產生更準確的電腦描述、開啟記錄表、子查詢轉換等操作。
    JOIN::optimize() 是整個 SELECT 流程的關鍵所在，它負責對前面產生的各種描述結構進行各種最佳化。最佳化過程基於大量的規則進行，這些規則我們後面再詳細講述。JOIN::optimize() 調用的一系列函數我們不一一述及，也將各函數內的最佳化規則總結、概括到後面去一併講解。
    JOIN::exec() 也會進行一些運行時最佳化，這些最佳化過程會導致實際執行過程與 EXPLAIN 中顯示的不一致。但大多數情況下，JOIN::exec() 會遵照前面最佳化的過程執行，因此 JOIN 的流程也基本在 optimize() 中確定。
    JOIN::exec() 與 JOIN 最相關的部分是調用 do_select() 函數執行取資料的操作。do_select() 會調用 sub_select() 函數，該調用採用遞迴的方法將兩兩相鄰的表按照依賴關係進行歸併，逐步得到最終的結果集。
    結果集返回的操作也在 JOIN::exec() 中執行，或返回到暫存資料表，或輸入到檔案，或發送到 socket。這些不是我們關注的重點，因此也一筆帶過。

   2. MySQL最佳化規則

    MySQL 最佳化器的工作是基於規則設計的，如果規則存在缺陷，相應部分的應用也會有一些效能損失。與一些先進的大型資料庫不同，MySQL 的這些效能損失可能是永久的（對固定版本而言）。因為大型資料庫在執行過程中會對各種最佳化結果的執行情況進行統計評估以便自動改進後續的執行最佳化狀況，而 MySQL 目前沒有這些功能。因此，瞭解 MySQL 的最佳化規則，對於較好地設計 SQL 陳述式，提高執行效率有很大的指導意義。
    下面列出 MySQL 5 在處理SELECT查詢時設計的一些規則。

    規則1：如果操作只涉及常表，則去除 DISTINCT 子句；否則如果只有一個表，在以下情況下會將 DISTINCT 轉為 GROUP BY 查詢：

• GROUP BY 可以通過索引實現（不用對索引排序），ORDER BY 只需對選擇出的記錄集排序（該情況下，最佳化器會對 GROUP BY 和 ORDER BY 進行額外的最佳化）。
• 沒有使用 LIMIT，所以要作全表掃描。

    全表掃描發生的情況通常為以下兩種：

• 查詢中使用了 SQL_CALC_FOUND_ROWS。
• 使用的 ORDER BY 子句無法最佳化。

    當 SELECT 語句包含了 LIMIT 子句（這裡和後文提及 LIMIT 子句的時候，預設是沒有 SQL_CALC_FOUND_ROWS 子句存在的情況）時，最佳化器將不使用這一最佳化規則，因為該情況下最佳化器將建立暫存資料表放置 LIMIT 所限制的記錄數，然後返回。
    註：LIMIT 子句跟 DISTINCT、GROUP BY、ORDER BY等子句共存的狀況比較複雜。此時使用 LIMIT 子句除了減少了發送記錄過程中的耗時以外，通常不應期望有更多的速度提高。因為後面三個子句中的任一個都可能會使得不管是否存在 LIMIT 子句都要做同樣多，甚至更多一點點的計算。 

    這裡順便介紹常表的概念。 所謂常表，包括以下類型：

• 一個沒有記錄或只有一行記錄的表。
• 一個表的運算式受 WHERE 子句限制，運算式形式為“column = constant”，並且該 column 是該表的 PRIMARY KEY 或 UNIQUE KEY（假設該 UNIQUE 列同時被定義為  NOT NULL）。

    規則2：最佳化器在以下情況會考慮建立暫存資料表：

• SELECT 語句中存在 DISTINCT 子句（基於準則1可以最佳化掉的 DISTINCT 已經最佳化掉了）。
• 對錶鏈（table list）中第二及後面的表施加了 ORDER BY 或 GROUP BY 操作。
• 使用了不同的 ORDER BY 和 GROUP BY 順序，或排序操作較為複雜。
• 使用者希望我們緩衝結果。
• 使用了 LIMIT 子句。

    是否要建立暫存資料表，會在所有表都讀入之前確定。

    規則3：盡量將 OUTER JOIN 轉換為 INNER JOIN，並儘可能地嵌套。相應地，ON 子句的條件運算式也會被移動到 WHERE 子句。
    如果嵌套迴圈JOIN的 WHERE 子句或 ON 子句中有一個條件運算式剔除了內表中某屬性為 NULL 的所有值，則  OUTER JOIN 可以替換為  INNER JOIN 。

    例如，下面的查詢中：
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a WHERE t2.b < 5     條件 t2.b < 5 剔除了 NULL 項，該查詢首先被轉換為:
SELECT * FROM t1 INNER JOIN t2 ON t2.a=t1.a WHERE t2.b < 5     然後轉換為等價形式：
SELECT * FROM t1, t2 ON t2.a=t1.a WHERE t2.b < 5 AND t2.a=t1.a

    類似地，下面的查詢：
SELECT * FROM t1 LEFT JOIN (t2, t3) ON t2.a=t1.a t3.b=t1.b WHERE t2.c < 5     轉化為：
SELECT * FROM t1, (t2, t3) WHERE t2.c < 5 AND t2.a=t1.a t3.b=t1.b

    一個轉換可能會觸發另一個
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a
         LEFT JOIN t3 ON t3.b=t2.b
         WHERE t3 IS NOT NULL     將轉換為：
SELECT * FROM t1 LEFT JOIN t2 ON t2.a=t1.a, t3
         WHERE t3 IS NOT NULL AND t3.b=t2.b     再轉換為：
SELECT * FROM t1, t2, t3
         WHERE t3 IS NOT NULL AND t3.b=t2.b AND t2.a=t1.a

    規則4：盡量將多個等式轉換連等式。

    規則5：ORDER BY 操作盡量施加在結果集而不是源集上。
    但在 JOIN 操作的 ON 子句中有等式或不等式（指不包括"!="在內的其他比較符號）且等式兩邊沒有常數時，可能會先對源集進行排序，然後進行歸併聯結。

    規則6：如果某個索引可以擷取所有 SELECT 語句需要的列，則優先考慮該索引。

    規則7：盡量將子查詢轉換為 JOIN。
    大多數情況下，子查詢可能需要較多的暫存資料表儲存，並且查詢速度較之 JOIN 要慢得多。

    規則8：在允許的情況下，對 JOIN 的各個表重排次序，提高執行的速度。
    資料量較小的表可能會被放在最前面先處理，資料量較大的表會稍後處理。但如果 ON 子句明確指定了依賴關係，根據依賴關係處理，順序不可調整。

 

   3. 建議兼總結

    研究MySQL原始碼，本來是期望從理解 MySQL 的處理流程中找到一些 SQL 最佳化的新思路。但是，很遺憾，在閱讀 MySQL 原始碼的過程中發現 JOIN 演算法其實是盡量簡單化、核心化。在洋洋洒洒上萬行的代碼中，JOIN 相關的演算法最核心的只不過若干行基於嵌套迴圈的排序歸併演算法，真正佔了絕大多數的代碼是在對各種條件進行最佳化、內外連接條件轉換、各種子句執行順序的重新排列等等繁瑣的處理，這些處理的最終目的只有一個：讓 SELECT SQL 能儘快按照 JOIN 的核心演算法執行並輸出結果。另外還有多處的代碼只是為了某一種特定的 SELECT 語句的最佳化，有補丁代碼的性質。
    所以這樣看來，研究 MySQL 原始碼可以對一些問題獲得比較確切的答案，但很難據此找到脫離於資料庫理論之外的其他措施來大幅提高資料庫查詢的效能。MySQL JOIN 演算法的思路和速度看起來都不錯，即便是 Oracle 之類的大型資料庫的 JOIN 演算法在速度方面未必能有多少的提高。Oracle 在 CBO 下的 Hash JOIN 在兩表大小相差很大的情況下會有較好的效能表現，但其使用面較窄，對於小型資料庫而言，不是首要發展方向（MySQL 的 index JOIN 過程中也使用了類似 Hash JOIN 的演算法，但未作重點發展）。
    儘管如此，從程式碼分析裡面還是可以獲得一些小技巧，從而充分利用MySQL的核心演算法優勢，同時避開其缺點。

• 1. 多用 JOIN，少用 IN 和 UNION。
  MySQL 在 JOIN 流程的最佳化上花了大量的精力，速度也確實很快。然而大多數子查詢或 UNION，人容易理解，但電腦不容易理解，造成最佳化功能的先天缺陷，執行速度會慢不少。
• 2. 對於多表查詢，如果可以確定表按照某一固定次序處理可以獲得較好的效率，則建議加上 STRAIGHT_JOIN 子句，以減少最佳化器對錶進行重排序最佳化的過程。
  該子句一方面可以用於最佳化器無法給出最優排列的 SQL 陳述式；另一方面同樣適用於最佳化器可以給出最優排列的 SQL 陳述式，因為 MySQL 算出最優排列也需要耗費較長的流程。
  對於後一狀況，可以根據 EXPLAIN 的提示選定表的順序，並加上 STRAIGHT_JOIN 子句固定該順序。該狀況下的使用前提是幾個表之間的資料量比例會一直保持在某一順序，否則在各表資料此消彼長之後會適得其反。
  對於經常調用的 SQL 陳述式，這一方法效果較好；同時操作的表越多，效果越好。
• 3. 用類型相同的列作比較。
  雖然 MySQL 在目前的版本中對不同類型的列之間的比較提供了一定的支援，但是事實證明，這些不同類型之間的比較引起效能的不穩定，甚至有時會匪夷所思。而且強制類型轉換會浪費大量的時間，且常常迫使最佳化器無法最佳化而遍曆所有記錄，這顯然是我們不希望的。因此對於經常查詢的列，一定要注意類型匹配。
• 4. 盡量將常數進行類型轉換，以使得比較運算式兩端的類型匹配。這一點不要指望最佳化器，它做得並不好。
• 5. 條件運算式中盡量不要對可以使用索引的列使用類型轉換或其他函數，否則很可能導致最佳化器無法使用索引。
• 6. 字串的比較會比整數比較慢很多，因此設計資料庫時，如果某些欄位可以用數字表示，則盡量不要用字串。這個好像不用多解釋。

    參考文檔：《MySQL Internals Manual》、《Pro MySQL》