SQL 将 SELECT COUNT 优化为 EXISTS
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5031471/
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
Optimizing SELECT COUNT to EXISTS
提问by Carlo V. Dango
I have a query to find certain customers from a table.
我有一个查询要从表中查找某些客户。
SELECT COUNT(*)
FROM CUSTOMER
WHERE amount <> 0
AND customerid = 22
There is an index on customerid, so the DB scans all rows with customerid = 22.
customerid 上有一个索引，因此 DB 扫描 customerid = 22 的所有行。
Since the result is processed by checking whether the count returns zero or more than zero, how can I optimize the query? I.e. such that at the first customer row with amount <> 0 the query returns 0 else if all rows are = 0, then return 1.
由于通过检查计数返回零还是大于零来处理结果，我该如何优化查询？即，在金额 <> 0 的第一个客户行，查询返回 0，否则如果所有行都 = 0，则返回 1。
回答by Martin Smith
select case
when exists (select *
from customer
where amount <> 0
and customerid = 22) then 1
else 0
end as non_zero_exists
回答by Chris Shain
First index on customerid and amount
客户 ID 和金额的第一个索引
CREATE INDEX customer_idx ON customer(customerid, amount);
then rewrite your query as
然后将您的查询重写为
IF EXISTS (SELECT customerid
FROM customer
WHERE amount > 0 -- I am assuming here that amount cannot be a negative number.
AND customerid = 22)
SELECT 1
ELSE
SELECT 0
This should result in an index seek on customer_idx. Otherwise you'll need to scan all rows for that customer (which your question seems to imply could be a lot).
这应该会导致在 customer_idx 上进行索引查找。否则，您将需要扫描该客户的所有行（您的问题似乎暗示了很多）。
回答by Conrad Frix
Seems straight forward enough
看起来很直接
IF EXISTS ( SELECT customerid
FROM customer
WHERE amount <> 0
and customerid = 22))
SELECT 1
ELSE
SELECT 0
回答by RichardTheKiwi
An alternative to EXISTS
EXISTS的替代品
select ISNULL((select TOP 1 1
from customer
where amount <> 0
and customerid = 22),0)
I already assumed that you will have an index on (customerid) or better (customerid,amount).
我已经假设您将拥有 (customerid) 或更好 (customerid,amount) 的索引。
