【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 驅(qū)動表
t_2 被驅(qū)動表
如上圖的執(zhí)行計劃, SELECT * 的數(shù)據(jù)可以從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 驅(qū)動表
t_2 被驅(qū)動表
如上圖的執(zhí)行計劃, SELECT * 的數(shù)據(jù)(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.將驅(qū)動表t_4中滿足WHERE子句的數(shù)據(jù)放入 join buffer.
2.順序掃描被驅(qū)動表t_2每一行數(shù)據(jù), 判斷與 join buffer 中的數(shù)據(jù)是否滿足 on ... and ... 條件, 滿足條件則放入結(jié)果集.
3.被驅(qū)動表t_2掃描完成之后, 對于驅(qū)動表t_4中未匹配到的數(shù)據(jù)行, 把剩余字段補NULL, 并放入結(jié)果集.
【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);
優(yōu)化器將其改寫成了 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);
優(yōu)化器將其改寫成了 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;
執(zhí)行以上2個命令之后, 就可以查看到實際被優(yōu)化器執(zhí)行的SQL
一旦WHERE條件中包含LEFT JOIN右側(cè)表的字段, LEFT JOIN的語義與JOIN一樣, 則優(yōu)化器將其改成JOIN
