排除 Aurora MySQL 数据库的查询性能故障 - Amazon Aurora
了解查询所花的时间查看查询优化器设置

排除 Aurora MySQL 数据库的查询性能故障

MySQL 通过系统变量（它们影响评估查询计划的方式）、可切换的优化、优化器和索引提示以及优化器成本模型，来提供查询优化器控制。这些数据点不仅有助于比较不同的 MySQL 环境，还可以用来比较以前的查询执行计划和当前的执行计划，以及随时了解 MySQL 查询的总体执行情况。

查询性能取决于许多因素，包括执行计划、表架构和大小、统计信息、资源、索引和参数配置。查询调优要求识别瓶颈并优化执行路径。

  • 找到查询的执行计划，并检查查询是否正在使用适当的索引。您可以通过使用 EXPLAIN 和查看每个计划的详细信息来优化查询。

  • Aurora MySQL 版本 3（与 MySQL 8.0 社区版兼容）使用 EXPLAIN ANALYZE 语句。EXPLAIN ANALYZE 语句是一个分析工具，可显示 MySQL 在查询的哪些方面花费了时间以及原因。借助 EXPLAIN ANALYZE，Aurora MySQL 计划、准备和运行查询，同时计算行数并测量在执行计划的各个点花费的时间。查询完成后，EXPLAIN ANALYZE 输出计划及其测量值，而不是查询结果。

  • 通过使用 ANALYZE 语句使架构统计信息保持更新。查询优化器有时会因为统计信息过时而选择较差的执行计划。这可能会导致查询性能不佳，因为表和索引的基数估计值都不准确。innodb_table_stats 表的 last_update 列显示了上次更新架构统计信息的时间，这可以很好地表明是否“过时”。

  • 可能会出现其它问题，例如数据的分布偏斜，而表基数没有考虑这些问题。有关更多信息，请参阅 MySQL 文档中的 Estimating ANALYZE TABLE complexity for InnoDB tables 和 Histogram statistics in MySQL。

了解查询所花的时间

以下是确定查询所花时间的方法：

分析

默认情况下，分析功能处于禁用状态。启用分析功能，然后运行慢速查询并查看其分析结果。

SET profiling = 1;
Run your query.
SHOW PROFILE;

  1. 确定花费时间最多的阶段。根据 MySQL 文档中的常规线程状态，读取和处理 SELECT 语句的行通常是在给定查询的生命周期内运行时间最长的状态。您可以使用 EXPLAIN 语句来了解 MySQL 如何运行此查询。

  2. 查看慢速查询日志来评估 rows_examined 和 rows_sent，来确保每个环境中的工作负载相似。有关更多信息，请参阅Aurora MySQL 数据库日志记录。

  3. 对属于已识别查询的一部分的表运行以下命令：

    SHOW TABLE STATUS\G;

  4. 在每个环境上运行查询之前和之后捕获以下输出：

    SHOW GLOBAL STATUS;

  5. 在每个环境上运行以下命令，来查看是否有任何其它查询/会话影响此示例查询的性能。

    SHOW FULL PROCESSLIST;

SHOW ENGINE INNODB STATUS\G;

    有时，当服务器上的资源繁忙时，它会影响服务器上的每个其它操作，包括查询。您还可以在运行查询时定期捕获信息，或者设置 cron 任务来按有用的间隔捕获信息。

性能模式

性能模式提供有关服务器运行时性能的有用信息，同时对该性能的影响最小。这与提供有关数据库实例的模式信息的 information_schema 不同。有关更多信息，请参阅Aurora MySQL 上性能详情的性能架构概述。

查询优化器跟踪

为了理解为什么选择特定的查询计划来执行，您可以设置 optimizer_trace 来访问 MySQL 查询优化器。

运行优化器跟踪，来显示有关优化器可用的所有路径及其选择的大量信息。

SET SESSION OPTIMIZER_TRACE="enabled=on"; 
SET optimizer_trace_offset=-5, optimizer_trace_limit=5;

-- Run your query.
SELECT * FROM table WHERE x = 1 AND y = 'A';

-- After the query completes:
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION OPTIMIZER_TRACE="enabled=off";

查看查询优化器设置

Aurora MySQL 版本 3（与 MySQL 8.0 社区版兼容）与 Aurora MySQL 版本 2（与 MySQL 5.7 社区版兼容）相比，前者具有许多与优化器相关的更改。如果您对于 optimizer_switch 有一些自定义值，我们建议您查看默认值的差异，并设置最适合您的工作负载的 optimizer_switch 值。我们还建议您测试可用于 Aurora MySQL 版本 3 的选项，来检查查询的执行情况。

注意

Aurora MySQL 版本 3 对于 innodb_stats_persistent_sample_pages 参数使用社区默认值 20。

您可以使用以下命令来显示 optimizer_switch 值：

SELECT @@optimizer_switch\G;

下表显示 Aurora MySQL 版本 2 和 3 的默认 optimizer_switch 值。

设置 Aurora MySQL 版本 2 Aurora MySQL 版本 3
batched_key_access off off
block_nested_loop on on
condition_fanout_filter on on
derived_condition_pushdown – on
derived_merge on on
duplicateweedout on on
engine_condition_pushdown on on
firstmatch on on
hash_join off on
hash_join_cost_based on –
hypergraph_optimizer – off
index_condition_pushdown on on
index_merge on on
index_merge_intersection on on
index_merge_sort_union on on
index_merge_union on on
loosescan on on
materialization on on
mrr on on
mrr_cost_based on on
prefer_ordering_index on on
semijoin on on
skip_scan – on
subquery_materialization_cost_based on on
subquery_to_derived – off
use_index_extensions on on
use_invisible_indexes – off

有关更多信息，请参阅 MySQL 文档中的 Switchable optimizations (MySQL 5.7) 和 Switchable optimizations (MySQL 8.0) 。