Spring Boot构建SQL Server多表海量数据查询REST API技术问询

核心问题分析 #

你的现有代码存在三个致命缺陷：

1. 内存过载：一次性加载所有Port表的275万条数据到内存，再对每条数据发起重复查询，直接导致JVM内存溢出
2. 低效查询：循环遍历每张Port表后，又对每个SerialNo重复查询同一张表，产生数百万次冗余SQL请求，拖垮数据库
3. 分页逻辑错误：按表内行分页会导致同一SerialNo的数据被拆分到不同页，造成返回数据不全

优化步骤 #

1. 数据库层索引优化 #

先给关键字段加索引，大幅提升查询速度：

-- 批量给所有Port表的SerialNo、CDate加索引
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = @sql + 'CREATE NONCLUSTERED INDEX IX_' + table_name + '_SerialNo_CDate ON ' + table_name + '(SerialNo, CDate);' + CHAR(13)
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' AND table_schema='dbo' AND table_name LIKE 'Port[0-9]%' AND table_name != 'PortSetting'
EXEC sp_executesql @sql

-- 给PortSetting表的ParameterName加索引，加速阈值关联
CREATE NONCLUSTERED INDEX IX_PortSetting_ParameterName ON PortSetting(ParameterName)

2. 重构查询逻辑：一次性聚合所有Port数据 #

放弃循环遍历每张表的方式，用UNION ALL合并所有Port表的查询，同时关联PortSetting直接计算Status：

WITH AllPortData AS (
    SELECT 
        SerialNo,
        JobNo,
        ParameterName,
        PortValue,
        -- 清洗SerialNo，处理空格、大小写不规范问题
        LTRIM(RTRIM(LOWER(SerialNo))) AS CleanedSerialNo
    FROM Port1 WHERE CDate > '2024-01-01'
    UNION ALL SELECT SerialNo, JobNo, ParameterName, PortValue, LTRIM(RTRIM(LOWER(SerialNo))) FROM Port2 WHERE CDate > '2024-01-01'
    -- 手动补充Port3到Port50，或用动态SQL自动生成
),
PortWithStatus AS (
    SELECT 
        a.SerialNo,
        a.JobNo,
        a.ParameterName,
        a.PortValue,
        -- 直接在SQL中计算Status，减少应用层逻辑
        CASE 
            WHEN a.PortValue BETWEEN p.LTL AND p.UTL THEN 'Ok'
            ELSE 'Not Ok'
        END AS Status
    FROM AllPortData a
    LEFT JOIN PortSetting p ON a.ParameterName = p.ParameterName
)
SELECT * FROM PortWithStatus

如果Port表数量多，可以用动态SQL生成UNION ALL部分，避免手动编写50行重复代码。

3. 应用层分页：按SerialNo分页，保证数据完整性 #

不要按行分页，而是先获取去重的SerialNo列表，再分页查询这些SerialNo的完整数据：

@Service
public class DynamicPortDataService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedJdbcTemplate;

    @PostConstruct
    public void init() {
        namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    }

    // 获取符合条件的总SerialNo数量
    public long getTotalSerialNoCount() {
        String countSql = "SELECT COUNT(DISTINCT LTRIM(RTRIM(LOWER(SerialNo)))) " +
                "FROM (" +
                "    SELECT SerialNo FROM Port1 WHERE CDate > '2024-01-01' " +
                "    UNION ALL SELECT SerialNo FROM Port2 WHERE CDate > '2024-01-01' " +
                "    -- 补充Port3到Port50 " +
                ") AS AllSerials";
        return jdbcTemplate.queryForObject(countSql, Long.class);
    }

    // 分页查询指定页的完整SerialNo数据
    public List<DynamicPortDataDTO> getPage(int pageNum, int pageSize) {
        // 1. 获取当前页的SerialNo列表
        String serialNoPageSql = "SELECT DISTINCT LTRIM(RTRIM(LOWER(SerialNo))) AS CleanedSerialNo " +
                "FROM (" +
                "    SELECT SerialNo FROM Port1 WHERE CDate > '2024-01-01' " +
                "    UNION ALL SELECT SerialNo FROM Port2 WHERE CDate > '2024-01-01' " +
                "    -- 补充Port3到Port50 " +
                ") AS AllSerials " +
                "ORDER BY CleanedSerialNo " +
                "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        
        List<String> serialNos = jdbcTemplate.queryForList(serialNoPageSql, 
                new Object[]{(pageNum-1)*pageSize, pageSize}, String.class);

        if (serialNos.isEmpty()) {
            return Collections.emptyList();
        }

        // 2. 查询这些SerialNo的所有参数数据
        String dataSql = "WITH AllPortData AS (" +
                "    SELECT SerialNo, JobNo, ParameterName, PortValue FROM Port1 WHERE CDate > '2024-01-01' AND LTRIM(RTRIM(LOWER(SerialNo))) IN (:serialNos) " +
                "    UNION ALL SELECT SerialNo, JobNo, ParameterName, PortValue FROM Port2 WHERE CDate > '2024-01-01' AND LTRIM(RTRIM(LOWER(SerialNo))) IN (:serialNos) " +
                "    -- 补充Port3到Port50 " +
                "), " +
                "PortWithStatus AS (" +
                "    SELECT " +
                "        a.SerialNo, " +
                "        a.JobNo, " +
                "        a.ParameterName, " +
                "        a.PortValue, " +
                "        CASE WHEN a.PortValue BETWEEN p.LTL AND p.UTL THEN 'Ok' ELSE 'Not Ok' END AS Status " +
                "    FROM AllPortData a " +
                "    LEFT JOIN PortSetting p ON a.ParameterName = p.ParameterName " +
                ") " +
                "SELECT * FROM PortWithStatus";

        Map<String, Object> params = new HashMap<>();
        params.put("serialNos", serialNos);

        // 3. 流式聚合数据到DTO，避免内存过载
        Map<String, DynamicPortDataDTO> resultMap = new HashMap<>();
        namedJdbcTemplate.query(dataSql, params, rs -> {
            String serialNo = rs.getString("SerialNo");
            DynamicPortDataDTO dto = resultMap.getOrDefault(serialNo, new DynamicPortDataDTO());
            if (dto.getSerialNo() == null) {
                dto.setSerialNo(serialNo);
                dto.setJobNo(rs.getString("JobNo"));
                dto.setParameters(new HashMap<>()); // 用Map存储参数，比List<Map>更省内存且查询更快
                resultMap.put(serialNo, dto);
            }
            ParameterStatus param = new ParameterStatus();
            param.setPortValue(rs.getObject("PortValue"));
            param.setStatus(rs.getString("Status"));
            dto.getParameters().put(rs.getString("ParameterName"), param);
            return null;
        });

        return new ArrayList<>(resultMap.values());
    }
}

// 优化后的DTO结构，更紧凑高效
@Data
class DynamicPortDataDTO {
    private String serialNo;
    private String jobNo;
    private Map<String, ParameterStatus> parameters;
}

@Data
class ParameterStatus {
    private Object portValue;
    private String status;
}

4. 关键优化点说明 #

• 消除冗余查询：一次性获取所有需要的数据，不再对每个SerialNo重复查询表
• 内存优化：用Map存储参数替代List
• 分页正确性：按SerialNo分页，确保每个SerialNo的完整数据都在同一页
• SerialNo清洗：在SQL中统一处理不规范的SerialNo，避免关联错误

5. 可选进阶优化 #

• 数据库视图：创建一个包含所有Port表数据的视图，简化应用层SQL编写
• 异步处理：如果需要导出全量数据，用异步任务分批处理，避免阻塞HTTP请求
• 缓存：对频繁查询的SerialNo数据进行缓存，提升重复查询性能

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