【1】
CREATE TABLE `t_2` (
`u_name` char(10) DEFAULT NULL,
`year` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `t_3` (
`u_name` char(10) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
KEY `idx_year_u_name` (`year`,`u_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
explain select * from t_3 left join t_2 on(t_3.u_name=t_2.u_name) and (t_3.year=1992);
t_3 驱动表
t_2 被驱动表
如上图的执行计划, SELECT * 的数据可以从idx_year_u_name索引直接全部获取, 因此t_3表可通过扫描索引(type=index),而非全表扫描.
【2】
CREATE TABLE `t_4` (
`u_name` char(10) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
`address` varchar(10) DEFAULT NULL,
KEY `idx_year_u_name` (`year`,`u_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
explain select * from t_4 left join t_2 on(t_4.u_name=t_2.u_name) and (t_4.year=1992);
t_4 驱动表
t_2 被驱动表
如上图的执行计划, SELECT * 的数据(u_name,year,address)不可以从idx_year_u_name(year,u_name)索引直接全部获取, 因此t_4表只能通过全表扫描(type=ALL).
【3】
explain select * from t_4 left join t_2 on(t_4.u_name=t_2.u_name) and (t_4.year=1992);
explain select * from t_4 left join t_2 on(t_4.u_name=t_2.u_name) where (t_4.year=1992);
如果采用BNL算法的话
1.将驱动表t_4中满足WHERE子句的数据放入 join buffer.
2.顺序扫描被驱动表t_2每一行数据, 判断与 join buffer 中的数据是否满足 on ... and ... 条件, 满足条件则放入结果集.
3.被驱动表t_2扫描完成之后, 对于驱动表t_4中未匹配到的数据行, 把剩余字段补NULL, 并放入结果集.
【4】
CREATE TABLE `t_1` (
`u_name` char(10) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
KEY `idx_u_name` (`u_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `t_2` (
`u_name` char(10) DEFAULT NULL,
`year` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t_1 left join t_2 on(t_1.u_name=t_2.u_name) where (t_1.year=t_2.year);
优化器将其改写成了 JOIN , 即 select * from t_1 join t_2 where (t_1.u_name=t_2.u_name) and (t_1.year=t_2.year);
select * from t_1 left join t_2 on(t_1.u_name=t_2.u_name) where (t_2.year=1992);
优化器将其改写成了 JOIN , 即 select * from t_1 join t_2 where (t_1.u_name=t_2.u_name) and (t_2.year=1992);
explain extended + 原SQL
show warnings;
执行以上2个命令之后, 就可以查看到实际被优化器执行的SQL
一旦WHERE条件中包含LEFT JOIN右侧表的字段, LEFT JOIN的语义与JOIN一样, 则优化器将其改成JOIN
