测试策略、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，建议固定形成这个流程：

从日志拿到 SQL
在数据库中执行 EXPLAIN ANALYZE
看是否走索引
看扫描行数和返回行数差距
判断瓶颈是扫描、排序、join 还是锁等待
再决定改 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 高级能力
企业治理能力

一路走到了：

测试
观测
诊断
排障

这套体系已经相当完整。