使用 Amazon Aurora MySQL 的并行查询 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

使用 Amazon Aurora MySQL 的并行查询

本主题描述了 Amazon Aurora MySQL 兼容版的并行查询性能优化。该功能在某些数据密集型查询中使用特殊处理路径,从而利用 Aurora 共享存储架构。并行查询非常适合以下 Aurora MySQL 数据库集群:具有包含数百万行的表以及需要数分钟或数小时才能完成的分析查询。

Aurora MySQL 的并行查询概述

Aurora MySQL 并行查询是一种优化功能,它并行处理在处理数据密集型查询时涉及的一些 I/O 和计算。并行处理的工作包括从存储中检索行,提取列值以及确定哪些行与 WHERE 子句和联接子句中的条件匹配。这种数据密集型工作将委派(在数据库优化术语中为向下推送)给 Aurora 分布式存储层中的多个节点。如果不使用并行查询,每个查询将所有扫描的数据传输到 Aurora MySQL 集群中的单个节点(头节点),并在此处执行所有查询处理。

提示

PostgreSQL 数据库引擎还有一个称为“并行查询”的功能。该功能与 Aurora 并行查询无关。

如果开启了并行查询功能,Aurora MySQL 引擎将自动确定查询何时可以从中受益,而无需进行 SQL 更改(如提示或表属性)。在以下章节中,您可以找到何时将并行查询应用于查询的说明。您还可以了解如何确保在提供最大好处时应用并行查询。

注意

并行查询优化为需要数分钟或数小时才能完成的长时间运行的查询提供最大优势。Aurora MySQL 通常不会为低开销查询运行并行查询优化。如果另一种优化技术更有意义(如查询缓存、缓冲池缓存或索引查找),它通常也不会执行并行查询优化。如果发现在需要时未使用并行查询,请参阅 验证哪些语句使用并行查询

优点

使用并行查询,您可以对 Aurora MySQL 表运行数据密集型分析查询。在很多情况下,与传统的查询处理分工相比,性能提高了一个数量级。

并行查询的好处包括:

  • 由于跨多个存储节点并行处理物理读取请求,提高了 I/O 性能。

  • 降低了网络流量。Aurora 不会将存储节点中的整个数据页面传输到头节点并随后筛选掉不需要的行和列。相反,Aurora 传输仅包含结果集所需的列值的紧凑元组。

  • 由于向下推送 WHERE 子句的函数处理、行筛选和列投影,减少了头节点上的 CPU 使用率。

  • 减轻了缓冲池上的内存压力。并行查询处理的页面不会添加到缓冲池中。此方法可降低数据密集型扫描从缓冲池中逐出经常使用的数据的可能性。

  • 通过使在现有的数据上执行长时间运行的分析查询变得切实可行,可能会在提取、转换和加载 (ETL) 管道中减少重复的数据。

架构

并行查询功能使用 Aurora MySQL 的主要架构准则:将数据库引擎与存储子系统分离,并简化通信协议以减少网络流量。Aurora MySQL 使用这些技术加快写入密集型操作(例如重做日志处理)。并行查询将相同的准则应用于读取操作。

注意

Aurora MySQL 并行查询的架构与其他数据库系统中名称类似的功能架构不同。Aurora MySQL 并行查询不涉及对称多处理 (SMP),因此不依赖于数据库服务器的 CPU 容量。并行处理是在存储层中发生的,与作为查询协调器的 Aurora MySQL 服务器无关。

默认情况下,如果没有并行查询,Aurora 查询处理涉及将原始数据传输到 Aurora 集群中的单个节点(头节点)。之后,Aurora 会针对该单个节点上单个线程中的该查询执行所有进一步的处理。通过使用并行查询,该 I/O 密集型和 CPU 密集型工作的绝大部分将委派给存储层中的节点。仅将结果集的紧凑行传回到头节点,已筛选行并提取和转换了列值。性能优势来自于网络流量减少、头节点上的 CPU 使用率下降以及跨存储节点并行处理 I/O。并行 I/O、筛选和投影数量与运行查询的 Aurora 集群中的数据库实例数无关。

先决条件

要使用并行查询的所有功能,需要运行版本 2.09 或更高版本的 Aurora MySQL 数据库集群。如果您已有要与并行查询一起使用的集群,可以将其升级到兼容版本并在之后开启并行查询。在这种情况下,请确保遵循 并行查询的升级注意事项中的升级过程,因为这些较新版本中的配置设置名称和默认值不同。

集群中的数据库实例必须使用 db.r* 实例类。

确保为集群启用了哈希联接优化。要了解如何操作,请参阅为并行查询集群开启哈希联接

要自定义参数(如 aurora_parallel_queryaurora_disable_hash_join),您必须具有与集群一起使用的自定义参数组。您可以使用数据库参数组为每个数据库实例单独指定这些参数。但是,我们建议您在数据库集群参数组中指定它们。这样,集群中的所有数据库实例都会继承这些参数的相同设置。

限制

以下限制适用于并行查询功能:

  • Aurora I/O-Optimized 数据库集群存储配置不支持并行查询。

  • 您不能将并行查询与 db.t2 或 db.t3 实例类一起使用。即使您使用 aurora_pq_force 会话变量来请求并行查询,此限制也适用。

  • 并行查询不适用于使用 COMPRESSEDREDUNDANT 行格式的表。对于计划与并行查询结合使用的表,请使用 COMPACTDYNAMIC 行格式。

  • Aurora 使用基于成本的算法来确定是否对每个 SQL 语句使用并行查询机制。在语句中使用某些 SQL 结构可以防止并行查询,或使该语句不太可能执行并行查询。有关 SQL 结构与并行查询的兼容性的信息,请参阅 并行查询如何与 SQL 结构一起使用

  • 每个 Aurora 数据库实例每次只能运行一定数量的并行查询会话。如果查询具有多个使用并行查询的部分(例如,子查询、联接或 UNION 运算符),这些阶段将按顺序运行。在任何时候,该语句仅计为一个并行查询会话。您可以使用并行查询状态变量监控活动会话数。您可以查询 Aurora_pq_max_concurrent_requests 状态变量以检查给定数据库实例的并发会话数限制。

  • 并行查询适用于 Aurora 支持的所有Amazon区域。对于大多数 Amazon 区域,使用并行查询所需的最低 Aurora MySQL 版本为 2.09。

  • 并行查询旨在提高数据密集型查询的性能。它不是为轻量级查询而设计的。

  • 我们建议您为 SELECT 语句使用读取器节点,尤其是数据密集型语句。

并行查询导致的 I/O 成本

如果您的 Aurora MySQL 集群使用并行查询,您可能会看到 VolumeReadIOPS 值出现增长。并行查询不使用缓冲池。因此,尽管查询速度很快,但这种优化的处理可能会导致读取操作和相关费用的增加。

查询的并行查询 I/O 成本在存储层计量,开启并行查询后,该成本将相同或更高。好处是提高了查询性能。并行查询可能导致 I/O 成本更高的原因有两个:

  • 即使表中的某些数据位于缓冲池中,并行查询也要求在存储层扫描所有数据,这会产生 I/O 成本。

  • 运行并行查询不会预热缓冲池。因此,连续运行同一个并行查询会产生完全 I/O 成本。

规划并行查询集群

规划开启并行查询的数据库集群需要做出一些选择。其中包括执行设置步骤(创建或还原完整 Aurora MySQL 集群),以及确定在整个数据库集群中开启并行查询的范围。

作为规划的一部分,考虑以下事项:

  • 如果您使用与 MySQL 5.7 兼容的 Aurora MySQL,则必须选择 Aurora MySQL 2.09 或更高版本。在这种情况下,您始终创建预置的集群。然后,使用 aurora_parallel_query 参数开启并行查询。

    如果您的现有 Aurora MySQL 集群运行版本 2.09 或更高版本,则无需创建新集群即可使用并行查询。您可以将集群或集群中的特定数据库实例与开启了 aurora_parallel_query 参数的参数组相关联。这样,您可以减少设置要与并行查询结合使用的相关数据的时间和精力。

  • 规划需要重新组织的任何大型表,以便在访问它们时可以使用并行查询。您可能需要创建一些大型表的新版本,其中并行查询非常有用。例如,您可能需要删除全文搜索索引。有关详细信息,请参阅 创建架构对象以利用并行查询

检查并行查询的 Aurora MySQL 版本兼容性

要检查哪些 Aurora MySQL 版本与并行查询集群兼容,请使用 describe-db-engine-versions Amazon CLI CLI 命令并检查 SupportsParallelQuery 字段的值。以下代码示例说明了如何检查哪些组合适用于指定 Amazon 区域中的并行查询集群。确保在单行上指定完整的 --query 参数字符串。

aws rds describe-db-engine-versions --region us-east-1 --engine aurora-mysql \ --query '*[]|[?SupportsParallelQuery == `true`].[EngineVersion]' --output text

上述命令生成类似于以下内容的输出。输出可能因指定Amazon区域中可用的 Aurora MySQL 版本而异。

5.7.mysql_aurora.2.11.1 8.0.mysql_aurora.3.01.0 8.0.mysql_aurora.3.01.1 8.0.mysql_aurora.3.02.0 8.0.mysql_aurora.3.02.1 8.0.mysql_aurora.3.02.2 8.0.mysql_aurora.3.03.0

开始对集群使用并行查询后,可以监视性能并消除使用并行查询的障碍。有关这些说明,请参阅并行查询的性能优化

创建使用并行查询的数据库集群

要创建具有并行查询的 Aurora MySQL 集群,在其中添加新实例或执行其他管理操作,您可以使用与其他 Aurora MySQL 集群相同的Amazon Web Services Management Console和 Amazon CLI 方法。您可以创建新的集群以使用并行查询。也可以通过从 MySQL 兼容的 Aurora 数据库集群的快照还原,创建一个数据库集群以使用并行查询。如果不熟悉创建新的 Aurora MySQL 集群的过程,您可以在创建 Amazon Aurora 数据库集群中找到背景信息和先决条件。

在选择 Aurora MySQL 引擎版本时,建议您选择可用的最新版本。目前,Aurora MySQL 2.09 及更高版本支持并行查询。如果使用 Aurora MySQL 2.09 及更高版本,则可以更灵活地开启和关闭并行查询,或者将并行查询与现有集群结合使用。

无论是创建新集群还是从快照还原,您都可以使用与其他 Aurora MySQL 集群相同的方法添加新的数据库实例。

使用控制台创建并行查询集群

您可以使用控制台创建新的并行查询集群,如下所述。

使用 Amazon Web Services Management Console 创建并行查询集群
  1. 按照Amazon Web Services Management Console中的常规 创建 Amazon Aurora 数据库集群过程进行操作。

  2. 选择引擎屏幕上,选择 Aurora MySQL。

    对于引擎版本,选择 Aurora MySQL 2.09 或更高版本。有了这些版本,使用并行查询时的限制最少。这些版本还具有最大的灵活性,可以随时打开或关闭并行查询。

    如果对此集群使用最新 Aurora MySQL 版本不切实际,请选择显示支持并行查询功能的版本。这样做会筛选版本菜单,以仅显示与并行查询兼容的特定 Aurora MySQL 版本。

  3. 对于其他配置,请选择为数据库集群参数组创建的参数组。Aurora MySQL 2.09 和更高版本需要使用这样的自定义参数组。在数据库集群参数组中,指定参数设置 aurora_parallel_query=ONaurora_disable_hash_join=OFF。这样做会为集群开启并行查询,并开启与并行查询结合使用的哈希联接优化。

验证新集群是否可以使用并行查询
  1. 使用上述方法创建集群。

  2. (对于 Aurora MySQL 版本 2 或 3)检查 aurora_parallel_query 配置设置是否为 true。

    mysql> select @@aurora_parallel_query; +-------------------------+ | @@aurora_parallel_query | +-------------------------+ | 1 | +-------------------------+
  3. (对于 Aurora MySQL 版本 2)检查 aurora_disable_hash_join 设置是否为 false。

    mysql> select @@aurora_disable_hash_join; +----------------------------+ | @@aurora_disable_hash_join | +----------------------------+ | 0 | +----------------------------+
  4. 对于一些大型表和数据密集型查询,请检查查询计划以确认某些查询正在使用并行查询优化。为此,请按照验证哪些语句使用并行查询中的过程操作。

使用 CLI 创建并行查询集群

您可以使用 CLI 创建新的并行查询集群,如下所述。

使用 Amazon CLI 创建并行查询集群
  1. (可选)检查哪些 Aurora MySQL 版本与并行查询集群兼容。为此,请使用 describe-db-engine-versions 命令并检查 SupportsParallelQuery 字段的值。有关示例,请参阅检查并行查询的 Aurora MySQL 版本兼容性

  2. (可选)使用设置 aurora_parallel_query=ONaurora_disable_hash_join=OFF 创建自定义数据库集群参数组。使用如下命令。

    aws rds create-db-cluster-parameter-group --db-parameter-group-family aurora-mysql5.7 --db-cluster-parameter-group-name pq-enabled-57-compatible aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name pq-enabled-57-compatible \ --parameters ParameterName=aurora_parallel_query,ParameterValue=ON,ApplyMethod=pending-reboot aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name pq-enabled-57-compatible \ --parameters ParameterName=aurora_disable_hash_join,ParameterValue=OFF,ApplyMethod=pending-reboot

    如果执行此步骤,请在后续 --db-cluster-parameter-group-name my_cluster_parameter_group 语句中指定 create-db-cluster 选项。替换您自己的参数组的名称。如果省略此步骤,则创建参数组并稍后将其与集群关联,如 打开和关闭并行查询中所述。

  3. 按照Amazon CLI中的常规 创建 Amazon Aurora 数据库集群过程进行操作。

  4. 指定以下选项集:

    • 对于 --engine 选项,请使用 aurora-mysql。这些值生成的并行查询集群与 MySQL 5.7 或 8.0 兼容。

    • 对于 --db-cluster-parameter-group-name 选项,请指定您创建并指定参数值 aurora_parallel_query=ON 的数据库集群参数组的名称。如果省略此选项,则可以使用默认参数组创建集群,然后对其进行修改以使用此类自定义参数组。

    • 对于 --engine-version 选项,请使用与并行查询兼容的 Aurora MySQL 版本。如有必要,请使用 规划并行查询集群中的过程获取版本列表。至少使用版本 2.09.0。这些版本和所有更高版本都包含对并行查询的实质性增强。

      以下代码示例显示了操作方法。用您自己的值替换每个环境变量,如 $CLUSTER_ID。此示例还指定了生成主用户密码并在 Secrets Manager 中对其进行管理的 --manage-master-user-password 选项。有关更多信息,请参阅使用 Amazon Aurora 和 Amazon Secrets Manager 管理密码。或者,您可以使用 --master-password 选项自行指定和管理密码。

      aws rds create-db-cluster --db-cluster-identifier $CLUSTER_ID \ --engine aurora-mysql --engine-version 5.7.mysql_aurora.2.11.1 \ --master-username $MASTER_USER_ID --manage-master-user-password \ --db-cluster-parameter-group-name $CUSTOM_CLUSTER_PARAM_GROUP aws rds create-db-instance --db-instance-identifier ${INSTANCE_ID}-1 \ --engine same_value_as_in_create_cluster_command \ --db-cluster-identifier $CLUSTER_ID --db-instance-class $INSTANCE_CLASS
  5. 验证您创建或还原的集群是否具有可用的并行查询功能。

    检查 aurora_parallel_query 配置设置是否存在。如果此设置的值为 1,则可以使用并行查询。如果此设置的值为 0,请先将其设置为 1,然后才能使用并行查询。无论使用哪种方式,集群都能够执行并行查询。

    mysql> select @@aurora_parallel_query; +------------------------+ | @@aurora_parallel_query| +------------------------+ | 1 | +------------------------+
使用 Amazon CLI将快照还原到并行查询集群
  1. 检查哪些 Aurora MySQL 版本与并行查询集群兼容。为此,请使用 describe-db-engine-versions 命令并检查 SupportsParallelQuery 字段的值。有关示例,请参阅检查并行查询的 Aurora MySQL 版本兼容性。确定要用于还原的集群的版本。对于与 MySQL 5.7 兼容的集群,选择 Aurora MySQL 2.09.0 或更高版本。

  2. 找到与 Aurora MySQL 兼容的集群快照。

  3. 按照Amazon CLI中的常规 从数据库集群快照还原过程进行操作。

    aws rds restore-db-cluster-from-snapshot \ --db-cluster-identifier mynewdbcluster \ --snapshot-identifier mydbclustersnapshot \ --engine aurora-mysql
  4. 验证您创建或还原的集群是否具有可用的并行查询功能。使用与 使用 CLI 创建并行查询集群中相同的验证过程。

打开和关闭并行查询

如果开启了并行查询,Aurora MySQL 确定是否在运行时为每个查询使用该功能。对于联接、联合和子查询等,Aurora MySQL 确定每个查询块是否在运行时使用并行查询。有关详细信息,请参阅 验证哪些语句使用并行查询并行查询如何与 SQL 结构一起使用

您可以使用 aurora_parallel_query 选项,在数据库实例的全局和会话级别动态开启和关闭并行查询。默认情况下,您可以更改数据库集群组中的 aurora_parallel_query 设置,以启用或关闭并行查询。

mysql> select @@aurora_parallel_query; +------------------------+ | @@aurora_parallel_query| +------------------------+ | 1 | +------------------------+

要在会话级别切换 aurora_parallel_query 参数,请使用标准方法更改客户端配置设置。例如,可以通过 mysql 命令行或在 JDBC 或 ODBC 应用程序中执行此操作。标准 MySQL 客户端上的命令是 set session aurora_parallel_query = {'ON'/'OFF'}。您还可以将会话级参数添加到 JDBC 配置或应用程序代码中,以动态开启或关闭并行查询。

您可以为特定数据库实例或整个集群永久更改 aurora_parallel_query 参数的设置。如果您在数据库参数组中指定参数值,则该值仅适用于集群中的特定数据库实例。如果在数据库集群参数组中指定参数值,则集群中的所有数据库实例都将继承相同的设置。要切换 aurora_parallel_query 参数,请使用处理参数组的方法,如使用参数组中所述。按照以下步骤进行操作:

  1. 创建自定义集群参数组(推荐)或自定义数据库参数组。

  2. 在此参数组中,将 parallel_query 更新为所需的值。

  3. 根据您创建的是数据库集群参数组还是数据库参数组,将参数组附加到 Aurora 集群或计划使用并行查询功能的特定数据库实例。

    提示

    因为 aurora_parallel_query 是一个动态参数,所以在更改此设置后不需要重启集群。但是,在切换选项之前使用并行查询的任何连接都将继续执行此操作,直到连接关闭或实例重启。

您可以使用 ModifyDBClusterParameterGroupModifyDBParameterGroup API 操作或 Amazon Web Services Management Console修改并行查询参数。

为并行查询集群开启哈希联接

并行查询通常用于受益于哈希联接优化的各种资源密集型查询。因此,确保为计划使用并行查询的集群开启哈希联接非常有用。有关如何有效使用哈希联接的信息,请参阅 使用哈希联接优化大型 Aurora MySQL 联接查询

使用控制台开启和关闭并行查询

通过使用参数组,可以在数据库实例级别或数据库集群级别开启或关闭并行查询。

要使用 Amazon Web Services Management Console 开启或关闭数据库集群的并行查询
  1. 创建一个自定义参数组,如使用参数组中所述。

  2. aurora_parallel_query 更新为 1(开启)或 0(关闭)。对于可以使用并行查询功能的集群,将原定设置关闭 aurora_parallel_query

  3. 如果使用自定义集群参数组,请将其附加到计划使用并行查询功能的 Aurora 数据库集群。如果您使用自定义数据库参数组,请将其附加到集群中的一个或多个数据库实例。我们建议使用集群参数组。这样做可确保集群中的所有数据库实例对并行查询和关联功能(如哈希联接)具有相同的设置。

使用 CLI 开启和关闭并行查询

您可以使用 modify-db-cluster-parameter-groupmodify-db-parameter-group 命令修改并行查询参数。根据您是通过数据库集群参数组还是通过数据库参数组指定 aurora_parallel_query 的值,选择相应的命令。

要使用 CLI 开启或关闭数据库集群的并行查询
  • 使用 modify-db-cluster-parameter-group 命令修改并行查询参数。使用如下命令。用适当的名称替换您自己的自定义参数组。用 ONOFF替换 ParameterValue 选项的 --parameters 部分。

    $ aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name cluster_param_group_name \ --parameters ParameterName=aurora_parallel_query,ParameterValue=ON,ApplyMethod=pending-reboot { "DBClusterParameterGroupName": "cluster_param_group_name" } aws rds modify-db-cluster-parameter-group --db-cluster-parameter-group-name cluster_param_group_name \ --parameters ParameterName=aurora_pq,ParameterValue=ON,ApplyMethod=pending-reboot

您还可以在会话级别开启或关闭并行查询,例如,通过 mysql 命令行或在 JDBC 或 ODBC 应用程序中。为此,请使用标准方法更改客户端配置设置。例如,对于 Aurora MySQL,标准 MySQL 客户端上的命令是 set session aurora_parallel_query = {'ON'/'OFF'}

您还可以将会话级参数添加到 JDBC 配置或应用程序代码中,以动态开启或关闭并行查询。

覆盖并行查询优化程序

您可以使用 aurora_pq_force 会话变量覆盖并行查询优化程序并为每个查询请求并行查询。我们建议您仅出于测试目的这样做。以下示例显示如何在会话中使用 aurora_pq_force

set SESSION aurora_parallel_query = ON; set SESSION aurora_pq_force = ON;

要关闭覆盖,请执行以下操作:

set SESSION aurora_pq_force = OFF;

并行查询的升级注意事项

根据升级并行查询集群时的原始版本和目标版本,您可能会发现并行查询可以优化的查询类型的增强功能。您可能还会发现不需要为并行查询指定特殊的引擎模式参数。以下各节介绍了升级已开启并行查询的集群时的注意事项。

将并行查询集群升级到 Aurora MySQL 版本 3

从 Aurora MySQL 版本 3 开始,一些 SQL 语句、子句和数据类型具有新的或改进的并行查询支持。从版本 3 之前的版本升级时,请检查其他查询是否可以从并行查询优化中受益。有关这些并行查询增强的信息,请参阅 列数据类型分区表聚合函数、GROUP BY 子句和 HAVING 子句

如果您要从 Aurora MySQL 2.08 或更低版本升级并行查询集群,还可以了解开启并行查询的方式的变化。为此,请阅读 升级到 Aurora MySQL 2.09 及更高版本

原定设置情况下,哈希联接优化在 Aurora MySQL 版本 3 中处于开启状态。早期版本中的 aurora_disable_hash_join 配置选项未使用。

升级到 Aurora MySQL 2.09 及更高版本

在 Aurora MySQL 版本 2.09 及更高版本中,并行查询适用于预调配的集群,但不需要 parallelquery 引擎模式参数。因此,您无需创建新集群或从现有快照还原,即可将并行查询与这些版本结合使用。您可以使用 升级 Aurora MySQL 数据库集群的次要版本或补丁程序级别中介绍的升级过程将集群升级到此类版本。您可以升级较旧的集群,无论它是并行查询集群还是预置集群。要减少引擎版本菜单中的选项数,可以选择显示支持并行查询功能的版本来筛选该菜单中的条目。然后,选择 Aurora MySQL 2.09 或更高版本。

将较早的并行查询集群升级到 Aurora MySQL 2.09 或更高版本后,您可以在升级后的集群中开启并行查询。默认情况下,在这些版本中,并行查询处于关闭状态,并且启用该查询的过程不同。预设情况下,哈希联接优化也处于关闭状态,必须单独开启。因此,请确保在升级后再次开启这些设置。有关此操作的说明,请参阅 打开和关闭并行查询为并行查询集群开启哈希联接

特别是,您可以通过使用配置参数 aurora_parallel_query=ONaurora_disable_hash_join=OFF 而不是 aurora_pq_supportedaurora_pq 来开启并行查询。aurora_pq_supportedaurora_pq 参数在较新 Aurora MySQL 版本中已弃用。

在升级的集群中,EngineMode 属性的值是 provisioned 而不是 parallelquery。要检查并行查询是否可用于指定的引擎版本,现在您检查 SupportsParallelQuery describe-db-engine-versions 命令输出中 Amazon CLI 字段的值。在早期 Aurora MySQL 版本中,您已检查 parallelquery 列表中 SupportedEngineModes 是否存在。

升级到 Aurora MySQL 版本 2.09 或更高版本后,您可以利用以下功能。这些功能不适用于运行较旧 Aurora MySQL 版本的并行查询集群。

并行查询的性能优化

要管理并行查询工作负载的性能,请确保将并行查询用于该优化最有用的查询。

为此,您可以执行以下操作:

创建架构对象以利用并行查询

在创建或修改您计划用于并行查询的表之前,请确保自行熟悉 先决条件限制中描述的要求。

由于并行查询要求表使用 ROW_FORMAT=CompactROW_FORMAT=Dynamic 设置,请检查 Aurora 配置设置以了解对 INNODB_FILE_FORMAT 配置选项的任何更改。发出 SHOW TABLE STATUS 语句以确认数据库中的所有表的行格式。

在更改架构以开启并行查询来处理更多表之前,请确保进行测试。测试应确认并行查询是否会导致这些表的性能净增加。此外,还要确保并行查询的架构要求与您的目标相符。

例如,在从 ROW_FORMAT=Compressed 切换到 ROW_FORMAT=CompactROW_FORMAT=Dynamic 之前,请针对原始表和新表测试工作负载的性能。此外,还要考虑其他潜在影响,例如,数据量增加。

验证哪些语句使用并行查询

在典型操作中,您无需执行任何特殊操作即可利用并行查询。在查询满足并行查询的基本要求后,查询优化程序自动确定是否在每个特定查询中使用并行查询。

如果您在开发或测试环境中运行试验,您可能会发现未使用并行查询,因为您的表的行数或总数据量太少。表的数据也可能完全位于缓冲池中,尤其是最近创建以执行试验的表。

在监控或优化集群性能时,请确保确定是否在相应的上下文中使用并行查询。您可以调整数据库架构、设置、SQL 查询甚至集群拓扑和应用程序连接设置以利用该功能。

要检查查询是否使用并行查询,请运行 EXPLAIN 语句以检查查询计划(也称为“解释计划”)。有关 SQL 语句、子句和表达式如何影响并行查询的 EXPLAIN 输出的示例,请参阅并行查询如何与 SQL 结构一起使用

以下示例说明了传统查询计划和并行查询计划之间的区别。此解释计划来自 TPC-H 基准中的查询 3。本节中的很多示例查询使用 TPC-H 数据集中的表。您可以从 TPC-H 网站获取生成示例数据的表定义、查询以及 dbgen 程序。

EXPLAIN SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'AUTOMOBILE' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < date '1995-03-13' AND l_shipdate > date '1995-03-13' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate LIMIT 10;

默认情况下,查询可能具有如下所示的计划。如果您没有看到查询计划中使用的哈希联接,请确保首先开启了优化。

+----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+ | 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 1480234 | 10.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 14875240 | 3.33 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | lineitem | NULL | ALL | NULL | NULL | NULL | NULL | 59270573 | 3.33 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+----------+------------+------+---------------+------+---------+------+----------+----------+----------------------------------------------------+

对于 Aurora MySQL 版本 3,您可以通过发出以下语句在会话级别开启哈希联接。

SET optimizer_switch='block_nested_loop=on';

对于 Aurora MySQL 版本 2.09 及更高版本,您可以将 aurora_disable_hash_join 数据库参数或数据库集群参数设置为 0(关闭)。关闭 aurora_disable_hash_join 会将 optimizer_switch 的值设置为 hash_join=on

开启哈希连接后,尝试再次运行 EXPLAIN 语句。有关如何有效使用哈希联接的信息,请参阅 使用哈希联接优化大型 Aurora MySQL 联接查询

在开启哈希联接但关闭并行查询的情况下,查询可能具有如下所示的计划,该计划使用哈希联接而不是并行查询。

+----+-------------+----------+...+-----------+-----------------------------------------------------------------+ | id | select_type | table |...| rows | Extra | +----+-------------+----------+...+-----------+-----------------------------------------------------------------+ | 1 | SIMPLE | customer |...| 5798330 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | orders |...| 154545408 | Using where; Using join buffer (Hash Join Outer table orders) | | 1 | SIMPLE | lineitem |...| 606119300 | Using where; Using join buffer (Hash Join Outer table lineitem) | +----+-------------+----------+...+-----------+-----------------------------------------------------------------+

在开启并行查询后,该查询计划中的两个步骤可以使用并行查询优化,如 EXPLAIN 输出中的 Extra 列所示。这些步骤的 I/O 密集型和 CPU 密集型处理将向下推送到存储层。

+----+...+--------------------------------------------------------------------------------------------------------------------------------+ | id |...| Extra | +----+...+--------------------------------------------------------------------------------------------------------------------------------+ | 1 |...| Using where; Using index; Using temporary; Using filesort | | 1 |...| Using where; Using join buffer (Hash Join Outer table orders); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) | | 1 |...| Using where; Using join buffer (Hash Join Outer table lineitem); Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra) | +----+...+--------------------------------------------------------------------------------------------------------------------------------+

有关如何解释并行查询的 EXPLAIN 输出以及并行查询可以应用到的 SQL 语句部分的信息,请参阅并行查询如何与 SQL 结构一起使用

以下示例输出显示在具有冷缓冲池的 db.r4.2xlarge 实例上运行上述查询的结果。在使用并行查询时,查询运行速度要快得多。

注意

由于计时取决于很多环境因素,因此,您的结果可能会有所不同。请始终执行您自己的性能测试,以便在您自己的环境、工作负载等条件下确认这些结果。

-- Without parallel query +------------+-------------+-------------+----------------+ | l_orderkey | revenue | o_orderdate | o_shippriority | +------------+-------------+-------------+----------------+ | 92511430 | 514726.4896 | 1995-03-06 | 0 | . . | 28840519 | 454748.2485 | 1995-03-08 | 0 | +------------+-------------+-------------+----------------+ 10 rows in set (24 min 49.99 sec)
-- With parallel query +------------+-------------+-------------+----------------+ | l_orderkey | revenue | o_orderdate | o_shippriority | +------------+-------------+-------------+----------------+ | 92511430 | 514726.4896 | 1995-03-06 | 0 | . . | 28840519 | 454748.2485 | 1995-03-08 | 0 | +------------+-------------+-------------+----------------+ 10 rows in set (1 min 49.91 sec)

本节中的很多示例查询使用该 TPC-H 数据集中的表,尤其是具有 2000 万行和以下定义的 PART 表。

+---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | p_partkey | int(11) | NO | PRI | NULL | | | p_name | varchar(55) | NO | | NULL | | | p_mfgr | char(25) | NO | | NULL | | | p_brand | char(10) | NO | | NULL | | | p_type | varchar(25) | NO | | NULL | | | p_size | int(11) | NO | | NULL | | | p_container | char(10) | NO | | NULL | | | p_retailprice | decimal(15,2) | NO | | NULL | | | p_comment | varchar(23) | NO | | NULL | | +---------------+---------------+------+-----+---------+-------+

在您的工作负载条件下试验,以了解各个 SQL 语句是否可以利用并行查询。然后,使用以下监控方法帮助验证在一段时间内在实际工作负载条件下使用并行查询的频率。对于实际工作负载,还存在额外的影响因素,例如,并发限制。

监控并行查询

如果您的 Aurora MySQL 集群使用并行查询,您可能会看到 VolumeReadIOPS 值出现增长。并行查询不使用缓冲池。因此,尽管查询速度很快,但这种优化的处理可能会导致读取操作和相关费用的增加。

除了在 Amazon RDS 控制台中查看指标中所述的 Amazon CloudWatch 指标以外,Aurora 还提供了其他全局状态变量。可以使用这些全局状态变量来帮助监视并行查询执行情况。它们可以让您深入了解为什么优化程序在给定情况下可能使用或不使用并行查询。要访问这些变量,您可以使用 SHOW GLOBAL STATUS 命令。您还可以找到在下面列出的这些变量。

并行查询会话不一定与由数据库执行的查询呈一一对应关系。例如,假设您的查询计划具有两个使用并行查询的步骤。在这种情况下,查询涉及两个并行会话,并且尝试的请求和成功请求的计数器增加 2 个。

在执行 EXPLAIN 语句以试验并行查询时,即使查询没有实际运行,也会看到指定为“未选择”的计数器增加。在生产环境中使用并行查询时,您可以检查“未选择”计数器的增加速度是否比预期速度快。此时,您可以进行调整,以便为您期望的查询运行并行查询。为此,您可以更改集群设置、查询组合、开启并行查询的数据库实例等。

将在数据库实例级别跟踪这些计数器。在连接到不同的终端节点时,您可能会看到不同的指标,因为每个数据库实例运行自己的一组并行查询。如果读取器终端节点在每个会话中连接到不同的数据库实例,您可能也会看到不同的指标。

名称 描述

Aurora_pq_bytes_returned

在并行查询期间传输到头节点的元组数据结构的字节数。除以 16,384 以与 Aurora_pq_pages_pushed_down 进行比较。

Aurora_pq_max_concurrent_requests

可以在该 Aurora 数据库实例上并发运行的最大并行查询会话数。这是一个取决于 Amazon 数据库实例类的固定数字。

Aurora_pq_pages_pushed_down

并行查询避免通过网络传输到头节点的数据页面数量(每个页面具有 16 KiB 的固定大小)。

Aurora_pq_request_attempted

请求的并行查询会话数。该值可能表示每个查询具有多个会话,具体取决于 SQL 结构,如子查询和联接。

Aurora_pq_request_executed

成功运行的并行查询会话数。

Aurora_pq_request_failed

向客户端返回错误的并行查询会话数。在某些情况下,并行查询请求可能会失败,例如,由于在存储层中出现问题。在这些情况下,将使用非并行查询机制重试失败的查询部分。如果重试的查询也失败,则会向客户端返回错误并增加该计数器。

Aurora_pq_request_in_progress

当前运行的并行查询会话数。该数字适用于您连接到的特定 Aurora 数据库实例,而不适用于整个 Aurora 数据库集群。要查看数据库实例是否接近其并发限制,请将该值与 Aurora_pq_max_concurrent_requests 进行比较。

Aurora_pq_request_not_chosen

未选择并行查询以满足查询条件的次数。该值是几个其他更精细的计数器的总和。即使没有实际执行查询,EXPLAIN 语句也可能增加此计数器。

Aurora_pq_request_not_chosen_below_min_rows

由于表中的行数而未选择并行查询的次数。即使没有实际执行查询,EXPLAIN 语句也可能增加此计数器。

Aurora_pq_request_not_chosen_column_bit

由于投影列的列表中的数据类型不受支持而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_column_geometry

由于表具有 GEOMETRY 数据类型的列而使用非并行查询处理路径的并行查询请求数。有关删除此限制的 Aurora MySQL 版本的信息,请参阅 将并行查询集群升级到 Aurora MySQL 版本 3

Aurora_pq_request_not_chosen_column_lob

由于表具有 LOB 数据类型的列或具有(由于声明的长度)而在外部存储的 VARCHAR 列,因此使用非并行查询处理路径的并行查询请求数。有关删除此限制的 Aurora MySQL 版本的信息,请参阅 将并行查询集群升级到 Aurora MySQL 版本 3

Aurora_pq_request_not_chosen_column_virtual

由于表包含虚拟列而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_custom_charset

由于表具有带自定义字符集的列而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_fast_ddl

由于表当前正在被快速 DDL ALTER 语句更改而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_few_pages_outside_buffer_pool

由于没有足够的未缓冲表数据以值得运行并行查询而未选择并行查询的次数,即使缓冲池中的表数据少于 95%。

Aurora_pq_request_not_chosen_full_text_index

由于表具有全文索引而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_high_buffer_pool_pct

由于在缓冲池中具有较高比例的表数据(目前大于 95%)而未选择并行查询的次数。在这些情况下,优化程序确定从缓冲池中读取数据更高效。即使没有实际执行查询,EXPLAIN 语句也可能增加此计数器。

Aurora_pq_request_not_chosen_index_hint

由于查询包含索引提示而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_innodb_table_format

由于表使用不受支持的 InnoDB 行格式,因此使用非并行查询处理路径的并行查询请求数。Aurora 并行查询仅适用于 COMPACTREDUNDANTDYNAMIC 行格式。

Aurora_pq_request_not_chosen_long_trx

由于正在长时间运行的事务中启动查询而使用非并行查询处理路径的并行查询请求数。即使没有实际执行查询,EXPLAIN 语句也可能增加此计数器。

Aurora_pq_request_not_chosen_no_where_clause

由于查询不包含任何 WHERE 子句而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_range_scan

由于查询对索引使用范围扫描而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_row_length_too_long

由于所有列的总组合长度过长而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_small_table

由于表的总大小(由行数和平均行长度确定)而未选择并行查询的次数。即使没有实际执行查询,EXPLAIN 语句也可能增加此计数器。

Aurora_pq_request_not_chosen_temporary_table

由于查询引用了临时表(这些临时表使用不受支持的 MyISAMmemory 表类型)而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_tx_isolation

由于查询使用不受支持的事务隔离级别而使用非并行查询处理路径的并行查询请求数。在读取器数据库实例上,并行查询仅适用于 REPEATABLE READREAD COMMITTED 隔离级别。

Aurora_pq_request_not_chosen_update_delete_stmts

由于查询是 UPDATEDELETE 语句的一部分而使用非并行查询处理路径的并行查询请求数。

Aurora_pq_request_not_chosen_unsupported_access

由于 WHERE 子句不符合并行查询条件而使用非并行查询处理路径的并行查询请求数。如果查询不需要数据密集型扫描,或者查询是 DELETEUPDATE 语句,则会出现该结果。

Aurora_pq_request_not_chosen_unsupported_storage_type

由于 Aurora MySQL 数据库集群未使用支持的 Aurora 集群存储配置,而使用非并行查询处理路径的并行查询请求的数量。此参数适用于 Aurora MySQL 版本 3.04 及更高版本。有关更多信息,请参阅限制

Aurora_pq_request_throttled

由于在特定 Aurora 数据库实例上已运行的最大并发并行查询数而未选择并行查询的次数。

并行查询如何与 SQL 结构一起使用

在下一节中,您可以找到为什么特定 SQL 语句使用或不使用并行查询的更多详细信息。本节还详细介绍了 Aurora MySQL 功能如何与并行查询交互。此信息可以帮助您诊断使用并行查询的集群的性能问题,或了解并行查询如何应用于您的特定工作负载。

确定是否使用并行查询取决于在运行语句时存在的很多因素。因此,某些查询可能始终、从不或仅在某些情况下使用并行查询。

提示

当您在 HTML 中查看这些示例时,可以使用每个代码列表右上角的复制小部件来复制 SQL 代码以便自行尝试。使用复制小部件可避免复制 mysql> 提示行和 -> 延续行周围的多余字符。

EXPLAIN 语句

正如本节中的示例所示,EXPLAIN 语句指示查询的每个阶段当前是否适合运行并行查询。它还指示可以将查询的哪些方面向下推送到存储层。查询计划中的最重要项目如下所示:

  • NULL 列的 key 以外的值表明可以使用索引查找高效地执行查询,而不会运行并行查询。

  • 较小的 rows 列值(不是数百万的值)表明查询没有访问足够的数据以值得运行并行查询。这意味着不太可能使用并行查询。

  • Extra 列显示是否需要使用并行查询。该输出类似于以下示例。

    Using parallel query (A columns, B filters, C exprs; D extra)

    columns 数字表示查询块中引用的列数。

    filters 数字表示 WHERE 谓词数,它表示列值与常数的简单比较。比较的结果可以是相等、不相等,或者是处于某个范围内。Aurora 可以十分高效地并行处理此类谓词。

    exprs 数字表示也可以并行处理但不像筛选条件那样高效的表达式数,例如,函数调用、运算符或其他表达式。

    extra 数字表示无法向下推送并由头节点执行的表达式数。

例如,请考虑以下 EXPLAIN 输出。

mysql> explain select p_name, p_mfgr from part -> where p_brand is not null -> and upper(p_type) is not null -> and round(p_retailprice) is not null; +----+-------------+-------+...+----------+----------------------------------------------------------------------------+ | id | select_type | table |...| rows | Extra | +----+-------------+-------+...+----------+----------------------------------------------------------------------------+ | 1 | SIMPLE | part |...| 20427936 | Using where; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) | +----+-------------+-------+...+----------+----------------------------------------------------------------------------+

Extra 列中的信息显示从每行中提取 5 列以计算查询条件并构建结果集。一个 WHERE 谓词涉及一个筛选条件,即,在 WHERE 子句中直接测试的列。两个 WHERE 子句需要计算更复杂的表达式,在这种情况下,将涉及函数调用。0 extra 字段确认 WHERE 子句中的所有操作将作为并行查询处理的一部分向下推送到存储层。

如果未选择并行查询,您通常可以从 EXPLAIN 输出的其他列中推断出原因。例如,rows 值可能太小,或者 possible_keys 列可能表示查询可以使用索引查找,而不是数据密集型扫描。以下示例显示了一个查询,其中优化程序可以估计查询将仅扫描少量的行。它根据主键的特性执行此操作。在这种情况下,不需要运行并行查询。

mysql> explain select count(*) from part where p_partkey between 1 and 100; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | part | range | PRIMARY | PRIMARY | 4 | NULL | 99 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

显示是否使用并行查询的输出考虑了在运行 EXPLAIN 语句时存在的所有因素。在实际运行查询时,如果在此期间情况发生变化,优化程序可能会做出不同的选择。例如,EXPLAIN 可能会报告语句将使用并行查询。但在以后实际运行查询时,它可能会根据此时的情况不使用并行查询。此类条件可以包括同时运行其他几个并行查询。此类情况还可能包括从表中删除行,创建新的索引,在打开事务中经过的时间太长,等等。

WHERE 子句

要使查询使用并行查询优化,它必须 包含一个 WHERE 子句。

并行查询优化加快了 WHERE 子句中使用的多种类型的表达式的速度:

  • 列值与常数的简单比较,称为筛选条件。这些比较从向下推送到存储层中受益最多。将在 EXPLAIN 输出中报告查询中的筛选条件表达式数。

  • 如果可能,WHERE 子句中的其他类型的表达式也会向下推送到存储层。将在 EXPLAIN 输出中报告查询中的此类表达式数。这些表达式可能是函数调用、LIKE 运算符、CASE 表达式,等等。

  • 目前,并行查询不会向下推送某些函数和运算符。查询中的此类表达式数将在 extra 输出中报告为 EXPLAIN 计数器。查询的其余部分仍然可以使用并行查询。

  • 虽然不会向下推送选择列表中的表达式,但包含此类函数的查询仍然可以从并行查询的中间结果的网络流量减少中受益。例如,在选择列表中调用聚合函数的查询可以从并行查询中受益,即使不向下推送聚合函数。

例如,以下查询执行全表扫描并处理 P_BRAND 列的所有值。不过,它不使用并行查询,因为查询不包含任何 WHERE 子句。

mysql> explain select count(*), p_brand from part group by p_brand; +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+ | 1 | SIMPLE | part | ALL | NULL | NULL | NULL | NULL | 20427936 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

相反,以下查询包括筛选结果的 WHERE 谓词,因此,可以应用并行查询:

mysql> explain select count(*), p_brand from part where p_name is not null -> and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 -> group by p_brand; +----+...+----------+-------------------------------------------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+----------+-------------------------------------------------------------------------------------------------------------+ | 1 |...| 20427936 | Using where; Using temporary; Using filesort; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) | +----+...+----------+-------------------------------------------------------------------------------------------------------------+

如果优化程序估计查询块的返回行数很少,则不会在该查询块中使用并行查询。以下示例说明了一种主键列上的大于运算符应用于数百万行的情况,这会导致使用并行查询。估计反向小于测试仅应用于几行,而不使用并行查询。

mysql> explain select count(*) from part where p_partkey > 10; +----+...+----------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+----------+----------------------------------------------------------------------------+ | 1 |...| 20427936 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) | +----+...+----------+----------------------------------------------------------------------------+ mysql> explain select count(*) from part where p_partkey < 10; +----+...+------+--------------------------+ | id |...| rows | Extra | +----+...+------+--------------------------+ | 1 |...| 9 | Using where; Using index | +----+...+------+--------------------------+

数据定义语言 (DDL)

在 Aurora MySQL 版本 2 中,并行查询仅适用于没有挂起的快速数据定义语言(DDL)操作的表。在 Aurora MySQL 版本 3 中,您可以在表上与即时 DDL 操作同时使用并行查询。

Aurora MySQL 版本 3 中的即时 DDL 取代了 Aurora MySQL 版本 2 中的快速 DDL 功能。有关即时 DDL 的信息,请参阅 即时 DDL(Aurora MySQL 版本 3)

列数据类型

在 Aurora MySQL 版本 3 中,并行查询可以处理包含具有数据类型 TEXTBLOBJSONGEOMETRY 的列的表。它也可以使用最大声明长度超过 768 字节的 VARCHARCHAR 列。如果您的查询引用任何包含此类大型对象类型的列,则检索它们的额外工作确实会增加查询处理的一些开销。在这种情况下,请检查查询是否可以省略对这些列的引用。如果没有,运行基准测试以确认在开启或关闭并行查询的情况下,此类查询是否更快。

在 Aurora MySQL 版本 2 中,并行查询对于大型对象类型有以下限制:

  • TEXTBLOBJSONGEOMETRY 数据类型不支持并行查询。引用这些类型的任何列的查询无法使用并行查询。

  • 可变长度列(VARCHARCHAR 数据类型)与并行查询兼容,最大声明长度最多为 768 字节。如果查询引用的任何列具有使用更长最大长度声明的类型,则无法使用并行查询。对于使用多字节字符集的列,字节限制将字符集中的最大字节数考虑在内。例如,对于字符集 utf8mb4(最大字符长度为 4 字节),VARCHAR(192) 列与并行查询兼容,但 VARCHAR(193) 列不兼容。

分区表

在 Aurora MySQL 版本 3 中,您可以将分区表与并行查询结合使用。由于分区表在内部表示为多个较小的表,因此对非分区表使用并行查询的查询可能不会对相同的分区表使用并行查询。Aurora MySQL 考虑每个分区是否足以符合并行查询优化条件,而不是评估整个表的大小。检查是否Aurora_pq_request_not_chosen_small_table如果分区表上的查询在预期时不使用并行查询,则状态变量将递增。

例如,考虑用 PARTITION BY HASH (column) PARTITIONS 2 分区的一个表和用 PARTITION BY HASH (column) PARTITIONS 10 分区的另一个表。在有两个分区的表中,分区的大小是有十个分区的表的五倍。因此,并行查询更有可能用于对分区较少的表进行查询。在以下示例中,表 PART_BIG_PARTITIONS 有两个分区,PART_SMALL_PARTITIONS 有十个分区。在数据相同的情况下,并行查询更有可能用于大分区较少的表。

mysql> explain select count(*), p_brand from part_big_partitions where p_name is not null -> and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand; +----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+ | id | select_type | table | partitions | Extra | +----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | part_big_partitions | p0,p1 | Using where; Using temporary; Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra; 1 group-bys, 1 aggrs) | +----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+ mysql> explain select count(*), p_brand from part_small_partitions where p_name is not null -> and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand; +----+-------------+-----------------------+-------------------------------+------------------------------+ | id | select_type | table | partitions | Extra | +----+-------------+-----------------------+-------------------------------+------------------------------+ | 1 | SIMPLE | part_small_partitions | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | Using where; Using temporary | +----+-------------+-----------------------+-------------------------------+------------------------------+

聚合函数、GROUP BY 子句和 HAVING 子句

涉及聚合函数的查询通常是并行查询的理想之选,因为它们涉及扫描大型表中的大量行。

在 Aurora MySQL 3 中,并行查询可以优化选择列表和 HAVING 子句中的聚合函数调用。

在 Aurora MySQL 3 之前,选择列表或 HAVING 子句中的聚合函数调用不会向下推送到存储层。不过,并行查询仍然可以使用聚合函数提高此类查询的性能。为此,它先在存储层中从原始数据页面中并行提取列值。然后,它以紧凑元组格式将这些值发回到头节点,而不是作为完整数据页面。与往常一样,查询需要具有至少一个 WHERE 谓词才能激活并行查询。

以下简单示例说明了可以从并行查询中受益的聚合查询种类。它们以紧凑形式将中间结果返回到头节点以及/或者从中间结果中筛选不匹配的行。

mysql> explain select sql_no_cache count(distinct p_brand) from part where p_mfgr = 'Manufacturer#5'; +----+...+----------------------------------------------------------------------------+ | id |...| Extra | +----+...+----------------------------------------------------------------------------+ | 1 |...| Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) | +----+...+----------------------------------------------------------------------------+ mysql> explain select sql_no_cache p_mfgr from part where p_retailprice > 1000 group by p_mfgr having count(*) > 100; +----+...+-------------------------------------------------------------------------------------------------------------+ | id |...| Extra | +----+...+-------------------------------------------------------------------------------------------------------------+ | 1 |...| Using where; Using temporary; Using filesort; Using parallel query (3 columns, 0 filters, 1 exprs; 0 extra) | +----+...+-------------------------------------------------------------------------------------------------------------+

WHERE 子句中的函数调用

Aurora 可以将并行查询优化应用于 WHERE 子句中的大多数内置函数调用。并行处理这些函数调用将会从头节点中卸载一些 CPU 负载。通过在最早的查询阶段并行计算谓词函数,有助于 Aurora 最大限度减少在后续阶段传输和处理的数据量。

目前,并行处理不适用于选择列表中的函数调用。头节点计算这些函数,即使在 WHERE 子句中出现相同的函数调用。来自相关列的原始值包含在从存储节点发回到头节点的元组中。头节点执行任何转换(如 UPPERCONCATENATE 等)以生成结果集的最终值。

在以下示例中,并行查询将并行处理对 LOWER 的调用,因为它出现在 WHERE 子句中。并行查询不会影响对 SUBSTRUPPER 的调用,因为它们出现在选择列表中。

mysql> explain select sql_no_cache distinct substr(upper(p_name),1,5) from part -> where lower(p_name) like '%cornflower%' or lower(p_name) like '%goldenrod%'; +----+...+---------------------------------------------------------------------------------------------+ | id |...| Extra | +----+...+---------------------------------------------------------------------------------------------+ | 1 |...| Using where; Using temporary; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) | +----+...+---------------------------------------------------------------------------------------------+

相同的注意事项适用于其他表达式,例如,CASE 表达式或 LIKE 运算符。例如,以下示例显示并行查询计算 CASE 子句中的 LIKE 表达式和 WHERE 运算符。

mysql> explain select p_mfgr, p_retailprice from part -> where p_retailprice > case p_mfgr -> when 'Manufacturer#1' then 1000 -> when 'Manufacturer#2' then 1200 -> else 950 -> end -> and p_name like '%vanilla%' -> group by p_retailprice; +----+...+-------------------------------------------------------------------------------------------------------------+ | id |...| Extra | +----+...+-------------------------------------------------------------------------------------------------------------+ | 1 |...| Using where; Using temporary; Using filesort; Using parallel query (4 columns, 0 filters, 2 exprs; 0 extra) | +----+...+-------------------------------------------------------------------------------------------------------------+

LIMIT 子句

目前,并行查询不用于包含 LIMIT 子句的任何查询块。并行查询可能仍用于具有 GROUP by、ORDER BY 或联接的早期查询阶段。

比较运算符

优化程序估计要扫描的行数以计算比较运算符,并根据该估计确定是否使用并行查询。

下面的第一个示例显示,可以在没有并行查询的情况下高效地执行与主键列的相等比较。下面的第二个示例显示,与未编制索引的列进行类似比较需要扫描数百万行,因此,可以从并行查询中受益。

mysql> explain select * from part where p_partkey = 10; +----+...+------+-------+ | id |...| rows | Extra | +----+...+------+-------+ | 1 |...| 1 | NULL | +----+...+------+-------+ mysql> explain select * from part where p_type = 'LARGE BRUSHED BRASS'; +----+...+----------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+----------+----------------------------------------------------------------------------+ | 1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) | +----+...+----------+----------------------------------------------------------------------------+

同样的注意事项适用于不等于测试和范围比较,例如小于、大于或等于或 BETWEEN。优化程序估计要扫描的行数,并根据 I/O 总量确定是否值得运行并行查询。

Joins

大型表的联接查询通常涉及数据密集型操作,这些操作将从并行查询优化中受益。目前,不会并行处理多个表之间的列值比较(即,联接谓词本身)。不过,并行查询可以向下推送其他联接阶段的一些内部处理,例如,在哈希联接期间构建 Bloom 筛选条件。即使没有 WHERE 子句,并行查询也可以应用于联接查询。因此,对于需要使用 WHERE 子句以使用并行查询的规则,联接查询是一种例外情况。

将计算联接处理的每个阶段以检查它是否符合并行查询条件。如果多个阶段可以使用并行查询,将按顺序执行这些阶段。因此,每个联接查询在并发限制中计为单个并行查询会话。

例如,如果联接查询包含 WHERE 谓词以筛选联接的某个表中的行,该筛选选项可以使用并行查询。作为另一个示例,假设联接查询使用哈希联接机制,例如,将大表与小表联接在一起。在这种情况下,用于生成 Bloom 筛选条件数据结构的表扫描或许可以使用并行查询。

注意

并行查询通常用于受益于哈希联接优化的各种资源密集型查询。开启哈希联接优化的方法取决于 Aurora MySQL 版本。有关各版本的详细信息,请参阅 为并行查询集群开启哈希联接。有关如何有效使用哈希联接的信息,请参阅 使用哈希联接优化大型 Aurora MySQL 联接查询

mysql> explain select count(*) from orders join customer where o_custkey = c_custkey; +----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+ | id |...| table | type | possible_keys | key |...| rows | Extra | +----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+ | 1 |...| customer | index | PRIMARY | c_nationkey |...| 15051972 | Using index | | 1 |...| orders | ALL | o_custkey | NULL |...| 154545408 | Using join buffer (Hash Join Outer table orders); Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) | +----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

对于使用嵌套循环机制的联接查询,最外层的嵌套循环块可能会使用并行查询。是否使用并行查询取决于与往常相同的因素,例如,是否在 WHERE 子句中存在其他筛选条件。

mysql> -- Nested loop join with extra filter conditions can use parallel query. mysql> explain select count(*) from part, partsupp where p_partkey != ps_partkey and p_name is not null and ps_availqty > 0; +----+-------------+----------+...+----------+----------------------------------------------------------------------------+ | id | select_type | table |...| rows | Extra | +----+-------------+----------+...+----------+----------------------------------------------------------------------------+ | 1 | SIMPLE | part |...| 20427936 | Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) | | 1 | SIMPLE | partsupp |...| 78164450 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+----------+...+----------+----------------------------------------------------------------------------+

子查询

外部查询块和内部子查询块可能分别使用并行查询,也可能不使用。它们是否这样做取决于针对每个块的表、 WHERE 子句等的常用特征。例如,以下查询在子查询块中使用并行查询,但在外部块中不使用并行查询。

mysql> explain select count(*) from part where --> p_partkey < (select max(p_partkey) from part where p_name like '%vanilla%'); +----+-------------+...+----------+----------------------------------------------------------------------------+ | id | select_type |...| rows | Extra | +----+-------------+...+----------+----------------------------------------------------------------------------+ | 1 | PRIMARY |...| NULL | Impossible WHERE noticed after reading const tables | | 2 | SUBQUERY |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) | +----+-------------+...+----------+----------------------------------------------------------------------------+

目前,关联子查询无法使用并行查询优化。

联合

对于 UNION 的每个部分,根据表和 WHERE 子句等的通常特性,UNION 查询中的每个查询块可能会使用或不使用并行查询。

mysql> explain select p_partkey from part where p_name like '%choco_ate%' -> union select p_partkey from part where p_name like '%vanil_a%'; +----+----------------+...+----------+----------------------------------------------------------------------------+ | id | select_type |...| rows | Extra | +----+----------------+...+----------+----------------------------------------------------------------------------+ | 1 | PRIMARY |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) | | 2 | UNION |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) | | NULL | UNION RESULT | <union1,2> |...| NULL | Using temporary | +----+--------------+...+----------+----------------------------------------------------------------------------+
注意

查询中的每个 UNION 子句是按顺序运行的。即使查询包含的多个阶段均使用并行查询,它在任何时间也仅运行单个并行查询。因此,甚至是复杂的多阶段查询也仅在并发并行查询限制中计为 1 个。

视图

优化程序将视图作为使用基础表的较长查询以重写任何查询。因此,无论表引用是视图还是实际表,并行查询的工作方式都是相同的。有关在查询中是否使用并行查询以及向下推送哪些部分的所有相同注意事项适用于最终重写的查询。

例如,以下查询计划显示通常不使用并行查询的视图定义。在使用额外的 WHERE 子句查询视图时,Aurora MySQL 使用并行查询。

mysql> create view part_view as select * from part; mysql> explain select count(*) from part_view where p_partkey is not null; +----+...+----------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+----------+----------------------------------------------------------------------------+ | 1 |...| 20427936 | Using where; Using parallel query (1 columns, 0 filters, 0 exprs; 1 extra) | +----+...+----------+----------------------------------------------------------------------------+

数据操作语言 (DML) 语句

如果 INSERT 部分满足并行查询的其他条件,则 SELECT 语句可以在 SELECT 处理阶段中使用并行查询。

mysql> create table part_subset like part; mysql> explain insert into part_subset select * from part where p_mfgr = 'Manufacturer#1'; +----+...+----------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+----------+----------------------------------------------------------------------------+ | 1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) | +----+...+----------+----------------------------------------------------------------------------+
注意

通常,在执行 INSERT 语句后,新插入的行的数据将位于缓冲池中。因此,在插入大量行后,表可能不符合并行查询条件。以后,在正常运行期间从缓冲池中移出数据后,针对表的查询可能会再次开始使用并行查询。

CREATE TABLE AS SELECT 语句不使用并行查询,即使该语句的 SELECT 部分符合并行查询条件。该语句的 DDL 特性导致它与并行查询处理不兼容。相反,在 INSERT ... SELECT 语句中,SELECT 部分可以使用并行查询。

DELETEUPDATE 语句中从不使用并行查询,而无论表的大小和 WHERE 子句中的谓词如何。

mysql> explain delete from part where p_name is not null; +----+-------------+...+----------+-------------+ | id | select_type |...| rows | Extra | +----+-------------+...+----------+-------------+ | 1 | SIMPLE |...| 20427936 | Using where | +----+-------------+...+----------+-------------+

事务和锁定

您可以在 Aurora 主实例上使用所有隔离级别。

在 Aurora 读取器数据库实例上,并行查询适用于 REPEATABLE READ 隔离级别下执行的语句。Aurora MySQL 版本 2.09 或更高版本也可以在读取器数据库实例上使用 READ COMMITTED 隔离级别。REPEATABLE READ 是 Aurora 读取器数据库实例的原定设置隔离级别。要在读取器数据库实例上使用 READ COMMITTED 隔离级别,需要在会话级别设置 aurora_read_replica_read_committed 配置选项。读取器实例的 READ COMMITTED 隔离级别符合 SQL 标准行为。但是,与查询在写入器实例上使用 READ COMMITTED 隔离级别时相比,对读取器实例的隔离没有那么严格。

有关 Aurora 隔离级别的更多信息,特别是写入器实例与读取器实例之间的 READ COMMITTED 区别,请参阅 Aurora MySQL 隔离级别

在较大的事务完成后,表统计数据可能会过时。这种过时的统计数据可能要求使用 ANALYZE TABLE 语句,然后 Aurora 才能准确估计行数。大型 DML 语句可能还会将大部分的表数据放入缓冲池中。将该数据放入缓冲池可能会导致不常为该表选择并行查询,直到将数据从池中移出。

如果您的会话位于长时间运行的事务中(默认为 10 分钟),则该会话中的其他查询不会使用并行查询。在单个长时间运行的查询期间,也可能会发生超时。如果查询在并行查询处理开始之前运行的时间超过最大间隔(当前为 10 分钟),则可能会发生这种超时。

您可以在执行临时(一次)查询的 autocommit=1 会话中设置 mysql,以降低意外启动长时间运行的事务的可能性。甚至针对表的 SELECT 语句也可以创建读取视图以开始运行事务。读取视图 是一个用于后续查询的一致数据集,将在提交事务之前保留该数据集。在 Aurora 中使用 JDBC 或 ODBC 应用程序时,也要注意该限制,因为此类应用程序可能会在禁用 autocommit 设置的情况下运行。

以下示例显示对表运行查询如何创建一个读取视图以隐式开始运行事务(在禁用了 autocommit 设置的情况下)。稍后运行的查询仍然可以使用并行查询。不过,在暂停几分钟后,查询不再符合并行查询条件。如果将事务以 COMMITROLLBACK 结尾,将恢复并行查询条件。

mysql> set autocommit=0; mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0; +----+...+---------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+---------+----------------------------------------------------------------------------+ | 1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) | +----+...+---------+----------------------------------------------------------------------------+ mysql> select sleep(720); explain select sql_no_cache count(*) from part where p_retailprice > 10.0; +------------+ | sleep(720) | +------------+ | 0 | +------------+ 1 row in set (12 min 0.00 sec) +----+...+---------+-------------+ | id |...| rows | Extra | +----+...+---------+-------------+ | 1 |...| 2976129 | Using where | +----+...+---------+-------------+ mysql> commit; mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0; +----+...+---------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+---------+----------------------------------------------------------------------------+ | 1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) | +----+...+---------+----------------------------------------------------------------------------+

要查看查询由于位于长时间运行的事务中而不符合并行查询条件的次数,请检查 Aurora_pq_request_not_chosen_long_trx 状态变量。

mysql> show global status like '%pq%trx%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | Aurora_pq_request_not_chosen_long_trx | 4 | +-------------------------------+-------+

获取锁定的任何 SELECT 语句(如 SELECT FOR UPDATESELECT LOCK IN SHARE MODE 语法)无法使用并行查询。

并行查询可以用于 LOCK TABLES 语句锁定的表。

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055'; +----+...+-----------+----------------------------------------------------------------------------+ | id |...| rows | Extra | +----+...+-----------+----------------------------------------------------------------------------+ | 1 |...| 154545408 | Using where; Using parallel query (3 columns, 1 filters, 0 exprs; 0 extra) | +----+...+-----------+----------------------------------------------------------------------------+ mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055' for update; +----+...+-----------+-------------+ | id |...| rows | Extra | +----+...+-----------+-------------+ | 1 |...| 154545408 | Using where | +----+...+-----------+-------------+

B 树索引

根据每个列的数据特性,ANALYZE TABLE 语句收集的统计数据可以帮助优化程序确定何时使用并行查询或索引查找。在执行对表中的数据进行重大更改的 DML 操作后,请运行 ANALYZE TABLE 以将统计数据保持最新状态。

如果索引查找可以在没有数据密集型扫描的情况下高效地执行查询,则 Aurora 可能会使用索引查找。这样做可以避免并行查询处理的开销。可以在任何 Aurora 数据库集群上同时运行的并行查询数也存在并发限制。确保使用最佳实践为表编制索引,以便最频繁和最高并发性的查询使用索引查找。

全文搜索 (FTS) 索引

目前,并行查询不用于包含全文搜索索引的表,而无论查询是引用此类索引列,还是使用 MATCH 运算符。

虚拟列

目前,并行查询不适用于包含虚拟列的表,无论查询是否引用任何虚拟列。

内置缓存机制

Aurora 包括内置缓存机制,即,缓冲池和查询缓存。Aurora 优化程序在这些缓存机制和并行查询之间进行选择,具体取决于哪一个对特定查询最有效。

在并行查询筛选行并转换和提取列值时,数据将作为元组而不是数据页面传回到头节点。因此,运行并行查询不会将任何页面添加到缓冲池中,也不会移出已位于缓冲池中的页面。

Aurora 会检查位于缓冲池中的表数据页数,以及该数字所代表的表数据比例。Aurora 会通过该信息确定使用并行查询是否更加高效,并且决定是否需要绕过缓冲池中的数据。或者,Aurora 可能使用非并行查询处理路径,这会使用缓冲池中缓存的数据。缓存哪些页面以及数据密集型查询如何影响缓存和移出取决于与缓冲池相关的配置设置。因此,很难预测任何特定查询是否使用并行查询,因为这取决于缓冲池中不断变化的数据。

此外,Aurora 对并行查询施加并发限制。由于并非每个查询都使用并行查询,多个查询同时访问的表通常将大部分数据放在缓冲池中。因此,Aurora 通常不会选择这些表以运行并行查询。

在同一个表上运行一系列非并行查询时,由于数据没有位于缓冲池中,第一个查询的速度可能很慢。由于缓冲池现已“预热”,第二次和后续查询要快得多。并行查询通常从针对表的第一个查询开始就具有一致的性能。在进行性能测试时,将使用冷缓冲池和热缓冲池对非并行查询进行基准测试。在某些情况下,使用热缓冲池的结果可能与并行查询时间接近。在这些情况下,请考虑诸如针对该表进行查询的频率等因素。还要考虑是否值得将该表的数据保留在缓冲池中。

在提交相同的查询以及未更改基础表数据时,查询缓存可以避免重新运行查询。并行查询功能优化的查询可以放入查询缓存中,实际上可以在再次运行时立即运行它们。

注意

在进行性能比较时,查询缓存可能会生成虚假的低计时数。因此,在与基准测试类似的情况下,您可以使用 sql_no_cache 提示。该提示可以防止从查询缓存中提供结果,即使以前运行了相同的查询。该提示直接位于查询中的 SELECT 语句后面。本主题中的很多并行查询示例包括该提示,以使开启和关闭并行查询的查询版本具有类似的查询时间。

在生产环境中使用并行查询时,请确保从源中删除该提示。

优化程序提示

控制优化程序的另一种方法是使用优化程序提示,可以在单个语句中指定优化程序提示。例如,您可以对语句中的一个表开启优化,然后对另一个表关闭优化。有关这些提示的更多信息,请参阅《MySQL 参考手册》中的优化程序提示

您可以将 SQL 提示与 Aurora MySQL 查询结合使用来微调性能。您还可以使用提示来防止重要查询的执行计划由于不可预知的条件而发生变化。

我们扩展了 SQL 提示功能,以帮助您控制查询计划的优化程序选择。这些提示适用于使用并行查询优化的查询。有关更多信息,请参阅Aurora MySQL 提示

MyISAM 临时表

并行查询优化仅适用于 InnoDB 表。由于 Aurora MySQL 在后台的临时表中使用 MyISAM,因此,涉及临时表的内部查询阶段从不使用并行查询。这些查询阶段由 Using temporary 输出中的 EXPLAIN 指示。