更新实时数据库表的好策略是什么？
我有一个数据库表，它会定期用新数据完全重新填充。然后需要将该数据推送到相应的实时数据库表中，覆盖之前的实时数据。
随着表大小的增加，将数据推送到实时表所需的时间也会增加，并且应用程序看起来会丢失数据。
一种解决方案是将新数据推送到 live_temp 表中，然后对此表运行 SQL RENAME 命令以将其重命名为活动表。重命名通常在亚秒内完成。这是解决这个问题的“正确”方法吗？
还有其他策略或工具来解决这个问题吗？谢谢。
I have a db table that gets entirely re-populated with fresh data periodically. This data needs to be then pushed into a corresponding live db table, overwriting the previous live data.
As the table size increases, the time required to push the data into the live table also increases, and the app would look like its missing data.
One solution is to push the new data into a live_temp table and then run an SQL RENAME command on this table to rename it as the live table. The rename usually runs in sub-second time. Is this the "right" way to solve this problem?
Are there other strategies or tools to tackle this problem? Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不喜欢以这种方式弄乱架构对象 - 它可能会混淆查询优化器，而且我不知道执行重命名时正在进行的任何事务会发生什么。
我更喜欢在表中添加版本列，并有一个单独的表来保存当前版本。
这样，客户端代码就变成了
这也保留了历史记录——这可能有用，也可能没用；如果设置 CurrentVersion 列后不删除旧记录。
I don't like messing with schema objects in this way - it can confuse query optimizers and I have no idea what will happen to any transactions that are going on while you execute the rename.
I much prefer to add a version column to the table, and have a separate table to hold the current version.
That way, the client code becomes
This also keeps history around - which may or not be useful; if it's not delete old records after setting the CurrentVersion column.
创建一个重复表 - 精确副本。
创建一个新表，其作用只是跟踪“最新”表。
最近（表）
id（列）- 保存保存“最新”数据的表的名称。
重新填充时，填充旧表并更新 MostCurrent.id 以反映该表。
现在，在将数据绑定到页面的应用程序中，绑定最新的表。
Create a duplicate table - exact copy.
Create a new table that does nothing more than keep track of the "up to date" table.
MostCurrent (table)
id (column) - holds name of table holding the "up to date" data.
When repopulating, populate the older table and update MostCurrent.id to reflect this table.
Now, in your app where you bind the data to the page, bind the newest table.
仅将更改推送到实时数据库表是否合适？对于我使用过的大多数应用程序来说，变化很小。您应该能够在单个事务中应用所有更改。提交事务将使它们可见，并且表上不会出现中断。
如果数据确实完全更改，那么您可以配置数据库，以便可以在单个事务中替换所有数据。
Would it be appropriate to only push changes to the live db table? For most applications I have worked with changes have been minimal. You should be able to apply all the changes in a single transaction. Committing the transaction will make them visible with no outage on the table.
If the data does change entirely, then you could configure the database so that you can replace all the data in a single transaction.