共計 5702 個字符，預(yù)計需要花費(fèi) 15 分鐘才能閱讀完成。
本篇內(nèi)容主要講解“MySQL 中的 join 語句算法如何優(yōu)化”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓丸趣 TV 小編來帶大家學(xué)習(xí)“MySQL 中的 join 語句算法如何優(yōu)化”吧!
一、join 語句算法
創(chuàng)建兩個表 t1 和 t2
CREATE TABLE `t2` ( `id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
declare i int;
set i=1;
while(i =1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
create table t1 like t2;
insert into t1 (select * from t2 where id =100);這兩個表都有一個主鍵索引 id 和一個索引 a，字段 b 上無索引。存儲過程 idata() 往表 t2 里插入了 1000 行數(shù)據(jù)，在表 t1 里插入的是 100 行數(shù)據(jù)
1、Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);如果直接使用 join 語句，MySQL 優(yōu)化器可能會選擇表 t1 或 t2 作為驅(qū)動表，通過 straight_join 讓 MySQL 使用固定的連接方式執(zhí)行查詢，在這個語句里，t1 是驅(qū)動表，t2 是被驅(qū)動表
被驅(qū)動表 t2 的字段 a 上有索引，join 過程用上了這個索引，因此這個語句的執(zhí)行流程是這樣的：
1. 從表 t1 中讀入一行數(shù)據(jù) R
2. 從數(shù)據(jù)行 R 中，取出 a 字段到表 t2 里去查找
3. 取出表 t2 中滿足條件的行，跟 R 組成一行，作為結(jié)果集的一部分
4. 重復(fù)執(zhí)行步驟 1 到 3，直到表 t1 的末尾循環(huán)結(jié)束
這個過程可以用上被驅(qū)動表的索引，稱之為 Index Nested-Loop Join，簡稱 NLJ
在這個流程里：
1. 對驅(qū)動表 t1 做了全表掃描，這個過程需要掃描 100 行
2. 而對于每一行 R，根據(jù) a 字段去表 t2 查找，走的是樹搜索過程。由于我們構(gòu)造的數(shù)據(jù)都是一一對應(yīng)的，因此每次的搜索過程都只掃描一行，也是總共掃描 100 行
3. 所以，整個執(zhí)行流程，總掃描行數(shù)是 200
假設(shè)不使用 join，只能用單表查詢：
1. 執(zhí)行 select * from t1，查出表 t1 的所有數(shù)據(jù)，這里有 100 行
2. 循環(huán)遍歷這 100 行數(shù)據(jù)：
從每一行 R 取出字段 a 的值 $R.a
執(zhí)行 select * from t2 where a=$R.a
把返回的結(jié)果和 R 構(gòu)成結(jié)果集的一行
這個查詢過程，也是掃描了 200 行，但是總共執(zhí)行了 101 條語句，比直接 join 多了 100 次交互。客戶端還要自己拼接 SQL 語句和結(jié)果。這么做還不如直接 join 好
在可以使用被驅(qū)動表的索引的情況下：
使用 join 語句，性能比強(qiáng)行拆成多個單表執(zhí)行 SQL 語句的性能要好
如果使用 join 語句的話，需要讓小表做驅(qū)動表
2、Simple Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.b);由于表 t2 的字段 b 上沒有索引，因此每次到 t2 去匹配的時候，就要做一次全表掃描。這個算法叫做 Simple Nested-Loop Join
這樣算來，這個 SQL 請求就要掃描表 t2 多達(dá) 100 次，總共掃描 100*100=10 萬行
MySQL 沒有使用這個 Simple Nested-Loop Join 算法，而是使用了另一個叫作 Block Nested-Loop Join 的算法，簡稱 BNL
3、Block Nested-Loop Join
被驅(qū)動表上沒有可用的索引，算法的流程如下：
1. 把表 t1 的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中，由于這個語句中寫的是 select *，因此是把整個表 t1 放入了內(nèi)存
2. 掃描表 t2，把表 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)作比對，滿足 join 條件的，作為結(jié)果集的一部分返回
在這個過程中，對表 t1 和表 t2 都做了一次全表掃描，因此總的掃描行數(shù)是 1100。由于 join_buffer 是以無序數(shù)組的方式組織的，因此對表 t2 中的每一行，都要做 100 次判斷，總共需要在內(nèi)存中做的判斷次數(shù)是 100*1000=10 萬次
使用 Simple Nested-Loop Join 算法進(jìn)行查詢，掃描行數(shù)也是 10 萬行。因此，從時間復(fù)雜度上來說，這兩個算法是一樣的。但是，Block Nested-Loop Join 算法的這 10 萬次判斷是內(nèi)存操作，速度上會快很多，性能也更好
假設(shè)小表的行數(shù)是 N，大表的行數(shù)是 M，那么在這個算法里：
1）兩個表都做一次全表掃描，所以總的掃描行數(shù)是 M + N
2）內(nèi)存中的判斷次數(shù)是 M ? N
這時候選擇大表還是小表做驅(qū)動表，執(zhí)行耗時是一樣的
join_buffer 的大小是由參數(shù) join_buffer_size 設(shè)定的，默認(rèn)值是 256k。如果放不下表 t1 的所有數(shù)據(jù)話，策略很簡單，就是分段放
1）掃描表 t1，順序讀取數(shù)據(jù)行放入 join_buffer 中，假設(shè)放到第 88 行 join_buffer 滿了
2）掃描表 t2，把 t2 中的每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比，滿足 join 條件的，作為結(jié)果集的一部分返回
3）清空 join_buffer
4）繼續(xù)掃描表 t1，順序讀取最后的 12 行放入 join_buffer 中，繼續(xù)執(zhí)行第 2 步
由于表 t1 被分成了兩次放入 join_buffer 中，導(dǎo)致表 t2 會被掃描兩次。雖然分成兩次放入 join_buffer，但是判斷等值條件的此時還是不變的
假設(shè)，驅(qū)動表的數(shù)據(jù)行數(shù)是 N，需要分成 K 段才能完成算法流程，被驅(qū)動表的數(shù)據(jù)行數(shù)是 M。這里的 K 不是常數(shù)，N 越大 K 就會越大，因此把 K 表示為 λ ? N，λ 的取值范圍是 (0,1)。所以，在這個算法的執(zhí)行過程中：
1. 掃描行數(shù)是 N + λ ? N ? M
2. 內(nèi)存判斷 N ? M
考慮到掃描行數(shù)，N 小一些，整個算式的結(jié)果會更小。所以應(yīng)該讓小表當(dāng)驅(qū)動表
4、能不能使用 join 語句？
1. 如果可以使用 Index Nested-Loop Join 算法，也就是說可以用上被驅(qū)動表上的索引，其實(shí)是沒問題的
2. 如果使用 Block Nested-Loop Join 算法，掃描行數(shù)就會過多。尤其是在大表上的 join 操作，這樣可能要掃描被驅(qū)動表很多次，會占用大量的系統(tǒng)資源。所以這種 join 盡量不要用
5、如果使用 join，應(yīng)該選擇大表做驅(qū)動表還是選擇小表做驅(qū)動表
1. 如果是 Index Nested-Loop Join 算法，應(yīng)該選擇小表做驅(qū)動表
2. 如果是 Block Nested-Loop Join 算法：
在 join_buffer_size 足夠大的時候，是一樣的
在 join_buffer_size 不夠大的時候，應(yīng)該選擇小表做驅(qū)動表
在決定哪個表做驅(qū)動表的時候，應(yīng)該是兩個表按照各自的條件過濾，過濾完成以后，計算參數(shù) join 的各個字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個表，就是小表，應(yīng)該作為驅(qū)動表
二、join 語句優(yōu)化
創(chuàng)建兩個表 t1、t2
create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
CREATE DEFINER = CURRENT_USER PROCEDURE `idata`()
BEGIN
declare i int;
set i=1;
while(i =1000)do
insert into t1 values(i, 1001-i, i);
set i=i+1;
end while;
set i=1;
while(i =1000000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
END;在表 t1 中，插入了 1000 行數(shù)據(jù)，每一行的 a =1001-id 的值。也就是說，表 t1 中字段 a 是逆序的。同時，在表 t2 中插入了 100 萬行數(shù)據(jù)
1、Multi-Range Read 優(yōu)化
Multi-Range Read（MRR）優(yōu)化主要的目的是盡量使用順序讀盤
select * from t1 where a =1 and a =100;主鍵索引是一棵 B + 樹，在這棵樹上，每次只能根據(jù)一個主鍵 id 查到一行數(shù)據(jù)。因此，回表是一行行搜索主鍵索引的
如果隨著 a 的值遞增順序查找的話，id 的值就變成隨機(jī)的，那么就會出現(xiàn)隨機(jī)訪問，性能相對較差
因?yàn)榇蠖鄶?shù)的數(shù)據(jù)都是按照主鍵遞增順序插入得到的，所以如果按照主鍵的遞增順序查詢，對磁盤的讀比較接近順序讀，能夠提升讀性能
這就是 MRR 優(yōu)化的設(shè)計思路，語句的執(zhí)行流程如下：
1. 根據(jù)索引 a，定位到滿足條件的記錄，將 id 值放入 read_rnd_buffer 中
2. 將 read_rnd_buffer 中的 id 進(jìn)行遞增排序
3. 排序后的 id 數(shù)組，依次到主鍵 id 索引中查記錄，并作為結(jié)果返回
read_rnd_buffer 的大小是由 read_rnd_buffer_size 參數(shù)控制的。如果步驟 1 中，read_rnd_buffer 放滿了，就會先執(zhí)行完步驟 2 和 3，然后清空 read_rnd_buffer。之后繼續(xù)找索引 a 的下個記錄，并繼續(xù)循環(huán)
如果想要穩(wěn)定地使用 MRR 優(yōu)化的話，需要設(shè)置 set optimizer_switch= mrr_cost_based=off
explain 結(jié)果中，Extra 字段多了 Using MRR，表示的是用上了 MRR 優(yōu)化。由于在 read_rnd_buffer 中按照 id 做了排序，所以最后得到的結(jié)果也是按照主鍵 id 遞增順序的
MRR 能夠提升性能的核心在于，這條查詢語句在索引 a 上做的是一個范圍查詢，可以得到足夠多的主鍵 id。這樣通過排序以后，再去主鍵索引查數(shù)據(jù)，才能體現(xiàn)出順序性的優(yōu)勢
2、Batched Key Access
MySQL5.6 引入了 Batched Key Access（BKA）算法。這個 BKA 算法是對 NLJ 算法的優(yōu)化
NLJ 算法流程圖：
NLJ 算法執(zhí)行的邏輯是從驅(qū)動表 t1，一行行地取出 a 的值，再到被驅(qū)動表 t2 去做 join
BKA 算法流程圖：
BKA 算法執(zhí)行的邏輯是把表 t1 的數(shù)據(jù)取出來一部分，先放到一個 join_buffer，一起傳給表 t2。在 join_buffer 中只會放入查詢需要的字段，如果 join_buffer 放不下所有數(shù)據(jù)，就會將數(shù)據(jù)分成多段執(zhí)行上圖的流程
如果想要使用 BKA 優(yōu)化算法的話，執(zhí)行 SQL 語句之前，先設(shè)置
set optimizer_switch= mrr=on,mrr_cost_based=off,batched_key_access=on其中前兩個參數(shù)的作用是啟用 MRR，原因是 BKA 算法的優(yōu)化要依賴與 MRR
3、BNL 算法的性能問題
InnoDB 對 Buffer Pool 的 LRU 算法做了優(yōu)化，即：第一次從磁盤讀入內(nèi)存的數(shù)據(jù)頁，會先放在 old 區(qū)域。如果 1 秒之后這個數(shù)據(jù)頁不再被訪問了，就不會被移動到 LRU 鏈表頭部，這樣對 Buffer Pool 的命中率影響就不大
如果一個使用 BNL 算法的 join 語句，多次掃描一個冷表，而且這個語句執(zhí)行時間超過 1 秒，就會在再次掃描冷表的時候，把冷表的數(shù)據(jù)頁移到 LRU 鏈表頭部。這種情況對應(yīng)的，是冷表的數(shù)據(jù)量小于整個 Buffer Pool 的 3 /8，能夠完全放入 old 區(qū)域的情況
如果這個冷表很大，就會出現(xiàn)另外一種情況：業(yè)務(wù)正常訪問的數(shù)據(jù)頁，沒有機(jī)會進(jìn)入 young 區(qū)域。
由于優(yōu)化機(jī)制的存在，一個正常訪問的數(shù)據(jù)頁，要進(jìn)入 young 區(qū)域，需要隔 1 秒后再次被訪問到。但是，由于 join 語句在循環(huán)讀磁盤和淘汰內(nèi)存頁，進(jìn)入 old 區(qū)域的數(shù)據(jù)頁，很可能在 1 秒之內(nèi)就被淘汰了。這樣就會導(dǎo)致 MySQL 實(shí)例的 Buffer Pool 在這段時間內(nèi)，young 區(qū)域的數(shù)據(jù)頁沒有被合理地淘汰
BNL 算法對系統(tǒng)的影響主要包括三個方面：
1. 可能會多次掃描被驅(qū)動表，占用磁盤 IO 資源
2. 判斷 join 條件需要執(zhí)行 M?N 次對比，如果是大表就會占用非常多的 CPU 資源
3. 可能會導(dǎo)致 Buffer Pool 的熱數(shù)據(jù)被淘汰，影響內(nèi)存命中率
4、BNL 轉(zhuǎn) BKA
一些情況下，我們可以直接在被驅(qū)動表上建索引，這時就可以直接轉(zhuǎn)成 BKA 算法了
如果碰到一些不適合在被驅(qū)動表上建索引的情況，可以考慮使用臨時表。大致思路如下：
select * from t1 join t2 on (t1.b=t2.b) where t2.b =1 and t2.b =2000;1）把表 t2 中滿足條件的數(shù)據(jù)放在臨時表 tmp_t 中
2）為了讓 join 使用 BKA 算法，給臨時表 tmp_t 的字段 b 加上索引
3）讓表 t1 和 tmp_t 做 join 操作
SQL 語句寫法如下：
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b =1 and b =2000;select * from t1 join temp_t on (t1.b=temp_t.b);5、擴(kuò)展 hash join
MySQL 的優(yōu)化器和執(zhí)行器不支持哈希 join，可以自己實(shí)現(xiàn)在業(yè)務(wù)端，實(shí)現(xiàn)流程大致如下：
1.select * from t1; 取得表 t1 的全部 1000 行數(shù)據(jù)，在業(yè)務(wù)端存入一個 hash 結(jié)構(gòu)
2.select * from t2 where b =1 and b =2000; 獲取表 t2 中滿足條件的 2000 行數(shù)據(jù)
3. 把這 2000 行數(shù)據(jù)，一行一行地取到業(yè)務(wù)端，到 hash 結(jié)構(gòu)的數(shù)據(jù)表中尋找匹配的數(shù)據(jù)。滿足匹配的條件的這行數(shù)據(jù)，就作為結(jié)果集的一行
到此，相信大家對“MySQL 中的 join 語句算法如何優(yōu)化”有了更深的了解，不妨來實(shí)際操作一番吧！這里是丸趣 TV 網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
