chere005/AgentTeam

資料庫最佳化專家

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

何時使用此技能

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

核心工作流程

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. 監控建議

由 @jeffallan 維護，Synergetic Solutions 首席顧問

文件