MySQL 聯(lián)合查詢執(zhí)行策略。

以一個 UNION 查詢?yōu)槔?，MySQL 執(zhí)行 UNION 查詢時，會把他們當(dāng)做一系列的單個查詢語句，然后把對應(yīng)的結(jié)果放入到臨時表中，最終再讀出來返回。在 MySQL中，每個獨(dú)立的查詢都是一個聯(lián)合查詢，從臨時表讀取返回結(jié)果也一樣。

這種情形下，MySQL 的聯(lián)合查詢執(zhí)行很簡單——它將這里的聯(lián)合查詢當(dāng)做是嵌套循環(huán)的聯(lián)合查詢。這意味著 MySQL 會運(yùn)行一個循環(huán)去從數(shù)據(jù)表讀取數(shù)據(jù)行，然而在運(yùn)行一個嵌套循環(huán)從下一個表讀取匹配的數(shù)據(jù)行。這個過程一直持續(xù)，直到找到聯(lián)合查詢中的所有匹配的數(shù)據(jù)行。然后再根據(jù) SELECT 語句中需要的列去構(gòu)建返回結(jié)果。如下面的查詢語句所示：

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

實(shí)際轉(zhuǎn)換為 MySQL可能執(zhí)行的偽代碼是下面這樣的：

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

轉(zhuǎn)換為偽代碼后如下所示

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

另一個方式可視化展現(xiàn)查詢計(jì)劃的方式是使用泳道圖的形式。下面的圖展示了 內(nèi)連接查詢的泳道圖。

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

當(dāng)然，并不是所有合法的 SQL 查詢語句都可以這么做，有些查詢這么做的效果可能很差。

執(zhí)行計(jì)劃

MySQL不像其他很多數(shù)據(jù)庫產(chǎn)品，它不會將查詢語句產(chǎn)生字節(jié)碼去執(zhí)行查詢計(jì)劃。實(shí)際上，查詢執(zhí)行計(jì)劃是一棵指令樹，查詢執(zhí)行引擎根據(jù)這棵樹產(chǎn)生查詢結(jié)果。最終的查詢計(jì)劃包含了足夠多的信息去重構(gòu)最初的查詢。如果在查詢語句上執(zhí)行EXPLAIN EXTENDED（MySQL 8以后不需要加 EXTENDED），然后再執(zhí)行SHOW WARNINGS，就可以看到重構(gòu)后的查詢。

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

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

聯(lián)合查詢優(yōu)化器

MySQL 的查詢優(yōu)化器中最重要的部分是聯(lián)合查詢優(yōu)化器，由它來決定多表查詢執(zhí)行過程的最優(yōu)順序。通常可以通過多種聯(lián)合查詢的次序獲取相同的結(jié)果。聯(lián)合查詢優(yōu)化器試圖估計(jì)這些方案的代價，然后選擇最低代價的方案去執(zhí)行。

下面是一個查詢相同結(jié)果，但不同次序的聯(lián)合查詢示例。

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 索引去查找對應(yīng)的 actor_di 值，然后再從 actor 表使用主鍵找到對應(yīng)的 actor 數(shù)據(jù)行。而 Oracle 用戶可能會表述為：“film 表是 film_actor 的驅(qū)動表，而 film_actor 是 actor 表的驅(qū)動表”。而使用 Explain 解析的結(jié)果如下：

******** 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: 

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

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 為什么需要反序執(zhí)行查詢，這會使得檢查的數(shù)據(jù)行更少。

• 先查詢 film 表會需要對 film_actor 和 actor 進(jìn)行951次查詢（最外層循環(huán)）
• 如果將 actor表前置，則只需要對其他表進(jìn)行200次查詢。
  

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

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