join 幾種算法筆記

這兩個(gè)表都有一個(gè)主鍵索引 id 和一個(gè)索引 a械馆,字段 b 上無(wú)索引缕碎。t1 100行乙嘀,t2 1000行

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 table t1 like t2;

1.Index Nested-Loop Join

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

STRAIGHT_JOIN只適用于內(nèi)連接恃疯,因?yàn)閘eft join殴胧、right join已經(jīng)知道了哪個(gè)表作為驅(qū)動(dòng)表,哪個(gè)表作為被驅(qū)動(dòng)表戳鹅,比如left join就是以左表為驅(qū)動(dòng)表均驶,right join反之,而STRAIGHT_JOIN就是在內(nèi)連接中使用枫虏,而強(qiáng)制使用左表來(lái)當(dāng)驅(qū)動(dòng)表妇穴,所以這個(gè)特性可以用于一些調(diào)優(yōu),強(qiáng)制改變mysql的優(yōu)化器選擇的執(zhí)行計(jì)劃

在這個(gè)語(yǔ)句里隶债,STRAIGHT_JOIN 指定左表為驅(qū)動(dòng)表腾它,t1 是驅(qū)動(dòng)表,t2 是被驅(qū)動(dòng)表死讹。
執(zhí)行流程是這樣的:
從表 t1 中讀入一行數(shù)據(jù) R瞒滴;
從數(shù)據(jù)行 R 中,取出 a 字段到表 t2 里去查找回俐;
取出表 t2 中滿足條件的行逛腿,跟 R 組成一行稀并,作為結(jié)果集的一部分仅颇;
重復(fù)執(zhí)行步驟 1 到 3,直到表 t1 的末尾循環(huán)結(jié)束碘举。

這個(gè) join 語(yǔ)句執(zhí)行過(guò)程中忘瓦,驅(qū)動(dòng)表是走全表掃描,而被驅(qū)動(dòng)表(存在索引)是走樹(shù)搜索引颈。


image.png

2.Simple Nested-Loop Join

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

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

3.Block Nested-Loop Join

被驅(qū)動(dòng)表上沒(méi)有可用的索引,算法的流程是這樣的:把表 t1 的數(shù)據(jù)讀入線程內(nèi)存 join_buffer 中售滤,由于我們這個(gè)語(yǔ)句中寫(xiě)的是 select *罚拟,因此是把整個(gè)表 t1 放入了內(nèi)存台诗;掃描表 t2,把表 t2 中的每一行取出來(lái)赐俗,跟 join_buffer 中的數(shù)據(jù)做對(duì)比拉队,滿足 join 條件的,作為結(jié)果集的一部分返回阻逮。


image.png

在這個(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)行查詢食呻,掃描行數(shù)也是 10 萬(wàn)行流炕。因此,從時(shí)間復(fù)雜度上來(lái)說(shuō)仅胞,這兩個(gè)算法是一樣的每辟。但是,Block Nested-Loop Join 算法的這 10 萬(wàn)次判斷是內(nèi)存操作干旧,速度上會(huì)快很多渠欺,性能也更好。

4.Multi-Range Read 優(yōu)化

Multi-Range Read 優(yōu)化 (MRR)椎眯。這個(gè)優(yōu)化的主要目的是盡量使用順序讀盤(pán)挠将。字段a存在索引

select * from t1 where a>=1 and a<=100;

主鍵索引是一棵 B+ 樹(shù),在這棵樹(shù)上编整,每次只能根據(jù)一個(gè)主鍵 id 查到一行數(shù)據(jù)舔稀。因此,回表肯定是一行行搜索主鍵索引的


image.png

MRR 優(yōu)化的設(shè)計(jì)思路掌测。此時(shí)内贮,語(yǔ)句的執(zhí)行流程變成了這樣:
a.根據(jù)索引 a,定位到滿足條件的記錄汞斧,將 id 值放入 read_rnd_buffer 中 ;
b.將 read_rnd_buffer 中的 id 進(jìn)行遞增排序夜郁;
c.排序后的 id 數(shù)組,依次到主鍵 id 索引中查記錄粘勒,并作為結(jié)果返回

image.png
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末竞端,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子庙睡,更是在濱河造成了極大的恐慌事富,老刑警劉巖剑勾,帶你破解...
    沈念sama閱讀 217,509評(píng)論 6 504
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異赵颅,居然都是意外死亡虽另,警方通過(guò)查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,806評(píng)論 3 394
  • 文/潘曉璐 我一進(jìn)店門饺谬,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)捂刺,“玉大人,你說(shuō)我怎么就攤上這事募寨∽逭梗” “怎么了?”我有些...
    開(kāi)封第一講書(shū)人閱讀 163,875評(píng)論 0 354
  • 文/不壞的土叔 我叫張陵拔鹰,是天一觀的道長(zhǎng)仪缸。 經(jīng)常有香客問(wèn)我,道長(zhǎng)列肢,這世上最難降的妖魔是什么恰画? 我笑而不...
    開(kāi)封第一講書(shū)人閱讀 58,441評(píng)論 1 293
  • 正文 為了忘掉前任,我火速辦了婚禮瓷马,結(jié)果婚禮上拴还,老公的妹妹穿的比我還像新娘。我一直安慰自己欧聘,他們只是感情好片林,可當(dāng)我...
    茶點(diǎn)故事閱讀 67,488評(píng)論 6 392
  • 文/花漫 我一把揭開(kāi)白布。 她就那樣靜靜地躺著怀骤,像睡著了一般费封。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上蒋伦,一...
    開(kāi)封第一講書(shū)人閱讀 51,365評(píng)論 1 302
  • 那天弓摘,我揣著相機(jī)與錄音,去河邊找鬼凉敲。 笑死衣盾,一個(gè)胖子當(dāng)著我的面吹牛寺旺,可吹牛的內(nèi)容都是我干的爷抓。 我是一名探鬼主播,決...
    沈念sama閱讀 40,190評(píng)論 3 418
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼阻塑,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼蓝撇!你這毒婦竟也來(lái)了?” 一聲冷哼從身側(cè)響起陈莽,我...
    開(kāi)封第一講書(shū)人閱讀 39,062評(píng)論 0 276
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤渤昌,失蹤者是張志新(化名)和其女友劉穎虽抄,沒(méi)想到半個(gè)月后,有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體独柑,經(jīng)...
    沈念sama閱讀 45,500評(píng)論 1 314
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡迈窟,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,706評(píng)論 3 335
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了忌栅。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片车酣。...
    茶點(diǎn)故事閱讀 39,834評(píng)論 1 347
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖索绪,靈堂內(nèi)的尸體忽然破棺而出湖员,到底是詐尸還是另有隱情,我是刑警寧澤瑞驱,帶...
    沈念sama閱讀 35,559評(píng)論 5 345
  • 正文 年R本政府宣布娘摔,位于F島的核電站,受9級(jí)特大地震影響唤反,放射性物質(zhì)發(fā)生泄漏凳寺。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,167評(píng)論 3 328
  • 文/蒙蒙 一彤侍、第九天 我趴在偏房一處隱蔽的房頂上張望读第。 院中可真熱鬧,春花似錦拥刻、人聲如沸怜瞒。這莊子的主人今日做“春日...
    開(kāi)封第一講書(shū)人閱讀 31,779評(píng)論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)吴汪。三九已至,卻和暖如春蒸眠,著一層夾襖步出監(jiān)牢的瞬間漾橙,已是汗流浹背。 一陣腳步聲響...
    開(kāi)封第一講書(shū)人閱讀 32,912評(píng)論 1 269
  • 我被黑心中介騙來(lái)泰國(guó)打工楞卡, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留霜运,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 47,958評(píng)論 2 370
  • 正文 我出身青樓蒋腮,卻偏偏與公主長(zhǎng)得像淘捡,于是被迫代替她去往敵國(guó)和親。 傳聞我的和親對(duì)象是個(gè)殘疾皇子池摧,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,779評(píng)論 2 354

推薦閱讀更多精彩內(nèi)容

  • 概述 相信有開(kāi)發(fā)或DBA小伙伴,對(duì)于mysql處理多表關(guān)聯(lián)方式或者說(shuō)性能方面一直不太滿意,對(duì)于開(kāi)發(fā)提交的join查...
    Sunny捏閱讀 787評(píng)論 0 0
  • 1. join 有什么問(wèn)題作彤? 2. 兩個(gè)大小不同表 join膘魄,哪個(gè)做驅(qū)動(dòng)表乌逐? 主鍵索引 id 和索引 a,b 上無(wú)...
    hedgehog1112閱讀 556評(píng)論 0 0
  • 一创葡、join的執(zhí)行過(guò)程: 對(duì)于下表: 1浙踢、Index Nested-Loop Join: <1>、語(yǔ)句: ??對(duì)于...
    墨殤染淚閱讀 469評(píng)論 0 1
  • JOIN查詢?cè)砣绻袃蓮垟?shù)據(jù)結(jié)構(gòu)一樣的表(id-主鍵) ,(a有索引) ,(b無(wú)索引)灿渴。其中表t1(100條數(shù)據(jù)...
    小亮__閱讀 782評(píng)論 0 3
  • 夜鶯2517閱讀 127,720評(píng)論 1 9