Java项目添加慢SQL查询工具的实践指南_Java

日期：2026-02-28

场景：校园项目访问 oracle 数据库，部分查询慢，偶尔出现 hikari 连接池超时，需要定位慢 sql 并统计分析。

背景问题

项目在高并发或大数据量查询时，经常出现：

caused by: org.springframework.jdbc.cannotgetjdbcconnectionexception:
failed to obtain jdbc connection; request timed out after 30000ms

初步排查发现：

oracle 数据库连接池配置较小，慢 sql 导致连接占用过久
业务查询中存在 like、group by、分页等操作，大数据量下执行慢

目标：快速定位慢 sql，统计出现次数和耗时，便于优化。

工具选型

选择 p6spy + logback：

p6spy 拦截 jdbc 调用，透明记录 sql
可配置 慢 sql 阈值（executionthreshold）
性能损耗极小

spring boot 配置示例

spring:
  datasource:
    driver-class-name: com.p6spy.engine.spy.p6spydriver
    url: jdbc:p6spy:oracle:thin:@// **********  /orcl
    username: *********
    password: **********************

spy.properties：

appender=com.p6spy.engine.spy.appender.slf4jlogger
executionthreshold=1000      # 超过1秒才记录
logmessageformat=com.p6spy.engine.spy.appender.singlelineformat
resultsetloggable=false

logback.xml：

<appender name="slow_sql_file" 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>50mb</maxfilesize>
        <maxhistory>30</maxhistory>
    </rollingpolicy>
    <encoder>
        <pattern>%d{yyyy-mm-dd hh:mm:ss.sss} %msg%n</pattern>
    </encoder>
</appender>

<logger name="p6spy" level="info" additivity="false">
    <appender-ref ref="slow_sql_file"/>
</logger>

这样就可以单独输出慢 sql 到 slow-sql.log，不干扰 info.log、error.log。

分析思路

慢 sql 日志样例：

2026-02-28 17:03:13.617 1772269393616|1073|statement|connection 0|url jdbc:p6spy:oracle:thin:@//...|select count(*) ...

字段说明：

字段	含义
1073	sql 执行耗时（ms）
statement	sql 类型
connection 0	jdbc 连接编号
select ...	实际执行 sql

分析方法：

找出耗时 > 阈值的 sql
判断 sql 是否包含 like、group by 或分页
统计出现次数、最大耗时、平均耗时
结合连接池监控判断是否是连接占用导致超时

java 分析工具实现

可以直接在 java 环境运行，无需 python。

import java.io.*;
import java.util.*;
import java.util.regex.*;

public class slowsqlanalyzer {

    public static void main(string[] args) throws ioexception {
        if (args.length < 1) {
            system.out.println("usage: java slowsqlanalyzer <slow-sql.log>");
            return;
        }

        string logfile = args[0];

        // 正则匹配 p6spy sql 日志行
        pattern sqlpattern = pattern.compile("\\|\\d+\\|statement\\|.*?\\|(select|insert|update|delete).*", pattern.case_insensitive);

        map<string, sqlstats> statsmap = new hashmap<>();

        try (bufferedreader reader = new bufferedreader(new filereader(logfile))) {
            string line;
            while ((line = reader.readline()) != null) {
                string[] parts = line.split("\\|", 6);
                if (parts.length < 6) continue;
                int exectime;
                try {
                    exectime = integer.parseint(parts[1]);
                } catch (numberformatexception e) {
                    continue;
                }
                string sqltext = parts[5].trim();
                matcher matcher = sqlpattern.matcher(line);
                if (matcher.find()) {
                    // 用前200字符作为 key，避免重复太多
                    string key = sqltext.length() > 200 ? sqltext.substring(0, 200) : sqltext;
                    sqlstats s = statsmap.getordefault(key, new sqlstats(sqltext));
                    s.count++;
                    s.totaltime += exectime;
                    s.maxtime = math.max(s.maxtime, exectime);
                    s.like = s.like || sqltext.touppercase().contains("like");
                    s.groupby = s.groupby || sqltext.touppercase().contains("group by");
                    statsmap.put(key, s);
                }
            }
        }

        // 输出统计结果
        system.out.printf("%5s | %12s | %12s | %6s | %8s | %s%n",
                "次数", "平均耗时(ms)", "最大耗时(ms)", "like", "group by", "sql示例前200字符");
        system.out.println("--------------------------------------------------------------------------------------------------------");

        statsmap.values().stream()
                .sorted((a,b) -> long.compare(b.totaltime, a.totaltime))
                .foreach(s -> {
                    long avg = s.totaltime / s.count;
                    system.out.printf("%5d | %12d | %12d | %6b | %8b | %s%n",
                            s.count, avg, s.maxtime, s.like, s.groupby, s.sqlsnippet);
                });
    }

    static class sqlstats {
        string sqlsnippet;
        int count = 0;
        long totaltime = 0;
        long maxtime = 0;
        boolean like = false;
        boolean groupby = false;

        sqlstats(string sql) {
            this.sqlsnippet = sql;
        }
    }
}

执行方法：

javac slowsqlanalyzer.java
java slowsqlanalyzer /data/javaapp/rest-tonp-realization/logs/slow-sql.log

输出示例：

次数 | 平均耗时(ms) | 最大耗时(ms) | like | group by | sql示例前200字符
--------------------------------------------------------------------------------------------------------
3 | 1234 | 1567 | true | true | select ... from certification ...
2 | 1050 | 1200 | false| true | select ... from product_type_new ...