交易记录表优化方案

记录一次JPA分页查询优化方案

一、根据需求创建联合索引

alter table t_threeds_trans add index 索引名字 (create_date,acct_number,trans_status....);

二、List查询修改

/**
 * 使用原生SQL进行查询交易表数据
 * 根据条件查询出第一条数据的oid，然后根据oid进行分页查询
 * t.* 可使用对应字段代替
 * pageSize 为从第几条开始分页 计算方式为 (pageSize = 页码*5)
 */
select t.* from t_threeds_trans t where t.oid >= (
	select oid from t_threeds_trans where
        (:createDateStart is null or create_date >= :createDateStart)
        and (:createDateEnd is null or create_date <= :createDateEnd)
        and (:cardScheme is null or card_scheme >= :cardScheme)
        and (:transStatus is null or trans_status >= :transStatus)
        and ......
    order by oid limit :pageSize,1
)limit 5

三、Count查询

/**
 * 使用原生SQL进行查询交易表条数
 * 注意索引
 * 20W数据jpa查询时间为2s左右
 */
 select count(*) from t_threeds_trans where 
 	and (:createDateStart is null or create_date >= :createDateStart)
 	and (:createDateEnd is null or create_date <= :createDateEnd)
    and (:cardScheme is null or card_scheme >= :cardScheme)
    and (:transStatus is null or trans_status >= :transStatus)
    and ......
    
 /**
  * 使用JDBC查询总数
  * 20W数据查询时间为1s左右
  * 推荐使用
  */
  略...

四、Java修改分页方式

4.1 Dao层修改

//Dao层返回结果修改为List<ThreedsTrans>
@Query(value="参考上面List查询SQL",nativeQuery = true)
List<ThreedsTrans> findByCriteria2(@Param("createDateStart") String createDateStart,
                                   @Param("createDateEnd") String createDateEnd
                                   ..........);
//Dao层新增查询总数方法返回类型为int
@Query(value="参考上面Count查询SQL",nativeQuery = true)
int findCountByCriteria2(@Param("createDateStart") String createDateStart,
                         @Param("createDateEnd") String createDateEnd
                         ..........);

4.2 Service层修改

//Service层返回结果修改为List<ThreedsTrans>
List<ThreedsTrans> findByCriteria2(@Param("createDateStart") String createDateStart,
                                   @Param("createDateEnd") String createDateEnd
                                   ..........){
    return dao.findByCriteria2(createDateStart,createDateEnd......);
}
//Service新增查询总数方法返回类型为int
int findCountByCriteria2(@Param("createDateStart") String createDateStart,
                         @Param("createDateEnd") String createDateEnd
                         ..........){
    return dao.findCountByCriteria2(createDateStart,createDateEnd......);
}

4.3 Controller层修改

//手动塞入分页数据
List<ThreedsTrans> data = service.findByCriteria2(....);
int count = service.findCountByCriteria2(....);
Page<ThreedsTrans> transPage = new PageImpl<>(data,
                                              new PageRequest(form.getPageNumber(),application.getPageSize()),
                                              count);