From e7790134e4210ee69974fc158d13445e9f6277af Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期二, 04 八月 2026 20:43:02 +0800
Subject: [PATCH] Merge branch 'franchisee-user-perf' into 2.2
---
ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/feignClient/AppUserTagClient.java | 15 +++
ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/controller/TChargingOrderControllerTest.java | 16 +++-
docs/franchisee_order_indexes.sql | 60 +++++++++++++++
ruoyi-service/ruoyi-account/src/main/java/com/ruoyi/account/controller/TAppUserTagController.java | 28 ++++++
ruoyi-service/ruoyi-account/src/test/java/com/ruoyi/account/controller/TAppUserTagControllerTest.java | 70 +++++++++++++++++
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java | 24 +++---
ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/factory/AppUserTagFallbackFactory.java | 11 ++
7 files changed, 202 insertions(+), 22 deletions(-)
diff --git a/docs/franchisee_order_indexes.sql b/docs/franchisee_order_indexes.sql
new file mode 100644
index 0000000..e3e16a1
--- /dev/null
+++ b/docs/franchisee_order_indexes.sql
@@ -0,0 +1,60 @@
+-- 加盟商看板查询性能优化：t_charging_order 分片表复合索引（直连后端 MySQL 执行，不要走 MyCat 代理口）
+-- 库：charging_pile_order 实例：prototype 分片：t_charging_order_0..49（分片键 app_user_id）
+-- 背景：加盟商看板 franchiseeOrders 按 site_id IN + end_time 区间过滤，未走分片键，MyCat 广播到 50 片；
+-- 物理表无 site_id/end_time 索引时每片全表扫描，年/月范围查询可达数十秒。
+-- 该索引让每片走 (site_id, end_time) 范围扫描，同时受益接口：/app/franchisee/{charge-data,income,user,operation}
+-- ALTER TABLE ADD INDEX 支持在线执行（MySQL 5.6+），但建议业务低峰期执行，50 条可分批执行
+
+USE `charging_pile_order`;
+
+-- ========== t_charging_order 50 张 ==========
+ALTER TABLE `t_charging_order_0` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_1` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_2` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_3` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_4` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_5` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_6` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_7` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_8` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_9` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_10` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_11` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_12` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_13` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_14` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_15` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_16` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_17` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_18` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_19` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_20` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_21` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_22` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_23` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_24` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_25` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_26` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_27` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_28` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_29` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_30` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_31` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_32` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_33` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_34` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_35` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_36` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_37` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_38` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_39` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_40` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_41` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_42` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_43` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_44` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_45` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_46` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_47` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_48` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
+ALTER TABLE `t_charging_order_49` ADD INDEX `idx_site_id_end_time` (`site_id`, `end_time`), ALGORITHM=INPLACE, LOCK=NONE;
diff --git a/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/factory/AppUserTagFallbackFactory.java b/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/factory/AppUserTagFallbackFactory.java
index e9cad57..b5c41ef 100644
--- a/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/factory/AppUserTagFallbackFactory.java
+++ b/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/factory/AppUserTagFallbackFactory.java
@@ -10,6 +10,8 @@
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
+import java.util.List;
+
/**
*
@@ -24,14 +26,19 @@
public AppUserTagClient create(Throwable throwable) {
log.error("调用失败:{}", throwable.getMessage());
return new AppUserTagClient() {
-
-
+
+
@Override
public R<TAppUserTag> getUserTag(TAppUserTag appUserTag) {
return R.fail("获取用户标签关系失败：" + throwable.getMessage());
}
@Override
+ public R<List<TAppUserTag>> listByUserIds(List<Long> userIds) {
+ return R.fail("获取用户标签关系失败：" + throwable.getMessage());
+ }
+
+ @Override
public void addUserTag(TAppUserTag appUserTag) {
}
diff --git a/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/feignClient/AppUserTagClient.java b/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/feignClient/AppUserTagClient.java
index 4a4966a..4b036e5 100644
--- a/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/feignClient/AppUserTagClient.java
+++ b/ruoyi-api/ruoyi-api-account/src/main/java/com/ruoyi/account/api/feignClient/AppUserTagClient.java
@@ -8,13 +8,15 @@
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
+import java.util.List;
+
/**
* @author zhibing.pu
* @Date 2024/9/25 16:17
*/
@FeignClient(contextId = "AppUserTagClient", value = ServiceNameConstants.ACCOUNT_SERVICE, fallbackFactory = AppUserTagFallbackFactory.class)
public interface AppUserTagClient {
-
+
/**
* 获取用户标签关系
* @param appUserTag
@@ -22,7 +24,16 @@
*/
@PostMapping(value = "/t-app-user-tag/tags/getUserTag")
R<TAppUserTag> getUserTag(@RequestBody TAppUserTag appUserTag);
-
+
+ /**
+ * 按用户ID集合批量获取用户标签关系
+ * t_app_user_tag 分片键为 app_user_id，IN 查询可被 MyCat 精确路由
+ * @param userIds app用户ID集合
+ * @return
+ */
+ @PostMapping(value = "/t-app-user-tag/tags/listByUserIds")
+ R<List<TAppUserTag>> listByUserIds(@RequestBody List<Long> userIds);
+
/**
* 添加用户标签关系数据
* @param appUserTag
diff --git a/ruoyi-service/ruoyi-account/src/main/java/com/ruoyi/account/controller/TAppUserTagController.java b/ruoyi-service/ruoyi-account/src/main/java/com/ruoyi/account/controller/TAppUserTagController.java
index 54bd6b0..e05f2a4 100644
--- a/ruoyi-service/ruoyi-account/src/main/java/com/ruoyi/account/controller/TAppUserTagController.java
+++ b/ruoyi-service/ruoyi-account/src/main/java/com/ruoyi/account/controller/TAppUserTagController.java
@@ -18,6 +18,8 @@
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
+import java.util.ArrayList;
+import java.util.Collections;
import java.util.List;
/**
@@ -105,8 +107,30 @@
.eq(TAppUserTag::getUserTagId, appUserTag.getUserTagId()));
return R.ok(one);
}
-
-
+
+
+ /**
+ * 按用户ID集合批量获取用户标签关系
+ * t_app_user_tag 分片键为 app_user_id，IN 查询被 MyCat 精确路由；按 500 分批避免 IN 过大
+ * @param userIds app用户ID集合
+ * @return
+ */
+ @PostMapping(value = "/tags/listByUserIds")
+ public R<List<TAppUserTag>> listByUserIds(@RequestBody List<Long> userIds){
+ if (userIds == null || userIds.isEmpty()) {
+ return R.ok(Collections.emptyList());
+ }
+ List<TAppUserTag> result = new ArrayList<>();
+ for (int i = 0; i < userIds.size(); i += 500) {
+ List<Long> batch = userIds.subList(i, Math.min(i + 500, userIds.size()));
+ result.addAll(appUserTagService.lambdaQuery()
+ .in(TAppUserTag::getAppUserId, batch)
+ .list());
+ }
+ return R.ok(result);
+ }
+
+
/**
* 保存用户标签关系数据
* @param appUserTag
diff --git a/ruoyi-service/ruoyi-account/src/test/java/com/ruoyi/account/controller/TAppUserTagControllerTest.java b/ruoyi-service/ruoyi-account/src/test/java/com/ruoyi/account/controller/TAppUserTagControllerTest.java
new file mode 100644
index 0000000..43fc9bb
--- /dev/null
+++ b/ruoyi-service/ruoyi-account/src/test/java/com/ruoyi/account/controller/TAppUserTagControllerTest.java
@@ -0,0 +1,70 @@
+package com.ruoyi.account.controller;
+
+import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
+import com.ruoyi.account.api.model.TAppUserTag;
+import com.ruoyi.account.service.TAppUserTagService;
+import com.ruoyi.common.core.domain.R;
+import org.junit.jupiter.api.Test;
+import org.springframework.test.util.ReflectionTestUtils;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyCollection;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.verifyNoInteractions;
+import static org.mockito.Mockito.when;
+
+class TAppUserTagControllerTest {
+
+ @Test
+ void listByUserIdsReturnsEmptyWithoutTouchingServiceForNullOrEmptyInput() {
+ TAppUserTagService service = mock(TAppUserTagService.class);
+ TAppUserTagController controller = new TAppUserTagController();
+ ReflectionTestUtils.setField(controller, "appUserTagService", service);
+
+ R<List<TAppUserTag>> nullResult = controller.listByUserIds(null);
+ R<List<TAppUserTag>> emptyResult = controller.listByUserIds(Collections.emptyList());
+
+ assertTrue(nullResult.getData().isEmpty());
+ assertTrue(emptyResult.getData().isEmpty());
+ verifyNoInteractions(service);
+ }
+
+ @Test
+ void listByUserIdsChunksLargeInputByFiveHundredAndMergesResults() {
+ TAppUserTagService service = mock(TAppUserTagService.class);
+ TAppUserTagController controller = new TAppUserTagController();
+ ReflectionTestUtils.setField(controller, "appUserTagService", service);
+
+ LambdaQueryChainWrapper<TAppUserTag> chain = mock(LambdaQueryChainWrapper.class);
+ when(service.lambdaQuery()).thenReturn(chain);
+ when(chain.in(any(), anyCollection())).thenReturn(chain);
+
+ List<Long> userIds = new ArrayList<>();
+ for (int i = 0; i < 1001; i++) {
+ userIds.add((long) i);
+ }
+ TAppUserTag first = new TAppUserTag();
+ first.setAppUserId(0L);
+ TAppUserTag second = new TAppUserTag();
+ second.setAppUserId(500L);
+ TAppUserTag third = new TAppUserTag();
+ third.setAppUserId(1000L);
+ when(chain.list()).thenReturn(
+ Collections.singletonList(first),
+ Collections.singletonList(second),
+ Collections.singletonList(third));
+
+ List<TAppUserTag> result = controller.listByUserIds(userIds).getData();
+
+ assertEquals(3, result.size());
+ verify(service, times(3)).lambdaQuery();
+ }
+}
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java
index 3bd56f5..aede364 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java
@@ -396,23 +396,23 @@
R<List<TUserTag>> tagResult = userTagClient.getAllUserTag();
List<TUserTag> tags = tagResult == null || tagResult.getData() == null
? Collections.emptyList() : tagResult.getData();
+ // 单次批量拉取全部用户标签关系（t_app_user_tag 分片键 app_user_id 精确路由），
+ // 避免原先 标签×用户 双层循环逐个远程调用导致接口 40s+。
+ R<List<TAppUserTag>> relationResult = appUserTagClient.listByUserIds(new ArrayList<>(userIds));
+ List<TAppUserTag> relations = relationResult == null || relationResult.getData() == null
+ ? Collections.emptyList() : relationResult.getData();
+ Map<Integer, Set<Long>> usersByTag = relations.stream()
+ .filter(relation -> relation.getUserTagId() != null && relation.getAppUserId() != null)
+ .collect(Collectors.groupingBy(TAppUserTag::getUserTagId,
+ Collectors.mapping(TAppUserTag::getAppUserId, Collectors.toSet())));
Map<String, Long> counts = new LinkedHashMap<>();
for (TUserTag tag : tags) {
if (tag == null || tag.getId() == null || StringUtils.isBlank(tag.getName())) {
continue;
}
- long count = 0;
- for (Long userId : userIds) {
- TAppUserTag query = new TAppUserTag();
- query.setAppUserId(userId);
- query.setUserTagId(tag.getId());
- R<TAppUserTag> userTagResult = appUserTagClient.getUserTag(query);
- if (userTagResult != null && userTagResult.getData() != null) {
- count++;
- }
- }
- if (count > 0) {
- counts.put(tag.getName(), count);
+ Set<Long> taggedUsers = usersByTag.get(tag.getId());
+ if (taggedUsers != null && !taggedUsers.isEmpty()) {
+ counts.put(tag.getName(), (long) taggedUsers.size());
}
}
return counts.entrySet().stream()
diff --git a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/controller/TChargingOrderControllerTest.java b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/controller/TChargingOrderControllerTest.java
index 996e0bb..355d2b1 100644
--- a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/controller/TChargingOrderControllerTest.java
+++ b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/controller/TChargingOrderControllerTest.java
@@ -161,9 +161,14 @@
TAppUserTag oldUserTag = new TAppUserTag();
oldUserTag.setAppUserId(101L);
oldUserTag.setUserTagId(1);
- when(appUserTagClient.getUserTag(org.mockito.ArgumentMatchers.argThat(query ->
- query.getAppUserId().equals(101L) && query.getUserTagId().equals(1))))
- .thenReturn(R.ok(oldUserTag));
+ TAppUserTag duplicateOldUserTag = new TAppUserTag();
+ duplicateOldUserTag.setAppUserId(101L);
+ duplicateOldUserTag.setUserTagId(1);
+ TAppUserTag activeUserTag = new TAppUserTag();
+ activeUserTag.setAppUserId(101L);
+ activeUserTag.setUserTagId(2);
+ when(appUserTagClient.listByUserIds(org.mockito.ArgumentMatchers.anyList()))
+ .thenReturn(R.ok(Arrays.asList(oldUserTag, duplicateOldUserTag, activeUserTag)));
TChargingOrder firstOrder = new TChargingOrder();
firstOrder.setAppUserId(101L);
@@ -176,9 +181,12 @@
List<java.util.Map<String, Object>> result = ReflectionTestUtils.invokeMethod(controller,
"franchiseeUserTags", Arrays.asList(firstOrder, repeatOrder, untaggedOrder));
- assertEquals(1, result.size());
+ assertEquals(2, result.size());
assertEquals("老用户", result.get(0).get("name"));
assertEquals(1L, result.get(0).get("value"));
+ assertEquals("活跃用户", result.get(1).get("name"));
+ assertEquals(1L, result.get(1).get("value"));
+ verify(appUserTagClient).listByUserIds(org.mockito.ArgumentMatchers.anyList());
}
@Test
--
Gitblit v1.7.1