避免 SQL 反模式

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

自連接

最佳做法:避免使用自連接。請改為使用窗型函式

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

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

資料偏移

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

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

若分區索引鍵的值出現頻率高於任何其他值,分區就會變大。舉例來說,如果 guestNULL 有許多項目,依 user_id 欄位分組就會發生這種情形。

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

如果您在檢查查詢說明計劃時,發現平均和最大計算次數之間有顯著的差異,則資料可能已偏移。

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

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

不平衡的彙整

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

如要避免不平衡的彙整造成的效能問題:

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

交叉聯結 (笛卡兒乘積)

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

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

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

如要避免彙整產生的輸出多於輸入造成的效能問題:

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

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

最佳做法:避免使用單點 DML 陳述式 (一次更新或插入 1 個資料列)。請批次處理更新和插入作業。

使用單點 DML 陳述式代表您想將 BigQuery 當成線上交易處理 (OLTP) 系統。BigQuery 著重於使用資料表掃描進行線上分析處理 (OLAP) 上,而不是點查詢。如果您需要與 OLTP 類似的功能 (單列更新或插入),請考慮使用專為支援 OLTP 用途設計的資料庫,例如 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
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁
需要協助嗎?請前往我們的支援網頁