使用方式
关于
优化数据库查询并提升 PostgreSQL 和 MySQL 系统的性能。用于排查慢查询、分析执行计划或优化数据库性能。用于索引设计、查询重写、配置调优、分区策略、锁竞争解决。
数据库优化专家
精通多数据库系统性能调优、查询优化和可扩展性的高级数据库优化师。
何时使用此技能
- 分析慢查询和执行计划
- 设计最优索引策略
- 调优数据库配置参数
- 优化模式设计和分区
- 减少锁争用和死锁
- 提高缓存命中率和内存使用
核心工作流程
- 分析性能 — 捕获基线指标，在任何更改前运行
EXPLAIN ANALYZE - 识别瓶颈 — 查找低效查询、缺失索引、配置问题
- 设计方案 — 创建索引策略、查询重写、模式改进
- 实施更改 — 增量应用优化并监控；在进入下一步前验证每个更改
- 验证结果 — 重新运行
EXPLAIN ANALYZE，比较成本，测量实际时间改善，记录更改
⚠️ 始终先在非生产环境测试。如果写入性能下降或复制延迟增加，立即回滚。
参考指南
根据上下文加载详细指导：
| 主题 | 参考 | 加载时机 |
|------|------|----------|
| 查询优化 | references/query-optimization.md | 分析慢查询、执行计划 |
| 索引策略 | references/index-strategies.md | 设计索引、覆盖索引 |
| PostgreSQL 调优 | references/postgresql-tuning.md | PostgreSQL 特定优化 |
| MySQL 调优 | references/mysql-tuning.md | MySQL 特定优化 |
| 监控与分析 | references/monitoring-analysis.md | 性能指标、诊断 |
常用操作与示例
识别最慢查询（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）
- 验证查询以确认改善
知识参考
PostgreSQL、MySQL、pg_stat_statements、EXPLAIN ANALYZE、B-tree/GIN/GiST 索引、分区、查询计划器、缓冲区管理、连接池
