EXISTS 与 IN 的子查询 - MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14190788/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subqueries with EXISTS vs IN - MySQL
提问by Techie
Below two queries are subqueries. Both are the same and both works fine for me. But the problem is Method 1 query takes about 10 secs to execute while Method 2 query takes under 1 sec.
下面两个查询是子查询。两者都是一样的，对我来说都很好。但问题是方法 1 查询需要大约 10 秒来执行，而方法 2 查询需要不到 1 秒。
I was able to convert method 1 query to method 2 but I don't understand what's happening in the query. I have been trying to figure it out myself. I would really like to learn what's the difference between below two queries and how does the performance gain happen ? what's the logic behind it ?
我能够将方法 1 查询转换为方法 2，但我不明白查询中发生了什么。我一直试图自己弄清楚。我真的很想了解以下两个查询之间的区别以及性能提升是如何发生的？它背后的逻辑是什么？
I'm new to these advance techniques. I hope someone will help me out here. Given that I read the docswhich does not give me a clue.
我是这些先进技术的新手。我希望有人能帮助我。鉴于我阅读了没有给我任何线索的文档。
Method 1 :
方法一：
SELECT
*
FROM
tracker
WHERE
reservation_id IN (
SELECT
reservation_id
FROM
tracker
GROUP BY
reservation_id
HAVING
(
method = 1
AND type = 0
AND Count(*) > 1
)
OR (
method = 1
AND type = 1
AND Count(*) > 1
)
OR (
method = 2
AND type = 2
AND Count(*) > 0
)
OR (
method = 3
AND type = 0
AND Count(*) > 0
)
OR (
method = 3
AND type = 1
AND Count(*) > 1
)
OR (
method = 3
AND type = 3
AND Count(*) > 0
)
)
Method 2 :
方法二：
SELECT
*
FROM
`tracker` t
WHERE
EXISTS (
SELECT
reservation_id
FROM
`tracker` t3
WHERE
t3.reservation_id = t.reservation_id
GROUP BY
reservation_id
HAVING
(
METHOD = 1
AND TYPE = 0
AND COUNT(*) > 1
)
OR
(
METHOD = 1
AND TYPE = 1
AND COUNT(*) > 1
)
OR
(
METHOD = 2
AND TYPE = 2
AND COUNT(*) > 0
)
OR
(
METHOD = 3
AND TYPE = 0
AND COUNT(*) > 0
)
OR
(
METHOD = 3
AND TYPE = 1
AND COUNT(*) > 1
)
OR
(
METHOD = 3
AND TYPE = 3
AND COUNT(*) > 0
)
)
回答by bonCodigo
An Explain Planwould have shown you why exactly you should use Exists. Usually the question comes Exists vs Count(*). Existsis faster. Why?
一个Explain Plan会告诉你为什么你应该使用Exists. 通常问题来了Exists vs Count(*)。 Exists是比较快的。为什么？
With regard to challenges present by NULL: when subquery returns
Null, for IN the entire query becomesNull. So you need to handle that as well. But usingExist, it's merely afalse. Much easier to cope. SimplyINcan't compare anything withNullbutExistscan.e.g.
Exists (Select * from yourtable where bla = 'blabla');you get true/false the moment one hit is found/matched.In this case
INsort of takes the position of theCount(*)to select ALLmatching rows based on theWHEREbecause it's comparing all values.
关于 NULL 提出的挑战：当子查询返回时
Null，对于 IN 整个查询变为Null。所以你也需要处理它。但是使用Exist，它只是一个false. 应付起来容易多了。根本IN无法与任何东西进行比较，Null但Exists可以。例如
Exists (Select * from yourtable where bla = 'blabla');，当找到/匹配一个命中时，您会得到真/假。在这种情况下
IN，会根据 的位置Count(*)选择所有匹配的行，WHERE因为它正在比较所有值。
But don't forget this either:
但也不要忘记这一点：
EXISTSexecutes at high speed againstIN: when the subquery results is very large.INgets ahead ofEXISTS: when the subquery results is very small.
EXISTSIN当子查询结果非常大时，对:高速执行。IN超前EXISTS：当子查询结果非常小时。
Reference to for more details:
更多详情请参考：
回答by jsist
Method 2 is fast because it is using EXISTSoperator, where I MySQLdo not load any results.
As mentioned in your docslink as well, that it omits whatever is there in SELECTclause. It only checks for the first value that matches the criteria, once found it sets the condition TRUEand moves for further processing.
方法 2 很快，因为它使用EXISTS运算符，我MySQL不加载任何结果。正如您的文档链接中所提到的，它省略了SELECT子句中的任何内容。它只检查与条件匹配的第一个值，一旦找到，它就会设置条件TRUE并移动以进行进一步处理。
On the other side Method 1 has INoperator which loads all possible values and then matches it. Condition is set TRUEonly when exact match is found which is time consuming process.
另一方面，方法 1 具有IN加载所有可能值然后匹配它的运算符。TRUE仅当找到完全匹配时才设置条件，这是一个耗时的过程。
Hence your method 2 is fast.
因此，您的方法 2 很快。
Hope it helps...
希望能帮助到你...
回答by k.t
The EXISTSoperator is a Boolean operator that returns either true or false. The EXISTS operator is often used the in a subqueryto test for an “exist” condition.
该EXISTS运算符是一个布尔运算符返回true或false。EXISTS 运算符通常用于子查询中以测试“存在”条件。
SELECT
select_list
FROM
a_table
WHERE
[NOT] EXISTS(subquery);
If the subquery returns any row, the EXISTSoperator returns true, otherwise, it returns false.
如果子查询返回任何行，则EXISTS运算符返回 true，否则返回 false。
In addition, the EXISTSoperator terminates further processing immediately once it finds a matching row. Because of this characteristic, you can use the EXISTSoperator to improve the performance of the query in some cases.
此外，一旦找到匹配的行，EXISTS运算符就会立即终止进一步的处理。由于此特性，您可以在某些情况下使用EXISTS运算符来提高查询的性能。
The NOToperator negates the EXISTSoperator. In other words, the NOT EXISTSreturns true if the subquery returns no row, otherwise it returns false.
在NOT运算符否定EXISTS操作。换句话说，如果子查询没有返回行，则NOT EXISTS返回 true，否则返回 false。
You can use SELECT *, SELECT column, SELECT a_constant, or anything in the subquery. The results are the same because MySQL ignores the select_listthat appears in the SELECTclause.
您可以使用SELECT *、SELECT column、SELECT a_constant或子查询中的任何内容。结果是一样的，因为 MySQL 忽略了出现在SELECT子句中的select_list。
The reason is that the EXISTSoperator works based on the “at least found” principle. It returns true and stops scanning table once at least one matching row found.
原因是EXISTS运算符基于“至少找到”原则工作。一旦找到至少一个匹配的行，它就会返回 true 并停止扫描表。
On the other hands, when the INoperator is combined with a subquery, MySQL must process the subquery first and then uses the result of the subquery to process the whole query.
另一方面，当IN运算符与子查询结合时，MySQL 必须先处理子查询，然后使用子查询的结果来处理整个查询。
The general rule of thumb is that if the subquery contains a large volume of data, the EXISTSoperator provides a better performance.
一般的经验法则是，如果子查询包含大量数据，则EXISTS运算符提供更好的性能。
However, the query that uses the INoperator will perform faster if the result set returned from the subquery is very small.
但是，如果从子查询返回的结果集非常小，则使用IN运算符的查询将执行得更快。
For detail explanations and examples: MySQL EXISTS - mysqltutorial.org
有关详细说明和示例：MySQL EXISTS - mysqltutorial.org
回答by medina
The second Method is faster because you've got this like there "WHERE t3.reservation_id = t.reservation_id". In the first case your subquery has to do a full scan into the table to verify the information. However at the 2o Method the subquery knows exactly what it is looking for and once it is found is checked the having condition then.
第二种方法更快，因为你有这样的“WHERE t3.reservation_id = t.reservation_id”。在第一种情况下，您的子查询必须对表进行全面扫描以验证信息。然而，在 2o 方法中，子查询确切地知道它在寻找什么，一旦找到它就会检查具有条件。
回答by kta
Their Official Documentation.SubQuery Optimization with Exists
他们的官方文档。使用 Exists 优化子查询
