天天看點
傳回

mysql 兩個表的親密接觸-連接配接查詢的原理

關系型資料庫還有一個重要的概念：Join（連接配接）。使用Join有好處，也會壞處，隻有我們明白了其中的原理，才能更多的使用Join。切記不可以：

  • 業務之上，再複雜的查詢也在一個連表語句中完成。
  • 敬而遠之，DBA每次上報的慢查詢都是連接配接查詢導緻的，我再也不用了。
mysql 兩個表的親密接觸-連接配接查詢的原理

連接配接簡介

連接配接的本質

我們先來建立兩個簡單的表，再初始化一些資料

CREATE TABLE t1 (m1 int, n1 varchar(1));

CREATE TABLE t2 (m2 int, n2 varchar(1));

INSERT INTO t1 VALUES(1, 'a'), (2 , 'b') ,(3 ,'c') ;
 
INSERT INTO t2 VALUES(2 , 'b'), (3 , 'c '),(4 , 'd');

從本質上來說，連接配接就是把各個表的資料都取出來進行比對，t1 和 t2 的兩個表連接配接起來就是這樣的：

mysql 兩個表的親密接觸-連接配接查詢的原理

連接配接文法：

select * from t1, t2;

如果樂意，我們可以連接配接任意數量的表。但是如果不加任何限制條件的話，這個資料量是非常大的，我們現實中使用都是會加上限制條件的。我們來看下下面這條語句

select * from t1,t2 where t1.m1 > 1 and t1.m1 = t2.m2 and t2.n2 = 'c';

這個連接配接查詢的執行過程大緻如下

  1. 首先确定第一個需要查詢 表稱為驅動表（t1）
  2. 步驟1中從驅動表 (t1) 中每獲得一條記錄，都要去被驅動表 (t2) 中查詢比對。

從上面的步驟，可以看出上述的連表查詢我們需要查詢一次t1，兩次t2。也就是說，兩表的連接配接查詢中，需要查詢一次驅動表，被驅動表需要查詢多次。

這裡需要注意下，并不是将所有滿足條件的驅動表記錄先查詢出來放到一個地方，然後再去被驅動表中查詢，(如果滿足條件的驅動表中的資料非常多，那要需要多大的記憶體呀。) 是以是每獲得一條驅動表記錄就去被驅動表中查詢。

内連接配接和外連接配接

我們再來建立兩個表，并插入一些資料

CREATE TABLE student ( 
number INT NOT NULL Auto_increment comment'學号',
name varchar (5) COMMENT '姓名',
major varchar (30) comment '專業',
PRIMARY KEY (number));

CREATE TABLE score ( 
number INT  comment'學号',
subject varchar (30) COMMENT '科目',
score TINYINT  comment '成績',
PRIMARY KEY (number, subject));


INSERT INTO `student` (`number`, `name`, `major`) 
VALUES ('20230301', '小趙', '計算機科學');
INSERT INTO `student` (`number`, `name`, `major`) 
VALUES ('20230302', '小錢', '通信');
INSERT INTO `student` (`number`, `name`, `major`) 
VALUES ('20230303', '小孫', '土木工程');

INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230301', '高等數學', '60');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230301', '英語', '70');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '高等數學', '80');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '英語', '90');

如果我們想把所有的學生的成績都查出來，隻需要這樣執行：

select s1.number, s1.name, s1.major, s2.subject, s2.score 
	from student as s1 , score as s2 
where s1.number = s2.number;

有個問題就是小孫因為某些原因沒有參加考試，是以在結果表中沒有對應 的成績記錄。如果老師想檢視所有學生的考試成績，即使是缺考的學生 他們的成績也應該展示出來。

為了解決這個問題，就有了内連接配接和外連接配接的概念：

  • 對于内連接配接的兩個表，若驅動表中的記錄在被驅動表找不到比對的記錄，則該記錄不會加入到最後的結果集。前面提到的連接配接都是内連接配接。
  • 對于外連接配接的兩個表，時驅動表中的記錄在被驅動表中沒有比對的記錄，也仍然需要加入到結果集。

MySQL 中，根據選取的驅動表的不同，外連接配接可以細分為

  • 左外連接配接 選取左側的表為驅動表。
  • 右外連接配接·選取右側的表為驅動表。

當我們使用外連接配接的時候 有時候我們也不想把驅動表的全部記錄都加入到最後的結果集中，這個時候我們就要使用過濾條件了。

• WHERE 子句中的過濾條件：不論是内連接配接還是外連接配接 凡是不符合 WHERE 子句中過濾條件的記錄都不會被加入到最後的結果集。

• ON 子句中的過濾條件：對于外連接配接的驅動表中的記錄來說，如果無法在被驅動表中找到比對 ON 子句 中過濾條件的記錄 那麼該驅動表記錄仍然會被加入到結果集中，對應的被驅動表記錄的各個字段使用NULL 值填充。

是以上述的需求我們可以左查詢這樣來做：

select s1.number, s1.name, s1.major, s2.subject, s2.score 
	from student as s1 left join score as s2 
on s1.number = s2.number;

文法：

#左連接配接
select * from t1 left join t2 on '連接配接條件' where '普通過濾條件'
#右連接配接
select * from t1 right join t2 on '連接配接條件' where '普通過濾條件'

内連接配接的另一種寫法，也是常用寫法

select s1.number, s1.name, s1.major, s2.subject, s2.score 
	from student as s1 inner join score as s2 
where s1.number = s2.number;

文法：

select * from t1 inner join t2 on '連接配接條件' where '過濾條件'

連接配接原理

上述說了這麼多，知識簡單回顧一下連接配接，左連接配接，右連接配接這些概念。接下來我們重點說一下 MySQL 采用了什麼樣的算法來進行表與表之前的連接配接。

Nested-Loop Join （嵌套循環連接配接) NLJ

前面我們已經介紹過了執行連接配接查詢的大緻步驟了，我們再來簡單回顧一下

  • 步驟1：選取驅動表，使用相關的過濾條件，選取代價最低的單表通路方法來執行通路。
  • 步驟2：對步驟1中查詢到的驅動表結果中的每一條記錄，都分别在被驅動表中比對符合條件的記錄。
  • 如果有三個表，那麼步驟2中得到的結果集就像是新的驅動表，然後第三個表就成為了驅動表，重複上述的過程。

整個過程就像是一個嵌套循環，是以這種連接配接方式稱為 嵌套循環連接配接 ，這是最簡單也是最笨的一種連接配接查詢算法。大緻處理過程如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

需要注意的是對于獲套循環連接配接算法法來說，每當我們從驅動表中得到了一條記錄時，就根據這條記錄立時到被驅動表中查詢一次，如果得到了比對的記錄， 就把組合後 的記錄發送給用戶端，然後再到驅動表中擷取下一條記錄。這個過程将重複進行。

有什麼方式可以優化嗎

使用索引加快連接配接速度

這個是我們比較熟悉的方式，也是相對來說最有用的方式，在被驅動表上建立合适的索引，隻傳回必要的字段等都可以起到一些優化的作用。

Block Nested-Loop Join（塊嵌套循環連接配接）BNL

每次通路被驅動表，其表中的記錄都會被加載到記憶體中，然後再從驅動表中取出一條與其比對，比對結束後清楚記憶體，然後再從驅動表中加載一條記錄，然後把被驅動表的記錄加載到記憶體比對，如果這個被驅動表中的資料特别多而且不能使用索引進行通路，那就相當于要從磁盤上讀這個表好多次，這個IO的代價就非常大了。是以我們得想辦法，盡量減少被驅動表的通路次數，于是就出現了下面這種方式。

不再是逐條擷取驅動表的資料，而是一塊一塊的擷取，引入join buffer 緩沖區， 将驅動表join 相關的部分資料列（大小受join buffer的限制）緩存到 join buffer中，然後開始掃描被驅動表，被驅動表的每一條記錄一次性和join buffer中所有的驅動表記錄進行比對（記憶體中操作）。将簡單嵌套循環中的多次比較合并成一次，降低了備驅動表的通路頻率。

這裡緩存的不隻是關聯表的列，select後面的列也會緩存起來。是以查詢的時候盡量減少不必要的字段，可以讓join buffer中可以存放更多的列。

join_buffer_size的最大值在32為系統中可以申請4G，在64為作業系統中可以申請大于4G的空間。

mysql 兩個表的親密接觸-連接配接查詢的原理

MySQL對于被驅動表的關聯字段沒索引的關聯查詢，一般都會使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有 索引的情況下 NLJ 算法比 BNL算法性能更高。

關聯查詢優化總結

  1. 超過三個表禁止 join。【阿裡巴巴JAVA開發手冊】
  2. 需要 join 的字段，資料類型必須絕對一緻；【阿裡巴巴JAVA開發手冊】
  3. 多表關聯查詢時，保證被關聯的字段需要有索引，盡量選擇NLJ算法。【阿裡巴巴JAVA開發手冊】
  4. 小表驅動大表，寫多表連接配接sql時如果明确知道哪張表是小表可以用straight_join寫法固定連接配接驅動方式，省去mysql優化器自己判斷的時間
連接配接查詢 親密接觸 mysql
上一篇: 伺服器多個網站seo,網站SEO中伺服器優化的三個問題
下一篇: IDEA開發工具出現錯誤: 找不到或無法加載主類

繼續閱讀