是否可以加速 MySQL 中的 sum()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2178441/
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
Is it possible to speed up a sum() in MySQL?
提问by Teflon Ted
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?
我正在对 MySQL 数据库执行“select sum(foo) from bar”查询，该查询汇总了 7.3 毫米的记录，每次运行大约需要 22 秒。有没有加快 MySQL 求和的技巧？
回答by zombat
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.
不，你不能加速函数本身。这里的问题实际上是您要选择 730 万条记录。MySQL 必须扫描整个表，730 万是一个相当大的数字。实际上，它完成得如此之快，给我留下了深刻的印象。
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.
您可以采用的一种策略是将您的数据分解为更小的子集（可能按日期？月？）并为不会更改的旧数据维护一个总和。您可以定期更新总和，并且可以通过添加总和以及此后添加的任何新数据来计算总体值，这将是少得多的行数。
回答by Yada
Turn on QUERY CACHE in mysql. Caching is OFF by default. You need to set mysql ini file.
在mysql中开启QUERY CACHE。默认情况下缓存处于关闭状态。您需要设置 mysql ini 文件。
-- hint mysql server about caching
SELECT SQL_CACHE sum(foo) FROM bar;
MySQL optimizer may be able to return a cache if no changes were made to the table.
如果没有对表进行任何更改，MySQL 优化器可能能够返回缓存。
Read more here: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
在此处阅读更多信息：http: //www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
回答by Sergiy Tytarenko
Two things here:
这里有两件事：
1) You should not do sum for 7.3m records on regular basis - introduce staging tables serving business needs (by day, month, year, department, etc.) and fill them on scheduled basis, possibly reuse those tables instead of original 'raw' table (like select summarized value for each day when you need few days interval, etc.)
1) 您不应该定期对 730 万条记录进行汇总 - 引入满足业务需求的临时表（按天、月、年、部门等）并按计划填充它们，可能会重用这些表而不是原始的“原始” ' 表（例如，当您需要几天的间隔时，选择每天的汇总值等）
2) check your transaction settings
2) 检查您的交易设置
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read
http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read
回答by Phill
No, not really. It will always have to enumerate all the rows in the table.
不，不是真的。它总是必须枚举表中的所有行。
You could create a additional table and update the sum in there on every insert, update, delete?
您可以创建一个附加表并在每次插入、更新、删除时更新总和吗？
回答by hongliang
You can probably try adding an index on bar.foo field. The index will contain all values of bar column, but is smaller thus quicker to scan than the original foo table, especially if foo has a lot of other columns.
您可以尝试在 bar.foo 字段上添加索引。索引将包含 bar 列的所有值，但比原始 foo 表更小，因此扫描速度更快，特别是如果 foo 有很多其他列。
回答by Gnark
If your query is really that simple, no... but if your are using a more complex query (and abbreviated it here) you could (probably) - like using better joins...
如果您的查询真的那么简单，不...但如果您使用更复杂的查询（并在此处缩写），您可以（可能）-例如使用更好的连接...
