Spring Batch中如何配置Hibernate打印SQL并统计查询平均执行耗时

你可以从应用层、数据库层两个维度选择实现方案，以下是三种常用的可落地方式：

方案1：开启Hibernate内置统计功能 #

无需引入额外依赖，仅修改Hibernate配置即可实现统计：

1. 首先修改你现有Hibernate配置的hibernateProperties节点，新增两个属性：

<prop key="hibernate.generate_statistics">true</prop>
<!-- 按需设置慢查询阈值，单位毫秒，设为1即可记录所有执行的查询 -->
<prop key="hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS">1</prop>

2. 编写统计数据输出逻辑，可以绑定到Spring Batch作业的结束监听器中，每次作业执行完成后输出统计结果：

import org.hibernate.stat.Statistics;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class SqlStatisticsPrinter {
    @Autowired
    private SessionFactory sessionFactory;

    // 可在JobExecutionListener的afterJob方法中调用该方法
    public void printSqlMetrics() {
        Statistics stats = sessionFactory.getStatistics();
        // 遍历所有执行过的SQL查询
        String[] allQueries = stats.getQueries();
        for (String sql : allQueries) {
            long executeCount = stats.getQueryExecutionCount(sql);
            long totalTime = stats.getQueryExecutionTotalTime(sql);
            double avgTime = totalTime * 1.0 / executeCount;
            System.out.printf("SQL语句：%s %n执行次数：%d %n平均执行时间：%.2fms%n----------%n",
                    sql, executeCount, avgTime);
        }
        // 统计完成后可清空数据，避免内存占用过高
        stats.clear();
    }
}

方案2：数据源代理层统计（覆盖所有JDBC操作） #

如果你的应用除了Hibernate之外还有直接使用JdbcTemplate等操作数据库的场景，可以通过代理数据源实现全量SQL统计，以datasource-proxy为例：

1. 新增依赖：

<dependency>
    <groupId>net.ttddyy</groupId>
    <artifactId>datasource-proxy</artifactId>
    <version>1.8.1</version>
</dependency>

2. 修改现有数据源配置，将原有数据源包装为代理数据源：

<!-- 原有demoDataSource配置保持不变 -->
<bean id="demoDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
    <property name="url" value="jdbc:sqlserver://demo.database.windows.net:1433;database=sqldb;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;" />
    <property name="username" value="user1" />
    <property name="password" value="p@ssword1" />
</bean>

<!-- 新增代理数据源配置 -->
<bean id="proxyDataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
    <property name="dataSource" ref="demoDataSource" />
    <property name="listener">
        <bean class="net.ttddyy.dsproxy.listener.ChainListener">
            <property name="listeners">
                <list>
                    <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
                </list>
            </property>
        </bean>
    </property>
</bean>

3. 将sessionFactory、事务管理器中引用的数据源替换为proxyDataSource，后续可以通过QueryCountHolder.getGrandTotal()获取所有SQL的执行次数、总耗时，自行计算平均执行时间即可。

方案3：Azure SQL原生查询存储（无需修改应用代码） #

Azure SQL默认开启了查询存储功能，直接在数据库侧执行以下SQL即可获取全量查询的统计数据，适合生产环境使用：

SELECT 
    qt.query_sql_text AS sql_content,
    SUM(rs.execution_count) AS total_execution_count,
    AVG(rs.avg_duration / 1000) AS avg_execution_time_ms -- 原始单位为微秒，转换为毫秒
FROM 
    sys.query_store_query q
JOIN 
    sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN 
    sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
WHERE
    qt.query_sql_text NOT LIKE '%sys.query_store%' -- 过滤查询存储自身的查询语句
GROUP BY 
    qt.query_sql_text
ORDER BY 
    avg_execution_time_ms DESC;

内容的提问来源于stack exchange，提问作者One Developer