來源：https://blog.csdn.net/qq_27529917/
先構建本篇博客的案列演示表：
1.驅動表如何選擇？
驅動表的概念是指多表關聯查詢時，第一個被處理的表，使用此表的記錄去關聯其他表。驅動表的確定很關鍵，會直接影響多表連接的關聯順序，也決定了後續關聯時的查詢性能。
驅動表的選擇遵循一個原則：在對最終結果集沒影響的前提下，優先選擇結果集最小的那張表作為驅動表。改變驅動表就意味著改變連接順序，只有在不會改變最終輸出結果的前提下才可以對驅動表做優化選擇。
在外連接情況下，很多時候改變驅動表會對輸出結果有影響，比如left join的左邊表和right join的右邊表，驅動表選擇join的左邊或者右邊最終輸出結果很有可能會不同。
用結果集來選擇驅動表，那結果集是什麼？如何計算結果集？mysql在選擇前會根據where裡的每個表的篩選條件，相應的對每個可作為驅動表的表做個結果記錄預估，預估出每個表的返回記錄行數，同時再根據select裡查詢的字段的字節大小總和做乘積：
每行查詢字節數 * 預估的行數 = 預估結果集
通過where預估結果行數，遵循以下規則：
- 如果where裡沒有相應表的篩選條件，無論on裡是否有相關條件，默認為全表
- 如果where裡有篩選條件，但是不能使用索引來篩選，那麼默認為全表
- 如果where裡有篩選條件，而且可以使用索引，那麼會根據索引來預估返回的記錄行數
我們以上述創建的表為基礎，用如下sql作為案列來演示：
<code>selecta.*,c.c2fromajoincona.a2=c.c2wherea.a1>5andc.c1>5;/<code>
通過explain查看其執行計劃：
explain顯示結果裡排在第一行的就是驅動表，此時表c為驅動表。
如果將sql修改一下，將select 裡的條件c.c2 修改為 c.* ：
<code>selecta.*,c.*fromajoincona.a2=c.c2wherea.a1>5andc.c1>5;/<code>
通過explain查看其執行計劃：
此時驅動表還是c，按理來說 c.* 的數據量肯定是比 a.*大的，似乎結果集大小的規則在這裡沒有起作用。
此情形下如果用a作為驅動表，通過索引c2關聯到c表，那麼還需要再回表查詢一次，因為僅僅通過c2獲取不到c.* 的數據，還需要通過c2上的主鍵c1再查詢一次。而上一個sql查詢的是c2，不需要額外查詢。同時因為a表只有兩個字段，通過a2索引能夠直接獲得a.* ,不需要額外查詢。
綜上所述，雖然使用c表來驅動，結果集大一些，但是能夠減少一次額外的回表查詢，所以mysql認為使用c表作為驅動來效率更高。
結果集是作為選擇驅動表的一個主要因素，但不是唯一因素。
2.兩表關聯查詢的內在邏輯是怎樣的？
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如下：
<code>selecta.*,c.*fromajoincona.a2=c.c2wherea.a1>4;/<code>
通過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 語句之前先設置：
<code>setoptimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';/<code>
前兩個參數的作用是要啟用 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如下：
<code>selecta.*,d.*fromajoindona.a2=d.d2wherea.a1>7;/<code>
用explain查看其執行計劃：
3.多表連接如何執行？是先兩表連接的結果集然後關聯第三張表，還是一條記錄貫穿全局？
其實看連接算法的名稱：Nested-Loop join，嵌套循環連接，就知道是多表嵌套的循環連接，而不是先兩表關聯得出結果，然後再依次關聯的形式，其形式類似於下面這樣：
對於不同的join方式，有下列情況：
<code>IndexNested-Loopjoin：/<code>
sql如下：
<code>selecta.*,b.*,c.*fromajoincona.a2=c.c2joinbonc.c2=b.b2whereb.b1>4;/<code>
通過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。
本篇博客主要就是講述上述三個問題，如何確定驅動表，兩表關聯的執行細節，多表關聯的執行流程。
閱讀更多 程序員BUG 的文章
