跳至主要內容
LinPolly
文章 程式開發

SQL 查詢效能調校：讓慢查詢快 10 倍的實戰技巧

SQL 查詢效能調校：讓慢查詢快 10 倍的實戰技巧

資料庫查詢效能是後端開發中最常遇到的瓶頸之一。我在工作中處理過不少慢查詢，有些加個索引就解決了，有些需要重寫整個查詢邏輯。這篇文章整理我實際用過的調校技巧，以 PostgreSQL 為主要範例。

首先：找到慢查詢

-- PostgreSQL：查看慢查詢（需要 pg_stat_statements 擴展）
SELECT 
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 20;

-- 設定慢查詢記錄閾值（在 postgresql.conf）
-- log_min_duration_statement = 1000  -- 超過 1 秒就記錄

EXPLAIN ANALYZE：理解查詢計畫

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 20;

輸出中要注意的關鍵字：

  • Seq Scan：全表掃描，大表上很慢
  • Index Scan：使用索引，通常快
  • Hash Join / Nested Loop：JOIN 的執行方式
  • cost=X..Y：預估成本，Y 越大越慢
  • actual time=X..Y：實際執行時間（毫秒）
  • rows=N：實際回傳行數

索引優化

基本索引

-- 在常用於 WHERE 條件的欄位建立索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 複合索引：注意欄位順序（最常用的過濾條件放前面）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- 這個索引可以支援：WHERE user_id = X 或 WHERE user_id = X AND status = Y
-- 但不能支援：WHERE status = Y（沒有 user_id）

部分索引（Partial Index）

-- 只對特定條件的資料建立索引，更小、更快
CREATE INDEX idx_pending_orders
ON orders(created_at)
WHERE status = 'pending';

-- 查詢時只要 WHERE 包含相同條件，就會使用這個索引
SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

表達式索引

-- 在函數結果上建立索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 查詢時也要用相同表達式
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

常見效能陷阱

1. 在 WHERE 中對欄位使用函數

-- 壞：對索引欄位使用函數，無法使用索引
SELECT * FROM orders WHERE DATE(created_at) = '2025-06-01';

-- 好：改用範圍查詢
SELECT * FROM orders
WHERE created_at >= '2025-06-01'
  AND created_at < '2025-06-02';

2. N+1 查詢問題

-- 壞：先查 100 個用戶，再對每個用戶查訂單（101 次查詢）
SELECT * FROM users LIMIT 100;
-- 然後對每個 user.id 執行：
-- SELECT * FROM orders WHERE user_id = ?

-- 好：一次 JOIN 查詢
SELECT
    u.id,
    u.name,
    COALESCE(o.order_count, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON o.user_id = u.id
LIMIT 100;

3. SELECT * 的問題

-- 壞：拉取所有欄位，包含大型 TEXT/BLOB 欄位
SELECT * FROM articles WHERE category_id = 1;

-- 好：只取需要的欄位
SELECT id, title, published_at, excerpt
FROM articles
WHERE category_id = 1;

善用 CTE 和視窗函數

-- 用 CTE 讓複雜查詢更易讀
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS total
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
),
ranked_months AS (
    SELECT
        month,
        total,
        LAG(total) OVER (ORDER BY month) AS prev_month_total
    FROM monthly_sales
)
SELECT
    month,
    total,
    ROUND((total - prev_month_total) / prev_month_total * 100, 2) AS growth_rate
FROM ranked_months
ORDER BY month;

分頁查詢優化

-- 壞：OFFSET 在大資料集非常慢（需要跳過大量記錄）
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000;

-- 好：Keyset Pagination（游標分頁）
SELECT * FROM posts
WHERE id > :last_seen_id  -- 上次最後一筆的 ID
ORDER BY id
LIMIT 20;

統計資料更新

-- PostgreSQL 的查詢最佳化器依賴統計資料
-- 如果統計資料過時，查詢計畫可能不準確
ANALYZE orders;        -- 更新特定資料表的統計資料
VACUUM ANALYZE orders; -- 回收空間 + 更新統計資料

-- 查看資料表統計資訊
SELECT
    tablename,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

效能調校沒有萬靈丹，每個問題都需要先分析、再對症下藥。EXPLAIN ANALYZE 是最重要的工具，看懂查詢計畫後，優化方向通常就很清楚了。

#Supabase

分享這篇文章