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 但实际行数=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 首席顾问

文档