在 Amazon Aurora MySQL 或 Amazon RDS for MySQL 中处理无主键的表以实现与 Amazon Redshift 的零

ETL 整合

作者 ：Rohit Vashishtha, Abhinav Dhandh, Kanishka Chaturvedi, SzymonKomendera, Stas Bogachinsky
日期 ：2024年4月18日
来源 ： 浏览更多 文章。

关键要点

• 本文探讨如何在创建 Amazon Aurora MySQL 或 Amazon RDS for MySQL 的零 ETL 整合时处理没有主键的表。
• 主键是表中唯一标识一行的属性，它可以是一个或多个非空列的组合。
• 如果表中没有主键，可能会导致零 ETL 整合遇到问题，您需要显式定义主键以进行整合。
• 提供了几种方法来为现有表添加主键，包括使用 ALTER TABLE 语句和使用架构变更工具。
• 在处理大型表的更改时请求低影响的替代方案。

在 AWS，我们一直在朝着实现我们的稳步前进。通过，您可以将的事务数据与的分析能力结合起来。该整合有助于您在多个应用程序中获得整体见解，打破组织中的数据孤岛，并实现显著的成本节约和运营效率。在 2023 年的上，AWS 宣布了四个新的零 ETL整合，以加快和简化跨数据存储的数据访问和分析。

如今，各行业的客户希望通过实施近实时分析用例来增加收入和客户互动，这些用例包括个性化策略、欺诈检测、库存监控等。零 ETL 整合使您能够解锁所有此类用例。

在撰写本文时，通过的零 ETL 整合已普遍可用，而和处于公共预览阶段，而则处于私有预览阶段。更多信息请参见。

在本文中，我们将向您展示在创建 Amazon Aurora MySQL 或 Amazon RDS for MySQL 的零 ETL整合时如何处理没有主键的表。有关 Aurora PostgreSQL 的类似策略，请参阅。

零 ETL 和 MySQL 中的主键

主键是唯一标识表中一条记录（行）的属性。它由一个或多个列的组合定义，其中没有可为 NULL 的列，而且列值的组合在表中是唯一的。在 MySQL InnoDB中，主键通常对应于，这是一种树形组织结构，存储行数据。

InnoDB 选择主键的方式有三种：

• 可以在表定义中显式声明主键
• 如果没有显式定义主键，InnoDB 将选择第一个所有列都定义为 NOT NULL 的 UNIQUE 索引
• 如果没有定义主键且表没有合适的 UNIQUE 索引，InnoDB 将使用一个 6 字节的单调递增数字创建一个隐藏的内部列

零 ETL 整合与 Amazon Redshift 依赖于主键来跟踪行更改。没有主键的表可以在零 ETL整合中被，如果您不希望将其复制到 Amazon Redshift 中。然而，如果您希望使用某个表进行零 ETL，表必须具有显式定义的主键，如第一种选择中所述。

在本文中，我们将指导您为表选择主键并修改（更改）表以添加主键。但首先，让我们讨论您在活动零 ETL整合中可能会观察到的症状，这些整合【会遇到无主键表】（https://docs.aws.amazon.com/redshift/latest/mgmt/zero- etl-using.troubleshooting.html#zero-etl-using.troubleshooting.primary-key）。

要查看具有整合问题的表，请在 Amazon Redshift 控制台中的整合详细信息页面上转到 表统计 选项卡，或使用

系统视图。您会注意到整合状态下显示“需要注意”的消息，以及一个或多个表的状态为 失败 。

删除)

您还会在“原因”字段中找到错误原因，这应该指向表中缺少主键。

发现表需要主键后，您可以决定是基于现有表列创建一个，还是添加新列作为主键。

如果您不希望没有主键的表使用零 ETL 整合进行复制，您可以使用零 ETL 整合向导中的 功能，过滤掉没有主键的源表。

选择主键

如前所述，主键是一个或多个列的组合，其值是唯一且不可为 NULL 的。一些表可能已经包含符合此定义的列。当您根据现有表列定义主键时，这种主键被称为 自然键 。例如，在存储驾驶执照信息的表中，驾驶执照 ID 很可能是唯一且始终存在的值（从不为空）。

您还可以在具有人工值的列上定义主键，这些值可以唯一标识行，但并非源于现实世界数据。在 MySQL 中，这种主键通常被称为 合成主键 或 替代主键 。数字自动递增列是合成主键最常见的示例。

选择自然主键和合成主键之间的选择涉及性能和效率的权衡。以下是选择主键时主要的考虑因素：

• 聚集索引是一个存储行数据的树形结构，按主键值排序。因此，主键的选择决定了新行在该结构中存储的位置。无序的主键（或随机排序的主键，如 UUID 版本 4）可能导致表页的访问模式更随机，从而可能导致性能降低。它还可能加剧某些类型的页面争用，例如，当 InnoDB 必须拆分现有页面以为新行腾出空间时。
• 表中每个二级索引的每一行条目都包含主键值的副本。较长的主键会导致二级索引结构中出现过多冗余，从而降低存储效率，并在对表进行写入时显著影响性能。
• 更改行的主键值意味着需要将行从一个数据库页面物理移动到另一个数据库页面，这需要比更改非主键列值更多的工作。因此，具有频繁更改值的列通常不是理想的主键选择。

换句话说，主键不仅仅是与表旁边独立存在的构造。它是表结构的一个重要组成部分，直接影响写入性能和存储效率。掌握这一知识后，您可以在自然主键和合成主键之间进行选择。一般来说，选择应优先考虑效率和性能，即使这意味着添加一个新列。在设计以实现最大效率时，请记住某些值类型可以以多种格式编码。例如，UUID值通常以人可读的文本表示，但 UUID 本质上只是一个数字。文本编码的值将非常长且低效，而以 BINARY 数据类型存储的相同值将占用更少的空间。

在不确定的情况下，自动递增的数字列应很好地作为主键，在大多数情况下提供低开销和充足的性能。在创建自动递增主键时，使用 BIGINT 数据类型（8字节）而不是 INT（4 字节）。额外的 4 字节存储是一小部分开销，但能避免值耗尽的问题。

为表添加主键

在前面的部分中，我们已经建立了每个参与零 ETL 整合的表都必须定义主键，并解释了选择主键的最佳实践。添加主键到表中最优的方法是解决最后一块拼图。

在大型表下进行写入密集型负载时，添加或修改主键并不是一项简单的任务。有三种主要方法可以更改表的结构：

• 使用 DDL 语句直接修改表。
• 使用架构变更工具，例如 、 或 。架构变更工具通常通过创建新表，迁移数据和任何进行中的更改，然后交换表名来工作。
• 开发自定义解决方案，针对您的特定情况进行调整和优化。

通过使用 ALTER TABLE 语句，您请求 InnoDB重构聚集索引，因此将表数据重写为新主键的顺序。这是一个资源密集型的操作，在大型表上可能需要数小时才能完成。InnoDB支持，这意味着在更改的整个过程中，表不会被完全锁定。然而，在线 DDL操作仍然涉及需要独占表锁的阶段，可能会导致查询停滞一段难以预测的时间。基于 ALTER的方法对于非常小的表、没有活跃流量的表或临界表变更的临时工作负担影响可接受时可以是一个不错的选择。

架构变更工具在架构修改挑战中是常见的解决方案，广泛用于无法容忍就地 ALTER影响的数据库。这类工具的主要前提是避免就地修改表，而是使用可以用现有数据填充的新表并与进行中的更改进行同步。在新表完全填充后，该工具通常使用 RENAME语句交换表。

关于数据迁移和变更同步，具体的实现细节因工具而异。例如， 使用触发器来同步正在进行的更改，而 和

则通过二进制日志提取更改记录。每个工具都有自己的配置设置，实施通常反映了特定的性能改善和减少工作负担影响的重点。如果您计划使用架构变更工具，请务必选择与您的优先事项一致的工具。

最后，如果上述选项都不足以满足您的需求，可以设计和开发自定义架构变更工具。自定义工具必须支持以下四个主要任务：

1. 创建具有所需结构的新表。
2. 将数据从现有表迁移到新表。
3. 同步步骤 2 中由活动工作负载引入的表更改。
4. 原子切换，从旧表切换到新表，通常使用 RENAME 语句。

在开发自定义工具时，以下目标至关重要：

• 控制架构更改对工作负担性能的影响 - 这一考虑在步骤 2 和 3 中起着关键作用，这两个阶段是资源密集型和对性能敏感的过程。常见的方法是分批迁移数据，并在批次间设置可配置的批量大小和休眠时间，以控制你的架构迁移相对于其他工作负载的优先性。您可以手动设置批量大小和休眠参数，或实施逻辑以根据关键数据库指标（例如查询延迟或 InnoDB 行锁定时间）实时调整这些参数。
• 提供持续的完全同步正在进行的更改 - 这是步骤 3 中的关键考虑。在这里，您的实施决策主要会受到性能期望的驱动。接收每分钟少量简单更新的表可以承担基于触发器的简单同步机制，而处理每秒数十万次写入的繁忙表可能需要不同的方法。
• 尽量避免粗锁 - 您可能无法完全避免锁定，因为即使是 RENAME 命令也需要短暂的元数据锁定。
• 在过程的每个步骤提供崩溃安全性 - 这确保中断的架构更改不会使表无法使用。这在切换步骤中是一个关键考虑。
• 能够停止并恢复架构更改 - 这可能很有价值，特别是对于可能需要几天时间才能应用更改的低影响工具。

通过逻辑复制减少影响

繁忙的 MySQL数据库常常使用二进制日志复制进行大的架构更改。在这种方法中，在主数据库和副本之间处理数据同步。架构更改在副本上执行，从而使主数据库免受其影响，尽管您仍需在过程结束时执行切换。

根据您的架构更改的范围，复制兼容性可能成为一个问题。有关详细信息，请参见 MySQL参考手册中的。

设置基于复制的解决方案的确切步骤将依赖于您的数据库解决方案。对于 RDS for MySQL数据库实例，您可以使用服务提供的只读副本。架构更改后，可以将副本提升为独立实例，并重新配置您的应用程序以使用该副本。

对于 Amazon Aurora MySQL 或您不想使用内置只读副本的 Amazon RDS for MySQL 用例，您可以按照以下步骤进行：

1. 在 RDS for MySQL 数据库实例或 Aurora MySQL 集群上启用二进制日志：
2. 在 Amazon RDS for MySQL 中，通过启用自动备份来启动二进制日志。
3. 在 Amazon Aurora MySQL 中，在自定义 DB 集群参数组中使用 参数。
4. 使用 存储过程配置二进制日志保留。选择一个保留设置，以使您有足够的时间完成其余设置步骤。
5. 创建数据库实例或集群的副本：
6. 在 Amazon RDS for MySQL 中，您可以从最近的快照还原数据库，或使用。
7. 在 Amazon Aurora MySQL 中，您可以。
8. 使用针对 或 的复制存