17370845950

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

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

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

解决方案：使用物化视图

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

具体步骤如下：

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

   例如，对于以下查询：

   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;

   可以创建如下物化视图：

   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 ...: 为物化视图的关键列创建索引，进一步提高查询效率。

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

   @Override
   public ResponsePagingDTO 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 responsePagingDTO = new ResponsePagingDTO<>();
   
       List 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 子句中的条件与原始查询保持一致，但直接作用于物化视图的列。

3. 定时刷新物化视图： 使用 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: 启用该任务。

注意事项：

• 物化视图的维护成本： 每次刷新物化视图都会消耗一定的系统资源。因此，需要根据实际情况合理设置刷新频率。
• 数据一致性： 物化视图中的数据不是实时更新的，存在一定的延迟。如果对数据的实时性要求很高，则需要谨慎使用物化视图。
• 索引优化： 为物化视图的关键列创建索引，可以进一步提高查询效率。
• 存储空间： 物化视图会占用额外的存储空间，需要根据数据量和刷新频率合理规划存储空间。

总结：

通过使用物化视图和定时任务，可以有效地解决 Oracle JDBCTemplate 参数化查询性能慢的问题。这种方法通过预先计算并存储结果，显著提高了查询速度，尤其是在涉及复杂连接和过滤的查询中。然而，需要注意物化视图的维护成本和数据一致性问题，并根据实际情况进行合理配置。