以最有效的方式获取带有JDBC的MySQL查询返回的结果计数 [英] Getting the count of results returned by a MySQL Query with JDBC in the most performance efficient way
问题描述
The accepted way of getting the number of results from a JDBC result seems to be, to do resultSet.last(), and then resultSet.getRow(), according to this answer. But, in that answer, the author also says:
这可能不是一个好主意，因为它可能意味着阅读整个表格 通过网络丢弃数据.从中执行SELECT COUNT(*) ...而是查询.
it may not be a good idea as it can mean reading the entire table over the network and throwing away the data. Do a SELECT COUNT(*) FROM ... query instead.
我正在寻找一个明确的答案.从性能角度来看，最好单独进行一次COUNT(*)查询以获取结果数，还是先执行resultSet.last()和resultSet.getRow()，然后再执行resultSet.first()更好?
I'm looking for a definite answer on this. Performance wise, will it be better to do a separate COUNT(*) query to get the number of results, or will it be better to do resultSet.last() and resultSet.getRow(), followed by resultSet.first() again?
如果ResultSet已经获取了结果并将其保存在内存中，那么毫无疑问，最好只是执行last()和getRow()，因为(我认为)它将在内存中循环结果.但是上述OP的答案似乎暗示它会按照要求从数据库中延迟加载结果.
If the ResultSet has already fetched the results and is holding them in memory, then it'd undoubtedly be better just to do last() and getRow() as (I assume) it would just loop over the results in the memory. But the OP's answer above seems to imply that it lazy loads the results from the db as they're requested.
推荐答案
根据我的说法，这取决于您的用例.如果resultset足够小，则性能差异可以忽略不计.我非常怀疑resultset是否在较大结果集的主存储器中获取整个结果.其次，这是链接关于这种特殊情况.在这种情况下，使用last()，getrows()后跟first()来计算大小显然是效率低下的，因为它必须首先将resultset的一部分加载到内存中并执行这些操作(并且不要忘记网络传输时间)以计算净尺寸.另一方面，count(*)只会进入并计算结果集中的行.
According to me it depends on your use case. If you resultset is small enough then the performance difference would be negligible. I highly doubt that the resultset fetches the whole result in the main memory for larger resultsets. To second this here is the link that talks about this particular scenario. In such case calculating size using last() , getrows() followed by first() would be obviously inefficient as it has to first load the portion of resultset in memory and perform these operations(and don't forget the network transfer time) to calculate the net size. On the other hand count(*) would just go in and count the rows in your result set.
这是我对哪个执行其他操作的理解.我愿意接受别人的任何意见
这篇关于以最有效的方式获取带有JDBC的MySQL查询返回的结果计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
