EXISTS 比 COUNT(*)>0 更有效吗？
我使用的是 MySQL 5.1，并且有一个大致如下形式的查询：
select count(*) from mytable where a = "foo" and b = "bar";
在我的程序中，它唯一检查的是它是否为零或非零。如果我将其转换为：
select exists(select * from mytable where a = "foo" and b = "bar");
MySQL 是否足够聪明，可以在遇到第一个搜索时停止搜索？或者是否有其他方式与 MySQL 进行通信，我的目的只是找出是否有任何记录与此匹配，并且我不需要确切的计数？
I'm using MySQL 5.1, and I have a query that's roughly of the form:
select count(*) from mytable where a = "foo" and b = "bar";
In my program, the only thing that it checks is whether this is zero or nonzero. If I convert this into:
select exists(select * from mytable where a = "foo" and b = "bar");
is MySQL smart enough to stop searching when it hits the first one? Or is there some other way to communicate to MySQL that my intent is simply to find out if any records match this, and I don't need an exact count?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
是的，当使用 Exists 函数返回一行时，MySQL（实际上据我所知，所有数据库系统）都会停止处理。
您可以在 MySQL 文档中阅读更多内容：
如果子查询返回任何行， EXISTS 子查询为 TRUE。
Yes, MySQL (indeed all database systems as far as I'm aware) will stop processing when a row is returned when using an Exists function.
You can read more at the MySQL documentation:
If a subquery returns any rows at all, EXISTS subquery is TRUE.
我已经运行了 1000 个查询的测试。
SELECT EXISTS比SELECT COUNT快约 25%。将limit 1添加到SELECT COUNT没有任何区别。I have run a test with 1000 queries.
SELECT EXISTSwas about 25% faster thanSELECT COUNT. Addinglimit 1toSELECT COUNTdid not make any difference.这或许也是一种做法。
这不会遍历所有满足 where 条件的记录，而是在第一次“命中”后返回“1”。
缺点是您需要检查结果，因为返回的记录集可能为空。
This might be an approach too.
This would not traverce all records that meet the where condition but rather return '1' after first 'hit'.
The drawback is you need to check for result, because there might be empty record set in return.
最可靠的方法可能是
LIMIT 1，但这不是重点。假设您有一个类似 CREATE INDEX mytable_index_a_b ON mytable (a,b) 的索引，MySQL 应该足够智能，可以从索引返回计数，并且根本不触及任何行。 LIMIT 1 的好处可能可以忽略不计。
如果 (a,b) 上没有索引，那么性能将会很糟糕。 LIMIT 1 可能会让事情变得不那么可怕，但仍然会很糟糕。
The most reliable way is probably
LIMIT 1, but that's not the point.Provided you have an index like
CREATE INDEX mytable_index_a_b ON mytable (a,b), MySQL should be smart enough to return the count from the index and not touch any rows at all. The benefit of LIMIT 1 is probably negligible.If you don't have an index on (a,b), then performance will be terrible. LIMIT 1 may make it significantly less terrible, but it'll still be terrible.
我不知道这对于优化效果如何，但它的功能应该与
exists相同。例外的是，如果没有匹配，它将不返回任何行。I don't know how well this works for optimization, but it should work functionally the same as
exists. Exception being that it will return no row if there is no match.