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

作者：做好一個程序猿 2023-04-12 08:14:10

數(shù)據(jù)庫其他數(shù)據(jù)庫

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

關系型數(shù)據(jù)庫還有一個重要的概念：Join（連接）。使用Join有好處，也會壞處，只有我們明白了其中的原理，才能更多的使用Join。切記不可以：

業(yè)務之上，再復雜的查詢也在一個連表語句中完成。

敬而遠之，DBA每次上報的慢查詢都是連接查詢導致的，我再也不用了。

連接簡介

連接的本質

我們先來創(chuàng)建兩個簡單的表，再初始化一些數(shù)據(jù)

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');

從本質上來說，連接就是把各個表的數(shù)據(jù)都取出來進行匹配，t1 和 t2 的兩個表連接起來就是這樣的：

連接語法：

select * from t1, t2;

如果樂意，我們可以連接任意數(shù)量的表。但是如果不加任何限制條件的話，這個數(shù)據(jù)量是非常大的，我們現(xiàn)實中使用都是會加上限制條件的。我們來看下下面這條語句

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

這個連接查詢的執(zhí)行過程大致如下

首先確定第一個需要查詢表稱為驅動表（t1）

步驟1中從驅動表 (t1) 中每獲得一條記錄，都要去被驅動表 (t2) 中查詢匹配。

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

這里需要注意下，并不是將所有滿足條件的驅動表記錄先查詢出來放到一個地方，然后再去被驅動表中查詢，(如果滿足條件的驅動表中的數(shù)據(jù)非常多，那要需要多大的內存呀。) 所以是每獲得一條驅動表記錄就去被驅動表中查詢。

內連接和外連接

我們再來創(chuàng)建兩個表，并插入一些數(shù)據(jù)

CREATE TABLE student ( 
number INT NOT NULL Auto_increment comment'學號',
name varchar (5) COMMENT '姓名',
major varchar (30) comment '專業(yè)',
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', '高等數(shù)學', '60');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230301', '英語', '70');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '高等數(shù)學', '80');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '英語', '90');

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

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

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

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

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

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

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

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

? 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 （嵌套循環(huán)連接) NLJ

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

步驟1：選取驅動表，使用相關的過濾條件，選取代價最低的單表訪問方法來執(zhí)行訪問。
步驟2：對步驟1中查詢到的驅動表結果中的每一條記錄，都分別在被驅動表中匹配符合條件的記錄。
如果有三個表，那么步驟2中得到的結果集就像是新的驅動表，然后第三個表就成為了驅動表，重復上述的過程。

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

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
    }
  }
}

需要注意的是對于獲套循環(huán)連接算法法來說，每當我們從驅動表中得到了一條記錄時，就根據(jù)這條記錄立時到被驅動表中查詢一次，如果得到了匹配的記錄，就把組合后的記錄發(fā)送給客戶端，然后再到驅動表中獲取下一條記錄。這個過程將重復進行。

有什么方式可以優(yōu)化嗎

使用索引加快連接速度

這個是我們比較熟悉的方式，也是相對來說最有用的方式，在被驅動表上創(chuàng)建合適的索引，只返回必要的字段等都可以起到一些優(yōu)化的作用。

Block Nested-Loop Join（塊嵌套循環(huán)連接）BNL

每次訪問被驅動表，其表中的記錄都會被加載到內存中，然后再從驅動表中取出一條與其匹配，匹配結束后清楚內存，然后再從驅動表中加載一條記錄，然后把被驅動表的記錄加載到內存匹配，如果這個被驅動表中的數(shù)據(jù)特別多而且不能使用索引進行訪問，那就相當于要從磁盤上讀這個表好多次，這個IO的代價就非常大了。所以我們得想辦法，盡量減少被驅動表的訪問次數(shù)，于是就出現(xiàn)了下面這種方式。

不再是逐條獲取驅動表的數(shù)據(jù)，而是一塊一塊的獲取，引入join buffer 緩沖區(qū)，將驅動表join 相關的部分數(shù)據(jù)列（大小受join buffer的限制）緩存到 join buffer中，然后開始掃描被驅動表，被驅動表的每一條記錄一次性和join buffer中所有的驅動表記錄進行匹配（內存中操作）。將簡單嵌套循環(huán)中的多次比較合并成一次，降低了備驅動表的訪問頻率。

這里緩存的不只是關聯(lián)表的列，select后面的列也會緩存起來。所以查詢的時候盡量減少不必要的字段，可以讓join buffer中可以存放更多的列。

join_buffer_size的最大值在32為系統(tǒng)中可以申請4G，在64為操作系統(tǒng)中可以申請大于4G的空間。

關聯(lián)查詢優(yōu)化總結

超過三個表禁止 join。【阿里巴巴JAVA開發(fā)手冊】
需要 join 的字段，數(shù)據(jù)類型必須絕對一致；【阿里巴巴JAVA開發(fā)手冊】
多表關聯(lián)查詢時，保證被關聯(lián)的字段需要有索引，盡量選擇NLJ算法。【阿里巴巴JAVA開發(fā)手冊】
小表驅動大表，寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅動方式，省去mysql優(yōu)化器自己判斷的時間

責任編輯：武曉燕來源：今日頭條

mysql 關聯(lián)字段索引