# SQL優化查詢的效能與時間
此為翻譯文章，能夠了解到SQL如果用了許多SUM,COUNT，可能會讓查詢消耗許多效能，如何使用 ==CTE==(Common Table Expressions)讓查詢時間縮短了 80%以上 。
系統在高負載下逐漸吃不消，頁面載入變慢，儀表板頻頻超時，問題的根源是一個跨多個服務使用的關鍵查詢，隨著時間推移，這個查詢不斷增加連接、篩選條件和計算，最終成為整個系統中最慢的瓶頸。
我們嘗試了各種方法：建立索引、反正規化、使用查詢提示，但都無法解決問題。直到有位開發者運用了一個鮮為人知的 SQL 小技巧，將查詢重新改寫，才迎刃而解：
## 問題：擁腫的分析查詢
```typescript=
SELECT
users.id,
users.name,
COUNT(DISTINCT orders.id) AS order_count,
SUM(orders.amount) AS total_spent,
MAX(logins.timestamp) AS last_login
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN logins ON users.id = logins.user_id
WHERE users.created_at >= '2023-01-01'
GROUP BY users.id;
```
包含以下內容的表格上：
* 500 萬使用者
* 5000 萬訂單
* 8000 萬個登錄事件
平均耗時超過 9.3 秒
## 解決方法：可重用的內聯 CTE
問題不只是資料量龐大，而是查詢中出現了過多的重複連接（joins）與掃描。為了計算彙總資料，我們對每個使用者都需連接像是 ==orders== 和 ==logins== 這類的大型資料表，導致效能大幅下降。
因此，我們重構了查詢邏輯，導入了 CTE（Common Table Expressions）。這不僅讓邏輯更清晰，也讓我們能夠預先彙總資料並重複使用中間結果，大幅提升查詢效率。
```typescript=
WITH recent_users AS (
SELECT id, name
FROM users
WHERE created_at >= '2023-01-01'
),
order_stats AS (
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
),
last_logins AS (
SELECT user_id, MAX(timestamp) AS last_login
FROM logins
GROUP BY user_id
)
SELECT
u.id,
u.name,
o.order_count,
o.total_spent,
l.last_login
FROM recent_users u
LEFT JOIN order_stats o ON u.id = o.user_id
LEFT JOIN last_logins l ON u.id = l.user_id;
```
現在，每個聯接都會命中預先計算的、 分組的和較小的數據集，而不是原始Table。
## 基準測試結果
我們針對運行兩個版本。以下是所看到的數據：
```tex=
| Query Version | Avg Time | % Improvement |
| ------------- | -------- | ---------------- |
| Original | 9.3s | - |
| CTE Refactor | 1.8s | **80.6% faster** |
```
指數化之前有所説明，但這次 CTE 重組帶來了最大的收益。
## 這樣做的好處
這種加速背後有兩個關鍵原則：
### 1. 避免重複工作
原本查詢中的每個 ==LEFT JOIN== 都會掃描整個資料表，並在每一列上重新計算彙總資料，造成大量重複運算與資源浪費。
透過預先彙總的 CTE（Common Table Expressions），查詢規劃器（query planner）只需處理經過簡化的中間結果，大幅減少資料量與運算成本，整體效能因此顯著提升。
### 2. 打破複雜性
複雜的大型查詢容易讓查詢最佳化器（optimizer）難以做出有效判斷。
將邏輯拆分為多個 CTE（Common Table Expressions），可以讓資料庫更清楚地理解每個子查詢的結構，進而更有效地進行子計劃（subplans）的平行處理與最佳化，提升整體執行效能與可維護性。
### 值得關注的問題
在 PostgreSQL 中，CTE（Common Table Expressions）過去會被視為最佳化的障礙，意思是它們不會被內嵌（inlined）進主查詢中。從 PostgreSQL 12 開始，非具體化（non-materialized）的 CTE 預設會被內嵌，除非你明確使用 ==MATERIALIZED== 關鍵字。
CTE 並不適合用在單純的篩選條件上；它真正發揮效用的情境是當你需要重複使用查詢結果，或希望減少資料掃描的範圍與次數。
使用 ==EXPLAIN ANALYZE== 來觀察實際的執行計畫，以確認 CTE 是否帶來效能提升。有時候，使用子查詢（subquery）反而會比 CTE 表現更好，這取決於資料庫的最佳化策略與實際資料分布。
### 學到了什麼
* CTE（Common Table Expressions）不只是為了讓查詢更易讀，在用來做預先彙總時，它也能大幅提升效能。
* 查詢的複雜度常常是悄悄累積的 —— 原本執行快速的查詢，隨著需求成長，逐漸演變成難以擴充的效能瓶頸，而我們往往沒有即時察覺。
* 將查詢邏輯拆解，不只讓開發者更容易理解，也有助於查詢規劃器（query planner）更有效地做出最佳化決策，提升整體查詢效率。
### 優化之前...
* 從 ==pg_stat_statements== 中找出執行最慢的查詢，這是優化的起點。
* 接著，檢查是否有多餘的資料表連接（joins）或重複的查詢邏輯，這些常是效能瓶頸的根源。
* 每次修改後都要進行效能測試（benchmark）—— 在一個資料結構中有效的方法，可能在另一個架構下反而造成反效果。
## 最後的思考
* CTE（Common Table Expressions）常被當作提升查詢可讀性的工具來教學，但當它用於資料預先彙總與邏輯切分時，往往能帶來顯著的效能改善 —— 即使不動用索引或調整資料結構也能達成。
* 這個方法幫助我們避免了一次大規模的系統重構。如果你也遇到長時間執行的 JOIN 或緩慢的分析查詢，不妨試著用 CTE 重構查詢邏輯。
* 它可能不只替你省下數天的排查時間，還能節省運算成本。
>資料翻譯來源:https://medium.com/@kanishks772/we-cut-80-of-our-query-time-by-using-this-little-known-sql-pattern-fadec2bdb592
Sign in with Wallet
Connect another wallet