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进行insert、update、delete和slave进行select

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

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