是否可以加速 MySQL 中的 sum()?
我正在对 MySQL 数据库执行select sum(foo) from bar"查询，该查询汇总了 7.3 毫米的记录，每次运行大约需要 22 秒.在 MySQL 中是否有加速求和的技巧?
I'm doing a "select sum(foo) from bar" query on a MySQL database that's summing up 7.3mm records and taking about 22 seconds per run. Is there a trick to speeding up sums in MySQL?
推荐答案
不，你不能加速函数本身.这里的问题实际上是您要选择 730 万条记录.MySQL 必须扫描整个表，730 万是一个相当大的数字.实际上，它完成得如此之快给我留下了深刻的印象.
No, you can't speed up the function itself. The problem here is really that you're selecting 7.3 million records. MySQL has to scan the entire table, and 7.3 million is a pretty big number. I'm impressed that it finishes that fast, actually.
您可以采用的一种策略是将您的数据分解为更小的子集(可能按日期?月?)并为不会更改的旧数据维护一个总和.您可以定期更新总和，可以通过将总和以及此后添加的任何新数据(行数少得多)相加来计算总体值.
A strategy you could employ would be to break your data into smaller subsets (perhaps by date? Month?) and maintain a total sum for old data that's not going to change. You could periodically update the sum, and the overall value could be calculated by adding the sum, and any new data that's been added since then, which will be a much smaller number of rows.
相关文章