最佳化查詢

使用本節中描述的查詢最佳化技術，可加快查詢執行速度，或做為 Athena 中超出資源限制的查詢解決方法。

最佳化聯結

在分散式查詢引擎中執行聯結的策略有很多。最常見的兩個是分散式雜湊聯結和具有複雜聯結條件的查詢。

在分佈式散列連接中，將大表放在左側，右側放置小表

最常見的聯結類型使用對等比較做為聯結條件。Athena 以分散式雜湊聯結的形式執行此類聯結。

在分散式雜湊聯結中，引擎會從聯結的一側建置一個查詢表 (雜湊資料表)。這一側稱為建置端。建置端的記錄分散在多個節點上。每個節點為其子集建置一個查詢表。接著，聯結的另一端稱為探查端，會透過節點進行串流。探查端的記錄以與建置端相同的方式分散在節點上。這樣會啟用每個節點，以透過在其自己的查詢表中查詢相符記錄來執行聯結。

當從聯結的建置端建立的查詢表不適合放入記憶體中時，查詢可能會失敗。即使建置端的總大小小於可用記憶體，如果記錄的分佈存在很大偏差，查詢也可能會失敗。在極端情況下，所有記錄可能具有相同的聯結條件值，並且必須放入單一節點上的記憶體中。如果將一組值傳送至相同節點，且值加起來超過可用記憶體，即使是查詢偏差較小，也可能會失敗。節點確實可以將記錄溢寫至磁碟，但溢出會降低查詢執行速度，並且可能不足以防止查詢失敗。

Athena 會嘗試重新排序聯結，以使用較大的關係做為探查端，而較小的關係做為建置端。不過，由於 Athena 不會管理資料表中的資料，所以資訊有限，而且通常必須假設第一個資料表較大，第二個資料表較小。

使用以等值為基礎的聯結條件撰寫聯結時，假設 JOIN 關鍵字左側的資料表是探查端，右側的資料表是建置端。確保正確的資料表 (建置端) 是較小的資料表。如果無法使聯結的建置端足夠小以放入記憶體中，請考慮執行多個聯結建置資料表子集的查詢。

用EXPLAIN於分析具有複雜聯結的查詢

具有複雜聯結條件的查詢 (例如，使用 LIKE、> 或其他運算子的查詢) 通常運算要求較高。在最壞的情況下，聯結一端的每條記錄必須與聯結另一端的每條記錄進行比較。由於執行時間會隨著記錄數量的平方而增加，因此此類查詢會有超過最大執行時間的風險。

若要了解 Athena 將如何事先執行查詢，您可以使用 EXPLAIN 陳述式。如需詳細資訊，請參閱 使用EXPLAIN和 EXPLAIN ANALYZE Athena 和 瞭解 Athena EXPLAIN 聲明結果。

減少窗口功能的範圍，或刪除它們

由於視窗函數是資源密集型操作，所以它們可能會使查詢執行緩慢甚至失敗，並顯示在此擴展因數下查詢耗盡的資源。視窗函數將其操作的所有記錄保留在記憶體中，以便計算其結果。當視窗非常大時，視窗函數可能會耗盡記憶體。

為了確保您的查詢在可用的記憶體限制內執行，請減少視窗函數操作的視窗大小。為此，您可以新增 PARTITIONED BY 子句或縮小現有分割區子句的範圍。

使用非視窗功能

有時，具有視窗函數的查詢可以在沒有視窗函數的情況下重寫。例如，您可以使用 ORDER BY 和 LIMIT，而不是使用 row_number 來尋找前 N 個記錄。您可以使用彙總函數 (例如 max_by、min_by 和 arbitrary)，而不是使用 row_number 或 rank 來重複記錄。

例如，假設您的資料集包含來自感應器的更新。感應器會定期報告其電池狀態，並包含一些中繼資料，例如位置。如果您想知道每個感應器及其位置的最新電池狀態，可以使用以下查詢：

SELECT sensor_id,
       arbitrary(location) AS location,
       max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id

因為每筆記錄的中繼資料 (例如位置) 都相同，因此您可以使用 arbitrary 函數從群組中選擇任何值。

要獲取最新的電池狀態，您可以使用 max_by 函數。max_by 函數從找到另一個資料欄的最大值的記錄中選擇資料欄的值。在此範例中，它會傳回群組內上次更新時間的記錄的電池狀態。與具有視窗函數的對等查詢相比，此查詢執行速度更快，而且使用的記憶體更少。

最佳化彙總

當 Athena 執行彙總時，它會使用 GROUP BY 子句中的資料欄，跨不同的工作節點分佈記錄。為了使相符記錄與群組的任務盡可能有效率，節點會嘗試將記錄放入記憶體中，但必要時會將記錄溢寫至磁碟。

避免在 GROUP BY 子句中包含備援資料欄也是個不錯的主意。由於較少的資料欄需要較少的記憶體，因此使用較少資料欄描述群組的查詢會更有效率。數值資料欄使用的記憶體也少於字串。例如，當您彙總同時具有數值類別 ID 和類別名稱的資料集時，請僅使用 GROUP BY 子句中的類別 ID 資料欄。

有時候，查詢會在 GROUP BY 子句中包含資料欄，以解決資料欄必須是 GROUP BY 子句的一部分或彙總表達式的事實。如果未遵循此規則，您可能會收到以下錯誤訊息：

EXPRESSION_ NOT _AGGREGATE: 行 1:8: '類別' 必須是彙總運算式或出現在 GROUP BY 子句中

若要避免在 GROUP BY 子句中新增備援資料欄，您可以使用任意函數，如下列範例所示。

SELECT country_id,
       arbitrary(country_name) AS country_name,
       COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id

ARBITRARY 函數會從群組中傳回任意值。當您知道群組中的所有記錄都具有相同的資料欄值，但該值無法識別群組時，此函數非常有用。

最佳化前 N 個查詢

ORDER BY 子句會以排序順序傳回查詢的結果。Athena 使用分散式排序，在多個節點上平行執行排序操作。

如果您並不需要對結果進行排序，請避免新增 ORDER BY 子句。另外，如果並非絕對必要，請避免新增 ORDER BY 到內部查詢。在許多情況下，查詢規劃程式可以移除備援排序，但不保證一定如此。此規則的例外是，如果內部查詢正在執行前 N 個操作，例如尋找最新的 N 個值或最常用的 N 個值。

當 Athena 發現 ORDER BY 與 LIMIT 時，就會明白您正在執行前 N 個查詢，並相應地使用專用操作。

注意

雖然 Athena 也經常可以偵測視窗函數 (例如使用前 N 個的 row_number)，但我們建議使用 ORDER BY 和 LIMIT 的較簡單版本。如需詳細資訊，請參閱減少窗口功能的範圍，或刪除它們。

僅包含必填資料欄

如果您並不需要資料欄，請不要將其包含在查詢中。查詢必須處理的資料越少，執行速度就越快。這可以減少所需的記憶體數量，以及必須在節點之間傳送的資料量。如果您使用的是單欄式檔案格式，減少資料欄數量也會減少從 Amazon S3 讀取的資料量。

Athena 對結果中的資料欄數量沒有特定限制，但是執行查詢的方式會限制可能的資料欄合併大小。資料欄的合併大小包括它們的名稱和類型。

例如，下列錯誤是由超出關係描述項大小限制的關係造成的：

GENERICINTERNAL_ERROR：IO.Airft. 字節碼。 CompilationException

若要解決此問題，請減少查詢中的資料欄數量或建立子查詢，並使用擷取較少量資料的 JOIN。如果您有在最外層查詢中執行 SELECT * 的查詢，則應將 * 變更為僅包含所需資料欄的清單。

使用近似值最佳化查詢

Athena 支援近似彙總函數，用於計算獨特值、最常出現的值、百分位數 (包括近似中位數)，以及建立直方圖。每當不需要確切值時，請使用這些函數。

與 COUNT(DISTINCT col) 操作不同，approx_distinct 使用的記憶體更少且執行速度更快。同樣，使用 numeric_histogram (而不是直方圖) 使用了近似方法，因此所需的記憶體更少。

最佳化 LIKE

您可以使用 LIKE 來查找相符字串，但對於長字串而言，此為計算密集型。regexp_like 函數在大多數情況下是一個更快的替代方案，並且還提供了更多的靈活性。

通常，您可以透過錨定要尋找的子字串來最佳化搜尋。例如，如果您正在尋找前綴，則使用 '會更好substr%' 而不是 '%substr%'。 或者，如果您使用regexp_like的是 '^substr'.

使用UNIONALL而不是 UNION

UNION ALL 和 UNION 是兩種將兩個查詢結果合併為一個結果的方法。UNION ALL 將第一個查詢中的記錄與第二個查詢中的記錄串連起來，且 UNION 會執行相同的操作，但也會移除重複項目。UNION 需要處理所有記錄並找到重複項目，此為記憶體和計算密集型，不過 UNION ALL 是一個相對快速的操作。除非您需要重複記錄，否則請使用 UNION ALL 以獲得最佳效能。

用UNLOAD於大型結果集

當查詢結果預期很大 (例如，數萬列或更多列) 時，請使用UNLOAD來匯出結果。在大多數情況下，這比執行規則查詢更快，並且使用 UNLOAD 也可以讓您更好地控制輸出。

查詢完成執行後，Athena 會將結果以單一未壓縮的CSV檔案形式存放在 Amazon S3 上。這比 UNLOAD 需要更長的時間，不僅因為結果未壓縮，而且還因為操作無法平行化。相反地，UNLOAD 會直接從工作節點寫入結果，並充分利用運算叢集的平行處理。此外，您可以配置為UNLOAD以壓縮格式和其他文件格式（例如JSON和 Parquet）寫入結果。

如需詳細資訊，請參閱UNLOAD。

使用CTAS或 Glue ETL 來實現常用的聚合

「具體化」查詢是一種透過儲存預先計算的複雜查詢結果 (例如，彙總和聯結) 以便在後續查詢中重複使用，來加速查詢效能的方法。

如果您的許多查詢包含相同的聯結和彙總，您可以將通用子查詢具體化為新資料表，然後針對該資料表執行查詢。您可以使用或像 Glue 這樣的從查詢結果建立資料表 (CTAS)專用ETL工具來創建新表ETL。

例如，假設您的儀表板中包含顯示訂單資料集的不同層面的小工具。每個小工具都有自己的查詢，但所有查詢會共用相同的聯結和篩選條件。訂單資料表會與明細項目資料表結合在一起，而且有一個篩選條件，可僅顯示過去三個月。如果您識別這些查詢的常用功能，您可以建立小工具可使用的新資料表。這樣可以減少重複並提高效能。缺點是您必須將資料表保持在最新狀態。

重複使用查詢結果

同一查詢在短時間內執行多次非常常見。例如，當多個人開啟相同的資料儀表板時，就會發生這種情況。執行查詢時，您可以告訴 Athena 重複使用先前計算的結果。您可以指定要重複使用的結果的最長期限。如果先前在該時間範圍內執行同一查詢，Athena 會傳回這些結果，而不是再次執行查詢。如需詳細資訊，請參閱此處《Amazon Athena 使用者指南》中的 在 Athena 中重複使用查詢，以及 AWS 大數據部落格中的使用 Amazon Athena 查詢結果重複使用降低成本和提升查詢效能。