MySQL查询优化器--逻辑查询优化技术（五）--外连接的消除（一）

14.1.5 外连接消除

MySQL支持外连接转换为内连接，转换的条件是符合“空值拒绝”。

 

示例：假设表t_1和表t_2结构与数据参见表14-4：

 

表14-4 示例数据表

表t_1	表t_2
CREATE TABLE t_1 (t_1_id INT UNIQUE, t_1_col_1 INT, t_1_col_2 VARCHAR(10));	CREATE TABLE t_2 (t_2_id INT UNIQUE, t_2_col_1 INT, t_2_col_2 VARCHAR(10));
t_1_id | t_1_col_1 | t_1_col_2 --------+-----------+-----------       1 |        11 | t_1_1       2 |        12 |       3 |           | t_1_3       4 |        14 | t_1_4       5 |        15 |       7 |           | (6 rows)	t_2_id | t_2_col_1 | t_2_col_2 --------+-----------+-----------       1 |        11 | t_2_1       2 |           | t_2_2       3 |        13 |       4 |        14 | t_2_4       6 |        16 | t_2_6       7 |           | (6 rows)

 

第一组。表中的ID列，唯一键，作为连接条件(对比 where 和 on 是否有不同)

语句一：

SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_id = t_2_id;

语句二：

SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id;

语句三：

SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id = t_2_id;

 

表14-5 示例结果表

语句	查询结果
语句一 语句三	+--------+-----------+-----------+--------+-----------+-----------+ | t_1_id | t_1_col_1 | t_1_col_2 | t_2_id | t_2_col_1 | t_2_col_2 | +--------+-----------+-----------+--------+-----------+-----------+ |      1 |        11 | t_1_1     |      1 |        11 | t_2_1     | |      2 |        12 | NULL      |      2 |      NULL | t_2_2     | |      3 |      NULL | t_1_3     |      3 |        13 | NULL      | |      4 |        14 | t_1_4     |      4 |        14 | t_2_4     | |      7 |      NULL | NULL      |      7 |      NULL | NULL      | +--------+-----------+-----------+--------+-----------+-----------+ 5 rows in set (0.00 sec)
语句二	+--------+-----------+-----------+--------+-----------+-----------+ | t_1_id | t_1_col_1 | t_1_col_2 | t_2_id | t_2_col_1 | t_2_col_2 | +--------+-----------+-----------+--------+-----------+-----------+ |      1 |        11 | t_1_1     |      1 |        11 | t_2_1     | |      2 |        12 | NULL      |      2 |      NULL | t_2_2     | |      3 |      NULL | t_1_3     |      3 |        13 | NULL      | |      4 |        14 | t_1_4     |      4 |        14 | t_2_4     | |      5 |        15 | NULL      |   NULL |      NULL | NULL      | |      7 |      NULL | NULL      |      7 |      NULL | NULL      | +--------+-----------+-----------+--------+-----------+-----------+ 6 rows in set (0.00 sec)
对比一	语句一、三使用了“WHERE t_1_id = t_2_id”条件，这个条件中，有如下四种情况： 1)   t_1_id = NULL，t_2_id = NULL：则NULL != NULL值为UNKOWEN 2)   t_1_id = NULL，t_2_id != NULL：则NULL = 非NULL值为UNKOWEN 3)   t_1_id != NULL，t_2_id = NULL： 同上 4)   t_1_id != NULL，t_2_id !=NULL：“t_1_id = t_2_id”值要么是TRUE要么是FALSE 所以，四种情况都是满足“空值拒绝”的，所以，语句一、三，可以把外连接转换为內连接（语句一特殊处在于，t_1_id是UNIQUE键，实际的值不会为NULL）   语句二，没有WHERE条件，只有JOIN/ON连接条件，这样，t_1_id=5时，右表t_2中没有与其匹配项（右表只能以空值满足左表的匹配要求），所以会比內连接多出连接的行，所以，不可以把外连接转换为內连接，即右表没有满足“空值拒绝”   以上的印证，可以从下表看出，下表给出对应的查询执行计划，语句一的查询执行计划是语句四，语句二的查询执行计划是语句无，语句三的查询执行计划是语句六。语句一、三等价所以其查询执行计划四、六结果相同

 

观察查询执行计划，看哪个真正执行的是外连接，哪个被从外连接优化为了内连接

语句四：

EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_id = t_2_id;

语句五：

EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id;

语句六：

EXPLAIN EXTENDED SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_id = t_2_id WHERE t_1_id = t_2_id;

 

表14-6 示例结果表2

语句	查询语句优化后的结果
语句四、语句六	/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` join `test`.`t_2` where (`test`.`t_2`.`t_2_id` = `test`.`t_1`.`t_1_id`)
语句五	/* select#1 */ select `test`.`t_1`.`t_1_id` AS `t_1_id`, `test`.`t_1`.`t_1_col_1` AS `t_1_col_1`, `test`.`t_1`.`t_1_col_2` AS `t_1_col_2`, `test`.`t_2`.`t_2_id` AS `t_2_id`, `test`.`t_2`.`t_2_col_1` AS `t_2_col_1`, `test`.`t_2`.`t_2_col_2` AS `t_2_col_2` from `test`.`t_1` left join `test`.`t_2` on((`test`.`t_1`.`t_1_id` = `test`.`t_2`.`t_2_id`)) where 1
对比二	语句五，多了“left”，表明最后得到的查询执行计划还是按照左连接的语义执行，没有被优化为内连接；而语句四、六，执行两表连接时，只是“join”，已经没有外连接存在，表明最后得到的查询执行计划是按照内连接的语义执行的，外连接被优化为了內连接

 

第二组，普通列（t_1_col_1，列上有值或可为NULL）作为连接条件

语句七：

SELECT * FROM t_1 LEFT JOIN t_2 ON true WHERE t_1_col_1 = t_2_col_1;

语句八：

SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1;

语句九：

SELECT * FROM t_1 LEFT JOIN t_2 ON t_1_col_1 = t_2_col_1 WHERE t_1_col_1 = t_2_col_1;

 

表14-7 示例结果表3

语句	查询结果
语句七、语句九	+--------+-----------+-----------+--------+-----------+-----------+ | t_1_id | t_1_col_1 | t_1_col_2 | t_2_id | t_2_col_1 | t_2_col_2 | +--------+-----------+-----------+--------+-----------+-----------+ |      1 |        11 | t_1_1     |      1 |        11 | t_2_1     | |      4 |        14 | t_1_4     |      4 |        14 | t_2_4     | +--------+-----------+-----------+--------+-----------+-----------+ 2 rows in set (0.00 sec)
语句八	+--------+-----------+-----------+--------+-----------+-----------+ | t_1_id | t_1_col_1 | t_1_col_2 | t_2_id | t_2_col_1 | t_2_col_2 | +--------+-----------+-----------+--------+-----------+-----------+ |      1 |        11 | t_1_1     |      1 |        11 | t_2_1     | |      4 |        14 | t_1_4     |      4 |        14 | t_2_4     | |      2 |        12 | NULL      |   NULL |      NULL | NULL      | |      3 |      NULL | t_1_3     |   NULL |      NULL | NULL      | |      5 |        15 | NULL      |   NULL |      NULL | NULL      | |      7 |      NULL | NULL      |   NULL |      NULL | NULL      | +--------+-----------+-----------+--------+-----------+-----------+ 6 rows in set (0.00 sec)
对比三	语句七、九使用了“WHERE t_1_col_1 = t_2_col_1”条件，这个条件中，有四种情况，等同“对比一”中的分析，所以，满足“空值拒绝”。 语句一的特点是使用了UNIQUE键作为连接条件，而语句七使用了普通列作为连接条件，且列上都有空值存在（可见列上的NULL值，不影响“空值拒绝”判断）