為什麼我的查詢在 Amazon RDS for MySQL 中執行緩慢？

2 分的閱讀內容

我正在嘗試針對在 Amazon Relational Database Service (Amazon RDS) for MySQL 中查詢執行緩慢進行疑難排解。為什麼會發生這種情況，我該如何改善查詢效能？

簡短描述

若要改善查詢效能，請考量下列因素：

資源使用率 (例如 CPU、記憶體和儲存體)
工作負載分析
查詢調校和監控

解決方案

資源使用率 (例如 CPU、記憶體和儲存體)

若要了解任何資料庫效能問題的根本原因，請檢查執行個體正在使用的所有伺服器資源。您可以監控工作負載，以及調查正常時與查詢開始耗費過久時間執行時的查詢效能之間的比較。

使用 Amazon CloudWatch 指標來監控資源在一段時間內的變化，包括效能視為正常的天數。您也可以在 Amazon RDS 主控台中檢視效能指標，以監控資料庫效能。

您也可以檢查執行個體狀態，以識別任何其他可能會影響資料庫效能的作用中或排程程序。在 Amazon RDS 主控台中，於資料庫執行不佳時檢查發生的事件。

工作負載分析

若要分析造成資源耗用的工作負載，請使用績效詳情。績效詳情將會提供您所有查詢的圖形分析，以及任何造成資源耗用增加的等待。

績效詳情會使用工作負載作為其主要指標，而不是使用執行個體的 vCPU 數目。如果您目前的工作負載超過 vCPU 限制，則您的伺服器會超載。如果您的伺服器超載，請檢查造成工作負載的查詢，並找出最佳化查詢的方式。然後，考量修改您的執行個體類別。

您的績效詳情工作負載也可以劃分為等待事件。依等待事件數目分割資料庫負載，以調查耗用最多資源的等待。負載圖表中較厚的顏色帶表示對工作負載造成最大影響的等待類型。如需詳細資訊，請參閱在 Amazon RDS 上使用績效詳情監控資料庫負載。

您也可以使用緩慢查詢記錄 (在您的自訂參數群組中啟用) 來識別執行緩慢的查詢。

然後，您可以使用 Amazon CloudWatch 指標來檢查執行個體上完成的工作量是否增加。例如：

資料庫連線：連線至資料庫執行個體的用戶端工作階段數目。
網路接收輸送量 (MB/ 秒)：進出資料庫執行個體的網路流量速率。
寫入和讀取輸送量：每秒從磁碟讀取或寫入磁碟的平均 MB 數。
寫入和讀取延遲：讀取或寫入作業的平均時間 (以毫秒為單位)。
IOPS (讀取和寫入)：每秒磁碟讀取或寫入作業的平均數目。
可用儲存空間 (MB)：資料庫執行個體目前未使用的磁碟空間量。

延遲指標表示完成讀取或寫入磁碟 I/O 作業所花費的時間。延遲指標與增加的資料庫連線或輸送量指標的相互關聯，可能表示工作負載是查詢執行緩慢的原因。如需識別使用率因素的詳細資訊，請參閱如何檢視執行 MySQL 的 Amazon RDS 資料庫執行個體中使用哪些儲存體？

您也可以使用「增強型監控」來擷取工作負載中涉及的作業系統清單，以及基礎系統指標。根據預設，「增強型監控」的監控間隔為 60 秒。最佳實務是將其設定為 1-5 秒的間隔，以取得更細微的資料點。

查詢最佳化

從緩慢查詢記錄或績效詳情識別出長時間執行的查詢之後，請考量改善查詢效能的方法。如果要調整查詢，請考慮以下方法：

如果要尋找花費最多時間的狀態，請分析較緩慢的查詢。如需詳細資訊，請參閱 MySQL 網站上的 SHOW PROFILE 陳述式。
執行 SHOW FULL PROCESSLIST 命令以及「增強型監控」。一起使用時，您可以檢閱目前正在資料庫伺服器上執行的作業清單。
使用 SHOW ENGINE INNODB STATUS 命令以取得交易處理、等待和鎖死的相關資訊。
尋找任何封鎖查詢並解決封鎖問題。如需詳細資訊，請參閱為什麼在沒有其他使用中工作階段時阻止對我的 Amazon RDS for MySQL 資料庫執行個體的查詢？
將 MySQL 記錄發佈到 Amazon CloudWatch。記錄會每小時輪換一次，以維持配置儲存空間閾值的 2%。然後，如果它們超過兩週或如果它們的合併大小超過 2% 閾值，則它們會遭到清除。
設定 Amazon CloudWatch 警示，以便您可以監控資源使用情況，並在超出閾值時收到警示。
尋找查詢的執行計劃，並檢查查詢是否使用適當的索引。您可以使用 EXPLAIN 計劃最佳化您的查詢，並檢閱有關 MySQL 如何執行查詢的詳細資訊。
使用 ANALYZE 資料表陳述式讓您的查詢統計資料保持在最新狀態。查詢最佳化程式有時會因為過時的統計資料而選擇不良的執行計劃。這可能會導致查詢效能不佳，因為資料表和索引的基數預估都不正確。
MySQL 8.0 現在使用 EXPLAIN ANALYZE 陳述式。EXPLAIN ANALYZE 陳述式是您的查詢的分析工具，為您顯示 MySQL 將時間花費在查詢的哪裡以及原因。使用 EXPLAIN ANALYZE，MySQL 可計劃、檢測和執行查詢，同時計算資料列及測量花費在執行計劃各個點的時間。當查詢完成時，EXPLAIN ANALYZE 會列印計劃及其測量，而不是查詢結果。
在 MySQL 第 8 版中，請注意鎖定等待會列在 data_lock_waits 資料表的效能結構描述中。例如：

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;

如需詳細資訊，請參閱 MySQL 網站上的使用 InnoDB 交易和鎖定資訊。