為什麼我的 SELECT 查詢在我的 Amazon Aurora MySQL 資料庫叢集上運行緩慢？

2 分的閱讀內容

我有一個 Amazon Aurora MySQL 相容版資料庫叢集，我想要使用 SELECT 查詢從我的資料庫中選取資料。當我在我的資料庫叢集上運行 SELECT 查詢時，查詢運行緩慢。如何識別和修復 SELECT 查詢緩慢的原因？

簡短描述

SELECT 查詢可能會在 Aurora MySQL 相容的資料庫叢集上執行緩慢的原因有幾個：

您的 Amazon Relational Database Service (Amazon RDS) 系統資源已過度使用。這可能是因為 CPU 過高、記憶體不足或工作負載超過資料庫執行個體類型所能處理的工作負載。
資料庫已鎖定，而產生的等待事件導致 SELECT 查詢執行不佳。
SELECT 查詢正在對大型資料表執行全資料表掃描，或者查詢缺少必要的索引。
由於長時間運行的事務，您的 InnoDB 歷史記錄清單長度（HLL）已經大大增長。

解決方法

使用指標監控您的 Amazon RDS 系統資源

最佳實務是始終監控您的 CPU 使用率並釋放 Amazon Aurora 叢集上的記憶體。雖然偶爾的 CPU 尖峰是正常的，但長時間持續高 CPU 可能會使SELECT 查詢運行緩慢。使用下列工具來決定您使用 CPU 的方式和位置：

1. Amazon CloudWatch 指標是監控您的 CPU 使用率的最簡單方法。如需有關可用於 Aurora 的指標的詳細資訊，請參閱適用於 Aurora 的 CloudWatch 指標。

2. 增強型監控可以更低的粒度詳細查看作業系統層級指標。詳細分解顯示了程序如何使用您的 CPU。

3. 效能洞見可準確判斷您的資料庫負載。開啟資料庫執行個體的效能洞見，然後檢查您的負載是否超過最大 vCPU。您也可以透過等待來監控負載承載查詢和 SQL，並識別造成最大等待的使用者。

由於磁碟尋道，SELECT 查詢也可能執行緩慢。為了最小化磁碟 I/O，資料庫引擎會嘗試快取從磁碟讀取的區塊。這意味著下次資料庫需要相同的資料區塊時，該區塊是從內存而不是從磁碟中獲取。

使用這些指標來檢查您是從磁碟還是記憶體提供特定查詢：

**VolumeReadsIOPS：**此指標是計費的磁碟區層級[磁碟]讀取作業數目。最佳實務是確保此值盡可能低。
**BufferCacheHitRatio：**此指標是緩衝快取所服務的請求百分比。這是確保此值盡可能高的最佳實務。如果 BufferCacheHitRatio 下降，而您的 SELECT 陳述式很慢，則表示您正在處理來自基礎磁碟區的查詢。

識別慢速 SELECT 陳述式的另一個重要資源是慢速查詢日誌。為您的數據庫叢集啟用緩慢查詢記錄以記錄這些查詢並在稍後採取行動。對於與 MySQL 5.6 相容的版本，請使用我的 SQL 效能結構描述持續監控查詢效能。

識別死鎖和等待事件

Amazon RDS 會鎖定資料庫中的資料，以便在任何指定時間只有一個使用者工作階段可以寫入或更新資料列。需要此資料列的任何其他事務都會保持為暫停狀態。在共用鎖中，讀取事務讀取資料時，寫入/更新事務保持為暫停狀態。如果查詢正在等待存取由另一個查詢鎖定的資料列，這可能會造成死鎖。

若要識別資料庫上的死鎖，請在參數群組中啟用 innodb_print_all_deadlocks 參數。然後，從 RDS console/CLI/API 監控 mysql-error.log 。

或者，使用管理員帳戶登入 MySQL，然後執行此命令，從 最新偵測到的死鎖區段中的命令輸出識別死鎖：

mysql> SHOW ENGINE INNODB STATUS\G;

檢查您的查詢是否使用索引

如果查詢沒有索引或執行完整資料表掃描，則查詢執行速度會比較慢。索引有助於加快 SELECT 查詢。

若要檢查您的查詢是否使用索引，請使用 EXPLAIN 查詢。這是疑難排解慢速查詢的有用工具。在 EXPLAIN 輸出中，檢查查詢期間掃描的資料表名稱、使用的金鑰和掃描的資料列數。如果輸出未顯示任何使用中的索引鍵，請在 WHERE子句中使用的資料欄上建立索引。

如果資料表具有所需的索引，請檢查資料表統計資料是否為最新狀態。確定統計資料正確，表示查詢最佳化工具使用具有正確基數的最具選擇性的索引。這可改善查詢效能。

檢查歷史記錄清單長度（HLL）

InnoDB 使用稱為多版本並行控制（MVCC）的概念。MVCC 維護相同記錄的多個副本，以保持讀取一致性。這意味著，當你提交一個事務，InnoDB 就會清除舊的副本。但是，當由於撤消段的增長而長時間未提交事務時，歷史記錄清單長度（HLL）會增加。InnoDB 歷史記錄清單長度表示未刷新更改的數量。

如果您的工作負載需要多個開放或長時間執行的事務，那麼您可能會在資料庫上看到較高的 HLL。

**注意：**長時間執行的事務並不是導致 HLL 尖峰的唯一原因。即使清除執行緒無法跟上資料庫上的變更，HLL 也可能保持較高。

如果您不監控 HLL 的大小，則效能會隨著時間的推移而退化。HLL 的大小增加也可能導致更高的資源消耗、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

對於 Aurora MySQL，由於共用儲存空間磁碟區的性質，歷史記錄清單長度是在叢集層級，而不是個別執行個體層級。連線到您的寫入器並執行以下查詢：

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

此查詢可協助您瞭解讀取器節點與寫入器節點之間的複寫延遲。它也詳細說明資料庫執行個體從儲存空間中讀取的最舊 LSN，以及資料庫執行個體最舊的讀取檢視 TRX ID。使用此資訊來檢查其中一個讀取器是否持有舊的讀取視圖（與寫入器上的引擎 InnoDB 狀態相比）。

**注意：**從 Aurora MySQL 1.19 和 2.06 開始，您可以使用 CloudWatch 中的 RollbackSegmentHistoryListLength 指標監控 HLL。或者，在舊版本中，使用 trx_rseg_history_len 通過以下命令來檢查 HLL：

select NAME AS RollbackSegmentHistoryListLength,
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

如果您的 Aurora MySQL 執行個體已啟用效能洞見，則您可以檢查 RollbackSegmentHistoryListLength。導覽至寫入器效能分析，然後執行下列動作：

1. 選取 Manage metrics（管理指標），然後選取 Database metrics（資料庫指標）。

2. 選取 trx_rseg_history_len 指標，然後選取 Update graph（更新圖形）。

使用以下方法解決 HLL 增長的問題：

如果 DML（寫入）導致 HLL 增長：取消或終止這個陳述式牽涉到中斷事務的復原。這會花費大量時間，因為在此時間點之前進行的所有更新都會復原。
如果 READ 導致 HLL 增長：使用 mysql.rds_kill_query 終止查詢。
視查詢執行的時間長度而定，您可以使用 DBA 來檢查是否可以使用預存程序終止查詢。

最佳實務是使用這些方法監控 HLL 來避免增長，並避免在資料庫上開啟或長時間執行的事務。此外，最佳實務是以較小的批次提交資料。

**重要事項：**請勿重新啟動資料庫叢集或執行個體。當 HLL 可以存取緩衝集區中記憶體中的資料時，清除 HLL 會更有效率。如果您重新啟動資料庫，可能會遺失可存活的頁面快取。發生這種情況時，必須讀取叢集磁碟區中的資料頁，才能清除 HLL。這比在內存中執行它慢，並導致額外的 I/O 計費成本。