JDBC 反模式与排查宝典

概述

本系列从 JDBC 规范内核出发，逐层深入到 Spring JdbcTemplate、预编译协议、批处理优化、连接池架构与源码级实现，并最终探索了 PG JDBC 驱动的独有特性。然而，知晓原理不等于能在生产环境正确运用。本文作为系列收官之作，将前面的所有核心技术点投射到真实的故障场景中，通过“反模式→排查→修复”的闭环，帮助读者将分散的知识点内化为系统化的排障直觉。

“连接池爆满”“OOM 宕机”“预编译失效”“批处理慢如蜗牛”——这些 JDBC 相关的线上故障，根因往往不是某个技术本身有问题，而是使用方式违背了它的设计本意。本文将 JDBC 生态中最常见的反模式归纳为六大领域，以 19 个以上的真实案例为载体，严格遵循“错误示例→现象描述→排查思路→根因分析→修正方案→最佳实践”的六步诊断法，并搭建带预期结果校验的故障模拟实验室，绘制可打印的标准化排查决策树，让你在遭遇类似问题时能够快速定位根因并实施修复。

核心要点

• 六大反模式领域：连接、SQL 执行、预编译、批处理、连接池、驱动配置，共 20 个深度案例。
• 六步诊断法：从错误代码到修复的标准化流程，每个步骤均提供可复现的诊断命令与监控截图。
• 故障模拟实验室：两个完整故障复现过程，每个关键步骤标注预期结果并配有监控输出。
• 诊断工具集：从数据库到应用层的全链路排查工具速查，包含超过 15 条现象映射。
• 标准化排查决策树：覆盖连接耗尽、查询变慢、OOM、批处理吞吐低、预编译失效五类典型故障的决策路径，每个分支节点都链接到具体案例。
• 面试高频故障排查专题：16 道纯故障场景题，涵盖两大设计题，与前 9 篇原理题互补。

文章组织架构图

flowchart TD
  1[连接反模式<br/>4案例]
  2[SQL执行反模式<br/>4案例]
  3[预编译反模式<br/>3案例]
  4[批处理反模式<br/>3案例]
  5[连接池反模式<br/>3案例]
  6[驱动配置反模式<br/>3案例]
  7[故障模拟实验室<br/>带预期结果校验]
  8[诊断工具集与工具-现象映射表]
  9[标准化排查决策树<br/>五条主干]
  10[面试高频故障排查专题<br/>16题含2设计题]

  1 --> 2 --> 3 --> 4 --> 5 --> 6 --> 7 --> 8 --> 9 --> 10

架构图说明：

• 总览说明：全文 10 个模块以前 6 个反模式领域的案例分析为主体，后接带预期结果校验的故障模拟实验室、诊断工具集速查和五分支标准化排查决策树，最后以面试故障排查专题收束。每个模块均可独立阅读，又可交叉引用。
• 逐模块说明：模块 1-6 覆盖 JDBC 全生命周期中的典型错误，边界清晰，每个案例均明确标注与前文原理的对应关系；模块 7 通过真实环境复现故障，提供完整的 Docker Compose、JMeter 测试计划和 Spring Boot 应用源码，每个关键步骤配预期结果；模块 8 构建三层工具链并提供超过 15 行的速查映射表；模块 9 绘制五大故障决策路径图，节点中直接嵌入排查命令和案例跳转；模块 10 收束全文，16 道故障排查题检验读者排障能力。
• 关键结论：JDBC 反模式的根因往往可追溯到前 9 篇的核心原理。掌握六步诊断法和五大故障决策路径，是将理论转化为排障能力的关键。每一个案例都是一个独立的排障剧本，通过反复演练可在真实故障中形成肌肉记忆。

---

1. 连接反模式

连接是所有数据库交互的起点。本领域聚焦物理连接的获取与释放行为，与第 6 篇《连接池技术内核》和第 7 篇《HikariCP 无锁并发》形成深度对照。下面的四个案例从泄漏、池容量、生命周期冲突到未池化四个维度展开，每个案例均提供详细的监控命令输出和 Arthas 追踪脚本。

案例 1.1：连接泄漏——pg_stat_activity 排查与六步修复

1.1.1 错误示例

许多开发者在代码中手动打开连接，却忘记在 finally 块中关闭，或者由于异常路径未处理导致连接未归还。

场景 1：直接使用 DriverManager，未关闭连接

// 文件：LeakService.java
public class LeakService {
    private static final String DB_URL = "jdbc:postgresql://localhost:5432/mydb";
    private static final String USER = "app";
    private static final String PASS = "pass";

    public List<User> fetchUsers() {
        List<User> users = new ArrayList<>();
        try {
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, name FROM users");
            while (rs.next()) {
                users.add(new User(rs.getInt("id"), rs.getString("name")));
            }
            // 没有显式关闭任何资源
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }
}

此代码看似正常，但在发生异常或返回中间时，Connection、Statement、ResultSet 均未关闭。如果方法被频繁调用，将导致连接泄漏。

场景 2：连接池环境下异常未关闭

// 使用 HikariCP 连接池，但业务异常导致连接无法归还
public void processOrder(int orderId) throws SQLException {
    Connection conn = dataSource.getConnection();
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM orders WHERE id = ?");
    ps.setInt(1, orderId);
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        String status = rs.getString("status");
        if ("CANCELLED".equals(status)) {
            throw new RuntimeException("Order was cancelled"); // 直接抛出异常，资源未关闭
        }
        // 正常处理...
    }
    // 假设正常路径会关闭，但异常路径完全绕过
    rs.close();
    ps.close();
    conn.close();
}

在连接池环境下，每次 getConnection() 从池中借用连接，close() 将连接归还池。若未执行 close()，该连接既不被应用使用也不会回到池中，形成“漂浮”连接，最终耗尽池中所有连接。

1.1.2 现象描述

数据库端：

• 执行 SELECT count(*), state FROM pg_stat_activity WHERE application_name = 'yourapp' GROUP BY state; 观察到大量 idle 状态连接，而且数量持续增长。
• 随着 idle 连接数接近连接池设定的 maximumPoolSize 或数据库 max_connections 上限，新的连接请求开始排队。
• 最终出现错误：FATAL: remaining connection slots are reserved for non-replication superuser connections 或 Too many connections。

应用端：

• 日志中批量出现 java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
• 应用吞吐量急剧下降，线程 Dump 显示大量线程 BLOCKED 在 HikariPool.getConnection() 方法上。
• 如果开启了 HikariCP 的泄漏检测（leakDetectionThreshold），日志中会出现 Connection leak detection triggered for connection ...，并打印出借用时的堆栈信息。

监控系统：

• HikariCP Metrics 中的 hikaricp_connections_active 和 hikaricp_connections_idle 指标会显示出诡异曲线：active 也许不高，但 idle 接近最大值，且 pending 持续增长。
• 数据库监控显示 idle 连接的内存占用（每个连接约 5-10MB）持续累加，整体内存使用上升。

1.1.3 排查思路

当遇到“获取连接超时”或连接池耗尽时，按以下顺序排查：

步骤 1：确认数据库连接现状

-- 查看按状态的连接分布
SELECT state, count(*) 
FROM pg_stat_activity 
WHERE application_name = 'yourapp' 
GROUP BY state;

-- 查看处于 idle 状态且持续时间异常长的连接（超过30分钟）
SELECT pid, usename, state, backend_start, state_change, query
FROM pg_stat_activity 
WHERE state = 'idle' 
  AND application_name = 'yourapp' 
  AND state_change < now() - interval '30 minutes';

若 idle 连接数量远大于预期并发量，且持续增长不回收，则存在连接泄漏。

步骤 2：启用连接池泄漏检测 临时修改配置（或通过动态配置中心）开启泄漏检测，并降低阈值以快速获取证据：

spring:
  datasource:
    hikari:
      leak-detection-threshold: 10000  # 10秒，触发泄漏告警

重启应用后，观察日志。理想的泄漏日志格式如下：

2025-05-13 10:35:12.321 WARN  [HikariPool-1 connection adder] com.zaxxer.hikari.pool.ProxyLeakTask : 
Connection leak detection triggered for conn: 1234567890 on thread http-nio-8080-exec-5, 
stack trace follows:
java.lang.Exception: Apparent connection leak detected
    at com.example.LeakingService.processOrder(LeakingService.java:42)
    ...

此日志精确指示了哪个线程在什么堆栈上获取了连接但迟迟未归还。

步骤 3：动态追踪 getConnection 与 close 调用比例 使用 Arthas 在不重启应用的情况下实时监控方法调用：

# 监控 HikariPool.getConnection 的调用，统计频率
watch com.zaxxer.hikari.pool.HikariPool getConnection '{params, returnObj}' -n 200 -b -s

# 监控 Connection.close() 的调用
watch java.sql.Connection close '{target}' -n 200 -b -s

# 使用 monitor 命令统计调用次数和成功率
monitor -c 5 com.zaxxer.hikari.pool.HikariPool getConnection
monitor -c 5 java.sql.Connection close

正常情况下，close 的调用次数应与 getConnection 接近（在连接池健康时，归还和借用会动态平衡）。如果发现 getConnection 调用次数远大于 close，则可确定存在未归还路径。

步骤 4：利用 JFR/堆转储分析连接对象 如果泄漏连接数量巨大，可以生成堆转储：

jmap -dump:live,format=b,file=heap.hprof <pid>

使用 Eclipse MAT 打开堆转储，搜索 com.zaxxer.hikari.pool.PoolEntry 或 com.zaxxer.hikari.pool.HikariProxyConnection 实例，查看其引用路径。如果存在大量 PoolEntry 且没有被 HikariPool 内部空闲队列引用，很可能就是泄漏在外的连接。结合 GC Root 路径可以定位到持有这些连接的线程和方法。

1.1.4 根因分析

连接的生命周期必须遵循 JDBC 规范中“获取-使用-释放”的铁三角。在池化环境下，Connection.close() 的行为已经被代理拦截，它的作用并非关闭物理连接，而是将连接标记为空闲并归还到连接池中。任何遗漏的 close() 都意味着连接从池中“漂走”，永不复返。

在场景 1 的 DriverManager 直连中，物理连接未关闭，数据库后端进程持续存在，直到被数据库侧超时参数回收（若有设置）或应用进程重启。在场景 2 的池化环境中，泄漏的连接因为仍然持有对物理连接的引用，数据库侧连接也会保持 idle 状态，而连接池内部则记录该连接为“出去未归”。久而久之，池中可用连接数耗尽，后续请求都只能排队等超时。

根本原因是异常路径下的资源释放缺失。无论是检查型异常还是运行时异常，都必须确保 close() 在 finally 块中执行。如果不使用 try-with-resources，开发者需要在 finally 中按顺序关闭 ResultSet、Statement、Connection，并处理每个 close 自身可能抛出的异常。

1.1.5 修正方案

方案 1：使用 try-with-resources（推荐） Java 7 以上可利用 try-with-resources 自动关闭资源：

public List<User> fetchUsers() throws SQLException {
    String sql = "SELECT id, name FROM users";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        List<User> users = new ArrayList<>();
        while (rs.next()) {
            users.add(new User(rs.getInt("id"), rs.getString("name")));
        }
        return users;
    } // 自动按 rs -> ps -> conn 顺序关闭
}

Spring 的 JdbcTemplate 内部已经使用了 try-with-resources，可以进一步简化。

方案 2：显式 finally 块（Java 6 或更早）

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = dataSource.getConnection();
    ps = conn.prepareStatement(sql);
    rs = ps.executeQuery();
    // 处理结果
} catch (SQLException e) {
    // 异常处理
} finally {
    if (rs != null) { try { rs.close(); } catch (SQLException e) { /* log */ } }
    if (ps != null) { try { ps.close(); } catch (SQLException e) { /* log */ } }
    if (conn != null) { try { conn.close(); } catch (SQLException e) { /* log */ } }
}

方案 3：连接池防御性设置 即使代码泄漏，也能通过连接池参数兜底：

spring:
  datasource:
    hikari:
      leak-detection-threshold: 30000   # 30秒，生产环境合理值
      max-lifetime: 600000              # 10分钟，超过此时间的连接会被池主动废弃回收，即使泄漏也能回收
      connection-timeout: 5000          # 获取连接超时，5秒失败快速抛出异常

1.1.6 最佳实践

1. 强制使用 try-with-resources：通过 SonarQube 规则 squid:S2095 和 java:S2093 静态检查资源关闭。
2. 泄漏检测阈值设为 p99 响应时间的 3~5 倍，例如应用 p99 响应为 2 秒，阈值可设为 10000ms（10秒）。过低会造成正常慢查询误报，过高则检测不及时。
3. 设置 maxLifetime 比数据库最小超时短 1-2 分钟，确保连接即使泄漏也会被池本身剔除并回收到数据库层，防止僵死连接长期占用。
4. 定期审查长时间闲置连接：SELECT pid, state, now() - state_change AS idle_duration FROM pg_stat_activity WHERE state = 'idle' AND application_name = 'yourapp' AND now() - state_change > interval '10 minutes'; 设置告警。
5. 在微服务中，将连接的 application_name 设置为服务名和实例标识，便于快速定位泄漏来源。

连接泄漏排查流程图：

flowchart TD
    A[应用报获取连接超时] --> B[查 pg_stat_activity, 观察 idle 连接数]
    B --> C{idle 连接数是否接近 max_connections?}
    C -->|是| D[打开 HikariCP leakDetectionThreshold, 收集泄漏堆栈]
    C -->|否| E[检查连接池配置: maxPoolSize, timeout 等]
    D --> F[Arthas watch getConnection/close 调用比例]
    F --> G[定位未关闭连接的方法与线程]
    G --> H[修正代码, 使用 try-with-resources 或确保 finally 块完整]
    E --> I[调整池参数, 扩容或优化慢 SQL]
    H --> I

说明：

• 该路径首先从数据库端观测物理连接状态，这一步是全局视角，可以最快证实是否为连接数耗尽。
• 然后切入池层泄漏检测，利用 leakDetectionThreshold 获取具体堆栈，或在无重启条件下用 Arthas 动态追踪 getConnection 和 close 的调用次数差异。
• 诊断过程三层递进：数据库 → 连接池 → 应用代码，层层收敛。
• 实践中，多数连接耗尽由泄漏引起，而非真的流量尖峰。若调查发现 idle 连接数并不高，但 active 却接近池最大值，则可能是 SQL 执行过慢导致连接占用时间长——应转向案例 2.2 或 SQL 优化。
• 修复后，务必移除或降低频繁的 Arthas 监控和过低的 leakDetectionThreshold，避免性能开销。

案例 1.2：maximumPoolSize 过大/过小——配置计算与验证

1.2.1 错误示例

过大：开发人员经验不足，凭感觉设置 maximumPoolSize=200，而数据库 max_connections 只有 150，且应用实例数 5 个。每个实例 200，总申请连接数 1000，远超过数据库承受能力。

spring:
  datasource:
    hikari:
      maximum-pool-size: 200
      minimum-idle: 50

过小：担心资源占用，将池大小设为 5，但应用高峰期并发线程数达到 50，每个请求还需执行一个耗时 200ms 的查询。

hikari:
  maximum-pool-size: 5

1.2.2 现象描述

过大现象：

• 数据库 pg_stat_activity 显示大量 active 连接，甚至超过了数据库的 max_connections 导致拒绝新连接。
• 数据库服务器 CPU sys 和 user 均出现尖峰，context switch 数量激增，磁盘 IO 也可能出现阻塞。
• 应用虽然能获取到连接，但由于数据库端连接争用锁和内核资源，整体 SQL 响应时间反而不稳定且变长。

过小现象：

• HikariCP Metrics 的 hikaricp_connections_pending 持续大于 0，甚至高达数十。
• 应用日志频繁出现 Connection is not available, request timed out after ...。
• 线程 dump 显示大量线程 BLOCKED 或 WAITING 在 HikariPool.getConnection()。
• 吞吐量远未达到数据库或应用服务器的硬件瓶颈。

1.2.3 排查思路

步骤 1：收集连接池运行时指标 使用 Spring Boot Actuator + Micrometer 暴露 HikariCP 指标到 Prometheus，或直接通过 JMX 访问：

// 在应用内通过 MBeanServer 获取
MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();
ObjectName poolName = new ObjectName("com.zaxxer.hikari:type=Pool (HikariPool-1)");
int active = (Integer) mBeanServer.getAttribute(poolName, "ActiveConnections");
int idle = (Integer) mBeanServer.getAttribute(poolName, "IdleConnections");
int pending = (Integer) mBeanServer.getAttribute(poolName, "PendingThreads");
int total = (Integer) mBeanServer.getAttribute(poolName, "TotalConnections");
System.out.println("Active: " + active + ", Idle: " + idle + ", Pending: " + pending + ", Total: " + total);

观察在流量高峰时 ActiveConnections 是否一直贴近 maximumPoolSize，而 PendingThreads 持续为正。

步骤 2：分析数据库连接负载

-- 查看当前活跃连接数及其执行的查询
SELECT count(*) FILTER (WHERE state = 'active') AS active_count,
       count(*) FILTER (WHERE state = 'idle') AS idle_count,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx
FROM pg_stat_activity 
WHERE application_name = 'yourapp';

-- 若 active 连接长期稳定在 max_pool_size，说明池确实不够

步骤 3：压测寻找最佳池大小 使用 JMeter 构建代表线上真实比例的 SQL 场景，逐步增加池大小，观察 TPS 和响应时间变化。经验法则：池大小从 CPU 核心数 * 2 开始，每次增加 5，直至 TPS 不再增长或响应时间开始恶化。这个“拐点”就是最佳大小。 示例：

// 简单压测端点
@GetMapping("/test")
public Map<String, Object> test() {
    jdbcTemplate.queryForList("SELECT 1"); // 模拟快速查
    return Map.of("result", "ok");
}

设置 JMeter 线程组 200 并发，池大小分别为 10、20、30、40 测试。

步骤 4：检查数据库侧连接限制 数据库的 max_connections 必须大于所有应用实例的 maximumPoolSize 总和加上 reserved connections。如果数据库是共享的，还需要协调各应用池大小。

1.2.4 根因分析

池大小的设置遵循排队论。过大的池造成数据库端过度的并发竞争：每个活跃连接消耗一个后端进程（PostgreSQL 约 5-10MB 初始内存），同时多个活跃事务可能竞争相同的行锁、表锁或缓冲区，导致上下文切换开销超过实际工作。此外，许多数据库的优化器在连接数极高时可能改变执行计划的选择。

经典的 PostgreSQL 池大小公式：connections = ((core_count * 2) + effective_spindle_count)，但这个公式假设所有连接都是活跃的。在现代 SSDs 和混合工作负载下，HikariCP 官方推荐：连接池大小 = 常规峰值并发线程数。如果应用是 IO 密集（等待远程服务调用，计算少），过多的连接池反而不利于数据库稳定。

过小的池则导致请求排队，应用线程阻塞在获取连接上，连接本身的利用率不到 100%，而业务线程却大量空闲等待。这浪费了应用服务器的线程资源和数据库的处理能力。

1.2.5 修正方案

根据压测结果和业务特点设定合理值。单服务实例通常设置 20 ~ 50 即可，微服务实例较多时每个实例可设置 10 ~ 20。

spring:
  datasource:
    hikari:
      maximum-pool-size: 30
      minimum-idle: 10
      idle-timeout: 600000
      max-lifetime: 900000

验证修正效果：高峰时 PendingThreads 应保持在 0，ActiveConnections 低于 maximumPoolSize 一定缓冲，平均响应时间稳定。

1.2.6 最佳实践

1. 连接池大小与业务分离：批处理任务和在线 OLTP 必须使用不同的连接池，避免批处理长时间占用连接导致在线请求阻塞。
2. 避免公式化：依据实际压测结果设定，而不是简单的公式。使用 JMeter 加 Gatling 模拟真实流量。
3. 动态调整：如果使用了类似 Kubernetes 的弹性伸缩，应注意 minimumIdle 设置不要过高，否则空闲连接占用数据库资源，阻碍其他实例扩容。
4. 监控告警：设置 hikaricp_connections_pending > 0 连续 3 分钟告警，以及 hikaricp_connections_active 接近 max 且持续 5 分钟告警。

案例 1.3：maxLifetime 与数据库超时冲突——时间线分析与配置对齐

1.3.1 错误示例

场景 1：连接池 maxLifetime=1800000（30分钟），但数据库设置 idle_in_transaction_session_timeout=10min（10分钟），且应用存在偶尔的事务长时间闲置。连接可能在闲置超过 10 分钟后被数据库杀死，而连接池认为该连接仍然有效，导致后续使用时失败。

hikari:
  max-lifetime: 1800000  # 30分钟
  idle-timeout: 300000    # 5分钟

数据库参数：

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

场景 2：云数据库（如 AWS RDS）默认有 connection timeout 设置，可能在 15 分钟断开空闲连接，而应用连接池未及时感知。

1.3.2 现象描述

• 应用中偶发 SQL 异常：org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend 或 This connection has been closed.
• 这种异常不持续，往往在流量较低时出现，或刚好在某个事务闲置后重现。
• 连接池 Metrics 显示 ActiveConnections 偶尔骤降，然后迅速恢复（池丢弃死连接并创建新连接）。这称为“连接风暴”。
• 数据库日志记录：could not receive data from client: Connection reset by peer 或 terminating connection due to idle-in-transaction timeout 或 could not send data to client: Broken pipe。

1.3.3 排查思路

步骤 1：对齐数据库和连接池的超时参数 查询数据库相关超时设置：

SHOW idle_in_transaction_session_timeout;
SHOW statement_timeout;
SHOW tcp_keepalives_idle;
SHOW tcp_keepalives_interval;

连接池配置读取：

System.out.println("maxLifetime: " + hikariConfig.getMaxLifetime());
System.out.println("idleTimeout: " + hikariConfig.getIdleTimeout());

步骤 2：开启数据库连接断开日志

ALTER SYSTEM SET log_disconnections = 'on';
SELECT pg_reload_conf();

当连接断开时，日志会记录原因，例如：

2025-05-13 11:05:00 UTC LOG:  disconnection: session time: 0:12:34.567 user=app database=mydb host=10.0.1.25 port=5433 

结合应用错误时间，如果断开时间刚好在 idle_in_transaction_session_timeout 附近，那么超时冲突就是元凶。

步骤 3：Arthas 追踪异常连接 在连接获取时记录创建时间，在执行查询时报错可推断该连接已被数据库杀死。利用 Arthas trace 功能查看错误 SQL 执行时连接的状态：

trace com.zaxxer.hikari.pool.HikariProxyPreparedStatement executeQuery '#cost > 1000' -n 5

步骤 4：时间线计算 确保池的生命周期参数满足：maxLifetime < 数据库的最小超时 - 安全余量（通常为 1-2 分钟）。连接在 idleTimeout 后被池回收，也应在数据库空闲超时之前。

1.3.4 根因分析

HikariCP 的 maxLifetime 用于控制连接最大存活时间，无论连接是否空闲，到达时间后池会将其剔除。但是在连接被业务借用期间，驱逐检查不会中断查询。如果一次查询或事务执行时间超过了数据库设置的 statement_timeout 或 idle_in_transaction_session_timeout，数据库侧会单方面终止连接。池在下次尝试使用此连接时，发现 IO 错误，便会抛出异常。

此外，idleTimeout 控制连接在池中空闲多久后被回收，若此值大于数据库的空闲超时，则会出现连接在池中闲着，但数据库已将其杀死。池仍然认为连接有效，但实际已断开，出现类似与场景 1 的异常。

根因在于应用层和数据库层生命周期参数不协调。连接池管理的连接是长连接，但数据库可能基于自己的策略（云厂商的固定超时、管理员配置的空闲断连）强制断开它们，而连接池无法实时感知底层的 TCP 断开状态，直到发生 IO 操作。

1.3.5 修正方案

方案 1：统一主动回收等待数据库被动断开 将连接池的 maxLifetime 设置为比数据库最小超时（包括 idle_in_transaction_session_timeout、statement_timeout、云服务商空闲超时）短 1-2 分钟。并且 idleTimeout 设置为比数据库的空闲超时也短。

hikari:
  max-lifetime: 540000   # 9分钟
  idle-timeout: 480000   # 8分钟（云厂商空闲断连10分钟）

数据库侧：

ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';  -- 适当放宽，压力不大的环境可设15min
SELECT pg_reload_conf();

方案 2：利用 TCP KeepAlive 探测死连接 即使设置了 maxLifetime，若业务存在长时间运行的事务，事务内空闲可能超时。可在数据库和驱动侧启用 TCP keepalive：

# JDBC URL 添加 TCP keepalive 相关参数
jdbc:postgresql://host:5432/db?tcpKeepAlive=true&socketTimeout=600

PostgreSQL 端：

SET tcp_keepalives_idle = 60;  -- 60秒无数据就发探测
SET tcp_keepalives_interval = 30;

方案 3：避免事务内空闲 从根本上减少 idle in transaction 的出现（参见案例 2.2），缩短事务执行时间，避免在事务内调用外部 RPC 或进行耗时计算。

1.3.6 最佳实践

1. 设定黄金法则：maxLifetime < 数据库最小连接超时 - 2 * delta，其中 delta 可取 60 秒。
2. 云数据库（RDS、云原生数据库）超时通常较短（10-15 分钟），务必确认并调整池生命周期。
3. 不要将 maxLifetime 设置得过短（如几分钟），否则会频繁创建销毁连接，增加数据库负担。
4. 在所有环境中启用 TCP Keepalive，并确保 socketTimeout 小于事务的最长可接受时间，防止僵死连接。

案例 1.4：未使用连接池直连——性能对比与迁移指导

1.4.1 错误示例

遗留系统或简单脚本仍在使用 DriverManager.getConnection() 每次请求新建连接：

public class LegacyDAO {
    public String getUsername(int id) {
        String url = "jdbc:postgresql://dbhost:5432/mydb";
        Properties props = new Properties();
        props.setProperty("user", "user");
        props.setProperty("password", "pass");
        try (Connection conn = DriverManager.getConnection(url, props);
             PreparedStatement ps = conn.prepareStatement("SELECT username FROM users WHERE id = ?")) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

虽然使用了 try-with-resources 正确关闭，但每次调用都经历完整的 TCP 三次握手、SSL 握手（若配置）、认证、后端进程 fork 等开销。即使对于低并发，也会拖慢响应。

1.4.2 现象描述

• 简单查询的平均响应时间在 20-50ms 以上（其中连接建立消耗超过 10ms）。
• 当并发量增加时，数据库 pg_stat_activity 中连接建立/断开的 authentication 过程频发，甚至出现 too many clients 错误。
• 应用吞吐量远低于服务器硬件能力上限。

对比测试：在同一环境下，用 JMeter 分别测试直连和连接池的 TPS。

• 直连 100 并发，每条请求执行 SELECT 1，TPS 可能只有 500 左右。
• HikariCP 连接池 20 大小，相同压测，TPS 可达 5000+。

1.4.3 排查思路

如果怀疑业务使用直连，可以：

1. 代码审查：搜索 DriverManager.getConnection 或 new com.mysql.cj.jdbc.MysqlConnection 等模式。
2. 数据库连接方差监测：SELECT application_name, count(*), state FROM pg_stat_activity GROUP BY 1, 3; 观察连接频率，若每个请求都产生一个新的 application_name 可能是未设置，但可以通过连接建立速率监控：执行 SELECT count(*) FROM pg_stat_activity WHERE backend_start > now() - interval '1 minute'; 每分钟建连数极高即为直连。
3. Arthas trace：trace java.sql.DriverManager getConnection -n 50 可看到调用频繁程度。

1.4.4 根因分析

PostgreSQL 为每个连接 fork 一个操作系统进程，并分配私有内存。频繁建连/断连不仅消耗操作系统资源，还涉及大量的网络握手。连接池通过复用连接，减少了 95% 以上的建连成本。

1.4.5 修正方案

迁移到连接池（以 HikariCP 为例）：

// 定义一个全局 DataSource Bean
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://dbhost:5432/mydb?ApplicationName=myapp");
        config.setUsername("user");
        config.setPassword("pass");
        config.setMaximumPoolSize(30);
        config.setMinimumIdle(5);
        config.setIdleTimeout(600000);
        return new HikariDataSource(config);
    }
}

业务代码全部通过注入的 DataSource 获取连接：

@Autowired
private DataSource dataSource;

public String getUsername(int id) throws SQLException {
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT username FROM users WHERE id = ?")) {
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        return rs.next() ? rs.getString(1) : null;
    }
}

1.4.6 最佳实践

1. 任何生产应用必须使用连接池，避免直连。即便是批处理脚本，也推荐使用连接池单次执行。
2. 全局 DataSource 单例，避免多个池竞争同一数据库资源。Spring Boot 自动配置已确保单例。
3. 数据库连接 URL 指定 ApplicationName，便于监控识别。

---

2. SQL 执行反模式

聚焦 Statement/ResultSet 使用、事务边界和结果集处理。本节案例大量引用第 3 篇《Spring JDBC 整合》与第 5 篇《批处理内核》，并通过详细的 pg_stat_statements 分析展示性能影响。

案例 2.1：fetchSize 未设置导致 OOM——堆分析 + 游标配置

2.1.1 错误示例

查询百万行或大字段表时，未指定 fetchSize，驱动默认一次性将所有结果加载到内存。

public List<Map<String, Object>> exportAllLogs() {
    return jdbcTemplate.queryForList("SELECT * FROM application_logs");
}

application_logs 表含有 500 万行，每行包含一个 TEXT 字段存储请求响应体，单行平均 2KB。总数据量约 10GB。应用 JVM 堆设置为 -Xmx2g，必定 OOM。

2.1.2 现象描述

• 应用每次执行到此查询时，堆内存迅速攀升，触发连续的 Full GC，最终 java.lang.OutOfMemoryError: Java heap space。
• 堆转储分析（使用 MAT）显示，com.zaxxer.hikari.pool.HikariProxyResultSet 或 org.postgresql.jdbc.PgResultSet 内部持有巨大的 byte[][] rows 或 java.util.ArrayList，其中包含了所有行的数据。
• pg_stat_activity 中该查询的 state 长时间显示为 active，网络传输流出大量数据。
• 该查询的 pg_stat_statements 记录中 rows 数为 5000000。

2.1.3 排查思路

步骤 1：堆转储分析 OOM 当 OOM 发生时，自动生成堆转储（-XX:+HeapDumpOnOutOfMemoryError）。使用 MAT 打开 *.hprof，查看 “Leak Suspects” 报告。若发现一个巨大 ArrayList 或类似集合是 ResultSet 的一部分，则确定是查询结果集问题。

步骤 2：检查 SQL 返回行数

SELECT COUNT(*) FROM application_logs;

确认数据量级巨大。

步骤 3：检查驱动默认 fetchSize 默认情况下，PgStatement.getFetchSize() 返回 0，表示一次性获取全部。可通过 Arthas 确认：

# 获取 PreparedStatement 的 fetchSize
watch org.postgresql.jdbc.PgStatement getFetchSize '{returnObj}' -x 2

步骤 4：检查 pg_stat_statements 验证

SELECT queryid, query, calls, rows, mean_exec_time 
FROM pg_stat_statements 
WHERE query LIKE '%application_logs%' 
ORDER BY rows DESC;

如果每次调用返回全部行，那么 rows / calls 即为全表行数。

2.1.4 根因分析

PostgreSQL JDBC 驱动默认 fetchSize=0 意指“一次性获取所有结果行”。驱动在 executeQuery 时发送 Execute 消息，服务器端将整个查询结果打包发送给客户端，客户端驱动解析全部结果并缓存在内存中。这在结果集很小时是高效的，但对于大结果集会迅速耗尽 JVM 堆内存。

游标机制（fetchSize > 0）则是利用 PostgreSQL 的 portal（轻量级游标）功能，客户端可以在一个事务中声明游标，然后分批次 FETCH 指定行数，一边获取一边处理，内存占用仅与 fetchSize 相关。

需要注意：游标必须在事务内工作，因此需要 conn.setAutoCommit(false)。

2.1.5 修正方案

方案 1：通过 Statement.setFetchSize

public void processLogs() {
    jdbcTemplate.query("SELECT * FROM application_logs", rs -> {
        // 但 JdbcTemplate 默认 conn 是自动提交，需要覆写
    });
}

更精细控制：

jdbcTemplate.execute((Connection conn) -> {
    conn.setAutoCommit(false);
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM application_logs");
    ps.setFetchSize(5000);  // 每次取 5000 行
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        // 处理每一行，内存只缓存 5000 行
        String row = rs.getString("log");
        // 处理...
    }
    rs.close();
    ps.close();
    conn.commit();
    return null;
});

方案 2：全局设置 defaultRowFetchSize 驱动参数 在 JDBC URL 上直接指定：

jdbc:postgresql://host:5432/db?defaultRowFetchSize=5000

这样所有通过此 DataSource 创建的 Statement 默认都使用游标。但注意，JdbcTemplate 每次操作通常会自动提交事务，可能导致游标失效（因为游标必须在事务内）。因此全局设置需要配合 Spring 事务管理（在 @Transactional 方法内使用）。

方案 3：Spring 批处理支持 使用 RowMapper 配合 JdbcTemplate.query 并设置 fetchSize：

SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM application_logs");
// 内部并不游标，依然全量，所以需改用 NamedParameterJdbcTemplate 的自定义 PreparedStatementCallback

最佳是在 Spring Batch 中使用 JdbcCursorItemReader，它原生支持 fetchSize。

2.1.6 最佳实践

1. 任何可能返回超过 1000 行的查询，必须设置 fetchSize。根据行宽，一般 1000~10000 可达到良好性能。
2. 全局 defaultRowFetchSize 适合 OLTP 大量小查询也可以开启游标吗？ 不建议，因为游标引入了额外的事务要求和网络往返。对于小查询（返回行数少），一次 fetch 更高效。推荐在全局不设，在特定大批量查询的方法中显式设置 fetchSize。
3. 注意事务边界：确保游标查询在一个显式事务中运行，否则驱动可能退化或报错。
4. 服务器端游标会占用数据库临时资源，如果客户端处理过慢，可能导致数据库端 active 连接长时间存在。需要合理设置 fetchSize 和处理速度。
5. 与 maxRows 配合：stmt.setMaxRows(limit) 可限制总行数，同时使用游标控制内存。

案例 2.2：autoCommit 未关导致长事务——idle in transaction 检测与 VACUUM 阻塞

2.2.1 错误示例

场景 1：Spring @Transactional 使用不当，在事务方法内进行 RPC 调用或阻塞等待，造成事务长时间不提交。

@Service
public class OrderService {
    @Transactional
    public void processOrder(int orderId) {
        // 1. 更新订单状态
        jdbcTemplate.update("UPDATE orders SET status = 'PROCESSING' WHERE id = ?", orderId);
        // 2. 调用外部支付系统，可能耗时 10 秒
        paymentGateway.charge(orderId);
        // 3. 更新为已支付
        jdbcTemplate.update("UPDATE orders SET status = 'PAID' WHERE id = ?", orderId);
    }
}

如果支付接口响应缓慢或异常，事务一直持有未提交持续数分钟。

场景 2：手动管理连接，关闭了 autoCommit 但忘记提交或回滚，或者连接池归还时未恢复 autoCommit 状态。

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
// 执行业务...
// 忘记 conn.commit() 或 conn.rollback()
conn.close(); // 连接归还池，但 autoCommit 仍为 false，导致下个借用者处于事务中

2.2.2 现象描述

• pg_stat_activity 中出现大量 idle in transaction 状态的连接，query 列显示为上次执行的 SQL，xact_start 早于当前时间很久。
• 表膨胀严重：SELECT n_dead_tup, n_live_tup FROM pg_stat_user_tables WHERE relname = 'orders'; n_dead_tup 数量巨大。由于长事务阻止 VACUUM 清理死元组，导致表大小不断增长。
• 可能发生锁阻塞：长事务可能持有行锁，导致其他事务等待，pg_locks 中可见 not granted 的记录。
• 如果 autoCommit 未恢复，连接池内的连接会被“污染”，导致后续使用该连接的操作处于不易察觉的事务上下文中，引发数据不一致或意外的锁持有。

2.2.3 排查思路

步骤 1：识别 idle in transaction 连接

SELECT pid, usename, application_name, state, now() - xact_start AS txn_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction' 
  AND application_name = 'yourapp'
ORDER BY txn_age DESC;

如果存在持续 5 分钟以上的此类连接，可能是问题所在。

步骤 2：确定事务持有的锁

SELECT l.pid, l.relation::regclass, l.mode, l.granted, 
       a.query, a.state, now() - a.xact_start AS txn_age
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted AND a.application_name = 'yourapp';

步骤 3：检查连接池中的连接是否携带未关闭事务 HikariCP 默认在连接归还时自动回滚未提交的事务（如果 autoCommit 是 false，它会执行 rollback() 吗？官方文档：HikariCP 不会改变 autoCommit 状态，但如果你设置 autoCommit=false 并在归还前未提交，池不会自动回滚，这会导致下个借用者继承该事务状态）。可以通过测试证实：若发现不正常的 idle in transaction，在池配置中开启 leakDetectionThreshold，看是否有长时间占用连接的堆栈。

步骤 4：开启数据库参数终止长闲置事务

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

这样超过 5 分钟的 idle in transaction 会被数据库强制终止，释放资源。

2.2.4 根因分析

事务未及时提交或回滚，连接在归还池时仍处于事务中。当连接归还后，它进入空闲队列，但数据库侧该连接的事务并未结束，因此 PostgreSQL 认为它处于 idle in transaction 状态。由于 PostgreSQL 的 MVCC，一个长时间打开的事务（无论是否活跃）会阻止 VACUUM 清理在该事务启动后产生的死元组，因为该事务仍可能读取那些元组。这导致表膨胀。

此外，如果连接池未改变 autoCommit，下一个借用该连接的业务代码如果也未设置 autoCommit，将在前一个事务的上下文中操作，造成混乱。

2.2.5 修正方案

方案 1：Spring 事务边界优化 将外部调用移出事务：

@Service
public class OrderService {
    public void processOrder(int orderId) {
        // 第一步，内部事务
        updateStatusInNewTransaction(orderId, "PROCESSING");
        // 外部调用
        paymentGateway.charge(orderId);
        // 第二步，内部事务
        updateStatusInNewTransaction(orderId, "PAID");
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void updateStatusInNewTransaction(int orderId, String status) {
        jdbcTemplate.update("UPDATE orders SET status = ? WHERE id = ?", status, orderId);
    }
}

或者使用异步消息解耦，支付成功后再更新状态。

方案 2：确保手动连接正确管理

Connection conn = dataSource.getConnection();
boolean originalAutoCommit = conn.getAutoCommit();
try {
    conn.setAutoCommit(false);
    // 业务逻辑
    conn.commit();
} catch (Exception e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(originalAutoCommit); // 恢复
    conn.close();
}

连接池层面，HikariCP 默认 autoCommit=true，建议业务代码不要全局改为 false，而是在需要时局部改变。

方案 3：数据库侧兜底

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

避免长事务永久存在。

2.2.6 最佳实践

1. 事务尽可能短：不在事务内调用外部系统、不等待用户输入。
2. 数据库层设置 idle_in_transaction_session_timeout 作为安全网，推荐 5-15 分钟。
3. 监控 idle in transaction 连接数并告警：超过 0 持续 2 分钟即展开排查。
4. 连接池层面，保持 autoCommit 默认 true，使用声明式事务管理，避免手动设置 autoCommit，归还时 Spring 会处理提交/回滚。

案例 2.3：预编译未生效硬解析泛滥——pg_stat_statements 中 calls 与 planning 时间分析

此例与第 4 篇《预编译内核》和第 9 篇驱动参数形成强联动，透过 pg_stat_statements 数据定位硬解析。

2.3.1 错误示例

场景 1：使用 Statement 拼接参数，而不是 PreparedStatement。

Statement stmt = conn.createStatement();
for (int id : ids) {
    ResultSet rs = stmt.executeQuery("SELECT * FROM items WHERE item_id = " + id);
    // ...
}

每次传入不同的 id，SQL 文本都不同，数据库无法缓存查询计划。

场景 2：虽然使用了 PreparedStatement，但每次新建对象且只执行一次就关闭，并未达到 prepareThreshold，同时连接池频繁回收连接，计数器无法累计。

for (int i=0; i<100; i++) {
    PreparedStatement ps = conn.prepareStatement("SELECT * FROM items WHERE item_id = ?");
    ps.setInt(1, i);
    ps.executeQuery();
    ps.close();
}

默认 prepareThreshold 为 5，但每次 ps 都是新对象，执行 1 次就关闭了，不会触发服务端预编译。

2.3.2 现象描述

• pg_stat_statements 中，相同模板的 SQL 出现多个 queryid 条目，每个条目 calls 很小，例如 1 或 2。同时 total_plan_time 在整体响应时间中占比较高。
• 数据库 CPU sys 消耗升高（解析 SQL 涉及内核调用），硬解析数量可通过 pg_stat_database 的 xact_commit 与 blks_read 等间接反映，但直接观察 pg_stat_statements 的 mean_plan_time 大于 0.1ms 且占总时间比例高。
• 应用平均查询响应时间不稳定，有明显抖动。
• 使用 pg_prepared_statements 查看：SELECT count(*) FROM pg_prepared_statements; 结果可能为 0 或很少。

2.3.3 排查思路

步骤 1：使用 pg_stat_statements 分析解析时间

SELECT queryid, query, calls, 
       total_plan_time, total_exec_time,
       mean_plan_time, mean_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_plan_time DESC
LIMIT 20;

如果某个查询模板有大量 queryid（即相同的 query 但有多个 queryid），说明每次执行都被当作一个全新的语句，硬解析泛滥。若 calls 为 1，则每次都是硬解析。

步骤 2：检查应用代码 代码审计，是否使用了动态拼接 SQL 或 Statement，或 PreparedStatement 未复用。

步骤 3：检查驱动及连接池配置 查看 prepareThreshold 设置：

// 默认值为 5
PGProperty.PREPARE_THRESHOLD.get(info); // 通过代码查看

查看 JDBC URL 是否有 prepareThreshold 参数。 检查连接池是否执行了 DISCARD ALL 导致预编译缓存清空。

步骤 4：Arthas 跟踪 Parse 消息 如果开启驱动日志不方便，可以用 Arthas 跟踪 PreparedStatement 的 executeQuery 内部调用：

# 监控 PgStatement 的 execute 方法，看是否传递了参数
trace org.postgresql.jdbc.PgPreparedStatement executeQuery -n 50

（难以直接看到 Parse，但可以监控到频繁的 prepareStatement 调用）

2.3.4 根因分析

PostgreSQL 的预编译机制依赖于客户端驱动发送 Parse 消息（包含 SQL 模板）给服务端，生成命名的 prepared statement。之后通过 Bind 和 Execute 复用。驱动通过内部计数器判断是否达到 prepareThreshold 来决定是否进行服务端预编译。如果压根没使用 PreparedStatement，自然不会有 Parse；如果使用了，但同一对象只执行 1-2 次，也未达到默认阈值；且即使达到了，连接关闭后服务端缓存也会清除。再者，某些连接池为了重置连接（如 Tomcat JDBC）会执行 DISCARD ALL，清除当前会话的所有预编译语句。

因此，硬解析泛滥的根因是SQL 模板无法被稳定复用，驱动无法或不愿发起服务端预编译，导致每条执行都包含 Parse+Plan+Execute，严重浪费数据库 CPU。

2.3.5 修正方案

方案 1：使用 PreparedStatement 并降低 prepareThreshold 在 JDBC URL 中设置：

jdbc:postgresql://host/db?prepareThreshold=1

这样第一次执行即会创建服务端预编译语句，适用于 OLTP 系统中 SQL 模板有限且频繁重复的情况。

方案 2：确保连接池不执行破坏性重置 HikariCP 默认不执行 DISCARD ALL，保持兼容。但如果自己配置了 connectionInitSql 或其他参数，千万不要包括 DISCARD ALL。

方案 3：Spring JdbcTemplate 内部复用 JdbcTemplate 的 query(String sql, PreparedStatementSetter, RowCallbackHandler) 方法内部会创建 PreparedStatement 并执行一次，通常不会触发预编译。若想复用，可以使用 PreparedStatementCallback：

jdbcTemplate.execute("SELECT * FROM items WHERE item_id = ?", 
    (PreparedStatement ps) -> {
        for (int id : ids) {
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            // 处理
            rs.close();
        }
        return null;
    });

这将在同一个 PreparedStatement 上多次执行，促使达到阈值。

方案 4：服务端预编译缓存监控与验证

SELECT name, statement, prepare_time, parameter_types FROM pg_prepared_statements;

定期检查条目，确保核心 SQL 都在缓存中。

2.3.6 最佳实践

1. OLTP 系统默认设置 prepareThreshold=1，减少解析开销。
2. 利用连接池的稳定性：HikariCP 长连接复用，有利于预编译缓存跨越多次请求存活。
3. 避免在循环内重复创建 PreparedStatement；应在循环外创建，内循环复用。
4. 定期清理过期的预编译语句：如果应用有大量一次性查询（如报表），可单独使用连接池，设置高 prepareThreshold 以减少服务端缓存。

案例 2.4：Statement 拼接 SQL 导致注入风险与性能下降——WallFilter 日志验证

2.4.1 错误示例

经典字符串拼接：

String userId = request.getParameter("userId");
String query = "SELECT * FROM users WHERE user_id = '" + userId + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);

如果 userId 为 ' OR 1=1 --，则返回全表数据。

2.4.2 现象描述

• 安全扫描报告指出 SQL 注入漏洞。
• 如果使用了 Druid 连接池并开启了 WallFilter，日志中会出现：

  2025-05-13 11:20:00 ERROR [Druid-DataSource-WallFilter-1] com.alibaba.druid.wall.WallFilter : 
  sql injection violation, part deny : select * from users where user_id = '' OR 1=1 --'
  

  相应请求被拦截。
• 即使没有注入攻击，这类拼接 SQL 使得每个用户 ID 都产生一个全新的 SQL 文本，导致查询计划缓存频繁变动，性能下降。

2.4.3 排查思路

1. 静态代码扫描：使用 SpotBugs 或 SonarQube 规则 SQL_INJECTION_JDBC 发现 Statement 使用。
2. Druid SQL 监控：查看 WallFilter 拦截历史，分析哪些 SQL 被拦截。
3. 动态校验：尝试输入特殊字符 ' OR '1'='1，确认应用是否报错或返回异常。

2.4.4 根因分析

字符串拼接使不可信的数据与 SQL 命令混淆。数据库无法区分数据和代码，为注入攻击打开大门。同时，每次拼接出的 SQL 文本不同，优化器需要重新解析，增加了成本，也阻碍了预编译。

2.4.5 修正方案

统一使用参数化查询：

PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE user_id = ?");
ps.setString(1, userId);
ResultSet rs = ps.executeQuery();

Druid WallFilter 可保留默认黑名单模式，并通过白名单动态放行必要动态表名。

2.4.6 最佳实践

1. 永远不要用字符串拼接用户输入到 SQL。
2. 代码层面启用 SpotBugs 规则 SQL_INJECTION_JDBC。
3. Druid WallFilter 生产环境建议开启拦截模式，但需定期审查误伤。

---

3. 预编译反模式

深入第 4 篇的原理，聚焦驱动与数据库协作中的失效场景。下面的案例通过 pg_prepared_statements 和 pg_stat_statements 结合排查。

案例 3.1：prepareThreshold 过高——pg_prepared_statements 为空，每条 SQL 仍 PARSE

3.1.1 错误示例

假设 prepareThreshold 采用默认值 5，但应用的 SQL 在执行 3 次后就不再执行，循环里每次都新建 PreparedStatement。

for (int i=0; i<3; i++) {
    PreparedStatement ps = conn.prepareStatement("SELECT name FROM users WHERE id = ?");
    ps.setInt(1, i);
    ps.executeQuery();
    ps.close();
}

该代码中，同一个 SQL 模板被执行了 3 次，但因为每次执行都是通过不同的 PreparedStatement 对象，每个对象的执行计数器独立，都为 1，没有达到阈值 5。

3.1.2 现象描述

服务端 SELECT * FROM pg_prepared_statements; 返回空。驱动每次执行时，都在网络上发送 Parse、Bind、Execute、Sync 消息序列（通过抓包可见）。pg_stat_statements 显示该模板的 calls 为 3，但 mean_plan_time 相对较高，因为没有利用预编译。

3.1.3 排查思路

通过查询确认：

SELECT count(*) FROM pg_prepared_statements;

若为 0 或极少，而应用实际有大量重复 SQL 调用，则很可能是阈值问题。

开启驱动日志 &loggerLevel=TRACE&loggerFile=pgjdbc.log，查看日志中是否包含 Parse: 消息。

3.1.4 根因分析

驱动根据每个 PgPreparedStatement 对象的执行次数来计数。prepareThreshold 控制在该对象上执行多少次后才向服务器发送 Parse 消息。如果对象被频繁创建销毁，计数器永远到不了阈值。

3.1.5 修正方案

降低 prepareThreshold：

# application.properties
spring.datasource.hikari.data-source-properties.prepareThreshold=1

或使用连接池复用 PreparedStatement（如 Spring 的 JdbcTemplate 可以考虑包装模式，但默认不支持）。最简单是设置阈值为 1。

业务代码优化：在循环外创建 PreparedStatement 复用：

PreparedStatement ps = conn.prepareStatement("SELECT name FROM users WHERE id = ?");
for (int i=0; i<10; i++) {
    ps.setInt(1, i);
    ps.executeQuery();
}
ps.close();

3.1.6 最佳实践

对于重复执行相同模板的查询，统一将 prepareThreshold 降为 1 或 0（0 表示禁用服务端预编译，适合一次性查询）。默认 OLTP 建议 1。

案例 3.2：连接池重置导致缓存失效——HikariCP connectionTestQuery 配置查看与修复

3.2.1 错误示例

用户在 HikariCP 未提供 connectionTestQuery 的情况下，错误地模仿 Tomcat JDBC 配置了 connectionInitSql：

spring:
  datasource:
    hikari:
      connection-init-sql: "DISCARD ALL"

或者使用了其他连接池（如 Tomcat JDBC Pool）并开启了 testOnBorrow=true 并执行 DISCARD ALL 做清理。

3.2.2 现象描述

即使 prepareThreshold=1，pg_prepared_statements 中的条目在每次连接归还并再次借用后消失。硬解析周期性增加，性能毛刺与连接池的回收/创建节奏吻合。监控 pg_stat_statements 的 total_plan_time 会出现每隔几分钟的尖峰。

3.2.3 排查思路

检查连接池所有初始化 SQL 和验证查询的配置项：

HikariConfig config = ...;
System.out.println("connectionInitSql: " + config.getConnectionInitSql());
System.out.println("connectionTestQuery: " + config.getConnectionTestQuery());

在数据库日志中观察是否有 DISCARD ALL 的执行。

3.2.4 根因分析

DISCARD ALL 会清除会话的所有临时资源，包括已创建的预编译语句、临时表、序列状态等。每次连接重新初始化时执行，就会导致之前建立的预编译缓存全部丢失。HikariCP 本身不需要也不执行 DISCARD ALL，因为它通过 isValid() 或 ping 检测连接，不会影响状态。

3.2.5 修正方案

移除任何包含 DISCARD ALL 的初始化 SQL，或直接删除 connectionInitSql 和 connectionTestQuery 配置。HikariCP 能自行管理连接有效性。

3.2.6 最佳实践

1. 信任 HikariCP 的内置校验机制，不必画蛇添足。
2. 如果必须使用其他连接池，避免执行 DISCARD ALL，使用 SELECT 1 或其他无副作用的语句验证连接。
3. 监控 pg_prepared_statements 数量趋势，异常归零时告警。

案例 3.3：动态 SQL 过多导致服务端缓存膨胀——pg_prepared_statements 行数监控与 DEALLOCATE 策略

3.3.1 错误示例

应用后台管理系统，根据数十种筛选条件动态拼接 SQL，每种组合都生成一段 SQL 模板，且 prepareThreshold 设为 1，导致每种模板都创建了服务端预编译语句。

String base = "SELECT * FROM products WHERE 1=1 ";
if (name != null) base += "AND name LIKE ? ";
if (category != null) base += "AND category_id = ? ";
// 类似条件 20 个...
PreparedStatement ps = conn.prepareStatement(base);
// 设置参数并执行

不同的条件组合产生大量 SQL 模板变体，可达数百种。

3.3.2 现象描述

SELECT count(*) FROM pg_prepared_statements; 返回上百条甚至上千条。数据库共享内存配置 shared_buffers 不足以支撑，日志出现 could not create prepared statement: out of shared memory。新建连接时可能失败。

3.3.3 排查思路

直接查询：

SELECT name, statement FROM pg_prepared_statements ORDER BY prepare_time DESC;

如果数量接近 max_prepared_transactions（虽然默认是 0 表示不限制数量，但受共享内存限制）或持续增长，即为问题。

3.3.4 根因分析

每个服务端预编译语句需要消耗共享内存中的缓存空间来保存解析树和查询计划。大量的变体不仅不会提升性能，反而挤占了宝贵的库缓存，甚至导致 OOM。

3.3.5 修正方案

方案 1：动态 SQL 转为通用查询 使用参数化方式处理可选条件，避免拼出几十种模板：

SELECT * FROM products 
WHERE (name IS NULL OR name LIKE ?) 
  AND (category_id IS NULL OR category_id = ?);

通过传入 NULL 或有效值，减少 SQL 模板变体。

方案 2：对非重复动态 SQL 禁用服务端预编译 在特定连接或 Statement 上设置 prepareThreshold=0 来避免生成缓存。可以在 URL 中配置或使用 ps.setPrepareThreshold(0)（PG JDBC 支持）。

方案 3：定期释放长时间未使用的缓存 通过 DEALLOCATE ALL 或 DEALLOCATE <name> 释放。但必须小心在活动连接中执行，否则可能影响并发查询。更稳妥的是通过监控自动清理：当一个 pg_prepared_statements 超过一定时间未被使用，考虑让应用手动释放。

3.3.6 最佳实践

1. 限制动态 SQL 变体数量（<50），避免缓存爆炸。
2. 采用 ORM 查询框架（如 jOOQ、MyBatis）的条件构造，内部优化变体。
3. 设置数据库级参数 max_prepared_transactions？ 其实它控制并发两阶段提交的预编译事务数，不是限制 pg_prepared_statements。真正的限制是共享内存。因此更要控制应用产生缓存的数量。

预编译失效排查序列图：

sequenceDiagram
    participant App as 应用
    participant Driver as PG JDBC Driver
    participant Pool as 连接池
    participant DB as PostgreSQL

    App->>Pool: getConnection()
    Pool-->>App: 连接(计数器归零)
    App->>Driver: prepareStatement(sql)
    Driver->>DB: Parse (如果count >= threshold)
    alt 达到阈值
        DB-->>Driver: prepared plan name
        Driver->>DB: Bind/Execute
    else 未达到阈值
        Driver->>DB: 简单查询协议(Parse+Execute同时)
    end
    App->>Pool: close connection
    opt 连接池误配置 DISCARD ALL
        Pool->>DB: DISCARD ALL
        DB-->>Pool: 清除预编译缓存
    end
    Pool->>App: 连接返回池中
    Note over App,DB: 下次获取连接，计数器归零，再次硬解析

说明：

• 此序列图详细展示了预编译失效的两种路径：一是 prepareThreshold 未达到未发送 Parse；二是连接池重置破坏了已有缓存。
• 在 HikariCP 默认配置中不会执行 DISCARD ALL，但若管理员误加 connectionInitSql，则跌落陷阱。
• 排查时可借助 pg_prepared_statements 视图验证缓存状态，用 pg_stat_statements 的 total_plan_time 量化影响。

---

4. 批处理反模式

第 5 篇深入了 batch 内核，这里将其投射到配置错误中，通过详细的参数对照揭示吞吐瓶颈。

案例 4.1：未开启 SQL 重写——rewriteBatchedInserts=off 导致逐条 INSERT

4.1.1 错误示例

本案例是批处理性能杀手之一。代码如下：

String sql = "INSERT INTO logs (user_id, action, log_time) VALUES (?, ?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (int i = 0; i < 10000; i++) {
        ps.setInt(1, i % 100);
        ps.setString(2, "action" + i);
        ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now()));
        ps.addBatch();
    }
    ps.executeBatch();
}

JDBC URL 未指定 reWriteBatchedInserts 参数，PG JDBC 默认其为 false。驱动会将每条 addBatch 参数留在客户端，当执行 executeBatch 时，将 10000 条独立的 INSERT 语句依次发送给服务器，每条都带完整的参数值。网络包中 10000 次独立的请求/响应（即使在一定程度的 pipeline 下），数据库也需逐一解析执行。

4.1.2 现象描述

• 批量插入的吞吐量远低于预期，例如插入 10 万行需要 30 秒，TPS 约 3000。
• 网络带宽消耗极大，应用与数据库之间的包数量巨大，网络延迟主导了总耗时。
• 查看数据库日志，如果开启 log_statement = 'all'，会看到大量的 INSERT INTO logs (user_id, action, log_time) VALUES ($1, $2, $3) 单行插入记录，而不是一条多行的 INSERT。
• pg_stat_statements 中该插入语句的 calls 为 10000，rows 为 10000。

4.1.3 排查思路

步骤 1：检查连接 URL 参数

// 取得当前 DataSource 并查看 url
HikariDataSource ds = (HikariDataSource) dataSource;
System.out.println(ds.getJdbcUrl());

确认是否包含 reWriteBatchedInserts=true。

步骤 2：抓包或开启 PG 日志检验 暂时开启 log_statement = 'all' 并重定向到临时表，执行批处理然后查看日志中形式。如果是重复的单行 INSERT，则是未重写。

步骤 3：对比开启重写前后性能 在测试环境修改 URL 为：

jdbc:postgresql://host/db?reWriteBatchedInserts=true

重新压测，TPS 应显著提高，例如提高到 15000+。

4.1.4 根因分析

reWriteBatchedInserts 参数控制着 PG JDBC 驱动是否将批量插入语句重写为多行 VALUES 形式。例如：

INSERT INTO logs (user_id, action, log_time) VALUES (1, 'action1', '2025-01-01'), (2, 'action2', '2025-01-01'), ...;

服务器可以将整个多行插入作为一个命令处理，减少解析和规划开销，并且在一个事务中可能更有效地写入 WAL。不开启则每条 INSERT 都是独立的消息交换，网络往返和解析开销巨大。

4.1.5 修正方案

在 JDBC URL 上添加：

?reWriteBatchedInserts=true

同时确保 prepareThreshold 与批处理配合，最好设为 1，以便预编译也被使用。完整 URL：

jdbc:postgresql://host/db?reWriteBatchedInserts=true&prepareThreshold=1

代码无需改动，驱动会自动重写 executeBatch。

4.1.6 最佳实践

1. 批量 INSERT 绝对必须开启 reWriteBatchedInserts=true，这是性价比最高的优化。
2. 注意重写有一定的限制：rewriteBatchedInserts 只对 INSERT ... VALUES 语句有效，对 INSERT ... SELECT 或 COPY 不适用。遇到 INSERT ... ON CONFLICT ... 的某些变体也可能不重写。
3. 配合 prepareThreshold=1 确保预编译，使重写后的语句也能受益。

案例 4.2：批大小过大导致客户端 OOM 或 WAL 暴涨

4.2.1 错误示例

将所有数据积累到一个超级大 batch 中执行：

PreparedStatement ps = conn.prepareStatement("INSERT INTO events (payload) VALUES (?)");
for (int i = 0; i < 1_000_000; i++) {
    ps.setString(1, someLargeJson(i));
    ps.addBatch();
}
ps.executeBatch();

addBatch 将所有参数缓存在驱动内部的 ArrayList 中，每个参数都可能是大对象。100 万行，每行 JSON 平均 2KB，则驱动缓存占用了约 2GB 内存，直接导致客户端堆 OOM。同时，即使 OOM 未发生，这么大的事务一次性提交，PostgreSQL 需要在 WAL 中记录整个变更，导致 WAL 刷盘压力剧增，可能造成 IO 阻塞和数据库性能下降。

4.2.2 现象描述

• 应用堆内存迅速耗尽，频繁 Full GC，最终 OOM。
• 如果未 OOM，数据库端 WAL 写入量突发，pg_stat_bgwriter 中 buffers_backend_fsync 或 buffers_backend 计数飙升，磁盘 IO 打满。主备同步延迟增加。
• 长事务持续时间过长，阻塞 VACUUM，表上死元组累积。

4.2.3 排查思路

客户端 OOM：堆转储分析，发现 java.util.ArrayList 或 org.postgresql.jdbc.BatchResultHandler 等持有大量 byte[][] 或参数数组。

数据库端：监控 pg_stat_wal 或 pg_stat_bgwriter，wal_bytes 迅速增长。同时 pg_locks 中可能出现长时间持有的锁。

4.2.4 根因分析

未控制批次大小，一次性将所有参数加入批处理，导致驱动客户端内存暴增，同时单个事务过大给数据库带来沉重负担。

4.2.5 修正方案

分批提交，每批 5000 行（根据行宽调整）：

int batchSize = 5000;
int count = 0;
PreparedStatement ps = conn.prepareStatement("INSERT INTO events (payload) VALUES (?)");
for (int i = 0; i < items.size(); i++) {
    ps.setString(1, items.get(i).getPayload());
    ps.addBatch();
    if (++count % batchSize == 0) {
        ps.executeBatch();
        ps.clearBatch();
    }
}
ps.executeBatch(); // 提交剩余的

分批后，每批提交一个事务，释放内存并允许 VACUUM 正常进行。

4.2.6 最佳实践

1. 单批次大小建议 1000~10000 行，根据行大小调整。可以在测试环境不断调整找到吞吐最优的批次。
2. 结合 reWriteBatchedInserts=true 使用，重写后每批生成多条 VALUES 的 INSERT，进一步减少开销。
3. 考虑使用 PostgreSQL 的 COPY API 进行超大批量加载，它是最快的导入方式，且流式处理，占用内存恒定（参阅第 5 篇）。
4. 监控事务大小：设置 log_min_duration_statement 记录超过一定时间的语句，及时发现大事务。

案例 4.3：批大小过小导致网络往返过多，TPS 无法提升

4.3.1 错误示例

每 10 行执行一次 executeBatch()。

int batchSize = 10;
int count = 0;
for (Item item : items) {
    ps.setString(1, item.getName());
    ps.addBatch();
    if (++count % batchSize == 0) {
        ps.executeBatch();
        ps.clearBatch();
    }
}

4.3.2 现象描述

虽然开启了 reWriteBatchedInserts=true，但由于批次太小，网络往返次数仍然很多，TPS 无法随连接数和资源增加而线性提升。CPU 和网络都有剩余，但性能停滞。

4.3.3 排查思路

使用网络抓包工具（如 tcpdump）观察在批处理期间客户端和数据库之间的数据包交换频率，可以看出批次执行次数。

4.3.4 根因分析

每个 executeBatch() 都需要一次网络往返，即使重写成了多行 INSERT，单次往返的开销（包括提交、数据库内部处理）占有相当比例。批次大小与吞吐量关系呈倒 U 型，过小则往返开销大，过大则内存和 WAL 压力大。

4.3.5 修正方案

增大批次至 1000 ~ 5000 行，压测确定最佳点。

4.3.6 最佳实践

通过压测获得批次大小的 sweet spot，一般从 1000 起步，逐步加倍，直到 TPS 不再提高。

---

5. 连接池反模式

聚焦池化参数的配置与维护，区别于连接物理操作的反模式。以下案例深入 HikariCP 参数细节。

案例 5.1：minimumIdle 设置过高——闲置连接浪费数据库资源

5.1.1 错误示例

为追求零延迟获取连接，将 minimumIdle 设置为与 maximumPoolSize 相同，例如 30，并且 idleTimeout 设置为 0（永不回收）。

hikari:
  maximum-pool-size: 30
  minimum-idle: 30
  idle-timeout: 0

5.1.2 现象描述

在非高峰时段，数据库 pg_stat_activity 中仍有 30 个 idle 连接长期驻留。如果有 10 个服务实例，总计 300 个空闲连接，消耗数据库内存（约 3GB）。这些连接无法被其他应用或同一数据库的其他服务利用，造成资源浪费。云数据库按连接数收费时，成本更高。

5.1.3 排查思路

检查 HikariCP Metrics：

hikaricp_connections_idle = 30 （与 max 相同）

并且在长时间低流量下该值不变。

5.1.4 根因分析

minimumIdle 控制池尝试维持的最小空闲连接数。当设为与 max 相同且不回收时，这些连接永久保持，即使毫无流量。

5.1.5 修正方案

根据正常负载设置合理的 minimumIdle，如 5，并设置空闲超时回收多余连接：

hikari:
  maximum-pool-size: 30
  minimum-idle: 5
  idle-timeout: 600000   # 10分钟回收多余空闲连接

这样在流量低谷时只保留 5 个连接，高峰需要时池会动态创建。

5.1.6 最佳实践

1. minimumIdle 应设为常规负载所需的最小连接数，避免静态占用过多。
2. 必须将 idleTimeout 与 minimumIdle 结合使用，让池弹性伸缩。
3. 在多实例部署环境中，合理降低每个实例的 minimumIdle，因为总连接数 = 实例数 * 最小空闲。

案例 5.2：leakDetectionThreshold 误报/漏报——调整与日志分析

5.2.1 错误示例

误报场景：leakDetectionThreshold 设置为 2000ms（2秒），但业务存在一些慢查询（例如报表）响应时间在 3-5 秒。每次慢查询都被记录为连接泄漏，造成告警风暴，运维疲惫。

hikari:
  leak-detection-threshold: 2000

漏报场景：完全不设置 leakDetectionThreshold，真实的连接泄漏直到连接池耗尽才发现，被动。

5.2.2 现象描述

• 误报：日志中出现大量 “Connection leak detection triggered” 警告，开发人员查验后发现连接最终被归还，只是执行慢。干扰严重。
• 漏报：发生连接泄漏时，没有任何提示，直到应用报 Connection is not available。

5.2.3 排查思路

对比 HikariCP Metrics 的 ActiveConnections 变化趋势与 leak 日志的时间。如果 Active 在日志提示泄漏后几分钟内快速下降，则为慢查询，而非泄漏。反之，如果 Active 持续增加不下降，则真泄漏。

5.2.4 根因分析

泄漏检测的工作原理是：当连接被借出超过 leakDetectionThreshold 时，打印警告。如果业务本身存在正常的慢查询，这些查询会被误判。阈值需要大于业务查询的绝大部分响应时间。

5.2.5 修正方案

将 leakDetectionThreshold 设置为 p99 响应时间的 3 倍，例如 p99 为 5 秒，设为 15000ms（15秒）。如果业务响应时间跨度大，可适当放宽到 30 秒。

hikari:
  leak-detection-threshold: 30000

5.2.6 最佳实践

1. 生产环境必须开启泄漏检测，但要调优阈值，建议 15~60 秒。
2. 设置日志告警规则：当一段时间内泄漏告警次数超过一定数量时（如 5 分钟 3 次），触发告警。
3. 与 APM 工具集成：如 Spring Boot Actuator 暴露 hikaricp_connections_active，设置 active 持续高水位告警。

案例 5.3：连接验证查询高成本——SELECT 1 vs SELECT count(*) FROM large_table 的性能差异

5.3.1 错误示例

有开发者担心连接“假死”，便自定义验证查询为：

hikari:
  connection-test-query: "SELECT count(*) FROM audit_log "

audit_log 表有数亿行。每次连接借出时的验证（如果池配置了 testOnBorrow 等，HikariCP 实际上不需要connectionTestQuery，除非特殊配置）会执行一次昂贵的全表扫描，不仅慢，还消耗 IO。

5.3.2 现象描述

突然发现数据库慢查询激增，大量 SELECT count(*) FROM audit_log 在 pg_stat_activity 中出现，连接池获取连接延迟暴增，应用整体 RT 飙升。

5.3.3 排查思路

通过 pg_stat_statements 找出 count(*) FROM audit_log 的调用频率和时间。若发现其 calls 与连接获取相关，则是验证查询。

5.3.4 根因分析

连接验证应当是一个几乎无开销的操作。复杂的验证查询会拖慢整个连接获取流程，使得问题迅速放大。

5.3.5 修正方案

移除 connectionTestQuery，HikariCP 默认通过 java.sql.Connection.isValid() 检测，极其高效。如果坚持要明确验证，应使用 SELECT 1 或 SELECT 1 类似的轻量查询。

# 可以移除 connection-test-query
hikari:
  # connection-test-query:  /* 不要设置 */

5.3.6 最佳实践

1. 绝不要自定义高成本验证查询。
2. 信任连接池内置的 isValid() 机制，它能正确检测连接是否存活。
3. 若不得不使用验证查询，只能 SELECT 1。

---

6. 驱动配置反模式

覆盖 JDBC 驱动特有连接参数误配，与第 9 篇驱动内核呼应，本部分聚焦全局性错误。

案例 6.1：ApplicationName 缺失导致无法区分应用连接来源

6.1.1 错误示例

所有服务共用数据库，但在 JDBC URL 中未设置 ApplicationName。

jdbc:postgresql://pg-host:5432/mydb

所有来自不同微服务实例的连接在 pg_stat_activity 中 application_name 都显示默认空或 PostgreSQL JDBC Driver。

6.1.2 现象描述

当数据库出现性能问题，需要定位是哪个应用导致的连接数高或执行了慢查询时，却无法从 pg_stat_activity 直接区分。运维只能通过客户端 IP 猜测，效率低下。

6.1.3 排查思路

SELECT application_name, count(*), state FROM pg_stat_activity GROUP BY 1, 3;

如果 application_name 没有区分度，或全部相同，即为缺失。

6.1.4 根因分析

PG JDBC 驱动提供 ApplicationName 连接参数，可在建立连接时向数据库注册应用名。忽略此参数导致观测性缺失。

6.1.5 修正方案

为每个服务甚至每个实例配置独特的 ApplicationName。

jdbc:postgresql://pg-host:5432/mydb?ApplicationName=order-service-prod-1

在 Spring Boot 中可以通过占位符动态生成：

spring:
  datasource:
    hikari:
      data-source-properties:
        ApplicationName: ${spring.application.name}-${random.uuid}

或包含主机名。

6.1.6 最佳实践

1. 每个应用必须设置 ApplicationName，格式：服务名-环境-实例标识。
2. 在监控工具中可见，方便按应用聚合连接数和慢查询。

案例 6.2：defaultRowFetchSize 未设置导致流式查询退化为全量加载

与案例 2.1 呼应，但强调驱动全局参数的设置。若未设置全局 defaultRowFetchSize，开发者容易在个别查询中遗漏 setFetchSize，导致 OOM 风险。

修正方案：在 JDBC URL 添加：

jdbc:postgresql://host/db?defaultRowFetchSize=5000

但要配合事务使用。最佳实践：全局不设置，但对需要的大查询方法强制规范必须调用 setFetchSize，或者在基础库中封装。

案例 6.3：reWriteBatchedInserts 误配或连接超时/套接字超时混淆导致异常行为

6.3.1 错误示例

误配 1：开启了 reWriteBatchedInserts=true，但同时设置了 prepareThreshold=0（禁用服务端预编译）。重写需要预编译支持，可能不生效或性能不佳。

误配 2：socketTimeout 设置得太短，例如 10 秒。一个正常的大批量插入因数据量大，在网络传输和执行上耗时 15 秒，结果 Socket 读取超时，连接被中断。

jdbc:postgresql://host/db?reWriteBatchedInserts=true&socketTimeout=10&connectTimeout=5

误配 3：将 loginTimeout 和 connectTimeout 混淆，导致连接建立超时过短。

6.3.2 现象描述

• 批处理出现 java.net.SocketTimeoutException: Read timed out。
• 连接池创建连接时偶发 Connection timed out。
• 重写没达到预期加速效果。

6.3.3 排查思路

检查 URL 参数，与实际操作耗时对比。

• 通过 pg_stat_activity 观察批处理的实际持续时间，与 socketTimeout 对比。
• 开启驱动日志确认连接超时类型。

6.3.4 根因分析

各超时参数含义不同：

• connectTimeout：建立 TCP 连接的超时。
• socketTimeout：Socket 读取数据的超时，包括执行查询和获取结果。
• loginTimeout：用于 DriverManager 登录超时，连接池一般不使用。
• statement_timeout：数据库端控制单个语句执行超时。 如果 socketTimeout 小于一批 SQL 的处理时间 + 网络传输时间，就会中断成功执行的事务，导致连接异常。

6.3.5 修正方案

合理设置超时：

jdbc:postgresql://host/db?reWriteBatchedInserts=true&prepareThreshold=1&socketTimeout=600&connectTimeout=10&tcpKeepAlive=true

• socketTimeout 设为 600 秒（10分钟），足够长事务完成。
• 数据库端可额外设置 statement_timeout 作为最后防线。
• 不要混淆参数。

6.3.6 最佳实践

详细区分每个超时参数的作用，为不同场景合理配置。更多参数细节参见第 9 篇驱动内核。

六大反模式全景分类图：

flowchart LR
    subgraph 连接反模式
        A1[1.1 连接泄漏]
        A2[1.2 池大小失当]
        A3[1.3 生命周期冲突]
        A4[1.4 未池化直连]
    end
    subgraph SQL执行反模式
        B1[2.1 fetchSize导致OOM]
        B2[2.2 autoCommit长事务]
        B3[2.3 硬解析泛滥]
        B4[2.4 拼接注入]
    end
    subgraph 预编译反模式
        C1[3.1 prepareThreshold过高]
        C2[3.2 缓存重置失效]
        C3[3.3 动态SQL膨胀]
    end
    subgraph 批处理反模式
        D1[4.1 未开启rewrite]
        D2[4.2 批大小过大]
        D3[4.3 批大小过小]
    end
    subgraph 连接池反模式
        E1[5.1 minimumIdle过高]
        E2[5.2 leakDetection误报]
        E3[5.3 验证查询高成本]
    end
    subgraph 驱动配置反模式
        F1[6.1 ApplicationName缺失]
        F2[6.2 fetchSize全局]
        F3[6.3 超时混淆]
    end

说明：

• 此图概括了全文 20 个案例的全景分类，构建起排障的故障地图。
• 每个领域独立成块，边界清晰：连接反模式聚焦物理连接的生命周期管理，连接池反模式聚焦池化参数调优，驱动配置反模式则关注 JDBC 特有的连接参数。
• 读者遇到生产故障时，可先根据现象归类到领域，然后对照具体案例进行排查，无需大海捞针。

---

7. 故障模拟实验室（带预期结果校验）

本实验室提供两个完整的故障复现场景，所有组件均基于 Docker Compose 运行，读者可以在本地或测试环境快速搭建并亲手体验排查全过程。每个关键步骤都标注预期结果，使理论与实践无缝衔接。

7.1 实验环境搭建

7.1.1 整体架构

flowchart TD
    JMeter[Apache JMeter 压测节点] -->|HTTP| App[Spring Boot Lab App :8080]
    App -->|JDBC/HikariCP| PgSQL[(PostgreSQL 16)]
    Prometheus[Prometheus 监控] -->|pull| App
    Prometheus -->|pg_exporter| PgSQL
    Grafana[Grafana 可视化] --> Prometheus
    Arthas[Arthas 动态诊断] -.->|attach| App

• JMeter：模拟并发用户请求，触发故障场景。
• Spring Boot App：精心设计的故障端点，使用 HikariCP 连接池，暴露 Actuator 指标。
• PostgreSQL：开启 pg_stat_statements、pg_stat_activity 监控视图。
• Prometheus + Grafana：可选，用于高级监控。
• Arthas：无需重启即可动态追踪，排查内存泄漏、连接泄漏等。

7.1.2 Docker Compose 配置

version: '3.8'
services:
  postgres:
    image: postgres:16
    container_name: jdbc-lab-pg
    environment:
      POSTGRES_USER: lab
      POSTGRES_PASSWORD: lab123
      POSTGRES_DB: jdbclab
    ports:
      - "5432:5432"
    command: >
      postgres
      -c shared_preload_libraries='pg_stat_statements'
      -c pg_stat_statements.track=all
      -c log_statement='all'
      -c log_disconnections=on
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    networks:
      - lab-net

  app:
    build: ./spring-app
    container_name: jdbc-lab-app
    ports:
      - "8080:8080"
      - "9090:9090"  # JMX remote (便于 Arthas 连接)
    environment:
      SPRING_DATASOURCE_URL: jdbc:postgresql://postgres:5432/jdbclab?ApplicationName=lab-app
      SPRING_DATASOURCE_USERNAME: lab
      SPRING_DATASOURCE_PASSWORD: lab123
      JAVA_OPTS: "-Xmx256m -Xms256m -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/tmp/heapdump.hprof"
    depends_on:
      - postgres
    networks:
      - lab-net

networks:
  lab-net:

说明：

• PostgreSQL 启用了 pg_stat_statements 扩展和连接断开日志。
• 应用 JVM 堆设置为 256MB，便于复现 OOM，并自动生成堆转储。

初始化 SQL 脚本 init.sql：

-- 启用 pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 创建测试表，填充 100 万行数据
CREATE TABLE IF NOT EXISTS test_data (
    id SERIAL PRIMARY KEY,
    payload TEXT
);

-- 插入数据（如果之前没有）
INSERT INTO test_data (payload)
SELECT repeat('x', 200) FROM generate_series(1, 500000);

-- 创建用户
CREATE TABLE IF NOT EXISTS app_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    created_at TIMESTAMP DEFAULT now()
);

INSERT INTO app_users (username) VALUES ('user1'), ('user2'), ('user3');

Spring Boot 应用示例代码：

@SpringBootApplication
public class LabApplication {
    public static void main(String[] args) {
        SpringApplication.run(LabApplication.class, args);
    }
}

@RestController
public class LabController {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 连接泄漏端点：每次泄漏一个连接
    @GetMapping("/leak")
    public String leak() {
        try {
            Connection conn = dataSource.getConnection();
            Statement stmt = conn.createStatement();
            stmt.execute("SELECT 1");
            // 故意不关闭，泄漏
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "leaked one connection";
    }

    // 全量获取端点（导致 OOM）
    @GetMapping("/fetch-all")
    public List<Map<String, Object>> fetchAll() {
        return jdbcTemplate.queryForList("SELECT * FROM test_data");
    }

    // 正常测试端点
    @GetMapping("/test")
    public String test() {
        jdbcTemplate.queryForList("SELECT 1");
        return "ok";
    }
}

配置 application.yml：

spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 5000
      leak-detection-threshold: 10000
      idle-timeout: 300000
      max-lifetime: 600000
server:
  port: 8080

7.2 实验一：“服务过载导致 PG 连接耗尽”的完整复现

目标：模拟代码连接泄漏，导致连接池耗尽，新请求无法获取连接。

步骤：

1. 启动环境：docker-compose up -d，确认所有服务就绪。
2. 观察初始状态：进入 PG 容器查询连接数：

   docker exec -it jdbc-lab-pg psql -U lab -d jdbclab -c "SELECT count(*), state FROM pg_stat_activity WHERE application_name = 'lab-app' GROUP BY state;"
   

   预期结果：初始连接数较少，例如 idle 2-3 个。
3. 模拟轻微泄漏：使用 curl 或 JMeter 调用 /leak 端点 15 次。

   for i in {1..15}; do curl -s http://localhost:8080/leak; done
   

   预期结果：每次调用获取一个连接并未归还。检查数据库连接数：

   SELECT count(*), state FROM pg_stat_activity WHERE application_name = 'lab-app' GROUP BY state;
   

   应显示大约 10 个 idle 连接（连接池最大 10，已全部泄漏占用，这些连接在数据库显示为 idle，因为没有被 active 查询）。此时池中已无可用连接。
4. 触发获取超时：尝试调用 /test 端点，正常业务请求会排队等待连接，但连接永远不会归还，所以会超时。

   curl -s http://localhost:8080/test
   

   预期结果：等待 5 秒后返回 500 错误或抛出异常，应用日志出现 Connection is not available, request timed out after 5000ms。
5. 使用 Arthas 定位泄漏：在宿主机 attach 到应用容器：

   # 进入 app 容器或使用 Arthas Tunnel
   docker exec -it jdbc-lab-app java -jar /arthas/arthas-boot.jar
   

   在 Arthas 控制台执行：

   # 统计 getConnection 和 close 的调用次数
   monitor -c 10 com.zaxxer.hikari.pool.HikariPool getConnection
   monitor -c 10 java.sql.Connection close
   

   预期结果：getConnection 调用 15+ 次，close 调用 0 次，明显失衡。
6. 启用 HikariCP 泄漏检测日志观察堆栈：调整 application.yml 中 leak-detection-threshold: 5000 并重启。重复步骤 3，日志中会出现包含 LabController.leak 的堆栈信息。
7. 修复与验证：修改 /leak 端点使用 try-with-resources，重启应用，再次调用 /leak 多次，然后调用 /test，应该都能成功，数据库连接数保持稳定。

监控截图及关键命令输出示例：

• pg_stat_activity 检查：

   state | count 
  -------+-------
   idle  |    10
   active|     1
  

• HikariPool 日志：

  HikariPool-1 - Connection is not available, request timed out after 5000ms.
  

• Arthas monitor 命令输出：

  timestamp            class              method         total  success  fail  rt    
  2025-05-13 11:30:00  HikariPool         getConnection  15     15       0     1.2ms
  2025-05-13 11:30:00  Connection         close          0      0        0     0ms
  

7.3 实验二：“大字段查询导致应用 OOM”的完整复现

目标：查询未设置 fetchSize，结果集过大导致堆溢出，并通过 MAT 分析定位。

步骤：

1. 保持环境启动，确认 test_data 表有 50 万行，每行约 200 字节，总数据量约 100 MB。
2. 触发 OOM：使用 JMeter 并发 5 线程，循环调用 /fetch-all 端点 10 次。JVM 堆只有 256MB，很快 OOM。 JMeter 线程组配置：线程数 5，循环 10 次，HTTP 请求指向 http://app:8080/fetch-all。
3. 观察现象：应用日志出现 java.lang.OutOfMemoryError: Java heap space。容器可能重启（如果配置了 -XX:+ExitOnOutOfMemoryError），但我们的配置未退出。
4. 生成堆转储：JVM 参数已配置，在应用容器内 /tmp/heapdump.hprof 生成。复制到宿主机：

   docker cp jdbc-lab-app:/tmp/heapdump.hprof ./heapdump.hprof
   

5. MAT 分析：用 Eclipse Memory Analyzer 打开 heapdump，运行 “Leak Suspects Report”。会看到一个巨大的 java.util.ArrayList 实例，内部元素是 HashMap（对应 queryForList 返回的列表），总计 50 万个元素，每个元素包含 payload 字段。GC Root 路径追溯到 PgResultSet 内部缓存的 rows 数组。
6. 优化并验证：修改查询，使用 fetchSize：

   @GetMapping("/fetch-all-safe")
   public void fetchAllSafe(HttpServletResponse response) throws Exception {
       try (Connection conn = dataSource.getConnection()) {
           conn.setAutoCommit(false);
           PreparedStatement ps = conn.prepareStatement("SELECT * FROM test_data");
           ps.setFetchSize(5000);
           ResultSet rs = ps.executeQuery();
           response.setContentType("text/plain");
           PrintWriter out = response.getWriter();
           while (rs.next()) {
               out.println(rs.getString("payload"));
           }
           out.close();
           rs.close();
           ps.close();
           conn.commit();
       }
   }
   

   重新压测，堆内存平稳，不再 OOM，且响应流式返回，内存消耗在预期范围内。

预期结果：

• 未优化前，JMeter 报错率 100%，堆内存达到 256MB 上限。
• MAT 分析确认大对象为全量 ResultSet 缓存。
• 修复后，同样 5 并发，内存稳定在 100MB 左右，请求全部成功。

---

8. 诊断工具集与工具→现象映射表

8.1 三层工具速查

• 数据库层：pg_stat_activity（连接与事务状态）、pg_stat_statements（SQL 统计与解析时间）、pg_prepared_statements（预编译缓存）、pg_locks（锁等待）、pg_stat_user_tables（死元组）、log_min_duration_statement 日志。
• 连接池层：HikariCP Metrics（active/idle/pending 指标）、HikariCP 泄漏检测日志、Druid SQL 监控面板及 WallFilter 拦截历史。
• 应用层：Arthas（watch、trace、monitor、vmoption）、JFR/Async Profiler（CPU/内存火焰图）、堆转储分析工具（Eclipse MAT）、JMeter（压测模拟与基线）、网络抓包工具（Wireshark/tcpdump）。

诊断工具三层全景图：

flowchart TB
    subgraph DB[数据库层]
        DB1[pg_stat_activity 连接状态]
        DB2[pg_stat_statements SQL解析]
        DB3[pg_prepared_statements 缓存]
        DB4[pg_locks 锁]
        DB5[日志 log_disconnections/log_statement]
    end
    subgraph Pool[连接池层]
        Pool1[HikariCP Metrics 指标]
        Pool2[leakDetection 泄漏日志]
        Pool3[Druid SQL Monitor / WallFilter]
    end
    subgraph App[应用层]
        App1[Arthas 动态追踪]
        App2[JFR/Profiler 性能剖析]
        App3[Heap Dump / MAT 内存分析]
        App4[JMeter 压测]
        App5[网络抓包]
    end
    DB --> Pool --> App

说明：

• 三层划分清晰，排查时按需从上到下或从下到上。
• 数据库层呈现最终的物理状态和统计，是最真实的证据。
• 连接池层揭示连接获取/释放的动态和泄漏。
• 应用层则深入代码执行细节和资源占用。
• 下面映射表提供按现象索骥的快速检索。

8.2 工具→反模式现象映射表

典型现象	推荐工具	关键检查命令/指标	常见根因
应用无法获取连接，报 timeout	pg_stat_activity, HikariCP Metrics	SELECT count(*) FROM pg_stat_activity WHERE application_name='x' AND state='idle' 大于 max, hikaricp_connections_active 长时间等于 max	连接泄漏（案例1.1）、池过小（案例1.2）、慢查询占用连接（案例2.2）
数据库连接数突增且接近 max_connections	pg_stat_activity	SELECT count(*) FROM pg_stat_activity; 超过 80%	连接池过大（案例1.2）、未设置 maxLifetime 导致僵尸连接（案例1.3）
查询响应时间突然变长，CPU 高	pg_stat_statements	SELECT query, calls, mean_plan_time, mean_exec_time FROM pg_stat_statements WHERE mean_plan_time > 1 ORDER BY total_plan_time DESC;	硬解析泛滥（案例2.3）、预编译失效（案例3.1/3.2）
应用偶发 OOM，流量无突增	jmap / MAT, Arthas	堆转储分析 ResultSet 或 RowData 大对象，ps.getFetchSize() 监控	fetchSize 未设置导致全量拉取（案例2.1、6.2）
批处理导入极慢，TPS 远低于预期	pg_stat_statements, 抓包工具	检查 reWriteBatchedInserts 配置，SQL 日志中是否有单行多次 INSERT	未开启 rewrite（案例4.1），批次过小（案例4.3）
批处理时应用 OOM 或数据库 WAL 暴涨	HikariCP Metrics, pg_stat_wal	监控堆内存，WAL bytes 增速	批大小过大（案例4.2）
idle in transaction 连接堆积，表膨胀	pg_stat_activity, pg_stat_user_tables	SELECT pid, now() - xact_start AS age FROM pg_stat_activity WHERE state = 'idle in transaction'; n_dead_tup 持续增长	长事务未提交（案例2.2）
预编译缓存频繁清空	pg_prepared_statements	SELECT count(*) FROM pg_prepared_statements; 出现从有到无	连接池执行 DISCARD ALL 重置（案例3.2）
Druid 拦截正常 SQL	Druid Monitor, WallFilter 日志	查看拦截记录，是否为参数化不当	WallFilter 规则过严（案例2.4）
应用连接获取慢，数据库出现大批 SELECT count(*)	pg_stat_activity	观察 query 列	连接验证查询代价高（案例5.3）
多应用共用数据库但无法区分来源	pg_stat_activity	SELECT application_name, count(*) FROM pg_stat_activity GROUP BY 1; 缺乏区分	ApplicationName 未设置（案例6.1）
执行长批量更新或查询时，抛 SocketTimeoutException	驱动日志, Arthas	对比操作耗时与 socketTimeout 配置	套接字超时混淆（案例6.3）
连接泄漏告警频繁但无实际影响	HikariCP 泄漏日志, Metrics	泄漏日志时间与 Active 连接回落时间对比	leakDetectionThreshold 阈值过低（案例5.2）
minimumIdle 过高耗尽数据库连接	HikariCP Metrics, pg_stat_activity	hikaricp_connections_idle 恒为高值，pg_stat_activity 大量 idle	minimumIdle 配置不当（案例5.1）
动态 SQL 导致 out of shared memory	pg_prepared_statements	SELECT count(*) FROM pg_prepared_statements; 过大	动态 SQL 变体过多，缓存膨胀（案例3.3）

---

9. 标准化排查决策树（五条主干）

当面对一个未知的 JDBC 故障，可以遵循以下决策树快速定位。树中每个分支节点都包含判断条件、参照案例和具体的排查命令，可直接打印为应急卡片。

标准化排查决策树总图：

flowchart TD
    Start["遇到 JDBC 故障，现象是什么？"] --> Q1{"无法获取连接 / 超时?"}
    Q1 -->|"是"| A1["查连接池 Active/Idle/Pending 指标"]
    Q1 -->|"否"| Q2{"查询响应时间突然变慢?"}
    Q2 -->|"是"| B1["查 pg_stat_statements 观察 plan/exec 时间比例"]
    Q2 -->|"否"| Q3{"应用偶发 OOM?"}
    Q3 -->|"是"| C1["生成堆转储，用 MAT 分析大对象"]
    Q3 -->|"否"| Q4{"批处理吞吐持续低下?"}
    Q4 -->|"是"| D1["检查 JDBC URL 的 rewrite 参数 和 batch size"]
    Q4 -->|"否"| Q5{"预编译看起来未生效?"}
    Q5 -->|"是"| E1["查 pg_prepared_statements 条目"]

    A1 --> A2{"Active 接近 max 且 Pending 高?"}
    A2 -->|"是"| A3{"Idle 是否也接近 max?"}
    A3 -->|"是"| A4["疑似连接泄漏：启用 leakDetection + Arthas 对比 get/close 次数，到案例 1.1"]
    A3 -->|"否"| A5["池大小不足或慢 SQL 占用：扩容或优化 SQL，案例 1.2/2.2"]
    A2 -->|"否"| A6{"Idle 不高但获取慢?"}
    A6 -->|"是"| A7["检查验证查询成本或网络，案例 5.3/6.3"]

    B1 --> B2{"mean_plan_time 占比高?"}
    B2 -->|"是"| B3["硬解析问题：检查 prepareThreshold 及连接池重置，案例 2.3, 3.1, 3.2"]
    B2 -->|"否"| B4["锁争用或 IO？查 pg_locks 和 pg_stat_user_tables，案例 2.2"]

    C1 --> C2{"大对象是否为 ResultSet 相关?"}
    C2 -->|"是"| C3["设置 fetchSize 或 defaultRowFetchSize，案例 2.1, 6.2"]
    C2 -->|"否"| C4["业务对象累积，检查代码逻辑"]

    D1 --> D2{"reWriteBatchedInserts 是否开启?"}
    D2 -->|"否"| D3["开启并设置 prepareThreshold=1，案例 4.1"]
    D2 -->|"是"| D4["检查 batch size：过大导致 OOM/WAL，过小导致 RTT 多，案例 4.2/4.3"]

    E1 --> E2{"pg_prepared_statements 为空或很少?"}
    E2 -->|"是"| E3["降低 prepareThreshold 或检查 DISCARD ALL，案例 3.1/3.2"]
    E2 -->|"否"| E4{"缓存数量是否过多?"}
    E4 -->|"是"| E5["减少动态 SQL 或手动 DEALLOCATE，案例 3.3"]
    E4 -->|"否"| E6["可能正常，但检查连接池回收是否导致周期失效"]

    classDef decision fill:#fff4e6,stroke:#ff9800,stroke-width:2px,color:#333;
    classDef process fill:#f8f9fa,stroke:#333,stroke-width:1px,color:#333;
    class Start,A1,A4,A5,A7,B1,B3,B4,C1,C3,C4,D1,D3,D4,E1,E3,E5,E6 process;
    class Q1,Q2,Q3,Q4,Q5,A2,A3,A6,B2,C2,D2,E2,E4 decision;

图说明：

• 主干基于五大经典现象进行第一层分类，随后逐层细化。
• 每个叶子节点均指向对应的反模式案例和解决工具，实现“现象 → 分支判断 → 具体案例”的快速跳转。
• 分支间存在交叉：例如 OOM 也可能是连接泄漏导致连接持有大对象，可从 C1 转向 A 分支。
• 使用此图时，先根据最显著的异常现象选择入口，然后按照条件判断向下走，执行蓝色方框中的检查命令。
• 建议团队将本文档和决策树打印为墙贴，在紧急故障时集体参照。

---

10. 面试高频故障排查专题

本文面试题聚焦故障排查场景，与前 9 篇侧重原理与架构的面试题形成互补，建议对照复习以构建完整的 JDBC 知识体系。以下 16 道题均源自真实线上的 JDBC 故障，每道题不仅给出答案，更提供可直接执行的排查命令、监控输出解读和修复步骤，帮助读者在面试中展现扎实的实战能力。

10.1 线上突然出现大量 SQLException: Connection is not available 的根因排查

场景描述
一个平稳运行的订单微服务，上午 10 点流量无明显尖峰，但日志中突然开始批量出现 java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms。随后服务健康检查失败，Pod 被重启多次，但问题复现。数据库为 PostgreSQL 16，连接池使用 HikariCP 5.x，配置 maximumPoolSize=20。

故障现象

• 应用日志大量连接超时异常，堆栈指向 HikariPool.getConnection() 阻塞。
• HikariCP Metrics 显示 hikaricp_connections_active 持续为 20，hikaricp_connections_idle 为 0，hikaricp_connections_pending 不断上升至数十。
• 数据库 pg_stat_activity 查询显示该应用连接数达到 20，且全部处于 idle 状态，state_change 时间戳很早，说明长时间没有活动。
• 数据库整体连接数并未触及 max_connections，但应用已无法获取新连接。

排查思路引导

1. 确认池内连接状态
   通过 Spring Boot Actuator 或 JMX 查看 HikariCP 运行时指标：

   curl http://localhost:8080/actuator/metrics/hikaricp.connections.active
   curl http://localhost:8080/actuator/metrics/hikaricp.connections.idle
   curl http://localhost:8080/actuator/metrics/hikaricp.connections.pending
   

   若 active=20，idle=0，pending>0，表明池中所有连接均被“借出”未归还。

2. 检查数据库端连接真实状态
   进入数据库执行：

   SELECT pid, state, query, now() - state_change AS idle_duration
   FROM pg_stat_activity 
   WHERE application_name = 'your-app-name';
   

   若所有连接均为 idle，且 idle_duration 远超正常业务执行时间（例如超过 10 分钟），说明连接已被应用取走但未执行任何操作——最可能的原因是连接泄漏。

3. 开启连接池泄漏检测，获取泄漏堆栈
   临时修改 HikariCP 配置（或通过配置中心动态下发），开启 leak-detection-threshold 并设置一个较短的阈值（如 10000 毫秒）：

   spring.datasource.hikari.leak-detection-threshold: 10000
   

   重启应用后，日志中会出现类似以下警告：

   WARN  com.zaxxer.hikari.pool.ProxyLeakTask : Connection leak detection triggered
   for connection conn 12345 on thread http-nio-8080-exec-3, stack trace follows:
   java.lang.Exception: Apparent connection leak detected
       at com.example.service.OrderService.processOrder(OrderService.java:57)
       ...
   

   堆栈直接指出了哪个方法借走了连接但未归还。

4. 无重启动态追踪
   如果无法重启，使用 Arthas 监控 getConnection 和 close 方法的调用次数：

   monitor -c 10 com.zaxxer.hikari.pool.HikariPool getConnection
   monitor -c 10 java.sql.Connection close
   

   正常情况下，getConnection 和 close 的次数应接近。若发现 getConnection 累积次数远大于 close，即可证实泄漏。

5. 进一步定位泄漏点
   使用 Arthas 的 watch 命令观察 getConnection 的调用者：

   watch com.zaxxer.hikari.HikariDataSource getConnection '{params, returnObj, @java.lang.Thread@currentThread().getStackTrace()}' -x 3 -n 50
   

   从输出的堆栈中分析哪些业务代码获取连接后没有在 finally 中关闭。

根因与修复方案
根因：业务代码中存在连接未关闭的异常路径。典型错误是在 try 块中获取连接，但在发生 RuntimeException 时跳过了 finally 中的 conn.close()。例如：

public void processOrder(int orderId) {
    Connection conn = dataSource.getConnection();
    PreparedStatement ps = conn.prepareStatement("SELECT ...");
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        if (rs.getString("status").equals("CANCELLED")) {
            throw new RuntimeException("Order cancelled"); // 连接泄漏
        }
    }
    rs.close(); ps.close(); conn.close();
}

连接池将 conn.close() 视为归还连接，遗漏该调用即造成连接永不归还，池中可用连接逐渐耗尽。

修复：统一使用 try-with-resources 确保所有路径释放资源：

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT ...");
     ResultSet rs = ps.executeQuery()) {
    // 处理逻辑
} catch (SQLException e) {
    // 异常处理
}

同时保留连接池参数作为兜底：设置 max-lifetime 确保即使泄漏，连接也会被池强行废弃回收。

最佳实践

• 强制代码规范：使用 try-with-resources，并通过 SonarQube 规则 squid:S2095 检查。
• 生产环境开启 leak-detection-threshold，阈值设为 p99 响应时间的 3 倍（通常 30~60 秒）。
• 设置合理的 max-lifetime 和 idle-timeout，避免僵死连接长期占用。
• 监控 hikaricp_connections_pending，一旦持续 >0 就告警排查。

---

10.2 慢查询数量突增，但 SQL 本身未变，如何分析？

场景描述
DBA 反馈某核心业务库的 pg_stat_statements 显示，几个高频查询的 mean_exec_time 从上周的 2ms 突增到 20ms，且 total_plan_time 大幅增长。应用端并未发版，SQL 文本完全相同，数据库统计信息也正常。连接池使用 HikariCP，驱动为 PG JDBC 42.7.x。

故障现象

• 应用平均响应时间上升约 15%。
• pg_stat_statements 中受影响查询的 mean_plan_time 由 0.05ms 升至 1.2ms，calls 分布极散，出现大量新的 queryid。
• pg_prepared_statements 视图条目数在问题时段骤降为 0。

排查思路引导

1. 确认硬解析现象
   查询 pg_stat_statements 对比历史：

   SELECT queryid, query, calls, total_plan_time, mean_plan_time, mean_exec_time
   FROM pg_stat_statements
   WHERE query LIKE '%your_sql_pattern%'
   ORDER BY total_plan_time DESC
   LIMIT 10;
   

   若同一个 SQL 模板出现多个 queryid，且每个 queryid 的 calls 很低（例如 1-5 次），说明每次执行都经历了硬解析。

2. 检查预编译缓存状态

   SELECT name, statement, prepare_time FROM pg_prepared_statements;
   

   如果结果为空，而该应用理应使用预编译，则缓存已丢失。

3. 排查连接池重连策略
   HikariCP 默认不执行 DISCARD ALL，但需检查是否人为配置了 connectionInitSql 或 connectionTestQuery：

   grep -r "connection-init-sql\|connection-test-query" application.yml
   

   若发现有类似 connection-init-sql: DISCARD ALL，正是元凶。DISCARD ALL 会清除会话中所有预编译语句、临时表、序列状态等，导致每次连接归还再借出后，服务端预编译缓存被清空。

4. 核实驱动 prepareThreshold 设置
   PG JDBC 驱动默认 prepareThreshold=5，即同一 PreparedStatement 对象需执行 5 次才会向服务器发送 Parse 消息。如果业务代码每次仅执行 1-2 次就关闭 Statement，或因为连接重置导致对象计数器清 0，则永远不会触发服务端预编译。可通过 JDBC URL 参数检查并调整。

5. 动态监控 Parse 消息
   有条件可开启驱动日志 &loggerLevel=TRACE，查看是否频繁出现 Parse: 消息；或使用 Arthas 监控 PgPreparedStatement.execute 内部调用，结合 monitor 统计 SQL 执行次数与 Statement 创建次数的比例。

根因与修复方案
根因：连接池在每次回收连接时执行了 DISCARD ALL（可能来自错误的配置模板），导致 PostgreSQL 服务端预编译缓存被清空，应用每次执行查询都需要重新硬解析。同时 prepareThreshold 较高，进一步减少了触发预编译的机会，形成“硬解析风暴”。

修复方案

1. 移除所有包含 DISCARD ALL 的初始化 SQL，确保连接池不做破坏性重置。HikariCP 的默认行为已经足够安全。
2. 在 JDBC URL 上显式设置 prepareThreshold=1，使第一次执行就生成服务端预编译语句：

   jdbc:postgresql://host/db?ApplicationName=myapp&prepareThreshold=1
   

3. 重启应用，验证 pg_prepared_statements 恢复条目并稳定增长，pg_stat_statements 的 total_plan_time 回落。

最佳实践

• 统一 OLTP 系统设置 prepareThreshold=1。
• 监控 pg_prepared_statements 的条目数和 total_plan_time，一旦异常清零立即告警。
• 避免在连接池中执行 DISCARD ALL 或其他有副作用的重置命令。

---

10.3 应用定期 OOM，但流量无明显突增，如何定位？

场景描述
某报表服务每天凌晨 2 点触发一个定时任务，导出全量交易记录为 CSV。近几天任务执行时应用容器频繁 OOM 重启，但观察白天流量正常，业务查询并未引起内存问题。JVM 堆内存设置为 -Xmx512m。

故障现象

• 应用日志出现 java.lang.OutOfMemoryError: Java heap space，堆转储文件生成。
• 该任务执行期间，垃圾回收日志显示频繁 Full GC，老年代持续增长无法回收。
• pg_stat_activity 显示一条长时间运行的查询：SELECT * FROM transactions，状态为 active，wait_event_type 为 ClientWrite（说明数据库在等待将大量数据发送给客户端）。
• pg_stat_statements 中该查询的 rows 数等于全表行数（2000 万行），单行大小约 1KB。

排查思路引导

1. 分析 OOM 时刻的堆转储
   使用 Eclipse MAT 打开 heapdump.hprof，运行 “Leak Suspects” 报告。若发现一个巨大的 ArrayList 占用了大部分堆，其内部元素为 java.util.HashMap（对应 JdbcTemplate.queryForList 的返回结果），且 GC Root 链条末端为 org.postgresql.jdbc.PgResultSet，即可确定是结果集全量缓存导致。

2. 检查数据量级

   SELECT count(*) FROM transactions;
   

   确认返回行数巨大。

3. 验证驱动默认行为
   通过 Arthas 或驱动日志查看 Statement.getFetchSize() 返回值：

   watch org.postgresql.jdbc.PgStatement getFetchSize '{returnObj}' -x 2
   

   若返回 0，表示驱动一次性拉取全部结果。

4. 审查代码
   定时任务通常使用 jdbcTemplate.queryForList("SELECT * FROM transactions")，该方法没有设置 fetchSize，PG JDBC 默认将整个查询结果集加载到 JVM 堆中。

根因与修复方案
根因：未设置 fetchSize，导致驱动采用“一次性全部获取”模式，将千万行数据全部缓存在客户端内存中，远超 JVM 堆容量。

修复方案
应用游标分批获取：

public void exportTransactions(OutputStream out) throws Exception {
    try (Connection conn = dataSource.getConnection()) {
        conn.setAutoCommit(false); // 游标必须在事务中
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM transactions");
        ps.setFetchSize(5000);      // 每次 fetch 5000 行
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String row = transformToCsvLine(rs);
            out.write(row.getBytes(StandardCharsets.UTF_8));
        }
        conn.commit();
    }
}

或者全局配置 defaultRowFetchSize（但要确保查询处在事务中）。

最佳实践

• 任何可能返回超过 1000 行的查询，必须评估并设置合适的 fetchSize。
• 定时任务、大数据量导出应使用游标或数据库 COPY 命令，配合流式处理。
• 避免在生产代码中使用 queryForList 返回海量数据，应使用 RowCallbackHandler 或 ResultSetExtractor 逐行处理。

---

10.4 批处理任务凌晨执行缓慢，从 JDBC 层如何优化？

场景描述
财务系统每天凌晨 3 点需要导入约 100 万条流水记录到 PostgreSQL。任务采用 JDBC batch 方式，伪代码如下：

PreparedStatement ps = conn.prepareStatement("INSERT INTO journal (account, amount, tx_time) VALUES (?, ?, ?)");
for (Journal j : list) {
    ps.setString(1, j.getAccount());
    ps.setBigDecimal(2, j.getAmount());
    ps.setTimestamp(3, j.getTxTime());
    ps.addBatch();
}
ps.executeBatch();

任务运行耗时 45 分钟，远低于业务预期。应用服务器和数据库资源使用率均不高。

故障现象

• 批量插入 TPS 约 300，网络带宽消耗大，查看网卡流量基本打满。
• pg_stat_statements 中该 INSERT 语句的 calls 恰好为 100 万，rows 也为 100 万，说明每条 INSERT 单独处理。
• 数据库日志 log_statement=all 显示有 100 万条独立的 INSERT INTO journal ... VALUES (...)，而不是多行的 INSERT INTO ... VALUES (...), (...)... 形式。

排查思路引导

1. 检查 JDBC URL 是否开启批量重写
   查看 DataSource 配置，JDBC URL 应为：

   jdbc:postgresql://host/db?reWriteBatchedInserts=true
   

   若缺少此参数，驱动默认不会将批处理重写为多行 INSERT。

2. 验证重写是否生效
   临时开启驱动日志 &loggerLevel=TRACE，调用 executeBatch() 时，驱动应输出类似 Rewrote batch of 1000 inserts into multi-valued insert 的日志。如果没有，说明未重写。

3. 检查 prepareThreshold 配合
   reWriteBatchedInserts 需要预编译支持，若 prepareThreshold=0（禁用服务端预编译）或默认 5 但 Statement 对象只创建一次且执行一次，可能影响重写。建议设置 prepareThreshold=1。

4. 评估批次大小
   当前代码将所有 100 万条堆积成一个巨大的 batch，可能带来内存压力和单一事务过大风险。但吞吐问题主要是未重写导致的网络往返爆炸。

根因与修复方案
根因：未启用 reWriteBatchedInserts，导致每个 addBatch 条目都作为独立的 INSERT 语句发送，网络报文数量巨大，给网络带宽和数据库解析带来沉重负担。

修复方案

1. 修改 JDBC URL 增加 reWriteBatchedInserts=true：

   jdbc:postgresql://host/db?reWriteBatchedInserts=true&prepareThreshold=1
   

2. 代码层面分批次提交，每 5000 行执行一次 executeBatch()，避免单个事务过大：

   int batchSize = 5000;
   int count = 0;
   for (Journal j : list) {
       ps.setString(1, j.getAccount());
       ps.addBatch();
       if (++count % batchSize == 0) {
           ps.executeBatch();
           ps.clearBatch();
       }
   }
   ps.executeBatch();
   

重新执行后，导入时间缩短至 3 分钟以内，TPS 提升至 5000+。

最佳实践

• 任何批量 INSERT 操作，务必设置 reWriteBatchedInserts=true。
• 合理分批（1000~10000 行），权衡内存、事务大小和网络往返。
• 对于超大数据量加载，优先考虑 COPY API，它是批量导入最快的方式。

---

10.5 预编译本应生效但 pg_stat_statements 显示大量硬解析，为什么？

场景描述
团队已经将所有 SQL 改用 PreparedStatement 并上线，但 DBA 发现 pg_stat_statements 中仍然存在大量 queryid 不同但模版相同的查询，mean_plan_time 较高。连接池为 HikariCP，配置了 prepareThreshold=5（默认）。

故障现象

• pg_stat_statements 中同一模版存在几十个不同的 queryid，calls 多为 1~2。
• pg_prepared_statements 视图中条目数极少（例如 <5），但理论上应该有几十条。
• 数据库 CPU 的 sys 时间较高。

排查思路引导

1. 分析应用代码模式
   审查代码发现，虽然用了 PreparedStatement，但每次查询都创建新对象并在循环内只执行一次：

   for (String id : ids) {
       PreparedStatement ps = conn.prepareStatement("SELECT * FROM items WHERE id = ?");
       ps.setString(1, id);
       ps.executeQuery();
       ps.close();
   }
   

   每个 PreparedStatement 对象的执行计数器独立，且只计为 1 次，未达到默认的 prepareThreshold=5，因此驱动始终使用简单查询协议，不发送单独的 Parse 消息进行服务端预编译。

2. 验证驱动行为
   通过 JMX 或 Arthas 观察 getFetchSize() 或开启驱动日志，确认没有 Parse 消息发出。

3. 检查连接池稳定性
   如果连接频繁创建销毁（例如 maxLifetime 设置过短），预编译缓存也会丢失。HikariCP 通过长连接复用避免此问题，但若配置了短 maxLifetime 或 idleTimeout 会导致缓存丢失。

根因与修复方案
根因：PreparedStatement 对象被频繁创建和销毁，每个对象只执行一次，无法达到 prepareThreshold 的触发阈值；加上连接复用度不够，偶尔的重连也会清除缓存，导致预编译机制形同虚设。

修复方案

1. 降低 prepareThreshold 为 1，确保即使每次新建对象，第一次执行就会发送 Parse：

   jdbc:postgresql://host/db?prepareThreshold=1
   

2. 业务代码优化：在循环外创建 PreparedStatement，循环内复用：

   PreparedStatement ps = conn.prepareStatement("SELECT * FROM items WHERE id = ?");
   for (String id : ids) {
       ps.setString(1, id);
       ResultSet rs = ps.executeQuery();
       // 处理结果...
       rs.close();
       ps.clearParameters();
   }
   ps.close();
   

3. 确保连接池配置合理，避免频繁建连断连，保持缓存长连接。

最佳实践

• 对于 OLTP 系统，建议全局设置 prepareThreshold=1。
• 重用 PreparedStatement 对象，特别是在循环内。
• 监控 pg_prepared_statements 和 total_plan_time，确保预编译生效。

---

10.6 连接池泄漏导致数据库连接数满，如何紧急恢复？

场景描述
生产环境监控突然告警：数据库 max_connections 仅剩个位数，多个应用服务开始报 too many connections 或 remaining connection slots are reserved。初步查看 pg_stat_activity，发现某个应用（application_name=order-service）的连接多达 150 个（配置 max 为 20，但实例数 8 个，总数应 160，数据库连接上限 200，其他服务共享）。这些连接大多处于 idle 状态且 state_change 时间久远。

故障现象

• 数据库连接数逼近上限，新连接无法建立。
• 订单服务大量请求报 Connection is not available。
• 其他服务也受到波及，开始出现获取连接失败。
• 业务熔断器打开。

排查思路与紧急恢复步骤

1. 快速缓解：释放僵死连接
   在数据库端查询并终止长期 idle 的连接（注意：不要杀死正在活跃的事务）：

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE application_name = 'order-service'
     AND state = 'idle'
     AND now() - state_change > interval '5 minutes';
   

   每执行一次，连接数会下降，为其他服务腾出空间。

2. 临时扩容数据库连接（如果允许）

   ALTER SYSTEM SET max_connections = 300;
   SELECT pg_reload_conf();
   

   但根本问题未解决，只能争取时间。

3. 定位泄漏应用实例
   查看各 order-service 实例的连接分布，找到连接数远超配置（>20）的异常实例，可能是连接泄漏的源头。重启该实例可以立即释放所有其占用的连接，恢复正常。

4. 开启泄漏检测准备故障复盘
   在问题实例重启前，如果能进入容器，快速打开 Arthas 执行：

   monitor -c 2 com.zaxxer.hikari.pool.HikariPool getConnection
   monitor -c 2 java.sql.Connection close
   

   收集调用次数差异。同时导出线程堆栈和 JFR 数据，供后续分析。

根因分析
依旧是连接泄漏，可能由某段新上线的代码或边界条件触发。连接池本应限制每个实例最多 20 个连接，但泄漏的连接未关闭，导致池内所有连接外借，而数据库侧永远显示这些连接处于 idle，因为应用虽然持有但未执行查询。长时间后池耗尽，所有请求排队超时。

修复方案

1. 重启异常实例，恢复服务。
2. 根据泄漏检测日志或 Arthas 记录找到泄漏代码，修改为 try-with-resources。
3. 设置连接池的 max-lifetime 和 leak-detection-threshold 作为双重防护。

最佳实践

• 建立“连接数满”应急预案，包括数据库紧急杀连接、重启异常实例、临时提高 max_connections。
• 连接池必须配置 leakDetectionThreshold 和合理的生命周期，防止泄漏雪崩。
• 应用部署时各实例的 maximumPoolSize 总和应小于数据库 max_connections 减去预留。

---

10.7 idle in transaction 连接堆积的排查方法

场景描述
数据库管理员发现 pg_stat_activity 中有数十个连接处于 idle in transaction 状态，持续超过 15 分钟，且 orders 表和 inventory 表出现明显膨胀（n_dead_tup 持续增长）。业务无明显报错，但数据库磁盘使用率上升，查询性能下滑。

故障现象

• pg_stat_activity 中 state = 'idle in transaction'，xact_start 时间戳很早。
• pg_stat_user_tables 中相关表的 n_dead_tup 不断增加，last_autovacuum 滞后。
• pg_locks 中有一些事务持有着 RowExclusiveLock 或 ShareLock 未释放。

排查思路引导

1. 定位长事务的源头

   SELECT pid, usename, application_name, now() - xact_start AS txn_age,
          now() - state_change AS idle_age, query
   FROM pg_stat_activity
   WHERE state = 'idle in transaction'
   ORDER BY txn_age DESC;
   

   找到 query 列，它显示事务中执行的最后一条 SQL，例如 UPDATE orders SET status = 'PROCESSING' WHERE id = $1。application_name 指示来自哪个微服务。

2. 追溯应用端代码
   在对应的微服务实例上，利用 Arthas 查找当前正在执行类似 SQL 的线程：

   trace com.example.service.OrderService processOrder -n 5
   

   可能会发现某个方法内部调用了远程支付 API，且该 API 响应超时（30s），而整个方法标记了 @Transactional，事务一直挂起等待外部调用返回。

3. 查看锁信息

   SELECT l.pid, l.relation::regclass, l.mode, l.granted
   FROM pg_locks l
   JOIN pg_stat_activity a ON l.pid = a.pid
   WHERE a.state = 'idle in transaction'
     AND NOT l.granted;
   

   如果出现未授权的锁，说明该长事务阻塞了其他操作。

4. 数据库侧强制终止
   若 idle_in_transaction_session_timeout 已设置（例如 5min），但此处事务总在闲置超时前又被业务活动唤起，则不会自动终止。应调整业务逻辑。

根因与修复方案
根因：@Transactional 方法内包含耗时的外部 RPC 调用，事务获取数据库连接后，在等待外部服务响应期间一直占用连接并保持事务打开，形成 idle in transaction。当外部服务超时重试，多个线程并发处理，造成连接堆积和锁竞争。

修复方案

1. 将外部调用移出事务边界。可使用 TransactionTemplate 或 @Transactional(propagation = Propagation.REQUIRES_NEW) 将数据库操作置于独立短事务中：

   public void processOrder(int orderId) {
       updateOrderStatus(orderId, "PROCESSING");
       paymentGateway.charge(orderId); // 非事务
       updateOrderStatus(orderId, "PAID");
   }
   
   @Transactional(propagation = Propagation.REQUIRES_NEW)
   public void updateOrderStatus(int orderId, String status) {
       jdbcTemplate.update("UPDATE orders SET status = ? WHERE id = ?", status, orderId);
   }
   

2. 数据库侧设置兜底超时：

   ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
   SELECT pg_reload_conf();
   

最佳实践

• 事务中绝不执行外部 RPC、消息队列发送、文件 IO 等非数据库操作。
• 监控 idle in transaction 连接数，持续 >0 即排查。
• 使用 Spring 事务监听器（@TransactionalEventListener）或异步处理削峰解耦。

---

10.8 COPY 导入任务中途失败，数据是否部分写入？

场景描述
数据工程师使用 PostgreSQL JDBC 的 CopyManager API 导入一个 5GB 的 CSV 文件。在导入过程中，网络闪断导致连接断开，任务失败。他担心是否已经有部分数据被持久化到表中，是否会造成数据不一致。

故障现象

• JDBC 抛出 java.net.SocketException: Connection reset 或 PSQLException: An I/O error occurred while sending to the backend。
• pg_stat_activity 中不再有该 COPY 的会话。
• 客户端程序退出，重新连接后查询表的 count(*) 与导入前完全一致。

排查思路与原理分析
PostgreSQL 的 COPY 命令（包括 JDBC 的 CopyManager.copyIn()）在默认情况下运行于当前事务中。如果事务是显式开启的（autoCommit=false），整个 COPY 过程同属一个事务。如果在 COPY 过程中发生任何错误（包括网络断开、数据格式错误、违反约束），数据库会回滚整个事务，不会留下部分导入的数据。

即使用默认的 autoCommit=true，底层驱动也会为 COPY 操作隐式开启一个事务，失败时自动回滚。因此不存在部分写入的情况。

验证方法
可以通过模拟实验验证：启动一个 COPY 导入大量数据，中途强制 kill 客户端进程或拔网线，之后查看表行数无变化。

注意事项
如果使用 COPY 的 FREEZE 选项或特殊参数，务必了解其事务行为。对于极特殊场景（如使用 COPY 到分区表但出现约束错误时默认插入到了默认分区，这种情况不算部分写入，但数据去了其他地方），但一般不涉及。

最佳实践

• 大批量加载推荐使用 COPY，既快又事务安全。
• 如果网络不稳定，可分割文件分批导入，每批一个小事务，失败时只需重试当前批次。
• 设置合理的 socketTimeout 和 tcpKeepAlive 防止长时间无响应断开。

---

10.9 Druid WallFilter 拦截了正常业务 SQL，如何快速放开？

场景描述
使用 Druid 连接池的应用突然发现部分动态报表功能报错，日志显示：

sql injection violation, part deny : select * from report where 1=1 and name = 'xxx' order by id asc

该 SQL 是由后台管理功能拼接了 order by 参数动态生成，虽然使用了参数化查询，但 order by 字段名无法用 ? 占位，业务使用了白名单拼接：String sql = "select * from report where name = ? order by " + sanitize(orderField);。但 Druid 的 WallFilter 仍因 order by 包含动态文本判定为注入。业务急需临时放开限制。

排查思路

1. 查看 Druid Monitor 的 SQL 防火墙页面，确认被拦截的具体 SQL 和规则。
2. 检查 WallConfig 配置，可能默认禁用了多语句、注释、函数等，但这里触发了 selectAllow 或 statementAllow 的限制。
3. 分析业务 SQL：虽然进行了白名单校验，但 Druid 无法感知，需要动态调整规则。

修复方案
方法一：局部使用白名单配置
在 Spring 配置中添加自定义 WallFilter Bean，设置更宽松的 WallConfig：

@Bean
public WallFilter wallFilter() {
    WallFilter wallFilter = new WallFilter();
    WallConfig config = new WallConfig();
    config.setSelectAllow(true);  // 允许所有 select 语法
    // 或者更精细：config.setSelectWhereAlwayTrueCheck(false);
    wallFilter.setConfig(config);
    return wallFilter;
}

但此方法降低了整体安全性，不建议全局使用。

方法二：针对特定 SQL 添加白名单（推荐）
使用 Druid 的 wall.permit 属性或通过在 SQL 中添加特殊注释绕过：

/* druid-wall-filter: disable */ 
select * from report where name = ? order by ?

Druid 支持通过 /*druid*/ 注释指令局部关闭防火墙。可以在业务代码中动态拼接此注释。

方法三：重构 SQL 消除动态 order by
使用 CASE WHEN 彻底避免拼接：

SELECT * FROM report
WHERE name = ?
ORDER BY CASE ? 
    WHEN 'id' THEN id 
    WHEN 'name' THEN name 
    ELSE id END;

这样所有参数都是占位符，Druid 不会误报，同时从根本上杜绝注入风险。

最佳实践

• 动态排序、动态表名等场景，优先使用 CASE 或后端映射完全避免拼接。
• 保留 WallFilter 生产开启，提高安全性。
• 对不可避免的合法动态 SQL，配置细粒度的白名单规则或使用注释指令局部放行。

---

10.10 驱动超时参数混淆导致应用在数据库短暂故障后雪崩

场景描述
某应用配置了 socketTimeout=5（秒），connectTimeout=2（秒）。一次数据库主备切换导致服务暂停 10 秒。恢复后，大量应用的 JDBC 连接因 socketTimeout 到期抛出 SocketTimeoutException，应用框架没有合理的重试和熔断，直接不断尝试建立新连接并立即超时，造成线程池耗尽，服务雪崩。

故障现象

• 数据库故障期间，应用日志满是 Read timed out。
• 数据库恢复后，应用仍然无法正常工作，连接池中连接被标记为 broken 并不断尝试创建新连接，但新连接同样因超时设置过短或网络拥塞而失败。
• 应用所有接口 500，线程堆栈集中在 HikariPool.getConnection() 或 SocketInputStream.read()。

排查思路

1. 区分各超时参数

   • connectTimeout：TCP 连接建立超时。应较短（如 2-3 秒）。
   • socketTimeout：Socket 读写超时，应足够长以覆盖最长 SQL 执行时间（如 30 秒以上或 600 秒）。
   • loginTimeout：DriverManager 专用，连接池一般不使用。
   • statement_timeout（数据库端）：控制单个 SQL 执行时间。

2. 查看应用配置
   发现 socketTimeout=5000，但业务存在需要 10 秒以上的复杂查询。故障恢复期间，数据库可能因为检查点、连接风暴等原因需要更长时间响应，导致大量查询在 5 秒内无法完成，触发超时。

3. 观察连接池 Metrics
   hikaricp_connections_timeout_total 计数激增，hikaricp_connections_active 波动剧烈。

根因与修复方案
根因：socketTimeout 设置过短，且应用未集成熔断器，导致在数据库出现性能抖动时，查询超时比正常执行时间还短，触发大量异常，形成重试风暴。

修复方案

1. 合理设置超时参数：
   • socketTimeout 设置为最长业务 SQL 执行时间的 3 倍（如 120 秒）。
   • statement_timeout 在数据库侧根据不同业务设置（如 OLTP 5s，报表 60s）。
   • connectTimeout 保持较短（2-5s）。
2. 引入 Resilience4j 或 Spring Cloud Circuit Breaker，在数据库访问层增加熔断和重试策略，快速失败并保护线程池。
3. 连接池层面启用 HikariCP 的 validationTimeout 和 connection-timeout 配合控制。

最佳实践

• 所有数据库访问必须合理配置超时，并理解各超时参数的含义（参考第 9 篇驱动内核）。
• 超时并不是越短越好，需要根据业务 SLO 设定。
• 必须配合熔断器、限流器等弹性组件，避免雪崩。

---

10.11 设计题：设计一套针对“连接池参数不合理导致雪崩”的混沌工程实验方案

目标
验证系统在连接池配置不当（如池大小过小、泄漏、无超时等）时的容错能力和自恢复速度，暴露潜在风险。

实验环境

• 微服务架构，订单服务依赖 PostgreSQL，使用 HikariCP。
• 流量模拟：通过 JMeter 或生产流量回放生成基线负载（如 200 QPS）。
• 监控：Prometheus + Grafana 收集应用和数据库指标，日志收集至 ELK。
• 混沌工程工具：可使用 ChaosBlade、LitmusChaos 或自研脚本。

实验一：连接池大小骤降
假设：运维误操作或配置中心推送错误，将 maximumPoolSize 由 20 改为 3。

• 注入方法：调用配置中心 API 或修改容器环境变量并动态生效（若无法动态，则重启实例注入）。
• 预期现象：
  • hikaricp_connections_active 很快达到 3，hikaricp_connections_pending 持续上升。
  • 用户请求延迟增加，部分请求因排队超时而失败（Connection is not available）。
  • 依赖订单服务的上游系统也开始报错。
• 监测指标：事务成功率、p99 延迟、熔断器状态切换次数。
• 自动恢复措施验证：配置监控告警，当 pending 连续 3 分钟 >0 时，自动重启服务实例恢复正确配置（或通过配置中心回滚）。
• 分析：观察系统是否能在 5 分钟内通过告警和自愈恢复。找出配置推送、监控告警、自动回滚之间的时间盲区。

实验二：连接泄漏模拟
注入：在代码中动态启用一个“泄漏端点”，每次调用 GET /leak 会获取一个连接但不归还。缓慢调用 15 次，耗尽池。

• 预期现象：
  • 连接池 active 不变，但 hikaricp_connections_idle 逐渐降至 0，外观如正常增加连接获取超时。
  • leakDetectionThreshold 应触发日志告警。
  • 部分请求 500，数据库连接数增加（未归还的 idle 连接）。
• 验证：
  • 查看日志中的泄漏堆栈，是否能快速定位到 /leak 端点。
  • 观察是否自动触发隔离（如将可疑实例拉出服务注册中心）。
• 恢复：重启该实例，池恢复，可设置 maxLifetime 较短观察是否能够自动回收部分泄漏连接。

实验三：连接生命周期与数据库超时冲突
注入：设置数据库 idle_in_transaction_session_timeout = 3min，连接池 maxLifetime=10min，并编写一个事务内等待 4 分钟的任务。

• 预期现象：任务执行中连接被数据库杀死，池抛出 already closed 异常，ActiveConnections 骤降后恢复。
• 验证：应用能否正确处理异常并重试，统计重试成功率。

实验产出

• 混沌工程实验报告，包含每个场景的恢复时间（MTTR）、影响范围。
• 优化后的告警阈值和自动化脚本。
• 修订的故障应急预案。

---

10.12 设计题：基于文中的决策树思想，为一套微服务系统规划数据库访问层的故障应急预案

要求
针对订单、库存、账户三个核心微服务，每个服务独立连接池。数据库为 PostgreSQL 16（一主两从）。设计一套覆盖连接耗尽、查询变慢、OOM、批处理失败、预编译失效五大类故障的预案，实现 1 分钟发现、5 分钟定位、10 分钟修复（1-5-10 目标）。

预案结构

1. 监控与发现（1 分钟）

• 工具链：Prometheus 采集 HikariCP Metrics（active/idle/pending/timeout）、数据库 pg_stat_activity、pg_stat_statements（通过 postgres_exporter）、应用 JVM 堆内存（Micrometer）。
• 仪表盘：Grafana 大盘展示各服务连接池状态、数据库连接分布、SQL 解析时间、JVM 堆使用。
• 告警规则（触发即通知值班群）：
  • hikaricp_connections_pending > 0 持续 2 分钟 → 可能连接池不足或泄漏。
  • hikaricp_connections_active 达到 max 的 90% 持续 5 分钟 → 扩展池或慢SQL。
  • pg_stat_activity 中某应用 idle 连接数超过配置 max 的 80% → 疑似泄漏。
  • pg_stat_statements.mean_plan_time 突增 > 1ms 且 pg_prepared_statements 减少 → 预编译失效。
  • 应用 JVM 堆使用 > 85% 连续 5 分钟 → 可能 OOM 风险。

2. 初步定位（5 分钟内）
值班人员根据告警类型，对照标准化决策树（打印为墙贴或电子卡片）执行操作：

• 若出现“连接耗尽”：
  → 打开 Grafana 面板，确定是哪个服务。
  → 执行 SELECT count(*), state FROM pg_stat_activity WHERE application_name = '服务名' GROUP BY state;
  → 若大量 idle，进入泄漏分支：启用该服务实例的 leakDetectionThreshold（如通过 Spring Cloud Config 临时下发）或通过 Arthas attach 运行 monitor 比较 getConnection / close。
  → 快速定位泄漏代码。

• 若出现“查询变慢”：
  → 查看 pg_stat_statements 的 mean_plan_time 排名。
  → 检查 pg_prepared_statements 条目数。
  → 若缓存丢失，排查连接池 connectionInitSql 是否含 DISCARD ALL。
  → 通知开发修复或动调整 prepareThreshold。

• 若出现“OOM”：
  → 自动收集 heap dump（-XX:+HeapDumpOnOutOfMemoryError）上传至对象存储。
  → 查看 Grafana JVM 面板，确认是否因大结果集。
  → 暂停导致 OOM 的定时任务（通过配置中心关闭触发开关）。

• “批处理低吞吐”：
  → 检查 reWriteBatchedInserts 配置，若未开启则修改 JDBC URL 并在下次任务窗口生效。
  → 查看 pg_stat_statements 中相关插入的 rows/calls 比例。

• “预编译失效”：
  → 检查 pg_prepared_statements 数量，若为 0 则查 prepareThreshold 及连接重置。
  → 通过配置中心下发 prepareThreshold=1 并重启实例。

3. 快速修复（10 分钟内）

• 连接泄漏：重启问题实例（临时止血），同时修复代码并走紧急发布。数据库侧执行 pg_terminate_backend 清理残留连接。
• 慢查询：降低 prepareThreshold 或移除 DISCARD ALL，滚动重启。
• OOM：增大堆内存临时解决，同时调整 fetchSize 后发布。
• 批处理：动态调整 batch size 参数限流，修复配置。
• 预编译失效：统一配置 prepareThreshold=1，纳入基础镜像。

4. 自动化防护

• 设定故障自愈规则：例如当 pending 持续过高，自动扩容实例数（K8s HPA 基于自定义指标）或临时增大 maximumPoolSize（需配置中心支持动态生效）。
• 数据库端设置 idle_in_transaction_session_timeout 和 statement_timeout 作为安全网。
• 定期混沌工程演练，验证预案有效性。

预案训练
每季度组织一次桌推，根据决策树卡片模拟故障排查，确保每位成员熟练掌握工具和流程。

---

10.13 PSQLException: This connection has been closed 偶发的全面排查

场景描述
某电商应用平稳运行时，监控日志和链路追踪偶尔爆发几秒的 org.postgresql.util.PSQLException: This connection has been closed. 异常，随后自动恢复。该异常不是持续出现，而是每隔大约 10-15 分钟零星爆发一次，且往往与数据库连接池的 maxLifetime 回收周期吻合。应用节点和数据库之间网络正常。

故障现象

• 异常堆栈通常从 HikariProxyPreparedStatement.executeQuery 或 HikariProxyConnection.prepareStatement 抛出。
• 每次异常爆发仅影响极少量的请求（<1%），但调用方可以看到 500 错误。
• pg_stat_activity 中可见少量 idle 连接突然消失，同时有新的连接瞬间建立。
• 数据库日志 log_disconnections = on 会记录类似 disconnection: session time: 0:14:23.456 user=app database=mydb host=10.0.1.25 port=5432。
• 应用侧 HikariCP Metrics 显示 hikaricp_connections_active 偶尔出现骤降并迅速恢复的“V 形”缺口。

排查思路引导

1. 对时数据库断连日志与应用异常时间
   在数据库日志中查找连接断开记录，确认断开时间是否与异常发生时间吻合，以及断开原因（例如 idle in transaction timeout、connection reset by peer、terminating connection due to administrator command）。

   2025-05-13 11:15:00 UTC LOG:  disconnection: session time: 0:14:30.123 user=app database=mydb host=10.0.1.25 port=5433
   

   如果 session time 刚好是 14 分钟左右，而连接池 maxLifetime 为 30 分钟，可能是数据库侧超时主动断开。

2. 检查数据库侧超时参数
   执行 SHOW 命令查看：

   SHOW idle_in_transaction_session_timeout;
   SHOW statement_timeout;
   SHOW tcp_keepalives_idle;
   

   若 idle_in_transaction_session_timeout = 10min，而应用中某些查询后连接因事务未提交或连接闲置，超时被数据库杀死。

3. 检查连接池生命周期参数
   查看 HikariCP 配置：

   max-lifetime: 1800000  # 30分钟
   idle-timeout: 600000   # 10分钟
   

   问题在于 idle-timeout 或 max-lifetime 大于数据库超时。HikariCP 在连接空闲超过 idleTimeout 时会将其回收，但如果数据库在空闲 10 分钟时就主动断开了，而池的 idleTimeout 为 10 分钟，可能在达到空闲超时检查时，连接早已被数据库杀死，导致在借出使用时才发现已关闭。

4. 观察连接失效时的线程栈
   用 Arthas trace 包围出错的 executeQuery，查看具体连接对象是否在池中闲置过长。

5. 时间线计算工具
   编写简单的脚本，根据 HikariCP 的 maxLifetime 和数据库超时推算安全边际：

   long maxLifetime = 1800_000L; // 30min
   long dbIdleTxTimeout = 600_000L; // 10min
   if (maxLifetime > dbIdleTxTimeout) {
       System.out.println("DANGER: maxLifetime exceeds db timeout!");
   }
   

根因与修复方案
根因：连接池生命周期的 maxLifetime 或 idleTimeout 大于数据库侧的连接超时（如 idle_in_transaction_session_timeout 或云服务商的空闲连接回收策略），导致连接在池中闲置期间被数据库单方面断开，但池仍认为其有效。当该连接被再次借用时，一执行 SQL 就发现连接已关闭，引发异常。

修复方案

1. 调整连接池参数，确保 maxLifetime < 数据库最小超时 - 安全余量（如 1-2 分钟）：

   spring:
     datasource:
       hikari:
         max-lifetime: 480000   # 8 分钟（小于数据库空闲超时 10 分钟）
         idle-timeout: 420000   # 7 分钟
   

2. 如有必要，数据库侧也可适当放宽超时（例如 15 分钟）：

   ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
   SELECT pg_reload_conf();
   

3. 启用 TCP keepalive 作为辅助探测：

   jdbc:postgresql://host/db?tcpKeepAlive=true&socketTimeout=30
   

最佳实践

• 定期审查数据库的所有超时参数，并与连接池配置对齐，形成文档。
• 在云数据库环境下，特别注意厂商的默认空闲超时（AWS RDS 有时是 10 分钟，GCP Cloud SQL 可能不同），必须在应用连接池中调低 maxLifetime。
• 监控 hikaricp_connections_active 的异常波动，出现“V 形”缺口时应立即检查生命周期冲突。

---

10.14 同一 SQL 测试环境极快，上生产就慢，深入分析

场景描述
开发人员发现一个简单的 SELECT 查询在测试环境执行时间稳定在 2 ms，但在生产环境相同的表结构、索引和数据量级下，执行时间在 10 ms ~ 50 ms 波动，且 CPU 使用率偏高。SQL 文本一模一样，数据库参数大致相同，仅硬件配置生产略高。连接池配置也一致。

故障现象

• pg_stat_statements 对应该查询的记录在生产环境中 mean_exec_time 显著高于测试，且 mean_plan_time 占比高（例如 30%）。
• 生产数据库中 pg_prepared_statements 视图几乎为空，而测试环境存在若干预编译缓存条目。
• 生产连接池的 maxLifetime 设置适中，但连接频繁建立销毁（因为 minimumIdle 很小且高峰期连接数波动大），导致预编译频繁丢失。
• 对比测试：在生产的某个会话中手动执行 6 次相同的 PreparedStatement，第 6 次及之后执行时间下降至接近测试水平，证明预编译可提速。

排查思路引导

1. 比较 mean_plan_time 与 mean_exec_time

   SELECT query, calls, mean_plan_time, mean_exec_time 
   FROM pg_stat_statements 
   WHERE query LIKE '%your_query%'
   ORDER BY total_plan_time DESC;
   

   如果 mean_plan_time 在生产明显更高，说明大部分时间花在解析和计划生成。

2. 检查服务端预编译状态

   SELECT count(*) FROM pg_prepared_statements;
   

   如果数量很少或为空，则预编译未生效。

3. 对比两环境的连接生命周期和复用

   • 测试环境连接池可能因为流量低, idleTimeout 后连接长期闲置仍存在，maxLifetime 也可能更长，预编译缓存容易积累。
   • 生产环境连接创建销毁频繁（请求多，池不断扩容缩容），新连接没有预编译缓存。
   • 检查生产 minimumIdle 是否过低，idleTimeout 过短导致空闲连接被快速回收。

4. 检查是否执行 DISCARD ALL
   生产可能存在数据库连接初始化脚本执行 DISCARD ALL 清除了缓存。

5. 确认 prepareThreshold 设置
   如果都是默认 5，且应用代码每次新建 PreparedStatement 只执行 1-3 次，可能在生产无法触发预编译，而测试环境由于请求不频繁，偶尔能达到阈值。

6. 压测对比实验
   在测试环境模拟生产并发，观察 pg_stat_statements 变化，复现问题。

根因与修复方案
根因：生产环境连接池频繁创建和销毁连接，加上 prepareThreshold 较高且 PreparedStatement 对象复用度低，导致服务端预编译缓存几乎无法建立或刚建立就被清除。每次查询都要重新硬解析，生成执行计划（尽管计划本身可能相同），增加了 planning time。相比之下测试环境因为连接稳定，缓存得以保留。

修复方案

1. 全局降低 prepareThreshold：在 JDBC URL 设置 prepareThreshold=1。
2. 优化连接池配置：合理设置 minimumIdle 并延长 idleTimeout，保持一定数量的长连接存活，减少连接频繁建立销毁。
3. 业务代码复用 PreparedStatement：将相同 SQL 模板的 PreparedStatement 提取为单例或在方法外创建，循环内复用。
4. 确保无 DISCARD ALL 误配。
5. 验证：重启应用后，观察 pg_prepared_statements 条目逐渐增多并稳定，mean_plan_time 回落至接近测试环境水平。

最佳实践

• 坚决不在生产环境使用频繁建连的策略，连接一旦建立应尽量延长生命周期以积累预编译缓存。
• 生产与测试环境除数据和资源外，连接池参数应尽量一致，避免因连接生命周期差异导致性能表现不同。
• 将 prepareThreshold=1 作为基础镜像的默认配置，除非有特殊场景。

---

10.15 HikariPool-1 - Connection marked as broken because of SQLSTATE(08006) 的排查与修复

场景描述
应用日志频繁出现 HikariPool-1 - Connection marked as broken because of SQLSTATE(08006) 警告，有时伴随 An I/O error occurred while sending to the backend。服务偶发报错，但整体可用。数据库为 PostgreSQL，连接池 HikariCP。

故障现象

• 日志中每隔几分钟就有一条 marked as broken 记录。
• hikaricp_connections_active 偶尔小幅波动，hikaricp_connections_creation_total 计数增加。
• pg_stat_activity 中可见连接被断开后立刻补充新连接的动态。

排查思路引导

1. 了解 SQLSTATE 08006 含义
   PostgreSQL 错误码 08006 表示 “connection failure”，即连接在通信过程中发生了不可恢复的错误，可能是网络中断、数据库进程崩溃、防火墙切断空闲连接、或数据库参数强制断开。

2. 查数据库日志
   查看 PostgreSQL 日志中的 disconnection 记录，注意断开原因：

   LOG:  could not receive data from client: Connection reset by peer
   LOG:  terminating connection due to administrator command
   LOG:  could not send data to client: Broken pipe
   

   判断是客户端主动重置还是服务端主动杀死。如果是 “idle in transaction timeout” 或 “statement timeout”，则与超时参数有关。

3. 检查网络层
   使用 tcpdump 抓取 5432 端口流量，查看在连接断开前后的 TCP RST 包，判断是客户端还是服务端发起断开。结合应用容器与数据库之间的防火墙或负载均衡器超时配置（如 AWS NLB 空闲超时 350 秒）。

4. 排查连接池和 JDBC 超时参数

   • socketTimeout：若设置过短，长时间未收到数据会触发 SocketTimeoutException，进而池标记连接为 broken。
   • connectionTimeout：连接建立超时无关，这里是已建立连接的问题。
   • 数据库端 idle_in_transaction_session_timeout 或 statement_timeout 也会导致服务端主动踢掉连接。

5. 检查应用是否有长时间运行的事务或查询
   如果某些 SQL 执行时间超过 socketTimeout 或 statement_timeout，事务被强制终止，连接也会标记为 broken。

根因与修复方案
可能根因组合

• 情况 A：数据库 idle_in_transaction_session_timeout 为 5 分钟，应用存在事务空闲超过 5 分钟，数据库主动断开连接。连接回到池后，下次借用时 HikariCP 尝试使用该连接，触发 IO 异常，标记 broken。
• 情况 B：应用与数据库之间存在 L4 负载均衡器，其空闲超时为 300 秒，而连接池的 maxLifetime 或 idleTimeout 超过 300 秒，导致空闲连接被 LB 清除，RST 包送到数据库，连接被标记 broken。
• 情况 C：socketTimeout 设置过短（例如 10 秒），而复杂查询执行 15 秒，导致 Socket 读取超时，连接被标记 broken。

修复方案

1. 如果是数据库侧超时，按案例 10.13 的方法对齐 maxLifetime、idleTimeout 与数据库超时。
2. 如果是 LB 超时，则需要调整 LB 空闲超时或降低连接池的 maxLifetime 使其短于 LB 超时，或启用 TCP keepalive 保持连接活跃。
3. 如果是 socketTimeout 不合理，调整其值大于最长 SQL 执行时间。
4. 在明确根因后，日志中的 marked as broken 会减少并消失。

最佳实践

• 做好全链路的超时对齐：应用 socketTimeout、连接池生命周期、数据库 session timeout、网络中间件空闲超时，形成一个“超时金字塔”，池生命周期位于最内层。
• 观察 hikaricp_connections_active 和 broken 的频率，设置告警，当 broken 率超过 5% 时排查。
• 使用 HikariCP 的 keepaliveTime 参数（3.4.0+）定期发送心跳，保持连接活跃，防止被中间件断开。

---

10.16 综合系统设计：从零搭建一套 JDBC 性能监控与故障自愈系统

场景描述
某大型电商平台微服务架构，超过 50 个服务使用 JDBC 访问 PostgreSQL 集群。要求设计一套集监控、告警、自动诊断、自愈于一体的 JDBC 访问层管理系统，实现“秒级发现、分钟级诊断、分钟级恢复”。

系统目标

• 全覆盖监控：采集所有服务的 JDBC 连接池指标、SQL 执行统计、JVM 堆内存、数据库端连接与查询状态。
• 智能告警：基于动态阈值和异常检测，减少误报，快速通知。
• 自动诊断：故障发生时，自动收集堆栈、连接快照、执行计划，按照决策树分析可能原因，生成诊断报告。
• 自愈能力：对已知可自愈故障（如连接泄漏的临时回收、慢查询限流、OOM 重启）执行自动化操作，对复杂故障升级人工。

设计架构

flowchart TD
    A[微服务实例] -->|Micrometer| Prom[Prometheus]
    A -->|Arthas API| Diag[诊断引擎]
    A -->|日志| ELK[ELK Stack]
    DB[PostgreSQL] -->|postgres_exporter| Prom
    Prom --> Alert[AlertManager]
    Alert --> Diag
    Alert --> Ops[运维通知]
    Diag --> Action[自愈执行器]
    Action --> K8s[K8s API]
    Action --> DB_Admin[数据库管理]
    Diag --> Report[故障报告]
    Grafana --> Prom

核心组件设计

1. 数据采集层

• HikariCP Metrics：通过 Micrometer 暴露到 Prometheus，采集 active、idle、pending、timeout、creation、connection 等。
• Druid SQL 监控（如果多池共存）：通过其 StatFilter 采集 SQL 执行耗时、次数、并发。
• 数据库侧：使用 postgres_exporter + pg_stat_statements 抓取 calls、mean_exec_time、mean_plan_time、rows 及 pg_stat_activity 连接状态。自定义脚本定期将 pg_prepared_statements 数量、pg_locks 等待信息写入时序库。
• JVM 指标：Micrometer 采集堆内存、GC、线程。
• Arthas 诊断数据：通过 Arthas 的 monitor、watch 等命令，由诊断引擎在需要时触发采集，例如高 pending 时获取 getConnection / close 比率。

2. 告警引擎
动态阈值基于历史分位数计算（如昨天同时段的 P95）。告警规则示例：

• hikaricp_connections_pending > 0 持续 2 分钟 → 级别 P1。
• hikaricp_connections_active / maximumPoolSize > 0.9 持续 5 分钟 → P2。
• pg_stat_statements.mean_plan_time > 2ms 且 pg_prepared_statements count < 10 → P2 预编译失效。
• jvm_memory_used_bytes / jvm_memory_max_bytes > 0.9 持续 5 分钟 → P1 内存压力。
• idle in transaction count > 5 持续 5 分钟 → P2 长事务。

3. 自动诊断引擎
诊断引擎接收告警后，根据决策树执行诊断动作（参考本文第 9 节），并生成 JSON 格式的诊断报告。步骤示例：

• 收到 “连接耗尽” 告警 → 查询 Prometheus 找出受影响服务实例 → 通过 Arthas API 对该实例执行 monitor -c 5 HikariPool getConnection 和 close，对比次数 → 若失调，标记泄漏，并提取 leakDetectionThreshold 日志堆栈 → 输出诊断：连接泄漏，指向 XService.YMethod。
• “慢查询” 告警 → 查询 pg_stat_statements 获取 top 查询 → 检查 pg_prepared_statements → 若缺失，检查应用配置是否有 DISCARD ALL → 输出诊断：预编译失效，建议修改配置。

4. 自愈执行器
对于确诊的常见故障，执行自动修复动作，记录操作日志并通知。

• 连接泄漏：若为代码缺陷，不能自动修复代码，但可以临时重启该实例释放连接，并触发回滚发布。同时可在数据库端执行 pg_terminate_backend 清理残留连接。
• 慢查询风暴：临时启用限流（如 Sentinel 热点参数限流），并调低 statement_timeout 在数据库侧拦截慢 SQL。
• OOM：如果是因为 fetchSize 未设置，可自动化调整该实例的环境变量 DEFAULT_ROW_FETCH_SIZE 并重启，或直接 kill 容器的同时扩容。
• 批处理失败：如果监控发现 WAL 暴涨，触发调整 batch size 或临时暂停非关键批处理。
• 预编译失效：通过配置中心下发 prepareThreshold=1，并滚动重启。

5. 可观测性输出

• Grafana 大屏：显示全局连接池健康度、SQL 性能、故障时间线。
• 故障报告：每次诊断后生成 Markdown 报告，包含时间、现象、诊断步骤、决策依据、采取的自愈动作、建议后续人工处置项。

实施关键点

• 安全边界：自愈系统不能随意修改生产配置，需有审批流和灰度；高危操作（如重启实例）必须有二次确认或限频。
• Arthas 安全：仅在故障时动态开启，用完即关闭，避免性能影响。
• 数据库保护：所有 terminate_backend 等操作应限制频率和范围。

最佳实践

• 优先完善监控和告警，再逐步引入自愈。
• 定期对自愈规则进行混沌工程验证，确保不产生误动作。
• 将诊断决策树代码化，与本文案例保持同步更新。
• 所有自愈动作记录到审计日志，便于复盘。

---

JDBC 排障工具速查表

工具	作用域	关键命令 / 指标	典型现象映射
pg_stat_activity	数据库	SELECT pid, state, now() - xact_start AS txn_age, query FROM pg_stat_activity WHERE application_name='...';	连接泄漏、长事务、idle 堆积
pg_stat_statements	数据库	SELECT queryid, calls, total_plan_time, mean_exec_time, query FROM pg_stat_statements ORDER BY total_plan_time DESC LIMIT 20;	硬解析泛滥、慢查询分析
pg_prepared_statements	数据库	SELECT name, statement, prepare_time FROM pg_prepared_statements;	预编译缓存状态，缓存重置、膨胀
pg_locks	数据库	SELECT relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted;	锁争用、长事务阻塞
pg_stat_user_tables	数据库	SELECT relname, n_dead_tup, n_live_tup, last_vacuum FROM pg_stat_user_tables;	表膨胀、VACUUM 阻塞
HikariCP Metrics	连接池	hikaricp_connections_active, hikaricp_connections_idle, hikaricp_connections_pending, hikaricp_connections_timeout_total	连接池耗尽、泄漏、排队
HikariCP leak log	连接池	日志关键字 Connection leak detection triggered 及其堆栈	定位连接泄漏代码位置
Druid SQL Monitor	连接池	监控面板中的 SQL 执行统计、WallFilter 拦截日志	注入风险、慢 SQL、错误 SQL
Arthas watch/monitor	应用层	watch com.zaxxer.hikari.pool.HikariPool getConnection, monitor -c 5 com.zaxxer.hikari.pool.HikariPool getConnection	连接获取/释放频率比对，定位泄漏或不足
Arthas trace	应用层	trace com.example.service.* execute 查看耗时分布	慢操作根因分析
JMeter / Gatling	压测	配置 JDBC Request 或 HTTP Request 模拟业务模型	性能基线、容量规划、配置调优
jmap / MAT	应用堆	jmap -dump:live,file=heap.hprof <pid> + MAT “Leak Suspects”	OOM 根因，大结果集识别
Wireshark / tcpdump	网络	捕捉 tcp.port == 5432，分析 SQL 包和往返时间	批处理未重写、网络延迟排查

延伸阅读：

• 《PostgreSQL 9.6 High Performance》
• HikariCP 官方 Wiki（github.com/brettwooldr…
• Druid 官方 Wiki（github.com/alibaba/dru…
• Arthas 用户手册（arthas.aliyun.com/doc/）

---