sql分析工具-p6spy

一，mybatisplus自带的性能分析插件在3.4后移除了，官方推荐用p6spy， SQL分析与打印 | MyBatis-Plus (baomidou.com)

二，完整的配置文件

server:
  port: 8080
  servlet:
    context-path: /ruleengine
spring:
  application:
    name: service-ruleengine
  cloud:
    nacos:
      discovery:
        server-addr: 10.96.0.102:8848
  

  


  main: 
    allow-bean-definition-overriding: true
  redis:
    host: 192.168.10.224
    password: WangYueQi@01
    port: 6381
    database: 0
    oauth-database: 5   
  datasource:
    username: carcredit
    password: Carcredit123
    url: jdbc:p6spy:mysql://192.168.10.224:3308/ruleengine?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT&useSSL=false
#    driver-class-name: com.mysql.jdbc.Driver
#    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
#    driver-class-name: net.sf.log4jdbc.DriverSpy
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 2
      min-idle: 2
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,slf4j
      connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
      stat-view-servlet:
        url-pattern: "/druid/*"
        allow: 127.0.0.1,192.168.163.1
        deny: 192.168.1.73
        reset-enable: false
        login-username: admin
        login-password: 123456
  servlet:
    multipart:
      max-file-size: 20MB
      max-request-size: 200MB
      
logging:
  level:
    root: INFO
    com.bpmbrain: ERROR

ribbon:  
 ReadTimeout: 60000  
 ConnectTimeout: 60000
hystrix:
  command:
    default:
      execution:
        isolation:
          thread:
            timeoutInMilliseconds:  60000

error:
  msg: errors.properties
mail:
  from: ittest@carcredit.com.cn
  auth: Aa2022
  host: smtp.exmail.qq.com


indicatorTableSync:
  callBackAddress: https://sit.carcredit.com.cn/ruleengine2/messageQueue/callRule

ruleJobGroupId: 21

xxl:
  job:
    executor:
      appname: job-executor-ruleengine
      ip:
      port: 9991
      logpath: /data/applogs/xxl-job/jobhandler
      logretentiondays: 30
      serviceName: xxl-job
      contextPath: /xxl-job-admin
      accessToken: XXXXYYYY
      
bpm:
  BusiNo: com.linkstec.lmspcom.lbpm_AddOrUpdDefBo 
  BusiNoName: BpmWebHook
  callBackUrl: lb://service-ruleengine
  callBackContext: /ruleengine/decisionTree/callNode
  endCallBackContext: /ruleengine/decisionTree/endCallNode
  callBackTranContext: /ruleengine/decisionTree/callTran
  execute: 
    exerNo: 2534
    exerName: 规则引擎专属账号
autoreject:
  scoreCardCode: pre_score_card_new

ruleLogPath: C:\\Users\\carcredit\\Desktop\\test\\ruleLog
decisionTreeLogPath: C:\\Users\\carcredit\\Desktop\\test\\decisiontreelog
decisionTreeTranLogPath: C:\\Users\\carcredit\\Desktop\\test\\decisionTreeTranLog

# ruleLogPath: /home/nas/ruleLog
# decisionTreeLogPath: /home/nas/decisiontreelog
# decisionTreeTranLogPath: /home/nas/decisionTreeTranLog

excel:
  templatePath: /home/nas/rulefile
  excelPath: /home/nas/rulefile


j2cache:
  config-location: /j2cache.properties


rule_back_url: https://sit.carcredit.com.cn/ruleengine

#mybatis-plus:
#  configuration:
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#  global-config:
#    db-config:
#      banner: false
#  # 3.5.x 已内置性能分析插件
#  interceptor:
#   performance:
#      max-time: 0      # 0 表示不限，单位毫秒
#      format: true     # 控制台打印 SQL + 耗时

decorator:
  datasource:
    p6spy:
      # 日志格式
      log-format: "\ntime:%(executionTime) || sql:%(sql)\n"
      # 自定义日志类
      logging: custom
      custom-appender-class: com.cfam.ruleengine.config.StdoutLogger

三，

spy.properties：

# 模块列表，根据版本选择合适的配置
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory

# 自定义日志格式
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger

# 日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger

# 取消JDBC驱动注册
deregisterdrivers=true

# 使用前缀
useprefix=true

# 排除的日志类别
excludecategories=info,debug,result,commit,resultset

# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss

# 实际驱动列表
# driverlist=org.h2.Driver

# 开启慢SQL记录
outagedetection=true

# 慢SQL记录标准（单位：秒）
outagedetectioninterval=2
driverlist=com.mysql.jdbc.Driver

# 过滤 flw_ 开头的表 SQL 打印
filter=true
exclude=flw_*

四：

StdoutLogger：

package com.cfam.ruleengine.config;

public class StdoutLogger extends com.p6spy.engine.spy.appender.StdoutLogger {
    public void logText(String text) {
        System.out.println(text );
    }
}