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	性能指标、诊断

常见操作与示例

识别Top慢查询（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. 监控建议

文档