fix: 充电桩订单列表无筛选时 MyCat 超时，改手动分页并去跨库 JOIN

longz

2026-08-05 25da561c48437b52a82829e61ce83d2f56026cec

fix: 充电桩订单列表无筛选时 MyCat 超时，改手动分页并去跨库 JOIN

/chargingOrder/list 无筛选（清空默认时间范围）必然 MyCat 超时，根因：
- mapper 带 IPage，分页拦截器生成全量 COUNT 无法下推到分片
- 列表 SQL 无条件跨库 LEFT JOIN app_user/site/partner + 最外层 ORDER BY，
  无分片键时 MyCat 需把 50 片全量拉回代理层排序/计数

修复（沿用 getRefundList 手动分页模式）：
- 去掉 IPage，改传 offset/pageSize，ORDER BY + LIMIT 内联下推
- 新增单表 chargingOrderCount（COUNT 可下推到各分片求和）
- 去掉不参与过滤的跨库 JOIN；手机号在 Service 层预解析为 userIds
  （app_user_id 即分片键可直接路由），phone/partnerName 由 Service 回填

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java

@@ -61,7 +61,9 @@
    List<TChargingOrder> getNoInvoicedOrder(@Param("appUserId") Long appUserId, @Param("month") String month,
                                            @Param("pageCurr") Integer pageCurr, @Param("pageSize") Integer pageSize);

    List<ChargingOrderVO> chargingOrder(@Param("pageInfo") PageInfo<ChargingOrderVO> pageInfo, @Param("req") ChargingOrderQuery dto, @Param("startTime1") String startTime1, @Param("startTime2") String startTime2, @Param("endTime1") String endTime1, @Param("endTime2") String endTime11);
    List<ChargingOrderVO> chargingOrder(@Param("req") ChargingOrderQuery dto, @Param("startTime1") String startTime1, @Param("startTime2") String startTime2, @Param("endTime1") String endTime1, @Param("endTime2") String endTime11, @Param("offset") long offset, @Param("pageSize") long pageSize);

    long chargingOrderCount(@Param("req") ChargingOrderQuery dto, @Param("startTime1") String startTime1, @Param("startTime2") String startTime2, @Param("endTime1") String endTime1, @Param("endTime2") String endTime11);

    Map<String, Object> chargingOrderBaseSummary(@Param("req") ChargingOrderQuery dto, @Param("startTime1") String startTime1, @Param("startTime2") String startTime2, @Param("endTime1") String endTime1, @Param("endTime2") String endTime11);


 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java

@@ -2693,12 +2693,28 @@
        String startTime2 = timeRange[1];
        String endTime1 = timeRange[2];
        String endTime2 = timeRange[3];
        PageInfo<ChargingOrderVO> pageInfo = new PageInfo<>(dto.getPageCurr(), dto.getPageSize());
        int pageCurr = dto.getPageCurr() == null ? 1 : dto.getPageCurr();
        int pageSize = dto.getPageSize() == null ? 50 : dto.getPageSize();
        PageInfo<ChargingOrderVO> pageInfo = new PageInfo<>(pageCurr, pageSize);
        Long userId = tokenService.getLoginUser().getUserid();
        SysUser sysUser = sysUserClient.getSysUser(userId).getData();
        Integer roleType = sysUser.getRoleType();
        setChargingOrderAuthSiteIds(dto, sysUser, userId, roleType);
        List<ChargingOrderVO> list = this.baseMapper.chargingOrder(pageInfo, dto, startTime1, startTime2, endTime1, endTime2);
        if (StringUtils.hasLength(dto.getPhone()) && (dto.getUserIds() == null || dto.getUserIds().isEmpty())) {
            List<Long> data = appUserClient.getUserIdsByPhone(dto.getPhone()).getData();
            if (data == null || data.isEmpty()) {
                pageInfo.setTotal(0L);
                pageInfo.setRecords(Collections.emptyList());
                return pageInfo;
            }
            dto.setUserIds(data);
        }
        // 手动分页：不传 IPage（避免分页拦截器生成无法下推的全量 COUNT），COUNT 与 ORDER BY + LIMIT 均由 SQL 下推到分片
        long offset = (long) (pageCurr - 1) * pageSize;
        long total = this.baseMapper.chargingOrderCount(dto, startTime1, startTime2, endTime1, endTime2);
        pageInfo.setTotal(total);
        List<ChargingOrderVO> list = total == 0L ? Collections.emptyList()
                : this.baseMapper.chargingOrder(dto, startTime1, startTime2, endTime1, endTime2, offset, pageSize);
        fillChargingOrderBalanceIncome(list);
        fillChargingOrderShareIncome(list);
        fillChargingOrderPageExtra(list, dto.getSiteIds(), sysUser, roleType);
@@ -2777,6 +2793,25 @@
        Map<Integer, Site> siteMap = pageSites.stream()
                .filter(item -> item.getId() != null)
                .collect(Collectors.toMap(Site::getId, item -> item, (a, b) -> a));
        // 列表 SQL 不再跨库 JOIN app_user/partner（避免分片表无筛选全量广播），手机号与合作商名在此回填
        Map<Long, String> phoneMap = new HashMap<>();
        List<Long> pageUserIds = list.stream().map(ChargingOrderVO::getAppUserId).filter(Objects::nonNull).distinct().collect(Collectors.toList());
        if (!pageUserIds.isEmpty()) {
            List<TAppUser> pageUsers = Optional.ofNullable(appUserClient.getUserByIds(pageUserIds).getData()).orElse(new ArrayList<>());
            for (TAppUser user : pageUsers) {
                if (user != null && user.getId() != null) {
                    phoneMap.put(user.getId(), user.getPhone());
                }
            }
        }
        Map<Integer, String> partnerNameMap = new HashMap<>();
        List<Integer> pagePartnerIds = siteMap.values().stream().map(Site::getPartnerId).filter(Objects::nonNull).distinct().collect(Collectors.toList());
        for (Integer partnerId : pagePartnerIds) {
            Partner partner = Optional.ofNullable(partnerClient.getPartnerById(partnerId).getData()).orElse(null);
            if (partner != null && partner.getId() != null) {
                partnerNameMap.put(partner.getId(), partner.getName());
            }
        }
        Map<Long, List<TChargingOrderAccountingStrategy>> strategyMap = orderIds.isEmpty() ? new HashMap<>() :
                chargingOrderAccountingStrategyService.lambdaQuery()
                        .in(TChargingOrderAccountingStrategy::getChargingOrderId, orderIds)
@@ -2794,12 +2829,14 @@
            if (roleType == 2 && chargingOrderVO.getSiteId() != null) {
                chargingOrderVO.setAuthRecord(Boolean.TRUE.equals(authRecordMap.get(chargingOrderVO.getSiteId())));
            }
            chargingOrderVO.setPhone(phoneMap.get(chargingOrderVO.getAppUserId()));
            chargingOrderVO.setCommissionAmount(chargingOrderVO.getRechargeAmount() != null ? chargingOrderVO.getRechargeAmount().multiply(new BigDecimal("0.006")) : new BigDecimal("0"));
            chargingOrderVO.setPlatFormMoney(chargingOrderVO.getRechargeAmount() != null ? chargingOrderVO.getRechargeAmount().multiply(new BigDecimal("0.006")).setScale(2, BigDecimal.ROUND_DOWN) : new BigDecimal("0"));
            chargingOrderVO.setUid(chargingOrderVO.getId() + "");
            Site site = siteMap.get(chargingOrderVO.getSiteId());
            if (site != null) {
                chargingOrderVO.setSiteName(site.getName());
                chargingOrderVO.setPartnerName(partnerNameMap.get(site.getPartnerId()));
            }
            TChargingGun gun = gunMap.get(chargingOrderVO.getChargingGunId());
            TChargingPile pile = pileMap.get(chargingOrderVO.getChargingPileId());

 ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml

@@ -103,15 +103,12 @@
        </if>
    </select>

    <select id="chargingOrder" resultType="com.ruoyi.order.api.vo.ChargingOrderVO">
        select t1.*,
        app_user.phone,
        partner.name as partnerName,
        IFNULL(t1.whitelist_electric_refund_amount, 0) + IFNULL(t1.whitelist_service_refund_amount, 0) as whitelistRefundAmount
        from t_charging_order t1
        left join `charging_pile_account`.`t_app_user` app_user on app_user.id = t1.app_user_id
        left join `charging_pile_service`.`t_site` site on site.id = t1.site_id
        left join `charging_pile_service`.`t_partner` partner on partner.id = site.partner_id
    <!--
        充电桩订单分页列表。手动分页（不传 IPage，避免分页拦截器生成无法下推的全量 COUNT）；
        ORDER BY + LIMIT 内联，MyCat 可下推到各分片。不跨库 JOIN app_user/site/partner（无筛选时会导致全量广播拉取），
        手机号在 Service 层预解析为 userIds（app_user_id 即分片键，可直接路由），phone/partnerName 由 Service 层回填。
    -->
    <sql id="ChargingOrderListWhere">
        where t1.del_flag = 0
        <if test="null != req.code and req.code!=''">
            and t1.code LIKE CONCAT('%',#{req.code},'%')
@@ -121,9 +118,6 @@
            <foreach collection="req.userIds" item="item" index="index" separator="," open="(" close=")">
                #{item}
            </foreach>
        </if>
        <if test="req.phone != null and req.phone != ''">
            and app_user.phone LIKE CONCAT('%',#{req.phone},'%')
        </if>
        <if test="null != req.siteIds and req.siteIds.size()>0">
            and t1.site_id in
@@ -173,8 +167,20 @@
        <if test="endTime1 != null and endTime1!=''">
            and (t1.end_time between #{endTime1} and #{endTime2})
        </if>
        and t1.del_flag = 0
    </sql>
    <select id="chargingOrder" resultType="com.ruoyi.order.api.vo.ChargingOrderVO">
        select t1.*,
        IFNULL(t1.whitelist_electric_refund_amount, 0) + IFNULL(t1.whitelist_service_refund_amount, 0) as whitelistRefundAmount
        from t_charging_order t1
        <include refid="ChargingOrderListWhere"/>
        order by t1.create_time desc
        limit #{offset}, #{pageSize}
    </select>
    <!-- 充电桩订单总数（列表口径，手动分页用）。单表广播，COUNT 可下推到各分片后由代理层求和。 -->
    <select id="chargingOrderCount" resultType="long">
        select count(*)
        from t_charging_order t1
        <include refid="ChargingOrderListWhere"/>
    </select>
    <select id="payOrderQuery" resultType="com.ruoyi.order.dto.PayOrderDto">
        SELECT o.*,i.phone FROM (