5.6新特性之NL,BNL,MRR和BKA

NLJ(nested-loop join):
从第一个表每次读一行数据，传递到一个嵌套循环（处理join中的下一个表）。
这个处理重复次数跟join中涉及的表相同?
例子：
Table   Join Type
t1      range
t2      ref
t3      ALL
逻辑处理：
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
      send to client
    }
  }
}

BNL（Block Nested-Loop Join）：
通过缓存外层循环读的行，来降低内层表的读取次数。比如： 10行数据读入到buffer中，
然后buffer被传递到内层循环，内层表读出的每一行都要跟这个缓存的10行依次做对比，
这样就降低了内层表数据的读取次数。
使用条件：
1] join_buffer_size决定了每一个join buffer的大小
2] 只有当join type是 all or index（没有合适的索引，使用全索引或者全表扫描的场景）,
   range的时候才会使用。5.6中，外连接也可以用buffer了。
3] 每一个需要buffer的join都会申请一个独立的buffer，也就是说一个查询可能使用多个join buffer。
4] 第一个非常量表是不会使用join buffer的。
5] join buffer在执行join之前申请，在查询完成后释放。
6] join buffer只保存跟join有关的列，而不是整行

explain 列显示：Using join buffer (Block Nested Loop)

逻辑处理：
for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
      }
      empty buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions,
      send to client
    }
  }
}

MRR(Multi-Range Read):
当一个表很大，不能存储到存储引擎的缓存的时候，使用二级索引做范围扫描会引起大量磁盘随机读。
MRR的存在就是为了优化这些随机读。mysql开始只扫描跟行相关的索引和收集key，然后把这些key排序，
最后根据排好序的primary key来从基础表获取数据。  MRR的目的是，降低随机的磁盘IO，替换成相对更
有顺序的IO。
MRR的好处：
1、随机IO转换成顺序IO。
2、批量处理请求

优化场景：
A： MRR可以用来做innodb，myiasm的索引范围扫描和等值join操作。
1、索引元组累积到一个buffer
2、buffer中的元组根据rowid排序
3、根据排序好的索引元组顺序去获取数据行
4、当不需要回表访问的时候，MRR就失去意义了（比如覆盖索引）

当使用MRR的时候 explain出现：Using MRR标志

存储引擎使用read_rnd_buffer_size 的值来确定MRR时的buffer大小。

 

BKA(Batched Key Access):
当使用索引访问第二个join对象的时候，跟BNL类似，BKA使用一个join buffer
来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引
查找。key是通过MRR接口提交给引擎的，这样，MRR使得查询更有效率。
BKA使用join buffer size来确定buffer的大小，buffer越大，访问右侧表就越
顺序。
使用BAK的条件：
SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20625855/viewspace-1456701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20625855/viewspace-1456701/