database-optimizer

数据库优化器

用途

为 PostgreSQL、MySQL、MongoDB、Redis 等主流数据库系统提供专业的性能调优与优化，专注于查询优化、索引设计、执行计划分析和系统配置。通过系统化的优化方法，实现亚秒级查询性能和资源利用率最优化。

适用场景

• 查询执行时间超过性能目标（OLTP 超过 100ms，分析型查询超过 5s）
• 数据库 CPU／内存／I/O 利用率持续高于 70%
• 应用遇到数据库连接耗尽或超时问题
• 慢查询日志显示存在问题模式或缺少索引
• 数据库难以应对预期负载或流量激增
• 复制延迟超过可接受阈值（关键系统超过 1s）
• 需要针对特定工作负载（OLTP 与 OLAP）优化数据库配置
• 规划数据库容量或水平扩展策略

快速开始

在以下情况调用此技能：

• 需要优化慢查询（EXPLAIN ANALYZE 显示存在问题）
• 需要设计或审查索引策略
• 需要调优数据库配置
• 需要进行容量规划或制定扩展决策

以下情况不要调用：

• 没有性能问题的简单 CRUD 操作
• 不以优化为重点的架构设计（请使用 database-administrator）
• 仅涉及应用层缓存（请使用 backend-developer）

核心能力

查询优化

• 分析执行计划并识别瓶颈
• 重写查询以实现最佳性能
• 优化联接、子查询和聚合
• 实施查询结果缓存策略

索引设计

• 设计合适的索引类型（B-tree、GIN、BRIN、hash）
• 为多列查询创建复合索引
• 针对特定查询模式实施部分索引
• 管理索引维护并避免索引膨胀

数据库配置

• 针对特定工作负载调优数据库参数
• 优化内存分配（缓冲池、缓存大小）
• 配置连接池和并发设置
• 为大型表实施分区策略

性能监控

• 设置查询性能监控和告警
• 分析慢查询日志并识别模式
• 实施数据库指标采集（EXPLAIN ANALYZE）
• 建立性能基线并进行容量规划

决策框架

优化优先级矩阵

症状	首要操作	工具
查询 >100ms	EXPLAIN ANALYZE	执行计划审查
CPU 占用率高	pg_stat_statements	找出最耗资源的查询
I/O 占用率高	索引审查	检测缺失索引
连接耗尽	连接池调优	PgBouncer／连接数限制
复制延迟	写入优化	批量操作

索引决策树

Query Performance Issue
│
├─ WHERE clause filtering?
│  └─ Create B-tree index on filter columns
│
├─ JOIN operations slow?
│  └─ Index foreign key columns
│
├─ ORDER BY/GROUP BY expensive?
│  └─ Include sort columns in index
│
├─ Covering index possible?
│  └─ Add INCLUDE columns to avoid heap fetches
│
└─ Selective queries (status='active')?
   └─ Use partial index with WHERE clause

核心工作流：慢查询优化

场景：生产环境中的查询耗时 3.2s，需要降至 <100ms

步骤 1：使用 EXPLAIN ANALYZE 记录基线

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT u.id, u.email, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
  AND u.status = 'active'
GROUP BY u.id, u.email
ORDER BY total_spent DESC
LIMIT 100;

步骤 2：从执行计划中识别问题

• 使用顺序扫描而非索引扫描
• 共享读取量高（缓存未命中）
• 过滤／联接列缺少索引

步骤 3：创建有针对性的索引

-- Covering index for users with partial index
CREATE INDEX CONCURRENTLY idx_users_status_created_active 
  ON users (status, created_at) 
  INCLUDE (id, email)
  WHERE status = 'active';

-- Covering index for orders JOIN
CREATE INDEX CONCURRENTLY idx_orders_user_id_total 
  ON orders (user_id) 
  INCLUDE (id, total);

-- Update statistics
ANALYZE users;
ANALYZE orders;

步骤 4：验证优化效果

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
-- Same query - should now show:
-- - Index Only Scan instead of Seq Scan
-- - Heap Fetches: 0
-- - Execution Time: <100ms

预期结果：

• 执行时间缩短 95% 以上（3205ms -> 87ms）
• 消除缓冲区读取（全部命中缓存）
• 将顺序扫描替换为索引扫描
• 查询计划稳定且可预测

快速参考：性能目标

指标	OLTP 目标	分析型查询目标
P50 延迟	<50ms	<2s
P95 延迟	<100ms	<5s
P99 延迟	<200ms	<10s
缓存命中率	>95%	>90%
索引使用率	>95%	>80%

快速参考：配置指南

参数	公式	示例（32GB RAM）
shared_buffers	RAM 的 25%	8GB
effective_cache_size	RAM 的 75%	24GB
work_mem	RAM / max_connections / 4	40MB
maintenance_work_mem	RAM 的 10%	2GB
random_page_cost	1.1（SSD）/ 4.0（HDD）	1.1

危险信号 — 何时升级处理

观察情况	操作
查询复杂度激增	升级给架构师，重新设计架构
复制延迟 >10s	升级给 DBA，检查基础设施
连接池耗尽	检查应用程序的连接处理方式
磁盘 I/O 饱和	考虑使用只读副本或缓存层

其他资源

• 详细技术参考：参见 REFERENCE.md

  • 数据库配置调优工作流
  • 时序数据的分区策略
  • 高级监控查询

• 代码示例与模式：参见 EXAMPLES.md

  • 反模式（过度索引、过早反规范化）
  • 优化项目质量检查清单
  • 索引监控与维护查询