sql优化杂记——回表

			String end = DateUtil.getDate(DateUtil.YYYY_MM_DD_HH_MM_SS);
            String begin = DateUtil.getBeforeDay(new Date(),DateUtil.YYYY_MM_DD_HH_MM_SS);
            log.info("存管回调报文重试任务开始begin：{}，end：{}",begin,end);
            BatchCallbackRetryRecord first = batchCallbackRetryRecordMapper.getFirstByCreateTime(begin,end);
            if(first == null){
                log.info("无重试订单，直接返回");
                return new ReturnT<String>(200, OrderStatusEnum.SUCCESS.getCode());
            }
            BatchCallbackRetryRecord last = batchCallbackRetryRecordMapper.getLastByCreateTime(begin,end);

            Long firstId = first.getId();
            Long lastId = last.getId();
            log.info("存管回调报文重试,firstId:{},lastId:{}",firstId,lastId);
            Semaphore semaphore = new Semaphore(concurrency);
            if(firstId.equals(lastId)){
                jobHandlerService.depositoryCallback(first,semaphore);
            }
            while(firstId < lastId){
                List<BatchCallbackRetryRecord> list = batchCallbackRetryRecordMapper.selectById(firstId,firstId+count);
                if(list == null || list.size() == 0){
                    firstId = firstId + count;
                    continue;
                }
                for(BatchCallbackRetryRecord retryRecord : list){
                    if(retryRecord.getRetryTimes() >= notifyRetryTime){
                        continue;
                    }
                    jobHandlerService.depositoryCallback(retryRecord,semaphore);
                }
                firstId = firstId + count;
            }


            //查看当前可用许可数，等于concurrency结束
            while (semaphore.availablePermits() != concurrency){
                Thread.sleep(1000);
            }

	@Select({
            "select * from tb_batch_callback_retry_record where status = 'FAIL' and create_time > #{begin,jdbcType=VARCHAR} and create_time <= #{end,jdbcType=VARCHAR} limit 1"
    })
    @ResultMap("BaseResultMap")
    BatchCallbackRetryRecord getFirstByCreateTime(@Param("begin")String begin,@Param("end")String end);

	@Select({
            "select * from tb_batch_callback_retry_record where status = 'FAIL' and create_time > #{begin,jdbcType=VARCHAR} and create_time <= #{end,jdbcType=VARCHAR} order by id desc limit 1"
    })
    @ResultMap("BaseResultMap")
    BatchCallbackRetryRecord getLastByCreateTime(@Param("begin")String begin,@Param("end")String end);


@Select({
            "select * from tb_batch_callback_retry_record where status = 'FAIL' and id >= #{first,jdbcType=INTEGER} and id <= #{last,jdbcType=INTEGER}"
    })
    @ResultMap("BaseResultMap")
    List<BatchCallbackRetryRecord> selectById(@Param("first")long first,@Param("last")long last);

create_time字段没有索引，所以需要通过create_time找到第一条数据和最后一条数据。
再通过上面两个数据的id获取中间范围的数据集合，通过id找效率比较高。