COUNT比拉记录和计数代码快吗? [英] Is COUNT faster than pulling the records and counting in code?
问题描述
这里是情况:
-
我首先需要运行查询以了解存在多少记录.
I first need to run a query to know how many records exist.
例如:SELECT COUNT(DISTINCT userid) from users;
通常这就是所需要的.但是，有时(例如30％的时间)在第一个查询之后，用户将需要运行第二个查询，详细记录.
Often this will be all that's needed. However, sometimes (say 30% of the time) following the first query, the user will want to run a second query, detailing the records.
例如:SELECT * FROM users;
是否有任何理由最初运行SELECT COUNT而不是仅仅运行SELECT?也就是说，使SQL中的记录计数比实际拉回记录更快吗?还是无论哪种方式本质上都是相同的工作，所以我应该避免做两个查询?
Is there any reason to run SELECT COUNT initially instead of just SELECT? That is, is making the count of records in SQL faster than actually pulling the records back? Or is it doing essentially the same work either way and so I should avoid doing two queries?
换句话说，最好总是只在第一个查询中提取记录(不使用COUNT)，然后对代码(Java)中的记录进行计数.如果用户想运行第二个查询，那么很好，我已经有了数据.如果没有，那么就将其丢弃.
In other words, is it better to just always pull the records in the first query (not use COUNT), then count the records in code (Java). If the user wants to run the second query, then great, I already have the data. If not, then just dump it.
这里的最佳做法是什么?
What's the best practice here?
推荐答案
如果您知道需要数据，请继续进行提取，并在代码中进行计数.但是，如果只需要计数，则从数据库中提取计数要比实际检索行快得多.同样的标准做法是只拉您所需的东西.
If you know you need the data, go ahead and pull it and count it in code. However, if you only need the count, it is significantly faster to pull the count from the database than it is to actually retrieve rows. Also it is standard practice to only pull what you need.
例如，如果要计算表中的所有行，则大多数数据库实现都不需要查看任何行.表知道它们有多少行.如果查询在where子句中具有过滤器，并且可以使用索引，则它不再需要查看实际行的数据，只需对索引中的行进行计数即可.
For instance, if you are counting all the rows in a table, most database implementations do not need to look at any rows. Tables know how many rows they have. If the query has filters in the where clause and it can use an index, it again will not need to look at the actual rows' data, just counts the rows from the index.
所有这些都没有计算出传输的数据量较少.
And all this is not counting the less data transferred.
关于数据库速度的经验法则是继续尝试并自己尝试一下.一般规则并不总是一个好的指标.例如，如果表只有10行，只有几列，那么我可能会在需要时立即将整个事情拖过来，因为两次数据库往返将超过查询的成本.
A rule of thumb about database speeds is go ahead and try it for yourself. General rules are not always a good indicator. For instance, if the table was 10 rows and only a few columns, I might just pull the whole thing anyway on the off chance I needed it, since 2 round trips to the database would outweigh the cost of the query.
这篇关于COUNT比拉记录和计数代码快吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
