快捷導(dǎo)航

MySQL兩個(gè)表的親密接觸-連接查詢的原理分析

更新時(shí)間：2024年01月24日 09:09:39 作者：廈門微思網(wǎng)絡(luò)

這篇文章主要介紹了MySQL兩個(gè)表的親密接觸-連接查詢的原理,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

連接的本質(zhì)

我們先來創(chuàng)建兩個(gè)簡(jiǎn)單的表，再初始化一些數(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');

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

連接語法：

select * from t1, t2;

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

我們來看下下面這條語句

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

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

首先確定第一個(gè)需要查詢表稱為驅(qū)動(dòng)表（t1）

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

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

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

內(nèi)連接和外連接

我們?cè)賮韯?chuàng)建兩個(gè)表，并插入一些數(shù)據(jù)

CREATE TABLE student ( 
number INT NOT NULL Auto_increment comment'學(xué)號(hào)',
name varchar (5) COMMENT '姓名',
major varchar (30) comment '專業(yè)',
PRIMARY KEY (number));
 
CREATE TABLE score ( 
number INT  comment'學(xué)號(hào)',
subject varchar (30) COMMENT '科目',
score TINYINT  comment '成績(jī)',
PRIMARY KEY (number, subject));
 
 
INSERT INTO `student` (`number`, `name`, `major`) 
VALUES ('20230301', '小趙', '計(jì)算機(jī)科學(xué)');
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ù)學(xué)', '60');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230301', '英語', '70');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '高等數(shù)學(xué)', '80');
INSERT INTO `score` (`number`, `subject`, `score`) 
VALUES ('20230302', '英語', '90');

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

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

有個(gè)問題就是小孫因?yàn)槟承┰驔]有參加考試，所以在結(jié)果表中沒有對(duì)應(yīng) 的成績(jī)記錄。如果老師想查看所有學(xué)生的考試成績(jī)，即使是缺考的學(xué)生他們的成績(jī)也應(yīng)該展示出來。

為了解決這個(gè)問題，就有了內(nèi)連接和外連接的概念：

對(duì)于內(nèi)連接的兩個(gè)表，若驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表找不到匹配的記錄，則該記錄不會(huì)加入到最后的結(jié)果集。前面提到的連接都是內(nèi)連接。
對(duì)于外連接的兩個(gè)表，時(shí)驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表中沒有匹配的記錄，也仍然需要加入到結(jié)果集。

MySQL 中，根據(jù)選取的驅(qū)動(dòng)表的不同，外連接可以細(xì)分為

左外連接選取左側(cè)的表為驅(qū)動(dòng)表。
右外連接·選取右側(cè)的表為驅(qū)動(dòng)表。

當(dāng)我們使用外連接的時(shí)候有時(shí)候我們也不想把驅(qū)動(dòng)表的全部記錄都加入到最后的結(jié)果集中，這個(gè)時(shí)候我們就要使用過濾條件了。

WHERE 子句中的過濾條件：不論是內(nèi)連接還是外連接凡是不符合 WHERE 子句中過濾條件的記錄都不會(huì)被加入到最后的結(jié)果集。
ON 子句中的過濾條件：對(duì)于外連接的驅(qū)動(dòng)表中的記錄來說，如果無法在被驅(qū)動(dòng)表中找到匹配 ON 子句中過濾條件的記錄那么該驅(qū)動(dòng)表記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用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 '普通過濾條件'

內(nèi)連接的另一種寫法，也是常用寫法

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 '過濾條件'

連接原理

上述說了這么多，知識(shí)簡(jiǎn)單回顧一下連接，左連接，右連接這些概念。

接下來我們重點(diǎn)說一下 MySQL 采用了什么樣的算法來進(jìn)行表與表之前的連接。

Nested-Loop Join （嵌套循環(huán)連接) NLJ

前面我們已經(jīng)介紹過了執(zhí)行連接查詢的大致步驟了，我們?cè)賮砗?jiǎn)單回顧一下

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

整個(gè)過程就像是一個(gè)嵌套循環(huán)，所以這種連接方式稱為嵌套循環(huán)連接，這是最簡(jiǎ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
    }
  }
}

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

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

使用索引加快連接速度

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

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

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

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

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

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