Talk about how to use p6spy for sql monitoring

foreword

1. Introduction to p6spy

P6Spy is a framework that seamlessly intercepts and logs database data without any code changes to existing applications. Through P6Spy, we can intercept SQL statements, which is equivalent to a logger of SQL statements, so that we can use it for related analysis, such as performance analysis

2. Implementation principle

p6spy hijacks the data source of the application. The application operation database actually calls the data source of p6spy. After p6spy hijacks the sql or hql statement that needs to be executed, he calls a realDatasource by himself, and then operates the database

3. Relevant official documents

github : https://github.com/p6spy/p6spy

Official website : https://p6spy.readthedocs.io/en/latest/index.html

p6spy uses

1. Introduce the relevant GAV to the pom in the project

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

2. Switch the jdbc driver and data source in the project

Change it to the following

 spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: ${DRIVER_CALSS_NAME:com.p6spy.engine.spy.P6SpyDriver}
        url: ${DATASOURCE_URL:jdbc:p6spy:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai}
        username: ${DATASOURCE_USERNAME:root}
        password: ${DATASOURCE_PWD:123456}

3. Add spy.properties to the resource directory

The configuration is as follows

 #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
logMessageFormat=com.github.lybgeek.p6spy.extentsion.CustomP6SpyLogger
#日志输出到控制台
#appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
appender=com.github.lybgeek.p6spy.extentsion.CustomStdoutLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消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

4. Custom log format [optional]

 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" : "";
    }
}

Configure custom log format in spy.properties

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

5. Custom log output [optional]

 public class CustomStdoutLogger extends com.p6spy.engine.spy.appender.StdoutLogger{

    @Override
    public void logText(String text) {
        System.out.println("sql:" + text);
    }
}

Configure custom log output in spy.properties

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

6. Test and observe console output

 sql: 耗时：1 ms 2022-05-10 11:38:34
 执行 SQL：SELECT id,username,password,fullname,mobile,email FROM t_user

Summarize

p6spy can analyze sql and optimize sql according to the execution efficiency of sql, but because p6spy will have a certain impact on performance, it is not suitable for use in a formal environment. In addition, for more detailed configuration of p6spy, you can view the following link
https://p6spy.readthedocs.io/en/latest/configandusage.html

demo link

https://github.com/lyb-geek/springboot-learning/tree/master/springboot-p6spy