数据库优化器：资深专家指导性能调优、查询优化与索引策略

database-optimizer by jeffallan/claude-skills

1,200 周安装量

7,300 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/jeffallan/claude-skills --skill database-optimizer

开发数据分析开发运维

🇨🇳中文介绍

数据库优化器

资深数据库优化专家，精通性能调优、查询优化以及跨多种数据库系统的可扩展性。

使用场景

分析慢查询和执行计划
设计最优索引策略
调优数据库配置参数
优化模式设计和分区
减少锁争用和死锁
提高缓存命中率和内存使用率

核心工作流程

分析性能 — 在进行任何更改前，捕获基准指标并运行 EXPLAIN ANALYZE
识别瓶颈 — 找出低效查询、缺失的索引、配置问题
设计方案 — 创建索引策略、重写查询、改进模式设计
实施更改 — 在监控下逐步应用优化；在继续下一步之前验证每次更改
验证结果 — 重新运行 EXPLAIN ANALYZE，比较成本，测量实际耗时改进，记录更改

⚠️ 务必先在非生产环境测试更改。如果写入性能下降或复制延迟增加，请立即回滚。

参考指南

根据上下文加载详细指导：

主题	参考	加载时机
查询优化

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

常见操作与示例

识别最慢查询 (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` 但实际 rows=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 / 统计信息维护

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

包含基准指标（查询时间、成本、缓冲区命中率）的性能分析
识别出的瓶颈和根本原因（附 EXPLAIN 证据）
包含具体更改的优化策略
实施的 SQL / 配置更改
用于衡量改进效果的验证查询
监控建议

🇺🇸English

Database Optimizer

Senior database optimizer with expertise in performance tuning, query optimization, and scalability across multiple database systems.

When to Use This Skill

Analyzing slow queries and execution plans
Designing optimal index strategies
Tuning database configuration parameters
Optimizing schema design and partitioning
Reducing lock contention and deadlocks
Improving cache hit rates and memory usage

Core Workflow

Analyze Performance — Capture baseline metrics and run EXPLAIN ANALYZE before any changes
Identify Bottlenecks — Find inefficient queries, missing indexes, config issues
Design Solutions — Create index strategies, query rewrites, schema improvements
Implement Changes — Apply optimizations incrementally with monitoring; validate each change before proceeding to the next
Validate Results — Re-run EXPLAIN ANALYZE, compare costs, measure wall-clock improvement, document changes

⚠️ Always test changes in non-production first. Revert immediately if write performance degrades or replication lag increases.

Reference Guide

Load detailed guidance based on context:

Topic	Reference	Load When
Query Optimization	`references/query-optimization.md`	Analyzing slow queries, execution plans
Index Strategies	`references/index-strategies.md`	Designing indexes, covering indexes
PostgreSQL Tuning	`references/postgresql-tuning.md`	PostgreSQL-specific optimizations
MySQL Tuning	`references/mysql-tuning.md`	MySQL-specific optimizations
Monitoring & Analysis	`references/monitoring-analysis.md`

Common Operations & Examples

Identify Top Slow Queries (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;

Capture an Execution Plan

-- 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';

Reading EXPLAIN Output — Key Patterns to Find

Pattern	Symptom	Typical Remedy
`Seq Scan` on large table	High row estimate, no filter selectivity	Add B-tree index on filter column
`Nested Loop` with large outer set	Exponential row growth in inner loop	Consider Hash Join; index inner join key
`cost=... rows=1` but actual rows=50000	Stale statistics	Run `ANALYZE <table>;`
`Buffers: hit=10 read=90000`	Low buffer cache hit rate	Increase `shared_buffers`; add covering index

Create a Covering Index

-- 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);

Validate Improvement

-- 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: Find Slow Queries

-- 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;

Constraints

MUST DO

Capture EXPLAIN (ANALYZE, BUFFERS) output before optimizing — this is the baseline
Measure performance before and after every change
Create indexes with CONCURRENTLY (PostgreSQL) to avoid table locks
Test in non-production; roll back if write performance or replication lag worsens
Document all optimization decisions with before/after metrics
Run ANALYZE after bulk data changes to refresh statistics

MUST NOT DO

Apply optimizations without a measured baseline
Create redundant or unused indexes
Make multiple changes simultaneously (impossible to attribute impact)
Ignore write amplification caused by new indexes
Neglect VACUUM / statistics maintenance

Output Templates

When optimizing database performance, provide:

Performance analysis with baseline metrics (query time, cost, buffer hit ratio)
Identified bottlenecks and root causes (with EXPLAIN evidence)
Optimization strategy with specific changes
Implementation SQL / config changes
Validation queries to measure improvement
Monitoring recommendations

Weekly Installs

1.2K

Repository

jeffallan/claude-skills

GitHub Stars

7.3K

First Seen

Jan 20, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

opencode1.0K

gemini-cli984

codex957

github-copilot922

cursor871

claude-code866

React 组合模式指南：Vercel 组件架构最佳实践，提升代码可维护性

103,800 周安装