SpringBoot与P6Spy整合，实现数据库操作的SQL性能监控与敏感数据脱敏功能

Java知识日历

431人浏览 · 2025-08-21 08:30:20

Java知识日历 · 2025-08-21 08:30:20 发布

引入P6Spy是为了在不改动业务代码的前提下，增强我们对数据库交互的可观测性。帮助我们主动发现性能问题（如：慢查询），在开发阶段提供便利的调试信息，并通过自定义日志处理提供了一种基础的数据安全保护手段。这对于提升应用质量、保障系统稳定性和安全性具有重要意义！

为什么推荐使用P6Spy？

P6Spy通过JDBC驱动代理的方式工作，对应用程序代码完全透明。我们无需修改任何业务逻辑或数据访问代码，只需简单配置数据源 URL 和驱动类即可启用。
能捕获并记录应用程序发送到数据库的每一条 SQL 语句及其执行时间。这对于理解应用的实际数据库行为、排查问题和性能分析非常有价值。
P6Spy 允许我们设置慢查询阈值，当 SQL 执行时间超过这个阈值时，P6Spy 会特别标记这些查询。这使我们能够快速识别出性能瓶颈，优先优化那些对系统响应时间影响最大的 SQL 语句，而不是盲目地优化。
在开发和测试环境中，开启 P6Spy 可以让开发者实时看到执行的 SQL，这对于调试 ORM (如 JPA/Hibernate) 映射问题、验证查询逻辑是否正确非常有帮助。它能清晰地展示框架实际生成的 SQL，避免“黑盒”操作带来的困惑。
通过实现自定义的 MessageFormattingStrategy，我们可以在 SQL 语句被记录到日志之前，对其进行处理。例如：本例子代码中，我们利用正则表达式识别并替换了身份证号，实现了基础的日志脱敏功能。这在记录生产环境日志时，能有效防止敏感信息（如身份证号、手机号等）泄露，满足基本的安全和合规要求。

代码实操

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

        <!-- P6Spy dependency -->
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.9.1</version>
        </dependency>

spy.properties

“
src/main/resources/spy.properties

# 指定应用的日志系统 (使用SLF4J，与Spring Boot默认日志一致)
appender=com.p6spy.engine.spy.appender.Slf4JLogger

# 自定义日志消息格式类
logMessageFormat=com.example.demo.config.P6SpyLogger

# 设置要记录的SQL类别
filter=true
includeCategories=statement,prepared

# 启用慢查询检测 (可选，但日志格式化器中已手动实现)
# outageDetection=true
# outageDetectionInterval=1

# 排除某些表或SQL语句（可选）
# exclude=select 1

# 自定义驱动列表 (可选，但如果遇到驱动加载问题可尝试添加)
# driverlist=com.mysql.cj.jdbc.Driver

application.properties

“
请注意看清楚是使用 jdbc:p6spy:mysql:// 作为前缀

# MySQL Database configuration
# 注意使用 jdbc:p6spy:mysql:// 作为前缀
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/mydbtest_spy?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.username=root
spring.datasource.password=12345678

# JPA/Hibernate properties
spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect
# 在生产环境中应设置为 validate 或 none
spring.jpa.hibernate.ddl-auto=create-drop
# 禁用Hibernate自带的SQL显示，使用P6Spy
spring.jpa.show-sql=false
spring.jpa.properties.hibernate.format_sql=true

# 日志级别设置，方便查看P6Spy输出
logging.level.com.example.demo=DEBUG
logging.level.p6spy=DEBUG

P6SpyLogger

package com.example.demo.config;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 自定义 P6Spy 日志格式化策略
 * 实现慢查询日志记录和身份证字段过滤
 */
@Component
publicclass P6SpyLogger implements MessageFormattingStrategy {

    // 中国大陆18位身份证号正则表达式
    privatestaticfinal String ID_CARD_PATTERN = "\\d{17}[\\dXx]";
    // 用于替换身份证号的掩码
    privatestaticfinal String MASK = "****MASKED****";

    /**
     * 格式化P6Spy输出的消息
     * @param connectionId 数据库连接ID
     * @param now 当前时间字符串 (已弃用)
     * @param elapsed 执行耗时(毫秒)
     * @param category SQL类别 (statement, prepared, resultset等)
     * @param prepared 预编译SQL (如果可用)
     * @param sql 原始SQL
     * @param url 数据库URL
     * @return 格式化后的日志消息
     */
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String url) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");

        // 优先使用预编译SQL，因为它通常包含参数占位符，更清晰
        String effectiveSql = (prepared != null && !prepared.isEmpty()) ? prepared : sql;

        // 对SQL进行脱敏处理
        String maskedSql = maskIdCard(effectiveSql);

        // 构建日志消息
        StringBuilder logEntry = new StringBuilder();
        logEntry.append(dateFormat.format(new Date()))
                .append(" | took ")
                .append(elapsed)
                .append("ms");

        // 标记慢查询 (假设阈值为1000ms)
        if (elapsed > 1000) {
             logEntry.append(" [SLOW QUERY!]");
        }

        logEntry.append(" | ")
                .append(category)
                .append(" | connection ")
                .append(connectionId)
                .append(" | url ")
                .append(url)
                .append(System.lineSeparator())
                .append(maskedSql)
                .append(";");

        return logEntry.toString();
    }

    /**
     * 使用正则表达式查找并替换SQL中的身份证号码
     * @param sql 原始SQL字符串
     * @return 脱敏后的SQL字符串
     */
    private String maskIdCard(String sql) {
        if (sql == null || sql.isEmpty()) {
            return sql;
        }
        Pattern pattern = Pattern.compile(ID_CARD_PATTERN);
        Matcher matcher = pattern.matcher(sql);
        return matcher.replaceAll(MASK);
    }
}

Entity

package com.example.demo.entity;

import javax.persistence.*;

@Entity
@Table(name = "users")
publicclass User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "id_card")
    private String idCard; // 身份证字段

    // Getters and Setters
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getIdCard() {
        return idCard;
    }

    public void setIdCard(String idCard) {
        this.idCard = idCard;
    }
}

Repository

package com.example.demo.repository;

import com.example.demo.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
}

Service

package com.example.demo.service;

import com.example.demo.entity.User;
import com.example.demo.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
publicclass UserService {

    @Autowired
    private UserRepository userRepository;

    public List<User> findAllUsers() {
        // 模拟慢查询，休眠1.5秒
        try {
            Thread.sleep(1500);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
        return userRepository.findAll();
    }

    public User saveUser(User user) {
        return userRepository.save(user);
    }
}

Controller

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/users")
publicclass UserController {

    @Autowired
    private UserService userService;

    @GetMapping
    public List<User> getAllUsers() {
        return userService.findAllUsers();
    }

    @PostMapping
    public User createUser(@RequestBody User user) {
        return userService.saveUser(user);
    }
}

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

}

关注我，送Java福利

/**
 * 这段代码只有Java开发者才能看得懂！
 * 关注我微信公众号之后，
 * 发送:"666"，
 * 即可获得一本由Java大神一手面试经验诚意出品
 * 《Java开发者面试百宝书》Pdf电子书
 * 福利截止日期为2025年02月28日止
 * 手快有手慢没！！！
*/
System.out.println("请关注我的微信公众号：");
System.out.println("Java知识日历");