实为吾之愚见,望诸君酌之!闻过则喜,与君共勉
测试数据
PRIMARY KEY (`emp_no`,`dept_no`),
KEY `from_date` (`from_date`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1
`dept_name` varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select count(1) from departments;
mysql> select count(1) from dept_emp;
其中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;
执行计划对比
关闭block_nested_loop
打开block_nested_loop
打开batched_key_access
Nested-Loop事例
Nested-Loop Join
关闭设置optimizer_switch的block_nested_loop为off,然后查看查询的执行计划
If r and s satisfy the join condition
Block_nested_loop
先打开设置optimizer_switch的block_nested_loop为on,然后查看查询的执行计划
store used columns from R in join buffer
If r and s satisfy the join condition
当为dept_emp表的列dept_no添加一个索引的时候(二级索引,已经有主键索引),再观察执行计划: