# 排除 Aurora MySQL 数据库的查询性能故障
MySQL 通过系统变量（它们影响评估查询计划的方式）、可切换的优化、优化器和索引提示以及优化器成本模型，来提供[查询优化器控制](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)。这些数据点不仅有助于比较不同的 MySQL 环境，还可以用来比较以前的查询执行计划和当前的执行计划，以及随时了解 MySQL 查询的总体执行情况。
查询性能取决于许多因素，包括执行计划、表架构和大小、统计信息、资源、索引和参数配置。查询调优要求识别瓶颈并优化执行路径。
+ 找到查询的执行计划，并检查查询是否正在使用适当的索引。您可以通过使用 `EXPLAIN` 和查看每个计划的详细信息来优化查询。
+ Aurora MySQL 版本 3（与 MySQL 8.0 社区版兼容）使用 `EXPLAIN ANALYZE` 语句。`EXPLAIN ANALYZE` 语句是一个分析工具，可显示 MySQL 在查询的哪些方面花费了时间以及原因。借助 `EXPLAIN ANALYZE`，Aurora MySQL 计划、准备和运行查询，同时计算行数并测量在执行计划的各个点花费的时间。查询完成后，`EXPLAIN ANALYZE` 输出计划及其测量值，而不是查询结果。
+ 通过使用 `ANALYZE` 语句使架构统计信息保持更新。查询优化器有时会因为统计信息过时而选择较差的执行计划。这可能会导致查询性能不佳，因为表和索引的基数估计值都不准确。[innodb\$1table\$1stats](https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html#innodb-persistent-stats-tables) 表的 `last_update` 列显示了上次更新架构统计信息的时间，这可以很好地表明是否“过时”。
+ 可能会出现其它问题，例如数据的分布偏斜，而表基数没有考虑这些问题。有关更多信息，请参阅 MySQL 文档中的 [Estimating ANALYZE TABLE complexity for InnoDB tables](https://dev.mysql.com/doc/refman/8.0/en/innodb-analyze-table-complexity.html) 和 [Histogram statistics in MySQL](https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/)。
## 了解查询所花的时间
以下是确定查询所花时间的方法：
+ [分析](https://dev.mysql.com/doc/refman/8.0/en/show-profile.html)
+ [性能模式](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html)
+ [查询优化器](https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html)
**分析**
默认情况下，分析功能处于禁用状态。启用分析功能，然后运行慢速查询并查看其分析结果。
```
SET profiling = 1;
Run your query.
SHOW PROFILE;
```
1. 确定花费时间最多的阶段。根据 MySQL 文档中的[常规线程状态](https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html)，读取和处理 `SELECT` 语句的行通常是在给定查询的生命周期内运行时间最长的状态。您可以使用 `EXPLAIN` 语句来了解 MySQL 如何运行此查询。
1. 查看慢速查询日志来评估 `rows_examined` 和 `rows_sent`，来确保每个环境中的工作负载相似。有关更多信息，请参阅 [Aurora MySQL 数据库日志记录](aurora-mysql-troubleshooting-logging.md)。
1. 对属于已识别查询的一部分的表运行以下命令：
```
SHOW TABLE STATUS\G;
```
1. 在每个环境上运行查询之前和之后捕获以下输出：
```
SHOW GLOBAL STATUS;
```
1. 在每个环境上运行以下命令，来查看是否有任何其它查询/会话影响此示例查询的性能。
```
SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
```
有时，当服务器上的资源繁忙时，它会影响服务器上的每个其它操作，包括查询。您还可以在运行查询时定期捕获信息，或者设置 `cron` 任务来按有用的间隔捕获信息。
**性能模式**
性能模式提供有关服务器运行时性能的有用信息，同时对该性能的影响最小。这与提供有关数据库实例的模式信息的 `information_schema` 不同。有关更多信息，请参阅 [Aurora MySQL 上性能详情的性能架构概述](USER_PerfInsights.EnableMySQL.md)。
**查询优化器跟踪**
为了理解为什么选择特定的[查询计划来执行](https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html)，您可以设置 `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\$1stats\$1persistent\$1sample\$1pages](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_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\$1key\$1access | off | off |
| block\$1nested\$1loop | on | on |
| condition\$1fanout\$1filter | on | on |
| derived\$1condition\$1pushdown | – | on |
| derived\$1merge | on | on |
| duplicateweedout | on | on |
| engine\$1condition\$1pushdown | on | on |
| firstmatch | on | on |
| hash\$1join | off | on |
| hash\$1join\$1cost\$1based | on | – |
| hypergraph\$1optimizer | – | off |
| index\$1condition\$1pushdown | on | on |
| index\$1merge | on | on |
| index\$1merge\$1intersection | on | on |
| index\$1merge\$1sort\$1union | on | on |
| index\$1merge\$1union | on | on |
| loosescan | on | on |
| materialization | on | on |
| mrr | on | on |
| mrr\$1cost\$1based | on | on |
| prefer\$1ordering\$1index | on | on |
| semijoin | on | on |
| skip\$1scan | – | on |
| subquery\$1materialization\$1cost\$1based | on | on |
| subquery\$1to\$1derived | – | off |
| use\$1index\$1extensions | on | on |
| use\$1invisible\$1indexes | – | off |
有关更多信息，请参阅 MySQL 文档中的 [Switchable optimizations (MySQL 5.7)](https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html) 和 [Switchable optimizations (MySQL 8.0)](https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html) 。