這兩個(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ù)搜索引颈。
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é)果集的一部分返回阻逮。
在這個(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ù)舔稀。因此,回表肯定是一行行搜索主鍵索引的
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é)果返回