Mysql连接用法及效率分析.docx

1、Mysql连接用法及效率分析MySQL左连接、右连接和内连接详解以MySql为例。在MySQL数据库中建立两X数据表，并分别插入一些数据。示例脚本如下：1.droptabletable1; 2.CREATETABLEandrew.table1 3.( 4.nameVARCHAR(32)NOTNULL, 5.cityVARCHAR(32)NOTNULL 6.) 7.ENGINE=MyISAM; 8.insertintoTABLE1(name,city)values(PersonA,BJ); 9.insertintoTABLE1(name,city)values(PersonB,BJ); 10.i

2、nsertintoTABLE1(name,city)values(PersonC,SH); 11.insertintoTABLE1(name,city)values(PersonD,SZ); 12.mit; 13.droptabletable2; 14.CREATETABLEandrew.table2 15.( 16.nameVARCHAR(32)NOTNULL, 17.cityVARCHAR(32)NOTNULL 18.) 19.ENGINE=MyISAM; 20.insertintoTABLE2(name,city)values(PersonW,BJ); 21.insertintoTABL

3、E2(name,city)values(PersonX,SH); 22.insertintoTABLE2(name,city)values(PersonY,SH); 23.insertintoTABLE2(name,city)values(PersonZ,NJ); 24.mit;1. MySQL外连接 左连接结果table1居左，故谓之左连接。这种情况下，以table1为主，即table1中的所有记录均会被列出。有一下三种情况：a. 对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且刚好只有一条，那么就会在返回的结果中形成一条新的记录。如上面Person A和Perso

4、n B对应的情况。b. 对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。如上面的Person C对应的情况。c. 对于table1中的每一条记录对应的城市如果在table2中不存在，那么就会在返回的结果中形成一条条新的记录，且该记录的右边全部NULL。如上面的Person D对应的情况。不符合上面三条规则的记录不会被列出。2. MySQL外连接 右连接结果table2居右，故谓之右连接。这种情况下，以table2为主，即table2中的所有记录均会被列出。有一下三种情况：a. 对于table2中的每一条记录对应的城市如果

5、在table1中也恰好存在而且刚好只有一条，那么就会在返回的结果中形成一条新的记录。如上面Person X和Person Y对应的情况。b. 对于table2中的每一条记录对应的城市如果在table1中也恰好存在而且有N条，那么就会在返回的结果中形成N条新的记录。如上面的Person W对应的情况。c. 对于table2中的每一条记录对应的城市如果在table1中不存在，那么就会在返回的结果中形成一条条新的记录，且该记录的左边全部NULL。如上面的Person Z对应的情况。不符合上面三条规则的记录不会被列出。3. MySQL内连接MySQL内连接的数据记录中，不会存在字段为NULL的情况。可

6、以简单地认为，内的结果就是在左连接或者右连接的结果中剔除存在字段为NULL的记录后所得到的结果。甚至可以认为，如果两个表中仅分别剩下内连接运算后所得的数据记录，如table1中只有Person A、Person B和Person C，table2中只有Person W、Person X和Person Y，那么这两个表的之间的左连接和右连接的返回的结果是一样的。注意：select * from table1 a inner join table2 b on a.city = b.city 和select * from table1 a join table2 b on a.city = b.ci

7、ty 的效果是一样的，即如果join的左边没有诸如left、right或者inner这样的关键字时，缺省的是内连接。另外，MySQL不支持full join。MySQL的联结（Join）语法1内联结、外联结、左联结、右联结的含义及区别：在讲MySQL的Join语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内容有错误或有疑问，可以来信咨询：陈朋奕 chenpengyi#gmail.），国内关于MySQL联结查询的资料十分少，相信大家在看了本文后会对MySQL联结语法有相当清晰的了解，也不会被Oracle的外联结的（“”号）弄得糊涂了。在SQL标准中规划

8、的（Join）联结大致分为下面四种：1 内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。2 外联结：分为外左联结和外右联结。左联结A、B表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：Select A.name B.name From A Left Join B On A.id=B.id 和Select A.name B.name From B Right Join A on B.id=A.id执行后

9、的结果是一样的。3全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。4无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版的数据库系统概论书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这

10、些联结功能的内涵。2 MySQL联结（Join）的语法MySQL支持Select和某些Update和Delete情况下的Join语法，具体语法上的细节有：table_references: table_reference , table_reference table_reference: table_factor | join_tabletable_factor: tbl_name AS alias USE|IGNORE|FORCE INDEX (key_list) | ( table_references ) | OJ table_reference LEFT OUTER JOIN tab

11、le_reference ON conditional_expr join_table: table_reference INNER | CROSS JOIN table_factor join_condition | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON condition | table_reference LEFT OUTER JOIN table_reference join_condition | table_reference NATURA

12、L LEFT OUTER JOIN table_factor | table_reference RIGHT OUTER JOIN table_reference join_condition | table_reference NATURAL RIGHT OUTER JOIN table_factorjoin_condition: ON conditional_expr | USING (column_list)上面的用法摘自权威资料，不过大家看了是否有点晕呢？呵呵，应该问题主要还在于table_reference是什么，table_factor又是什么？这里的table_reference

13、其实就是表的引用的意思，因为在MySQL看来，联结就是一种对表的引用，因此把需要联结的表定义为table_reference，同时在SQL Standard中也是如此看待的。而table_factor则是MySQL对这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表，如下面例子中的JOIN后面括号：SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)这个语句的执行结果和下面语句其实是一样的：SELECT * FROM t1 LEFT JOIN (t2 CROSS JOI

14、N t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)这两个例子不仅让我们了解了MySQL中table_factor和table_reference含义，同时能理解一点CROSS JOIN的用法，我要补充的是在MySQL现有版本中CROSS JOIN的作用和INNER JOIN是一样的（虽然在SQL Standard中是不一样的，然而在MySQL中他们的区别仅仅是INNER JOIN需要附加ON参数的语句，而CROSS JOIN不需要）。既然说到了ON语句，那就解释一下吧，ON语句其实和WHERE语句功能大致相当，只是这里的

15、ON语句是专门针对联结表的，ON语句后面的条件的要求和书写方式和WHERE语句的要求是一样的，大家基本上可以把ON当作WHERE用。大家也许也看到了OJ table_reference LEFT OUTER JOIN table_reference这个句子，这不是MySQL的标准写法，只是为了和ODBC的SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。那下面就具体讲讲简单的JOIN的用法了。首先我们假设有2个表A和B，他们的表结构和字段分别为：表A：IDName1Tim2Jimmy3John4Tom表B：IDHobby1Football2Basketball2Ten

16、nis4Soccer1 内联结：Select A.Name B.Hobby from A, B where A.id = B.id，这是隐式的内联结，查询的结果是：NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer它的作用和 Select A.Name from A INNER JOIN B ON A.id = B.id是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。2 外左联结Select A.Name from A Left JOIN B ON A.id = B.id，典型的外左联结，这样查询得到的结果将会

17、是保留所有A表中联结字段的记录，若无与其相对应的B表中的字段记录则留空，结果如下：NameHobbyTimFootballJimmyBasketball，TennisJohnTomSoccer所以从上面结果看出，因为A表中的John记录的ID没有在B表中有对应ID，因此为空，但Name栏仍有John记录。3 外右联结如果把上面查询改成外右联结：Select A.Name from A Right JOIN B ON A.id = B.id，则结果将会是：NameHobbyTimFootballJimmyBasketballJimmyTennisTomSoccer这样的结果都是我们可以从外左联结

18、的结果中猜到的了。说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲MySQL联结查询中的某些参数的作用：1USING (column_list)：其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：a LEFT JOIN b USING (c1,c2,c3)，其作用相当于下面语句a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3只是用ON来代替会书写比较麻烦而已。2NATURAL LEFT JOIN：这个句子的作用相当于

19、INNER JOIN，或者是在USING子句中包含了联结的表中所有字段的Left JOIN（左联结）。3STRAIGHT_JOIN：由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。最后要说的就是，在MySQL5.0以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT

20、JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;但是MySQL并不是这样执行的，其后台的真正执行方式是下面的语句：SELECT t1.id,t2.id,t3.id FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) ) WHERE t1.id=t2.id;这并不是我们想要的效果，所以我们需要这样输入：SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;在这里括号是相当重要的，因此以后在写这样的查询的时候我

21、们不要忘记了多写几个括号，至少这样能避免很多错误（因为这样的错误是很难被开发人员发现的）。如果对上面内容有疑问可以来信查询：陈朋奕 chenpengyi#gmail.，请注明出处及作者。MySQL left join 联合查询的效率分析database.51cto. 2010-05-21 14:36 佚名 博客园 我要评论(0)摘要：我们今天主要向大家介绍的是MySQL left join 联合查询的效率分析，以及在实际操作中值得我们大家注意的事项的描述。标签：MySQL left join 以下的文章主要讲述的是MySQL left join 联合查询的效率分析，我在一个信誉度很好的找到一个

22、关于MySQL left join 联合查询的效率分析的资料，今天拿出来供大家分享，希望会给你带来一些帮助在此方面。user表:代码:id | name1 | libk2 | zyfon3 | daodaouser_action表:代码:user_id | action1 | jump1 | kick1 | jump2 | run4 | swimsql:代码:select id, name, action from user as uleft join user_action a on u.id = a.user_idresult:代码:id | name | action1 | libk |

23、 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中没有相应的纪录，但是却出现在了结果集中因为现在是MySQL left join，所有的工作以left为准.结果1，2，3，4都是既在左表又在右表的纪录，5是只在左表，不在右表的纪录结论：我们可以想象MySQL left join 是这样工作的从左表读出一条

24、，选出所有与on匹配的右表纪录(n条)进行连接，形成n条纪录(包括重复的行，如：结果1和结果3)，如果右边没有与on条件匹配的表，那连接的字段都是null.然后继续读下一条。引申：我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表，不在右表的纪录，注意用来判断的那列必须声明为not null的。如：sql:代码:1.selectid,name,actionfromuserasu 2.leftjoinuser_actionaonu.id=a.user_id 3.wherea.user_idisNULL(注意:1.列值为null应该用is null 而不能用=NULL2.这里a.u

25、ser_id 列必须声明为 NOT NULL 的)代码:1.result: 2.id|name|action 3.3|daodao|NULLTips:1. on a.c1 = b.c1 等同于 using(c1)2. INNER JOIN 和 , (逗号) 在语义上是等同的3. 当 MySQL 在从一个表中检索信息时，你可以提示它选择了哪一个索引。如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引，这个特性将是很有用的。通过指定 USE INDEX (key_list)，你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。可选的二选一句法 IGNOR

26、E INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。4. 一些例子：代码:1.MySQLSELECT*FROMtable1,table2WHEREtable1.id=table2.id; 2.MySQLSELECT*FROMtable1LEFTJOINtable2ONtable1.id=table2.id; 3.MySQLSELECT*FROMtable1LEFTJOINtable2USING(id); 4.MySQLSELECT*FROMtable1LEFTJOINtable2ONtable1.id=table2.id 5.-LEFTJOINtable3ONta

27、ble2.id=table3.id; 6.MySQLSELECT*FROMtable1USEINDEX(key1,key2) 7.-WHEREkey1=1ANDkey2=2ANDkey3=3; 8.MySQLSELECT*FROMtable1IGNOREINDEX(key3) 9.-WHEREkey1=1ANDkey2=2ANDkey3=3;如何优化LEFT JOIN和RIGHT JOIN在MySQL中，A LEFT JOIN B join_condition执行过程如下：根据表A和A依赖的所有表设置表B。根据MySQL LEFT JOIN条件中使用的所有表(除了B)设置表A。LEFT JOI

28、N条件用于确定如何从表B搜索行。(换句话说，不使用WHERE子句中的任何条件）。可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。进行所有标准WHERE优化。如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的col_name IS NULL，其中col_name是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

29、RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取：代码:1.SELECT* 2.FROMa,bLEFTJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key) 3.WHEREb.key=d.key;在这种情况下修复时用a的相反顺序，b列于FROM子句中：代码:1.SELECT* 2.FROMb,aLEF

30、TJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key) 3.WHEREb.key=d.key;MySQL可以进行下面的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接。例如，在下面的查询中如果t2.column1为NULL，WHERE 子句将为false：代码:1.SELECT*FROMt1LEFTJOINt2ON(column1)WHEREt2.column2=5;因此，可以安全地将查询转换为普通联接：1.SELECT*FROMt1,t2WHEREt2.column2=5ANDt1.column1=t2.column1;这样可以