database-optimizer by 404kidwiz/claude-supercode-skills
npx skills add https://github.com/404kidwiz/claude-supercode-skills --skill database-optimizer提供跨主流数据库系统(PostgreSQL、MySQL、MongoDB、Redis)的专家级数据库性能调优和优化,专注于查询优化、索引设计、执行计划分析和系统配置。通过系统化的优化方法,实现亚秒级查询性能和最优资源利用率。
在以下情况下调用此技能:
在以下情况下请勿调用:
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
| 症状 | 首要操作 | 工具 |
|---|---|---|
| 查询 >100ms | EXPLAIN ANALYZE | 执行计划审查 |
| 高 CPU | pg_stat_statements | 查找顶部查询 |
| 高 I/O | 索引审查 | 缺失索引检测 |
| 连接耗尽 | 连接池调优 | PgBouncer/连接限制 |
| 复制延迟 | 写入优化 | 批量操作 |
Query Performance Issue
│
├─ WHERE 子句过滤?
│ └─ 在过滤列上创建 B-tree 索引
│
├─ 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:创建策略性索引
-- 为 users 表创建带部分索引的覆盖索引
CREATE INDEX CONCURRENTLY idx_users_status_created_active
ON users (status, created_at)
INCLUDE (id, email)
WHERE status = 'active';
-- 为 orders 表 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)
-- 相同查询 - 现在应显示:
-- - Index Only Scan 替代 Seq Scan
-- - Heap Fetches: 0
-- - Execution Time: <100ms
预期结果:
| 指标 | OLTP 目标 | 分析型查询目标 |
|---|---|---|
| P50 延迟 | <50ms | <2s |
| P95 延迟 | <100ms | <5s |
| P99 延迟 | <200ms | <10s |
| 缓存命中率 | >95% | >90% |
| 索引使用率 | >95% | >80% |
| 参数 | 公式 | 示例(32GB RAM) |
|---|---|---|
| shared_buffers | 25% 内存 | 8GB |
| effective_cache_size | 75% 内存 | 24GB |
| work_mem | RAM / max_connections / 4 | 40MB |
| maintenance_work_mem | 10% 内存 | 2GB |
| random_page_cost | 1.1 (SSD) / 4.0 (HDD) | 1.1 |
| 观察现象 | 操作 |
|---|---|
| 查询复杂度爆炸 | 升级至架构师进行模式重新设计 |
| 复制延迟 >10s | 升级至 DBA 进行基础设施审查 |
| 连接池耗尽 | 审查应用程序连接处理 |
| 磁盘 I/O 饱和 | 考虑读取副本或缓存层 |
每周安装次数
60
代码仓库
GitHub 星标数
43
首次出现
2026年1月24日
安全审计
已安装于
opencode50
claude-code47
codex46
gemini-cli46
cursor42
github-copilot39
Provides expert database performance tuning and optimization across major database systems (PostgreSQL, MySQL, MongoDB, Redis) specializing in query optimization, index design, execution plan analysis, and system configuration. Achieves sub-second query performance and optimal resource utilization through systematic optimization approaches.
Invoke this skill when:
Do NOT invoke when:
| Symptom | First Action | Tool |
|---|---|---|
| Query >100ms | EXPLAIN ANALYZE | Execution plan review |
| High CPU | pg_stat_statements | Find top queries |
| High I/O | Index review | Missing index detection |
| Connection exhaustion | Pool tuning | PgBouncer/connection limits |
| Replication lag | Write optimization | Batch operations |
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
Scenario : Production query taking 3.2s, needs to be <100ms
Step 1: Capture baseline with 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;
Step 2: Identify issues from execution plan
Step 3: Create strategic indexes
-- 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;
Step 4: Verify optimization
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
-- Same query - should now show:
-- - Index Only Scan instead of Seq Scan
-- - Heap Fetches: 0
-- - Execution Time: <100ms
Expected outcome :
| Metric | OLTP Target | Analytics Target |
|---|---|---|
| P50 latency | <50ms | <2s |
| P95 latency | <100ms | <5s |
| P99 latency | <200ms | <10s |
| Cache hit ratio | >95% | >90% |
| Index usage | >95% | >80% |
| Parameter | Formula | Example (32GB RAM) |
|---|---|---|
| shared_buffers | 25% of RAM | 8GB |
| effective_cache_size | 75% of RAM | 24GB |
| work_mem | RAM / max_connections / 4 | 40MB |
| maintenance_work_mem | 10% of RAM | 2GB |
| random_page_cost | 1.1 (SSD) / 4.0 (HDD) | 1.1 |
| Observation | Action |
|---|---|
| Query complexity explosion | Escalate to architect for schema redesign |
| Replication lag >10s | Escalate to DBA for infrastructure review |
| Connection pool exhaustion | Review application connection handling |
| Disk I/O saturation | Consider read replicas or caching layer |
Detailed Technical Reference : See REFERENCE.md
Code Examples & Patterns: See EXAMPLES.md
Weekly Installs
60
Repository
GitHub Stars
43
First Seen
Jan 24, 2026
Security Audits
Gen Agent Trust HubFailSocketPassSnykPass
Installed on
opencode50
claude-code47
codex46
gemini-cli46
cursor42
github-copilot39
Azure 升级评估与自动化工具 - 轻松迁移 Functions 计划、托管层级和 SKU
111,700 周安装
Intercom自动化指南:通过Rube MCP与Composio实现客户支持对话管理
69 周安装
二进制初步分析指南:使用ReVa工具快速识别恶意软件与逆向工程
69 周安装
PrivateInvestigator 道德人员查找工具 | 公开数据调查、反向搜索与背景研究
69 周安装
TorchTitan:PyTorch原生分布式大语言模型预训练平台,支持4D并行与H100 GPU加速
69 周安装
screenshot 截图技能:跨平台桌面截图工具,支持macOS/Linux权限管理与多模式捕获
69 周安装
tmux进程管理最佳实践:交互式Shell初始化、会话命名与生命周期管理
69 周安装