Aurora MySQL 性能和扩展的最佳实践

您可以应用以下最佳实践来改进 Aurora MySQL 集群的性能和可扩展性。

使用 T 实例类进行开发和测试

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

如果您的 Aurora 集群大于 40 TB，请勿使用 T 实例类。当数据库带有大量数据时，管理架构对象的内存开销可能会超过 T 实例的容量。

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

提示

如果您的数据库有时处于空闲状态，但有时又有大量工作负载，则可以使用 Aurora Serverless v2 作为 T 实例的替代。使用 Aurora Serverless v2，您可以定义容量范围，Aurora 会根据当前的工作负载自动扩缩数据库。有关使用情况的详细信息，请参阅 使用 Aurora Serverless v2。有关可与 Aurora Serverless v2 一起使用的数据库引擎版本，请参阅 Aurora Serverless v2 的要求和限制。

在 Aurora MySQL 数据库集群中使用 T 实例作为数据库实例时，建议执行以下操作：

• 对数据库集群中的所有实例使用相同的数据库实例类。例如，如果您将 db.t2.medium 用于写入器实例，那么我们建议您也将 db.t2.medium 用于读取器实例。

• 不要调整任何与内存相关的配置设置，例如 innodb_buffer_pool_size。Aurora 对 T 实例上的内存缓冲区使用一组高度优化的默认值。Aurora 在内存受限的实例上运行时需要采用这些特殊的默认值。如果您在 T 实例上更改任何与内存相关的设置，则更有可能遇到内存不足的情况，即使您的更改旨在增加缓冲区大小。

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

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

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

  有关监视指标的更多信息，请参阅 在 Amazon RDS 控制台中查看指标。

• 监控写入器实例与读取器实例之间的副本滞后（AuroraReplicaLag）。

  如果读取器实例在写入器实例之前耗尽 CPU 积分，则产生的滞后可能会导致读取器实例频繁重新启动。如果应用程序在读取器实例之间分配较高的读取操作负载，同时写入器实例具有非常低的写入操作负载，则通常会出现这种情况。

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

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

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

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

使用异步键预取优化 Aurora MySQL 索引的联接查询

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

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

AKP 适用于 Aurora MySQL 版本 2.10 及更高版本和版本 3。有关 Aurora MySQL 版本的更多信息，请参阅Amazon Aurora MySQL 的数据库引擎更新。

启用异步键预取

您可以将 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_access 和 mrr_cost_based 优化程序开关的更多信息，请参阅 MySQL 文档中的 可切换的优化。

优化异步键预取的查询

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

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

• Using Key Prefetching

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

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

mysql> explain 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 输出格式。

使用哈希联接优化大型 Aurora MySQL 联接查询

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

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

• 您可以比较精确数值数据类型类别中的任意类型，例如，int、bigint、numeric 和 bit。

• 您可以比较近似数值数据类型类别中的任意类型，例如，float 和 double。

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

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

注意

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

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

• Aurora MySQL 版本 2 不支持左右外部联接，但版本 3 支持。

• 不支持半联接（如子查询），除非先具体化子查询。

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

  注意

  支持单表更新或删除。

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

启用哈希联接

启用哈希联接：

• Aurora MySQL 版本 2 – 将数据库参数或数据库集群参数 aurora_disable_hash_join 设置为 0。关闭 aurora_disable_hash_join 会将 optimizer_switch 的值设置为 hash_join=on。

• Aurora MySQL 版本 3 – 将 MySQL 服务器参数 optimizer_switch 设置为 block_nested_loop=on。

哈希联接在 Aurora MySQL 版本 3 中原定设置情况下开启，而在 Aurora MySQL 版本 2 中原定设置情况下关闭。以下示例说明了如何为 Aurora MySQL 版本 3 启用哈希联接。您可以先发布语句 select @@optimizer_switch，以了解 SET 参数字符串中存在的其他设置。更新 optimizer_switch 参数中的一个设置不会删除或修改其他设置。

mysql> SET optimizer_switch='block_nested_loop=on';

注意

对于 Aurora MySQL 版本 3，所有次要版本均支持哈希联接，并且原定设置开启哈希联接。

对于 Aurora MySQL 版本 2，所有次要版本均支持哈希联接。在 Aurora MySQL 版本 2 中，哈希联接功能始终由 aurora_disable_hash_join 值控制。

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

mysql> SET optimizer_switch='hash_join_cost_based=off';

注意

此设置将覆盖基于成本的优化程序的决策。虽然该设置对于测试和开发很有用，但我们建议您不要在生产中使用它。

优化哈希联接的查询

要确定查询是否可以使用哈希联接，请先使用 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 2.08 及更高版本中，您可以使用 SQL 提示来影响查询是否使用哈希联接，以及用于联接的构建和探查端的表。有关详细信息，请参阅 Aurora MySQL 提示。

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

您可以将 Amazon Aurora 用于 MySQL 数据库实例，以便利用 Amazon Aurora 的读取扩展功能并为 MySQL 数据库实例扩展读取工作负载。要使用 Aurora 对 MySQL 数据库实例进行读取扩展，请创建 Aurora MySQL 数据库集群并使它成为 MySQL 数据库实例的只读副本。然后连接到 Aurora MySQL 集群以处理读取查询。源数据库可以是 RDS for MySQL 数据库实例或是在 Amazon RDS 外部运行的 MySQL 数据库。有关更多信息，请参阅 使用 Amazon Aurora 扩展 MySQL 数据库的读取。

优化时间戳操作

当系统变量 time_zone 的值设置为 SYSTEM 时，每个需要时区计算的 MySQL 函数调用都会进行系统库调用。当您运行在高并发条件下返回或更改此类 TIMESTAMP 值的 SQL 语句时，可能会遇到延迟、锁定争用和 CPU 使用率增加的情况。有关更多信息，请参阅 MySQL 文档中的 time_zone。

为避免这种行为，我们建议您将 time_zone 数据库集群参数的值更改为 UTC。有关更多信息，请参阅 在 Amazon Aurora 中修改数据库集群参数组中的参数。

虽然 time_zone 参数是动态的（不需要重新启动数据库服务器），但新值仅用于新连接。为确保更新所有连接以使用新的 time_zone 值，我们建议您在更新数据库集群参数后回收应用程序连接。