名称: 数据库优化器描述: 当用户需要数据库查询优化、性能调优、索引策略、执行计划分析或跨PostgreSQL、MySQL、MongoDB、Redis等数据库系统的可扩展性解决方案时使用。

数据库优化器

目的

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

使用场景

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

快速开始

在以下情况调用此技能：

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

不要在以下情况调用：

无性能问题的简单CRUD操作
无优化重点的架构设计（使用数据库管理员技能）
仅应用层缓存（使用后端开发技能）

核心能力

查询优化

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

索引设计

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

数据库配置

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

性能监控

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

决策框架

优化优先级矩阵

症状	首要行动	工具
查询 >100ms	EXPLAIN ANALYZE	执行计划审查
高CPU	pg_stat_statements	查找顶级查询
高I/O	索引审查	缺失索引检测
连接耗尽	连接池调优	PgBouncer/连接限制
复制延迟	写入优化	批量操作

索引决策树

查询性能问题
│
├─ WHERE子句过滤？
│  └─ 在过滤列上创建B树索引
│
├─ JOIN操作慢？
│  └─ 索引外键列
│
├─ ORDER BY/GROUP BY代价高？
│  └─ 在索引中包含排序列
│
├─ 覆盖索引可行？
│  └─ 添加INCLUDE列避免堆获取
│
└─ 选择性查询（status='active'）？
   └─ 使用带WHERE子句的部分索引

核心工作流：慢查询优化

场景：生产查询耗时3.2秒，需要优化至<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：创建策略性索引

-- 用户表的覆盖索引（带部分索引）
CREATE INDEX CONCURRENTLY idx_users_status_created_active 
  ON users (status, created_at) 
  INCLUDE (id, email)
  WHERE status = 'active';

-- 订单表JOIN的覆盖索引
CREATE INDEX CONCURRENTLY idx_orders_user_id_total 
  ON orders (user_id) 
  INCLUDE (id, total);

-- 更新统计信息
ANALYZE users;
ANALYZE orders;

步骤4：验证优化效果

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
-- 相同查询 - 现在应显示：
-- - 索引仅扫描替代顺序扫描
-- - 堆获取：0
-- - 执行时间：<100ms

预期结果：

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

快速参考：性能目标

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

快速参考：配置指南

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

危险信号 - 何时升级处理

观察现象	行动
查询复杂度爆炸	升级至架构师进行架构重设计
复制延迟 >10s	升级至DBA进行基础设施审查
连接池耗尽	审查应用程序连接处理
磁盘I/O饱和	考虑读副本或缓存层

附加资源

详细技术参考：参见REFERENCE.md
- 数据库配置调优工作流
- 时间序列数据的分区策略
- 高级监控查询
代码示例与模式：参见EXAMPLES.md
- 反模式（过度索引、过早反规范化）
- 优化项目质量检查清单
- 索引监控和维护查询

名称: 数据库优化器 描述: 当用户需要数据库查询优化、性能调优、索引策略、执行计划分析或跨PostgreSQL、MySQL、MongoDB、Redis等数据库系统的可扩展性解决方案时使用。