以前曾经优化过一个前人留下代码,数据库查询结果,循环读取某个字段为索引查询其他字典表,读取结果取字段名赋值给原结构里扩展字段,看到这个代码就有点吐血
后来我就优化成系统起来先把字典表读出来放内存,即便是需要实时变化的,查询前更新字典列表内存也比循环里查数据库要好
今天又碰到一个代码查询,初步看来问题不大,用的框架查询,分页,结果只返回15条数据竟然花费1分多钟,看之前我就断言,肯定有优化空间
仔细研究后发现原来是主表数据在百万级,然后关联4个表,关联结果还在sql里计算和判断,最后我就用字典表提前查询,然后自定义sql分页查询主表,然后聚合主表关键字段查询关联表,然后循环把几个list合并,最后优化成2秒以内,好吧虽然还有进一步改进点,但也不想再费事了,这已经够用了。
下面是在mapper.xml里使用到的主表的sql语句配置
<sql id="listRideHistoryWhere">
<where>
<if test="query.mediaType != null and query.mediaType != '' ">
teer.media_type = #{query.mediaType}
</if>
</where>
</sql>
<select id="listRideHistory"
parameterType="com.model.RideHistoryQuery"
resultType="com.dto.TxnEntryExitRecordDto">
select * from txn_entry_exit_record teer
<include refid="listRideHistoryWhere"/>
order by teer.id DESC limit #{query.page.index},#{query.page.size}
</select>
<select id="pageRideHistory_COUNT" resultType="Long" useCache="false" parameterType="com.model.RideHistoryQuery">
SELECT COUNT(1) FROM txn_entry_exit_record teer
<include refid="listRideHistoryWhere"/>
</select>
下面是主程序逻辑
List<DictStation> dictStations= TmsCacheData.getInstance().getDictStations();
if(CollectionUtils.isEmpty(dictStations)){
dictStations=dictStationMapper.selectAllStation();
if(!CollectionUtils.isEmpty(dictStations)){
TmsCacheData.getInstance().setDictStations(dictStations);
}
}
if(rideHistoryQuery.getPage().getIndex()==null){
rideHistoryQuery.getPage().setIndex(0);
}
if(rideHistoryQuery.getPage().getCurrent()>0 && rideHistoryQuery.getPage().getIndex()==0){
rideHistoryQuery.getPage().setIndex(rideHistoryQuery.getPage().getCurrent()*rideHistoryQuery.getPage().getSize());
}
List<TxnEntryExitRecordDto> records=txnEntryExitRecordMapper.listRideHistory(rideHistoryQuery);
String tickets=records.stream().map(TxnEntryExitRecordDto::getMediaNumber).collect(Collectors.joining(","));
List<TxnTicketStub> ticketList=txnTicketStubMapper.selectByTicketNumber(tickets);
List<TxnFineStub> fineList=txnFineStubMapper.selectByTicketNumber(tickets);
for(TxnEntryExitRecordDto record:records){
DictStation dictStation=dictStations.stream().filter(e-> (e.getStationNumber().equals( record.getStationNumber()))).findFirst().orElse(null);
record.setStationName(dictStation);
if(record.getMediaType().equals("1")) {
TxnTicketStub txnTicketStub = ticketList.stream().filter(e -> (e.getTicketNumber().equals(record.getMediaNumber()))).findFirst().orElse(null);
if (txnTicketStub != null) {
record.setPrice(txnTicketStub.getActualPayment());
}
}else {
TxnFineStub txnFineStub = fineList.stream().filter(e -> (e.getTicketNumber().equals(record.getMediaNumber()))).findFirst().orElse(null);
if (txnFineStub != null) {
record.setPrice(txnFineStub.getFineAmount());
}
}
record.setContractType("");
}
page.setTotal(txnEntryExitRecordMapper.pageRideHistory_COUNT(rideHistoryQuery));
page.setRecords(records);
其中用到list的流用法,能把代码简化很多。