詳解MySQL 聯(lián)合查詢優(yōu)化機(jī)制

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

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

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

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

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

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

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

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

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

對(duì)于多表查詢?cè)诟拍钌峡梢杂脴?shù)代表。例如，一個(gè)4張表的查詢可能長(zhǎng)得像下面的樹(shù)一樣。這在計(jì)算機(jī)里稱為平衡樹(shù)，

然而這不是 MySQL 執(zhí)行查詢的方式。如前所述，MySQL 總是從一張數(shù)據(jù)表開(kāi)始，然后再?gòu)南乱粡埍韺ふ移ヅ涞臄?shù)據(jù)行。因此，MySQL 的查詢計(jì)劃看起來(lái)像下面的左深連接樹(shù)。

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

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

下面是一個(gè)查詢相同結(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);

這里面可能會(huì)有一些不同的查詢方式。比如，MySQL 可以從 film 表開(kāi)始，使用 film_actor 的film_id 索引去查找對(duì)應(yīng)的 actor_di 值，然后再?gòu)?actor 表使用主鍵找到對(duì)應(yīng)的 actor 數(shù)據(jù)行。而 Oracle 用戶可能會(huì)表述為：“film 表是 film_actor 的驅(qū)動(dòng)表，而 film_actor 是 actor 表的驅(qū)動(dòng)表”。而使用 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: 

這個(gè)執(zhí)行計(jì)劃與我們猜想的有很大不同。MySQL 首先從 actor 表開(kāi)始，然后次序是反向的。這是否真的更有效？我們可以在 EXPLAIN 上加上 STRAIGHT_JOIN 來(lái)避免優(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í)行查詢，這會(huì)使得檢查的數(shù)據(jù)行更少。

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

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

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