表結(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)連接

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

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

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

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

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

使用索引加快連接速度

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

最新評論