Spring Boot JPA 打印慢查询 SQL 语句

开启配置

注意：配置的1000表示1000毫秒

spring:
  jpa:
    properties:
      hibernate:
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 1000

日志样例

2022-11-04 16:36:00.926  INFO 24952 --- [           main] org.hibernate.SQL_SLOW                   : SlowQuery: 4583 milliseconds. 
SQL: 'HikariProxyPreparedStatement@1971858114 wrapping prep1: 
insert into user (id, create_by, create_ts, update_by, update_ts, version, id_card, name) values (default, ?, ?, ?, ?, ?, ?, ?)
{1: 'unknown', 2: TIMESTAMP '2022-11-04 16:35:52.5019959', 3: 'unknown', 4: TIMESTAMP '2022-11-04 16:35:52.5019959', 5: CAST(0 AS BIGINT), 6: '123', 7: 'zzz'}'

对应源码

hibernate配置文件

public final class Environment implements AvailableSettings {
    // ...
}

public interface AvailableSettings extends org.hibernate.jpa.AvailableSettings {

    String SHOW_SQL ="hibernate.show_sql";
    String FORMAT_SQL ="hibernate.format_sql";
    String GENERATE_STATISTICS = "hibernate.generate_statistics";
    // 慢 SQL 配置
    String LOG_SLOW_QUERY = "hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS";

}

初始化时logSlowQuery从hibernate环境变量及配置中读取：

public class JdbcServicesImpl implements JdbcServices, ServiceRegistryAwareService, Configurable {

    @Override
    public void configure(Map configValues) {
        // ...
        final long logSlowQuery = ConfigurationHelper.getLong( Environment.LOG_SLOW_QUERY, configValues, 0 );

        this.sqlStatementLogger = new SqlStatementLogger( showSQL, formatSQL, highlightSQL, logSlowQuery );
        // ...
    }
}

从源码中看到：配置的值大于等于1时，会输出日志。

可以在日志中搜索SlowQuery: 关键字快速定位慢SQL。

public class SqlStatementLogger {
    private final long logSlowQuery;

    public SqlStatementLogger(boolean logToStdout, boolean format, boolean highlight, long logSlowQuery) {
        // ...
        this.logSlowQuery = logSlowQuery;
    }

    @AllowSysOut
    public void logSlowQuery(String sql, long startTimeNanos) {
        if ( logSlowQuery < 1 ) {
            return;
        }
        if ( startTimeNanos <= 0 ) {
            throw new IllegalArgumentException( "startTimeNanos [" + startTimeNanos + "] should be greater than 0!" );
        }

        long queryExecutionMillis = TimeUnit.NANOSECONDS.toMillis( System.nanoTime() - startTimeNanos );

        if ( queryExecutionMillis > logSlowQuery ) {
            String logData = "SlowQuery: " + queryExecutionMillis + " milliseconds. SQL: '" + sql + "'";
            LOG_SLOW.info( logData );
            if ( logToStdout ) {
                System.out.println( logData );
            }
        }
    }
}

更多配置

更多hibernate配置可查看AvailableSettings接口。

AvailableSettings位于org.hibernate.cfg包下。

参考

https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html