测试策略、SQL 日志、性能诊断与生产排障

在前几篇中，我们已经逐步完成了：

• tio-boot + jOOQ 基础整合
• 事务管理
• Codegen 强类型升级
• Record / POJO CRUD
• 批量、分页、动态 SQL
• PostgreSQL UPSERT、returning
• 多表查询、DTO 投影、聚合统计
• JSONB、窗口函数、CTE 与 PostgreSQL 高级 SQL
• 审计字段、乐观锁、数据权限与企业级 Repository 设计

到这里，这套方案已经不只是“能跑起来”，而是已经具备：

• 强类型 SQL 能力
• 清晰的事务边界
• 可维护的 Repository 设计
• 企业级常见治理能力

但只要系统真的进入开发协作和生产运行阶段，很快就会遇到另一类同样关键的问题：

• 这些 SQL 该怎么测
• 出问题时怎么知道到底执行了什么 SQL
• 慢查询怎么定位
• 批量操作性能差怎么分析
• 事务、连接池、锁等待问题怎么排查
• 线上问题怎么快速收敛到 SQL 层

所以这一篇的主题不再是“怎么写 SQL”，而是：

怎么验证、观察、诊断并排查 tio-boot + jOOQ 体系中的数据库问题。

本文会系统讲清：

• Repository / Service / 集成测试策略
• SQL 日志打印与可观测性
• jOOQ 的 ExecuteListener 与调试扩展
• 慢 SQL 定位思路
• PostgreSQL 执行计划与常见性能问题
• 批量、分页、窗口函数、JSONB 场景下的诊断方法
• 生产环境常见数据库故障排查路径

一、为什么“会写 SQL”不等于“系统能稳定运行”

很多项目在开发阶段，最关心的是：

• 功能对不对
• SQL 能不能跑通
• 返回结果对不对

但到了联调、压测、上线后，真正高频的问题往往是：

• 这条 SQL 为什么这么慢
• 为什么某个接口偶发超时
• 为什么事务会卡住
• 为什么更新行数是 0
• 为什么批量导入时数据库 CPU 飙高
• 为什么明明有索引还是没走
• 为什么分页越翻越慢

也就是说，工程成熟度的分水岭，往往不在于“能写出来”，而在于：

是否具备可验证、可观察、可诊断、可排障的能力。

这一篇就是把这部分补齐。

二、测试策略：不要只靠手工点接口

如果数据访问层只靠：

• 本地手工点接口
• 看返回 JSON
• 线上出错再看日志

那成本会越来越高。

推荐从一开始就建立分层测试策略。

2.1 推荐的三层测试结构

最自然的测试结构通常是：

1. Repository 测试

验证：

• SQL 是否正确
• 条件拼装是否符合预期
• 返回结果是否正确
• 乐观锁 / 逻辑删除 / 数据权限是否生效

2. Service 测试

验证：

• 事务是否按预期提交或回滚
• 多 Repository 组合是否正确
• 并发冲突是否能正确处理

3. 接口集成测试

验证：

• HTTP 参数到数据库落地的完整链路
• AOP 注入 / 生命周期 / 事务拦截器是否正常工作

这三层不是互相替代，而是各自解决不同粒度的问题。

2.2 Repository 测试为什么最关键

因为 Repository 是最接近 SQL 的地方。

如果 Repository 层缺少测试，后面所有问题都会向上蔓延。

尤其以下能力，非常适合直接做 Repository 测试：

• 动态条件
• 分页
• 多表 join
• JSONB 查询
• UPSERT
• returning
• 乐观锁 version 判断
• 逻辑删除过滤
• 数据权限条件注入

换句话说：

SQL 层越强，Repository 测试就越重要。

三、推荐的测试环境策略

测试数据库有几种常见方案。

3.1 最不推荐：直接连开发共用库

这是很多项目一开始的做法，但问题很大：

• 测试数据会互相污染
• SQL 结果不稳定
• 删除、更新容易影响别人
• 很难重复执行

所以尽量不要让自动化测试依赖共用开发库。

3.2 推荐：独立测试库

最实用的方案是：

• 单独一个 PostgreSQL 测试数据库
• 每次测试前准备数据
• 测试结束后清理数据或回滚事务

优点：

• 环境稳定
• SQL 结果可重复
• 更接近真实数据库行为

3.3 更进一步：临时数据库 / 容器化测试库

如果团队条件允许，更理想的方案是：

• 每次测试使用临时数据库
• 或通过容器启动独立 PostgreSQL 实例

这样隔离性更好。

但如果当前先追求工程落地，不一定一开始就做到这一步。

四、Repository 测试怎么写

这里重点不是某个测试框架语法，而是测试内容和思路。

4.1 测试要覆盖哪些点

以 SystemAdminRepository 为例，至少建议覆盖：

插入

• 是否成功插入
• 是否返回正确主键
• 是否自动填充审计字段

查询

• findById
• existsByLoginName
• 分页、动态条件
• 逻辑删除后是否自动过滤

更新

• 普通更新是否成功
• 乐观锁 version 是否正确生效
• version 不匹配时是否更新 0 行

删除

• 逻辑删除是否只改标记，不做物理删除
• 删除后默认查询是否不可见

权限

• 超级管理员是否能查全部
• 普通用户是否只能查自己有权限的数据

4.2 一个典型 Repository 测试思路

例如测试乐观锁：

准备数据

插入一条管理员记录：

• id = 1
• version = 0

执行更新 1

调用：

updatePasswordWithVersion(1, "new1", 0)

预期：

• 返回 true
• 数据库 version 变成 1

执行更新 2

再次调用：

updatePasswordWithVersion(1, "new2", 0)

预期：

• 返回 false
• 因为 version 已不是 0

这个测试非常典型，也非常有价值，因为它直接验证了并发保护是否真正生效。

4.3 数据权限测试思路

例如测试“普通用户只能看自己创建的数据”。

准备两条数据

• A 记录：created_by = 1001
• B 记录：created_by = 1002

设置上下文

DataPermissionContext.setUserId(1001L);
DataPermissionContext.setSuperAdmin(false);

查询列表

预期：

• 只能查到 A
• 查不到 B

然后再切换：

DataPermissionContext.setSuperAdmin(true);

预期：

• A、B 都能查到

这种测试非常适合沉淀成固定用例。

五、Service 层测试：重点测事务与业务语义

Repository 层主要测 SQL 行为，Service 层则更适合测业务组合。

5.1 最典型的事务测试

假设有一个业务：

• 更新管理员密码
• 插入审计日志

那么测试重点应是：

成功场景

两者都成功，事务提交。

失败场景

如果插入审计日志时报错：

• 密码更新也必须回滚

这类测试能直接验证：

• TransactionManager 是否生效
• TransactionContext 是否正确传递
• 多个 DAO 是否真的复用了同一事务连接

5.2 为什么 Service 测试不能完全替代 Repository 测试

因为 Service 测试更偏：

• 流程正确性
• 事务一致性
• 业务语义

它不适合细测每条 SQL 的条件拼装细节。

所以两者要配合，而不是二选一。

六、SQL 日志：生产排障的第一现场

如果说测试解决的是“上线前尽量发现问题”，那么 SQL 日志解决的就是：

上线后出问题时，第一时间知道数据库层到底发生了什么。

在 tio-boot + jOOQ 体系里，SQL 日志至少要回答三件事：

• 执行了什么 SQL
• 带了什么参数
• 花了多长时间

6.1 最基础：依赖日志框架输出

如果项目里已经有 logback，可以先确保：

• jOOQ 的 SQL 执行日志能输出
• 连接池、事务异常也能输出

这通常是最低配置。

但如果想做得更工程化，推荐使用 jOOQ 的执行监听器。

七、jOOQ 的 ExecuteListener：最适合做 SQL 观测扩展

jOOQ 提供了一个非常关键的扩展点：

ExecuteListener

它可以在 SQL 执行前后介入，拿到：

• SQL 文本
• 绑定参数
• 执行耗时
• 异常信息

这几乎就是构建 SQL 日志和慢查询诊断的最佳入口。

7.1 一个简单的 SQL 日志监听器

package demo.jooq.monitor;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class SqlLogListener extends DefaultExecuteListener {

  private long startNanos;

  @Override
  public void executeStart(ExecuteContext ctx) {
    startNanos = System.nanoTime();
  }

  @Override
  public void executeEnd(ExecuteContext ctx) {
    long costMs = (System.nanoTime() - startNanos) / 1_000_000;
    log.info("jOOQ SQL cost={}ms, sql={}", costMs, ctx.sql());
  }

  @Override
  public void exception(ExecuteContext ctx) {
    log.error("jOOQ SQL error, sql={}", ctx.sql(), ctx.exception());
  }
}

这个监听器已经能做到：

• 打印 SQL
• 打印耗时
• 异常时输出 SQL 和异常

虽然简单，但已经很有价值。

7.2 在配置类中注册监听器

在 JooqConfig 里创建 DSLContext 时，可以通过 Configuration 注册。

例如：

import org.jooq.Configuration;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

Configuration configuration = new DefaultConfiguration()
    .set(dataSource)
    .set(SQLDialect.POSTGRES)
    .set(new demo.jooq.monitor.SqlLogListener());

DSLContext dslContext = DSL.using(configuration);

如果你的事务里也会动态创建 DSLContext，那么事务内创建时也应使用一致的配置，而不是只在全局单例 DSLContext 上配置监听器。

这点非常重要。

7.3 进一步：慢 SQL 单独打日志

如果只是一股脑把所有 SQL 都打印出来，线上日志很快会很吵。

更推荐的方式是：

• 普通 SQL 简要记录
• 慢 SQL 单独告警或单独日志分类

例如：

package demo.jooq.monitor;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class SlowSqlListener extends DefaultExecuteListener {

  private static final long SLOW_SQL_MS = 200;
  private long startNanos;

  @Override
  public void executeStart(ExecuteContext ctx) {
    startNanos = System.nanoTime();
  }

  @Override
  public void executeEnd(ExecuteContext ctx) {
    long costMs = (System.nanoTime() - startNanos) / 1_000_000;
    if (costMs >= SLOW_SQL_MS) {
      log.warn("slow sql detected, cost={}ms, sql={}", costMs, ctx.sql());
    }
  }
}

这在生产里非常实用。

八、日志里到底该不该打印参数

这是一个很实际的问题。

8.1 打印参数的好处

好处非常明显：

• 排查问题时能复现 SQL
• 能看到实际 where 条件
• 更容易发现空值、格式问题、越界问题

8.2 风险也很明显

但也有风险：

• 密码、token、隐私字段可能泄露
• 大 JSON 参数会让日志爆炸
• 批量 SQL 参数量会非常大

所以更推荐：

开发环境打印详细参数，生产环境适度脱敏或只打印 SQL 模板与耗时。

尤其像这些字段，通常建议脱敏：

• password
• access_token
• phone
• id_card
• 大段 JSON

九、SQL 诊断：先看哪几个维度

当某个接口慢时，不要一上来就说“数据库有问题”。

更推荐按层分解。

9.1 先确认是不是 SQL 本身慢

先从日志拿到：

• 具体 SQL
• 执行耗时
• 调用频率

如果 SQL 本身只要 5ms，那问题大概率不在数据库。

如果 SQL 本身要 800ms，才值得往下深挖。

9.2 再确认是不是偶发慢还是稳定慢

这是两个完全不同的问题。

稳定慢

通常更可能是：

• SQL 本身写法问题
• 索引缺失
• 分页过深
• JSONB 没索引
• join 条件没优化

偶发慢

通常更可能是：

• 锁等待
• 连接池耗尽
• 数据库抖动
• 某些大事务阻塞
• 批量任务和在线流量冲突

这个区分非常重要。

9.3 再看是不是接口总耗时远大于 SQL 耗时

例如：

• SQL 50ms
• 接口总耗时 900ms

那说明很可能：

• 应用层还有别的瓶颈
• JSON 序列化慢
• 远程调用慢
• 线程池排队
• 锁争用不在数据库层

所以不要把所有慢接口都归咎于 SQL。

十、PostgreSQL 执行计划：性能诊断的核心工具

只要确认“SQL 本身慢”，接下来最重要的一步通常就是：

看执行计划

也就是：

EXPLAIN ANALYZE ...

它能帮助回答：

• 有没有走索引
• 是顺序扫描还是索引扫描
• join 怎么执行的
• 排序是否代价很高
• 实际扫描行数是多少
• 估算和实际偏差大不大

10.1 最常见的几个关注点

1. Seq Scan

如果明明应该按主键或唯一字段查，却出现：

Seq Scan

通常要警惕：

• 没索引
• 条件函数导致索引失效
• 数据类型不匹配
• 表太小，优化器认为全表扫更便宜

2. Index Scan / Bitmap Index Scan

通常说明索引在发挥作用。

但还要继续看：

• 扫描行数是否过多
• 回表是否严重
• 过滤条件是否还有很多没命中索引

3. Sort

如果分页或 order by 很慢，经常会看到排序代价很高。

这时要思考：

• 排序字段是否有索引支持
• 是否是深分页
• 是否能改为基于游标的分页

4. Nested Loop / Hash Join / Merge Join

多表查询慢时，这几个 join 方式尤其值得关注。

不是说哪一种一定好或坏，而是要结合：

• 表大小
• 过滤条件
• 索引情况
• 实际行数

来看。

10.2 一个非常实用的排查习惯

对于慢 SQL，建议固定形成这个流程：

1. 从日志拿到 SQL
2. 在数据库中执行 EXPLAIN ANALYZE
3. 看是否走索引
4. 看扫描行数和返回行数差距
5. 判断瓶颈是扫描、排序、join 还是锁等待
6. 再决定改 SQL、补索引还是改分页方式

这样排查会越来越快。

十一、典型性能问题与排查思路

下面把前几篇出现过的一些高频场景，结合性能问题一起总结。

11.1 分页越来越慢

典型症状：

• 第 1 页很快
• 第 1000 页明显变慢

最常见原因：

• 使用了 limit + offset
• offset 很大时，数据库需要先跳过大量行

排查建议：

• 看 EXPLAIN ANALYZE
• 看排序是否昂贵
• 看是否需要改成“基于最后一条主键”的游标分页

11.2 JSONB 查询慢

典型症状：

• @> 查询慢
• profile->>'age' 查询慢

最常见原因：

• 没有 GIN 索引
• 高频 key 没有表达式索引
• JSON 查询写法不利于走索引

排查建议：

• 看 EXPLAIN ANALYZE
• 优先验证 GIN(profile) 是否存在
• 高频 key 单独建表达式索引

11.3 批量插入慢

典型症状：

• 一次导入很耗时
• 数据库 CPU 高
• 事务很长

最常见原因：

• 没有批处理，逐条 insert
• 批次过大
• 索引过多导致写放大
• 同时还在做复杂业务逻辑

排查建议：

• 看是否用了 batchInsert
• 看每批大小是否合理
• 看是否能拆批
• 看是否必须在一个大事务里完成

11.4 多表 join 慢

典型症状：

• 列表查询很慢
• DTO 投影接口慢

最常见原因：

• 关联字段没索引
• where 条件过滤性不强
• join 顺序不合理
• 一次拉太多列

排查建议：

• 看 join 字段索引
• 减少无关字段
• 确认 where 条件是否尽量早过滤
• 必要时考虑视图或预聚合

11.5 乐观锁更新失败率高

这不一定是性能问题，但常会被误认为“数据库异常”。

如果大量出现：

• update ... where id=? and version=?
• 返回 0 行

通常说明：

• 并发冲突频繁
• 业务操作过慢
• 一个对象被太多人同时编辑

这时候应该从业务交互和并发模型上看，而不是先怪数据库。

十二、锁等待与事务阻塞：生产最常见的“偶发慢”

很多线上慢请求并不是 SQL 本身写得差，而是：

被别的事务锁住了。

这是非常常见的生产问题。

12.1 典型表现

• 某条 update 平时很快
• 偶尔卡几秒甚至几十秒
• 数据库 CPU 不一定高
• 应用线程堆积

这种情况就要高度怀疑锁等待。

12.2 常见根因

1. 大事务太长

例如一个事务里：

• 批量更新很多数据
• 中间还做远程调用
• 事务迟迟不提交

那么它持有的锁会影响别人。

2. 热点行竞争

例如同一条配置、同一条库存、同一条账号信息被高频更新。

3. 事务里做了不必要的复杂逻辑

这会人为拉长锁持有时间。

12.3 处理思路

最重要的原则是：

尽量缩短事务时间，尽量缩小锁范围。

具体包括：

• 事务里不要做远程调用
• 事务里不要做重型计算
• 批量更新尽量拆批
• 热点更新考虑优化业务模型
• 必要时使用乐观锁替代部分悲观等待

十三、连接池问题：别把数据库慢和拿不到连接混为一谈

有时接口慢，并不是 SQL 慢，而是根本拿不到连接。

在使用 Druid 或 Agroal 时，这类问题也很常见。

13.1 典型表现

• 接口整体超时
• SQL 日志甚至没打印出来
• 线程堆积
• 连接池活动连接数接近上限

这时候要怀疑：

• 连接泄漏
• 事务未及时结束
• 连接池太小
• 应用并发超过数据库承受范围

13.2 排查重点

• 当前连接池最大连接数
• 活跃连接数
• 连接等待时间
• 是否有连接长期不归还
• 是否有大事务长期占连接

所以要记住：

“数据库接口慢”不等于“SQL 慢”，也可能是“连接都拿不到”。

十四、生产排障的一个推荐流程

真正线上出问题时，最需要的是排障路径，而不是零散技巧。

我更推荐固定成下面这个顺序。

14.1 第一步：先拿到具体接口与时间点

确认：

• 哪个接口慢
• 从什么时候开始
• 是全部请求慢还是部分请求慢
• 是稳定复现还是偶发

14.2 第二步：看应用日志与 SQL 日志

重点看：

• 是否打印出 SQL
• SQL 耗时是多少
• 是否有异常
• 是否有慢 SQL 告警

14.3 第三步：区分是哪一层慢

判断是：

• 应用层慢
• SQL 执行慢
• 锁等待
• 连接池等待
• 数据库资源不足

14.4 第四步：如果确认是 SQL 慢，就跑执行计划

使用：

EXPLAIN ANALYZE

看：

• 索引
• 扫描
• 排序
• join
• 实际行数

14.5 第五步：如果是偶发慢，重点查事务与锁

这时更要关注：

• 长事务
• 热点更新
• 大批量任务
• 并发冲突

14.6 第六步：修复后再回到测试与日志

一个成熟的闭环应该是：

• 线上发现问题
• 本地或测试环境复现
• 加测试覆盖
• 加日志或慢 SQL 观测
• 防止问题再次出现

这才算真正解决。

十五、推荐的日志与诊断实践清单

如果把这篇压缩成一个“最低落地清单”，我建议至少做到下面这些。

开发环境

• 打开 SQL 日志
• 能看到 SQL 文本和耗时
• Repository 层有基础测试
• Service 层关键事务有回滚测试

测试环境

• 独立测试数据库
• 覆盖分页、乐观锁、逻辑删除、权限条件
• 覆盖 UPSERT、JSONB、窗口函数这些复杂 SQL

生产环境

• 慢 SQL 单独告警
• SQL 异常日志可追踪
• 连接池监控可见
• 大事务和批量任务可识别
• 有固定的执行计划分析流程

十六、一个更完整的 SQL 监听器示例

最后给一个稍微更实用一点的监听器示例，把普通 SQL、慢 SQL、异常 SQL 放到一起。

package demo.jooq.monitor;

import org.jooq.ExecuteContext;
import org.jooq.impl.DefaultExecuteListener;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class SqlDiagnosticsListener extends DefaultExecuteListener {

  private static final long SLOW_SQL_MS = 200;
  private long startNanos;

  @Override
  public void executeStart(ExecuteContext ctx) {
    startNanos = System.nanoTime();
  }

  @Override
  public void executeEnd(ExecuteContext ctx) {
    long costMs = (System.nanoTime() - startNanos) / 1_000_000;
    String sql = ctx.sql();

    if (costMs >= SLOW_SQL_MS) {
      log.warn("slow sql detected, cost={}ms, sql={}", costMs, sql);
    } else {
      log.debug("sql cost={}ms, sql={}", costMs, sql);
    }
  }

  @Override
  public void exception(ExecuteContext ctx) {
    log.error("sql execute failed, sql={}", ctx.sql(), ctx.exception());
  }
}

注册方式与前文一致。

这个监听器不算复杂，但已经足够作为项目里的第一版 SQL 观测基础设施。

十七、本篇总结

这一篇不再讨论“SQL 能不能写出来”，而是讨论：

写出来之后，怎么验证、怎么观察、怎么诊断、怎么排障。

通过本文，我们完成了：

• 建立 Repository / Service / 接口 三层测试思路
• 明确为什么 Repository 测试最关键
• 理解 jOOQ ExecuteListener 是 SQL 日志与诊断的核心扩展点
• 建立慢 SQL 监控思路
• 学会区分 SQL 慢、锁等待、连接池等待、应用层慢
• 明确 PostgreSQL 执行计划是性能分析核心工具
• 总结分页、JSONB、批量、join、乐观锁等典型问题的排查思路
• 给出一条从线上告警到根因定位的推荐流程

一句话总结：

企业级的数据访问体系，真正成熟的标志，不只是“功能正确”，而是“出了问题也能快速定位并稳定修复”。

到这里，整个 tio-boot + jOOQ 系列已经从：

• 基础整合
• SQL 能力
• PostgreSQL 高级能力
• 企业治理能力

一路走到了：

• 测试
• 观测
• 诊断
• 排障

这套体系已经相当完整。