簡單聊聊MySQL中join查詢

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於join查詢的相關問題，下面一起來看一下，希望對大家有幫助。

推薦學習：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 <h3 data-id="heading-3"><strong>有索引查詢流程</strong></h3><p>我們使用查詢SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a)；因為join 查詢MYSQL 優化器不一定能按照我們的意願去執行，所以為了分析我們選擇用STRAIGHT_JOIN 來代替，從而更直觀的進行觀察</p><p> 圖1</p><p>#可以看出我們使用了t1 作為驅動表，t2 作為被驅動表，上圖的explain 中顯示本次查詢用上了t2 表的字段a索引，所以這個語句的執行過程應該是下面這樣的：</p>

• 從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 <p>上面这两条 SQL 我们加上了条件 t2.id </p><p>再看另一组：</p><pre class="brush:php;toolbar:false">SELECT t1.b,t2.* FROM t1 STRAIGHT_JOIN t2 ON t1.b = t2.b WHERE t2.id <p>这个例子里，表 t1 和 t2 都是只有 100 行参加 join。 但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的: 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，只需要放入字段 b 的值; 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字 段 id、a 和 b。</p><p>这里，我们应该选择表 t1 作为驱动表。也就是说在这个例子里，”只需要一列参与 join 的 表 t1“是那个相对小的表。</p><p>结论：</p><p>在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过 滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”， 应该作为驱动表。</p><p>推荐学习：<a href="https://www.php.cn/course/list/51.html" target="_blank" textvalue="mysql视频教程">mysql视频教程</a></p>

以上是簡單聊聊MySQL中join查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！