使用 Left Join 的 Mysql 查询太慢了
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3257575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql query with Left Join is too very slow
提问by Arshdeep
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?
两个表都有 8k 记录，但为什么它很慢，有时需要 2-3 分钟甚至更多？
OMG , this query makes mysql server down. Will get back to you peoples in a second :(
天哪，这个查询使mysql服务器停机。将在一秒钟内回复你们：(
All peoples those suggested Indexing the columns are all Correct. Yeh the query i wrote was silly and buggy. Thanks correcting me.
所有建议对列进行索引的人都是正确的。是的，我写的查询很愚蠢而且有问题。谢谢纠正我。
回答by bpeterson76
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.
还考虑索引你的表。我们在一个 100 万多记录表上运行多个左连接，返回结果的时间不超过一两秒钟。
回答by Martin Smith
Do you really need the !=or is it meant to be =?
你真的需要!=还是它的意思=？
select `r`.`id` as `id` from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`!=`r`.`id
This will select nearly the cartesian product of the 2 tables. (I guess around 60 million rows)
这将选择几乎 2 个表的笛卡尔积。（我猜大约有 6000 万行）
Edit:From the comment
编辑：来自评论
yes it is " != " to match tbl_rls.id those are not in tblc_comment_manager
是的，匹配 tbl_rls.id 那些不在 tblc_comment_manager 中的是“!=”
I think this is what you need if you want to use the outer joinapproach.
如果你想使用这种outer join方法，我认为这就是你所需要的。
select DISTINCT `r`.`id` as `id` from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`=`r`.`id
WHERE `cm`.`rlsc_id` IS NULL
Although my preference is usually
虽然我的偏好通常是
select `r`.`id` as `id`
from `tbl_rls`
as `r`
WHERE NOT EXISTS(
SELECT * FROM `tblc_comment_manager` as `cm`
WHERE `cm`.`rlsc_id`=`r`.`id)
回答by Naktibalda
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
如果要查找与其他表中的记录不匹配的 tbl_rls 记录，请使用此查询
select `r`.`id`
from `tbl_rls` as `r`
left join `tblc_comment_manager` as `cm`
on `cm`.`rlsc_id`=`r`.`id
where `cm`.`rlsc_id` IS NULL
回答by JohnB
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 子句的顺序（因为它很简单）：
ON r.id != cm.rlsc_id
ON r.id != cm.rlsc_id
Edit: and you should put indexes on your PK (id) columns.
编辑：你应该在你的 PK (id) 列上放置索引。
But I think this article might help you out.
但我认为这篇文章可能会对你有所帮助。
Basically it says that NOT INtakes less resources than LEFT JOIN. A commenter in that article mentions using NOT EXISTSis best.
基本上它说它NOT IN比LEFT JOIN. 那篇文章中的评论者提到使用NOT EXISTS是最好的。
Also, I'm not sure this is accurate or not, but this article says that NOT INdoes a full table scan, and NOT EXISTScan use an index.
另外，我不确定这是否准确，但是这篇文章说可以NOT IN进行全表扫描，并且NOT EXISTS可以使用 index。
回答by DaveWilliamson
Looks like you are wanting the r.id values that are not in the tblc_comment_manager table.
看起来您想要不在 tblc_comment_manager 表中的 r.id 值。
Use a Not In
使用不在
select r.idas id
from tbl_rlsas r
where r.idnot in (select distinct cm.rlsc_idfrom tblc_comment_manageras cm)
选择r。id如id
从tbl_rls作为r
地方r。id不在（选择不同的cm. rlsc_idfrom tblc_comment_manageras cm）
