From 5b434085676fd5c701a4a9ed86047b2143cae2a1 Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期二, 04 八月 2026 20:09:21 +0800
Subject: [PATCH] fix: 退款列表查询优化（MyCat 分片下推/逆反查/时间筛选）
---
ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java | 93 +++++++++++++++++++++-------------------------
1 files changed, 43 insertions(+), 50 deletions(-)
diff --git a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java
index a8e326d..898022a 100644
--- a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java
+++ b/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\">"));
}
--
Gitblit v1.7.1