MyBatis Plus 解决大数据量查询慢问题

方案：MySql自连表查询

添加Mapper自定义查询方法

1、MallOrderMapper文件

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jumi.microservice.entity.MallOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface MallOrderMapper extends BaseMapper<MallOrder> {
    @Select("<script>" +
            "SELECT a.* FROM `mall_order` AS a JOIN (SELECT id FROM `mall_order` WHERE 1=1 " +
            "<if test=\"orderNo!= null and orderNo!=''\"> AND order_no=#{orderNo}</if>" +
            "<if test=\"beginTime!= null and beginTime!=''\"> AND create_time <![CDATA[ >= ]]> #{beginTime}</if>" +
            "<if test=\"endTime!= null and endTime!=''\"> AND create_time <![CDATA[<= ]]> #{endTime}</if>" +
            "ORDER BY id DESC LIMIT #{pageNum},#{pageSize}" +
            ") AS b ON b.id=a.id" +
            "</script>")
    List<MallOrder> mallOrderList(@Param("orderNo") String orderNo,
                                  @Param("beginTime") String beginTime,
                                  @Param("endTime") String endTime,
                                  @Param("pageNum") Integer pageNum,
                                  @Param("pageSize") Integer pageSize
    );
}

2、MallOrderServiceImpl文件

@Service
public class MallOrderServiceImpl {
    @Resource
    private MallOrderMapper mallOrderMapper;
    
    public TableDataInfo<MallOrderListResponse> mallOrderList(MallOrderListRequest request) {
        QueryWrapper<MallOrder> queryWrapper = new QueryWrapper<>();
        String beginTime = "";
        String endTime = "";
        //订单号
        if (request.getOrderNo() != null && !"".equals(request.getOrderNo())) {
            queryWrapper.eq("order_no", request.getOrderNo());
        }
        //创建开始时间
        if (request.getBeginTime() != null && !"".equals(request.getBeginTime())) {
            beginTime = request.getBeginTime().replace("T00:00:00", "") + " 00:00:00";
            queryWrapper.ge("create_time", beginTime);
        }
        //创建结束时间
        if (request.getEndTime() != null && !"".equals(request.getEndTime())) {
            endTime = request.getEndTime().replace("T00:00:00", "") + " 23:59:59";
            queryWrapper.le("create_time", endTime);
        }
        queryWrapper.orderByDesc("id");
//        IPage<MallOrder> page = new Page<>(request.getPageNum(), request.getPageSize());
//        page = mallOrderMapper.selectPage(page, queryWrapper);
//        List<MallOrder> mallOrders = page.getRecords();
        List<MallOrder> mallOrders = mallOrderMapper.mallOrderList(request.getOrderNo(), beginTime, endTime, request.getPageNum() - 1, request.getPageSize());
        List<MallOrderListResponse> result = new ArrayList<>();
        for (MallOrder mallOrder : mallOrders) {
            MallOrderListResponse mallOrderListResponse = new MallOrderListResponse();
            BeanUtils.copyProperties(mallOrder, mallOrderListResponse);
            //付款方式对照
            MallPayTypeEnum mallPayTypeEnum = EnumUtil.getByCode(mallOrder.getPayCode(), MallPayTypeEnum.class);
            if (mallPayTypeEnum != null) {
                mallOrderListResponse.setPayCodeStr(mallPayTypeEnum.getMsg());
            }
            //订单状态对照
            MallOrderStatusEnum mallOrderStatusEnum = EnumUtil.getByCode(mallOrder.getOrderStatus(), MallOrderStatusEnum.class);
            if (mallOrderStatusEnum != null) {
                mallOrderListResponse.setOrderStatusStr(mallOrderStatusEnum.getMsg());
            }
            result.add(mallOrderListResponse);
        }
        TableDataInfo<MallOrderListResponse> dataInfo = new TableDataInfo<>();
        dataInfo.setRows(result);
        //dataInfo.setTotal(page.getTotal);
        dataInfo.setTotal(mallOrderMapper.selectCount(queryWrapper));
        return dataInfo;
    }
}

MYSQL百万级以上分页LIMIT速度太慢的优化方法