Oracle JDBC Template 性能优化：参数化查询速度慢的解决方案-java教程-PHP中文网

Oracle JDBC Template 性能优化：参数化查询速度慢的解决方案

霞舞

发布： 2025-10-17 10:49:14

原创

684人浏览过

oracle jdbc template 性能优化：参数化查询速度慢的解决方案

本文针对在使用 Spring Boot 的 JDBCTemplate 连接 Oracle 数据库时，参数化查询性能大幅下降的问题，提供了一种基于物化视图和定时任务的解决方案。通过创建物化视图并定期刷新，可以显著提升查询速度，解决因参数化查询导致的性能瓶颈。

在使用 Spring Boot 的 JDBCTemplate 操作 Oracle 数据库时，开发者可能会遇到一个常见的问题：直接在数据库控制台执行的 SQL 语句速度很快，但通过 JDBCTemplate 进行参数化查询时，性能却大幅下降。例如，一个查询在控制台只需几百毫秒，而通过 JDBCTemplate 加上参数后，耗时可能达到数分钟。

这种性能差异通常是由于 Oracle 数据库的执行计划优化器在处理参数化查询时，无法像处理静态 SQL 语句那样进行有效的优化。即使所有相关的列都已建立索引，也可能无法避免性能瓶颈。

解决方案：使用物化视图

一种有效的解决方案是利用 Oracle 的物化视图（Materialized View）。物化视图是一种预先计算并存储结果的数据库对象，可以显著提高查询速度，尤其是在涉及复杂连接和过滤的查询中。

具体步骤如下：

创建物化视图： 根据原始查询的逻辑，创建一个物化视图。确保物化视图包含所有需要的列，并使用适当的索引来优化查询。

例如，对于以下查询：
```
SELECT
    CLIENT_EXTRA_INFO.CLIENT_NUMBER,
    CLIENT_EXTRA_INFO.FULL_NAME
FROM
     CONTRACT
        JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID)
WHERE
    CLIENT_EXTRA_INFO.MBPHONE = :phone
  and CONTRACT.STATUS = 'ACTIVE'
  and CONTRACT.FLAG IN ('2', '5') FETCH  FIRST  :row ROWS ONLY;
```
登录后复制
可以创建如下物化视图：

网龙b2b仿阿里巴巴电子商务平台
本系统经过多次升级改造，系统内核经过多次优化组合，已经具备相对比较方便快捷的个性化定制的特性，用户部署完毕以后，按照自己的运营要求，可实现快速定制会费管理，支持在线缴费和退费功能财富中心，管理会员的诚信度数据单客户多用户登录管理全部信息支持审批和排名不同的会员级别有不同的信息发布权限企业站单独生成，企业自主决定更新企业站信息留言、询价、报价统一管理，分系统查看分类信息参数化管理，支持多样分类信息，

0

查看详情
```
CREATE MATERIALIZED VIEW MV_CLIENT_INFO
REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC(SYSDATE+1)
AS
SELECT
    CLIENT_EXTRA_INFO.CLIENT_NUMBER,
    CLIENT_EXTRA_INFO.FULL_NAME,
    CLIENT_EXTRA_INFO.MBPHONE,
    CONTRACT.STATUS,
    CONTRACT.FLAG,
    CONTRACT.CLIENTID
FROM
     CONTRACT
        JOIN CLIENT_EXTRA_INFO on (CONTRACT.CLIENTID = CLIENT_EXTRA_INFO.ID);

CREATE INDEX IDX_MV_CLIENT_INFO_PHONE ON MV_CLIENT_INFO (MBPHONE);
CREATE INDEX IDX_MV_CLIENT_INFO_STATUS ON MV_CLIENT_INFO (STATUS);
CREATE INDEX IDX_MV_CLIENT_INFO_FLAG ON MV_CLIENT_INFO (FLAG);
```
登录后复制
说明：
- CREATE MATERIALIZED VIEW MV_CLIENT_INFO: 创建名为 MV_CLIENT_INFO 的物化视图。
- REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC(SYSDATE+1): 指定物化视图的刷新方式为完全刷新（每次刷新都会重新计算整个视图），并设置首次刷新时间为当前时间，下次刷新时间为明天凌晨零点。可以根据实际需求调整刷新频率。
- AS SELECT ...: 定义物化视图的查询逻辑，与原始查询保持一致。
- CREATE INDEX ...: 为物化视图的关键列创建索引，进一步提高查询效率。

修改 JDBCTemplate 查询： 将 JDBCTemplate 中的原始查询替换为基于物化视图的查询。

@Override
public ResponsePagingDTO<RetailCustomerDTO> getDuplicateRetailCustomerWithPhoneNumber(DuplicatePhoneNumberRequest request) {
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
    mapSqlParameterSource.addValue("phone", request.getPhoneNumber());
    mapSqlParameterSource.addValue("row", request.getSize());
    String sql ="SELECT\n" +
    "    CLIENT_NUMBER,\n" +
    "    FULL_NAME\n" +
    "FROM\n" +
    "     MV_CLIENT_INFO\n" +
    "WHERE\n" +
    "    MBPHONE = :phone\n" +
    "  and STATUS = 'ACTIVE'\n" +
    "  and FLAG IN ('2', '5') FETCH  FIRST  :row ROWS ONLY";

    ResponsePagingDTO<RetailCustomerDTO> responsePagingDTO = new ResponsePagingDTO<>();

    List<RetailCustomerDTO> retailCustomerDTOS = new ArrayList<>();
    pulseOpsTemplateJdbc.query(sql, mapSqlParameterSource, (result -> {
        RetailCustomerDTO retailCustomer = new RetailCustomerDTO();
        retailCustomer.setClientNumber(result.getString(ClientConstant.CLIENT_NUM));
        retailCustomer.setFullName(result.getString(ClientConstant.FULL_NAME));
        retailCustomer.setPhoneNumber(request.getPhoneNumber());
        retailCustomerDTOS.add(retailCustomer);
    }));
    responsePagingDTO.setData(retailCustomerDTOS);
    return responsePagingDTO;
}

登录后复制

注意：

查询现在直接从 MV_CLIENT_INFO 物化视图中检索数据。
WHERE 子句中的条件与原始查询保持一致，但直接作用于物化视图的列。

定时刷新物化视图： 使用 Oracle 的调度器（Scheduler）创建一个定时任务，定期刷新物化视图。刷新频率取决于数据的更新频率和业务需求。例如，可以每天凌晨刷新一次。
```
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'REFRESH_MV_CLIENT_INFO',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'DBMS_MVIEW.REFRESH(''MV_CLIENT_INFO'', ''C'');',
        start_date      => TRUNC(SYSDATE+1),
        repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Refresh MV_CLIENT_INFO daily at 00:00'
    );
END;
/
```
登录后复制
说明：
- DBMS_SCHEDULER.CREATE_JOB: 创建一个名为 REFRESH_MV_CLIENT_INFO 的调度任务。
- job_type => 'PLSQL_BLOCK': 指定任务类型为 PL/SQL 块。
- job_action => 'DBMS_MVIEW.REFRESH(''MV_CLIENT_INFO'', ''C'');': 定义任务执行的 PL/SQL 代码，即刷新 MV_CLIENT_INFO 物化视图，'C' 表示完全刷新。
- start_date => TRUNC(SYSDATE+1): 设置任务的首次执行时间为明天凌晨零点。
- repeat_interval => 'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0': 设置任务的重复执行频率为每天凌晨零点。
- enabled => TRUE: 启用该任务。