MySQL表關聯join方式

概念引入

• MRR(Multi-Range Read)
  處理思路：空間換時間，化隨機讀為順序讀，優化通過二級索引檢索回表的效能問題

MySQL中，索引是B+ tree，在葉子節點中，資料是邏輯有序的，如主鍵索引中，是按照主鍵列有序排列，而二級索引中，是按照索引列進行有序排列，而二級索引的葉子節點儲存的是索引列和主鍵值，索引列是有序的，此時主鍵值卻不一定是有序的，往往是無序的，此時通過索引列定位到主鍵值，然後回表往往是離散的讀取資料。MRR中就是先在記憶體中分配read_rnd_buffer空間，先把二級索引的索引列和主鍵值放入到read_rnd_buffer中(放不下就會分多次放入，清空一批之後放入下一批)，然後按照主鍵值排序，這個時候再去回表，IO相對會有序。

注意：MRR需要足夠的資料支撐才行，如果只是少量資料回表，則完全沒必要使用MRR

• Simple Nested-loop Join：只是理論上的一種關聯方式,關聯的表均沒有索引，類似於笛卡兒積，MySQL不會採取這個關聯方式
• Block Nested-loop Join:將驅動表存放到join_buffer中，然後拿著整個join_buffer的資料去與被驅動表進行比較。

BNLJ是對SNLJ的一種優化，BNLJ比較的過程是在記憶體中執行，匹配速度會相對快點，另外個人理解，A和B兩人口袋裡各有一些撲克牌（N個和M個），兩個人撲克牌做匹對。SNLJ就是A從口袋裡一個一個地取出撲克到B的口袋中去匹對，而BNLJ可以理解為取出一把撲克去B的口袋中匹配，可以充分利用CPU的使用情況。但是總體的匹對量是一致均為N*M。

• Index Nested-loop Join:驅動表取出一條資料，然後通過被驅動表的索引進行資料匹配，可以理解為是在SNLJ的基礎上做了一層索引的優化。
• Batched key access:將驅動表放入join_buffer內，並按照索引列進行排序(呼叫了MRR的介面)，參考MRR的思路，隨機IO轉順序IO

BKA是對NLJ的一個優化，通過join_buffer和MRR進行優化

這四者的關係大致如下：

如何開啟MRR和BKA

example：MRR

###建立表T
create table t1(id int primary key, a int,create_time timestamp, index(a));
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=10000)do
    insert into t1 values(i, 100001-i, now());
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();


mysql> explain select * from t1 where a > 100 and a < 500;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+



mysql> set optimizer_switch="mrr_cost_based=off";
Query OK, 0 rows affected (0.00 sec)
mysql> explain select * from t1 where a > 100 and a < 500;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |    1 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

example：BKA

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';