java 计算查询行数的最有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3358843/
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
Most efficient way to count rows of a query
提问by digiarnie
I'm using Hibernate to retrieve the number of rows for a specific query. Let's say I have a table called 'Person' with various columns. One of those columns is 'name'.
我正在使用 Hibernate 来检索特定查询的行数。假设我有一个名为“Person”的表，其中包含多个列。其中一列是“名称”。
If I wanted to get the number of people with the name of 'Andrew', which of these ways would be most efficient? Assuming there is a performance difference between some/all of them. Is there a better way to do this using Hibernate/SQL?
如果我想获得名为“Andrew”的人数，以下哪种方式最有效？假设其中一些/全部之间存在性能差异。有没有更好的方法来使用 Hibernate/SQL 做到这一点？
(1) Select all columns
(1) 选择所有列
Query query = session.createQuery("from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();
(2) Select just the name column
(2) 只选择名称列
Query query = session.createQuery("select name from Person where name= :name");
query.setParameter("name", name);
List result = query.list();
int count = result.size();
(3) Using Count in the query
(3)在查询中使用Count
Query query = session.createQuery("select count(*) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();
(4) Using Count with the name column in the query
(4) 使用Count与查询中的name列
Query query = session.createQuery("select count(name) from Person where name= :name");
query.setParameter("name", name);
long count = (Long) query.uniqueResult();
Edit: Sorry, I had two number 3's in my list
编辑：抱歉，我的列表中有两个数字 3
回答by Pascal Thivent
Don't retrieve a result set if you just want to count the number of rows, this just means useless overhead:
如果您只想计算行数，请不要检索结果集，这意味着无用的开销：
- you'll get more stuff than actually wanted (whether you're selecting all columns or just one)
- you'll need to send them over the wire
- you'll need to create instances (whether it's a full
Personentity or just aString) for nothing.
- 你会得到比实际想要的更多的东西（无论你是选择所有列还是只选择一列）
- 你需要通过电线发送它们
- 您需要创建实例（无论是完整
Person实体还是只是一个String）。
In other words, if you only want to count, don't do it on the Java side, DBMS are optimized for this task and will do a much better job.
换句话说，如果你只想数数，不要在Java端做，DBMS针对这个任务做了优化，会做得更好。
This excludes (1) and (2).
这不包括（1）和（2）。
Regarding (3) and (4), note that there is a difference between count(*)and count(col)in general:
关于（3）和（4）中，请注意，有之间的差count(*)和count(col)在一般：
count(*)counts ALLrowscount(col)counts rows with non-nullvalues ofcol
count(*)计算所有行count(col)计算具有非空值的行col
So they will give different results in performance and query result if colcan be NULL (the count(*)being faster), otherwise identical performance.
因此，如果col可以为 NULL（count(*)更快），它们将在性能和查询结果上给出不同的结果，否则性能相同。
I'd use (3).
我会使用（3）。
Similar questions
类似问题
回答by Alain O'Dea
The count(*)method has profiled to be significantly faster than the size()method for my company. It is certainly more memory efficient since you aren't pulling across column data that you won't use. I don't know if count(name)makes a difference.
该count(*)方法已被描述为比size()我公司的方法快得多。它肯定会更有效地使用内存，因为您不会跨不会使用的列数据。不知道有没有count(name)区别
回答by C???
The less you put inside the COUNT()function the better. If you don't need any of the information from the table, I would say use COUNT(1). You can use COUNT(name)or COUNT(*)as long as your tables are properly indexed.
在COUNT()函数中放入的越少越好。如果您不需要表中的任何信息，我会说使用COUNT(1). 只要您的表被正确索引，您就可以使用COUNT(name)或COUNT(*)。
