聊聊如何利用p6spy進行sql監控

linyb極客之路發表於2022-06-28
SQL

前言

1、p6spy簡介

P6Spy 是一個框架，無需對現有應用程式進行任何程式碼更改，即可無縫攔截和記錄資料庫資料。通過 P6Spy 我們可以對 SQL 語句進行攔截，相當於一個 SQL 語句的記錄器，這樣我們可以用它來作相關的分析，比如效能分析

2、實現原理

p6spy將應用的資料來源給劫持了，應用運算元據庫其實在呼叫p6spy的資料來源，p6spy劫持到需要執行的sql或者hql之類的語句之後，他自己去呼叫一個realDatasource，再去運算元據庫

3、相關官方文件

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

官網：https://p6spy.readthedocs.io/en/latest/index.html

p6spy使用

1、在專案中的pom引入相關的GAV
  <dependency>
       <groupId>p6spy</groupId>
        <artifactId>p6spy</artifactId>
        <version>${p6spy.version}</version>
    </dependency>
2、切換專案中的jdbc驅動以及資料來源

改成如下內容

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、在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
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、自定義日誌格式【可選】
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
5、自定義日誌輸出【可選】
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
6、測試觀察控制檯輸出
sql: 耗時：1 ms 2022-05-10 11:38:34
 執行 SQL：SELECT id,username,password,fullname,mobile,email FROM t_user

總結

p6spy可以根據sql的執行效率分析sql對sql進行優化，但因為p6spy會對效能有一定影響，因此不適合在正式環境上使用。此外關於p6spy更詳細的配置可以檢視如下連結
https://p6spy.readthedocs.io/en/latest/configandusage.html

demo連結

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

相關文章