内连接和in（）子句的性能在哪里？

I can get same result for these queries, but which one is the fastest, and most efficient?

我可以为这些查询得到相同的结果,但哪一个是最快,最有效的?

where in() or inner join?

在()或内部连接的位置?

SELECT `stats`.`userid`,`stats`.`sumpoint` 
FROM  `stats` 
INNER JOIN users
ON `stats`.`userid` = `users`.`userid` 
WHERE `users`.`nick` =  '$nick'

ORDER BY `statoylar`.`sumpoint` DESC  limit 0,10

and

SELECT `stats`.`userid`,`stats`.`sumpoint` 
FROM  `stats` 
WHERE userid
IN (
SELECT userid
FROM  `users` 
WHERE  `users`.`nick` =  '$nick'
)
ORDER BY `stats`.`sumpoint` DESC  limit 0,10

3 个解决方案

#1

Depends on your SQL engine. Newer SQL systems that have reasonable query optimizers will most likely rewrite both queries to the same plan. Typically, a sub-query (your second query) is rewritten using a join (the first query).

取决于您的SQL引擎。具有合理查询优化器的较新SQL系统很可能会将两个查询重写为同一计划。通常,使用连接(第一个查询)重写子查询(第二个查询)。

In simple SQL engines that may not have great query optimizers, the join should be faster because they may run sub-queries into a temporary in-memory table before running the outer query.

在可能没有很好的查询优化器的简单SQL引擎中,连接应该更快,因为它们可以在运行外部查询之前将子查询运行到临时内存表中。

In some SQL engines that have limited memory footprint, however, the sub-query may be faster because it doesn't require joining -- which produces more data.

但是,在一些内存占用有限的SQL引擎中,子查询可能更快,因为它不需要连接 - 这会产生更多数据。

So, in summary, it depends.

总而言之,这取决于。

#2

to check the performance execute both Query with EXPLAIN SELECT .... AFAIK, INNER JOIN is faster than IN
btw what is your type of table engine MYISAM or INNODB

检查性能执行两个查询与EXPLAIN SELECT .... AFAIK,INNER JOIN快于IN顺便说一下你的表引擎类型MYISAM或INNODB

#3

-2

also there is another option, EXISTS. I'm a tsql guy so....

还有另一种选择,EXISTS。我是一个tsql的人......

SELECT s.[userid], s.[sumpoint] 
FROM stats AS s
WHERE
     EXISTS (
     SELECT 1
     FROM users AS u
     WHERE
         u.[userID] = s.[userID]
         AND u.[nick] = '$nick'
     )
ORDER BY s.[sumpoint] DESC

I think EXISTS is available in most engines. It's generally pretty fast.

我认为大多数引擎都可以使用EXISTS。它通常很快。

IN sql server at least (2005+) there is no performance difference at all between IN and EXISTS for cases where the column in question is not NULLABLE.

在sql server至少(2005+)中,对于有问题的列不可用的情况,IN和EXISTS之间根本没有性能差异。

probably irrelevant but hey.....

可能无关紧要,但嘿......

#1