避免 SQL 反模式

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

自我聯結

最佳做法:避免自我聯結。請改用窗型函式

通常自我聯結是用來計算列相依關係,結果可能會使輸出列數加倍,連帶導致作業效能低落。

不使用自我聯結,改用窗型 (分析) 函式可以減少查詢產生的額外位元組數。

資料偏移

最佳做法:如果查詢處理的是嚴重偏移至某些少數值的索引鍵,請盡早篩選資料。

分區偏移有時稱為資料偏移,指資料分割成大小非常不均的分區。這會造成運算單元之間傳送的資料數量不平衡。因為運算單元之間無法分享分區,所以如果其中一個分區特別大,效率就會變慢,甚至造成處理過大分區的運算單元毀損。

若分區索引鍵的值出現頻率高於任何其他值,分區就會變大。例如,按 user_id 欄位分組,而該欄位有許多 guestNULL 的項目。

運算單元的資源不堪負荷時,就會產生 resources exceeded 錯誤結果。若達到運算單元 (2 TB 壓縮記憶體) 的隨機排序限制,也會造成隨機排序寫入磁碟並進一步影響效能。採用固定費率計價方式的客戶可以增加分配的運算單元數。

當您檢查查詢說明計劃時,發現平均和最大計算時間出現重大差異,則資料可能已偏移。

如何避免資料偏移造成的效能問題:

  • 使用近似匯總函式 (如 APPROX_TOP_COUNT) 來判斷資料是否偏移。
  • 盡早篩選資料。

不平衡的聯結

當您使用 JOIN 子句時,也會出現資料偏移。因為 BigQuery 會隨機排序聯結兩端的資料,聯結索引鍵相同的所有資料都會歸入相同的資料分割。這種隨機排序會造成運算單元超載。

如何避免不平衡的聯結造成的效能問題:

  • 預先篩選表格中有不平衡索引鍵的列。
  • 如果可以,將查詢拆分成兩個查詢。

交叉聯結 (笛卡兒乘積)

最佳做法:避免聯結產生的輸出多於輸入。需要使用 CROSS JOIN 時,請預先匯總您的資料。

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

如果查詢工作完成,查詢計劃說明將顯示輸出列與輸入列。您可以將查詢修改為顯示 JOIN 子句兩端的列數並按聯結鍵分組,以確認笛卡爾乘積

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

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

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

最佳做法:避免使用單點 DML 陳述式 (一次只更新或插入一列),請採批次處理方式,集中進行更新和插入作業。

使用單點 DML 陳述式代表您想將 BigQuery 當成線上交易處理 (OLTP) 系統。BigQuery 會利用表格掃描和非點查閱,將重點放在線上分析處理 (OLAP) 上。如果您需要 OLTP 之類的行為 (單列更新或插入),請考慮使用設計目的為支援 OLTP 使用案例的資料庫,如 Google Cloud SQL

BigQuery DML 陳述式適用於大量更新。BigQuery 中的 UPDATEDELETE 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
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁