使用 Left Join 的 Mysql 查询太慢了
查询:
select `r`.`id` as `id`
from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm` on `cm`.`rlsc_id` != `r`.`id`
两个表都有8k条记录,但是为什么很慢,有时需要2-3分钟甚至更长时间?
天啊,这个查询让 mysql 服务器宕机了。将立即回复大家:(
所有建议对列进行索引的人都是正确的。 是的,我写的查询很愚蠢而且有问题。谢谢纠正我。
Query:
select `r`.`id` as `id`
from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm` on `cm`.`rlsc_id` != `r`.`id`
Both tables have 8k records but why is it very slow, taking 2-3 minutes and more sometimes?
OMG , this query makes mysql server down. Will get back to you peoples in a second :(
All peoples those suggested Indexing the columns are all Correct.
Yeh the query i wrote was silly and buggy. Thanks correcting me.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
还可以考虑为表建立索引。我们正在一个超过 100 万条记录的表上运行多个左连接,返回结果的时间不会超过一两秒。
Consider also indexing your tables. We're running multiple left joins on a 1million+ record table that doesn't take more than a second or two to return results.
您真的需要
!=
还是本来就是 =
?这将几乎选择两个表的笛卡尔积。 (我猜大约有 6000 万行)
编辑:来自评论
我认为如果您想使用
outer join
方法,这就是您所需要的。虽然我的偏好通常是
Do you really need the
!=
or is it meant to be=
?This will select nearly the cartesian product of the 2 tables. (I guess around 60 million rows)
Edit: From the comment
I think this is what you need if you want to use the
outer join
approach.Although my preference is usually
您想选择什么?
如果您想查找在其他表中没有匹配记录的 tbl_rls 记录,请使用此查询
What do you want to select?
Use this query if you want to find tbl_rls records that haven't matching records in other table
MySQL 的 EXPLAIN 可能会帮助您找出发生了什么。
MySQL's EXPLAIN might help you finding out what is going on.
您可能需要提供更多信息。但我会尝试的一件事是反转 ON 子句的顺序(因为它很简单):
ON r.id != cm.rlsc_id
编辑:你应该在 PK 上放置索引(id)列。
但我认为这篇文章可能会对您有所帮助。
基本上它表示
NOT IN
比LEFT JOIN
占用更少的资源。该文章中的评论者提到使用NOT EXISTS
是最好的。另外,我不确定这是否准确,但是这篇文章说< code>NOT IN 进行全表扫描,
NOT EXISTS
可以使用索引。You may need to provide more info. But one thing I would try is reversing the order of your ON clause (because it's so easy):
ON r.id != cm.rlsc_id
Edit: and you should put indexes on your PK (id) columns.
But I think this article might help you out.
Basically it says that
NOT IN
takes less resources thanLEFT JOIN
. A commenter in that article mentions usingNOT EXISTS
is best.Also, I'm not sure this is accurate or not, but this article says that
NOT IN
does a full table scan, andNOT EXISTS
can use an index.看起来您想要的 r.id 值不在 tblc_comment_manager 表中。
使用 Not In
select
r
.id
作为id
来自
tbl_rls
为r
其中
r
.id
不在(从tblc_comment_manager
中选择不同的cm
.rlsc_id
作为厘米
)Looks like you are wanting the r.id values that are not in the tblc_comment_manager table.
Use a Not In
select
r
.id
asid
from
tbl_rls
asr
where
r
.id
not in (select distinctcm
.rlsc_id
fromtblc_comment_manager
ascm
)