Aurora 的最佳做法我的SQL效能和擴展 - Amazon Aurora
使用 T 執行個體類別進行開發和測試索引聯結查詢的非同步索引鍵預先提取大型聯結查詢的雜湊聯結使用 Amazon Aurora 擴展我的SQL資料庫的讀取最佳化時間戳記操作

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

Aurora 的最佳做法我的SQL效能和擴展

您可以套用下列最佳做法來改善 Aurora My SQL 叢集的效能和延展性。

使用 T 執行個體類別進行開發和測試

使用db.t2、db.t3或db.t4g資料庫SQL執行個體類別的 Amazon Aurora My 執行個體最適合長時間不支援高工作負載的應用程式。T 執行個體旨在提供適度的基準效能和容量，可視您的工作負載需要大幅提升效能。它們適用於不CPU經常或一致地使用完整功能，但偶爾需要爆發的工作負載。建議您在開發、測試伺服器或其他非生產伺服器時，僅使用 T 資料庫執行個體類別。如需 T 執行個體類別的詳細資訊，請參閱爆量效能執行個體。

如果您的 Aurora 叢集大於 40 TB，請勿使用 T 執行個體類別。當您的資料庫有大量資料時，管理結構描述物件的記憶體額外負荷可能會超過 T 執行個體的容量。

請勿在 Amazon Aurora 我的 SQL T 執行個體上啟用我的SQL效能結構描述。如果已啟用 Performance Schema (效能結構描述)，執行個體可能會用盡記憶體。

提示

如果您的數據庫有時處於閒置狀態，但在其他時間有很大的工作量，則可以使用 Aurora Serverless v2 作為 T 實例的替代方案。同 Aurora Serverless v2，您可以定義容量範圍，Aurora 會根據目前的工作負載自動擴展或縮減資料庫。如需用量詳細資料，請參閱 使用 Aurora Serverless v2。對於您可以搭配使用的資料庫引擎版本 Aurora Serverless v2，請參閱的要求和限制 Aurora Serverless v2。

當您在 Aurora My DB 叢集中使用 T 執行個體做為資SQL料庫執行個體時，建議您執行下列操作：

  • 在您的資料庫叢集中，所有執行個體請皆使用相同的資料庫執行個體類別。例如：如果您的寫入器執行個體使用 db.t2.medium，則我們也建議讀取器執行個體使用 db.t2.medium。

  • 請不要調整任何與記憶體相關的組態設定，例如 innodb_buffer_pool_size。Aurora 可針對 T 執行個體上的記憶體緩衝區使用一組高度調整的預設值。Aurora 需要這些特殊的預設值，才能在記憶體受限的執行個體上執行。如果您在 T 實例上更改任何與內存相關的設置，則即使您的更改旨在增加緩衝區大小，也更有可能遇到 out-of-memory 條件。

  • 監控您的CPU信用餘額（CPUCreditBalance），以確保其處於可持續水平。也就是說，CPU積分的累積速度與使用中的速度相同。

    當您用盡執行個體的CPU積分時，您會看到可用的積分立即下降，以CPU及執行個體的讀取和寫入延遲增加。此情況會造成執行個體整體效能嚴重降低。

    如果您的積分餘CPU額不在可持續層級，建議您修改資料庫執行個體，以使用其中一個受支援的 R DB 執行個體類別 (擴展運算)。

    如需監控指標的詳細資訊，請參閱在 Amazon RDS 主控台中檢視指標。

  • 監控寫入器執行個體與讀取器執行個體之間的複本延遲 (AuroraReplicaLag)。

    如果在寫入器執行個體發生之前，讀取器執行個體用完了CPU信用，則產生的延遲可能會導致讀取器執行個體頻繁重新啟動。當應用程式分散在讀取器執行個體之間的讀取操作負載繁重，而同時寫入器執行個體有少量寫入操作時，此結果很常見。

    如果您看到複本延遲持續增加，請確定資料庫叢集中讀取器執行個體的CPU信用餘額未用盡。

    如果您的積分餘CPU額不是永續層級，建議您修改資料庫執行個體，以使用其中一個受支援的 R DB 執行個體類別 (擴展運算)。

  • 針對已啟用二進位日誌的資料庫叢集，將每個交易的插入數保持在 100 萬個以下。

    如果資料庫叢集的資料庫叢集參數群組的binlog_format參數設定為以外的值OFF，則如果資料庫叢集接收到要插入超過 100 萬個資料列的交易，您的資料庫叢集可能會遇到 out-of-memory條件。您可以監控可釋放記憶體 (FreeableMemory) 指標，以判斷資料庫叢集是否用盡可用的記憶體。然後可以檢查寫入操作 (VolumeWriteIOPS) 指標，以查看寫入器執行個體接收的寫入器操作負載是否繁重。若是這種情況，建議更新您的應用程式，將一個交易中的插入數限制在 100 萬個以下。或者，您可以修改執行個體，以使用其中一個支援的 R 資料庫執行個體類別 (擴展運算)。

使用非同步金鑰預取最佳化 Aurora 我的SQL索引聯結查詢

Aurora My SQL 可以使用非同步金鑰 prefetch (AKP) 功能來改善跨索引聯結資料表的查詢效能。此功能可預期執行查詢所需的資料列，其中JOIN查詢需要使用批次金鑰存取 (BKA) 聯結演算法和多範圍讀取 () 最佳化功能，藉此改善效能。MRR如需BKA和的詳細資訊MRR，請參閱 My 文件中的封鎖巢狀迴圈和批次金鑰存取聯結和多範圍讀取最佳化。SQL

若要利用AKP此功能，查詢必須同時使用BKA和MRR。一般而言，當查詢JOIN子句使用次要索引，但也需要主索引中的某些資料行時，就會發生這種查詢。例如，AKP當JOIN子句代表小型外部和大型內部表格之間索引值的等值連接時，您可以使用，並且索引在較大的資料表上具有高度選擇性。AKP在評估JOIN條款期間，配合使用BKA並MRR執行次要至主要索引查閱。AKP識別在評估JOIN子句期間執行查詢所需的資料列。然後使用背景執行緒，在執行查詢之前，將包含那些資料列的頁面非同步載入至記憶體。

AKP可用於 Aurora 我的SQL版本 2.10 及更高版本，以及版本 3。如需 Aurora 我的SQL版本的詳細資訊，請參閱Amazon Aurora 我的數據庫引擎更新 SQL。

啟用非同步索引鍵預先提取

您可以將 [我的SQL伺服器變數] 設定aurora_use_key_prefetch為來啟用AKP此功能on。依預設，此值是設為 on。不過，除非您同時啟用「BKA聯結」演算法並停用AKP以成本為基礎的MRR功能，否則無法啟用。若要這麼做，您必須為 optimizer_switch My SQL Server 變數設定下列值：

  • 將 batched_key_access 設定為 on。此值可控制「BKA聯結」演算法的使用。依預設，此值是設為 off。

  • 將 mrr_cost_based 設定為 off。此值可控制以成本為基礎之MRR功能的使用。依預設，此值是設為 on。

目前，您只能在工作階段層級設定這些值。下列範例說明如何透過執行SET陳述式來設定這些值，以便AKP為目前的工作階段啟用。


mysql> set @@session.aurora_use_key_prefetch=on;
mysql> set @@session.optimizer_switch='batched_key_access=on,mrr_cost_based=off';

同樣地，您可以使用SET陳述式來停用AKP和 BKA Join 演算法，並重新啟用目前階段作業的成本基礎MRR功能，如下列範例所示。


mysql> set @@session.aurora_use_key_prefetch=off;
mysql> set @@session.optimizer_switch='batched_key_access=off,mrr_cost_based=on';

如需有關 batched_key_access 和 mrr_cost_ 型最佳化程式參數的詳細資訊，請參閱我的文件中的可切換最佳化。 SQL

非同步索引鍵預先提取的最佳化查詢

您可以確認查詢是否可以利用AKP此功能。若要這麼做，請使用 EXPLAIN 陳述式在執行查詢之前描繪查詢。EXPLAIN 陳述式提供要針對指定查詢使用之執行計劃的相關資訊。

在 EXPLAIN 陳述式的輸出中，Extra 資料欄說明執行計劃隨附的其他資訊。如果此AKP功能適用於查詢中使用的資料表，則此欄會包含下列其中一個值：

  • Using Key Prefetching

  • Using join buffer (Batched Key Access with Key Prefetching)

下列範例顯示如何使用EXPLAIN來檢視可利用之查詢的執行計畫AKP。


mysql> explain select sql_no_cache
    ->     ps_partkey,
    ->     sum(ps_supplycost * ps_availqty) as value
    -> from
    ->     partsupp,
    ->     supplier,
    ->     nation
    -> where
    ->     ps_suppkey = s_suppkey
    ->     and s_nationkey = n_nationkey
    ->     and n_name = 'ETHIOPIA'
    -> group by
    ->     ps_partkey having
    ->         sum(ps_supplycost * ps_availqty) > (
    ->             select
    ->                 sum(ps_supplycost * ps_availqty) * 0.0000003333
    ->             from
    ->                 partsupp,
    ->                 supplier,
    ->                 nation
    ->             where
    ->                 ps_suppkey = s_suppkey
    ->                 and s_nationkey = n_nationkey
    ->                 and n_name = 'ETHIOPIA'
    ->         )
    -> order by
    ->     value desc;
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table    | type | possible_keys         | key           | key_len | ref                              | rows | filtered | Extra                                                       |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
|  1 | PRIMARY     | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where; Using temporary; Using filesort                |
|  1 | PRIMARY     | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  1 | PRIMARY     | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
|  2 | SUBQUERY    | nation   | ALL  | PRIMARY               | NULL          | NULL    | NULL                             |   25 |   100.00 | Using where                                                 |
|  2 | SUBQUERY    | supplier | ref  | PRIMARY,i_s_nationkey | i_s_nationkey | 5       | dbt3_scale_10.nation.n_nationkey | 2057 |   100.00 | Using index                                                 |
|  2 | SUBQUERY    | partsupp | ref  | i_ps_suppkey          | i_ps_suppkey  | 4       | dbt3_scale_10.supplier.s_suppkey |   42 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+----------+------+-----------------------+---------------+---------+----------------------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

如需有關EXPLAIN輸出格式的詳細資訊，請參閱我的SQL文件中的延伸EXPLAIN輸出格式。

最佳化大型 Aurora 我SQL使用雜湊聯結的聯接查

需要使用對等聯結來聯結大量資料時，雜湊聯結可以改善查詢效能。您可以為我的 Aurora 啟用雜湊聯結SQL。

雜湊聯結資料欄可以是任何複雜的表達式。在雜湊聯結資料欄中，您可以利用下列方式來比較各個資料類型：

  • 您可以比較各類別的精確數值資料類型項目，例如 int、bigint、numeric 和 bit。

  • 您可以比較各類別的近似數值資料類型項目，例如 float 和 double。

  • 您可以比較各字串類型的項目，以得知字串類型是否有相同的字元集和定序。

  • 您可以比較項目的日期和時間戳記資料類型，以得知類型是否相同。

注意

您無法在不同類別中比較資料類型。

下列限制適用於 Aurora My 的雜湊聯結SQL：

  • Aurora 我的SQL版本 2 不支援左右外部聯結，但版本 3 支援。

  • 不支援半聯結 (例如子查詢)，除非先將子查詢具體化。

  • 不支援多資料表更新或刪除。

    注意

    支援單一資料表更新或刪除。

  • BLOB和空間數據類型列不能是哈希連接中的連接列。

啟用雜湊聯結

若要啟用雜湊聯結：

  • Aurora 我的SQL版本 2 — 將資料庫參數或資料庫叢集參數設定aurora_disable_hash_join為0。若關閉 aurora_disable_hash_join，optimizer_switch 的值將為 hash_join=on。

  • Aurora 我的SQL版本 3 — 將我的SQL伺服器參數設定optimizer_switch為block_nested_loop=on。

Aurora 我的SQL版本 3 中預設會開啟雜湊聯結，並在 Aurora 我的SQL版本 2 中預設關閉。下列範例說明如何為 Aurora 我的SQL版本 3 啟用雜湊聯結。您可以發出陳述式 select @@optimizer_switch，以查看哪些其他設定存在於 SET 參數字串中。更新 optimizer_switch 參數中的某個設定不會清除或修改其他設定。

mysql> SET optimizer_switch='block_nested_loop=on';
注意

對於 Aurora 我的SQL版本 3，雜湊聯結支援在所有次要版本中均提供，且預設為開啟。

對於 Aurora 我的SQL版本 2，所有次要版本均提供雜湊連接支援。在 Aurora 我的SQL版本 2 中，散列連接功能始終由aurora_disable_hash_join值控制。

利用此設定，最佳化器會根據成本、查詢特質和資源可用性選擇使用雜湊聯結。如果成本估算不正確，您可以強制最佳化器選擇某個雜湊聯結。您可以透過將 [我的SQL伺服器變數] 設定hash_join_cost_based為off。下列範例說明如何強制最佳化器選擇雜湊聯結。


mysql> SET optimizer_switch='hash_join_cost_based=off';
注意

此設定會覆寫成本類型最佳化工具的決策。雖然該設定對於測試和開發非常有用，但建議您不要在生產環境中使用它。

最佳化雜湊聯結的查詢

若要了解查詢是否可利用雜湊聯結，請先使用 EXPLAIN 陳述式來描繪查詢。EXPLAIN 陳述式提供要針對指定查詢使用之執行計劃的相關資訊。

在 EXPLAIN 陳述式的輸出中，Extra 資料欄說明執行計劃隨附的其他資訊。如果雜湊聯結適用於查詢中所用的資料表，此資料欄會包括類似以下的值：

  • Using where; Using join buffer (Hash Join Outer table table1_name)

  • Using where; Using join buffer (Hash Join Inner table table2_name)

下面的例子顯示了如EXPLAIN何查看哈希聯接查詢的執行計劃的使用。


mysql> explain SELECT sql_no_cache * FROM hj_small, hj_big, hj_big2
    ->     WHERE hj_small.col1 = hj_big.col1 and hj_big.col1=hj_big2.col1 ORDER BY 1;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                                          |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
|  1 | SIMPLE      | hj_small | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort                                |
|  1 | SIMPLE      | hj_big   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where; Using join buffer (Hash Join Outer table hj_big)  |
|  1 | SIMPLE      | hj_big2  | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (Hash Join Inner table hj_big2) |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------------------------------------------------------+
3 rows in set (0.04 sec)

在輸出中，Hash Join Inner table 是用來建置雜湊資料表的資料表，而 Hash Join Outer table 是用來探測雜湊資料表的資料表。

如需有關延伸EXPLAIN輸出格式的詳細資訊，請參閱我的SQL產品文件中的延伸EXPLAIN輸出格式。

在 Aurora My SQL 2.08 及更高版本中，您可以使用SQL提示來影響查詢是否使用雜湊聯結，以及要用於聯結的建置和探查端的資料表。如需詳細資訊，請參閱 Aurora 我的SQL提示。

使用 Amazon Aurora 擴展我的SQL資料庫的讀取

您可以將 Amazon Aurora 與我的SQL資料庫執行個體搭配使用，以利用 Amazon Aurora 的讀取擴展功能，並擴展 My SQL 資料庫執行個體的讀取工作負載。若要使用 Aurora 讀取擴展您的 My SQL 資料庫執行個體，請建立 Aurora My SQL DB 叢集，並將其設為 My 資SQL料庫執行個體的僅供讀取複本。然後連接至 Aurora 我的SQL叢集以處理讀取查詢。來源資料庫可以是我的RDS資料SQL庫執行個體，也可以是 Amazon 外部執行的 My SQL 資料庫RDS。如需詳細資訊，請參閱使用 Amazon Aurora 擴展您的我的SQL資料庫的讀取。

最佳化時間戳記操作

當系統變數的值設定time_zone為時SYSTEM，每個需要時區計算的 My SQL 函數呼叫都會進行系統程式庫呼叫。當您執行以高度並行傳回或變更這類TIMESTAMP值的SQL陳述式時，您可能會遇到延遲、鎖定爭用和CPU使用量增加的陳述式。如需詳細資訊，請參閱我的文件中的 SQLtime_zone。

若要避免此行為，建議您將 time_zone 資料庫叢集參數的值變更為 UTC。如需詳細資訊，請參閱在 Amazon Aurora 中修改資料庫叢集參數群組中的參數。

儘管 time_zone 參數是動態的 (不需要重新啟動資料庫伺服器)，但新值僅用於新連線。若要確保所有連線都更新為使用新的 time_zone 值，建議您在更新資料庫叢集參數之後回收應用程式連線。