MySQL-归档数据，使用哪种解决方案? [英] Mysql - Archiving data, which solution to use?

问题描述

我正在使用MySQL 5.x，并且有一个主表包含所有客户的统计信息.我想在其上运行报告，但不想在同一表上运行它，因此我想每天晚上将数据移动到仅用于报告的另一张表上.我的问题是，对于每个客户也要有一个单独的表进行归档，或者对所有客户来说只有一个归档表，这会有所益处吗?如果我决定按客户分类，系统中可能有成千上万的客户，这可能意味着成千上万的存档表.你有什么想法吗?

I am using MySQL 5.x and there is one main table that has the stats combined for all customers. I would like to run reporting on it but dont want to run it on the same table so Im thinking of every night moving the data to a different table that will only be used for reporting. My question is would it be beneficial to have a seperate table for each customer to archive too or just have it just one archive table for all customers? There could be thousands of customers in the system which could mean thousands of archive tables if I decide to break it up by customer. Your thoughts?

推荐答案

如果您为每个客户使用个人，表的数量就会增加.

If you use the individual for each customer, tables will grow in number.

如果您有统计数据，那么我建议将其总结如下

If you have statistics data then i suggest to summarize it like below

1. 将最近1个月的数据保留在同一主表中.
2. maintable_quartely表中的四分之一数据/半年数据，用于根据报告要求保存平均值或汇总的数据
3. 在maintable_archieve中保留旧数据，该旧数据包含根据报告要求平均或汇总的四分之一或半年数据

如果您希望将整个内容放在一起，则需要合并三个表.

If you want the whole thing together you need to combine three tables.

另一种方法，使用mysql复制和master进行插入，更新，删除和slave进行选择

other way, use mysql replication and master for insert,update,delete and slave for select

这篇关于MySQL-归档数据，使用哪种解决方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！