查詢調優實戰

EXPLAIN 告訴你「問題在哪」，本篇則教你「如何解決」。我們將介紹實際的調優技巧和工具。

一、統計資訊與 ANALYZE

PostgreSQL 使用統計資訊來估算查詢成本。統計過時會導致執行計畫錯誤。

手動更新統計

sql

-- 更新單張表
ANALYZE users;

-- 更新整個資料庫
ANALYZE;

-- 查看統計資訊
SELECT * FROM pg_stats WHERE tablename = 'users' AND attname = 'email';

自動分析

PostgreSQL 有 autovacuum 背景程序自動執行 ANALYZE：

sql

-- 查看設定
SHOW autovacuum_analyze_threshold;     -- 預設 50
SHOW autovacuum_analyze_scale_factor;  -- 預設 0.1 (10%)

-- 計算觸發條件
-- 當變更列數 > threshold + scale_factor * 總列數 時觸發
-- 例如 10 萬列的表：50 + 0.1 * 100000 = 10050 次變更後分析

調整統計精度

sql

-- 預設統計目標是 100（樣本數）
ALTER TABLE users ALTER COLUMN email SET STATISTICS 200;
ANALYZE users;

-- 全域設定
SET default_statistics_target = 200;

二、關鍵參數調整

shared_buffers

資料庫快取大小，建議設為系統記憶體的 25%：

conf

# postgresql.conf
shared_buffers = 4GB  # 16GB RAM 的機器

work_mem

單一操作（排序、雜湊）可用的記憶體：

conf

work_mem = 64MB  # 預設只有 4MB

> **注意總記憶體**

複雜查詢可能有多個排序操作，每個都會使用 work_mem。總記憶體 ≈ work_mem × 並行連線數 × 操作數

maintenance_work_mem

維護操作（VACUUM、CREATE INDEX）可用記憶體：

conf

maintenance_work_mem = 512MB

effective_cache_size

告訴優化器系統有多少快取（不實際分配），影響成本估算：

conf

effective_cache_size = 12GB  # 約為 RAM 的 75%

random_page_cost

隨機 I/O 成本，SSD 可以調低：

conf

random_page_cost = 1.1  # SSD（預設 4.0 是磁碟）

三、連線池 (Connection Pooling)

問題：連線開銷高

每個 PostgreSQL 連線約佔 5-10MB 記憶體，且建立連線耗時。

解法：PgBouncer

PgBouncer 是輕量級連線池：

ini

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = users.txt
pool_mode = transaction  # 推薦
max_client_conn = 1000
default_pool_size = 20

池模式：

模式	說明	限制
session	客戶端連線持續佔用	無限制
transaction	交易結束後釋放（推薦）	不能用 SET 變數
statement	每個 SQL 後釋放	不能用交易

Node.js 連線池設定

javascript

const { Pool } = require("pg");

const pool = new Pool({
  host: "localhost",
  port: 5432,
  database: "mydb",
  user: "admin",
  password: "secret",
  max: 20, // 最大連線數
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

四、慢查詢分析

開啟慢查詢日誌

conf

# postgresql.conf
log_min_duration_statement = 1000  # 記錄超過 1 秒的查詢
log_statement = 'none'             # 不記錄全部（太多）
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

pg_stat_statements 擴充

統計所有查詢的執行次數和時間：

sql

-- 啟用
CREATE EXTENSION pg_stat_statements;

-- 配置 (postgresql.conf)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- 重啟後查詢
SELECT
    calls,
    total_exec_time::INTEGER AS total_ms,
    mean_exec_time::INTEGER AS mean_ms,
    rows,
    LEFT(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

欄位說明：

欄位	說明
calls	執行次數
total_exec_time	總執行時間
mean_exec_time	平均執行時間
rows	總返回列數

識別問題查詢

sql

-- 最耗時的查詢（優先優化）
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 最慢的單次查詢
SELECT query, max_exec_time
FROM pg_stat_statements
ORDER BY max_exec_time DESC
LIMIT 10;

-- 執行最頻繁的查詢（可能需要快取）
SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

五、常見優化技巧

1. 避免 SELECT *

sql

-- 不好
SELECT * FROM orders WHERE customer_id = 123;

-- 好：只選需要的欄位
SELECT id, amount, created_at FROM orders WHERE customer_id = 123;

2. 使用 LIMIT 限制結果

sql

-- 分頁時避免大 OFFSET
-- 不好
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- 好：Keyset Pagination
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;

3. 避免 OR 條件

sql

-- OR 可能無法使用索引
SELECT * FROM users WHERE email = 'a@x.com' OR phone = '123456';

-- 改用 UNION
SELECT * FROM users WHERE email = 'a@x.com'
UNION
SELECT * FROM users WHERE phone = '123456';

4. 優化 LIKE 查詢

sql

-- 前綴匹配可以用索引
SELECT * FROM users WHERE name LIKE 'Alice%';  -- ✅

-- 非前綴需要特殊索引
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%Alice%';  -- ✅ 有索引了

5. EXISTS vs JOIN

sql

-- 只需要知道「是否存在」時，EXISTS 更快
-- 不好
SELECT DISTINCT c.* FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- 好
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

6. 批量 INSERT

sql

-- 不好：逐筆插入
INSERT INTO logs (message) VALUES ('a');
INSERT INTO logs (message) VALUES ('b');
INSERT INTO logs (message) VALUES ('c');

-- 好：批量插入
INSERT INTO logs (message) VALUES ('a'), ('b'), ('c');

-- 最好：COPY（最快）
COPY logs (message) FROM '/tmp/data.csv' WITH CSV;

7. 使用 UNLOGGED 表

不需要持久化的臨時資料可以用 UNLOGGED：

sql

CREATE UNLOGGED TABLE temp_results (
    id SERIAL,
    data TEXT
);
-- 寫入速度快，但資料庫崩潰時會丟失

六、監控工具

pg_stat_activity

查看當前連線和查詢：

sql

SELECT
    pid,
    usename,
    state,
    wait_event,
    query_start,
    LEFT(query, 50) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

pg_stat_user_tables

表的統計資訊：

sql

SELECT
    relname AS table,
    n_live_tup AS rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

pg_stat_user_indexes

索引使用統計：

sql

SELECT
    indexrelname AS index,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

七、 VACUUM 與膨脹

為什麼需要 VACUUM？

PostgreSQL 使用 MVCC，更新/刪除不會立即移除舊版本，而是標記為「死列」。VACUUM 回收這些空間。

自動 VACUUM

sql

-- 查看自動 VACUUM 設定
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';

-- 針對特定表調整
ALTER TABLE busy_table SET (autovacuum_vacuum_scale_factor = 0.01);

手動 VACUUM

sql

-- 普通 VACUUM（回收空間，不鎖表）
VACUUM users;

-- VACUUM ANALYZE（同時更新統計）
VACUUM ANALYZE users;

-- VACUUM FULL（重建表，會鎖表）
VACUUM FULL users;

監控膨脹

sql

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

八、調優清單

層面	檢查項目
查詢	EXPLAIN 分析、避免全表掃描、使用適當索引
索引	索引是否被使用、是否需要複合索引、刪除冗餘索引
統計	ANALYZE 是否定期執行、統計目標是否足夠
記憶體	shared_buffers、work_mem、effective_cache_size
連線	使用連線池、避免連線數過多
維護	VACUUM 是否正常執行、表膨脹程度
硬體	SSD、足夠 RAM、CPU 核心數

總結

統計資訊：確保 ANALYZE 定期執行
參數調整：根據硬體配置合適的記憶體參數
連線池：使用 PgBouncer 減少連線開銷
慢查詢：用 pg_stat_statements 識別問題查詢
維護：監控 VACUUM 和表膨脹

效能調優是持續的過程，需要根據實際負載不斷調整。

進階挑戰

使用 pg_stat_statements 找出消耗最多時間的 10 個查詢，並逐一優化
設定 PgBouncer 連線池，測試在高併發下的效能提升
調整 work_mem 和 effective_cache_size，觀察大型排序查詢的效能變化

延伸閱讀與資源

← 上一章：EXPLAIN 完全解讀 | 返回專題首頁 | 下一章：交易與 ACID 原則 →

查詢調優實戰 ​

一、 統計資訊與 ANALYZE ​

手動更新統計 ​

自動分析 ​

調整統計精度 ​

二、 關鍵參數調整 ​

shared_buffers ​

work_mem ​

maintenance_work_mem ​

effective_cache_size ​

random_page_cost ​

三、 連線池 (Connection Pooling) ​

問題：連線開銷高 ​

解法：PgBouncer ​

Node.js 連線池設定 ​

四、 慢查詢分析 ​

開啟慢查詢日誌 ​

pg_stat_statements 擴充 ​

識別問題查詢 ​

五、 常見優化技巧 ​

1. 避免 SELECT * ​

2. 使用 LIMIT 限制結果 ​

3. 避免 OR 條件 ​

4. 優化 LIKE 查詢 ​

5. EXISTS vs JOIN ​

6. 批量 INSERT ​

7. 使用 UNLOGGED 表 ​

六、 監控工具 ​

pg_stat_activity ​

pg_stat_user_tables ​

pg_stat_user_indexes ​

七、 VACUUM 與膨脹 ​

為什麼需要 VACUUM？ ​

自動 VACUUM ​

手動 VACUUM ​

監控膨脹 ​

八、 調優清單 ​

總結 ​

進階挑戰 ​

延伸閱讀與資源 ​

查詢調優實戰

一、統計資訊與 ANALYZE

手動更新統計

自動分析

調整統計精度

二、關鍵參數調整

shared_buffers

work_mem

maintenance_work_mem

effective_cache_size

random_page_cost

三、連線池 (Connection Pooling)

問題：連線開銷高

解法：PgBouncer

Node.js 連線池設定

四、慢查詢分析

開啟慢查詢日誌

pg_stat_statements 擴充

識別問題查詢

五、常見優化技巧

1. 避免 SELECT *

2. 使用 LIMIT 限制結果

3. 避免 OR 條件

4. 優化 LIKE 查詢

5. EXISTS vs JOIN

6. 批量 INSERT

7. 使用 UNLOGGED 表

六、監控工具

pg_stat_activity

pg_stat_user_tables

pg_stat_user_indexes

七、 VACUUM 與膨脹

為什麼需要 VACUUM？

自動 VACUUM

手動 VACUUM

監控膨脹

八、調優清單

總結

進階挑戰

延伸閱讀與資源