在我協助客戶導入現代資料棧 (modern data stack) 的過程之中，常常見到一種奇怪的現象：分析工程師 (Analytic Engineer) 先嘗試用 SQL 物化視圖 (materialized view) 去改進效能，但是依然覺得不滿意之後，才再來研究 SQL 的索引 (index)。

真的要講的話，也不是不行，因為也沒有規定一定要先用 SQL 索引。然而，索引與物化視圖有一個關鍵差異：

• 物化視圖的本質是快取 (cache)。既然是快取，就必須要更新快取。而更新快取的實作細節，很容易會擴散到系統其它層，增加系統的複雜度。比方說，一旦使用了物化視圖，就得想清楚，何時要更新、何時不需要、要不要使用增量更新。相較之下，索引與其它不同層之間的耦合 (coupling) 少得多。換言之，同樣是改進效能的方式，索引要付出的日後維護成本比較少。

既然要討論 SQL 的效能議題，我們會分成下列 3 個方向來討論：

1. 資料倉儲的選擇
2. 資料表綱要 (table schema) 的設計
3. 索引 (index) 與物化視圖 (materialized view)

資料倉儲的選擇

選擇資料倉儲，大致有兩個方向：

1. 選擇 Google BigQuery, Snowflake, Databricks, AWS 的 Redshit 等等雲端資料倉儲 (cloud data warehouse)。
2. 自架資料倉儲。

選 1 的優點是，日後，幾乎不用擔心查詢 (Query) 會不夠快的問題。但是，缺點則是，需要擔心帳單的問題。選 2 的優點是不太需要擔心帳單，但是，之後，很快地就會發現，好像有一些查詢 (Query) 需要等待，或是說，需要最佳化。

我的建議是：

• 如果總資料量在 1T 以下，那先考慮 2 吧。
• 如果公司的預算是決策的重要考量之一，不妨考慮 2 ，在這邊有機會省很多錢。

現代資料棧可能會應用到許多的軟體，考慮到運維這些軟體所消耗的人力，都可以優先考慮付費使用這些軟體的雲端版本。上述這個通則，在雲端資料倉儲的例子，算是例外。原因是：在雲端資料倉儲裡由於資料量多，一旦沒有做充分的最佳化，就容易造成高額的帳單。

資料表綱要 (table schema) 的設計

在過去的資料倉儲技術還不發達、沒有 SSD、CPU 相當不快的年代，這個部分極為重要。也因此，過去的資料工程師會花費大量的時間，設計極其靈巧的資料表綱要 (table schema)，來避免查詢存取到冗餘的資料，以提高資料倉儲的查詢速度。

在現在的話，由於硬體已經有重大改進，在簡單的案例，不採用 star schema ，直接用 one big table 得到的查詢速度，很可能已經夠快了。

做效能改進的時候，如果接下來的第三種方法已經無法再讓系統更快時，不妨退回來這一步，好好地來思考，資料表綱要可以怎麼設計。

索引 (index) 與物化視圖 (materialized view)

SQL 有提供一個對於效能改進非常重要的指令：Explain。它可以讓使用者看出：某個查詢消耗了多少時間、觸碰了多少個 row、 join 的種類、有沒有使用到索引、index scan、index-only scan 等等。

搭配著 Explain 來做效能改進，具體的作法如下：

使用 Explain 的步驟

1. 當某個 Query 的速度頗慢，先用 Explain 量測一下 Query 的速度。
2. 若 Explain 出來的結果太混亂，覺得難以閱讀的話，可以上網找一些「協助使用者看懂 Explain 結果」的 SaaS 服務。
3. 對索引 (index) 與物化視圖 (materialized view) 做出一些調整，然後再回到第一步重新量測。

搭配 Explain 的調整

• SQL join 有三種。除了 hash join 之外，如果索引 (index) 有設定在 join key 上的話，就有機會改善效能。
  • nested loop join
  • merged join
  • hash join
• 設法把 index scan 變成 index-only scan 。比方說，在索引上設定 include column
• 有些 Query 會慢的原因是因為該 Query 會作用在視圖 (view)上，但是該視圖卻無法使用任何索引。這種情況下，可以考慮做物化視圖 (materialized view)，讓視圖可以運用到索引，就有機會加速。

後記

我曾看過許多做效能改進的人，做這件事的過程之中，熟讀了文件也做了多次的嘗試與實驗，結果卻徒勞無功或事倍功半，最關鍵的原因是：「他們沒有使用一個有效的效能量測工具，來輔助引導這個效能改進的過程。」

其它資源

1. 對 dbt 或 data 有興趣 👋？歡迎加入 dbt community 到 #local-taipei 找我們，也有實體 Meetup 請到 dbt Taipei Meetup 報名參加
2. 歡迎訂閱 PruningSuccess 電子報，主要談論軟體開發、資料處理、資料分析等議題。