Mysql连接用法及效率分析.docx
《Mysql连接用法及效率分析.docx》由会员分享，可在线阅读，更多相关《Mysql连接用法及效率分析.docx（14页珍藏版）》请在冰点文库上搜索。
Mysql连接用法及效率分析
MySQL左连接、右连接和内连接详解
以MySql为例。
在MySQL数据库中建立两X数据表，并分别插入一些数据。
示例脚本如下：
1.drop table table1;
2.CREATE TABLE `andrew`.`table1`
3.（
4.`name` VARCHAR（32） NOT NULL,
5.`city` VARCHAR（32） NOT NULL
6.）
7.ENGINE = MyISAM;
8.insert into TABLE1（name, city） values （'Person A', 'BJ'）;
9.insert into TABLE1（name, city） values （'Person B', 'BJ'）;
10.insert into TABLE1（name, city） values （'Person C', 'SH'）;
11.insert into TABLE1（name, city） values （'Person D', 'SZ'）;
12.mit;
13.drop table table2;
14.CREATE TABLE `andrew`.`table2`
15.（
16.`name` VARCHAR（32） NOT NULL,
17.`city` VARCHAR（32） NOT NULL
18.）
19.ENGINE = MyISAM;
20.insert into TABLE2（name, city） values （'Person W', 'BJ'）;
21.insert into TABLE2（name, city） values （'Person X', 'SH'）;
22.insert into TABLE2（name, city） values （'Person Y', 'SH'）;
23.insert into TABLE2（name, city） values （'Person Z', 'NJ'）;
24.mit;
1.MySQL外连接–左连接结果
table1居左，故谓之左连接。
这种情况下，以table1为主，即table1中的所有记录均会被列出。
有一下三种情况：
a.对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且刚好只有一条，那么就会在
返回的结果中形成一条新的记录。
如上面PersonA和PersonB对应的情况。
b.对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。
如上面的PersonC对应的情况。
c.对于table1中的每一条记录对应的城市如果在table2中不存在，那么就会在返回的结果中形成一条
条新的记录，且该记录的右边全部NULL。
如上面的PersonD对应的情况。
不符合上面三条规则的记录不会被列出。
2.MySQL外连接–右连接结果
table2居右，故谓之右连接。
这种情况下，以table2为主，即table2中的所有记录均会被列出。
有一下三种情况：
a.对于table2中的每一条记录对应的城市如果在table1中也恰好存在而且刚好只有一条，那么就会在
返回的结果中形成一条新的记录。
如上面PersonX和PersonY对应的情况。
b.对于table2中的每一条记录对应的城市如果在table1中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。
如上面的PersonW对应的情况。
c.对于table2中的每一条记录对应的城市如果在table1中不存在，那么就会在返回的结果中形成一条
条新的记录，且该记录的左边全部NULL。
如上面的PersonZ对应的情况。
不符合上面三条规则的记录不会被列出。
3.MySQL内连接
MySQL内连接的数据记录中，不会存在字段为NULL的情况。
可以简单地认为，内的结果就是在左连接或者右连接的结果中剔除存在字段为NULL的记录后所得到的结果。
甚至可以认为，如果两个表中仅分别剩下内连接运算后所得的数据记录，如table1中只有PersonA、PersonB和PersonC，table2中只有PersonW、PersonX和PersonY，那么这两个表的之间的左连接和右连接的返回的结果是一样的。
注意：
select*fromtable1ainnerjointable2bona.city=b.city和select*fromtable1ajointable2bona.city=b.city的效果是一样的，即如果join的左边没有诸如left、right或者inner这样的关键字时，缺省的是内连接。
另外，MySQL不支持fulljoin。
MySQL的联结（Join）语法
1．内联结、外联结、左联结、右联结的含义及区别：
在讲MySQL的Join语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内容有错误或有疑问，可以来信咨询：
陈朋奕chenpengyi#gmail.），国内关于MySQL联结查询的资料十分少，相信大家在看了本文后会对MySQL联结语法有相当清晰的了解，也不会被Oracle的外联结的（“＋”号）弄得糊涂了。
在SQL标准中规划的（Join）联结大致分为下面四种：
1． 内联结：
将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。
2． 外联结：
分为外左联结和外右联结。
左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。
右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：
SelectA.nameB.nameFromALeftJoinBOnA.id=B.id
和SelectA.nameB.nameFromBRightJoinAonB.id=A.id执行后的结果是一样的。
3．全联结：
将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。
4．无联结：
不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。
这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。
外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。
其实大家回忆高等教育出版的《数据库系统概论》书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。
2． MySQL联结（Join）的语法
MySQL支持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有：
table_references:
table_reference[,table_reference]…
table_reference:
table_factor
|join_table
table_factor:
tbl_name[[AS]alias]
[{USE|IGNORE|FORCE}INDEX（key_list）]
|（table_references）
|{OJtable_referenceLEFTOUTERJOINtable_reference
ONconditional_expr}
join_table:
table_reference[INNER|CROSS]JOINtable_factor[join_condition]
|table_referenceSTRAIGHT_JOINtable_factor
|table_referenceSTRAIGHT_JOINtable_factorONcondition
|table_referenceLEFT[OUTER]JOINtable_referencejoin_condition
|table_referenceNATURAL[LEFT[OUTER]]JOINtable_factor
|table_referenceRIGHT[OUTER]JOINtable_referencejoin_condition
|table_referenceNATURAL[RIGHT[OUTER]]JOINtable_factor
join_condition:
ONconditional_expr|USING（column_list）
上面的用法摘自权威资料，不过大家看了是否有点晕呢？
呵呵，应该问题主要还在于table_reference是什么，table_factor又是什么？
这里的table_reference其实就是表的引用的意思，因为在MySQL看来，联结就是一种对表的引用，因此把需要联结的表定义为table_reference，同时在SQLStandard中也是如此看待的。
而table_factor则是MySQL对这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表，如下面例子中的JOIN后面括号：
SELECT*FROMt1LEFTJOIN（t2,t3,t4）ON（t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c）
这个语句的执行结果和下面语句其实是一样的：
SELECT*FROMt1LEFTJOIN（t2CROSSJOINt3CROSSJOINt4）
ON（t2.a=t1.aANDt3.b=t1.bANDt4.c=t1.c）
这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSSJOIN的用法，我要补充的是在MySQL现有版本中CROSSJOIN的作用和INNERJOIN是一样的（虽然在SQLStandard中是不一样的，然而在MySQL中他们的区别仅仅是INNERJOIN需要附加ON参数的语句，而CROSSJOIN不需要）。
既然说到了ON语句，那就解释一下吧，ON语句其实和WHERE语句功能大致相当，只是这里的ON语句是专门针对联结表的，ON语句后面的条件的要求和书写方式和WHERE语句的要求是一样的，大家基本上可以把ON当作WHERE用。
大家也许也看到了OJtable_referenceLEFTOUTERJOINtable_reference这个句子，这不是MySQL的标准写法，只是为了和ODBC的SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。
那下面就具体讲讲简单的JOIN的用法了。
首先我们假设有2个表A和B，他们的表结构和字段分别为：
表A：
ID
Name
1
Tim
2
Jimmy
3
John
4
Tom
表B：
ID
Hobby
1
Football
2
Basketball
2
Tennis
4
Soccer
1． 内联结：
SelectA.NameB.HobbyfromA,BwhereA.id=B.id，这是隐式的内联结，查询的结果是：
Name
Hobby
Tim
Football
Jimmy
Basketball
Jimmy
Tennis
Tom
Soccer
它的作用和SelectA.NamefromAINNERJOINBONA.id=B.id是一样的。
这里的INNERJOIN换成CROSSJOIN也是可以的。
2． 外左联结
SelectA.NamefromALeftJOINBONA.id=B.id，典型的外左联结，这样查询得到的结果将会是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：
Name
Hobby
Tim
Football
Jimmy
Basketball，Tennis
John
Tom
Soccer
所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。
3． 外右联结
如果把上面查询改成外右联结：
SelectA.NamefromARightJOINBONA.id=B.id，则结果将会是：
Name
Hobby
Tim
Football
Jimmy
Basketball
Jimmy
Tennis
Tom
Soccer
这样的结果都是我们可以从外左联结的结果中猜到的了。
说到这里大家是否对联结查询了解多了？
这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？
最后给大家讲讲MySQL联结查询中的某些参数的作用：
1．USING（column_list）：
其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：
aLEFTJOINbUSING（c1,c2,c3），其作用相当于下面语句
aLEFTJOINbONa.c1=b.c1ANDa.c2=b.c2ANDa.c3=b.c3
只是用ON来代替会书写比较麻烦而已。
2．NATURAL[LEFT]JOIN：
这个句子的作用相当于INNERJOIN，或者是在USING子句中包含了联结的表中所有字段的LeftJOIN（左联结）。
3．STRAIGHT_JOIN：
由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。
最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。
譬如你需要进行多表联结，因此你输入了下面的联结查询：
SELECTt1.id,t2.id,t3.id
FROMt1,t2
LEFTJOINt3ON（t3.id=t1.id）
WHEREt1.id=t2.id;
但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：
SELECTt1.id,t2.id,t3.id
FROMt1,（t2LEFTJOINt3ON（t3.id=t1.id））
WHEREt1.id=t2.id;
这并不是我们想要的效果，所以我们需要这样输入：
SELECTt1.id,t2.id,t3.id
FROM（t1,t2）
LEFTJOINt3ON（t3.id=t1.id）
WHEREt1.id=t2.id;
在这里括号是相当重要的，因此以后在写这样的查询的时候我们不要忘记了多写几个括号，至少这样能避免很多错误（因为这样的错误是很难被开发人员发现的）。
如果对上面内容有疑问可以来信查询：
陈朋奕chenpengyi#gmail.，请注明出处及作者。
MySQLleftjoin联合查询的效率分析
database.51cto. 2010-05-2114:
36 佚名 博客园 我要评论（0）
∙摘要：
我们今天主要向大家介绍的是MySQLleftjoin联合查询的效率分析，以及在实际操作中值得我们大家注意的事项的描述。
∙标签：
MySQLleftjoin
∙
以下的文章主要讲述的是MySQLleftjoin联合查询的效率分析，我在一个信誉度很好的找到一个关于MySQLleftjoin联合查询的效率分析的资料，今天拿出来供大家分享，希望会给你带来一些帮助在此方面。
user表:
代码:
id|name
1|libk
2|zyfon
3|daodao
user_action表:
代码:
user_id|action
1|jump
1|kick
1|jump
2|run
4|swim
sql:
代码:
selectid,name,actionfromuserasu
leftjoinuser_actionaonu.id=a.user_id
result:
代码:
id|name|action
1|libk|jump①
1|libk|kick②
1|libk|jump③
2|zyfon|run④
3|daodao|null⑤
分析：
注意到user_action中还有一个user_id=4,action=swim的纪录，但是没有在结果中出现，
而user表中的id=3,name=daodao的用户在user_action中没有相应的纪录，但是却出现在了结果集中
因为现在是MySQLleftjoin，所有的工作以left为准.
结果1，2，3，4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录
结论：
我们可以想象MySQLleftjoin是这样工作的
从左表读出一条，选出所有与on匹配的右表纪录（n条）进行连接，形成n条纪录（包括重复的行，如：
结果1和结果3），
如果右边没有与on条件匹配的表，那连接的字段都是null.
然后继续读下一条。
引申：
我们可以用右表没有on匹配则显示null的规律,来找出所有在左表，不在右表的纪录，注意用来判断的那列必须声明为notnull的。
如：
sql:
代码:
1.select id, name, action from user as u
2.left join user_action a on u.id = a.user_id
3.where a.user_id is NULL
（注意:
1.列值为null应该用isnull而不能用=NULL
2.这里a.user_id列必须声明为NOTNULL的）
代码:
1.result:
2.id | name | action
3.3 | daodao | NULL
Tips:
1.ona.c1=b.c1等同于using（c1）
2.INNERJOIN和,（逗号）在语义上是等同的
3.当MySQL在从一个表中检索信息时，你可以提示它选择了哪一个索引。
如果EXPLAIN显示MySQL使用了可能的索引列表中错误的索引，这个特性将是很有用的。
通过指定USEINDEX（key_list），你可以告诉MySQL使用可能的索引中最合适的一个索引在表中查找记录行。
可选的二选一句法IGNOREINDEX（key_list）可被用于告诉MySQL不使用特定的索引。
4.一些例子：
代码:
1.MySQL> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
2.MySQL> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
3.MySQL> SELECT * FROM table1 LEFT JOIN table2 USING （id）;
4.MySQL> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
5.-> LEFT JOIN table3 ON table2.id=table3.id;
6.MySQL> SELECT * FROM table1 USE INDEX （key1,key2）
7.-> WHERE key1=1 AND key2=2 AND key3=3;
8.MySQL> SELECT * FROM table1 IGNORE INDEX （key3）
9.-> WHERE key1=1 AND key2=2 AND key3=3;
如何优化LEFTJOIN和RIGHTJOIN
在MySQL中，ALEFTJOINBjoin_condition执行过程如下：
根据表A和A依赖的所有表设置表B。
根据MySQLLEFTJOIN条件中使用的所有表（除了B）设置表A。
LEFTJOIN条件用于确定如何从表B搜索行。
（换句话说，不使用WHERE子句中的任何条件）。
可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。
如果出现循环依赖关系，MySQL提示出现一个错误。
进行所有标准WHERE优化。
如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。
如果使用LEFTJOIN找出在某些表中不存在的行，并且进行了下面的测试：
WHERE部分的col_nameISNULL，其中col_name是一个声明为NOTNULL的列，MySQL找到匹配LEFTJOIN条件的一个行后停止（为具体的关键字组合）搜索其它行。
RIGHTJOIN的执行类似LEFTJOIN，只是表的角色反过来。
联接优化器计算表应联接的顺序。
LEFTJOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。
请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFTJOIN强制它在d之前读取：
代码:
1.SELECT *
2.FROM a,b LEFT JOIN c ON （c.key=a.key） LEFT JOIN d ON （d.key=a.key）
3.WHERE b.key=d.key;
在这种情况下修复时用a的相反顺序，b列于FROM子句中：
代码:
1.SELECT *
2.FROM b,a LEFT JOIN c ON （c.key=a.key） LEFT JOIN d ON （d.key=a.key）
3.WHERE b.key=d.key;
MySQL可以进行下面的LEFTJOIN优化：
如果对于产生的NULL行，WHERE条件总为假，LEFTJOIN变为普通联接。
例如，在下面的查询中如果t2.column1为NULL，WHERE子句将为false：
代码:
1.SELECT * FROM t1 LEFT JOIN t2 ON （column1） WHERE t2.column2=5;
因此，可以安全地将查询转换为普通联接：
1.SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
这样可以
升级会员 