Spring Boot Java应用对接SQL Server的性能问题排查求助

我开发的Spring Boot Java应用从SQL Server取数时遇到性能问题，执行以下简单查询：

SELECT a, b, SUM(c)
FROM table
WHERE date = '2023-02-01' AND year = 2023
GROUP BY a, b

该查询仅返回12行数据，在SSMS中执行可立即得到结果，但通过Spring Boot应用（浏览器或Postman调用）时，耗时随机在3.5到10秒之间。

涉及的表约有800万行数据、14列，结构如下：

• 1个INT类型主键列
• 1个DATE类型列
• 2个DECIMAL(12,2)类型列
• 10个NVARCHAR(x)类型列

Spring Boot应用中使用标注@Query的原生SQL执行该查询。同事使用ASP.NET对接同一SQL Server时性能表现良好，推测为配置问题。

我已尝试以下方案：

1. 在application.properties中添加sendParametersAsUnicode=false
2. 复制表并将所有字符串列从NVARCHAR(x)改为VARCHAR(x)
3. 通过Postman追踪执行时间：99%以上耗时为传输时间
4. 为NVARCHAR(x)列添加@Nationalized注解
5. 查阅资料了解Spring Boot与SQL Server中NVARCHAR(x)和VARCHAR(x)的差异

现寻求可将执行时间从数秒缩短至远低于1秒的关键优化方案。

项目相关代码 #

application.properties #

spring.datasource.url=jdbc:sqlserver://10.191.144.180:1433;database=Spring;encrypt=true;trustServerCertificate=true;sendStringParametersAsUnicode=false
spring.datasource.username=username
spring.datasource.password=pw123456
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.jpa.open-in-view=false

Postman耗时截图 #

实体类 FSAGG.java #

@Entity
@Table(name="Fact_Snapshots_Agg")
public class FSAGG {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long id;
    Date filedate;
    String jahr;
    String a;
    String b;
    String d;
    String e;
    String f;
    String g;
    String h;
    float c;
    float i;
    String j;
    String k;
    // 含构造方法、getter和setter
}

资源类 FSAGGResource.java #

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.analytics_test.model.FSAGGTInterface;
import com.analytics_test.service.FSAGGService;

@RestController
@CrossOrigin
@RequestMapping("/FSAGG")
public class FSAGGResource {
    private final FSAGGService fsaggService;
    
    @Autowired
    public FSAGGResource(FSAGGService fsaggService) { this.fsaggService = fsaggService; }
    
    @GetMapping("/Actuals/Total/{jahr}/gesamt")
    public List<FSAGGTInterface> getActualsTotalGesamt(@PathVariable("jahr") String jahr) { return fsaggService.getActualsTotalGesamt(jahr); }
}

仓库类 FSAGGRepository.java #

package com.analytics_test.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.analytics_test.model.FSAGG;
import com.analytics_test.model.FSAGGTInterface;

public interface FSAGGRepository extends JpaRepository<FSAGG, Long> {
    
    // 修正原SQL的语法错误：SELECT需放在最前面
    @Query(value = "SELECT a as a, b as b, SUM(c) as c "
            + "FROM FSAGG WHERE filedate = '2023-02-01' AND year = :year "
            + "GROUP BY a, b", nativeQuery = true)
    List<FSAGGTInterface> getActualsTotalGesamt(@Param("year") String year);
}

服务类 FSAGGService.java #

package com.analytics_test.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import com.analytics_test.model.FSAGGTInterface;
import com.analytics_test.repository.FSAGGRepository;

@Component
@Service
public class FSAGGService {

    private final FSAGGRepository fsaggRepository;
    
    @Autowired
    public FSAGGService(FSAGGRepository fsaggRepository) { this.fsaggRepository = fsaggRepository; }
    
    public List<FSAGGTInterface> getStuff() { return fsaggRepository.getStuff(); }
    
    public List<FSAGGTInterface> getActualsTotalGesamt(String jahr) { return fsaggRepository.getActualsTotalGesamt(jahr); }
}

关键优化方案 #

1. 修复SQL语法错误 #

原仓库类中的SQL存在语法顺序错误，将SELECT前置后，避免JPA解析异常导致的额外延迟。

2. 创建覆盖索引 #

针对查询的过滤、分组、聚合字段创建覆盖索引，让数据库直接从索引获取数据，无需回表扫描：

CREATE NONCLUSTERED INDEX IX_Fact_Snapshots_Agg_Query ON Fact_Snapshots_Agg (filedate, year, a, b) INCLUDE (c);

该索引包含了查询所需的所有列，可大幅降低数据库查询耗时。

3. 匹配参数与字段类型 #

• 若表中year列是INT类型，实体类中jahr应改为Integer，避免字符串转整数的隐式转换导致索引失效。
• 将硬编码的日期字符串改为LocalDate类型参数传递，确保JPA正确绑定参数：

@Query(value = "SELECT a as a, b as b, SUM(c) as c "
        + "FROM FSAGG WHERE filedate = :date AND year = :year "
        + "GROUP BY a, b", nativeQuery = true)
List<FSAGGTInterface> getActualsTotalGesamt(@Param("date") LocalDate date, @Param("year") Integer year);

上层调用时传入LocalDate.of(2023,2,1)和2023作为参数。

4. 优化JDBC驱动配置 #

• 升级至最新版Microsoft SQL Server JDBC驱动（当前最新为12.4.x版本），修复旧版本的性能瓶颈。
• 在数据源URL中添加responseBuffering=adaptive，让驱动根据结果集大小自动调整缓冲策略，减少传输延迟：

spring.datasource.url=jdbc:sqlserver://10.191.144.180:1433;database=Spring;encrypt=true;trustServerCertificate=true;sendStringParametersAsUnicode=false;responseBuffering=adaptive

5. 精简数据映射 #

确保FSAGGTInterface是仅包含所需字段的投影接口，避免JPA加载实体的冗余字段：

public interface FSAGGTInterface {
    String getA();
    String getB();
    Float getC();
}

减少数据传输量和对象映射开销。

6. 调整连接池参数 #

优化HikariCP连接池配置，避免频繁创建连接的开销：

spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=20000

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