建立表
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()
begin
declare i int;
set i=1;
while(i<=1000)do
insert 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)
算法介紹
- Index Nested-Loop Join（NLJ）
select * from t1 straight_join t2 on (t1.a=t2.a);
執行過程
使用
straight_join将t1表固定為驅動表，避免MySql優化器改變驅動表。上述sql執行過程如下：
- 從表t1中讀入一行資料 R；
- 從資料行R中，取出a字段到表t2裡去查找；
- 取出表t2中滿足條件的行，跟R組成一行，作為結果集的一部分；
- 重複執行步驟1到3，直到表t1的末尾循環結束。
查詢t2表時使用到了t2的索引，稱為索引嵌套循環join。
- Simple Nest-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.b);
上述sql中t2表沒有使用到索引，每次到t2中比對時需要對t2做一次全表掃描。由于上述算法太笨重，Mysql對該算法進行優化為BlockNest-Loop Join。
- Block Nest-Loop Join
BNL算法流程如下：
- 将t1表中的資料全部取出放入線程記憶體join_buffer中；
- 掃描t2表，将t2中的每一條資料和join_buffer中的資料進行對比，滿足條件的資料作為結果集的一部分傳回；
BNL
Block Nest-Loop Join算法時間複雜度和simple nest-loop join相同，但是記憶體操作，速度會比simple nest-loop join 快。
join_buffer的大小是由參數join_buffer_size設定的，預設值是256k。如如果果放放不不下下表t1的所有資料的話，政策很簡單：分段放，取t2表中的資料對比後清空。分段即Block
關于小表
- demo1
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;
join_buffer隻需要放入t2的前50行，顯然是更好的。是以這裡“t2的前50行”是那個相對小的表，也就是“小表”。
- demo2
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;
雖然t1和t2查詢數量都是小于100條，但是對于查詢1，join_buffer中隻需要放入b值；對于查詢2需要放入id,a和b字段。
驅動表應該是按照各自條件過濾，計算參與join各個字段的總資料量，資料量小的表就是小表。
join優化
- Multi-Range Read優化
了解MRR之前先熟悉一下回表的概念：
InnoDB在普通索引查到主鍵id的值後，再根據主鍵id的值到主鍵索引上去查整行資料的過程。
回表操作是一條一條的到主鍵索引上查詢資料，并不是批量查詢。這就造成如果普通索引（非主鍵索引也被稱為二級索引secondary index）的值是遞增的話，查詢主鍵索引的id值就變成随機的。如果能将主鍵索引的值調整為遞增順序的話就提升效率。
- MRR設計思路
- 根據索引a，定位到滿足條件的記錄，将id值放入 read_rnd_buffer 中;
- 将 read_rnd_buffer 中的id進行遞增排序；
- 排序後的id數組，依次到主鍵id索引中查記錄，并作為結果傳回。
如果你想要穩定地使用MRR優化的話，需要設定 setoptimizer_switch="mrr_cost_based=off"
MRR執行過程
- Batched Key Access
MySQL在5.6版本後開始引入Batched Key Acess(BKA)算法，該算法是對NLJ的優化。
NLJ算法執行的邏輯是：從驅動表t1，一行行地取出a的值，再到被驅動表t2去做join。對于表t2來說，每次都是比對一個值。這時，MRR的優勢就用不上了。
優化思路：先把表t1的資料取出來一部分，先放到一個臨時記憶體join_buffer中，然後再一次性的傳給t2。
開啟BKA優化算法：
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';