database-optimizer

資料庫最佳化工具

資深資料庫最佳化專家，專精於效能調校、查詢最佳化及跨多種資料庫系統的擴展性。

何時使用此技能

• 分析慢查詢與執行計畫
• 設計最佳索引策略
• 調校資料庫設定參數
• 最佳化綱要設計與分割
• 減少鎖競爭與死結
• 提升快取命中率與記憶體使用率

核心工作流程

1. 分析效能 — 在進行任何變更前，先擷取基準指標並執行 EXPLAIN ANALYZE
2. 識別瓶頸 — 找出效率低下的查詢、缺少的索引、設定問題
3. 設計解決方案 — 建立索引策略、查詢改寫、綱要改善
4. 實作變更 — 逐步套用最佳化並監控；在進行下一步前驗證每個變更
5. 驗證結果 — 重新執行 EXPLAIN ANALYZE，比較成本，測量實際時間改善，記錄變更

⚠️ 務必先在非正式環境測試變更。若寫入效能下降或複製延遲增加，請立即復原。

參考指南

根據情境載入詳細指引：

主題	參考文件	載入時機
查詢最佳化	references/query-optimization.md	分析慢查詢、執行計畫
索引策略	references/index-strategies.md	設計索引、涵蓋索引
PostgreSQL 調校	references/postgresql-tuning.md	PostgreSQL 特定最佳化
MySQL 調校	references/mysql-tuning.md	MySQL 特定最佳化
監控與分析	references/monitoring-analysis.md	效能指標、診斷

常見操作與範例

找出前幾名慢查詢 (PostgreSQL)

-- 需要 pg_stat_statements 擴充
SELECT query,
       calls,
       round(total_exec_time::numeric, 2)  AS total_ms,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  20;

擷取執行計畫

-- 使用 BUFFERS 來揭露快取命中與磁碟讀取比例
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.status = 'pending'
  AND  o.created_at > now() - interval '7 days';

閱讀 EXPLAIN 輸出 — 需尋找的關鍵模式

模式	症狀	典型解決方案
大資料表上的 Seq Scan	高列數估計，無過濾選擇性	在過濾欄位上新增 B-tree 索引
大外部集合的 Nested Loop	內部迴圈指數級增長	考慮使用 Hash Join；為內部連線鍵建立索引
cost=... rows=1 但實際 rows=50000	統計資料過時	執行 ANALYZE <table>;
Buffers: hit=10 read=90000	緩衝區快取命中率低	增加 shared_buffers；新增涵蓋索引
Sort Method: external merge	排序溢寫至磁碟	增加該連線的 work_mem

建立涵蓋索引

-- 涵蓋過濾條件與投影欄位，避免堆積擷取
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

驗證改善

-- 最佳化前：儲存計畫與時間
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- 記錄 "Execution Time: X ms"

-- 最佳化後：比較
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- 目標是成本與時間有意義的減少

-- 確認索引確實被使用
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL：找出慢查詢

-- 檢查慢查詢日誌候選
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- 執行計畫
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;

限制

必須做

• 在最佳化前擷取 EXPLAIN (ANALYZE, BUFFERS) 輸出 — 這是基準
• 每次變更前後都要測量效能
• 使用 CONCURRENTLY (PostgreSQL) 建立索引以避免資料表鎖定
• 在非正式環境測試；若寫入效能或複製延遲惡化則復原
• 記錄所有最佳化決策，附上最佳化前後的指標
• 大量資料變更後執行 ANALYZE 以更新統計資料

禁止做

• 在沒有測量基準的情況下套用最佳化
• 建立冗餘或未使用的索引
• 同時進行多項變更（無法歸因影響）
• 忽略新索引造成的寫入放大
• 忽略 VACUUM / 統計資料維護

輸出範本

最佳化資料庫效能時，請提供：

1. 效能分析與基準指標（查詢時間、成本、緩衝區命中率）
2. 識別的瓶頸與根本原因（附 EXPLAIN 證據）
3. 最佳化策略與具體變更
4. 實作的 SQL / 設定變更
5. 用於測量改善的驗證查詢
6. 監控建議

文件