I am trying to rotate out an innoDB table which has a high number of transaction, so I need to lock all the tables involved.
我试图旋转出一个具有大量事务的innoDB表,所以我需要锁定所有涉及的表。
With the tables locked I cannot use RENAME to move data around using auxiliary tables.
锁定表后,我无法使用RENAME使用辅助表移动数据。
And if I do an INSERT, then I have to worry about the integrity of the ids.
如果我执行INSERT,那么我必须担心ID的完整性。
Any suggestions? Is this even possible
有什么建议?这甚至是可能的吗?
1 个解决方案
#1
Can you do the rotation in the log? Have 7 tables - one for each day of the week - and have your queries to take the current day into account, or even better, wrap the insert with a stored procedure. Same goes for 24 tables for hourly rotation, 12 for monthly rotation, etc.
你能在日志中进行轮换吗?有7个表 - 一周中的每一天一个 - 并且让您的查询考虑当前的一天,甚至更好,用存储过程包装插入。同样适用于每小时轮换24个表,每月轮换12个等。
#1
Can you do the rotation in the log? Have 7 tables - one for each day of the week - and have your queries to take the current day into account, or even better, wrap the insert with a stored procedure. Same goes for 24 tables for hourly rotation, 12 for monthly rotation, etc.
你能在日志中进行轮换吗?有7个表 - 一周中的每一天一个 - 并且让您的查询考虑当前的一天,甚至更好,用存储过程包装插入。同样适用于每小时轮换24个表,每月轮换12个等。