如何对运行缓慢的查询进行故障排除并提升我在 Amazon RDS for MySQL 中的查询性能？

解决方法

要对运行缓慢的查询进行故障排除并提高查询性能，请完成以下步骤：

检查您的资源利用率

要监控资源利用率并确定查询性能何时下降，请采取以下操作：

• 要在指定时间段内监控您的资源，请使用 Amazon CloudWatch 指标。
• 要监控您的数据库性能，请使用 Amazon RDS 控制台查看您的性能指标。
• 要确定可能影响数据库性能的活动或计划进程，请检查您的实例状态。确保检查数据库性能不佳时发生的 Amazon RDS 事件。

检查导致资源消耗的工作负载

要检查导致资源消耗的工作负载，请使用 Amazon RDS 上的性能详情。如果您当前的工作负载超过 vCPU 限制，则服务器会过载。如果您的服务器过载，请检查造成工作负载的查询并优化查询。然后，修改您的实例类。

要调查消耗最多等待事件的资源，请按等待事件的数量对数据库负载进行切片。负荷图上较厚的色带表示对您的工作负载影响最大的等待类型。有关详细信息，请参阅在 Amazon RDS 上使用性能详情监控数据库负载。

要识别慢速查询，请在参数组中开启 slow_query_log。要检查您的实例工作负载是否已增加，请使用 CloudWatch 指标检查以下内容：

• 数据库连接： 连接到数据库实例的客户端会话的数量。
• 网络接收吞吐量（MB/秒）： 进出数据库实例的网络流量速率。
• 写入和读取吞吐量： 每秒读取或写入磁盘的平均兆字节数。
• 写入和读取延迟： 读取或写入操作的平均时间，以毫秒为单位。延迟指标与增加的数据库连接或吞吐量指标的关联可能表明工作负载是查询速度慢的原因。有关详细信息，请参阅如何对显示存储空间已满的 RDS for MySQL 或 MariaDB 实例进行故障排除？
• IOPS（读取和写入）： 每秒磁盘读取或写入操作的平均次数。
• 可用存储空间 (MB)： 数据库实例当前未使用的磁盘空间量。
• 突发余额(%) - 通用固态硬盘 (gp2) 突发存储桶 I/O 积分的可用百分比

要查看工作负载中的操作系统 (OS) 列表和系统指标，请使用增强监控。默认情况下，增强监控的时间间隔为 60 秒。最佳做法是将间隔设置在 1 到 5 秒之间，以获得更详细的数据点。

检查您的查询是否有索引或是否进行全表扫描

如果您的查询有索引或进行全表扫描，则您的查询运行速度很慢。

要检查您的查询是否使用索引，请运行 EXPLAIN 查询。有关详细信息，请参阅 MySQL 网站上的 EXPLAIN 语句。在 EXPLAIN 输出中，检查表名称、使用的密钥以及为查询扫描的行数。如果输出未显示任何正在使用的密钥，则在 WHERE 子句中使用的列上创建索引。如果该表具有所需的索引，请检查表的统计信息是否是最新的。有关详细信息，请参阅 MySQL 网站上的 The INFORMATION_SCHEMA STATISTICS Table。当统计数据为最新时，查询优化器会使用具有正确基数的最具选择性的索引，从而使查询性能得到提升。

检查历史列表长度

InnoDB 使用多版本并发控制 (MVCC)。MVCC 维护同一记录的多个副本以保持读取一致性。历史列表长度是包含历史列表中修改的撤消日志的总数。当有一个写入或读取数据的长时间运行的事务时，历史列表的长度会增加，直到事务完成或回滚为止。此外，历史列表的长度会增加，而其他事务会修改长时间运行的事务所使用的表。

如果您的工作负载需要多个打开或长时间运行的事务，则数据库的历史列表长度会很长。如果您不监控历史列表的长度，则性能可能会随着时间的推移而下降。历史列表长度过高还可能导致高资源消耗、SELECT 语句性能缓慢且不一致以及存储空间增加。

**注意：**长时间运行的事务并不是历史列表长度激增的唯一原因。如果清除线程无法跟上数据库的变化，则历史列表长度仍然很高。在极端情况下，您还可能会遇到数据库中断的情况。

要检查您的历史列表长度，请运行以下命令：

SHOW ENGINE INNODB STATUS;

输出示例：

------------ TRANSACTIONS ------------
Trx id counter 26368570695
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

如果您的 Amazon RDS for MySQL 实例开启性能详情，请完成以下步骤以检查 RollbackSegmentHistoryListLength：

1. 导航到写入器性能详情。
2. 选择 Manage metrics（管理指标），然后选择 Database metrics（数据库指标）。
3. 选择 trx_rseg_history_len 指标，然后选择 Update graph（更新图表）。

要解决导致历史列表长度增加的问题，请执行以下操作：

• 如果 DML 写入导致历史列表长度增加，请取消或停止写入语句。这涉及回滚中断的事务，回滚更新需要花费大量时间。
• 如果 READ 导致历史列表长度增加，请使用 mysql.rds_kill_query 停止查询。
• 如果您的查询运行了很长时间，请与您的 DBA 一起检查是否可以停止查询。

**注意：**最佳做法是避免在数据库上进行公开或长时间运行的事务，并小批量提交数据。

提升查询性能

要提升查询性能，请遵循以下最佳实践：

• 要找出花费时间最多的状态，请对速度较慢的查询进行分析。有关详细信息，请参阅 MySQL 网站上的 SHOW PROFILE statement。

• 运行 SHOW FULL PROCESSLIST 命令以及增强监控。两者一起使用时，您可以查看当前在数据库服务器上执行的操作列表。

• 使用 SHOW ENGINE INNODB STATUS 命令获取有关事务处理、等待事件和死锁的信息。

• 查找并解决阻塞性查询。有关详细信息，请参阅为什么在没有其他活动会话的情况下，对 Amazon RDS for MySQL 数据库实例的查询会被阻止？

• 将 MySQL 日志发布到 CloudWatch Logs。日志每小时轮换一次，以保持分配存储空间阈值的 2％。超过两周的日志或总大小超过 2% 阈值的日志将被删除。

• 要监控您的资源使用情况并在超过阈值时收到提醒，请设置 CloudWatch 警报。

• 找到查询操作计划并检查您的查询是否使用了相应的索引。使用 EXPLAIN 优化您的查询并查看有关 MySQL 如何运行查询的详细信息。有关详细信息，请参阅 MySQL 网站上的 Optimizing Queries with EXPLAIN。

• 使用 ANALYZE TABLE 更新您的查询统计信息。有关详细信息，请参阅 MySQL 网站上的 ANALYZE TABLE Statement。

• MySQL 8.0 使用了 EXPLAIN ANALYZE。EXPLAIN ANALYZE 语句显示了 MySQL 在何处为您的查询分配时间以及分配该时间的原因。查询完成后，EXPLAIN ANALYZE 会打印计划及其测量结果。有关详细信息，请参阅 MySQL 网站上的 Obtaining Information with EXPLAIN ANALYZE。

• 在 MySQL 8.0 中，锁等待在 data_lock_waits 表的性能架构中列出。有关详细信息，请参阅 MySQL 网站上的 Using InnoDB Transaction and Locking Information。

  示例：

  SELECT  r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
  FROM       performance_schema.data_lock_waits w
  INNER JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_engine_transaction_id
  INNER JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_engine_transaction_id;