fix: 加盟商看板用户接口 40s 慢查询优化

longz

2026-08-04 7915fcba539cff47d312ea8043410af04bcbfd62

fix: 加盟商看板用户接口 40s 慢查询优化

- franchiseeUserTags 去掉 标签×用户 双层循环逐个 Feign 调用（N+1²），
  新增 account 批量接口 POST /t-app-user-tag/tags/listByUserIds
  （t_app_user_tag 分片键 app_user_id 精确路由，按 500 分批），单次拉取内存聚合
- AppUserTagClient/AppUserTagFallbackFactory 增加 listByUserIds
- 新增 docs/franchisee_order_indexes.sql：t_charging_order_0..49 加
  (site_id, end_time) 复合索引，消除加盟商订单查询 50 片全表扫描（需直连后端 MySQL 执行）
- 新增 TAppUserTagControllerTest，更新 TChargingOrderControllerTest

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

 docs/franchisee_order_indexes.sql

New file
@@ -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;

 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) {

            }

 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

 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

 ruoyi-service/ruoyi-account/src/test/java/com/ruoyi/account/controller/TAppUserTagControllerTest.java

New file
@@ -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();
    }
}

 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/controller/TChargingOrderController.java

@@ -374,23 +374,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()

 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