使用 MySQL 从详细记录创建摘要记录的最佳方法是什么？
我一天可能有 10 到 2000 万条详细记录（统计和性能数据），必须将其读入并汇总为 24 小时汇总记录和 1 条每日汇总记录。
该过程计算多个字段的平均值，获取其他字段的最大值和最小值，与 CPU 无关。
是否更好：
A）在记录进入时将详细记录汇总到汇总记录中，稍微延迟每个详细记录的插入？我假设汇总表中会有很多锁定（选择更新等），因为有几个不同的系统导入数据。
B）等到一个小时结束，然后选择整个前几个小时的数据并创建汇总记录？用户看到统计数据会有一定的延迟，但在此期间可以查看详细记录。
也许还有其他方法？
I have maybe 10 to 20 million detail records coming in a day (statistical and performance data), that must be read in, and summarized into 24 hourly and 1 daily summary records.
The process calculates averages on several fields, gets the max and min values of others, nothing significant CPU wise.
Is it better to:
A) summarize the detail records into the summary records while the records are coming in, delaying each detail record insert slightly? I assume there will be a lot of locking (select for update's etc) in the summary tables, as there are several different systems importing data.
B) wait until the hour is over, and then select the entire previous hours data and create the summary records? There would be a delay for users to see the statistics, however the detail records would be available during the time.
Perhaps there are alternative methods to this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只需为汇总表创建
view即可。您的所有插入将照常工作。只需根据您的需要提出意见作为总结即可。这将自动更新主表。您还可以进行 24 小时和 1 天的汇总。视图是存储的查询，调用时会生成结果集。视图充当虚拟表。
有关视图的更多详细信息，请参阅：http://dev.mysql。 com/doc/refman/5.0/en/create-view.html
如果您需要有关
mysql 视图的进一步帮助，请告诉我。Just make
viewfor summary tables. Your all insert will work as usual. Just make views according to your need as summary. That will update automatically with main tables.Also you can make the 24 hourly and 1 daily summary basis. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.
For more details about views refer : http://dev.mysql.com/doc/refman/5.0/en/create-view.html
Let me know if you want further assistance regarding
mysql views.这取决于运行单个更新所需的负载，但我可能会进行单独的摘要运行。我可能会打赌，一次更新所花费的时间比每次插入的累积时间要短。
It'd depend on the load required to run the single update, but I'd probably go with a separate summary run. I'd probably put a small bet on saying that a single update would take a shorter amount of time than the cumulative on-every-insert idea.