這兩個(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 中滿(mǎn)足條件的行，跟 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ì)比，滿(mǎn)足 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)行查詢(xú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，定位到滿(mǎn)足條件的記錄，將 id 值放入 read_rnd_buffer 中 ;
b.將 read_rnd_buffer 中的 id 進(jìn)行遞增排序；
c.排序后的 id 數(shù)組，依次到主鍵 id 索引中查記錄，并作為結(jié)果返回
