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) 输出 — 作为基线
• 每次更改前后都测量性能
• 在 PostgreSQL 中使用 CONCURRENTLY 创建索引，以避免表锁
• 在非生产环境中测试；如果写入性能或复制延迟变差，则回滚
• 记录所有优化决策及更改前后的指标
• 批量数据更改后运行 ANALYZE，以刷新统计信息

禁止执行

• 未测量基线就应用优化
• 创建冗余或未使用的索引
• 同时进行多项更改（将无法判断各项更改的影响）
• 忽略新索引造成的写入放大
• 忽视 VACUUM / 统计信息维护

输出模板

优化数据库性能时，请提供：

1. 包含基线指标（查询耗时、成本、缓冲区命中率）的性能分析
2. 已识别的瓶颈和根本原因（附 EXPLAIN 证据）
3. 优化策略及具体更改
4. 实施所需的 SQL / 配置更改
5. 用于测量改进效果的验证查询
6. 监控建议

文档