实为吾之愚见, 望诸君酌之! 闻过则喜, 与君共勉
测试数据
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),伪代码可以写成:
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 进行比较
来源: https://yq.aliyun.com/articles/397850