实为吾之愚见,望诸君酌之!闻过则喜,与君共勉
测试数据
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `from_date` (`from_date`),
CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select count(1) from departments;
+----------+
| count(1) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from dept_emp;
+----------+
| count(1) |
+----------+
| 331603 |
+----------+
1 row in set (0.08 sec)
其中dept_emp有331603行记录，departments有9行数据
事例查询
select e.to_date,d.dept_name from dept_emp e,departments d where e.dept_no=d.dept_no;
这是一个两表join的query，对应条件是where e.dept_no=d.dept_no，主要找出表dept_emp和departments中，满足dept_no相等的记录，然后展示出e.to_date,d.dept_name列其中dept_emp有331603行记录，departments有9行数据
执行计划对比
关闭block_nested_loop
打开block_nested_loop
打开batched_key_access
Nested-Loop事例
Nested-Loop Join
关闭设置optimizer_switch的block_nested_loop为off，然后查看查询的执行计划
从上图可知，执行计划对departments是全表扫描（9行数据），对dept_emp也是全表扫描（331570行数据），当使用Nested-Loop Join算法的时候，先逐行的读取departments表（此处是全表扫描，仅限于该sql），针对departments的每一行数据，都对表dept_emp的每一行记录进行匹配（此处是全表扫描，仅限于该sql）满足条件的行（where e.dept_no=d.dept_no），wiki的伪代码如下：
For each tuple r in R do
For each tuple s in S do
If r and s satisfy the join condition
Then output the tuple <r,s>
过程概括如下图：
上图所示，departments的row1要和dept_emp每一行做条件匹配，查找符合条件的行，反复循环，直至departments的记录扫描完成（最后一条记录rown与dept_emp的每一行都进行了条件匹配）
Block_nested_loop
先打开设置optimizer_switch的block_nested_loop为on，然后查看查询的执行计划
从上图可知，执行计划对departments是全表扫描（9行数据），对dept_emp也是全表扫描（331570行数据），但是extra列多了一部分” Using join buffer (Block Nested Loop)”，当使用Block Nested-Loop Join算法的时候，先逐行的读取departments表（此处是全表扫描，仅限于该sql），然后把读取的数据，存储到join buffer里（如果join buffer足够大，就可以一次全部存储departments所需要的join对象了，如果join buffer太小，一次只可以缓存departments的一部分join对象的话，就需要分多次进行缓存departments的join对象），针对join buffer中缓存的数据（注意之前的一次缓存以及多次缓存），批量（不需要与Nested-Loop Join一样，一条条的比较了，可以多条比较了）的对表dept_emp的每一行记录进行匹配（此处是全表扫描，仅限于该sql）满足条件的行（where e.dept_no=d.dept_no），伪代码可以写成(参考的wiki)：
For each tuple r in R do
store used columns from R in join buffer
For each tuple s in S do
If r and s satisfy the join condition
Then output the tuple <r,s>
过程概括如下图：
当为dept_emp表的列dept_no添加一个索引的时候（二级索引，已经有主键索引），再观察执行计划：
Join type从all变成了ref,可以理解为对dept_emp执行join的时候，使用索引进行匹配（对应之前的全表扫描），这里预估的行数是20723rows，比之前的331570rows少了很多（全表扫描），执行计划从全表扫描和ref（join type）中，选择了 ref，对比之前的block_nested_loop，过程变化如下：
由于dept_emp下的dept_no是二级索引，查询中又查询了e.to_date(单单从二级索引里获取不到数据)，于是需要通过索引，查询表里面对应的e.to_date的值，这是如上图可知，访问时随机的（图例的表现方式是row1对应pk2，row2对应pk1等等），可能会产生随机io，如果不查询e.to_date，则不需要再去表里查询了，同时Extra会显示Using index，如下：
Batched_key_access
在Block_nested_loop最后部分，使用二级索引查询的时候，出现了一个现象：当获取的数据二级索引无法满足时，需要去查询原始的数据表来确定数据，查询数据是通过主键去查的，会出现不是按照主键顺序查的情况，如果有办法把这些无序的主键查询转换成有序的去查询表的数据（聚簇索引），会节省很多的时间，mysql提供了Batched_key_access算法来实现这个需求，复现如下：
对比之前的Block_nested_loop（有索引和没有索引两部分），上面加索引后，开启了mrr，batched_key_access，同时关闭了mrr_cost_based，这个时候Extra列出现了Using join buffer (Batched Key Access)，使用Batched_key_access的过程如下：
上图看，join buffer会缓存departments相关的join列，Batched_key_access算法会使用dept_no二级索引去查找，由于是无序的，查找前把这些key（可以大概理解为主键信息+join buffer里行的标识信息）的信息反馈给mrr 去，mrr会按照主键（没有主键使用row id）排序，然后顺序的去dept_emp里去查找信息，并发信息再次反馈给Batched_key_access算法去和join buffer里的row进行比较