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，MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。在驱动表上做多条数据的cache，是为了产生较多的匹配数据，从而在被驱动表中，可以使用MRR，随机读可以转为顺序读，数据足够多的情况下，MRR才有优势。

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';