Left Join源码分析与实验测试

目的

根据对查询优化器的分析，发现LEFT JOIN查询与Where条件有一定的关系。因此单独对LEFT JOIN的处理进行进一步详细的分析和测试。

测试方案

1、数据表定义    

数据表包括学生表（student）、课程表（course）、选课表（std_cur），具体数据表定义如下。

student表定义如下所示：

 

CREATE TABLE `student` (

  `std_id` int(11),

  `std_name` varchar(20),

  PRIMARY KEY (`std_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

       course表定义如下所示：

 

CREATE TABLE `course` (

  `cur_id` int(11),

  `cur_name` varchar(20),

  PRIMARY KEY (`cur_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

       std_cur表定义如下所示：

 

CREATE TABLE `std_cur` (

  `std_id` int(11),

  `cur_id` int(11),

  `score` tinyint(4),

  PRIMARY KEY (`std_id`,`cur_id`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

2、测试数据

       添加测试数据如下所示：

 

INSERT INTO `student` VALUES (2012072301, 'aaa'), (2012072302, 'bbb'), (2012072303, 'ccc');

INSERT INTO `course` VALUES (101, 'C'), (102, 'C++');

INSERT INTO `std_cur` VALUES (2012072301, 101, 55), (2012072301, 102, 51), (2012072302, 101, 80), (2012072302, 102, 90);

 

 

3、查询SQL语句

       测试SQL语句设计如下所示：

 

1）SELECT student.std_id, student.std_name, course.cur_name, std_cur.score FROM student LEFT JOIN (std_cur JOIN course ON std_cur.cur_id = course.cur_id) ON student.std_id = std_cur.std_id WHERE course.cur_id=101;

2）SELECT student.std_id, student.std_name, course.cur_name, std_cur.score FROM student LEFT JOIN (std_cur JOIN course ON std_cur.cur_id = course.cur_id AND course.cur_id=101) ON student.std_id = std_cur.std_id;

 

测试

1）LEFT JOIN中where条件

       LEFT JOIN查询中where条件过滤的查询结果如下所示：

 

std_id	std_name	cur_name	score
2012072301	aaa	C	55
2012072302	bbb	C	80

 

对应的查询计划如下所示：

 

id	select_type	table	type	possible_keys	key	key_len	ref	row	Extra
1	SIMPLE	course	const	PRIMARY	PRIMARY	4	const	1
1	SIMPLE	student	ALL	PRIMARY	NULL	NULL	NULL	3
1	SIMPLE	std-cur	ALL	PRIMARY	NULL	NULL	NULL	4	Using where; Using join buffer

 

       从查询结果来看，LEFT JOIN左部的表student并没有查询所有的记录，而仅仅查找到匹配条件的语句。实际这是由于where条件对查询结果输出进行过滤的原因。

2）LEFT JOIN中ON条件

       LEFT JOIN查询中ON条件的查询结果如下所示：

 

std_id	std_name	cur_name	score
2012072301	aaa	C	55
2012072302	bbb	C	80
2012072303	ccc	NULL	NULL

 

对应的查询计划如下所示：

 

id	select_type	table	type	possible_keys	key	key_len	ref	row	Extra
1	SIMPLE	student	ALL	NULL	NULL	NULL	NULL	2
1	SIMPLE	std-cur	ref	PRIMARY	PRIMARY	4	test.student.std_id	1
1	SIMPLE	course	const	PRIMARY	PRIMARY	4	const	1

 

       从查询结果和查询计划可以看出，LEFT JOIN左部表中的所有记录全部查询出来，右部表中没有匹配的结果用NULL填充。从查询计划来看，与where条件的处理方式也不同。

源码分析

       为了更进一步对该问题进行详细的了解，对MySQL的源码（MySQL官方版本5.5.20）进行跟踪和分析。具体线索有以下几点。

1、注释

       在simplify_joins()函数（sql\sql_select.cc:8940）前面的注释中，对什么情况下将OUTER JOIN转化为INNER JOIN来处理。具体如下：

 

The function also calculates some attributes for nested joins:

    - used_tables   

    - not_null_tables

    - dep_tables.

    - on_expr_dep_tables

The first two attributes are used to test whether an outer join can be substituted for an inner join.

 

       以上内容说明了，将OUTER JOIN转化为INNER JOIN的条件是used_tables和not_null_tables两个参数的值。

2、函数实现

在函数的具体实现里，对该过程的处理也是这样实现的。在MySQL源代码simplify_joins()函数中（sql\sql_select.cc:9007）具体实现如下：

 

if (!table->outer_join || (used_tables & not_null_tables))

 

       从以上判断来看，如果当前的数据表为外连接，第一个条件为假，那么就根据used_tables和not_null_tables两个值按位与的结果来决定，是否进行该处理过程。而used_tables的值为查询所使用的表，该条件可以忽略。因此，问题定位到not_null_tables的值。

       从代码来看，not_null_tables的值来源于两个值，具体如下：

       1）NESTED_JOIN

       如果当前查询的表为嵌套JOIN查询，那么not_null_tables的值依赖于嵌套JOIN查询中该变量的值。在MySQL源码中（sql\sql_select.cc:8986）具体实现如下：

 

nested_join->used_tables= (table_map) 0;

      nested_join->not_null_tables=(table_map) 0;

      conds= simplify_joins(join, &nested_join->join_list, conds, top);

      used_tables= nested_join->used_tables;

      not_null_tables= nested_join->not_null_tables;

      

       但是，在赋值之前，递归调用simplify_joins()函数。因此，可以确定该值是从另外一条路径获取的。

       2）非NESTED_JOIN

       当前查询表不是嵌套JOIN查询，那么获取该值的逻辑在MySQL源码（sql\sql_select.cc:8986）中的具体实现如下所示：

 

  not_null_tables= conds->not_null_tables();

 

       跟踪源码，not_null_tables()函数在MySQL源码（sql\sql_item.h:872）中的具体描述和实现如下所示：

 

/*

    Return table map of tables that can't be NULL tables (tables that are

    used in a context where if they would contain a NULL row generated

    by a LEFT or RIGHT join, the item would not be true).

    This expression is used on WHERE item to determinate if a LEFT JOIN can be

    converted to a normal join.

    Generally this function should return used_tables() if the function

    would return null if any of the arguments are null

    As this is only used in the beginning of optimization, the value don't

    have to be updated in update_used_tables()

  */

  virtual table_map not_null_tables() const { return used_tables(); }

 

       从注释中可以看到，如果查询表使用了where条件，那么数据表就是不能为NULL的表。因此，OUTER JOIN查询就可以转化为INNER JOIN查询处理。

       通过以上分析可以知道，OUTER JOIN查询转化为INNER JOIN查询与where子句有关，当有where子句时，该转化过程会发生。

结论

       通过以上测试和源码分析，可以知道OUTER JOIN在查询过滤条件在where子句中时，会转化为INNER JOIN查询，并且也不会得到预期的结果。如果想通过LEFT（RIGHT） JOIN获取左（右）部表中的全部记录，那么可以将过滤条件放到ON条件中。

参考

1、MySQL源码：mysql-5.5.20