中文亚洲精品无码_熟女乱子伦免费_人人超碰人人爱国产_亚洲熟妇女综合网

當(dāng)前位置: 首頁(yè) > news >正文

網(wǎng)站建設(shè)初步規(guī)劃方案深圳網(wǎng)站設(shè)計(jì)小程序

網(wǎng)站建設(shè)初步規(guī)劃方案,深圳網(wǎng)站設(shè)計(jì)小程序,海豐網(wǎng)站建設(shè),蘇州高端網(wǎng)站建設(shè)企業(yè)Join語(yǔ)句執(zhí)行流程 Hi,我是阿昌,今天學(xué)習(xí)記錄的是關(guān)于Join語(yǔ)句執(zhí)行流程的內(nèi)容。 在實(shí)際生產(chǎn)中,關(guān)于 join 語(yǔ)句使用的問(wèn)題,一般會(huì)集中在以下兩類(lèi): 不讓使用 join,使用 join 有什么問(wèn)題呢?如果有…

Join語(yǔ)句執(zhí)行流程

Hi,我是阿昌,今天學(xué)習(xí)記錄的是關(guān)于Join語(yǔ)句執(zhí)行流程的內(nèi)容。

在實(shí)際生產(chǎn)中,關(guān)于 join 語(yǔ)句使用的問(wèn)題,一般會(huì)集中在以下兩類(lèi):

  1. 不讓使用 join,使用 join 有什么問(wèn)題呢?
  2. 如果有兩個(gè)大小不同的表做 join,應(yīng)該用哪個(gè)表做驅(qū)動(dòng)表呢?

創(chuàng)建兩個(gè)表 t1t2 來(lái)說(shuō)明。

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;drop procedure idata;
delimiter ;;
create procedure idata()
begindeclare i int;set i=1;while(i<=1000)doinsert into t2 values(i, i, i);set i=i+1;end while;
end;;
delimiter ;
call idata();create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

可以看到,這兩個(gè)表都有一個(gè)主鍵索引 id 和一個(gè)索引 a,字段 b 上無(wú)索引。

存儲(chǔ)過(guò)程 idata() 往表 t2 里插入了 1000 行數(shù)據(jù),在表 t1 里插入的是 100 行數(shù)據(jù)。


一、Index Nested-Loop Join

如果直接使用 join 語(yǔ)句,MySQL 優(yōu)化器可能會(huì)選擇表 t1 或 t2 作為驅(qū)動(dòng)表,這樣會(huì)影響分析 SQL 語(yǔ)句的執(zhí)行過(guò)程。

所以,為了便于分析執(zhí)行過(guò)程中的性能問(wèn)題,改用 straight_join 讓 MySQL 使用固定的連接方式執(zhí)行查詢(xún),這樣優(yōu)化器只會(huì)按照指定的方式去 join。

來(lái)看一下這個(gè)語(yǔ)句:

select * from t1 straight_join t2 on (t1.a=t2.a);

在這個(gè)語(yǔ)句里,t1 是驅(qū)動(dòng)表,t2 是被驅(qū)動(dòng)表。

現(xiàn)在,來(lái)看一下這條語(yǔ)句的 explain 結(jié)果。

圖 1 使用索引字段 join 的 explain 結(jié)果

可以看到,在這條語(yǔ)句里,被驅(qū)動(dòng)表 t2 的字段 a 上有索引,join 過(guò)程用上了這個(gè)索引,因此這個(gè)語(yǔ)句的執(zhí)行流程是這樣的:

  1. 從表 t1 中讀入一行數(shù)據(jù) R;
  2. 從數(shù)據(jù)行 R 中,取出 a 字段到表 t2 里去查找;
  3. 取出表 t2 中滿(mǎn)足條件的行,跟 R 組成一行,作為結(jié)果集的一部分;
  4. 重復(fù)執(zhí)行步驟 1 到 3,直到表 t1 的末尾循環(huán)結(jié)束。

這個(gè)過(guò)程是先遍歷表 t1,然后根據(jù)從表 t1 中取出的每行數(shù)據(jù)中的 a 值,去表 t2 中查找滿(mǎn)足條件的記錄。

在形式上,這個(gè)過(guò)程就跟寫(xiě)程序時(shí)的嵌套查詢(xún)類(lèi)似,并且可以用上被驅(qū)動(dòng)表的索引,所以稱(chēng)之為“Index Nested-Loop Join”,簡(jiǎn)稱(chēng) NLJ。它對(duì)應(yīng)的流程圖如下所示:

圖 2 Index Nested-Loop Join 算法的執(zhí)行流程

在這個(gè)流程里:

  1. 對(duì)驅(qū)動(dòng)表 t1 做了全表掃描,這個(gè)過(guò)程需要掃描 100 行;
  2. 而對(duì)于每一行 R,根據(jù) a 字段去表 t2 查找,走的是樹(shù)搜索過(guò)程。由于構(gòu)造的數(shù)據(jù)都是一一對(duì)應(yīng)的,因此每次的搜索過(guò)程都只掃描一行,也是總共掃描 100 行;
  3. 所以,整個(gè)執(zhí)行流程,總掃描行數(shù)是 200。

能不能使用 join?

假設(shè)不使用 join,那就只能用單表查詢(xún)。

看看上面這條語(yǔ)句的需求,用單表查詢(xún)?cè)趺磳?shí)現(xiàn)。

  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é)果集的一行。

可以看到,在這個(gè)查詢(xún)過(guò)程,也是掃描了 200 行,但是總共執(zhí)行了 101 條語(yǔ)句,比直接 join 多了 100 次交互。

除此之外,客戶(hù)端還要自己拼接 SQL 語(yǔ)句和結(jié)果。

顯然,這么做還不如直接 join 好。


怎么選擇驅(qū)動(dòng)表?

在這個(gè) join 語(yǔ)句執(zhí)行過(guò)程中,==驅(qū)動(dòng)表是走全表掃描,而被驅(qū)動(dòng)表是走樹(shù)搜索。==假設(shè)被驅(qū)動(dòng)表的行數(shù)是 M。

每次在被驅(qū)動(dòng)表查一行數(shù)據(jù),要先搜索索引 a,再搜索主鍵索引。

每次搜索一棵樹(shù)近似復(fù)雜度是以 2 為底的 M 的對(duì)數(shù),記為 log2M,所以在被驅(qū)動(dòng)表上查一行的時(shí)間復(fù)雜度是 2*log2M。

假設(shè)驅(qū)動(dòng)表的行數(shù)是 N,執(zhí)行過(guò)程就要掃描驅(qū)動(dòng)表 N 行,然后對(duì)于每一行,到被驅(qū)動(dòng)表上匹配一次。

因此整個(gè)執(zhí)行過(guò)程,近似復(fù)雜度是 N + N*2*log2M。

顯然,N 對(duì)掃描行數(shù)的影響更大,因此應(yīng)該讓小表來(lái)做驅(qū)動(dòng)表。

如果沒(méi)覺(jué)得這個(gè)影響有那么“顯然”, 可以這么理解:
N 擴(kuò)大 1000 倍的話(huà),掃描行數(shù)就會(huì)擴(kuò)大 1000 倍;
而 M 擴(kuò)大 1000 倍,掃描行數(shù)擴(kuò)大不到 10 倍。


小結(jié)一下,通過(guò)上面的分析得到了兩個(gè)結(jié)論:

  1. 使用 join 語(yǔ)句,性能比強(qiáng)行拆成多個(gè)單表執(zhí)行 SQL 語(yǔ)句的性能要好;
  2. 如果使用 join 語(yǔ)句的話(huà),需要讓小表做驅(qū)動(dòng)表。

但是,需要注意,這個(gè)結(jié)論的前提是“可以使用被驅(qū)動(dòng)表的索引”。


二、Simple Nested-Loop Join

再看看被驅(qū)動(dòng)表用不上索引的情況。

現(xiàn)在,把 SQL 語(yǔ)句改成這樣:

select * from t1 straight_join t2 on (t1.a=t2.b);

由于表 t2 的字段 b 上沒(méi)有索引,因此再用圖 2 的執(zhí)行流程時(shí),每次到 t2 去匹配的時(shí)候,就要做一次全表掃描。

你可以先設(shè)想一下這個(gè)問(wèn)題,繼續(xù)使用圖 2 的算法,是不是可以得到正確的結(jié)果呢?

如果只看結(jié)果的話(huà),這個(gè)算法是正確的,而且這個(gè)算法也有一個(gè)名字,叫做“Simple Nested-Loop Join”。

但是,這樣算來(lái),這個(gè) SQL 請(qǐng)求就要掃描表 t2 多達(dá) 100 次,總共掃描 100*1000=10 萬(wàn)行。

這還只是兩個(gè)小表,如果 t1 和 t2 都是 10 萬(wàn)行的表(當(dāng)然了,這也還是屬于小表的范圍),就要掃描 100 億行,這個(gè)算法看上去太“笨重”了。


三、Block Nested-Loop Join

當(dāng)然,MySQL 也沒(méi)有使用這個(gè) Simple Nested-Loop Join 算法,而是使用了另一個(gè)叫作“Block Nested-Loop Join”的算法,簡(jiǎn)稱(chēng) BNL。

這時(shí)候,被驅(qū)動(dòng)表上沒(méi)有可用的索引,算法的流程是這樣的:

  1. 把表 t1 的數(shù)據(jù)讀入線(xiàn)程內(nèi)存 join_buffer 中,由于我們這個(gè)語(yǔ)句中寫(xiě)的是 select *,因此是把整個(gè)表 t1 放入了內(nèi)存;
  2. 掃描表 t2,把表 t2 中的每一行取出來(lái),跟 join_buffer 中的數(shù)據(jù)做對(duì)比,滿(mǎn)足 join 條件的,作為結(jié)果集的一部分返回。

這個(gè)過(guò)程的流程圖如下:

圖 3 Block Nested-Loop Join 算法的執(zhí)行流程

對(duì)應(yīng)地,這條 SQL 語(yǔ)句的 explain 結(jié)果如下所示:

圖 4 不使用索引字段 join 的 explain 結(jié)果
可以看到,在這個(gè)過(guò)程中,對(duì)表 t1 和 t2 都做了一次全表掃描,因此總的掃描行數(shù)是 1100。

由于 join_buffer 是以無(wú)序數(shù)組的方式組織的,因此對(duì)表 t2 中的每一行,都要做 100 次判斷,總共需要在內(nèi)存中做的判斷次數(shù)是:100*1000=10 萬(wàn)次。

前面我們說(shuō)過(guò),如果使用 Simple Nested-Loop Join 算法進(jìn)行查詢(xún),掃描行數(shù)也是 10 萬(wàn)行。因此,從時(shí)間復(fù)雜度上來(lái)說(shuō),這兩個(gè)算法是一樣的。但是,Block Nested-Loop Join 算法的這 10 萬(wàn)次判斷是內(nèi)存操作,速度上會(huì)快很多,性能也更好。


在這種情況下,應(yīng)該選擇哪個(gè)表做驅(qū)動(dòng)表。

假設(shè)小表的行數(shù)是 N,大表的行數(shù)是 M,那么在這個(gè)算法里:

  1. 兩個(gè)表都做一次全表掃描,所以總的掃描行數(shù)是 M+N;
  2. 內(nèi)存中的判斷次數(shù)是 M*N。

可以看到,調(diào)換這兩個(gè)算式中的 M 和 N 沒(méi)差別,因此這時(shí)候選擇大表還是小表做驅(qū)動(dòng)表,執(zhí)行耗時(shí)是一樣的。

這個(gè)例子里表 t1 才 100 行,要是表 t1 是一個(gè)大表,join_buffer 放不下怎么辦呢?

join_buffer 的大小是由參數(shù) join_buffer_size 設(shè)定的,默認(rèn)值是 256k。如果放不下表 t1 的所有數(shù)據(jù)話(huà),策略很簡(jiǎn)單,就是分段放。

join_buffer_size 改成 1200,再執(zhí)行:

select * from t1 straight_join t2 on (t1.a=t2.b);

執(zhí)行過(guò)程就變成了:

  1. 掃描表 t1,順序讀取數(shù)據(jù)行放入 join_buffer 中,放完第 88 行 join_buffer 滿(mǎn)了,繼續(xù)第 2 步;
  2. 掃描表 t2,把 t2 中的每一行取出來(lái),跟 join_buffer 中的數(shù)據(jù)做對(duì)比,滿(mǎn)足 join 條件的,作為結(jié)果集的一部分返回;
  3. 清空 join_buffer;
  4. 繼續(xù)掃描表 t1,順序讀取最后的 12 行數(shù)據(jù)放入 join_buffer 中,繼續(xù)執(zhí)行第 2 步。

執(zhí)行流程圖也就變成這樣:
圖 5 Block Nested-Loop Join -- 兩段

圖中的步驟 4 和 5,表示清空 join_buffer 再?gòu)?fù)用。

這個(gè)流程才體現(xiàn)出了這個(gè)算法名字中“Block”的由來(lái),表示“分塊去 join”。

可以看到,這時(shí)候由于表 t1 被分成了兩次放入 join_buffer 中,導(dǎo)致表 t2 會(huì)被掃描兩次。

雖然分成兩次放入 join_buffer,但是判斷等值條件的次數(shù)還是不變的,依然是 (88+12)*1000=10 萬(wàn)次。

在這種情況下驅(qū)動(dòng)表的選擇問(wèn)題。

假設(shè),驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 N,需要分 K 段才能完成算法流程,被驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是 M。

注意,這里的 K 不是常數(shù),N 越大 K 就會(huì)越大,因此把 K 表示為λ*N,顯然λ的取值范圍是 (0,1)。

所以,在這個(gè)算法的執(zhí)行過(guò)程中:

  1. 掃描行數(shù)是 N+λNM;
  2. 內(nèi)存判斷 N*M 次。

顯然,內(nèi)存判斷次數(shù)是不受選擇哪個(gè)表作為驅(qū)動(dòng)表影響的。

而考慮到掃描行數(shù),在 M 和 N 大小確定的情況下,N 小一些,整個(gè)算式的結(jié)果會(huì)更小。所以結(jié)論是,應(yīng)該讓小表當(dāng)驅(qū)動(dòng)表。

在 N+λNM 這個(gè)式子里,λ才是影響掃描行數(shù)的關(guān)鍵因素,這個(gè)值越小越好。

剛剛我們說(shuō)了 N 越大,分段數(shù) K 越大。那么,N 固定的時(shí)候,什么參數(shù)會(huì)影響 K 的大小呢?(也就是λ的大小)答案是 join_buffer_size。

join_buffer_size 越大,一次可以放入的行越多,分成的段數(shù)也就越少,對(duì)被驅(qū)動(dòng)表的全表掃描次數(shù)就越少。

如果你的 join 語(yǔ)句很慢,就把 join_buffer_size 改大。


第一個(gè)問(wèn)題:能不能使用 join 語(yǔ)句?

  1. 如果可以使用 Index Nested-Loop Join 算法,也就是說(shuō)可以用上被驅(qū)動(dòng)表上的索引,其實(shí)是沒(méi)問(wèn)題的;
  2. 如果使用 Block Nested-Loop Join 算法,掃描行數(shù)就會(huì)過(guò)多。尤其是在大表上的 join 操作,這樣可能要掃描被驅(qū)動(dòng)表很多次,會(huì)占用大量的系統(tǒng)資源。所以這種 join 盡量不要用。

所以在判斷要不要使用 join 語(yǔ)句時(shí),就是看 explain 結(jié)果里面,Extra 字段里面有沒(méi)有出現(xiàn)“Block Nested Loop”字樣。


第二個(gè)問(wèn)題是:如果要使用 join,應(yīng)該選擇大表做驅(qū)動(dòng)表還是選擇小表做驅(qū)動(dòng)表?

  1. 如果是 Index Nested-Loop Join 算法,應(yīng)該選擇小表做驅(qū)動(dòng)表;
  2. 如果是 Block Nested-Loop Join 算法:
    • 在 join_buffer_size 足夠大的時(shí)候,是一樣的;
    • 在 join_buffer_size 不夠大的時(shí)候(這種情況更常見(jiàn)),應(yīng)該選擇小表做驅(qū)動(dòng)表。

所以,這個(gè)問(wèn)題的結(jié)論就是,總是應(yīng)該使用小表做驅(qū)動(dòng)表


當(dāng)然了,這里我需要說(shuō)明下,什么叫作“小表”。

如果我在語(yǔ)句的 where 條件加上 t2.id<=50 這個(gè)限定條件,再來(lái)看下這兩條語(yǔ)句:

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

注意,為了讓兩條語(yǔ)句的被驅(qū)動(dòng)表都用不上索引,所以 join 字段都使用了沒(méi)有索引的字段 b。

但如果是用第二個(gè)語(yǔ)句的話(huà),join_buffer 只需要放入 t2 的前 50 行,顯然是更好的。

所以這里,“t2 的前 50 行”是那個(gè)相對(duì)小的表,也就是“小表”。


再來(lái)看另外一組例子:

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

這個(gè)例子里,表 t1 和 t2 都是只有 100 行參加 join。

但是,這兩條語(yǔ)句每次查詢(xún)放入 join_buffer 中的數(shù)據(jù)是不一樣的:

  • 表 t1 只查字段 b,因此如果把 t1 放到 join_buffer 中,則 join_buffer 中只需要放入 b 的值;
  • 表 t2 需要查所有的字段,因此如果把表 t2 放到 join_buffer 中的話(huà),就需要放入三個(gè)字段 id、a 和 b。

應(yīng)該選擇表 t1 作為驅(qū)動(dòng)表。也就是說(shuō)在這個(gè)例子里,“只需要一列參與 join 的表 t1”是那個(gè)相對(duì)小的表。

所以,更準(zhǔn)確地說(shuō),在決定哪個(gè)表做驅(qū)動(dòng)表的時(shí)候,應(yīng)該是兩個(gè)表按照各自的條件過(guò)濾,過(guò)濾完成之后,計(jì)算參與 join 的各個(gè)字段的總數(shù)據(jù)量,數(shù)據(jù)量小的那個(gè)表,就是“小表”,應(yīng)該作為驅(qū)動(dòng)表。


四、總結(jié)

  1. 如果可以使用被驅(qū)動(dòng)表的索引,join 語(yǔ)句還是有其優(yōu)勢(shì)的;
  2. 不能使用被驅(qū)動(dòng)表的索引,只能使用 Block Nested-Loop Join 算法,這樣的語(yǔ)句就盡量不要使用;
  3. 在使用 join 的時(shí)候,應(yīng)該讓小表做驅(qū)動(dòng)表。

使用 Block Nested-Loop Join 算法,可能會(huì)因?yàn)?join_buffer 不夠大,需要對(duì)被驅(qū)動(dòng)表做多次全表掃描。如果被驅(qū)動(dòng)表是一個(gè)大表,并且是一個(gè)冷數(shù)據(jù)表,除了查詢(xún)過(guò)程中可能會(huì)導(dǎo)致 IO 壓力大以外,覺(jué)得對(duì)這個(gè) MySQL 服務(wù)還有什么更嚴(yán)重的影響嗎?

如果被驅(qū)動(dòng)表是一個(gè)大表(因?yàn)椴徽撚肂NL還是ILJ算法) 都是優(yōu)先讓被參與join的總的字段量較大的一張表作為一個(gè)被驅(qū)動(dòng)表。

但是由于關(guān)聯(lián)的時(shí)候被驅(qū)動(dòng)表的數(shù)據(jù)會(huì)頻繁被走索引數(shù), 所以根據(jù)MYSQL 的LRU算法 其實(shí)冷數(shù)據(jù)也會(huì)被提到鏈表的前部 ,造成冷數(shù)據(jù)的前移,其余業(yè)務(wù)數(shù)據(jù)被淘汰。 造成內(nèi)存命中率降低。 請(qǐng)求響應(yīng)變慢,業(yè)務(wù)可能造成阻塞。


http://www.risenshineclean.com/news/50232.html

相關(guān)文章:

  • 做ppt素材的網(wǎng)站開(kāi)創(chuàng)集團(tuán)與百度
  • 張家港做網(wǎng)站優(yōu)化排名十大經(jīng)典廣告營(yíng)銷(xiāo)案例
  • 設(shè)計(jì)一個(gè)網(wǎng)站的價(jià)格表seo培訓(xùn)班
  • 一站式做網(wǎng)站報(bào)價(jià)上海做網(wǎng)站優(yōu)化
  • 深夜小網(wǎng)站軟文文案
  • 有專(zhuān)業(yè)做網(wǎng)站的學(xué)校嗎搜索引擎關(guān)鍵詞競(jìng)價(jià)排名
  • 網(wǎng)站開(kāi)發(fā)流程包括需求分析seo推廣思路
  • 學(xué)做網(wǎng)站視頻論壇免費(fèi)友情鏈接網(wǎng)
  • 做app需要什么軟件手機(jī)優(yōu)化助手下載
  • 湛江網(wǎng)站開(kāi)發(fā)公司網(wǎng)站營(yíng)銷(xiāo)推廣有哪些
  • 電腦網(wǎng)站建設(shè)網(wǎng)絡(luò)推廣公司口碑
  • 網(wǎng)站建設(shè)模板一次收費(fèi)如何搜索網(wǎng)頁(yè)關(guān)鍵詞
  • 重慶建網(wǎng)站多少錢(qián)百度指數(shù)是免費(fèi)的嗎
  • 網(wǎng)站廣告條動(dòng)畫(huà) 怎么做深圳關(guān)鍵詞排名優(yōu)化系統(tǒng)
  • 安全的響應(yīng)式網(wǎng)站建設(shè)聊城網(wǎng)站推廣公司
  • 揭陽(yáng)建設(shè)網(wǎng)站北京網(wǎng)站建設(shè)運(yùn)營(yíng)
  • 公司做賣(mài)網(wǎng)站有前景嗎最佳磁力吧ciliba搜索引擎
  • 站群管理系統(tǒng)cms推廣普通話(huà)的宣傳內(nèi)容
  • 青海網(wǎng)網(wǎng)站建設(shè)寧波網(wǎng)站關(guān)鍵詞優(yōu)化公司
  • 網(wǎng)站怎么做uc整合百度競(jìng)價(jià)排名的使用方法
  • 找事做網(wǎng)站怎么弄百度網(wǎng)頁(yè)版登錄入口官網(wǎng)
  • 泰安集團(tuán)網(wǎng)站建設(shè)元搜索引擎有哪些
  • 個(gè)人攝影網(wǎng)站制作設(shè)計(jì)培訓(xùn)學(xué)院
  • 鄧州企業(yè)網(wǎng)站有鏈接的網(wǎng)站
  • 商城網(wǎng)站建設(shè)預(yù)算免費(fèi)網(wǎng)頁(yè)制作網(wǎng)站
  • magento建站是傻瓜式的嗎今日疫情最新情況
  • 笑話(huà)類(lèi)網(wǎng)站用什么做網(wǎng)絡(luò)運(yùn)營(yíng)怎么做
  • 長(zhǎng)沙市網(wǎng)站建設(shè)推廣谷歌推廣技巧
  • 南京專(zhuān)業(yè)網(wǎng)站建設(shè)整合營(yíng)銷(xiāo)傳播的概念
  • 世紀(jì)佳緣網(wǎng)站開(kāi)發(fā)公司網(wǎng)站大全軟件下載