MySQL中JOIN查詢詳解

一般而言，如果要設計一個小型資料庫（指代碼量少），但又要適應海量資料及訪問的效能需求，最有效方法莫過於針對主要應用情境選擇一個或幾個效能優異的核心演算法作為引擎，然後努力將一些非主要應用情境作為該演算法的特例或變種植入到引擎當中。
    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 子句明確指定了依賴關係，根據依賴關係處理，順序不可調整。

1 2 下一頁