站長資訊網本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于join查詢的相關問題，下面一起來看一下，希望對大家有幫助。
程序員必備接口測試調試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設計、調試、文檔、自動化測試工具
后端、前端、測試，同時在線協作，內容實時同步
推薦學習：mysql視頻教程
索引對 join 查詢的影響
數據準備
假設有兩張表 t1、t2，兩張表都存在有主鍵索引 id 和索引字段 a，b 字段無索引，然后在 t1 表中插入 100 行數據，t2 表中插入 1000 行數據進行實驗
CREATE TABLE `t2` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `t2_a_index` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE PROCEDURE **idata**() BEGIN DECLARE i INT; SET i = 1; WHILE (i <= 1000)do INSERT INTO t2 VALUES (i,i,i); SET i = i +1; END WHILE; END; CALL **idata**(); CREATE TABLE t1 LIKE t2; INSERT INTO t1 (SELECT * FROM t2 WHERE id <= 100);
有索引查詢過程
我們使用查詢 SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a)；因為 join 查詢 MYSQL 優化器不一定能按照我們的意愿去執行，所以為了分析我們選擇用 STRAIGHT_JOIN 來代替，從而更直觀的進行觀察
圖 1
可以看出我們使用了 t1 作為驅動表，t2 作為被驅動表，上圖的 explain 中顯示本次查詢用上了 t2 表的字段 a索引，所以這個語句的執行過程應該是下面這樣的：
-
從 t1 表中讀取一行數據 r
-
從數據 r中取出字段 a到表 t2 中進行匹配
-
取出 t2 表中符合條件的行，和 r組成一行作為結果集的一部分
-
重復執行步驟 1-3，直到表 t1 循環數據
該過程稱之為 Index Nested-Loop Join，在這個流程里，驅動表 t1 進行了全表掃描，因為我們給 t1 表插入了 100 行數據，所以本次的掃描行數是 100，而進行 join 查詢時，對于 t1 表的每一行都需去 t2 表中進行查找，走的是索引樹搜索，因為我們構造的數據都是一一對應的，所以每次搜索只掃描一行，也就是 t2 表也是總共掃描 100 行，整個查詢過程掃描的總行數是 100+100=200 行。
無索引查詢過程
SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a = t2.b);
圖 2
可以看出由于 t2 表字段 B上沒有索引，所以按照上述 SQL 執行時每次從 t1 去匹配 t2 的時候都要做一次全表掃描，這樣算下來掃描 t2 多大 100 次，總掃描次數就是 100*1000 = 10 萬行。
當然了這個查詢結果還是在我們建的這兩個都是小表的情況下，如果是數量級 10 萬行的表，就需要掃描 100 億行，這就太恐怖了！
2. 了解 Block Nested-Loop Join
Block Nested-Loop Join查詢過程
那么被驅動表上沒有存在索引，這一切都是怎么發生的呢？
實際上當被驅動表上沒有可用的索引，算法流程是這樣的：
-
把 t1 的數據讀取線程內存 join_buffer 中，因為上述我們寫的是 select * from，所以相當于是把整個 t1 表放入了內存;
-
掃描 t2 的過程，實際上是把 t2 的每一行取出來，跟 join_buffer 中的數據去做對比，滿足 join 條件的，作為結果集的一部分進行返回。
所以結合圖 2中 Extra 部分說明 Using join buffer 可以發現這一絲端倪，整個過程中，對表 t1 和t2 都做了一次全表掃描，因此掃描的行數是 100+1000=1100 行，因為 join_buffer 是以無序數組的方式組織的，因此對于表 t2 中每一行，都要做 100 次判斷，總共需要在內存中進行的判斷次數是 100*1000=10 萬次，但是因為這 10 萬次是發生在內存中的所以速度上要快很多，性能也更好。
Join_buffer
根據上述已經知道了，沒有索引的情況下 MySQL 是將數據讀取內存進行循環判斷的，那么這個內存肯定不是無限制讓你使用的，這時我們就需要用到一個參數 join_buffer_size，該值默認大小 256k，如下圖：
SHOW VARIABLES LIKE '%join_buffer_size%';
圖 4
假如查詢的數據過大一次加載不完，只能夠加載部分數據（80 條），那么查詢的過程就變成了下面這樣
-
掃描表 t1，順序讀取數據行放入 join_buffer 中，直至加載完第 80 行滿了
-
掃描表 t2，把 t2 表中的每一行取出來跟 join_buffer 中的數據做對比，將滿足條件的數據作為結果集的一部分返回
-
清空 join_buffer
-
繼續掃描表 t1，順序讀取剩余的數據行放入 join_buffer 中，執行步驟 2
這個流程體現了算法名稱中 Block 的由來，分塊 join，可以看出雖然查詢過程中 t1 被分成了兩次放入 join_buffer 中，導致 t2 表被掃描了 2次，但是判斷等值條件的次數還是不變的，依然是（80+20）*1000=10 萬次。
所以這就是有時候 join 查詢很慢，有些大佬會讓你把 join_buffer_size 調大的原因。
如何正確的寫出 join 查詢
驅動表的選擇
-
有索引的情況下
在這個 join 語句執行過程中，驅動表是走全表掃描，而被驅動表是走樹搜索。
假設被驅動表的行數是 M，每次在被驅動表查詢一行數據，先要走索引 a，再搜索主鍵索引。每次搜索一棵樹近似復雜度是以 2為底的 M的對數，記為 log2M，所以在被驅動表上查詢一行數據的時間復雜度是 2*log2M。
假設驅動表的行數是 N，執行過程就要掃描驅動表 N 行，然后對于每一行，到被驅動表上 匹配一次。因此整個執行過程，近似復雜度是 N + N2log2M。顯然，N 對掃描行數的影響更大，因此應該讓小表來做驅動表。
-
那沒有索引的情況
上述我知道了，因為 join_buffer 因為存在限制，所以查詢的過程可能存在多次加載 join_buffer，但是判斷的次數都是 10 萬次，這種情況下應該怎么選擇？
假設，驅動表的數據行數是 N，需要分 K 段才能完成算法流程，被驅動表的數據行數是 M。這里的 K不是常數，N 越大 K就越大，因此把 K 表示為λ*N，顯然λ的取值范圍 是 (0,1)。
掃描的行數就變成了 N+λNM，顯然內存的判斷次數是不受哪個表作為驅動表而影響的，而考慮到掃描行數，在 M和 N大小確定的情況下，N 小一些，整個算是的結果會更小，所以應該讓小表作為驅動表
總結：真相大白了，不管是有索引還是無索引參與 join 查詢的情況下都應該是使用小表作為驅動表。
什么是小表
還是以上面表 t1 和表 t2 為例子：
SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <= 50; SELECT * FROM t2 STRAIGHT_JOIN t1 ON t1.b = t2.b WHERE t2.id <= 50;
上面這兩條 SQL 我們加上了條件 t2.id <= 50，我們使用了字段 b，所以兩條 SQL 都沒有用上索引，但是第二條 SQL 可以看出 join_buffer 只需要放入前 50 行，顯然查詢更快，所以 t2 的前 50 行就是那個相對較小的表，也就是我們上面說所說的‘小表’。
再看另一組：
SELECT t1.b,t2.* FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <= 100; SELECT t1.b,t2.* FROM t2 STRAIGHT_JOIN t1 ON t1.b = t2.b WHERE t2.id <= 100;
這個例子里，表 t1 和 t2 都是只有 100 行參加 join。 但是，這兩條語句每次查詢放入 join_buffer 中的數據是不一樣的: 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的話，就需要放入三個字 段 id、a 和 b。
這里，我們應該選擇表 t1 作為驅動表。也就是說在這個例子里，”只需要一列參與 join 的 表 t1“是那個相對小的表。
結論：
在決定哪個表做驅動表的時候，應該是兩個表按照各自的條件過濾，過 濾完成之后，計算參與 join 的各個字段的總數據量，數據量小的那個表，就是“小表”， 應該作為驅動表。
推薦學習：mysql視頻教程
