架构设计之慢SQL监控

1、慢SQL

慢SQL指查询执行时间超出合理阈值（如数百毫秒以上）的语句，常因索引缺失、复杂联表、全表扫描或数据量激增导致。其直接影响包括响应延迟飙升、吞吐量骤降、系统资源（CPU/IO）耗尽，甚至引发服务雪崩。

我们可以使用第三方的组件（如：p6spy）来记录sql的执行情况

为实现技术能力进阶，我们自主开发了一个组件实现慢 SQL 监控。该组件支持JDBC、MyBatis 及 JPA 的 SQL 执行过程进行无侵入拦截，通过代理技术捕获数据库操作。当 SQL 执行耗时超过预设阈值（如 500ms）时，组件自动触发日志记录，输出结构化监控信息，涵盖完整 SQL 语句（含参数占位符及实际值）、执行耗时等关键信息进行记录，为性能分析提供全链路数据支撑。

最终效果如下：

mysql

---------------------------
SQL: SELECT id,name,password,sex,age,phone,birthday FROM t_user WHERE id=? 
Parameters: [1]
Slow SQL: true
Execution Time: 2ms
---------------------------

mysql

CREATE TABLE `t_user` (
  `id` bigint NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sex` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `phone` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

2、定义可配置信息

java

package com.xx.datasource;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:16
 */
@Component
@ConfigurationProperties(prefix = "xx.sql")
@Data
public class SqlProperties {

    /**
     * 是否显示sql
     */
    private boolean showSql = true ;

    /**
     * 是否显示执行SQL动态参数
     */
    private boolean showParam = false ;

    /**
     * 是否显示慢SQL
     */
    private boolean slowSql = false ;

    /**
     * 慢SQL所执行时间的阈值 单位 毫秒
     */
    private int slowSqlThreshold = 1000 ;
}

3、配置文件

yaml

xx:
  sql:
    show_sql: true
    show_param: true
    slow_sql: true
    slow_sql_threshold: 1000 # 自己测试的时候可以调小一点 单位毫秒

4、自定义数据源

我们需要拦截DataSource#getConnection方法，因为JDBC中的Connection对象用于创建对应的Statement对象，这是拦截SQL语句执行的关键前提。

java

package com.xx.datasource;

import com.zaxxer.hikari.HikariDataSource;
import jakarta.annotation.Resource;

import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:18
 */
public class XxDataSource extends HikariDataSource {

    @Resource
    private SqlProperties sqlProperties ;

    @Override
    public Connection getConnection() throws SQLException {
        Connection connection = super.getConnection();
        // 判断只有开启了显示SQL功能，才会进行代理的创建（这里你也可以加入日志级别的判断）。我们使用的JDK动态代理实现，所以这里的核心是InvocationHandler对象。
        if (this.sqlProperties.isShowSql()) {
            return (Connection) Proxy.newProxyInstance(
                    getClass().getClassLoader(),
                    new Class<?>[] {Connection.class},
                    new ConnectionInvocationHandler(connection, sqlProperties)) ;
        }
        return connection ;
    }

}

首先，判断只有开启了显示SQL功能，才会进行代理的创建（这里你也可以加入日志级别的判断）。我们使用的JDK动态代理实现，所以这里的核心是InvocationHandler对象。

java

package com.xx.datasource;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:22
 */
public class ConnectionInvocationHandler implements InvocationHandler {

    private final Connection target;
    private final SqlProperties sqlProperties ;

    public ConnectionInvocationHandler(Connection target,
                                       SqlProperties slowSqlProperties) {
        this.target = target;
        this.sqlProperties = slowSqlProperties ;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        if (method.getName().equals("prepareStatement")) {
            PreparedStatement realStatement = (PreparedStatement) method.invoke(target, args) ;
            // 原始 SQL（带 ? 占位符）
            String rawSql = (String) args[0];
            // 返回代理的 PreparedStatement，并绑定原始 SQL
            return Proxy.newProxyInstance(
                    getClass().getClassLoader(),
                    new Class[]{PreparedStatement.class},
                    new PreparedStatementInvocationHandler(realStatement, rawSql, sqlProperties)
            );
        }
        return method.invoke(target, args);
    }

}

5、拦截PreparedStatement

通过拦截PreparedStatement对象相应的set方法来获取动态设置的执行参数以及拦截execute开头的方法来记录SQL执行的耗时情况。

java

package com.xx.datasource;

import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:19
 */
@Slf4j
public class PreparedStatementInvocationHandler implements InvocationHandler {

    private final PreparedStatement target;
    private final String rawSql;
    private final SqlProperties sqlProperties ;
    private final Map<Integer, Object> paramMap = new HashMap<>();

    public PreparedStatementInvocationHandler(PreparedStatement target,
                                              String rawSql, SqlProperties sqlProperties) {
        this.target = target;
        this.rawSql = rawSql;
        this.sqlProperties = sqlProperties ;
    }

    @Override
    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
        // 拦截所有 setXXX() 方法，保存参数值
        if (method.getName().startsWith("set")) {
            int paramIndex = (int) args[0] ;
            Object paramValue = args[1] ;
            paramMap.put(paramIndex, paramValue) ;
        }

        // 拦截 execute() 方法，记录完整 SQL 和 参数
        if (method.getName().startsWith("execute")) {
            String sql = sqlAndParams() ;
            if (this.sqlProperties.isSlowSql()) {
                long start = System.currentTimeMillis() ;
                Object ret = method.invoke(target, args) ;
                long duration = System.currentTimeMillis() - start ;
                if (duration > this.sqlProperties.getSlowSqlThreshold()) {
                    log.warn("{}", buildSlowLogMessage(this.rawSql, sqlParamValues(), duration)) ;
                } else {
                    log.info("Executed SQL: {}", sql) ;
                }
                return ret ;
            } else {
                log.info("Executed SQL: {}", sql) ;
            }
        }

        return method.invoke(target, args) ;
    }

    private String buildSlowLogMessage(String sql, List<String> params, long executionTime) {
        StringBuilder sb = new StringBuilder();
        sb.append("\n---------------------------\n") ;
        sb.append("SQL Execution Details\n");
        sb.append("---------------------------\n") ;
        sb.append("SQL: ").append(sql).append("\n") ;
        sb.append("Parameters: ").append(params.toString()).append("\n") ;
        if (this.sqlProperties.isSlowSql() && executionTime > this.sqlProperties.getSlowSqlThreshold()) {
            sb.append("Slow SQL: true\n") ;
            sb.append("Execution Time: ").append(executionTime).append("ms\n");
        }
        sb.append("---------------------------");
        return sb.toString();
    }

    private String sqlAndParams() {
        // 获取键的集合并排序
        List<Integer> sortedKeys = new ArrayList<>(paramMap.keySet());
        Collections.sort(sortedKeys);
        if (this.sqlProperties.isShowParam()) {
            return rawSql + ", Params: " + sortedKeys.stream()
                    .map(paramMap::get)
                    .map(Object::toString)
                    .collect(Collectors.toList())
                    .toString() ;
        }
        return rawSql ;
    }
    private List<String> sqlParamValues() {
        return new ArrayList<>(paramMap.keySet()).stream()
                .map(paramMap::get)
                .map(Object::toString)
                .collect(Collectors.toList());
    }

}

6、配置数据源

java

package com.xx.datasource;

import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:21
 */
@Configuration
public class DataSourceConfig {

    @Bean
    // 表示从配置文件中读取以 spring.datasource.hikari 开头的属性，自动绑定到 DataSourceBuilder 中。
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    XxDataSource dataSource(DataSourceProperties properties) {
        return (XxDataSource) DataSourceBuilder.create()
                .type(XxDataSource.class)
                .driverClassName(properties.getDriverClassName())
                .url(properties.getUrl())
                .username(properties.getUsername())
                .password(properties.getPassword())
                .build() ;
    }
}

我们还是使用系统提供的默认数据源配置，DataSourceProperties是Spring Boot底层自动配置的属性（spring.datasource下的配置）。

7、测试

1、User

java

package com.xx.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:14
 */
@TableName("t_user")
@Data
public class User implements Serializable {

    @TableId
    private Long id;
    private String name;
    private String password;
    private String sex;
    private Integer age;
    private String phone;
    private Date birthday;

}

2、UserMapper

java

package com.xx.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xx.entity.User;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:15
 */
public interface UserMapper extends BaseMapper<User> {

    @Select(value = "select * from t_user where name = #{name} or age = #{age}")
    List<User> queryUser(String name, Integer age) ;

}

3、TestController

java

package com.xx.controller;

import com.xx.entity.User;
import com.xx.mapper.UserMapper;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:03
 */
@RestController
public class TestController {

    @Resource
    private UserMapper userMapper;

    @GetMapping("/testCreate")
    public void testCreate() {
        User user = new User();
        user.setId(1L);
        user.setAge(19);
        user.setBirthday(new Date());
        user.setName("小薛科技");
        user.setPassword("123123");
        user.setPhone("1111111111");
        user.setSex("男");
        this.userMapper.insert(user);
    }

    @GetMapping("/testQuery")
    public void testQuery() {
        this.userMapper.selectById(1L);
    }

    @GetMapping("/testQueryByNameAndAage")
    public void testQueryByNameAndAage() {
        this.userMapper.queryUser("小薛科技", 11);
    }
}

8、启动类

java

package com.xx;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @Author: xueqimiao
 * @Date: 2025/5/29 14:13
 */
@SpringBootApplication
@MapperScan(basePackages = {"com.xx.mapper"})
public class SpringBootSlowSQLApplication {

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

}

9、完整application.yml

yaml

spring:
  application:
    name: slow_sql_record
---
logging:
  include-application-name: false
  pattern:
    dateformat: 21:mm
---
spring:
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/xx_db2025?serverTimezone=GMT%2B8&useSSL=false&characterEncoding=UTF-8&rewriteBatchedStatements=true&cachePrepStmts=true
    username: root
    password: mac_root
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      minimumIdle: 100
      maximumPoolSize: 100
      autoCommit: true
      idleTimeout: 30000
      poolName: PackHikariCP
      maxLifetime: 1800000
      connectionTimeout: 30000
      connectionTestQuery: SELECT 1


---
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl
  type-aliases-package: com.xx.entity
  mapper-locations:
  - classpath:mapper/*.xml
---
xx:
  sql:
    show_sql: true
    show_param: true
    slow_sql: true
    slow_sql_threshold: 1

10、完整pom

xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.4.4</version>
        <relativePath/>
    </parent>

    <artifactId>xx-spring-boot-slow-sql</artifactId>
    <description>慢SQL日志记录</description>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
            <version>3.5.10.1</version>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

架构设计之慢SQL监控 ​

1、慢SQL ​

2、定义可配置信息 ​

3、配置文件 ​

4、自定义数据源 ​

5、拦截PreparedStatement ​

6、配置数据源 ​

7、测试 ​

1、User ​

2、UserMapper ​

3、TestController ​

8、启动类 ​

9、完整application.yml ​

10、完整pom ​

架构设计之慢SQL监控

1、慢SQL

2、定义可配置信息

3、配置文件

4、自定义数据源

5、拦截PreparedStatement

6、配置数据源

7、测试

1、User

2、UserMapper

3、TestController

8、启动类

9、完整application.yml

10、完整pom