Left Join源码分析与实验测试
来源:互联网 发布:淘宝回收充值卡安全吗 编辑:程序博客网 时间:2024/05/21 03:27
目的
根据对查询优化器的分析,发现LEFT JOIN查询与字段的定义是否为NULL有一定的关系。鉴于《MySQL查询优化器实验分析》中对LEFT JOIN的分析和测试不足,因此单独对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 ¬_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
- Left Join源码分析与实验测试
- JOIN与LEFT JOIN
- left join 分析
- Left join分析
- 12.4-----left join 分析
- MYSQl left join 分析
- mysql left join分析
- inner join 与 left join
- Left join 与 right join
- ORACLE- join,inner join 与 left join, left outer join
- left join 与where
- Merge 与Left join
- join,left join and where的测试
- sql各种连接笛卡尔积,inner join,left join,right join,full join等测试与区别
- HIVE 中内连接(JOIN ON)与LEFT SEMI JOIN查询结果不一致的分析
- HIVE 中内连接(JOIN ON)与LEFT SEMI JOIN查询结果不一致的分析
- HIVE 中内连接(JOIN ON) 与 LEFT SEMI JOIN 分析
- HIVE 中内连接(JOIN ON) 与 LEFT SEMI JOIN 分析
- JAVA图形编程入门(连载2)
- 容斥原理详解 以及代码的剖析 结合实例hdu4135
- 【win32 API】仿微软记事本V2.0
- 什么是涓流充电
- 今天 使用jquery
- Left Join源码分析与实验测试
- 数据结构-05 数组 -插入排序
- 基金申请要点
- WebServices Axis 简介
- java.io.FileNotFoundException
- 下代MapReduce预览 节点数翻倍最大达10000
- Struts首例:界面
- Linux进程间通信深刻理解
- 没有任何一块领土是靠谈判获得的