連接查詢的執行過程:
- 確定第一個需要查詢的表【驅動表】
- 選取代價最小的訪問方法去執行單表查詢語句
- 從驅動表每獲取到一條記錄,都需要到t2表中查找匹配的記錄
兩表連接查詢需要查詢一次t1表,兩次t2表,在兩表的連接查詢中,驅動表只需訪問一次,被驅動表可能需要訪問多次
并不是所有滿足條件的驅動表記錄先查詢出來放到一個地方再去被驅動表查詢的【如果符合條件的記錄很多,需要很大的存儲空間】,每獲取到一條驅動表記錄,就立刻到被驅動表中尋找匹配的記錄
內連接和外連接
-
對于內連接的兩個表,若驅動表中的記錄在被驅動表中找不到匹配的記錄,則該記錄不會加入到最后的結果集
- 對于內連接來說,驅動表和被驅動表是可以互換的
-
對于外連接的兩個表,即使驅動表中的記錄在被驅動表中沒有匹配的記錄,也仍需要加入到結果集中
-
左外連接:左側表為驅動表
-
右外連接:右側表為驅動表
-
驅動表記錄篩選
- where子句過濾
- On子句過濾:專門為外連接驅動表中的記錄在被驅動表找不到匹配記錄時是否應該把驅動表記錄加入結果集中這一場景設計
-
-
嵌套循環連接
-
-
-
驅動表只訪問一次,但被驅動表可能訪問多次,且訪問次數取決于對驅動表執行單表查詢后的結果集中有多少條記錄
-
驅動表得到一條記錄->被驅動表查詢匹配到結果->把組合后的記錄發送給客戶端,再到驅動表中獲取下一條記錄
-
-
使用索引加快連接速度
- 建議最好不要使用*作為查詢列表,而是把真正用到的列作為查詢列表
-
基于塊的嵌套循環連接Block Nested-Loop Join
- Join Buffer連接緩沖區【減少被驅動表的訪問次數】
-
執行連接查詢前申請的一塊固定大小的內存,先把若干條驅動表結果集中的記錄裝在Join Buffer中,然后開始掃描被驅動表,每一條被驅動表的記錄一次性地與Join Buffer中的多條驅動表記錄進行匹配,匹配過程都在內存中完成,顯著減少被驅動表的I/O代價
-
-
最好的情況是Join Buffer足夠大,能容納驅動表結果集中的所有記錄,只需訪問一次被驅動表就可完成連接操作
-
Join Buffer大小可以通過啟動選項或系統變量join_buffer_size進行配置【默認256KB,最小128字節】
-
優化對被驅動表的查詢時,最好為被驅動表加上高效率索引,實在不行可嘗試調大join_buffer_size對連接查詢進行優化
-
需注意,不會存放被驅動表記錄的所有列,只有查詢列表中的列和過濾條件中的列【最好不要用*查詢】,Join Buffer可以放更多記錄
-
- Join Buffer連接緩沖區【減少被驅動表的訪問次數】
Join注意點
- 小表作為驅動表,被驅動表是否能命中索引
- 驅動表需要全表掃描,而被驅動表通過索引查詢