fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）

longz

2026-08-05 fbce3ff8d28f55fdc8e7c6e446534dcfa181f488

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
package com.ruoyi.order.mapper;
 
import org.junit.jupiter.api.Test;
 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.stream.Collectors;
 
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
 
class PayOrderQueryPerformanceMapperTest {
 
    private static String readMapper() throws IOException {
        InputStream input = PayOrderQueryPerformanceMapperTest.class.getClassLoader()
                .getResourceAsStream("mapper/order/TChargingOrderMapper.xml");
        assertNotNull(input);
        try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) {
            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=\"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"));
        assertFalse(payOrderQuery.contains("t_vip_order_refund"));
    }
 
    @Test
    void invoiceOrderViewRetainsLinkedOrdersAfterPaymentStatusChanges() throws IOException {
        String mapper = readMapper();
 
        String payOrderQuery = mapper.substring(
                mapper.indexOf("<select id=\"payOrderQuery\""),
                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"));
        assertEquals(2, countOccurrences(payOrderQuery,
                "a.payment_status = 2 OR #{data.invoiceId} IS NOT NULL"));
    }
 
    @Test
    void paymentOrderListUsesLatestPaymentTimeAsItsDefaultSort() throws IOException {
        String mapper = readMapper();
 
        String payOrderQuery = mapper.substring(
                mapper.indexOf("<select id=\"payOrderQuery\""),
                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 refundListBranchesPushLimitIntoEachShard() throws IOException {
        String mapper = readMapper();
 
        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}"));
    }
 
    @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 {
        String mapper = readMapper();
 
        String refundQuery = mapper.substring(
                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\">"));
    }
 
    private int countOccurrences(String text, String target) {
        int count = 0;
        int index = 0;
        while ((index = text.indexOf(target, index)) >= 0) {
            count++;
            index += target.length();
        }
        return count;
    }
}