表結(jié)構(gòu)

t1

CREATE TABLE `t1` (
  `m1` int(11) DEFAULT NULL,
  `n1` char(1) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t1`(`m1`, `n1`) VALUES (1, 'a'),(2, 'b'),(3, 'c');

t2

CREATE TABLE `t2` (
  `m2` int(11) DEFAULT NULL,
  `n2` char(1) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `t2`(`m2`, `n2`) VALUES (2, 'b'),(3, 'c'),(4, 'd');

嵌套循環(huán)連接

對(duì)于兩張表的連接來說，驅(qū)動(dòng)表只會(huì)被訪問一遍，但被驅(qū)動(dòng)表卻要被訪問好多遍；具體訪問幾遍取決于對(duì)驅(qū)動(dòng)表執(zhí)行單表查詢后的結(jié)果集中有多少條記錄。對(duì)于內(nèi)連接來說選取哪個(gè)表作為驅(qū)動(dòng)表都沒關(guān)系；而外連接的驅(qū)動(dòng)表是固定的。對(duì)于t1表和t2表執(zhí)行內(nèi)鏈接的大致過程如下：

• 步驟1. 選取驅(qū)動(dòng)表，適用于驅(qū)動(dòng)表相關(guān)的過濾條件，選取代價(jià)最低的單表訪問方法來執(zhí)行對(duì)驅(qū)動(dòng)表的單表查詢。
• 步驟2. 對(duì)步驟1中查詢驅(qū)動(dòng)表得到的結(jié)果集中的每一條記錄，都分別到被驅(qū)動(dòng)表中查找匹配到記錄。

如果有3個(gè)表進(jìn)行連接，那么步驟2中得到的結(jié)果集就像是新的驅(qū)動(dòng)表，然后第三個(gè)表就成為了被驅(qū)動(dòng)表，然后重復(fù)上面的過程。也就是針對(duì)步驟2中得到的結(jié)果集中的每一條記錄都需要到t3表中找一找有沒有匹配到記錄。

這個(gè)過程就像是一個(gè)嵌套的循環(huán)，所以這種 “驅(qū)動(dòng)表只訪問一次，但被驅(qū)動(dòng)表卻可能訪問多次，且訪問次數(shù)取決于對(duì)驅(qū)動(dòng)表執(zhí)行單表查詢后的結(jié)果集中有多少條記錄” 的連接執(zhí)行方式稱為嵌套循環(huán)連接（Nested-Loop Join），這是最簡單也是最笨拙的一種連接查詢算法。

需要注意的是，對(duì)于嵌套循環(huán)連接算法來說，每當(dāng)我們從驅(qū)動(dòng)表中得到一條記錄時(shí)，就根據(jù)這條記錄立即到被驅(qū)動(dòng)表中查詢一次，如果得到了匹配到記錄，就把組合后到記錄發(fā)送給客戶端，然后再到驅(qū)動(dòng)表中獲取下一條記錄；這個(gè)過程將重復(fù)進(jìn)行。上面說到的 “結(jié)果集“ 是一個(gè)抽象的概念，并不是把驅(qū)動(dòng)表中所有的記錄都先查出來放到某個(gè)地方（比如內(nèi)存或者磁盤中），然后再遍歷被驅(qū)動(dòng)表。

使用索引加快連接速度

我們知道，在嵌套循環(huán)連接中肯能需要訪問多次被驅(qū)動(dòng)表。如果訪問被驅(qū)動(dòng)表的方式都是全表掃描，那得要掃描好多次！查詢t2表其實(shí)就相當(dāng)于一次單表查詢，我們可以利用索引來加快查詢速度。如下內(nèi)鏈接的例子：

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

這個(gè)連接查詢使用的其實(shí)是嵌套循環(huán)連接算法，首先查詢驅(qū)動(dòng)表t1后的結(jié)果集中有2條記錄,嵌套循環(huán)連接算法需要查詢被驅(qū)動(dòng)表兩次：

當(dāng)t1.m1=2 時(shí)，查詢一遍t2表，對(duì)t2表的查詢語句相當(dāng)于：

select * from t2 where t2.m2 = 2 and t2.n2 < 'd';

當(dāng)t1.m1=3 時(shí)，查詢一遍t2表，對(duì)t2表的查詢語句相當(dāng)于：

select * from t2 where t2.m2 = 3 and t2.n2 < 'd';

可以看到，原來的t1.m1=t2.m2這個(gè)涉及兩個(gè)表的過濾條件在針對(duì)t2表進(jìn)行查詢時(shí)，關(guān)于t1表的條件就已經(jīng)確定了，所以我們只需要單單優(yōu)化針對(duì)t2表的查詢即可。上述兩個(gè)對(duì)t2表的查詢語句中利用到的是m2和n2列，我們可以進(jìn)行如下嘗試。

• 在m2列上建立索引。因?yàn)獒槍?duì)m2列的條件是等值查找，比如t2.m2=2、t2.m2=3等，所以可能使用到ref訪問方法。假設(shè)使用ref訪問方法來執(zhí)行對(duì)t2表的查詢，需要在回表之后再判斷t2.n2<'d'這個(gè)條件是否成立。
  這里有一個(gè)比較特殊的情況，假設(shè)m2列是t2表的主鍵，或者是不允許存儲(chǔ)null值的唯一二級(jí)索引列，那么使用 "t2.m2=常數(shù)值" 這樣的條件從t2表中查找記錄時(shí)，代價(jià)就是常數(shù)級(jí)別的。我們知道，在黨表中使用主鍵值或者唯一二級(jí)索引列的值進(jìn)行等值查找的方式稱為const，而在連接查詢中對(duì)被驅(qū)動(dòng)表的主鍵或者不允許存儲(chǔ)null值的唯一二級(jí)索引進(jìn)行等值查找使用的訪問方法就稱為eq_ref。
• 在n2列上建立索引，涉及的條件是t2.n2<'d'，可能用到range訪問方法。假設(shè)使用range訪問方法對(duì)t2表進(jìn)行查詢，需要在回表之后再判斷包含m2列的條件是否成立。
  假設(shè)m2列和n2列上都存在索引，那么就需要從這兩個(gè)里面挑一個(gè)代價(jià)更低的索引來查詢t2表。
  另外，連接查詢的查詢列表和過濾條件中有時(shí)可能只涉及被驅(qū)動(dòng)表的部分列，而這些列都是某個(gè)二級(jí)索引的一部分，在這種情況下不能使用eq_ref、ref、ref_or_null或者range等訪問方法來查詢被驅(qū)動(dòng)表，也可以通過掃描全部二級(jí)索引記錄（即使用index訪問方法）來查詢被驅(qū)動(dòng)表。所以建議最好不要使用 * 作為查詢列表，而是把真正用到的列作為查詢列表。

基于塊的嵌套循環(huán)連接

由于現(xiàn)實(shí)生活中的表可不像t1、t2這樣只有3條記錄，成千上萬條記錄都是少的，幾百萬、幾千萬甚至幾億條記錄到處都是。現(xiàn)在假設(shè)我們不能使用索引加快被驅(qū)動(dòng)表的查詢過程，所以對(duì)于驅(qū)動(dòng)表結(jié)果集中的每一條記錄，都需要對(duì)被驅(qū)動(dòng)表執(zhí)行全表掃描。這樣在對(duì)被驅(qū)動(dòng)表進(jìn)行全表掃描時(shí)，可能前面的記錄還在內(nèi)存中，而表后面的記錄還在磁盤上。而等到掃描表中后面的記錄時(shí)，有可能由于內(nèi)存不足，需要把表前面的記錄從內(nèi)存中釋放掉給現(xiàn)在正在掃描的記錄騰地方。前面強(qiáng)調(diào)過，在采用嵌套循環(huán)連接算法的兩表連接過程中，被驅(qū)動(dòng)表可是要訪問好多次。如果這個(gè)被驅(qū)動(dòng)表中的數(shù)據(jù)特別多而且不能使用索引進(jìn)行訪問，那就相當(dāng)于要從磁盤上讀這個(gè)表好多次，這個(gè)I/O的代價(jià)就太大了。所以我們得想想辦法，盡量減少被驅(qū)動(dòng)表的訪問次數(shù)。

通過上面的敘述我們了解到，驅(qū)動(dòng)表結(jié)果集中有多少條記錄，就可能把被驅(qū)動(dòng)表從磁盤加載到內(nèi)存多少次。我們是否可以在把被驅(qū)動(dòng)表中的記錄加載到內(nèi)存時(shí)，一次性地與驅(qū)動(dòng)表中的多條記錄進(jìn)行匹配呢？這樣就可以大大減少重復(fù)從磁盤上加載被驅(qū)動(dòng)表的代價(jià)了。所以設(shè)計(jì)MySql的大叔提出了一個(gè)名為Join Buffer（連接緩沖區(qū)）的概念。Join Buffer就是在執(zhí)行連接查詢前申請(qǐng)的一塊固定大小的內(nèi)存。先把若干條驅(qū)動(dòng)表結(jié)果集中的記錄裝在這個(gè)Join Buffer中，然后開始掃描被驅(qū)動(dòng)的表，每一條被驅(qū)動(dòng)表的記錄一次性地與Join Buffer中的多條驅(qū)動(dòng)表記錄進(jìn)行匹配。由于匹配的過程都是在內(nèi)存中完成的，所以這樣可以顯著減少被驅(qū)動(dòng)表的I/O代價(jià)。

最好的情況是Join Buffer足夠大，能容納驅(qū)動(dòng)表結(jié)果集中的所有記錄，這樣只需要訪問一次被驅(qū)動(dòng)表就可以完成連接操作了。設(shè)計(jì)MySql的大叔把這種加入了Join Buffer的嵌套循環(huán)連接算法稱為基于塊的嵌套循環(huán)連接（Block Nested-Loop Join）算法。

這個(gè)Join Buffer的大小可以通過啟動(dòng)選項(xiàng)或者系統(tǒng)變量join_buffer_size進(jìn)行配置，默認(rèn)大小為262144字節(jié)（256KB），最小可以設(shè)置為128字節(jié)。當(dāng)然，在我們優(yōu)化對(duì)被驅(qū)動(dòng)表的查詢時(shí)，最好是為驅(qū)動(dòng)表加上高效的索引。如果實(shí)在不能使用索引，并且自己機(jī)器的內(nèi)存也比較大，則可以嘗試調(diào)大join_buffer_size的值來對(duì)連接查詢進(jìn)行優(yōu)化。

另外需要注意的是，Join Buffer中并不會(huì)存放驅(qū)動(dòng)表記錄的所有列，只有查詢列表中的列和過濾條件中的列才會(huì)被放到Join Buffer中，所以這也再次提醒我們，最好不要把 * 作為查詢列表，只需要把關(guān)心的列放到查詢列表中就好了；這樣還可以在Join Buffer中放置更多的記錄。

以上就是透徹理解Mysql連接的原理的詳細(xì)內(nèi)容，更多關(guān)于Mysql連接原理的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！