是否可以在 MySQL 中加快 sum() 的速度?
我正在对 MySQL 数据库执行从 bar 中选择 sum(foo)"查询，该数据库汇总了 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.
相关文章