我的 Aurora 我的SQL功能的建議 SQL - Amazon Aurora
在我的 Aurora 中使用多執行緒複寫 SQL調用 AWS Lambda 使用本機 My 函SQL數的函數避免使用 Amazon Aurora 我的 XA 交易 SQL在DML陳述式期間保持外鍵開啟設定日誌緩衝區的排清頻率最小化和疑難排解 Aurora 我的SQL死結

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

我的 Aurora 我的SQL功能的建議 SQL

以下功能在 Aurora 我的中可用，以提SQL供我的SQL相容性。但是，這些功能在 Aurora 環境中存在效能、可擴展性、穩定性或相容性的問題。因此，建議您在使用這些功能時遵循某些指導方針。例如，建議您不要將特定功能用於生產 Aurora 部署。

在我的 Aurora 中使用多執行緒複寫 SQL

使用多執行緒二進位記錄複寫時，執行SQL緒會從中繼記錄讀取事件，並將事件排入佇列以供 SQL Worker 執行緒套用。SQL工作線程由協調器線程管理。可能的話，會平行套用二進位日誌事件。

Aurora 我的SQL版本 3 和 Aurora 我的版本 2.12.1 及更高SQL版本中支援多執行緒複寫。

對於低於 3.04 的 Aurora My SQL 版本，當 Aurora My SQL DB 叢集用作二進位記錄複寫的僅供讀取複本時，依預設會使用單執行緒複寫。

早期版本的 Aurora 我的SQL版本 2 繼承了幾個關於從我的SQL社群版多執行緒複寫的問題。對於這些版本，我們建議您不要在生產環境中使用多執行緒複寫。

如果您確實使用多執行緒複寫，建議您徹底測試它。

如需在 Amazon Aurora 中使用複寫的詳細資訊，請參閱以 Amazon Aurora 進行複寫。如需 Aurora My 中的多執行緒複寫的相關資訊SQL，請參閱多執行緒二進位記錄複寫。

調用 AWS Lambda 使用本機 My 函SQL數的函數

我們建議您使用原生的 My SQL 函數，lambda_sync並叫lambda_async用 Lambda 函數。

如果您使用已棄用的 mysql.lambda_async 程序，建議在預存程序中包裝對 mysql.lambda_async 程序的呼叫。您可以從不同的來源 (例如觸發程式或用戶端程式碼) 呼叫此預存程序。此方法有助於避免阻抗不符問題，並讓您的資料庫程式設計人員能夠輕鬆呼叫 Lambda 函數。

如需從 Amazon Aurora 叫用 Lambda 函數的詳細資訊，請參閱從 Amazon Aurora 我的數SQL據庫集群調用 Lambda 函數。

避免使用 Amazon Aurora 我的 XA 交易 SQL

我們建議您不要將 eXtended 架構 (XA) 交易與 Aurora My 搭配使用SQL，因為如果 XA 處於PREPARED狀態，這些交易可能會造成較長的復原時間。如果您必須透過 Aurora My 使用 XA 交易SQL，請遵循下列最佳做法：

  • 請勿讓 XA 交易保持開啟在 PREPARED 狀態。

  • 盡可能讓 XA 交易越小越好。

如需搭配 My 使用 XA 交易的詳細資訊SQL，請參閱我的SQL文件中的 XA 交易。

在DML陳述式期間保持外鍵開啟

強烈建議您在foreign_key_checks變數設定為 (offDDL) 時，不要執行任何資料定義語言 0 () 陳述式。

如果您需要插入或更新需要暫時違反外部索引鍵的資料列，請遵循這些步驟：

  1. 將 foreign_key_checks 設定為 0。

  2. 進行資料操作語言 (DML) 變更。

  3. 確定您完成的變更未違反任何外部索引鍵限制。

  4. 將 foreign_key_checks 設為 1 (開啟)。

此外，遵循用於外部索引鍵限制的這些其他最佳實務：

  • 確定您的用戶端應用程式未隨著 foreign_key_checks 變數將 0 變數設為 init_connect。

  • 如果從邏輯備份 (例如 mysqldump) 的還原失敗或未完成，在相同工作階段中開始任何其他操作之前，請確定將 foreign_key_checks 設為 1。邏輯備份會在開始時將 foreign_key_checks 設為 0。

設定日誌緩衝區的排清頻率

在我的SQL社區版中，為了使事務持久，InnoDB 日誌緩衝區必須刷新到持久存儲。您可以使用 innodb_flush_log_at_trx_commit 參數來設定日誌緩衝區排清到磁碟的頻率。

當您將 innodb_flush_log_at_trx_commit 參數設為預設值 1 時，日誌緩衝區會在每次交易認可時進行排清。此設定有助於保持資料庫ACID相容性。建議您保留預設設定 1。

變更innodb_flush_log_at_trx_commit為非預設值有助於減少資料操作語言 (DML) 延遲，但會犧牲記錄記錄的耐久性。這種缺乏耐久性會導致資料庫ACID不相容。我們建議您的資料庫ACID符合規範，以避免在伺服器重新啟動時遺失資料的風險。如需有關此參數的詳細資訊，請參閱我的文件中的 in db_flush_log_at_trx_ commit。SQL

在 Aurora My 中SQL，重做記錄處理會卸載至儲存層，因此資料庫執行個體上不會排清記錄檔。發出寫入時，重做日誌會從寫入器資料庫執行個體直接傳送至 Aurora 叢集磁碟區。唯一跨網路的寫入是重做日誌記錄。始終不會從資料庫層寫入任何頁面。

依預設，每個執行緒認可交易都會等待 Aurora 叢集磁碟區的確認。此確認指出已寫入此記錄和所有先前的重做日誌記錄，都已寫入並達到仲裁。不論是透過自動認可還是明確認可，持續保留日誌記錄並達到仲裁，都能使交易耐久。如需 Aurora 儲存架構的詳細資訊，請參閱 Amazon Aurora 儲存解密。

Aurora My SQL 不會像我的SQL社群版一樣將記錄檔清除到資料檔案。不過，在將重做日誌記錄寫入至 Aurora 叢集磁碟區時，您可以使用 innodb_flush_log_at_trx_commit 參數來放鬆耐久性限制。

對於 Aurora 我的SQL版本 2：

  • innodb_flush_log_at_trx_commit= 0 或 2 — 資料庫不會等待重做日誌記錄寫入 Aurora 叢集磁碟區的確認。

  • innodb_flush_log_at_trx_commit= 1 — 資料庫會等待確認重做日誌記錄已寫入 Aurora 叢集磁碟區。

對於 Aurora 我的SQL版本 3：

  • innodb_flush_log_at_trx_commit= 0 — 資料庫不會等待重做日誌記錄寫入 Aurora 叢集磁碟區的確認。

  • innodb_flush_log_at_trx_commit= 1 或 2 — 資料庫會等待確認重做日誌記錄已寫入 Aurora 叢集磁碟區。

因此，若要在 Aurora 我的SQL版本 3 中取得與 Aurora 我的版本 2 中設定為 0 或 2 的相同非預設行為，請將參數設定為 0。SQL

雖然這些設定可以降低對用戶端的DML延遲，但也可能會在容錯移轉或重新啟動時造成資料遺失。因此，建議您將 innodb_flush_log_at_trx_commit 參數持續設定為預設值 1。

雖然「我的SQL社群版」和「我的 Aurora My」都可能發生資料遺失SQL，但由於其架構不同，每個資料庫的行為都有所不同。這些架構差異可能會導致不同程度的資料遺失。若要確定您的資料庫ACID符合規定，請始終設innodb_flush_log_at_trx_commit定為 1。

注意

在 Aurora My SQL 版本 3 中，您必須先將值變更innodb_flush_log_at_trx_commit為 1 以外的值，才能變更innodb_trx_commit_allow_data_loss為 1。通過這樣做，您承認數據丟失的風險。

最小化和疑難排解 Aurora 我的SQL死結

在相同資料頁面上並行修改記錄時，執行在唯一次要索引或外部索引鍵上經常遇到限制條件違規之工作負載的使用者，可能會遇到更多的死結情況和鎖定等待逾時。這些死結和逾時是因為「我的SQL社群版」錯誤修正。

此修正程式包含在我的SQL社群版本 5.7.26 及更高版本中，並已反向移植至 Aurora 我的 2.10.3 及更高版SQL本。通過對 InnoDB 表中的記錄所做的更改，對這些類型的數據操作語言（DML）操作實施其他鎖定來強制執行序列化性是必要的修復。發現此問題是針對先前「我的SQL社群版」錯誤修正所引入的鎖死問題調查的一部分。

該項修正變更了 InnoDB 儲存引擎中，元組 (資料列) 更新的部分回復內部處理。在外部索引鍵或唯一次要索引產生限制條件違規的操作會導致部分回復。這包括但不限於並行 INSERT...ON DUPLICATE KEY UPDATE、REPLACE INTO, 和 INSERT IGNORE 陳述式 (upserts)。

在此情況下，部分回復並非應用程式層級交易的回復，而是發生限制條件違規時，對叢集索引的內部 InnoDB 回復變更。例如，在 upsert 操作期間找到重複索引鍵值。

在正常的插入操作中，InnoDB 會自動為每個索引建立叢集和次要索引項目。如果 InnoDB 在 upsert 操作期間偵測到唯一次要索引上的重複值，則叢集索引中插入的項目必須回復 (部分回復)，並將更新套用至現有的重複列。在此內部部分回復步驟中，InnoDB 必須在操作中鎖定每個記錄。此修正會在部分回復之後引入額外鎖定，以確保交易序列化。

減少 InnoDB 死結情況

您可以採取下列方法來減少資料庫執行個體的死結情況發生頻率。更多示例可以在我的SQL文檔中找到。

  1. 為了減少死結情況發生，請在進行一系列相關變更後立即遞交交易。您可以將大型交易 (遞交之間的多列更新) 拆解為較小交易來執行此動作。若您要批次插入行，請盡量減少批次插入的大小，尤其是使用上述提及的 upsert 操作時。

    若要減少部分回復的次數，您可以嘗試下列方法：

    1. 一次插入一行，而非進行批次插入操作。如此便能減少可能發生衝突之交易的鎖定時間。

    2. 而不是使用REPLACE INTO，將SQL陳述式重新寫入為多重陳述式交易，如下所示：

      BEGIN;
DELETE conflicting rows;
INSERT new rows;
COMMIT;

    3. 而不是使用INSERT...ON DUPLICATE KEY UPDATE，將SQL陳述式重新寫入為多重陳述式交易，如下所示：

      BEGIN;
SELECT rows that conflict on secondary indexes;
UPDATE conflicting rows;
INSERT new rows;
COMMIT;

  2. 避免長時間執行且可能導致鎖定的作用中或閒置交易。這包括互動式「我的SQL用戶端」工作階段，這些工作階段可能會在未認可的交易中長時間開啟。最佳化交易大小或批次大小時，影響會因並行、重複項目數量和資料表結構等因素而有所不同。任何變更都應根據您的工作負載來執行和測試。

  3. 在部分情況下，當兩筆交易嘗試以不同順序存取相同的資料集時，不論資料集是否位於同一個資料表，皆可能發生死結情況。為了防止這種情況，您可以修改交易以相同順序存取資料，進而序列化該存取。例如，建立待完成的交易佇列。當多個交易並行發生時，此方法便能協助避免死結情況。

  4. 將謹慎選擇的索引新增到資料表中，可改善選取性並減少存取資料列的需求，從而減少死結情況。

  5. 若發生間隙鎖定，您可以將工作階段或交易的交易隔離層級修改為 READ COMMITTED，以避免發生間隙鎖定。有關 InnoDB 隔離級別及其行為的更多信息，請參閱我的SQL文檔中的事務隔離級別。

注意

雖然您可以採取預防措施來減少死結情況，但死結情況是可預期的資料庫行為，仍然可能發生。應用程式應具有必要的邏輯來處理死結情況。例如，在應用程式中執行重試和停止邏輯。最理想的是解決問題根本原因，但是若確實發生死結情況，應用程式仍可以選擇等待並重試。

監控 InnoDB 死結情況

SQL當應用程式交易嘗試以導致循環等待的方式取得資料表層級和資料列層級鎖定時，可能會在 My 中發生死結。偶爾的 InnoDB 死結情況不一定是大問題，因為 InnoDB 儲存引擎會立即偵測到狀況，並自動復原交易。若您經常遇到死結情況，建議您檢閱和修改應用程式，以減緩效能問題並避免死結發生。當死結偵測開啟 (預設值) 時，InnoDB 會自動偵測交易死結，並復原交易以解決死結情況。InnoDB 嘗試選擇要復原的小型交易，而交易的大小是由插入、更新或刪除的資料列數而定。

  • SHOW ENGINE 陳述式 – SHOW ENGINE INNODB STATUS \G 陳述式包含自上次重啟以來，資料庫最近遇到的死結詳細資訊。

  • 我的SQL錯誤日誌-如果您經常遇到SHOW ENGINE語句輸出不足的死鎖，則可以打開 innodb_print_all_ 死鎖數據庫集群參數。

    開啟此參數時，InnoDB 使用者交易中所有死結的相關資訊會記錄在 Aurora 我的SQL錯誤記錄檔中。

  • Amazon CloudWatch 指標 — 我們也建議您使用指標主動監控死 CloudWatch 結Deadlocks。如需詳細資訊，請參閱Amazon Aurora 的執行個體層級指標。

  • Amazon CloudWatch 日誌 — 使用 CloudWatch 日誌，您可以檢視指標、分析日誌資料以及建立即時警示。有關詳情，請參閱使用 Amazon 的 Amazon Aurora 我的SQL和 Amazon RDS 監視錯誤以 CloudWatch 及SQL使用亞馬遜發送通知SNS。

    使用 CloudWatch 記錄開innodb_print_all_deadlocks啟時，您可以設定警示，以便在死結數超過指定臨界值時通知您。若要定義閾值，建議您觀察平時情況，根據您的正常工作負載來定義該值。

  • 績效詳情 — 當您使用績效詳情時，您可以監控 innodb_deadlocks 和 innodb_lock_wait_timeout 指標。如需這些指標的詳細資訊，請參閱 我的 Aurora 的非本地計數器 SQL。