有時候 Query 效能會與我們期望的相差許多，優化 Query 效能可以有效增加例行作業的效率。本篇文章我們為您整理了企業級資料倉儲 Google Cloud BigQuery 所提供的 Query 效能優化技巧，適合目前有在使用 BigQuery 或是對於 BigQuery 有興趣的使用者參考。
Query 效能的評估要點
在評估 Query 效能時，有以下幾個影響的因素：
- Input 的數據及數據源 (I/O)：你的查詢會讀取多少資料量
- 節點間的傳輸：你的查詢會有多少資料量到下一階段？每個 slot 會被分配到多少資料
- 運算：您的查詢需要多少 CPU？
- Output：你的查詢會寫出多少資料量
- 查詢反面模式：您的查詢是否遵循 SQL 最佳實踐?
關於上述幾個評估要點，BigQuery 通常會在 query plan 提供解答，包括執行中的統計資料（如讀取的位元數及虛擬 CPU 使用的時間），同時也會顯示不同的執行階段，可幫助使用者診斷並改善 Query 效能。
此外，不論是什麼系統，提到優化效能往往需要一些取捨。例如，使用進階的 SQL 語法可以進行較複雜的計算，但同時也減少了 SQL 的可讀性，尤其是對非 SQL 專家；或是針對非關鍵性的工作進行 micro-optimizations，反而無法專注於開發應用程式新功能或是創造更好的優化成效。因此，建議針對手邊資料分析 pipeline 最重要的工作進行優化，以取得最佳的 ROI。
另外，即便是經過優化的 Query 效能也可能會因為 slot contention 而受到影響。舉例來說，如果現在有 10,000 個 slot 可分配給 6 個專案，並非每個專案都能分配到 2,000 個 slots，可能因此導致 Query 效能下降。如果經評估後，您的 Query 已無法再進一步優化，可考慮選用 BigQuery Reservation 功能，依據 Query 需求切換為 on-demand pricing（以用量計價）或是 flat-rate pricing（固定費率）方案，獲得最大的彈性與資源優化，請參考下方段落說明。
Capacity 及 Concurrency
BigQuery 把執行 SQL queries 所需的運算 capacity 拆解成一個個單位，稱為 slot，並依據 Query 的大小及複雜程度，計算每次 Query 需要多少個 slot，而 BigQuery 會依據使用者過往的使用狀況及花費，自動管理 Query 所需的 slot quota。
一般情況下，一個專案會使用到的 slot 數量其實不多，如果單純因為 Query 速度很慢而購買更多的 slot 並不等於能獲得更快的 query 效能，根本原因可能是 Query 沒寫好。但 Google 也表示，進行較大型或複雜的 Query 或是高度並行作業時，預留眾多的 slot 或許能改善 Query 的效能，在這樣的情況下，企業如想進一步改善 Query 效能，除了優化資料模型及 Query 之外，也可以考慮購買更多預留的 slot。
接下來分享 BigQuery 針對 Query 提供的兩種計價方式，分別是 on-demand pricing（以量計價）及 flat-rate pricing（固定費率）：
|
方案 |
計費方式 |
|
On-demand pricing |
此種計費模式會依照企業用量來收費，系統會根據各項查詢處理作業的位元組數來進行收費。BigQuery 每個月處理的前 1 TB 查詢資料為免費額度，超過額度後，則是每 TB 收取 5.75 美元（台灣 asia-east1 的定價）。 |
|
Flat-rate pricing |
採用此種計價模式，使用者每月會支付固定的資料分析費用，必須購買運算單元（虛擬 CPU），購買 CPU 時，您必須購買可用於執行查詢的專用處理容量。 |
Query 規劃與時間軸
以下，將介紹 BigQuery 提供給用戶做分析 Query 的工具，讓用戶可以查看 Query 在每個階段的使用量。
首先，透過BigQuery介面，使用者可以檢視現階段正在 BigQuery 平台上進行的 Query 進度及時間軸，你可透過 jobs.get API 來取得相關資訊；也可以搭配使用開源工具 BigQuery Visualiser，視覺化呈現 BigQuery 工作的執行階段。
當 BigQuery 在執行 Query 工作時，會將陳述性的 SQL 語法轉換為圖像，而圖像會細分為一系列的查詢階段，每個階段又會細分成更小的執行階段。 BigQuery 使用的是高度分散的平行架構來進行這些 Query 工作，並針對可能在同步進行的 Query 工作創建模組。
