哪个更快:关联子查询还是联接? [英] Which one is faster: correlated subqueries or join?

问题描述

我知道我们可以相关子查询并加入.但是哪一个更快?有黄金法则还是必须同时衡量?

I know we can do correlated subqueries and join. But which one is faster? Is there a golden rule or I must measure both?

推荐答案

首先，相关子查询确实是一种联接.关于哪一个产生最佳执行计划没有黄金法则.如果您对性能感兴趣，则需要尝试不同的表格以查看最有效的方法.或者，至少，看看执行该决定的行刑计划.

First, a correlated subquery really is a type of join. There is no golden rule about which produces the best execution plan. If you are interested in performance, you need to try out the different forms to see what works best. Or, at least, look at the exeuction plans to make that decision.

通常，出于两个原因，我倾向于避免使用相关的子查询.首先，几乎总是可以在没有相关性的情况下编写它们.其次，许多查询引擎将它们转换为嵌套循环联接(尽管使用索引)，而其他联接策略可能更好.在这种情况下，相关子查询使并行查询变得困难.第三，关联的子查询通常在SELECT或WHERE子句中使用.我希望所有表都位于FROM子句中.

In general, I tend to avoid correlated subqueries for a couple of reasons. First, they can almost always be written without the correlation. Second, many query engines turn them into nested loop joins (albeit using indexes), and other join strategies might be better. In such cases, correlated subqueries make it difficult to parallelize the query. Third, correlated subqueries are usualy in either the SELECT or WHERE clauses. I like for all my tables to be in the FROM clause.

但是，在MySQL中，关联子查询通常是进行查询的最有效的方法.在IN子句中使用子查询时，尤其如此.因此，没有黄金法则.

In MySQL however, correlated subqueries are often the most efficient way to do a query. This is especially true when using a subquery in an IN clause. So, there is no golden rule.

这篇关于哪个更快:关联子查询还是联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！