告别问号：借助p6Spy实现可执行SQL日志与性能瓶颈可视化分析

1. 为什么我们需要p6Spy？

在日常开发中，使用MyBatis、JPA等ORM框架时，最让人头疼的问题之一就是控制台打印的SQL语句总是带着一堆问号占位符。比如你可能会看到这样的输出：

SELECT * FROM users WHERE id = ? AND status = ?

这种语句既不能直接复制到数据库客户端执行，也无法直观地看到实际传入的参数值。更糟的是，当需要分析SQL性能问题时，我们往往需要知道完整的执行语句和具体的参数值才能准确判断问题所在。

我曾经在一个电商项目中遇到过这样的困扰：系统在促销活动期间频繁出现数据库响应缓慢的情况，但由于日志中的SQL都是带占位符的，我们花了整整两天时间才定位到是一个错误的分页查询导致的性能问题。如果当时能看到完整的SQL语句，可能半小时就能解决问题。

p6Spy就是为了解决这个痛点而生的。它通过拦截JDBC调用，能够输出完整的、可执行的SQL语句，并且还能记录每条SQL的执行时间，帮助我们快速发现性能瓶颈。实测下来，这个工具在开发调试阶段特别有用，能节省大量排查问题的时间。

2. p6Spy的工作原理

p6Spy的核心原理可以用一个生活中的例子来理解：想象你是一个快递员（应用程序），平时直接去快递公司（数据库）收发包裹（执行SQL）。现在来了一个快递代收点（p6Spy），你以后都要通过这个代收点来收发快递。代收点会记录下每个包裹的详细信息，然后再转交给真正的快递公司。

从技术实现上看，p6Spy主要通过以下几个步骤工作：

1. 数据源代理：p6Spy会创建一个代理数据源，替换应用原本使用的真实数据源
2. SQL拦截：当应用通过JDBC执行SQL时，请求会先被p6Spy拦截
3. 参数替换：p6Spy获取到预编译SQL和实际参数值，生成完整的可执行SQL
4. 日志记录：将完整SQL和执行时间等信息记录到日志
5. 请求转发：最后将请求转发给真实的数据库驱动执行

这种设计的好处是完全无侵入性，你不需要修改任何业务代码，只需要调整数据源配置就能启用p6Spy的功能。我在多个Spring Boot项目中实践过，基本上10分钟就能完成集成。

3. 快速集成p6Spy

3.1 添加依赖

首先需要在项目中引入p6Spy的依赖。以Maven项目为例：

<dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>3.9.1</version> </dependency>

如果你使用Gradle，可以这样配置：

implementation 'p6spy:p6spy:3.9.1'

3.2 修改数据源配置

接下来需要调整数据源配置，让p6Spy能够拦截JDBC调用。这里有两个关键修改：

1. 将JDBC驱动类改为com.p6spy.engine.spy.P6SpyDriver
2. 在原来的JDBC URL前加上p6spy:前缀

比如原来的MySQL配置可能是这样的：

spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/mydb

修改后应该变成：

spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/mydb

3.3 配置spy.properties

p6Spy的行为是通过spy.properties文件控制的，这个文件需要放在项目的resources目录下。下面是一个推荐的基础配置：

# 启用日志和超时检测模块 module.log=com.p6spy.engine.logging.P6LogFactory module.outage=com.p6spy.engine.outage.P6OutageFactory # 使用控制台输出日志 appender=com.p6spy.engine.spy.appender.StdoutLogger # 自定义日志格式 logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat customLogMessageFormat=%(currentTime) | 耗时 %(executionTime)ms | 连接 %(connectionId) | %(sql) # 排除不重要的日志类别 excludecategories=info,debug,result,resultset,batch # 设置真实数据库驱动 driverlist=com.mysql.jdbc.Driver # 开启慢SQL检测（超过2秒视为慢SQL） outagedetection=true outagedetectioninterval=2

这个配置会输出类似这样的日志：

2023-08-20 14:30:45 | 耗时 32ms | 连接 1 | SELECT * FROM users WHERE id = 123 AND status = 'ACTIVE';

4. 高级配置与定制

4.1 自定义日志格式

p6Spy默认的日志格式可能不符合你的需求，这时候可以自定义格式。有两种方式：

方式一：使用内置的CustomLineFormat

修改spy.properties：

logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat customLogMessageFormat=[%(currentTime)] 执行耗时: %(executionTime)ms | 操作类型: %(category) | SQL: %(sql)

方式二：实现MessageFormattingStrategy接口

如果你需要更复杂的格式，可以创建一个自定义类：

public class CustomP6SpyLogger implements MessageFormattingStrategy { @Override public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) { return String.format("[%s] [%s] [%dms] %s", now, category, elapsed, sql != null ? sql : prepared); } }

然后在配置中指定你的实现类：

logMessageFormat=com.your.package.CustomP6SpyLogger

4.2 多种日志输出方式

p6Spy支持多种日志输出目标：

1. 控制台输出（开发环境推荐）：

appender=com.p6spy.engine.spy.appender.StdoutLogger

2. 文件输出：

appender=com.p6spy.engine.spy.appender.FileLogger logfile=spy.log # 指定日志文件路径

3. Slf4j输出（生产环境推荐）：

appender=com.p6spy.engine.spy.appender.Slf4JLogger

4.3 慢SQL监控

p6Spy内置了慢SQL检测功能，可以帮我们快速发现性能问题：

# 开启慢SQL检测 outagedetection=true # 设置慢SQL阈值（单位：秒） outagedetectioninterval=2 # 慢SQL日志前缀（可选） outagedetectionmessage=Slow SQL detected:

当SQL执行时间超过设定阈值时，日志中会出现警告信息，方便我们快速定位问题。

5. 性能分析与优化实践

5.1 识别常见性能问题

通过p6Spy的日志，我们可以发现几种常见的性能问题：

1. N+1查询问题：在循环中执行大量相似查询
2. 全表扫描：缺少合适索引的查询
3. 大结果集查询：一次性获取过多数据
4. 复杂连接查询：多表关联导致的性能下降

我曾经遇到一个典型的案例：一个用户列表页面加载特别慢，通过p6Spy日志发现，原来是为每个用户都单独执行了一个查询权限的SQL。通过改为批量查询，性能提升了20倍。

5.2 结合可视化工具

虽然p6Spy的日志已经很直观，但对于大量SQL的分析，可视化工具会更高效。推荐几种组合方案：

1. ELK Stack：将p6Spy日志导入Elasticsearch，用Kibana做可视化分析
2. Prometheus + Grafana：通过自定义指标收集，实现SQL性能监控
3. Arthas：阿里开源的Java诊断工具，可以结合使用

5.3 生产环境注意事项

在生产环境使用p6Spy需要注意：

1. 性能开销：p6Spy会增加少量性能开销，建议只记录慢SQL
2. 日志量控制：避免记录过多SQL导致日志爆炸
3. 敏感信息：注意SQL中可能包含的敏感数据，做好脱敏处理

一个推荐的生成配置：

# 只记录慢SQL和错误 appender=com.p6spy.engine.spy.appender.Slf4JLogger outagedetection=true outagedetectioninterval=1 filter=true excludecategories=info,debug,result,resultset,batch

6. 常见问题与解决方案

在实际使用p6Spy的过程中，可能会遇到一些问题。下面分享几个我踩过的坑和解决方法：

问题一：SQL日志中出现乱码

解决方案：确保spy.properties中的编码设置正确

# 指定日志文件编码 logfileencoding=UTF-8

问题二：Spring Boot多数据源配置不生效

解决方案：需要为每个数据源单独配置p6Spy代理

@Bean @Primary @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource() { return DataSourceBuilder.create() .type(P6DataSource.class) .build(); }

问题三：Hibernate生成的SQL过于冗长

解决方案：在CustomLineFormat中使用%(sqlSingleLine)

customLogMessageFormat=%(sqlSingleLine)

问题四：日志中缺少参数值

检查是否使用了正确的驱动和URL格式，确保p6Spy能够正确拦截SQL。

经过多个项目的实践验证，p6Spy确实是一个简单但强大的SQL监控工具。它最大的价值在于让开发者能够看到真实的、可执行的SQL语句，而不是一堆带问号的模板。当你在深夜调试一个复杂的查询问题时，这种直观性显得尤为珍贵。