数据库慢查询与性能优化：用 EXPLAIN ANALYZE…

当 PostgreSQL 或 MySQL 出现慢查询、执行计划异常、索引设计不合理或锁竞争时，按基线测量—定位瓶颈—增量改动—验证对比的流程优化性能。

适用场景

解决思路

1. 先采集基线指标：在任何改动前运行 `EXPLAIN ANALYZE`（PostgreSQL 用 `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)`）并记录执行时间与 cost。
2. 用 `pg_stat_statements` 找出最慢查询：按 `mean_exec_time DESC` 排序取前 20 条，查看 calls、total_exec_time、mean_exec_time、stddev_exec_time、rows。
3. 阅读 EXPLAIN 输出定位典型问题：大表 `Seq Scan` 加 B-tree 索引；大外表 `Nested Loop` 考虑 Hash Join 并为内表连接键建索引；估算 rows=1 但实际 rows=50000 说明统计过期，执行 `ANALYZE <table>;`；`Buffers: hit=10 read=90000` 说明缓存命中率低，增大 `shared_buffers` 或加覆盖索引；`Sort Method: external merge` 说明排序落盘，为该会话增大 `work_mem`。
4. 设计覆盖索引以消除回表：例如 `CREATE INDEX CONCURRENTLY idx_orders_status_created_covering ON orders (status, created_at) INCLUDE (customer_id, total_amount);`，PostgreSQL 建索引用 `CONCURRENTLY` 避免锁表。
5. MySQL 侧用 `performance_schema.events_statements_summary_by_digest` 按 `SUM_TIMER_WAIT DESC` 找慢查询，并用 `EXPLAIN FORMAT=JSON` 查看执行计划。
6. 增量实施改动并逐个监控验证，每次只改一项，避免多个改动同时上线导致无法归因。
7. 改动后重新运行 `EXPLAIN (ANALYZE, BUFFERS)` 对比 cost 与执行时间，确认有显著下降。
8. 用 `SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'orders';` 确认新索引确实被使用。
9. 批量数据变更后运行 `ANALYZE` 刷新统计信息，并定期执行 `VACUUM` 维护。
10. 所有优化决策都要记录改动前后的指标对比，并给出验证查询与监控建议。
11. 先在非生产环境测试；若写入性能下降或复制延迟增加，立即回滚。

适用边界 · 注意事项

• 没有测量基线时不要盲目应用优化。
• 不要创建冗余或从未被使用的索引，也不要忽视新索引带来的写放大。
• 不要同时做多项改动，否则无法判断哪项生效。