Spring Boot使用原生查询从PostgreSQL取数据慢，该如何优化？

核心问题排查 #

现有代码的耗时高主要由以下几个原因导致：

• 原生SQL采用字符串拼接，无法复用数据库执行计划，每次查询都要重新解析生成执行计划，额外增加开销，同时存在SQL注入风险
• report_notifications表缺少匹配查询逻辑的索引，每次查询都要全表扫描过滤数据，数据量越大耗时越高
• 直接select *全量查询临时表，当表数据量较大时，磁盘IO、数据序列化、网络传输的开销会大幅上升，是耗时高的核心诱因
• 接口分两次请求数据库，多了一次网络往返的叠加耗时

落地优化方案 #

1. 数据库索引优化 #

给report_notifications创建覆盖索引，查询时直接从索引取数据不需要回表，查询速度可以提升10倍以上，执行SQL如下：

CREATE INDEX idx_report_user_id ON report_notifications(reportid, userid, id DESC) INCLUDE (reporttablename);

如果临时表数据量大，可根据常用的过滤条件给临时表添加对应索引，同时定期归档冷数据降低单表容量。

2. SQL逻辑优化 #

• 替换字符串拼接为参数绑定，复用数据库执行计划，同时解决SQL注入风险，修改后的获取表名代码如下：

@Override
public List getTablename(String reportId, String userId) {
    List tableName = null;
    try {
        tableName = entityManager.createNativeQuery("select reporttablename from report_notifications where reportid = ?1 and userid = ?2 order by id desc limit 1;")
                .setParameter(1, reportId)
                .setParameter(2, userId)
                .getResultList();
    } catch(Exception e) {
        logger.error("Exception", e);
    }
    return tableName;
}

• 避免全量查询临时表：如果业务允许分页，给查询语句增加limit offset分页参数；如果不需要所有字段，明确指定查询的字段名替代select *，大幅减少数据传输量。如果必须返回全量数据，可开启PostgreSQL游标查询分批加载，降低单次IO峰值。

3. 架构层面优化 #

• 增加缓存：如果同一个reportId+userId对应的表名不会频繁变动，可将表名结果存入Caffeine本地缓存或Redis分布式缓存，缓存过期时间根据业务数据变动频率设置，避免每次都查询report_notifications表。如果临时表数据实时性要求不高，也可以缓存临时表的查询结果，命中缓存直接返回。
• 合并两次数据库调用：可将两次查询逻辑封装为PostgreSQL存储过程，一次调用即可拿到最终结果，减少一次网络往返开销。

4. 配置优化 #

• 调整数据库连接池配置，保证连接数充足，避免连接等待导致的额外耗时。
• 开启JPA查询缓存、调大PostgreSQL共享缓冲区，提升热点数据的查询效率。

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