淺談Mysql多表連接查詢的執(zhí)行細(xì)節(jié)

先構(gòu)建本篇博客的案列演示表：

create table a(a1 int primary key, a2 int ,index(a2));  		--雙字段都有索引
create table c(c1 int primary key, c2 int ,index(c2), c3 int);  --雙字段都有索引
create table b(b1 int primary key, b2 int);						--有主鍵索引
create table d(d1 int, d2 int); 								--沒有索引

insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
insert into b values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
insert into c values(1,1,1),(2,4,4),(3,6,6),(4,5,5),(5,3,3),(6,3,3),(7,2,2),(8,8,8),(9,5,5),(10,3,3);  
insert into d values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);

驅(qū)動表如何選擇？

驅(qū)動表的概念是指多表關(guān)聯(lián)查詢時(shí)，第一個被處理的表，使用此表的記錄去關(guān)聯(lián)其他表。驅(qū)動表的確定很關(guān)鍵，會直接影響多表連接的關(guān)聯(lián)順序，也決定了后續(xù)關(guān)聯(lián)時(shí)的查詢性能。

驅(qū)動表的選擇遵循一個原則：在對最終結(jié)果集沒影響的前提下，優(yōu)先選擇結(jié)果集最小的那張表作為驅(qū)動表。改變驅(qū)動表就意味著改變連接順序，只有在不會改變最終輸出結(jié)果的前提下才可以對驅(qū)動表做優(yōu)化選擇。在外連接情況下，很多時(shí)候改變驅(qū)動表會對輸出結(jié)果有影響，比如left join的左邊表和right join的右邊表，驅(qū)動表選擇join的左邊或者右邊最終輸出結(jié)果很有可能會不同。

用結(jié)果集來選擇驅(qū)動表，那結(jié)果集是什么？如何計(jì)算結(jié)果集？mysql在選擇前會根據(jù)where里的每個表的篩選條件，相應(yīng)的對每個可作為驅(qū)動表的表做個結(jié)果記錄預(yù)估，預(yù)估出每個表的返回記錄行數(shù)，同時(shí)再根據(jù)select里查詢的字段的字節(jié)大小總和做乘積：

每行查詢字節(jié)數(shù) * 預(yù)估的行數(shù) = 預(yù)估結(jié)果集

通過where預(yù)估結(jié)果行數(shù)，遵循以下規(guī)則：

• 如果where里沒有相應(yīng)表的篩選條件，無論on里是否有相關(guān)條件，默認(rèn)為全表
• 如果where里有篩選條件，但是不能使用索引來篩選，那么默認(rèn)為全表
• 如果where里有篩選條件，而且可以使用索引，那么會根據(jù)索引來預(yù)估返回的記錄行數(shù)

我們以上述創(chuàng)建的表為基礎(chǔ)，用如下sql作為案列來演示：

select a.*,c.c2 from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;

通過explain查看其執(zhí)行計(jì)劃：

explain顯示結(jié)果里排在第一行的就是驅(qū)動表，此時(shí)表c為驅(qū)動表。

如果將sql修改一下，將select 里的條件c.c2 修改為 c.* ：

select a.*,c.* from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;

通過explain查看其執(zhí)行計(jì)劃：

此時(shí)驅(qū)動表還是c，按理來說 c.* 的數(shù)據(jù)量肯定是比 a.*大的，似乎結(jié)果集大小的規(guī)則在這里沒有起作用。

此情形下如果用a作為驅(qū)動表，通過索引c2關(guān)聯(lián)到c表，那么還需要再回表查詢一次，因?yàn)閮H僅通過c2獲取不到c.*的數(shù)據(jù)，還需要通過c2上的主鍵c1再查詢一次。而上一個sql查詢的是c2，不需要額外查詢。同時(shí)因?yàn)閍表只有兩個字段，通過a2索引能夠直接獲得a.*,不需要額外查詢。

綜上所述，雖然使用c表來驅(qū)動，結(jié)果集大一些，但是能夠減少一次額外的回表查詢，所以mysql認(rèn)為使用c表作為驅(qū)動來效率更高。

結(jié)果集是作為選擇驅(qū)動表的一個主要因素，但不是唯一因素。

兩表關(guān)聯(lián)查詢的內(nèi)在邏輯是怎樣的？

mysql表與表之間的關(guān)聯(lián)查詢使用Nested-Loop join算法，顧名思義就是嵌套循環(huán)連接，但是根據(jù)場景不同可能有不同的變種：比如Index Nested-Loop join，Simple Nested-Loop join,Block Nested-Loop join, Betched Key Access join等。

• 在使用索引關(guān)聯(lián)的情況下，有Index Nested-Loop join和Batched Key Access join兩種算法；
• 在未使用索引關(guān)聯(lián)的情況下，有Simple Nested-Loop join和Block Nested-Loop join兩種算法；

我們先來看有索引的情形，使用的是博客剛開始時(shí)建立的表，sql如下：

select a.*,c.* from a join c on a.a2=c.c2 where a.a1>4;

通過explain查看其執(zhí)行計(jì)劃：

首先根據(jù)第一步的邏輯來確定驅(qū)動表a，然后通過a.a1>4，a.來查詢一條記錄a1=5，將此記錄的c2關(guān)聯(lián)到c表，取得c2索引上的主鍵c1，然后用c1的值再去聚集索引上查詢c.*，組成一條完整的結(jié)果，放入net buffer，然后再根據(jù)條件a.a1>4，a. 取下一條記錄，循環(huán)此過程。過程圖如下：

通過索引關(guān)聯(lián)被驅(qū)動表，使用的是Index Nested-Loop join算法，不會使用msyql的join buffer。根據(jù)驅(qū)動表的篩選條件逐條地和被驅(qū)動表的索引做關(guān)聯(lián)，每關(guān)聯(lián)到一條符合的記錄，放入net-buffer中，然后繼續(xù)關(guān)聯(lián)。此緩存區(qū)由net_buffer_length參數(shù)控制，最小4k,最大16M,默認(rèn)是1M。 如果net-buffer滿了，將其發(fā)送給client，清空net-buffer，繼續(xù)上一過程。

通過上述流程知道，驅(qū)動表的每條記錄在關(guān)聯(lián)被驅(qū)動表時(shí)，如果需要用到索引不包含的數(shù)據(jù)時(shí)，就需要回表一次，去聚集索引上查詢記錄，這是一個隨機(jī)查詢的過程。每條記錄就是一次隨機(jī)查詢，性能不是非常高。mysql對這種情況有選擇的做了優(yōu)化，將這種隨機(jī)查詢轉(zhuǎn)換為順序查詢，執(zhí)行過程如下圖：

此時(shí)會使用Batched Key Access join 算法，顧名思義，就是批量的key訪問連接。

逐條的根據(jù)where條件查詢驅(qū)動表，將符合記錄的數(shù)據(jù)行放入join buffer，然后根據(jù)關(guān)聯(lián)的索引獲取被驅(qū)動表的索引記錄，存入read_rnd_buffer。join buffer和read_rnd_buffer都有大小限制，無論哪個到達(dá)上限都會停止此批次的數(shù)據(jù)處理，等處理完清空數(shù)據(jù)再執(zhí)行下一批次。也就是驅(qū)動表符合條件的數(shù)據(jù)可能不能夠一次處理完，而要分批次處理。

當(dāng)達(dá)到批次上限后，對read_rnd_buffer里的被驅(qū)動表的索引按主鍵做遞增排序，這樣在回表查詢時(shí)就能夠做到近似順序查詢：

如上圖，左邊是未排序前的隨機(jī)查詢示意圖，右邊是排序后使用MRR( Multi-Range Read)的順序查詢示意圖。

因?yàn)閙ysql的InnoDB引擎的數(shù)據(jù)是按聚集索引來排列的，當(dāng)對非聚集索引按照主鍵來排序后，再用主鍵去查詢就使得隨機(jī)查詢變?yōu)轫樞虿樵?，而?jì)算機(jī)的順序查詢有預(yù)讀機(jī)制，在讀取一頁數(shù)據(jù)時(shí)，會向后額外多讀取最多1M數(shù)據(jù)。此時(shí)順序讀取就能排上用場。

BKA算法在需要對被驅(qū)動表回表的情況下能夠優(yōu)化執(zhí)行邏輯，如果不需要會表，那么自然不需要BKA算法。

如果要使用 BKA 優(yōu)化算法的話，你需要在執(zhí)行 SQL 語句之前先設(shè)置：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前兩個參數(shù)的作用是要啟用 MRR（Multi-Range Read）。這么做的原因是，BKA 算法的優(yōu)化需要依賴于MRR，官方文檔的說法，是現(xiàn)在的優(yōu)化器策略，判斷消耗的時(shí)候，會更傾向于不使用 MRR，把 mrr_cost_based 設(shè)置為 off，就是固定使用 MRR 了。）

最后再用explain查看開啟參數(shù)后的執(zhí)行計(jì)劃：

上述都是有索引關(guān)聯(lián)被驅(qū)動表的情況，接下來我們看看沒有索引關(guān)聯(lián)被驅(qū)動表的情況。

沒有使用索引關(guān)聯(lián)，那么最簡單的Simple Nested-Loop join，就是根據(jù)where條件，從驅(qū)動表取一條數(shù)據(jù)，然后全表掃面被驅(qū)動表，將符合條件的記錄放入最終結(jié)果集中。這樣驅(qū)動表的每條記錄都伴隨著被驅(qū)動表的一次全表掃描，這就是Simple Nested-Loop join。

當(dāng)然mysql沒有直接使用Simple Nested-Loop join，而是對其做了一個優(yōu)化，不是逐條的獲取驅(qū)動表的數(shù)據(jù)，而是多條的獲取，也就是一塊一塊的獲取，取名叫Block Nested-Loop join。每次取一批數(shù)據(jù)，上限是達(dá)到j(luò)oin buffer的大小，然后全表掃面被驅(qū)動表，每條數(shù)據(jù)和join buffer里的所有行做匹配，匹配上放入最終結(jié)果集中。這樣就極大的減少了掃描被驅(qū)動表的次數(shù)。

BNL(Block Nested-Loop join) 和 BKA(Batched Key Access join)的流程有點(diǎn)類似， 但是沒有read_rnd_buffer這個步驟。

示例sql如下：

select a.*, d.* from a join d on a.a2=d.d2  where a.a1>7;

用explain查看其執(zhí)行計(jì)劃：

多表連接如何執(zhí)行？是先兩表連接的結(jié)果集然后關(guān)聯(lián)第三張表，還是一條記錄貫穿全局？

其實(shí)看連接算法的名稱：Nested-Loop join，嵌套循環(huán)連接，就知道是多表嵌套的循環(huán)連接，而不是先兩表關(guān)聯(lián)得出結(jié)果，然后再依次關(guān)聯(lián)的形式，其形式類似于下面這樣：

for row1 in table1 filtered by where{
	for row2 in table2 associated by table1.index1 filtered by where{
		for row3 in table3 associated by table2.index2 filtered by where{
			put into net-buffer then send to client;
		}
	}	
}

對于不同的join方式，有下列情況：

Index Nested-Loop join：

sql如下：

select a.*,b.*,c.* from a join c on a.a2=c.c2 join b on c.c2=b.b2 where b.b1>4;

通過explain查看其執(zhí)行計(jì)劃：

其內(nèi)部執(zhí)行流程如下：

執(zhí)行前mysql執(zhí)行器會確定好各個表的關(guān)聯(lián)順序。首先通過where條件，篩選驅(qū)動表b的第一條記錄b5，然后將用此記錄的關(guān)聯(lián)字段b2與第二張表a的索引a2做關(guān)聯(lián)，通過Btree定位索引位置，匹配的索引可能不止一條。當(dāng)匹配上一條，查看where里是否有a2的過濾條件且條件是否需要索引之外的數(shù)據(jù)，如果要則回表，用a2索引上的主鍵去查詢數(shù)據(jù)，然后做判斷。通過則用join后的信息再用同樣的方式來關(guān)聯(lián)第三章表c。

Block Nested-Loop join 和 Batched Key Access join : 這兩個關(guān)聯(lián)算法和Index Nested-Loop join算法類似，不過因?yàn)樗麄兡苁褂胘oin buffer，所以他們可以每次從驅(qū)動表篩選一批數(shù)據(jù)，而不是一條。同時(shí)每個join關(guān)鍵字就對應(yīng)著一個join buffer，也就是驅(qū)動表和第二張表用一個join buffer，得到的塊結(jié)果集與第三章表用一個join buffer。

本篇博客主要就是講述上述三個問題，如何確定驅(qū)動表，兩表關(guān)聯(lián)的執(zhí)行細(xì)節(jié)，多表關(guān)聯(lián)的執(zhí)行流程。

到此這篇關(guān)于淺談Mysql多表連接查詢的執(zhí)行細(xì)節(jié)的文章就介紹到這了,更多相關(guān)Mysql多表連接查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！