# Aurora MySQL 性能和扩展的最佳实践 您可以应用以下最佳实践来改进 Aurora MySQL 集群的性能和可扩展性。 **Topics** + [使用 T 实例类进行开发和测试](#AuroraMySQL.BestPractices.T2Medium) + [使用异步键预取优化 Aurora MySQL 索引的联接查询](#Aurora.BestPractices.AKP) + [使用哈希联接优化大型 Aurora MySQL 联接查询](#Aurora.BestPractices.HashJoin) + [使用 Amazon Aurora 为 MySQL 数据库扩展读取](#AuroraMySQL.BestPractices.ReadScaling) + [优化时间戳操作](#AuroraMySQL.BestPractices.Performance.TimeZone) + [虚拟索引 ID 溢出错误](#AuroraMySQL.BestPractices.Performance.VirtualIndexIDOverflow) ## 使用 T 实例类进行开发和测试 使用 `db.t2`、`db.t3` 或 `db.t4g` 数据库实例类的 Amazon Aurora MySQL 实例最适合不支持长时间运行较高工作负载的应用程序。T 实例旨在提供适度的基准性能，并能够根据您工作负载的需要实现性能的显著突增。它们旨在用于不经常或不持续使用完整 CPU、但偶尔需要突增性能的工作负载。建议仅将 T 数据库实例类用于开发和测试服务器，或其他非生产服务器。有关 T 实例类的更多详细信息，请参阅[具爆发能力的实例](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/burstable-performance-instances.html)。 如果您的 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.md)。有关可与 Aurora Serverless v2 一起使用的数据库引擎版本，请参阅 [Aurora Serverless v2 的要求和限制](aurora-serverless-v2.requirements.md)。 在 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 控制台中查看指标](USER_Monitoring.md)。 + 监控写入器实例与读取器实例之间的副本滞后（`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 文档中的[块嵌套循环和批处理键访问联接](https://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html)和[多区间读优化](https://dev.mysql.com/doc/refman/5.6/en/mrr-optimization.html)。 要利用 AKP 功能，查询必须使用 BKA 和 MRR。通常，当查询的 JOIN 子句使用二级索引并且还需要主索引中的一些列时，会出现此类查询。例如，如果 JOIN 子句表示小型外部表和大型内部表之间的索引值的 equijoin，并且索引在大型表中具有高选择性，则可以使用 AKP。AKP 与 BKA 和 MRR 协作，在计算 JOIN 子句期间执行二级索引到主索引的查找。AKP 标识计算 JOIN 子句期间运行查询所需的行。之后，在运行查询之前，它使用后台线程异步将包含这些行的页加载到内存中。 AKP 适用于 Aurora MySQL 版本 2.10 及更高版本和版本 3。有关 Aurora MySQL 版本的更多信息，请参阅[Amazon Aurora MySQL 的数据库引擎更新Amazon Aurora MySQL 的长期支持（LTS）版本和测试版](AuroraMySQL.Updates.md)。 ### 启用异步键预取 您可以将 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\$1key\$1access** 和 **mrr\$1cost\$1based** 优化程序开关的更多信息，请参阅 MySQL 文档中的 [可切换的优化](https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html)。 ### 优化异步键预取的查询 您可以确认查询是否能利用 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 输出格式](https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html)。 ## 使用哈希联接优化大型 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 输出格式](https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html)。 在 Aurora MySQL 2.08 及更高版本中，您可以使用 SQL 提示来影响查询是否使用哈希联接，以及用于联接的构建和探查端的表。有关详细信息，请参阅 [Aurora MySQL 提示](AuroraMySQL.Reference.Hints.md)。 ## 使用 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 数据库的读取](AuroraMySQL.Replication.ReadScaling.md)。 ## 优化时间戳操作 当系统变量 `time_zone` 的值设置为 `SYSTEM` 时，每个需要时区计算的 MySQL 函数调用都会进行系统库调用。当您运行在高并发条件下返回或更改此类 `TIMESTAMP` 值的 SQL 语句时，可能会遇到延迟、锁定争用和 CPU 使用率增加的情况。有关更多信息，请参阅 MySQL 文档中的 [time\$1zone](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_time_zone)。 为避免这种行为，我们建议您将 `time_zone` 数据库集群参数的值更改为 `UTC`。有关更多信息，请参阅 [在 Amazon Aurora 中修改数据库集群参数组中的参数](USER_WorkingWithParamGroups.ModifyingCluster.md)。 虽然 `time_zone` 参数是动态的（不需要重新启动数据库服务器），但新值仅用于新连接。为确保更新所有连接以使用新的 `time_zone` 值，我们建议您在更新数据库集群参数后回收应用程序连接。 ## 虚拟索引 ID 溢出错误 Aurora MySQL 将虚拟索引 ID 的值限制为 8 位，以防出现由 MySQL 中的撤消格式引起的问题。如果索引超过虚拟索引 ID 限制，则集群可能不可用。当索引接近虚拟索引 ID 限制时，或者当您尝试创建高于虚拟索引 ID 限制的索引时，RDS 可能会引发错误代码 `63955` 或警告代码 `63955`。要解决虚拟索引 ID 限制错误，我们建议您使用逻辑转储和还原来重新创建数据库。 有关 Amazon Aurora MySQL 的逻辑转储和还原的更多信息，请参阅 [Migrate very large databases to Amazon Aurora MySQL using MyDumper and MyLoader](https://aws.amazon.com/blogs/database/migrate-very-large-databases-to-amazon-aurora-mysql-using-mydumper-and-myloader/)。有关在 Amazon Aurora 中访问错误日志的更多信息，请参阅[监控 Amazon Aurora 日志文件](USER_LogAccess.md)。