fix: 退款列表查询优化（MyCat 分片下推/逆反查/时间筛选）

longz

2026-08-04 5b434085676fd5c701a4a9ed86047b2143cae2a1

 ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java

@@ -16,20 +16,22 @@

class PayOrderQueryPerformanceMapperTest {

    @Test
    void readsRefundStatusFromOrderTablesWithoutJoiningRefundDetails() throws IOException {
        InputStream input = getClass().getClassLoader()
    private static String readMapper() throws IOException {
        InputStream input = PayOrderQueryPerformanceMapperTest.class.getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);

        String mapper;
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            mapper = reader.lines().collect(Collectors.joining("\n"));
            return reader.lines().collect(Collectors.joining("\n"));
        }
    }

    @Test
    void readsRefundStatusFromOrderTablesWithoutJoiningRefundDetails() throws IOException {
        String mapper = readMapper();

        String payOrderQuery = mapper.substring(
                mapper.indexOf("<select id=\"payOrderQuery\""),
                mapper.indexOf("<select id=\"getRefundList\""));
                mapper.indexOf("<select id=\"getRefundListByOrderIds\""));
        assertEquals(3, countOccurrences(payOrderQuery, "a.refund_status as refund_status"));
        assertFalse(payOrderQuery.contains("t_charging_order_refund"));
        assertFalse(payOrderQuery.contains("t_shopping_order_refund"));
@@ -38,18 +40,11 @@

    @Test
    void invoiceOrderViewRetainsLinkedOrdersAfterPaymentStatusChanges() throws IOException {
        InputStream input = getClass().getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);

        String mapper;
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }
        String mapper = readMapper();

        String payOrderQuery = mapper.substring(
                mapper.indexOf("<select id=\"payOrderQuery\""),
                mapper.indexOf("<select id=\"getRefundList\""));
                mapper.indexOf("<select id=\"getRefundListByOrderIds\""));
        assertEquals(3, countOccurrences(payOrderQuery, "#{data.invoiceId} IS NOT NULL"));
        assertTrue(payOrderQuery.contains(
                "a.recharge_payment_status = 2 OR a.status = -1 OR #{data.invoiceId} IS NOT NULL"));
@@ -59,56 +54,54 @@

    @Test
    void paymentOrderListUsesLatestPaymentTimeAsItsDefaultSort() throws IOException {
        InputStream input = getClass().getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);

        String mapper;
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }
        String mapper = readMapper();

        String payOrderQuery = mapper.substring(
                mapper.indexOf("<select id=\"payOrderQuery\""),
                mapper.indexOf("<select id=\"getRefundList\""));
                mapper.indexOf("<select id=\"getRefundListByOrderIds\""));
        assertTrue(payOrderQuery.contains(
                "ORDER BY COALESCE(o.pay_time, o.create_time, o.end_time) DESC, o.create_time DESC, o.id DESC"));
    }

    @Test
    void refundListAppliesPartnerSiteScopeInsidePagedQuery() throws IOException {
        InputStream input = getClass().getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);
    void refundListBranchesPushLimitIntoEachShard() throws IOException {
        String mapper = readMapper();

        String mapper;
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }
        String refundList = mapper.substring(
                mapper.indexOf("<select id=\"getRefundListPage\""),
                mapper.indexOf("<select id=\"countRefundList\""));
        // 每个 UNION 分支必须内联 ORDER BY + LIMIT（branchLimit=offset+pageSize），外层只做归并切页
        assertEquals(3, countOccurrences(refundList, "ORDER BY re.refund_time DESC"));
        assertEquals(3, countOccurrences(refundList, "LIMIT #{branchLimit})"));
        assertTrue(refundList.contains("LIMIT #{offset}, #{limit}"));
    }

        String refundQuery = mapper.substring(
                mapper.indexOf("<select id=\"getRefundList\""),
                mapper.indexOf("<select id=\"chargingOrderGroup\""));
        assertTrue(refundQuery.contains("data.siteIds"));
        assertTrue(refundQuery.contains("o.site_id IN"));
        assertEquals(3, countOccurrences(refundQuery, "as site_id"));
    @Test
    void refundListAppliesPartnerSiteScopeViaReverseLookup() throws IOException {
        String mapper = readMapper();

        // 合作商站点范围通过逆反查实现：先按 site_id 查订单 id，再按退款表分片键 IN 过滤
        String siteLookup = mapper.substring(
                mapper.indexOf("<select id=\"listChargingOrderIdsBySiteIds\""),
                mapper.indexOf("<select id=\"getRefundListPage\""));
        assertTrue(siteLookup.contains("site_id IN"));
        assertTrue(siteLookup.contains("ORDER BY id DESC"));

        String filteredList = mapper.substring(
                mapper.indexOf("<select id=\"getRefundListByOrderIds\""),
                mapper.indexOf("<select id=\"countRefundListByOrderIds\""));
        assertTrue(filteredList.contains("type1OrderIds"));
        assertTrue(filteredList.contains("re.charging_order_id IN"));
    }

    @Test
    void specialRefundListSkipsUnrelatedOrderUnions() throws IOException {
        InputStream input = getClass().getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);

        String mapper;
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }
        String mapper = readMapper();

        String refundQuery = mapper.substring(
                mapper.indexOf("<select id=\"getRefundList\""),
                mapper.indexOf("<select id=\"chargingOrderGroup\""));
        assertTrue(refundQuery.contains("WHERE re.refund_title = '特殊退款处理'"));
                mapper.indexOf("<select id=\"getRefundListPage\""),
                mapper.indexOf("<select id=\"countRefundList\""));
        assertTrue(refundQuery.contains("refund_title = '特殊退款处理'"));
        assertTrue(refundQuery.contains("<if test=\"data.specialRefund == null or !data.specialRefund\">"));
    }