嵌套选择的性能 [英] Performance of nested select

问题描述

我知道这是一个常见的问题，我已经阅读了其他几篇文章和论文，但是找不到结合索引字段和两个查询都可以返回的记录量的文章。

I know this is a common question and I have read several other posts and papers but I could not find one that takes into account indexed fields and the volume of records that both queries could return.

我的问题确实很简单。建议在此处使用类似SQL的语法（就性能而言）编写两者中的哪一个。

My question is simple really. Which of the two is recommended here written in an SQL-like syntax (in terms of performance).

第一个查询：

Select *
from someTable s
where s.someTable_id in
                    (Select someTable_id 
                     from otherTable o
                     where o.indexedField = 123)

第二个查询：

Select *
from someTable
where someTable_id in
                  (Select someTable_id 
                   from otherTable o
                   where o.someIndexedField = s.someIndexedField
                   and o.anotherIndexedField = 123)

我的理解是第二个查询将在数据库中查询外部查询将返回的每个元组，第一个查询将首先评估内部选择，然后将过滤器应用于外部查询。

My understanding is that the second query will query the database for every tuple that the outer query will return where the first query will evaluate the inner select first and then apply the filter to the outer query.

现在，第二个查询可能考虑到someIndexedField字段已建立索引，但是又说我们拥有成千上万条记录，使用第一个查询会更快吗？

Now the second query may query the database superfast considering that the someIndexedField field is indexed but say that we have thousands or millions of records wouldn't it be faster to use the first query?

注意：在Oracle数据库中。

Note: In an Oracle database.

推荐答案

在MySQL中，如果嵌套选择位于同一表上，则查询的执行时间为

In MySQL, if nested selects are over the same table, the execution time of the query can be hell.

一种提高MySQL性能的好方法是为嵌套选择创建一个临时表，并将主选择应用于该表。

A good way to improve the performance in MySQL is create a temporary table for the nested select and apply the main select against this table.

例如：

Select *
from someTable s1
where s1.someTable_id in
                    (Select someTable_id 
                     from someTable s2
                     where s2.Field = 123);

可以通过以下方式获得更好的性能：

Can have a better performance with:

create temporary table 'temp_table' as (
  Select someTable_id 
  from someTable s2
  where s2.Field = 123
);

Select *
from someTable s1
where s1.someTable_id in
                    (Select someTable_id 
                     from tempTable s2);

我不确定大量数据的性能。

I'm not sure about performance for a large amount of data.

这篇关于嵌套选择的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！