詳解MySQL 聯合查詢優化機制

Posted on by

MySQL 聯合查詢執行策略。

以一個 UNION 查詢為例，MySQL 執行 UNION 查詢時，會把他們當做一系列的單個查詢語句，然後把對應的結果放入到臨時表中，最終再讀出來返回。在 MySQL中，每個獨立的查詢都是一個聯合查詢，從臨時表讀取返回結果也一樣。

這種情形下，MySQL 的聯合查詢執行很簡單——它將這裡的聯合查詢當做是嵌套循環的聯合查詢。這意味著 MySQL 會運行一個循環去從數據表讀取數據行，然而在運行一個嵌套循環從下一個表讀取匹配的數據行。這個過程一直持續，直到找到聯合查詢中的所有匹配的數據行。然後再根據 SELECT 語句中需要的列去構建返回結果。如下面的查詢語句所示：

SELECT tb1.col1, tb2.col2
FROM tb1 INNER JOIN tb2 USING(col3)
WHERE tb1.col1 IN(5,6);

實際轉換為 MySQL可能執行的偽代碼是下面這樣的：

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    while inner_row
    	output [outer_row.col1, inner_row.col2];
        inner_row = inner_iter.next;
	end
    outer_row = outer.iter.next;
end

轉換為偽代碼後如下所示

outer_iter = iterator over tb1 where col1 IN(5,6);
outer_row = outer_iter.next;
while outer_row
	inner_iter = iterator over tb2 where col3 = outer_row.col3;
	inner_row = inner_iter.next
    if inner_row
        while inner_row
            output [outer_row.col1, inner_row.col2];
            inner_row = inner_iter.next;
        end
    else
    	output [outer_row.col1, NULL];
	end
    outer_row = outer.iter.next;
end

另一個方式可視化展現查詢計劃的方式是使用泳道圖的形式。下面的圖展示瞭 內連接查詢的泳道圖。

MySQL 執行的各類查詢基本上都是相同的方式。例如，在 FROM 條件裡需要先執行的子查詢時，也是先將結果放入臨時表，然後再把臨時表當作普通表後聯合來處理。MySQL 執行聯合查詢時也是使用臨時表，然後將右連接查詢重寫為等價的左連接。簡而言之，當前版本的 MySQL 會盡可能把各類查詢轉成這種方式處理（最新版本 MySQL5.6以後引入瞭更多的復雜的處理方式）。

當然，並不是所有合法的 SQL 查詢語句都可以這麼做，有些查詢這麼做的效果可能很差。

執行計劃

MySQL不像其他很多數據庫產品，它不會將查詢語句產生字節碼去執行查詢計劃。實際上，查詢執行計劃是一棵指令樹，查詢執行引擎根據這棵樹產生查詢結果。最終的查詢計劃包含瞭足夠多的信息去重構最初的查詢。如果在查詢語句上執行EXPLAIN EXTENDED（MySQL 8以後不需要加 EXTENDED），然後再執行SHOW WARNINGS，就可以看到重構後的查詢。

對於多表查詢在概念上可以用樹代表。例如，一個4張表的查詢可能長得像下面的樹一樣。這在計算機裡稱為平衡樹，

然而這不是 MySQL 執行查詢的方式。如前所述，MySQL 總是從一張數據表開始，然後再從下一張表尋找匹配的數據行。因此，MySQL 的查詢計劃看起來像下面的左深連接樹。

聯合查詢優化器

MySQL 的查詢優化器中最重要的部分是聯合查詢優化器，由它來決定多表查詢執行過程的最優順序。通常可以通過多種聯合查詢的次序獲取相同的結果。聯合查詢優化器試圖估計這些方案的代價，然後選擇最低代價的方案去執行。

下面是一個查詢相同結果，但不同次序的聯合查詢示例。

SELECT film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

這裡面可能會有一些不同的查詢方式。比如，MySQL 可以從 film 表開始，使用 film_actor 的film_id 索引去查找對應的 actor_di 值，然後再從 actor 表使用主鍵找到對應的 actor 數據行。而 Oracle 用戶可能會表述為：“film 表是 film_actor 的驅動表，而 film_actor 是 actor 表的驅動表”。而使用 Explain 解析的結果如下：

******** 1.row ********
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: PRIMARY
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: film
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.film_id
rows: 1
Extra:

這個執行計劃與我們猜想的有很大不同。MySQL 首先從 actor 表開始，然後次序是反向的。這是否真的更有效？我們可以在 EXPLAIN 上加上 STRAIGHT_JOIN 來避免優化：

EXPLAIN SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id, actor.first_name, actor.last_name
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
INNER JOIN sakila.actor USING(actor_id);

******** 1.row ********
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:
******** 2.row ********
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: PRIMARY, idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: USING index
******** 3.row ********
id: 1
select_type: SIMPLE
table: actor
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.film_actor.actor_id
rows: 1
Extra:

這解釋瞭為什麼MySQL 為什麼需要反序執行查詢，這會使得檢查的數據行更少。

  • 先查詢 film 表會需要對 film_actor 和 actor 進行951次查詢（最外層循環）
  • 如果將 actor表前置，則隻需要對其他表進行200次查詢。

從這個例子可以看出，MySQL 的聯合查詢優化器可以通過調整查詢表次序降低查詢代價。重新排序後的聯合查詢通常是很有效的優化，通常是幾倍性能的提高。如果沒有性能提高的話，也可以使用 STRAIGHT_JOIN 來避免重排序，而使用我們自己認為最好的查詢方式。這種情況實際遇到的會很少，大部分情況下，聯合查詢優化器都會比人做得更出色。

聯合查詢優化器視圖以最低完成代價構建一個查詢執行樹。如果有可能，它會從全部的單表計劃開始，檢查所有可能的子樹組合。不幸的是，一個 N 張表的聯合查詢會有 N 個階乘的組合次序數量。這被稱之為所有可能的查詢計劃的搜索空間，這個數量增長非常快。一個10張表的聯合索引會有3628800個不同的方式！一旦搜索空間增長到過大，會導致查詢的優化十分久，這時候服務端會停止做全量分析，替代以類似貪婪算法的方式完成優化。這個數量通過 optimizer_search_depth 系統變量控制，可以自己修改該參數。

推薦閱讀：