mysql的join算法_mysql的Join算法-阿里云开发者社区

实为吾之愚见,望诸君酌之!闻过则喜,与君共勉

测试数据

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

过程概括如下图：

上图所示，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

过程概括如下图：

当为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进行比较