database-optimizer

用途

该技能定位为资深数据库优化角色，专注于性能调优、查询优化与可扩展性，覆盖 PostgreSQL 和 MySQL 等数据库系统。它适用于以下任务：分析慢查询与执行计划、设计最优索引策略、调优数据库配置参数、优化模式设计与分区、降低锁竞争与死锁、提升缓存命中率与内存使用效率。

主要能力

性能分析：采集基线指标，并在任何变更前运行 EXPLAIN ANALYZE。
瓶颈定位：识别低效查询、缺失索引与配置问题。
方案设计：制定索引策略、查询重写方案与模式改进。
增量实施：在监控下逐步应用优化，每项变更验证通过后再进行下一项。
结果验证：重新运行 EXPLAIN ANALYZE，对比成本，测量墙钟时间改进，并记录变更。

其参考指南按上下文加载详细内容，包括查询优化、索引策略、PostgreSQL 调优、MySQL 调优、监控与分析等主题文档（如 references/query-optimization.md、references/index-strategies.md、references/postgresql-tuning.md、references/mysql-tuning.md、references/monitoring-analysis.md）。

使用方式与常见操作

识别最慢查询（PostgreSQL，需 pg_stat_statements 扩展）：

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;

捕获执行计划（使用 BUFFERS 暴露缓存命中与磁盘读取比例）：

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`

创建覆盖索引（同时覆盖过滤与投影列，避免堆回表）：

CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

验证改进：

-- 优化前：保存计划与时间
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- 记录 "Execution Time: X ms"

-- 优化后：对比
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- 目标为成本与时间显著下降

-- 确认索引确实被使用
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL：查找慢查询与执行计划：

-- 检查慢查询日志候选
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- 执行计划
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）监控建议。

引用文档

以下为作者声明的引用来源，保留指定版本、署名和许可。

作者的来源声明

jeffallan/claude-skills/skills/database-optimizer/SKILL.md — 署名：Jeffallan；许可：MIT；版本：3bf9a24b76a7c122f1fc05e83929fbc84e1c207a。