什么查询最快? [英] What query will be the fastest?

问题描述

我有三种构造查询的方法:

I have three ways to construct my query:

第一个:

select obj from table1 where condition1 and obj in (
select obj from table2 where condition2 and obj in (
select obj from table3 where condition3 and obj in (
...
)))

第二个:

select obj from table1 where condition1
and obj in (select obj from table2 where condition2)
and obj in (select obj from table3 where condition3)
...

第三个:

select table1.obj from table1
inner join table2 on table2.obj = table1.obj and table2.condition='condition2'
inner join table3 on table3.obj = table2.obj and table3.condition='condition3'
...
where table1.condition='condition1'

我的问题是，这些查询是否提供相同的结果，以及这些查询是否同样最优.

My questions is if these queries provide the same result and if these query are equally optimal.

我很确定前两个查询会产生相同的输出，但是第二个查询会更快.不确定第三个查询.

I am pretty sure that the first two queries produce the same output but the second query is faster. Am not sure about the third query.

添加

还有另一种选择:

select table1.obj from table1
inner join table2 on table2.obj = table1.obj
inner join table3 on table3.obj = table2.obj
...
where
table1.condition='condition1' and 
table2.condition='condition2' and 
table3.condition='condition3'

推荐答案

尽管总是有例外，但是选项3几乎肯定是最佳/首选.根据您的索引和数据分布，MySQL查询执行计划器将处理从表中提取哪个顺序.

Although there are always exceptions, Option 3 is almost surely the best/first choice. Depending on your indexes and data distributions, the MySQL query execution planner will handle which order to pull from the tables.

在其他情况下，将对外部查询的每一行执行子查询(选项1和2)-它们的效率可能非常低.因此，按照上一条语句，嵌套子查询(选项1)可能比使用一阶子查询(选项2)或普通联接(选项3)要糟糕得多.

In the other cases, subqueries (Options 1 and 2) are executed for every row of the outer query -- they can be terribly inefficient. So, following the previous statement, nested subqueries (Option 1), can be exponentially worse than using first order subqueries (Option 2) or normal joins (Option 3).

请注意，对于INNER JOIN而言，如果JOIN子句或WHERE子句中有其他条件，则对于性能或功能均无关紧要.因此，您的 other 选项实际上等效于选项3.

Note that for INNER JOINs, it doesn't matter with respect to performance or functionality if the extra conditions are in the JOIN clauses or in the WHERE clauses. Consequently, your other option is effectively equivalent to the Option 3.

这篇关于什么查询最快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！