springboot+druid实现慢查询(慢sql)日志记录并记录到指定文件

原创于 2025-06-17 17:45:00 发布 · 298 阅读

CC 4.0 BY-SA版权

文章标签：

引入druid依赖

            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.14</version>
            </dependency>

添加yml配置

spring:
  profiles:
      active: dev
  datasource:
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    name: test
    url: jdbc:oscar://192.168.3.99:2222/ST_TEST?currentSchema=SENTINEL_TEST&serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false&allowMultiQueries=true
    username: admin
    password: 123456
    druid:
      # 使用自己的驱动，我这个是神通数据库驱动
      driver-class-name: com.oscar.Driver
      maxActive: 20
      initialSize: 5
      maxWait: 60000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20
      # 关键修改，配置慢查询日志
      filter:
        stat:
          enabled: true
          # 慢SQL阈值（单位：毫秒）
          slow-sql-millis: 2000
          # 记录慢SQL日志
          log-slow-sql: true
          # 合并相同SQL的统计
          merge-sql: true

修改logback-spring.xml文件
xml文件添加以下慢sql日志记录配置，将慢sql日志输出到指定的slow-sql.log文件。

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="60 seconds" debug="false">
    <!-- 日志存放路径 -->
    <property name="log.path" value="logs" />
    <!-- 慢SQL日志文件配置 -->
    <appender name="slow_sql" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <file>${log.path}/slow-sql.log</file>
        <rollingPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedRollingPolicy">
            <fileNamePattern>${log.path}/slow-sql.%d{yyyy-MM-dd}.%i.log</fileNamePattern>
            <maxFileSize>100MB</maxFileSize>
            <maxHistory>30</maxHistory>
        </rollingPolicy>
        <encoder>
            <pattern>%date %level [%thread] %logger{35} - %msg%n</pattern>
        </encoder>
    </appender>

    <!-- Druid 只输出到 slow_sql 文件 -->
    <logger name="druid.sql.Statement" level="WARN" additivity="false">
        <appender-ref ref="slow_sql"/>
    </logger>

    <logger name="druid.sql" level="WARN" additivity="false">
        <appender-ref ref="slow_sql"/>
    </logger>

    <logger name="com.alibaba.druid" level="WARN" additivity="false">
        <appender-ref ref="slow_sql"/>
    </logger>
</configuration>

完成，感谢大家阅读。