最佳化調整查詢運算

本文提供最佳化查詢效能的最佳做法。

執行查詢時，您可以在 Google Cloud 主控台中查看查詢計畫。您也可以使用 INFORMATION_SCHEMA.JOBS* 檢視畫面或 jobs.get REST API 方法，要求執行詳細資料。

查詢計畫包含查詢階段和步驟的詳細資料。這些詳細資料有助您找出改善查詢效能的方法。舉例來說，如果您發現某個階段寫入的輸出內容比其他階段多得多，可能表示您需要在查詢中更早進行篩選。

如要進一步瞭解查詢計畫，並查看查詢計畫資訊如何協助您改善查詢效能的範例，請參閱「取得查詢效能深入分析」。解決查詢效能洞察資料後，您可以執行下列工作，進一步改善查詢：

• 減少要處理的資料
• 最佳化查詢作業
• 減少查詢的輸出內容
• 避免 SQL 反模式

減少要處理的資料

您可以使用下列各節所述的選項，減少需要處理的資料。

建議不要使用 SELECT *

最佳做法：僅查詢所需欄位，控管投影。

投影指的是查詢作業讀取的欄位數。投影多餘的資料欄會產生額外的 (浪費) I/O 和具體化 (寫入結果)。

• 使用資料預覽選項如要進行資料實驗或是探索資料，請使用其中一個資料預覽選項，不要使用 SELECT *。
• 查詢特定欄：將 LIMIT 子句套用至 SELECT * 查詢不會影響讀取的資料量。系統會收取您讀取整個資料表中所有位元組的費用，也會收取超過免費版配額的查詢數費用。相反地，只要查詢需要的資料欄即可。例如，使用 SELECT * EXCEPT 將一或多個資料欄排除在結果之外。
• 使用分區資料表。如果您確實需要查詢資料表中的每個資料欄，但範圍限定在資料子集，請考慮：
  • 在目的地資料表中將結果具體化並改為查詢該資料表。
  • 將資料表分區並查詢相關分區。例如，使用 WHERE _PARTITIONDATE="2017-01-01" 只查詢 2017 年 1 月 1 日的分區。

• 使用 SELECT * EXCEPT。查詢資料子集或使用 SELECT * EXCEPT，可以大幅減少查詢讀取的資料量。除了節省成本，還可透過減少資料 I/O 量和查詢結果所需的具體化量，來改善效能。

  SELECT * EXCEPT (col1, col2, col5)
  FROM mydataset.newtable

避免使用過多萬用字元資料表

最佳做法：查詢萬用字元資料表時，請務必使用最精確的前置字串。

透過精簡的 SQL 陳述式使用萬用字元查詢多個資料表。萬用字元資料表是指符合萬用字元運算式的資料表集合。如果資料集包含下列資源，萬用字元資料表就很實用：

• 多個具備相容結構定義且命名類似的資料表
• 資料分區資料表

注意： 如果您的資料允許，請使用時間分區資料表，而非資料分割資料表。詳情請參閱「避免過度分割資料表」一文。

查詢萬用字元資料表時，請在通用資料表前置字串後指定萬用字元 (*)。例如，FROM bigquery-public-data.noaa_gsod.gsod194* 會查詢自 1940 年代以來的所有資料表。

相較於較短的前置字串，較詳細的前置字串查詢效果較好。舉例來說，FROM bigquery-public-data.noaa_gsod.gsod194* 的效能優於 FROM bigquery-public-data.noaa_gsod.*，因為符合萬用字元的資料表較少。

避免使用依日期進行資料分割的資料表

最佳做法：請勿使用依日期進行資料分割的資料表 (又稱為以日期命名的資料表) 來取代時間分區資料表。

分區資料表的執行效果比以日期命名的資料表好。當您建立依日期進行資料分割的資料表時，BigQuery 必須為每個以日期命名的資料表保留一份結構定義與中繼資料的複本。同時，當使用以日期命名的資料表時，BigQuery 可能需要驗證每個查詢資料表的權限。這個做法也會增加查詢的負擔，並影響查詢效能。

避免使用資料分割資料表

最佳做法：避免建立太多資料表資料分割。如果您要依日期對資料表進行資料分割，請改為使用時間分區資料表。

資料表資料分割是指將大型資料集分成單獨的資料表，並為每個資料表名稱加上尾碼。如果您要依日期對資料表進行資料分割，請改為使用時間分區資料表。

由於 BigQuery 儲存空間費用低廉，您不必像在關聯式資料庫系統中那樣，為了節省成本而調整資料表。建立大量資料表區塊會對效能造成影響，而這會抵銷任何成本效益。

資料分割資料表需要 BigQuery 為每個資料分割維持結構定義、中繼資料與權限。由於維持每個資料分割的資訊會增加額外負擔，因此對資料表進行資料分割會影響查詢效能。

查詢讀取的資料量和來源可能會影響查詢效能和費用。

修整分區查詢

最佳做法：查詢分區資料表時，如要使用分區資料表的分區進行篩選，請使用下列資料欄：

• 擷取時間分區資料表：使用虛擬資料欄 _PARTITIONTIME
• 如果是分區資料表 (例如以時間單位資料欄為基礎和整數範圍)，請使用分區資料欄。

對於時間單位分區資料表，使用 _PARTITIONTIME 或分區資料欄篩選資料時，您可以指定日期或日期範圍。例如，下列 WHERE 子句使用 _PARTITIONTIME 虛擬欄位指定 2016 年 1 月 1 日至 2016 年 1 月 31 日之間的分區：

WHERE _PARTITIONTIME
BETWEEN TIMESTAMP("20160101")
AND TIMESTAMP("20160131")

查詢只會處理該日期範圍指定的分區中資料。篩選分區可以提高查詢成效並降低成本。

使用 JOIN 之前減少資料

最佳做法：執行匯總作業，減少在 JOIN 子句之前處理的資料量。

使用 GROUP BY 子句搭配匯總函式會耗費大量運算資源，因為這類查詢會使用洗牌。由於這些查詢的運算量很大，因此請務必只在必要時使用 GROUP BY 子句。

如果查詢含有 GROUP BY 和 JOIN，請在查詢的較前位置執行匯總，以減少處理的資料量。例如，以下查詢會在兩個大型資料表上執行 JOIN，但不會事先進行任何篩選：

WITH
  users_posts AS (
  SELECT *
  FROM
    `bigquery-public-data`.stackoverflow.comments AS c
  JOIN
    `bigquery-public-data`.stackoverflow.users AS u
  ON
    c.user_id = u.id
  )
SELECT
  user_id,
  ANY_VALUE(display_name) AS display_name,
  ANY_VALUE(reputation) AS reputation,
  COUNT(text) AS comments_count
FROM users_posts
GROUP BY user_id
ORDER BY comments_count DESC
LIMIT 20;

這項查詢會預先匯總註解數量，藉此減少 JOIN 讀取的資料量：

WITH
  comments AS (
  SELECT
    user_id,
    COUNT(text) AS comments_count
  FROM
    `bigquery-public-data`.stackoverflow.comments
  WHERE
    user_id IS NOT NULL
  GROUP BY user_id
  ORDER BY comments_count DESC
  LIMIT 20
  )
SELECT
  user_id,
  display_name,
  reputation,
  comments_count
FROM comments
JOIN
  `bigquery-public-data`.stackoverflow.users AS u
ON
  user_id = u.id
ORDER BY comments_count DESC;

注意： 含有一般資料表運算式 (CTE) 的 WITH 子句是用於查詢可讀性，而非效能。我們無法保證新增 WITH 子句會導致 BigQuery 具體化暫時性中繼資料表，並重複使用暫時性結果做為多個參照。視查詢最佳化工具的決定而定，WITH 子句可能會在查詢中評估多次。

使用 WHERE 子句

最佳做法：使用 WHERE 子句限制查詢傳回的資料量。盡可能在 WHERE 子句中使用 BOOL、INT64、FLOAT64 或 DATE 欄。

對 BOOL、INT64、FLOAT64 和 DATE 欄執行的作業通常比對 STRING 或 BYTE 欄執行的作業更快。盡可能使用在 WHERE 子句中使用其中一種資料類型的資料欄，藉此減少查詢傳回的資料量。

指定主鍵和外鍵限制

最佳做法：如果資料表資料符合主鍵或外鍵限制的資料完整性規定，請在資料表結構定義中指定鍵限制。

查詢引擎可使用鍵限制來最佳化查詢計畫。如需詳細資訊，請參閱「使用 BigQuery 主鍵和外鍵進行彙整最佳化」一文。

BigQuery 不會自動檢查資料完整性，因此您必須確保資料符合資料表結構定義中的限制條件。如果您在含有指定限制的資料表中未維持資料完整性，查詢結果可能會不準確。

使用具體化檢視表

最佳做法：使用具象化檢視畫面預先計算查詢結果，以提升效能和效率。

具體化檢視表是預先運算的檢視表，會定期快取查詢結果，以提升效能和效率。BigQuery 會利用已實作的檢視表中預先計算的結果，並盡可能只讀取基礎資料表中的變更，以便計算最新結果。您可以直接查詢已實作的檢視表，也可以讓 BigQuery 最佳化工具使用這些檢視表，處理對基本資料表的查詢。

使用 BI Engine

最佳做法：使用 BigQuery BI Engine 快取您最常用到的資料，加快查詢速度。

建議您在要計算查詢的專案中，新增 BI Engine 保留項目。BigQuery BI Engine 會使用向量化查詢引擎，加快 SELECT 查詢效能。

使用搜尋索引

最佳做法：如果您需要在大型資料表中查找個別資料列，請使用搜尋索引來提升查詢效率。

搜尋索引項目是一種資料結構，可透過 SEARCH 函式進行非常有效率的搜尋，但也可以使用其他運算子和函式加速查詢，例如等於 (=)、IN 或 LIKE 運算子，以及特定字串和 JSON 函式。

最佳化查詢作業

您可以使用下列各節所述的選項，改善查詢作業。

避免重複轉換資料

最佳做法：如果您使用 SQL 執行 ETL 作業，請避免重複轉換相同資料。

例如，如果您是使用 SQL 來刪減字串或使用規則運算式擷取資料，則在目標資料表中將轉換的結果具體化的效能更高。規則運算式之類的函式需要額外的計算。在不增加轉換負擔的情況下查詢目標資料表會更有效率。

避免對相同的 CTE 進行多次評估

最佳做法：使用程序語言、變數、暫時性資料表和自動到期的資料表，保留計算結果，並在稍後的查詢中使用。

如果查詢包含在查詢中多個位置使用的通用表格運算式 (CTE)，這些運算式可能會在每次參照時進行評估。查詢最佳化工具會嘗試偵測可執行一次的查詢部分，但這不一定可行。因此，使用 CTE 可能無法減少內部查詢的複雜度和資源消耗量。

您可以根據 CTE 傳回的資料，將 CTE 的結果儲存在標量變數或臨時表格中。

避免重複的資料彙整與子查詢

最佳做法：避免重複彙整相同的資料表及使用相同的子查詢。

相較於重複彙整資料，使用巢狀、重複的資料來表示關係可能更有效率。若使用巢狀、重複的資料，彙整作業所需的通訊頻寬就不會對效能產生影響。如此一來，您也能節省重複讀取及寫入相同資料所產生的 I/O 成本。詳情請參閱「使用巢狀和重複的欄位」。

同樣的，重複執行相同的子查詢也會因重複的查詢處理作業而對效能造成影響。如果您要在多個查詢中使用相同的子查詢，請考慮具體化資料表中的子查詢結果，然後在查詢中使用經過具體化的資料。

具體化子查詢結果可提升效能，並減少 BigQuery 讀取和寫入的資料總量。雖然儲存具體化資料會產生少量費用，不過比起重複 I/O 與查詢處理作業的效能影響，仍是利大於弊。

將結合模式最佳化

最佳做法：如果查詢要彙整多個資料表的資料，請從最大的資料表開始，以便最佳化彙整模式。

使用 JOIN 子句建立查詢時，請考量合併資料的順序。GoogleSQL 查詢最佳化工具會決定哪個資料表應位於哪個彙整作業的哪一側。最佳做法是先放置資料列數量最多的表格，接著放置資料列數量最少的表格，然後依大小遞減順序放置其餘的表格。

將大型資料表放在 JOIN 的左邊，並將小型資料表放在 JOIN 的右邊時，便建立了傳播結合。傳播結合會將較小資料表中的所有資料傳送至處理較大資料表的每個運算單元。建議您先執行傳播結合。

如要查看 JOIN 中的資料表大小，請參閱「取得資料表相關資訊」。

最佳化 ORDER BY 子句

最佳做法：使用 ORDER BY 子句時，請務必遵循下列最佳做法：

• 在最外側查詢或 window 子句中使用 ORDER BY。將複雜作業推送至查詢結尾。除非是在窗型函式中使用 ORDER BY 子句，否則在查詢期間放入這類子句會大幅影響效能。

  為查詢進行排序的另一種技巧是將複雜的作業 (例如規則運算式和數學函式) 排至查詢的尾端。這項技巧可減少在執行複雜作業前要處理的資料。

• 使用 LIMIT 子句。如果您要排序的值非常大量，但不需要全部傳回，請使用 LIMIT 子句。例如，下列查詢會排序非常大型的結果集，並擲回 Resources exceeded 錯誤。查詢會依 mytable 中的 title 欄排序。title 資料欄中包含數百萬個值。

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title;

  如要移除錯誤，請使用類似下方內容的查詢：

  SELECT
  title
  FROM
  `my-project.mydataset.mytable`
  ORDER BY
  title DESC
  LIMIT
  1000;

• 使用窗型函式。如果要排序的值非常多，請使用窗型函式，並在呼叫窗型函式前限制資料。舉例來說，下列查詢會列出 10 個最資深的 Stack Overflow 使用者及其排名，其中最資深的帳戶排名最低：

  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY user_rank ASC
  LIMIT 10;

  這項查詢大約需要 15 秒才能執行。這項查詢會在查詢結尾使用 LIMIT，但不會在 DENSE_RANK() OVER 窗口函式中使用。因此，這項查詢要求所有資料必須在單一工作站節點上排序。

  相反地，您應在計算時間區間函式之前限制資料集，以便提升效能：

  WITH users AS (
  SELECT
  id,
  reputation,
  creation_date,
  FROM bigquery-public-data.stackoverflow.users
  ORDER BY creation_date ASC
  LIMIT 10)
  SELECT
  id,
  reputation,
  creation_date,
  DENSE_RANK() OVER (ORDER BY creation_date) AS user_rank
  FROM users
  ORDER BY user_rank;

  這項查詢的執行時間約為 2 秒，並傳回與先前查詢相同的結果。

  需要注意的是，DENSE_RANK() 函式會為各年份的資料進行排名，因此如果排名資料跨越多個年份，這些查詢就不會提供相同的結果。

將複雜查詢分割成較小的查詢

最佳做法：利用多陳述式查詢功能和儲存程序，將原本設計為單一複雜查詢的運算，改為多個較小且簡單的查詢。

複雜的查詢、REGEX 函式，以及分層子查詢或彙整作業可能會耗費大量資源，執行速度也較慢。嘗試將所有運算納入一個巨大的 SELECT 陳述式 (例如將其設為檢視畫面)，有時會造成反模式，並可能導致查詢速度變慢且耗用大量資源。在極少數情況下，內部查詢計畫會變得非常複雜，導致 BigQuery 無法執行。

將複雜查詢分割後，即可在變數或臨時資料表中實現中繼結果。接著，您可以在查詢的其他部分中使用這些中間結果。當您需要在查詢的多個位置使用這些結果時，這種做法就會越來越實用。

通常，您可以透過臨時表做為資料實例化點，更清楚表達查詢部分的真正意圖。

使用巢狀和重複的欄位

如要瞭解如何使用巢狀和重複的欄位將資料儲存空間去標準化，請參閱「使用巢狀和重複的欄位」。

在彙整中使用 INT64 資料類型

最佳做法：在彙整中使用 INT64 資料類型，而非 STRING 資料類型，以降低成本並改善比較效能。

BigQuery 不會像傳統資料庫一樣為主鍵建立索引，因此彙整資料欄的範圍越廣，比較所需的時間就越長。因此，在彙整作業中使用 INT64 資料類型比使用 STRING 資料類型更省錢且更有效率。

減少查詢輸出

您可以使用下列各節所述的選項，減少查詢輸出內容。

將大型結果集具體化

最佳做法：請考慮將大型結果集具體化並寫入目標資料表。寫入大型結果集會對效能與成本造成影響。

BigQuery 將快取結果限制在壓縮後約 10 GB。傳回較大結果的查詢會超過這個限制，而經常導致下列錯誤：Response too large。

如果您從含有可觀資料量的資料表中選取大量欄位，就很可能產生這個錯誤。若執行將資料正規化而未進行縮減或匯總的 ETL 查詢，則寫入快取結果時也可能會發生問題。

您可以使用下列選項克服快取結果大小的限制：

• 使用篩選器限制結果集
• 使用 LIMIT 子句減少結果集，尤其是在使用 ORDER BY 子句時
• 將輸出資料寫入目的地資料表

您可以使用 BigQuery REST API 逐頁瀏覽結果。詳情請參閱「分頁檢視資料表資料」。

注意： 將非常大型的結果集寫入目的地資料表會影響查詢效能 (I/O)。此外，儲存目的地資料表也會產生一筆小額費用。您可以使用資料集的預設資料表到期時間，自動刪除大型目的地資料表。詳情請參閱儲存空間最佳做法中的「使用到期日設定」。

避免 SQL 反模式

以下最佳做法將說明如何避免會影響 BigQuery 效能的查詢反模式。

避免自我彙整

最佳做法：不使用自連接，改為使用窗型 (分析) 函式或 PIVOT 運算子。

通常自我聯結是用來計算列相依關係，使用自訂彙整的結果可能會使輸出列數平方，此輸出量增加可能會導致效能低落。

避免交叉彙整

最佳做法：避免使用產生之輸出多於輸入的彙整。需要使用 CROSS JOIN 時，請預先匯總資料。

交叉聯結查詢是指第一個資料表中的每一個資料列會聯結至第二個資料表中的每一個資料列，且兩端的索引鍵會重複。最壞的輸出情況是左邊表格的列數乘以右邊表格的列數。在極少數的情況下，系統可能無法完成查詢。

如果查詢工作完成執行，查詢計劃說明會顯示輸出資料列與輸入資料列。您可以將查詢修改為顯示 JOIN 子句兩端的列數並按彙整索引鍵分組，以確認笛卡爾乘積。

如何避免聯結產生的輸出多於輸入造成的效能問題：

• 使用 GROUP BY 子句預先匯總資料。
• 使用窗型函式。窗型函式通常比使用交叉聯結更有效率。詳情請參閱窗型函式。

避免更新或插入單列的 DML 陳述式

最佳做法：避免使用更新或插入單列的 DML 陳述式。請採批次處理方式，集中進行更新和插入作業。

使用單點 DML 陳述式代表您想將 BigQuery 當成線上交易處理 (OLTP) 系統。BigQuery 著重於使用資料表掃描進行線上分析處理 (OLAP) 上，而不是點查詢。如果您需要與 OLTP 類似的功能 (單列更新或插入)，請考慮使用專為支援 OLTP 用途設計的資料庫，例如 Cloud SQL。

BigQuery DML 陳述式適用於批次更新。BigQuery 中的 UPDATE 和 DELETE DML 陳述式較適合定期重寫資料，而非單列異動。INSERT DML 陳述式適合少量使用。插入作業使用的修改配額與載入工作相同。如果您的使用案例涉及頻繁的單列插入，請考慮改為串流資料。

如果批次處理 UPDATE 陳述式在非常長的查詢中產生許多值組，則可能會達到 256 KB 的查詢長度限制。如果想要避開查詢長度限制的問題，請考慮是否可以依據邏輯準則而非使用一連串的直接值組取代來處理更新作業。

例如，您可以將一組取代記錄載入到另一個資料表中，然後編寫 DML 陳述式，使其在未更新的資料欄相符時，更新原始資料表中的所有值。例如，如果原始資料是在資料表 t 中，而更新是暫存在資料表 u 中，則查詢會與以下內容類似：

UPDATE
  dataset.t t
SET
  my_column = u.my_column
FROM
  dataset.u u
WHERE
  t.my_key = u.my_key

為名稱相似的資料欄使用別名

最佳做法：在查詢 (包括子查詢) 中使用名稱相似的資料欄時，請使用資料欄和表格別名。

別名可協助您找出除了初始的欄參照外，系統參照的其他欄和表格。使用別名有助於您瞭解並解決 SQL 查詢中的問題，包括找出子查詢中使用的資料欄。

後續步驟

• 瞭解如何降低成本。
• 瞭解如何最佳化儲存空間。
• 瞭解如何最佳化函式。