先構建本篇博客的案列演示表：

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);

驅動表如何選擇？

驅動表的概念是指多表關聯查詢時，第一個被處理的表，使用此表的記錄去關聯其他表。驅動表的確定很關鍵，會直接影響多表連接的關聯順序，也決定了后續關聯時的查詢性能。

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

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

每行查詢字節數 * 預估的行數 = 預估結果集

通過where預估結果行數，遵循以下規則：

• 如果where里沒有相應表的篩選條件，無論on里是否有相關條件，默認為全表
• 如果where里有篩選條件，但是不能使用索引來篩選，那么默認為全表
• 如果where里有篩選條件，而且可以使用索引，那么會根據索引來預估返回的記錄行數

我們以上述創建的表為基礎，用如下sql作為案列來演示：

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

通過explain查看其執行計劃：

explain顯示結果里排在第一行的就是驅動表，此時表c為驅動表。

如果將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查看其執行計劃：

此時驅動表還是c，按理來說 c.* 的數據量肯定是比 a.*大的，似乎結果集大小的規則在這里沒有起作用。

此情形下如果用a作為驅動表，通過索引c2關聯到c表，那么還需要再回表查詢一次，因為僅僅通過c2獲取不到c.*的數據，還需要通過c2上的主鍵c1再查詢一次。而上一個sql查詢的是c2，不需要額外查詢。同時因為a表只有兩個字段，通過a2索引能夠直接獲得a.*,不需要額外查詢。

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

結果集是作為選擇驅動表的一個主要因素，但不是唯一因素。

兩表關聯查詢的內在邏輯是怎樣的？

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

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

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

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

通過explain查看其執行計劃：

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

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

通過上述流程知道，驅動表的每條記錄在關聯被驅動表時，如果需要用到索引不包含的數據時，就需要回表一次，去聚集索引上查詢記錄，這是一個隨機查詢的過程。每條記錄就是一次隨機查詢，性能不是非常高。mysql對這種情況有選擇的做了優化，將這種隨機查詢轉換為順序查詢，執行過程如下圖：

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

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

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

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

因為mysql的InnoDB引擎的數據是按聚集索引來排列的，當對非聚集索引按照主鍵來排序后，再用主鍵去查詢就使得隨機查詢變為順序查詢，而計算機的順序查詢有預讀機制，在讀取一頁數據時，會向后額外多讀取最多1M數據。此時順序讀取就能排上用場。

BKA算法在需要對被驅動表回表的情況下能夠優化執行邏輯，如果不需要會表，那么自然不需要BKA算法。

如果要使用 BKA 優化算法的話，你需要在執行 SQL 語句之前先設置：

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

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

最后再用explain查看開啟參數后的執行計劃：

上述都是有索引關聯被驅動表的情況，接下來我們看看沒有索引關聯被驅動表的情況。

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

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

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

示例sql如下：

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

用explain查看其執行計劃：

多表連接如何執行？是先兩表連接的結果集然后關聯第三張表，還是一條記錄貫穿全局？

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

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查看其執行計劃：

其內部執行流程如下：

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

Block Nested-Loop join 和 Batched Key Access join : 這兩個關聯算法和Index Nested-Loop join算法類似，不過因為他們能使用join buffer，所以他們可以每次從驅動表篩選一批數據，而不是一條。同時每個join關鍵字就對應著一個join buffer，也就是驅動表和第二張表用一個join buffer，得到的塊結果集與第三章表用一個join buffer。

本篇博客主要就是講述上述三個問題，如何確定驅動表，兩表關聯的執行細節，多表關聯的執行流程。

到此這篇關于淺談Mysql多表連接查詢的執行細節的文章就介紹到這了,更多相關Mysql多表連接查詢內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！