如何系统如何进行SQL监控-执行SQL分析打印

文章目录 简介使用高级自定义日志格式自定义日志输出配合logback或者log4j打印日志 配置总结 Github： github /p6spy/p6spy

官网： p6spy.readthedocs.io/en/latest/index.html

简介

p6spy 是一个用于 Java 应用程序的数据库监控工具，它可以拦截和记录应用程序与数据库之间的 SQL 查询语句和操作，以帮助开发人员进行性能分析、调试和监控。

数据库监控工具：p6spy 主要用于监控数据库操作，它可以记录应用程序与数据库之间的所有 SQL 查询、更新、删除等操作，以便开发人员进行分析和调试。透明代理：p6spy 以透明代理的方式工作，开发人员无需修改应用程序代码。它实现了 JDBC（Java Database Connectivity）的代理，通过这种方式拦截数据库操作。日志记录：p6spy 可以将捕获到的数据库操作记录到日志文件中，开发人员可以轻松查看这些记录以分析数据库的性能和应用程序的数据库交互情况。性能分析：通过记录每个 SQL 查询的执行时间和频率，p6spy 可以帮助开发人员识别潜在的性能问题，优化数据库查询，以提高应用程序的响应时间。配置灵活：p6spy 允许开发人员对其行为进行灵活的配置，包括日志记录级别、输出格式、日志文件路径等，以满足不同的监控和调试需求。支持多种数据库：p6spy 支持多种流行的数据库系统，包括MySQL、Oracle、PostgreSQL、Microsoft SQL Server等，因此可以用于各种不同的数据库应用程序。 使用

1.项目中的pom引入相关依赖

<p6spy.version>3.9.1</p6spy.version> <dependency> <groupId>p6spy</groupId> <artifactId>p6spy</artifactId> <version>${p6spy.version}</version> </dependency>

2.切换项目中的jdbc驱动以及数据源

spring: datasource: driver-class-name: com.p6spy.engine.spy.P6SpyDriver url: jdbc:p6spy:mysql://localhost:3306/laker?useSSL=false&serverTimezone=GMT%2B8 # driver-class-name: com.mysql.cj.jdbc.Driver # url: jdbc:mysql://localhost:3306/laker?useSSL=false&serverTimezone=GMT%2B8 username: root password: 123456

3.在resource目录下添加spy.properties

#3.2.1以上使用 modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory #3.2.1以下使用或者不配置 #modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory # 自定义日志打印 logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger #日志输出到控制台 appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger # 使用日志系统记录 sql #appender=com.p6spy.engine.spy.appender.Slf4JLogger # 设置 p6spy driver 代理 deregisterdrivers=true # P6Log 模块执行时间设置，整数值 (以毫秒为单位)，只有当超过这个时间才停止记录 Log。 executionThreshold=10 # 取消JDBC URL前缀 useprefix=true # 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset. excludecategories=info,debug,result,commit,resultset # 日期格式 dateformat=yyyy-MM-dd HH:mm:ss # 实际驱动可多个 #driverlist=org.h2.Driver # 是否开启慢SQL记录 outagedetection=true # 慢SQL记录标准 2 秒 outagedetectioninterval=2 高级 自定义日志格式 public class CustomP6SpyLogger implements MessageFormattingStrategy { /** * Sql日志格式化 * * @param connectionId: 连接ID * @param now: 当前时间 * @param elapsed: 花费时间 * @param category: 类别 * @param prepared: 预编译SQL * @param sql: 最终执行的SQL * @param url: 数据库连接地址 * @return 格式化日志结果 */ @Override public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) { return StringUtils.isNotBlank(sql) ? " 耗时：" + elapsed + " ms " + now + "\n 执行 SQL：" + sql.replaceAll("[\\s]+", " ") + "\n" : ""; } }

在spy.properties中配置自定义日志格式

logMessageFormat=com.github.lybgeek.p6spy.extentsion.CustomP6SpyLogger

第二种是直接指定logMessageFormat的值，这样更加灵活，可以自己定义格式，比如：

logMessageFormat=%(currentTime) | SQL耗时： %(executionTime) ms | 连接信息： %(category)-%(connectionId) | 执行语句： %(sql) 可用的变量为: # %(connectionId) connection id # %(currentTime) 当前时间 # %(executionTime) 执行耗时 # %(category) 执行分组 # %(effectiveSql) 提交的SQL 换行 # %(effectiveSqlSingleLine) 提交的SQL 不换行显示 # %(sql) 执行的真实SQL语句，已替换占位 # %(sqlSingleLine) 执行的真实SQL语句，已替换占位 不换行显示 自定义日志输出 public class CustomStdoutLogger extends com.p6spy.engine.spy.appender.StdoutLogger{ @Override public void logText(String text) { System.out.println("sql:" + text); } }

在spy.properties中配置自定义日志输出

appender=com.github.lybgeek.p6spy.extentsion.CustomStdoutLogger

配合logback或者log4j打印日志

1.spy.properties配置

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

2.logback.xml配置

<!-- 定义p6spy sql监控 --> <logger name="p6spy" level="INFO"/> 配置

以下是P6Spy选项的整理表格：

配置项描述默认值模块modulelist模块列表，控制P6Spy的模块功能。只有列出的模块是活动的。默认模块driverlist要加载和注册的JDBC驱动程序的列表。空autoflush是否为每个语句刷新。falsedateformat日期格式，使用Java的SimpleDateFormat例程。空stacktrace是否为每个记录的语句打印堆栈跟踪。falsestacktraceclass如果stacktrace为true，则指定要打印的堆栈跟踪。空reloadproperties是否应重新加载属性文件。falsereloadpropertiesinterval重新加载属性的时间间隔（秒）。60appender用于记录的appender。com.p6spy.engine.spy.appender.FileLoggerlogfile要使用的日志文件的名称。注意Windows用户应使用正斜杠。spy.logappend是否追加到日志文件，如果为false，每次都会截断日志文件。truelogMessageFormat用于格式化日志消息的类。com.p6spy.engine.spy.appender.SingleLineFormatcustomLogMessageFormat自定义日志消息格式，仅当logMessageFormat设置为CustomLineFormat时使用。默认格式databaseDialectDateFormat用于记录java.util.Date实现的日期格式。yyyy-MM-dd’T’HH:mm:ss.SSSZdatabaseDialectTimestampFormat用于记录java.sql.Timestamp实现的日期格式。yyyy-MM-dd’T’HH:mm:ss.SSSZdatabaseDialectBooleanFormat用于记录布尔值的格式。booleandatabaseDialectBinaryFormat用于记录二进制数据的格式。如果excludebinary为true，则不适用。com.p6spy.engine.logging.format.HexEncodedBinaryFormatjmx是否通过JMX公开选项。truejmxPrefix如果通过JMX公开选项，用于命名的前缀。空useNanoTime是否以纳秒而不是毫秒测量执行时间。falserealdatasource要替换的真实DataSource类的JNDI名称。空realdatasourceclass真实DataSource类的类名。空realdatasourceproperties如果使用DataSource支持，定义需要设置的属性。空jndicontextfactoryJNDI上下文工厂类。空jndicontextproviderurlJNDI上下文提供者URL。空jndicontextcustomJNDI上下文的自定义属性。空filter是否过滤要记录的内容。falseinclude以逗号分隔的要包含的字符串列表。空exclude以逗号分隔的要排除的字符串列表。空sqlexpression用于评估是否使用正则表达式的SQL表达式。空excludecategories要排除的操作类别列表。info,debug,result,resultset,batchexcludebinary是否记录二进制值（传递到DB或检索的值）以占位符形式。falseexecutionThreshold执行阈值，只记录执行时间超过指定时间的语句(毫秒)。0outagedetection是否启用故障检测。falseoutagedetectioninterval故障检测的时间间隔（秒）。60 # 指定应用的日志拦截模块,默认为com.p6spy.engine.spy.P6SpyFactory #modulelist=com.p6spy.engine.spy.P6SpyFactory,com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory # 真实JDBC driver , 多个以 逗号 分割 默认为空 #driverlist= # 是否自动刷新 默认 flase #autoflush=false # 配置SimpleDateFormat日期格式 默认为空 #dateformat= # 打印堆栈跟踪信息 默认flase #stacktrace=false # 如果 stacktrace=true，则可以指定具体的类名来进行过滤。 #stacktraceclass= # 监测属性配置文件是否进行重新加载 #reloadproperties=false # 属性配置文件重新加载的时间间隔，单位:秒 默认60s #reloadpropertiesinterval=60 # 指定 Log 的 appender，取值： #appender=com.p6spy.engine.spy.appender.Slf4JLogger #appender=com.p6spy.engine.spy.appender.StdoutLogger #appender=com.p6spy.engine.spy.appender.FileLogger # 指定 Log 的文件名 默认 spy.log #logfile=spy.log # 指定是否每次是增加 Log，设置为 false 则每次都会先进行清空 默认true #append=true # 指定日志输出样式 默认为com.p6spy.engine.spy.appender.SingleLineFormat , 单行输出 不格式化语句 #logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat # 也可以采用 com.p6spy.engine.spy.appender.CustomLineFormat 来自定义输出样式, 默认值是%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine) # 可用的变量为: # %(connectionId) connection id # %(currentTime) 当前时间 # %(executionTime) 执行耗时 # %(category) 执行分组 # %(effectiveSql) 提交的SQL 换行 # %(effectiveSqlSingleLine) 提交的SQL 不换行显示 # %(sql) 执行的真实SQL语句，已替换占位 # %(sqlSingleLine) 执行的真实SQL语句，已替换占位 不换行显示 #customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine) # date类型字段记录日志时使用的日期格式 默认dd-MMM-yy #databaseDialectDateFormat=dd-MMM-yy # boolean类型字段记录日志时使用的日期格式 默认boolean 可选值numeric #databaseDialectBooleanFormat=boolean # 是否通过jmx暴露属性 默认true #jmx=true # 如果jmx设置为true 指定通过jmx暴露属性时的前缀 默认为空 # com.p6spy(.<jmxPrefix>)?:name=<optionsClassName> #jmxPrefix= # 是否显示纳秒 默认false #useNanoTime=false # 实际数据源 JNDI #realdatasource=/RealMySqlDS # 实际数据源 datasource class #realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource # 实际数据源所携带的配置参数 以 k=v 方式指定 以 分号 分割 #realdatasourceproperties=port;3306,serverName;myhost,databaseName;jbossdb,foo;bar # jndi数据源配置 # 设置 JNDI 数据源的 NamingContextFactory。 #jndicontextfactory=org.jnp.interfaces.NamingContextFactory # 设置 JNDI 数据源的提供者的 URL。 #jndicontextproviderurl=localhost:1099 # 设置 JNDI 数据源的一些定制信息，以分号分隔。 #jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.naming:org.jnp.interfaces # 是否开启日志过滤 默认false， 这项配置是否生效前提是配置了 include/exclude/sqlexpression #filter=false # 过滤 Log 时所包含的表名列表，以逗号分隔 默认为空 #include= # 过滤 Log 时所排除的表名列表，以逗号分隔 默认为空 #exclude= # 过滤 Log 时的 SQL 正则表达式名称 默认为空 #sqlexpression= #显示指定过滤 Log 时排队的分类列表，取值: error, info, batch, debug, statement, #commit, rollback, result and resultset are valid values # (默认 info,debug,result,resultset,batch) #excludecategories=info,debug,result,resultset,batch # 是否过滤二进制字段 # (default is false) #excludebinary=false # P6Log 模块执行时间设置，整数值 (以毫秒为单位)，只有当超过这个时间才进行记录 Log。 默认为0 #executionThreshold= # P6Outage 模块是否记录较长时间运行的语句 默认false # outagedetection=true|false # P6Outage 模块执行时间设置，整数值 （以秒为单位)），只有当超过这个时间才进行记录 Log。 默认60s # outagedetectioninterval=integer time (seconds) 总结

p6spy 在开发和测试阶段非常有用，可以帮助开发人员发现和解决SQL查询性能问题，但不适合在正式生产环境中广泛使用，因为它对性能有一定影响。