mysql - 如何设计一个架构来处理定期批量插入/更新？

Question

(tldr；我认为定期更新会强制表使用自然键。因此我必须迁移我的数据库架构。)

我有一个生产数据库，其中有一个像planets这样的表，尽管它具有良好的潜在自然键(例如，从未真正改变的行星名称)，但使用典型的递增整数作为主键。行星表有一个或两个自引用列，例如 *parent_planet_id*。

现在我正在构建基于离线云的工作人员，每周重新创建行星记录的子集，并且它们需要与主服务器集成。我的计划是:

• 工作实例具有迷你版本的数据库(相同架构，但没有行星记录)
• 工作线程每周启动一次，进行所有处理，创建大约 100,000 个行星记录，并导出数据。 (我认为导出格式对于这个特定问题并不重要:可能是 mysqldump、yaml 等)
• 然后，生产服务器导入记录:一些是新记录，大部分是更新记录。

这最后一步是我不知道如何解决的。我并不是每次都完全替换 Planets 表，所以问题是两个数据库都有自己的递增整数 PK。所以我不能只进行简单的导入。

我考虑过在没有 id 列的情况下导出，但后来我意识到自引用列会阻止这种情况。

我看到两种可能的解决方案:

• 重新设计架构以对行星表使用自然键。这会很痛苦。
• 使用 UUID 而不是 key 的递增整数。我想，搬到那里会更容易。 ID 是唯一的，并且可以安全地导入新行。这也避免了依赖键中的自然数据的问题。

Answer 1

修改Planets以使用替代层次结构技术，例如 nested sets 、闭包表或路径枚举然后导出。这将打破 ID 依赖性。

或者，如果您仍然不喜欢这个想法，请将导出和导入视为 ETL 问题。

• 在导出过程中修改记录以包含 PlanetName、ParentPlanetName
• 首先导入所有行星 (PlanetNames)
• 然后导入层次结构 (ParentPlanetName)

无论如何，第一个数据库中的代理键永远不应该离开该数据库——它在数据库之外没有任何意义。