内部联接和in()子句的性能在哪里? [英] inner join and where in() clause performance?

问题描述

对于这些查询，我可以获得相同的结果，但是哪一个是最快，最有效的?

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

in()或内部联接在哪里?

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

和

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

推荐答案

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

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引擎中，联接应该更快，因为它们可能在运行外部查询之前将子查询运行到临时内存表中.

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.

因此，总而言之，这取决于.

So, in summary, it depends.

这篇关于内部联接和in()子句的性能在哪里?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！