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）

-- Requires pg_stat_statements extension
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;

捕获执行计划

-- Use BUFFERS to expose cache hit vs. disk read ratio
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

创建覆盖索引

-- Covers the filter AND the projected columns, eliminating a heap fetch
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

验证改进

-- Before optimization: save plan & timing
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- note "Execution Time: X ms"

-- After optimization: compare
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost & time

-- Confirm index is actually used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL：查找慢查询

-- Inspect slow query log candidates
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- Execution plan
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 的首席顾问。

文档