Amazon Relational Database Service
用户指南 (API 版本 2014-10-31)
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 Amazon AWS 入门

Amazon Aurora MySQL 最佳实践

本主题包含有关使用 Amazon Aurora MySQL 数据库集群或向其迁移数据的最佳实践和选项的信息。

确定您连接到的数据库实例

您可以通过检查 innodb_read_only 全局变量,确定连接到 Aurora MySQL 数据库集群中的哪个数据库实例,如下面的示例所示。

SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

如果连接到 Aurora 副本,innodb_read_only 变量将设置为 ON;如果连接到主实例,该变量将设置为 OFF

如果要在应用程序代码中添加逻辑以平衡工作负载或确保写入操作使用正确的连接,该方法可能是非常有用的。

使用 T2 实例

使用 db.t2.smalldb.t2.medium 数据库实例类的 Amazon Aurora MySQL 实例最适用于不支持长时间运行高工作负载的应用程序。T2 实例旨在提供适度的基准性能,并能够根据您工作负载的需要实现性能的显著突增。它们旨在用于不经常或不持续使用完整 CPU、但偶尔需要突增性能的工作负载。db.t2.smalldb.t2.medium 数据库实例类最适用于开发和测试服务器,或其他非生产服务器。有关 T2 实例的更多详细信息,请参阅 T2 实例

不应在 Amazon Aurora MySQL T2 实例上启用 MySQL 性能架构。如果启用了性能架构,T2 实例可能会出现内存不足的情况。

在将 T2 实例用于 Aurora MySQL 数据库集群中的主实例或 Aurora 副本时,我们建议您采取以下措施:

  • 如果您将 T2 实例用作数据库集群中的数据库实例类,建议您的数据库集群中的所有实例都使用同一数据库实例类。例如,如果您使用 db.t2.medium 作为主实例,我们建议您的 Aurora 副本也使用 db.t2.medium

  • 监视 CPU 积分余额 (CPUCreditBalance) 以确保其处于可持续的水平。也就是说,CPU 积分将在使用 CPU 时按相同的费率累积。

    如果您用完实例的 CPU 积分,则会发现可用 CPU 立即下降,并且实例的读取和写入延迟将会增加。这种情况导致实例的总体性能大大降低。

    如果您的 CPU 积分余额未处于可持续的水平,建议您修改数据库实例以使用支持的 R3 数据库实例类之一 (扩展计算)。

    有关监视指标的更多信息,请参阅监控 Amazon Aurora 数据库集群

  • 监视 Aurora MySQL 数据库集群中的主实例与 Aurora 副本之间的副本滞后 (AuroraReplicaLag)。

    如果 Aurora 副本在主实例之前用完 CPU 积分,滞后于主实例将导致 Aurora 副本频繁重新启动。如果应用程序在 Aurora MySQL 数据库集群中的 Aurora 副本之间分配较高的读取操作负载,同时主实例具有非常低的写入操作负载,则通常会出现这种情况。

    如果您发现副本滞后持续增加,请确保数据库集群中的 Aurora 副本的 CPU 积分余额未被用完。

    如果您的 CPU 积分余额未处于可持续的水平,我们建议您修改数据库实例以使用支持的 R3 数据库实例类之一 (扩展计算)。

  • 对于已启用二进制日志记录的数据库集群,将每事务的插入次数保持在 100 万以下。

    如果数据库集群的数据库集群参数组将 binlog_format 参数设置为 OFF 以外的值,并且数据库集群收到的事务包含超过 100 万个要插入的行,数据库集群可能会出现内存不足的情况。您可以监控可释放内存 (FreeableMemory) 指标以确定数据库集群是否用完可用的内存。然后,您可以检查写入操作 (VolumeWriteIOPS) 指标以确定主实例是否收到较高的写入操作负载。如果出现这种情况,我们建议您更新应用程序以将事务中的插入数量限制为少于 100 万个。或者,您也可以修改实例以使用支持的 R3 数据库实例类之一 (扩展计算)。

调用 AWS Lambda 函数

如果使用 Amazon Aurora 1.16 或更高版本,我们建议您使用本机函数 lambda_synclambda_async 以调用 Lambda 函数。

如果使用已弃用的 mysql.lambda_async 过程,我们建议您将对 mysql.lambda_async 过程的调用封装在一个存储过程中。您可以从不同的来源调用该存储过程,例如,触发器或客户端代码。这种方法可以帮助您避免出现阻抗不一致问题,并使数据库编程人员更轻松地调用 Lambda 函数。

有关从 Amazon Aurora 调用 Lambda 函数的更多信息,请参阅从 Amazon Aurora MySQL 数据库集群调用 Lambda 函数

在 Amazon Aurora 中使用异步键预取

注意

异步键预取 (AKP) 功能适用于 Amazon Aurora MySQL 1.15 和更高版本。有关 Aurora MySQL 版本的更多信息,请参阅 Amazon Aurora MySQL 数据库引擎更新

Amazon Aurora 可以使用 AKP 提高跨索引联接表的查询的性能。此功能通过预测运行查询所需的行 (JOIN 查询需要使用批处理键访问 (BKA) 联接算法和多区间读 (MRR) 优化功能) 来提高性能。有关 BKA 和 MRR 的更多信息,请参阅 MySQL 文档中的块嵌套循环和批处理键访问联接多区间读优化

要利用 AKP 功能,查询必须使用 BKA 和 MRR。通常,当查询的 JOIN 子句使用二级索引并且还需要主索引中的一些列时,会出现此类查询。例如,如果 JOIN 子句表示小型外部表和大型内部表之间的索引值的 equijoin,并且索引在大型表中具有高选择性,则可以使用 AKP。AKP 与 BKA 和 MRR 协作,在计算 JOIN 子句期间执行二级索引到主索引的查找。AKP 标识计算 JOIN 子句期间运行查询所需的行。之后,在运行查询之前,它使用后台线程异步将包含这些行的页加载到内存中。

启用异步键预取

您可以将 MySQL 服务器变量 aurora_use_key_prefetch 设置为 on 以启用 AKP 功能。默认情况下,此值设置为 on. 不过,在您启用 BKA 联接算法并禁用基于成本的 MRR 功能之前,无法启用 AKP。为此,您必须为 MySQL 服务器变量 optimizer_switch 设置以下值:

  • batched_key_access 设置为 on。此值控制对 BKA 联接算法的使用。默认情况下,此值设置为 off.

  • mrr_cost_based 设置为 off。此值控制对基于成本的 MRR 功能的使用。默认情况下,此值设置为 on.

目前,您只能在会话级别设置这些值。以下示例说明如何设置这些值以通过执行 SET 语句来为当前会话启用 AKP。

mysql> set @@session.aurora_use_key_prefetch=on; mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

同样,您可以使用 SET 语句为当前会话禁用 AKP 和 BKA 联接算法并重新启用基于成本的 MRR 功能,如以下示例中所示。

mysql> set @@session.aurora_use_key_prefetch=off; mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

有关 batched_key_accessmrr_cost_based 优化程序开关的更多信息,请参阅 MySQL 文档中的 可切换的优化

优化异步键预取的查询

您可以确认查询是否能利用 AKP 功能。为此,请将 EXPLAIN 语句与 EXTENDED 关键字结合使用来分析查询,然后再运行查询。EXPLAIN 语句 提供有关用于指定查询的执行计划的信息。

在 EXPLAIN 语句的输出中,Extra 列描述执行计划附带的其他信息。如果 AKP 功能应用于查询中使用的表,则此列包含下列值之一:

  • Using Key Prefetching

  • Using join buffer (Batched Key Access with Key Prefetching)

以下示例说明如何将 EXPLAIN 与 EXTENDED 结合使用来查看可利用 AKP 的查询的执行计划。

mysql> explain extended select sql_no_cache -> ps_partkey, -> sum(ps_supplycost * ps_availqty) as value -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> group by -> ps_partkey having -> sum(ps_supplycost * ps_availqty) > ( -> select -> sum(ps_supplycost * ps_availqty) * 0.0000003333 -> from -> partsupp, -> supplier, -> nation -> where -> ps_suppkey = s_suppkey -> and s_nationkey = n_nationkey -> and n_name = 'ETHIOPIA' -> ) -> order by -> value desc; +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ | 1 | PRIMARY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 1 | PRIMARY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | | 2 | SUBQUERY | nation | ALL | PRIMARY | NULL | NULL | NULL | 25 | 100.00 | Using where | | 2 | SUBQUERY | supplier | ref | PRIMARY,i_s_nationkey | i_s_nationkey | 5 | dbt3_scale_10.nation.n_nationkey | 2057 | 100.00 | Using index | | 2 | SUBQUERY | partsupp | ref | i_ps_suppkey | i_ps_suppkey | 4 | dbt3_scale_10.supplier.s_suppkey | 42 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) | +----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+ 6 rows in set, 1 warning (0.00 sec)

有关扩展的 EXPLAIN 输出格式的更多信息,请参阅 MySQL 产品文档中的扩展的 EXPLAIN 输出格式

在 Amazon Aurora MySQL 中使用多线程复制从属

默认情况下,当 Aurora MySQL 数据库集群用作复制从属时,Aurora 使用单线程复制。虽然 Amazon Aurora 不禁止多线程复制,但 Aurora MySQL 延续了 MySQL 在多线程复制方面的一些问题。我们建议您不要在生产环境中使用多线程复制。如果使用多线程复制,我们建议您全面测试任何使用情况。

有关在 Amazon Aurora 中使用复制的更多信息,请参阅 使用 Amazon Aurora 进行复制

使用 Amazon Aurora 为 MySQL 数据库扩展读取

您可以将 Amazon Aurora 用于 MySQL 数据库实例,以便利用 Amazon Aurora 的读取扩展功能并为 MySQL 数据库实例扩展读取工作负载。要使用 Aurora 对 MySQL 数据库实例进行读取扩展,请创建 Amazon Aurora MySQL 数据库集群并使它成为 MySQL 数据库实例的复制从属。这适用于 Amazon RDS MySQL 数据库实例或是在 Amazon RDS 外部运行的 MySQL 数据库。

有关创建 Amazon Aurora 数据库集群的信息,请参阅创建 Amazon Aurora 数据库集群

在 MySQL 数据库实例与 Amazon Aurora 数据库集群之间设置复制时,请确保遵循以下准则:

  • 当您引用 Amazon Aurora MySQL 数据库集群时,使用 Amazon Aurora 数据库集群终端节点地址。如果发生故障转移,则提升为 Aurora MySQL 数据库集群主实例的 Aurora 副本继续使用数据库集群终端节点地址。

  • 在您的主实例上保留二进制日志,直至您确认其已应用于 Aurora 副本。此维护可确保您在发生故障时可还原您的主实例。

重要

当使用自管理复制时,您负责监控和解决可能发生的所有复制问题。有关更多信息,请参阅 诊断并解决只读副本之间的滞后

注意

对 Amazon Aurora MySQL 数据库集群启动复制功能所需的权限受到限制且对 Amazon RDS 主用户不可用。为此,您必须使用 Amazon RDS mysql.rds_set_external_mastermysql.rds_start_replication 命令来设置 Amazon Aurora MySQL 数据库集群与 MySQL 数据库实例之间的复制。

在 Amazon RDS 上启动外部主实例和 MySQL 数据库实例之间的复制

  1. 将源 MySQL 数据库实例设为只读:

    mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON;
  2. 对源 MySQL 数据库实例运行 SHOW MASTER STATUS 命令以确定二进制日志位置。将会收到类似于以下示例的输出:

    File Position ------------------------------------ mysql-bin-changelog.000031 107 ------------------------------------
  3. 使用 mysqldump 将数据库从外部 MySQL 数据库实例复制到 Amazon Aurora MySQL 数据库集群。对于非常大的数据库,您可能需要使用将数据导入到 Amazon RDS MySQL 或 MariaDB 数据库实例中,同时减少停机时间中的过程。

    针对 Linux、OS X 或 Unix:

    mysqldump \ --databases <database_name> \ --single-transaction \ --compress \ --order-by-primary \ –u <local_user> \ -p <local_password> | mysql \ --host aurora_cluster_endpoint_address \ –-port 3306 \ –u <RDS_user_name> \ –p <RDS_password>

    对于 Windows:

    mysqldump ^ --databases <database_name> ^ --single-transaction ^ --compress ^ --order-by-primary ^ –u <local_user> ^ -p <local_password> | mysql ^ --host aurora_cluster_endpoint_address ^ –-port 3306 ^ –u <RDS_user_name> ^ –p <RDS_password>

    注意

    确保 -p 选项和输入的密码之间没有空格。

    mysql 命令中使用 ‐‐host‐‐user (-u)‐‐port–p 选项,以指定用于连接到 Aurora 数据库集群的主机名、用户名、端口和密码。主机名是 Amazon Aurora 数据库集群终端节点中的 DNS 名称,例如 mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com。您可以在 Amazon RDS 管理控制台上的集群详细信息中找到终端节点值。

  4. 再次将源 MySQL 数据库实例设为可写:

    mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES;

    有关生成备份以用于复制的更多信息,请参阅 MySQL 文档中的通过将主体和副本设为只读来对其进行备份

  5. 在 Amazon RDS 管理控制台中,将承载源 MySQL 数据库的服务器的 IP 地址添加到 Amazon Aurora 数据库集群的 VPC 安全组。有关修改 VPC 安全组的更多信息,请参阅 Amazon Virtual Private Cloud 用户指南中的您的 VPC 的安全组

    您可能还需要配置本地网络以允许来自 Amazon Aurora 数据库集群的 IP 地址的连接,以便它能与源 MySQL 实例进行通信。要查找 Amazon Aurora 数据库集群的 IP 地址,请使用 host 命令。

    host <aurora_endpoint_address>

    主机名是 Amazon Aurora 数据库集群终端节点中的 DNS 名称。

  6. 通过使用所选的客户端,连接到外部 MySQL 实例并创建用于复制的 MySQL 用户。此账户仅用于复制,并且必须仅供您的域使用以增强安全性。以下是示例。

    CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
  7. 对于外部 MySQL 实例,向复制用户授予 REPLICATION CLIENTREPLICATION SLAVE 权限。例如,要为您的域的“repl_user”用户授予所有数据库的 REPLICATION CLIENTREPLICATION SLAVE 权限,请发出以下命令。

    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
  8. 在设置复制之前,请创建 Aurora MySQL 数据库集群的手动快照以作为复制从属实例。如果您需要将数据库集群作为从属复制来重新建立复制,则可从此快照还原 Aurora MySQL 数据库集群,而不必将 MySQL 数据库实例中的数据导入新的 Aurora MySQL 数据库集群。

  9. 使 Amazon Aurora 数据库集群成为副本。以主用户身份连接到 Amazon Aurora 数据库集群,并使用 mysql.rds_set_external_master 命令将源 MySQL 数据库标识为复制主体。使用您在步骤 2 中确定的主日志文件名和主日志位置。以下是示例。

    CALL mysql.rds_set_external_master ('mymasterserver.mydomain.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);
  10. 在 Amazon Aurora 数据库集群上,发出 mysql.rds_start_replication 命令以开始进行复制。

    CALL mysql.rds_start_replication;

在源 MySQL 数据库实例与 Amazon Aurora 数据库集群之间建立复制之后,可以将 Aurora 副本添加到 Amazon Aurora 数据库集群。随后可以连接到 Aurora 副本以对数据进行读取扩展。有关创建 Aurora 副本的信息,请参阅使用控制台创建 Aurora 副本

使用 Amazon Aurora 实现 MySQL 数据库的灾难恢复

您可以在 MySQL 数据库实例中使用 Amazon Aurora 创建异地备份以进行灾难恢复。要使用 Aurora 实现 MySQL 数据库实例的灾难恢复,请创建 Amazon Aurora 数据库集群并使它成为 MySQL 数据库实例的复制从属。这适用于 Amazon RDS MySQL 数据库实例或是在 Amazon RDS 外部运行的 MySQL 数据库。

重要

在 MySQL 数据库实例与 Amazon Aurora MySQL 数据库集群之间设置复制时,Amazon RDS 不会管理复制。必须监控复制以确保其保持正常运行并在需要时予以修复。

有关如何创建 Amazon Aurora MySQL 数据库集群并使它成为 MySQL 数据库实例的复制从属的说明,请遵循使用 Amazon Aurora 为 MySQL 数据库扩展读取中的过程。

从 MySQL 迁移到 Amazon Aurora MySQL,同时减少停机时间

将数据从支持活动应用程序的 MySQL 数据库导入到 Amazon Aurora MySQL 数据库集群时,您可能希望缩短在迁移时发生服务中断的时间。为此,您可以使用将数据导入到 Amazon RDS MySQL 或 MariaDB 数据库实例中,同时减少停机时间中介绍的步骤。如果使用非常大的数据库,该步骤可能是非常有用的。您可以使用该步骤最大限度减少通过网络传送到 AWS 的数据量以降低导入成本。

该过程所列的步骤可将数据库数据的副本传输到 Amazon EC2 实例,并将数据导入到新的 Amazon RDS MySQL 数据库实例。由于 Amazon Aurora 与 MySQL 兼容,因此,您可以为目标 Amazon RDS MySQL 数据库实例改用 Amazon Aurora 数据库集群。

在 Amazon Aurora MySQL 中使用 XA 事务

我们建议您不要在 Aurora MySQL 中使用扩展架构 (XA) 事务,因为在 XA 处于 PREPARED 状态时,它们可能会造成较长的恢复时间。如果必须在 Aurora MySQL 中使用 XA 事务,请遵循以下最佳实践:

  • 不要在 PREPARED 状态下打开 XA 事务。

  • 使 XA 事务尽可能小。

有关在 MySQL 中使用 XA 事务的更多信息,请参阅 MySQL 文档中的 XA 事务

在 Aurora MySQL 中使用哈希联接

在需要使用 equijoin 联接大量数据时,哈希联接可以提高查询性能。您可以为 Aurora MySQL 启用哈希联接。

哈希联接列可以是任何复杂表达式。在哈希联接列中,您可以使用以下方法比较不同的数据类型:

  • 您可以比较精确数值数据类型类别中的任意类型,例如,intbigintnumericbit

  • 您可以比较近似数值数据类型类别中的任意类型,例如,floatdouble

  • 如果字符串类型具有相同的字符集和排序规则,则可以比较具有这些类型的项目。

  • 如果日期和时间戳数据类型相同,则可以比较具有这些类型的项目。

注意

无法比较不同类别的数据类型。

以下限制适用于 Aurora MySQL 的哈希联接:

  • 不支持左右外部联接。

  • 不支持半联接 (如子查询),除非先具体化子查询。

  • 不支持多表更新或删除。

    注意

    支持单表更新或删除。

  • BLOB 以及空间数据类型列不能是哈希联接中的联接列。

启用哈希联接

您可以将 MySQL 服务器变量 optimizer_switch 设置为 on 以启用哈希联接。默认情况下,哈希联接的 optimizer_switch 参数设置为 on。以下示例说明了如何启用哈希联接。

mysql> SET optimizer_switch='hash_join=on';

在使用该设置时,优化程序选择使用基于成本、查询特性和资源可用性的哈希联接。如果成本估算不正确,您可以强制优化程序选择一个哈希联接。为此,请将 MySQL 服务器变量 hash_join_cost_based 设置为 off。以下示例说明了如何强制优化程序选择哈希联接。

mysql> SET optimizer_switch='hash_join_cost_based=off';

注意

目前,必须启用 Aurora 实验室模式以在 Aurora MySQL 中使用哈希联接。有关启用 Aurora 实验室模式的信息,请参阅Aurora 实验室模式

优化哈希联接的查询

要确定查询是否可以使用哈希联接,请先使用 EXPLAIN 语句分析查询。EXPLAIN 语句 提供有关用于指定查询的执行计划的信息。

在 EXPLAIN 语句的输出中,Extra 列描述执行计划附带的其他信息。如果哈希联接应用于查询中使用的表,该列将包含类似下面的值:

  • Using where; Using join buffer (Hash Join Outer table table1_name)

  • Using where; Using join buffer (Hash Join Inner table table2_name)

以下示例说明了如何使用 EXPLAIN 查看哈希联接查询的执行计划。

mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2 -> WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1; +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ | 1 | SIMPLE | hj_small | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 1 | SIMPLE | hj_big | ALL | NULL | NULL | NULL | NULL | 10 | Using where; Using join buffer (Hash Join Outer table hj_big) | | 1 | SIMPLE | hj_big2 | ALL | NULL | NULL | NULL | NULL | 15 | Using where; Using join buffer (Hash Join Inner table hj_big2) | +----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+ 3 rows in set (0.04 sec)

在输出中,Hash Join Inner table 是用于构建哈希表的表,Hash Join Outer table 是用于探查哈希表的表。

有关扩展的 EXPLAIN 输出格式的更多信息,请参阅 MySQL 产品文档中的扩展的 EXPLAIN 输出格式

在 Aurora MySQL 中使用外键

foreign_key_checks 变量设置为 0 (off) 时,我们强烈建议您不要运行任何数据定义语言 (DDL) 语句。

如果您需要插入或更新暂时违反外键的行,请执行以下步骤:

  1. foreign_key_checks 设置为 0

  2. 执行数据操纵语言 (DML) 更改。

  3. 确保您完成的更改不会违反任何外键约束。

  4. foreign_key_checks 设置为 1 (on)。

此外,请遵循外键约束的这些其他最佳实践:

  • 确保您的客户端应用程序未将 foreign_key_checks 变量作为 init_connect 变量的一部分设置为 0

  • 如果从诸如 mysqldump 这样的逻辑备份还原失败或还原不完整,请务必先将 foreign_key_checks 设置为 1,然后再在同一会话中开始任何其他操作。当逻辑备份开始时,它会将 foreign_key_checks 设置为 0

相关主题