真诚请教：大数据量左连接怎么提高性能？

wanan_YLF · 发表于 2011-3-11 17:51

感觉我在SQL语句这方面比较薄弱，像第一个改变我就没想到过可以这样写。
请问是否有复杂SQL语句的学习资料，感觉这方面的资料比较少...
邮箱：jzhzh1988@qq.com
谢谢...

wanan_YLF · 发表于 2011-3-11 18:03

limit 0，10 放在里面会让结果集产生变化吧...

而且原本结果是空集的，变了一下，有查询结果了...

[ 本帖最后由 wanan_YLF 于 2011-3-11 18:04 编辑 ]

jinguanding · 发表于 2011-3-11 23:07

limit 0，10 放在里面会让结果集产生变化吧... 跟这句没关系

而是跟把b表中的那个条件放到ON语句部分

因为你之前的写法中虽然使用了LEFT JOIN，但实现的效果非LEFT JOIN而是INNER JOIN的作用

按LEFT JOIN的效果只有左边表限制条件下能查到记录，则不管如何与右边的表连接关系，一定有结果集的

没有其他特殊的学习，资料，建议以手册为本，以及SQL标准语法的真谛即可.....去官方下个手册吧，等后续的话，我专挑个时间讲解下此写法

见过很多人搞混淆的情况.....

mysqldbd · 发表于 2011-3-13 13:28

原帖由 jinguanding 于 2011-3-11 23:07 发表
limit 0，10 放在里面会让结果集产生变化吧... 跟这句没关系

而是跟把b表中的那个条件放到ON语句部分

因为你之前的写法中虽然使用了LEFT JOIN，但实现的效果非LEFT JOIN而是INNER JOIN的作用

按LEFT JOIN的效果只有左边表限制条件下能查到记录，则不管如何与右边的表连接关系，一定有结果集的

没有其他特殊的学习，资料，建议以手册为本，以及SQL标准语法的真谛即可.....去官方下个手册吧，等后续的话，我专挑个时间讲解下此写法

见过很多人搞混淆的情况.....

是不是在on后面加上 where 子句的话，就表示用的是 INNER JOIN而不是LEFT JOIN的呢？

wanan_YLF · 发表于 2011-3-13 20:05

不一定的。像我这句，因为where语句后面加了一个 b.GongXiaoID=1040700，而这个字段只有B表才有，导致即使A表有不符合左连接条件a.MeiShiID=b.MeiShiID的行也被过滤掉了。所以这个语句的实际效果变成跟INNER join一样了。

呵呵，这是我的想法，错的话，希望能帮忙指正...

[ 本帖最后由 wanan_YLF 于 2011-3-13 20:26 编辑 ]

wanan_YLF · 发表于 2011-3-13 20:24

原帖由 jinguanding 于 2011-3-11 17:41 发表
EXPLAIN
SELECT a.MeiShiID,a.Title,a.Picture,a.HitNum,a.MainMaterials2,a.Adminicle2,a.Spice2,a.HitNum
FROM a left join  b ON a.MeiShiID=b.MeiShiID AND b.GongXiaoID=1040700
WHERE  Updated>'2011-03-05' AND  Updated'2011-03-05' AND  Updated
改成上述对你的业务逻辑没有问题的话，就可以继续修改为（注：因之前的写法为非标准的左连接）：

EXPLAIN
SELECT T.*
FROM (SELECT a.MeiShiID,a.Title,a.Picture,a.HitNum,a.MainMaterials2,a.Adminicle2,a.Spice2,a.HitNum FROM a  WHERE  Updated>'2011-03-05' AND  Updated<'2011-03-12'  ORDER BY a.HitNum DESC LIMIT 0,10) T
LEFT JOIN b ON T.MeiShiID=b.MeiShiID AND b.GongXiaoID=1040700

上面的第一个修改我是这样理解的，符合a.MeiShiID=b.MeiShiID AND b.GongXiaoID=1040700才进行左连接，这样就减小了左连接的数据量。我对左连接时索引的使用情况和原理不是很理解，要是兄台能对这方面说说就更好了。

第一个修改的执行顺序，我的理解是这样的：a.MeiShiID=b.MeiShiID AND b.GongXiaoID=1040700判断后再左连接成一个结果集，然后再根据where语句进行过滤，limit 0，10，最后再ORDER BY a.HitNum DESC

第二个修改的执行顺序：对A表Updated>'2011-03-05' AND Updated<'2011-03-12' ORDER BY a.HitNum DESC LIMIT 0,10后，得到10个结果后跟B表进行 ON T.MeiShiID=b.MeiShiID AND b.GongXiaoID=1040700的左连接。
我的疑惑在于，limit 条件在左连接前执行不是会对最终的结果产生影响吗？

呵呵，不好意思，这两天俗事缠身，这么晚才上来看。这句其实不是我写的，只是跑出来的慢查询语句，想到过INNER JOIN会好一点点，但这对性能上的影响很小，实在想不到更好的修改方案。

[ 本帖最后由 wanan_YLF 于 2011-3-13 22:52 编辑 ]

jinguanding · 发表于 2011-3-13 22:33

b.GongXiaoID=1040700加到WHERE 与ON部分是不一样的

WHERE部分会过滤掉左边表（a）符合要求的数据，而加入到ON部分只是过滤掉右边表(b)不符合的数据，对于左边表一起显示的时候用NULL替换掉

明天上班写个测试给你看下....

mysqldbd · 发表于 2011-3-14 10:06

原帖由 jinguanding 于 2011-3-13 22:33 发表
b.GongXiaoID=1040700加到WHERE 与ON部分是不一样的

WHERE部分会过滤掉左边表（a）符合要求的数据，而加入到ON部分只是过滤掉右边表(b)不符合的数据，对于左边表一起显示的时候用NULL替换掉

明天上班写个测试给你看下....

'

在A left join B中
有点明白了，where在左，管着A，on在右，管着B，

jinguanding · 发表于 2011-3-14 10:07

root@localhost : test 10:05:51> select * from eugene_test;
+------+------------------------+---------------------+---------------------+
| ID | UserName             | CreateDate       | AlterDate          |
+------+------------------------+---------------------+---------------------+
| 0 | ef773374-4ae2-11e0-9 | 2011-03-10 14:52:16 | 2011-03-10 14:55:22 |
| 2 | 5e3a5a34-4ae3-11e0-9 | 2010-12-20 14:55:22 | 2011-03-02 14:55:22 |
| 3 | 5e3f203c-4ae3-11e0-9 | 2010-12-21 14:55:22 | 2011-03-06 14:55:22 |
| 4 | 5e405844-4ae3-11e0-9 | 2010-12-16 14:55:22 | 2011-03-02 14:55:22 |
| 5 | 5e4190e2-4ae3-11e0-9 | 2010-12-12 14:55:22 | 2011-03-03 14:55:22 |
| 6 | 5e42c98a-4ae3-11e0-9 | 2011-02-11 14:55:22 | 2011-03-10 14:55:22 |
| 7 | 5e440304-4ae3-11e0-9 | 2011-02-16 14:55:22 | 2011-03-10 14:55:22 |
| 8 | 5e453bc0-4ae3-11e0-9 | 2010-12-21 14:55:22 | 2011-03-04 14:55:22 |
| 9 | 5e467454-4ae3-11e0-9 | 2011-03-01 14:55:22 | 2011-03-06 14:55:22 |
| 10 | 5e47acf2-4ae3-11e0-9 | 2010-12-29 14:55:22 | 2011-03-07 14:55:22 |
| 11 | 5e48e590-4ae3-11e0-9 | 2011-01-03 14:55:22 | 2011-03-08 14:55:22 |
| 12 | 5e4a1e2e-4ae3-11e0-9 | 2011-03-10 14:55:22 | 0000-00-00 00:00:00 |
| 13 | a3c665c0-4ae3-11e0-9 | 2011-02-21 14:57:18 | 2011-03-09 14:57:18 |
| 14 | a3cbf102-4ae3-11e0-9 | 2011-02-08 14:57:18 | 2011-03-10 14:57:18 |
| 15 | a3cd2978-4ae3-11e0-9 | 2011-02-20 14:57:18 | 2011-03-02 14:57:18 |
| 16 | a3ce6220-4ae3-11e0-9 | 2010-12-29 14:57:18 | 2011-03-10 14:57:18 |
| 17 | a3cf9b9a-4ae3-11e0-9 | 2010-12-09 14:57:18 | 2011-03-05 14:57:18 |
| 18 | a3d0d44c-4ae3-11e0-9 | 2011-03-10 14:57:18 | 2011-03-07 14:57:18 |
| 19 | a3d20d12-4ae3-11e0-9 | 2010-12-16 14:57:18 | 2011-03-09 14:57:18 |
| 20 | a3d345ba-4ae3-11e0-9 | 2011-03-10 14:57:18 | 2011-03-03 14:57:18 |
| 21 | a3d47e6c-4ae3-11e0-9 | 2011-01-18 14:57:18 | 2011-03-06 14:57:18 |
| 22 | a3d5b71e-4ae3-11e0-9 | 2010-12-30 14:57:18 | 2011-03-09 14:57:18 |
| 23 | a3d6efc6-4ae3-11e0-9 | 2011-03-10 14:57:18 | 0000-00-00 00:00:00 |
| 24 | 5d2ca326-4ae4-11e0-9 | 2010-12-21 15:02:29 | 2011-03-06 15:02:29 |
| 25 | 5d300516-4ae4-11e0-9 | 2011-01-01 15:02:29 | 2011-03-09 15:02:29 |
| 26 | 5d313a62-4ae4-11e0-9 | 2010-12-13 15:02:29 | 2011-03-02 15:02:29 |
| 27 | 5d327350-4ae4-11e0-9 | 2011-01-21 15:02:29 | 2011-03-01 15:02:29 |
| 28 | 5d33ac52-4ae4-11e0-9 | 2011-02-07 15:02:29 | 2011-03-03 15:02:29 |
| 29 | 5d34e702-4ae4-11e0-9 | 2010-12-31 15:02:29 | 2011-03-08 15:02:29 |
| 30 | 5d361ffa-4ae4-11e0-9 | 2011-02-12 15:02:29 | 2011-03-05 15:02:29 |
| 31 | 5d3758e8-4ae4-11e0-9 | 2010-12-13 15:02:29 | 2011-03-03 15:02:29 |
| 32 | 5d3891e0-4ae4-11e0-9 | 2011-02-11 15:02:29 | 2011-03-10 15:02:29 |
| 33 | 5d39cae2-4ae4-11e0-9 | 2011-02-09 15:02:29 | 2011-03-06 15:02:29 |
| 34 | 5d3b03ee-4ae4-11e0-9 | 2011-03-10 15:02:29 | 0000-00-00 00:00:00 |
| 35 | f8519230-4ae4-11e0-9 | 2011-02-15 15:06:50 | 2011-03-02 15:06:50 |
| 36 | f8555c9e-4ae4-11e0-9 | 2011-01-02 15:06:50 | 2011-03-03 15:06:50 |
| 37 | f856950a-4ae4-11e0-9 | 2010-12-26 15:06:50 | 2011-03-06 15:06:50 |
| 38 | f857cdbc-4ae4-11e0-9 | 2011-03-10 15:06:50 | 2011-03-04 15:06:50 |
| 39 | f859066e-4ae4-11e0-9 | 2011-01-28 15:06:50 | 2011-03-10 15:06:50 |
| 40 | f85a3f20-4ae4-11e0-9 | 2010-12-21 15:06:50 | 2011-03-01 15:06:50 |
| 41 | f85b78a4-4ae4-11e0-9 | 2011-01-31 15:06:50 | 2011-03-10 15:06:50 |
| 42 | f85cb156-4ae4-11e0-9 | 2011-02-19 15:06:50 | 2011-03-03 15:06:50 |
| 43 | f85dea08-4ae4-11e0-9 | 2011-02-16 15:06:50 | 2011-03-02 15:06:50 |
| 44 | f85f22c4-4ae4-11e0-9 | 2011-01-14 15:06:50 | 2011-03-08 15:06:50 |
| 45 | f862ce92-4ae4-11e0-9 | 2011-03-10 15:06:50 | 0000-00-00 00:00:00 |
| 46 | 9fd88e04-4ae6-11e0-9 | 2011-01-28 15:18:40 | 2011-03-06 15:18:40 |
| 47 | 9fdb8c8a-4ae6-11e0-9 | 2010-12-04 15:18:40 | 2011-03-06 15:18:40 |
| 48 | 9fdcc4f6-4ae6-11e0-9 | 2011-01-12 15:18:40 | 2011-03-06 15:18:40 |
| 49 | 9fddfda8-4ae6-11e0-9 | 2011-02-05 15:18:40 | 2011-03-06 15:18:40 |
| 50 | 9fdf363c-4ae6-11e0-9 | 2011-03-01 15:18:40 | 2011-03-08 15:18:40 |
| 51 | 9fe06fca-4ae6-11e0-9 | 2010-12-14 15:18:40 | 2011-03-04 15:18:40 |
| 52 | 9fe1a872-4ae6-11e0-9 | 2011-01-07 15:18:40 | 2011-03-09 15:18:40 |
| 53 | 9fe2e11a-4ae6-11e0-9 | 2011-03-01 15:18:40 | 2011-03-02 15:18:40 |
| 54 | 9fe419d6-4ae6-11e0-9 | 2011-02-27 15:18:40 | 2011-03-01 15:18:40 |
| 55 | 9fe55274-4ae6-11e0-955 | 2011-02-01 15:18:40 | 2011-03-10 15:18:40 |
| 56 | 9fe86022-4ae6-11e0-9 | 2011-03-10 15:18:40 | 0000-00-00 00:00:00 |
| 57 | 4876ab0a-4af0-11e0-9 | 2011-02-24 16:27:48 | 2011-03-05 16:27:48 |
| 58 | 4879d794-4af0-11e0-9 | 2011-02-04 16:27:48 | 2011-03-10 16:27:48 |
| 59 | 487b0ff6-4af0-11e0-9 | 2011-02-05 16:27:48 | 2011-03-06 16:27:48 |
| 60 | 487c4894-4af0-11e0-9 | 2011-02-25 16:27:48 | 2011-03-07 16:27:48 |
| 61 | 487d8150-4af0-11e0-9 | 2011-01-11 16:27:48 | 2011-03-03 16:27:48 |
| 62 | 487eba0c-4af0-11e0-9 | 2010-12-04 16:27:48 | 2011-03-05 16:27:48 |
| 63 | 48831430-4af0-11e0-9 | 2011-03-02 16:27:49 | 2011-03-04 16:27:49 |
| 64 | 48843928-4af0-11e0-9 | 2010-12-03 16:27:49 | 2011-03-01 16:27:49 |
| 65 | 488571da-4af0-11e0-9 | 2010-12-19 16:27:49 | 2011-03-08 16:27:49 |
| 67 | 48891fba-4af0-11e0-9 | 2011-03-10 16:27:49 | 0000-00-00 00:00:00 |
| 4886 | 4886aa8c-4af0-11e0-9 | 2010-12-28 16:27:49 | 2011-03-10 16:27:49 |
+------+------------------------+---------------------+---------------------+

root@localhost : test 10:06:20> select * from test_left;
+----+-------+
| ID | Money |
+----+-------+
| 51 | 200 |
| 60 | 102 |
| 67 | 100 |
+----+-------+
3 rows in set (0.00 sec)

root@localhost : test 10:05:44> SELECT M.*,N.money
-> FROM eugene_test  M LEFT JOIN test_left N ON M.ID=N.ID AND N.ID=60
-> WHERE CreateDate >='2011-02-01' AND  CreateDate <'2011-03-14' ORDER BY M.ID DESC LIMIT 10;
+----+------------------------+---------------------+---------------------+-------+
| ID | UserName             | CreateDate       | AlterDate          | money |
+----+------------------------+---------------------+---------------------+-------+
| 67 | 48891fba-4af0-11e0-9 | 2011-03-10 16:27:49 | 0000-00-00 00:00:00 |  NULL |
| 63 | 48831430-4af0-11e0-9 | 2011-03-02 16:27:49 | 2011-03-04 16:27:49 |  NULL |
| 60 | 487c4894-4af0-11e0-9 | 2011-02-25 16:27:48 | 2011-03-07 16:27:48 | 102 |
| 59 | 487b0ff6-4af0-11e0-9 | 2011-02-05 16:27:48 | 2011-03-06 16:27:48 |  NULL |
| 58 | 4879d794-4af0-11e0-9 | 2011-02-04 16:27:48 | 2011-03-10 16:27:48 |  NULL |
| 57 | 4876ab0a-4af0-11e0-9 | 2011-02-24 16:27:48 | 2011-03-05 16:27:48 |  NULL |
| 56 | 9fe86022-4ae6-11e0-9 | 2011-03-10 15:18:40 | 0000-00-00 00:00:00 |  NULL |
| 55 | 9fe55274-4ae6-11e0-955 | 2011-02-01 15:18:40 | 2011-03-10 15:18:40 |  NULL |
| 54 | 9fe419d6-4ae6-11e0-9 | 2011-02-27 15:18:40 | 2011-03-01 15:18:40 |  NULL |
| 53 | 9fe2e11a-4ae6-11e0-9 | 2011-03-01 15:18:40 | 2011-03-02 15:18:40 |  NULL |
+----+------------------------+---------------------+---------------------+-------+
10 rows in set (0.00 sec)

root@localhost : test 10:05:44> SELECT T.*,N.money
-> FROM (SELECT M.* FROM eugene_test  M WHERE CreateDate >='2011-02-01' AND  CreateDate <'2011-03-14'  ORDER BY M.ID DESC LIMIT 10)T
-> LEFT JOIN test_left N ON T.ID=N.ID AND N.ID=60;
+----+------------------------+---------------------+---------------------+-------+
| ID | UserName             | CreateDate       | AlterDate          | money |
+----+------------------------+---------------------+---------------------+-------+
| 67 | 48891fba-4af0-11e0-9 | 2011-03-10 16:27:49 | 0000-00-00 00:00:00 |  NULL |
| 63 | 48831430-4af0-11e0-9 | 2011-03-02 16:27:49 | 2011-03-04 16:27:49 |  NULL |
| 60 | 487c4894-4af0-11e0-9 | 2011-02-25 16:27:48 | 2011-03-07 16:27:48 | 102 |
| 59 | 487b0ff6-4af0-11e0-9 | 2011-02-05 16:27:48 | 2011-03-06 16:27:48 |  NULL |
| 58 | 4879d794-4af0-11e0-9 | 2011-02-04 16:27:48 | 2011-03-10 16:27:48 |  NULL |
| 57 | 4876ab0a-4af0-11e0-9 | 2011-02-24 16:27:48 | 2011-03-05 16:27:48 |  NULL |
| 56 | 9fe86022-4ae6-11e0-9 | 2011-03-10 15:18:40 | 0000-00-00 00:00:00 |  NULL |
| 55 | 9fe55274-4ae6-11e0-955 | 2011-02-01 15:18:40 | 2011-03-10 15:18:40 |  NULL |
| 54 | 9fe419d6-4ae6-11e0-9 | 2011-02-27 15:18:40 | 2011-03-01 15:18:40 |  NULL |
| 53 | 9fe2e11a-4ae6-11e0-9 | 2011-03-01 15:18:40 | 2011-03-02 15:18:40 |  NULL |
+----+------------------------+---------------------+---------------------+-------+
10 rows in set (0.00 sec)

另外从你那话语中，可以猜测到一点，兄弟对INNER JOIN 与LEFT JOIN的作用与区别  还需要再看下

wanan_YLF · 发表于 2011-3-14 11:56

感谢jinguanding的耐心和解答，确实我对join on的理解错了...
参考了下http://www.cnblogs.com/tinachang ... /09/25/1298949.html，确实对left join 和inner join了解得清晰一点了。
我想，反正A表不符合条件的最后都过滤掉了，那是不是直接做INNER JOIN 会更好一点？从性能上说...

真诚请教：大数据量左连接怎么提高性能？

回复 #9 jinguanding 的帖子

回复 #9 jinguanding 的帖子

回复 #12 wanan_YLF 的帖子

回复 #14 mysqldbd 的帖子

回复 #13 jinguanding 的帖子

回复 #15 wanan_YLF 的帖子

回复 #19 jinguanding 的帖子

浏览过的版块