数据库与查询层可观测性：慢查询、连接池与执行计划

第 1 层 应用侧：连接池状态、SQL 调用耗时、错误率
第 2 层 协议侧：网络往返、认证、会话建立
第 3 层 数据库侧：语句执行时间、锁等待、IO、缓存命中
第 4 层 存储侧：磁盘 IOPS、延迟、日志写入（WAL/binlog）

排查方向：从外向内逐层缩小，避免一上来就盯数据库内核

应用侧连接池往往是最先出问题的地方：
  - 池满 → 请求排队 → 超时，但数据库 CPU 很低
  - 慢查询占用连接 → 池被拖垮 → 整个服务雪崩
结论：先看池，再看库，能解决大部分"数据库慢"的误判

原则：每一层都要有"耗时"和"错误"两类指标，缺一不可

PostgreSQL（postgresql.conf）：
  log_min_duration_statement = 200ms   # 记录超过 200ms 的语句
  log_lock_waits = on
  log_temp_files = 0                   # 记录所有临时文件
  log_checkpoints = on

MySQL（my.cnf）：
  slow_query_log = 1
  long_query_time = 0.2
  log_queries_not_using_indexes = 1
  slow_query_log_file = /var/log/mysql/slow.log

原始语句：
  SELECT * FROM orders WHERE user_id = 88123 AND status = 'paid'
  SELECT * FROM orders WHERE user_id = 99001 AND status = 'new'
归一化后：
  SELECT * FROM orders WHERE user_id = ? AND status = ?

目的：把"同一类语句"聚合成一个指纹（fingerprint），才能统计
  调用次数、总耗时、平均耗时、P99
否则每条参数不同都被当成"不同语句"，统计失去意义

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 找出"总耗时最高"的语句（最能优化收益）
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 80) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

关键用法：
  - 按 total_exec_time 排序 → 找"优化收益最大"的语句
  - 按 mean_exec_time 排序 → 找"单次最慢"的语句
  - 关注 rows 与 calls 的比例 → 发现"扫了很多行只返回几行"
  - 定期 pg_stat_statements_reset() 做窗口对比

-- performance_schema 语句摘要
SELECT DIGEST_TEXT,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_s,
       ROUND(AVG_TIMER_WAIT/1e9, 2)  AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

1. 池大小（max / min / current）
2. 活跃连接数（in use）
3. 空闲连接数（idle）
4. 等待获取连接的请求数 + 等待时长  ← 最关键，最易被忽略

hikaricp_connections_active        当前活跃连接
hikaricp_connections_idle          空闲连接
hikaricp_connections_pending       等待获取连接的线程数
hikaricp_connections_acquire_seconds  获取连接耗时直方图
hikaricp_connections_timeout_total 获取连接超时次数
hikaricp_connections_usage_seconds 连接持有时长

告警建议：
  pending > 0 持续 1 分钟        → 池容量不足
  acquire P99 > 100ms            → 池争抢严重
  timeout_total 增长             → 已开始影响业务

经典公式（PostgreSQL 官方 Wiki）：
  连接数 = (CPU 核心数 × 2) + 有效磁盘数

示例：8 核 + 1 块 SSD → 8 × 2 + 1 = 17 个连接

误区：池越大越好？错。
  连接过多 → 数据库上下文切换、锁竞争加剧 → 反而更慢
  正确做法：小池 + 快速释放，让数据库把 CPU 用在查询上

总连接预算 = 池大小 × 应用实例数
必须 < 数据库 max_connections（留 20% 余量给运维连接）

示例：max_connections = 200，应用 10 个实例
  → 每实例池上限 ≤ 16，留 40 个给后台任务与 DBA
超配的后果：连接被拒、新实例起不来、故障恢复更慢

-- PostgreSQL：看真实执行计划（会真正执行）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, total FROM orders WHERE user_id = 12345 AND created_at > now() - interval '7 days';

-- MySQL：看执行计划
EXPLAIN ANALYZE SELECT id, total FROM orders WHERE user_id = 12345;

重点看什么：
  Seq Scan / full table scan   → 缺索引或索引未命中
  rows 估算 vs 实际行数差异大  → 统计信息过期
  Buffers: shared read 高      → 缓存未命中，物理读多
  Sort Method: external merge  → 排序落盘，work_mem 不足

-- 找出未被使用的索引（占空间且拖慢写入）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

索引健康度三问：
  1. 有没有该建没建的索引？（慢查询 + Seq Scan）
  2. 有没有建了不用的索引？（idx_scan = 0，浪费写入成本）
  3. 有没有重复/冗余索引？（同前缀索引可合并）

现象：同一条 SQL 昨天 10ms，今天 2s
根因：统计信息过期 → 优化器估算错误 → 选了坏计划

对策：
  - 定期 ANALYZE（PG 自动 vacuum 需确认开启）
  - 关注 pg_stat_user_tables.last_autoanalyze
  - 关键表设置更激进的 autovacuum 参数
  - 必要时用 pg_hint_plan 锁定计划（临时手段）

-- 当前正在等待的会话
SELECT pid, wait_event_type, wait_event, state, query_start, left(query, 60)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND state <> 'idle'
ORDER BY query_start;

常见等待类型解读：
  Lock / relation      → 表锁冲突，找阻塞者 pg_blocking_pids()
  IO / DataFileRead    → 物理读等待，缓存不足或磁盘慢
  LWLock / WALWrite    → WAL 写入瓶颈
  Client / ClientRead  → 应用取数据太慢（网络或应用问题）
  CPU（无等待）        → 纯计算，考虑加索引或改 SQL

SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0) AS hit_ratio
FROM pg_stat_database;

经验阈值：
  OLTP 命中率 > 99% 正常；< 95% 说明 shared_buffers 不足或有大范围扫描
  注意：命中率是"全局平均"，要按表/语句看才准

-- 找出阻塞关系
SELECT blocked.pid   AS blocked_pid,
       blocker.pid   AS blocker_pid,
       left(blocked.query, 40) AS blocked_query,
       left(blocker.query, 40) AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

锁等待的典型来源：
  长事务未提交 → 持锁不放
  批量 DDL → ACCESS EXCLUSIVE 锁
  缺索引的外键 → 更新时全表加锁

用 OpenTelemetry 数据库客户端插桩（自动）：
  db.system = postgresql / mysql
  db.statement = 归一化后的语句
  db.operation = SELECT / UPDATE
  db.name = 库名
  生成 span 挂在当前 trace 下

效果：链路里能看到"这条请求在 DB 上花了多少时间"，
      并与 pg_stat_statements 的语句指纹对上

同一时间窗口，对比三处数据：
  应用侧 query_duration_seconds P99
  数据库侧 pg_stat_statements mean/total
  存储侧 IO 延迟

若应用侧远大于数据库侧 → 问题在连接池或网络
若数据库侧远大于存储侧 → 问题在 SQL 或锁
若三者都高 → 真实负载压力，考虑扩容或优化

请求慢
 ├─ 池等待高？ → 调大池 / 找长事务占用连接
 ├─ SQL 执行慢？
 │   ├─ 计划劣化？ → ANALYZE / 锁计划
 │   ├─ 锁等待？   → 找阻塞者，缩短事务
 │   └─ 物理读高？ → 加索引 / 调 shared_buffers
 └─ 网络往返多？ → 减少 N+1 查询，批量取数

□ 应用侧必接连接池指标，尤其是等待时长与超时次数
□ 用 OpenTelemetry 自动插桩 DB 客户端，生成 db.* span
□ 慢查询阈值按业务基线设定，并做语句归一化聚合
□ 用 pg_stat_statements / performance_schema 做语句级排行
□ 定期 ANALYZE，监控统计信息新鲜度与计划漂移
□ 每季度审计索引：补缺失、删冗余、合并重复
□ 监控长事务与锁等待，建立阻塞链排查脚本
□ 连接池大小按公式计算，总连接数留 20% 余量
□ 建立"应用-数据库-存储"三层对账的排障 SOP
□ 关键慢查询优化前后留档，验证收益

先看池、再看语句、后看锁与计划——
数据库可观测性的核心是把「慢」归因到具体对象。