| ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 | |
| sql/large_screen_stat.sql | ●●●●● 补丁 | 查看 | 原始文档 | blame | 历史 |
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
@@ -207,6 +207,35 @@ Map<String, Object> getLargeScreenSummary(@Param("siteIds") List<Integer> siteIds); // ==================== 大屏预聚合表 t_large_screen_site_stat ==================== // 任务用: 按月切片聚合(与 getLargeScreenSummary 相同口径, 按站点分组) List<Map<String, Object>> monthSliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime); // 任务用: 按日切片聚合(与 getLargeScreenChargingPower 相同口径, 按站点分组) List<Map<String, Object>> daySliceAgg(@Param("startTime") String startTime, @Param("endTime") String endTime); // 任务用: 注册用户总数 Long globalRegisteredUsers(); // 任务用: 批量写入聚合行(REPLACE INTO) int replaceSiteStatBatch(@Param("rows") List<Map<String, Object>> rows); // 任务用: 表行数(判断是否需要启动回填) Long statTableCount(); // 接口用: 累计指标(M 行汇总) Map<String, Object> summaryFromStat(@Param("siteIds") List<Integer> siteIds); // 接口用: 近 N 月收入趋势(M 行按 period 分组) List<SixChargingDto> incomeTrendFromStat(@Param("siteIds") List<Integer> siteIds, @Param("sixBefore") String sixBefore); // 接口用: 日功率曲线(D 行按 period 分组) List<Map<String, Object>> powerFromStat(@Param("siteIds") List<Integer> siteIds, @Param("dayStart") String dayStart, @Param("dayEnd") String dayEnd); // 接口用: 注册用户数(G 行) Long registeredFromStat(); SixChargingDto getLargeScreenSettlementIncome(@Param("siteIdsCsv") String siteIdsCsv, @Param("startTime") String startTime, @Param("endTime") String endTime); ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/TChargingOrderService.java
@@ -320,6 +320,16 @@ List<LargeChargingPowerVo> getLargeScreenChargingPower(List<Integer> siteIds, LocalDateTime startTime, LocalDateTime endTime); /** * 重建大屏预聚合表(每日全量: 全部月份切片 + 近90天日切片 + 全局注册用户数) */ void rebuildLargeScreenStat(); /** * 刷新大屏预聚合表(每10分钟: 当月切片 + 今日日切片 + 全局注册用户数) */ void refreshLargeScreenStat(); List<Map<String, Object>> getHourType(List<Integer> siteIds, ChargingDetailQueryDto statisticsQueryDto);
@@ -103,6 +103,7 @@ import org.springframework.transaction.support.TransactionSynchronization; import org.springframework.transaction.support.TransactionSynchronizationManager; import org.springframework.transaction.support.TransactionTemplate; import org.springframework.util.DigestUtils; import org.springframework.util.StringUtils; import javax.annotation.Resource; @@ -111,6 +112,7 @@ import java.math.BigDecimal; import java.math.MathContext; import java.math.RoundingMode; import java.nio.charset.StandardCharsets; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.*; @@ -144,6 +146,12 @@ private static final int ENTERPRISE_INVOICE_BATCH_SIZE = 500; private static final int ENTERPRISE_ORDER_ID_BATCH_SIZE = 500; private static final int CHARGING_PERIOD_ACCOUNTING_BATCH_SIZE = 500; private static final String LARGE_SCREEN_CACHE_PREFIX = "large:screen:"; private static final long LARGE_SCREEN_CACHE_SECONDS = 60L; private static final LocalDate LARGE_SCREEN_STAT_DATA_START = LocalDate.of(2020, 1, 1); private static final DateTimeFormatter LARGE_SCREEN_MONTH_FMT = DateTimeFormatter.ofPattern("yyyy-MM"); private static final DateTimeFormatter LARGE_SCREEN_DAY_FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd"); private static final DateTimeFormatter LARGE_SCREEN_SQL_FMT = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); @Override public ReservationRecommendationVO getReservationRecommendation(Integer siteId) { @@ -6693,19 +6701,52 @@ @Override public Map<String, Object> getLargeScreenSummary(List<Integer> siteIds) { return this.baseMapper.getLargeScreenSummary(siteIds); String cacheKey = largeScreenCacheKey("summary", siteIds); Map<String, Object> cached = redisService.getCacheObject(cacheKey); if (cached != null) { return cached; } Map<String, Object> result = new HashMap<>(); try { Map<String, Object> summary = this.baseMapper.summaryFromStat(siteIds); if (summary != null) { result.putAll(summary); } Long registered = this.baseMapper.registeredFromStat(); result.put("registeredUserCount", registered == null ? 0L : registered); } catch (Exception e) { log.warn("大屏累计指标读取预聚合表失败, siteIds={}", siteIds, e); } Arrays.asList("validOrderCount", "chargingUserCount", "registeredUserCount", "totalChargingCapacity", "electricityFee", "serviceFee", "totalIncome") .forEach(key -> result.putIfAbsent(key, 0)); redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS); return result; } @Override public List<SixChargingDto> getLargeScreenIncomeTrend(List<Integer> siteIds, LocalDate sixBefore) { String siteIdsCsv = toSiteIdsCsv(siteIds); List<SixChargingDto> result = new ArrayList<>(); String cacheKey = largeScreenCacheKey("incomeTrend", siteIds); List<SixChargingDto> cached = redisService.getCacheObject(cacheKey); if (cached != null) { return cached; } LocalDate currentMonth = LocalDate.now().withDayOfMonth(1); List<SixChargingDto> result = new ArrayList<>(); try { String sixBeforeMonth = sixBefore.withDayOfMonth(1).format(LARGE_SCREEN_MONTH_FMT); List<SixChargingDto> incomeList = this.baseMapper.incomeTrendFromStat(siteIds, sixBeforeMonth); Map<String, SixChargingDto> incomeByMonth = new HashMap<>(); if (incomeList != null) { for (SixChargingDto income : incomeList) { if (income != null && income.getMonth() != null) { incomeByMonth.put(income.getMonth(), income); } } } LocalDate month = sixBefore.withDayOfMonth(1); while (!month.isAfter(currentMonth)) { LocalDate nextMonth = month.plusMonths(1); SixChargingDto income = this.baseMapper.getLargeScreenCurrentMonthIncome( siteIdsCsv, month.toString(), nextMonth.toString()); SixChargingDto income = incomeByMonth.get(month.format(LARGE_SCREEN_MONTH_FMT)); if (income == null) { income = new SixChargingDto(); } @@ -6717,34 +6758,186 @@ income.setServiceCharge(BigDecimal.ZERO); } result.add(income); month = nextMonth; month = month.plusMonths(1); } } catch (Exception e) { log.warn("大屏收入趋势读取预聚合表失败, siteIds={}, sixBefore={}", siteIds, sixBefore, e); LocalDate month = sixBefore.withDayOfMonth(1); while (!month.isAfter(currentMonth)) { SixChargingDto income = new SixChargingDto(); income.setMonth(String.format("%02d", month.getMonthValue())); income.setElectrovalence(BigDecimal.ZERO); income.setServiceCharge(BigDecimal.ZERO); result.add(income); month = month.plusMonths(1); } } redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS); return result; } @Override public List<LargeChargingPowerVo> getLargeScreenChargingPower(List<Integer> siteIds, LocalDateTime startTime, LocalDateTime endTime) { DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); String siteIdsCsv = toSiteIdsCsv(siteIds); String cacheKey = largeScreenCacheKey("power", siteIds); List<LargeChargingPowerVo> cached = redisService.getCacheObject(cacheKey); if (cached != null) { return cached; } List<LargeChargingPowerVo> result = new ArrayList<>(); LocalDateTime dayStart = startTime; while (dayStart.isBefore(endTime)) { LocalDateTime dayEnd = dayStart.plusDays(1); Map<String, Object> aggregate = this.baseMapper.getLargeScreenChargingPower( siteIdsCsv, dayStart.format(formatter), dayEnd.format(formatter)); BigDecimal totalPower = decimalValue(aggregate, "total_power"); BigDecimal orderCount = decimalValue(aggregate, "order_count"); try { String dayStart = startTime.toLocalDate().format(LARGE_SCREEN_DAY_FMT); String dayEnd = endTime.toLocalDate().format(LARGE_SCREEN_DAY_FMT); List<Map<String, Object>> dayAggs = this.baseMapper.powerFromStat(siteIds, dayStart, dayEnd); if (dayAggs != null) { for (Map<String, Object> agg : dayAggs) { BigDecimal totalPower = decimalValue(agg, "total_power"); BigDecimal orderCount = decimalValue(agg, "order_count"); BigDecimal power = orderCount.signum() == 0 ? BigDecimal.ZERO : totalPower.divide(orderCount, 0, RoundingMode.HALF_UP); LargeChargingPowerVo item = new LargeChargingPowerVo(); item.setTime(dayStart.toLocalDate().toString()); item.setPower(power == null ? BigDecimal.ZERO : power); Object day = agg.get("day"); item.setTime(day == null ? null : String.valueOf(day)); item.setPower(power); result.add(item); dayStart = dayEnd; } } } catch (Exception e) { log.warn("大屏功率曲线读取预聚合表失败, siteIds={}", siteIds, e); } redisService.setCacheObject(cacheKey, result, LARGE_SCREEN_CACHE_SECONDS, TimeUnit.SECONDS); return result; } @Override public void rebuildLargeScreenStat() { log.info("大屏预聚合表全量重建开始"); long start = System.currentTimeMillis(); // 1. 月度切片全量(数据起始月 ~ 当前月), 每次切片一次查询, 避免单条大查询超时 LocalDate month = LARGE_SCREEN_STAT_DATA_START.withDayOfMonth(1); LocalDate currentMonth = LocalDate.now().withDayOfMonth(1); while (!month.isAfter(currentMonth)) { writeMonthSlice(month, month.plusMonths(1)); month = month.plusMonths(1); } // 2. 近90天日切片 LocalDate day = LocalDate.now().minusDays(89); LocalDate today = LocalDate.now(); while (!day.isAfter(today)) { writeDaySlice(day); day = day.plusDays(1); } // 3. 全局注册用户数 writeGlobal(); log.info("大屏预聚合表全量重建完成, 耗时 {}ms", System.currentTimeMillis() - start); } @Override public void refreshLargeScreenStat() { long start = System.currentTimeMillis(); LocalDate currentMonth = LocalDate.now().withDayOfMonth(1); writeMonthSlice(currentMonth, currentMonth.plusMonths(1)); writeDaySlice(LocalDate.now()); writeGlobal(); log.info("大屏预聚合表刷新完成, 耗时 {}ms", System.currentTimeMillis() - start); } /** * 写入一个月度切片聚合(M 行) */ private void writeMonthSlice(LocalDate monthStart, LocalDate monthEnd) { List<Map<String, Object>> rows = this.baseMapper.monthSliceAgg( monthStart.atStartOfDay().format(LARGE_SCREEN_SQL_FMT), monthEnd.atStartOfDay().format(LARGE_SCREEN_SQL_FMT)); if (rows == null || rows.isEmpty()) { return; } List<Map<String, Object>> statRows = new ArrayList<>(); for (Map<String, Object> row : rows) { if (row == null || row.get("site_id") == null) { continue; } BigDecimal electricFee = decimalValue(row, "electric_fee"); BigDecimal serviceFee = decimalValue(row, "service_fee"); Map<String, Object> stat = new HashMap<>(); stat.put("statType", "M"); stat.put("period", monthStart.format(LARGE_SCREEN_MONTH_FMT)); stat.put("siteId", row.get("site_id")); stat.put("orderCount", decimalValue(row, "order_count")); stat.put("userCount", decimalValue(row, "user_count")); stat.put("capacity", decimalValue(row, "capacity")); stat.put("electricFee", electricFee); stat.put("serviceFee", serviceFee); stat.put("income", electricFee.add(serviceFee)); stat.put("totalPower", BigDecimal.ZERO); stat.put("registeredUserCount", 0L); statRows.add(stat); } if (!statRows.isEmpty()) { this.baseMapper.replaceSiteStatBatch(statRows); } } /** * 写入一个日切片聚合(D 行) */ private void writeDaySlice(LocalDate day) { List<Map<String, Object>> rows = this.baseMapper.daySliceAgg( day.atStartOfDay().format(LARGE_SCREEN_SQL_FMT), day.plusDays(1).atStartOfDay().format(LARGE_SCREEN_SQL_FMT)); if (rows == null || rows.isEmpty()) { return; } List<Map<String, Object>> statRows = new ArrayList<>(); for (Map<String, Object> row : rows) { if (row == null || row.get("site_id") == null) { continue; } Map<String, Object> stat = new HashMap<>(); stat.put("statType", "D"); stat.put("period", day.format(LARGE_SCREEN_DAY_FMT)); stat.put("siteId", row.get("site_id")); stat.put("orderCount", decimalValue(row, "order_count")); stat.put("userCount", BigDecimal.ZERO); stat.put("capacity", BigDecimal.ZERO); stat.put("electricFee", BigDecimal.ZERO); stat.put("serviceFee", BigDecimal.ZERO); stat.put("income", BigDecimal.ZERO); stat.put("totalPower", decimalValue(row, "total_power")); stat.put("registeredUserCount", 0L); statRows.add(stat); } if (!statRows.isEmpty()) { this.baseMapper.replaceSiteStatBatch(statRows); } } /** * 写入全局注册用户数(G 行) */ private void writeGlobal() { Long registered = this.baseMapper.globalRegisteredUsers(); Map<String, Object> stat = new HashMap<>(); stat.put("statType", "G"); stat.put("period", "ALL"); stat.put("siteId", 0); stat.put("orderCount", 0L); stat.put("userCount", 0L); stat.put("capacity", BigDecimal.ZERO); stat.put("electricFee", BigDecimal.ZERO); stat.put("serviceFee", BigDecimal.ZERO); stat.put("income", BigDecimal.ZERO); stat.put("totalPower", BigDecimal.ZERO); stat.put("registeredUserCount", registered == null ? 0L : registered); this.baseMapper.replaceSiteStatBatch(Collections.singletonList(stat)); } private String largeScreenCacheKey(String endpoint, List<Integer> siteIds) { String idPart = siteIds == null ? "all" : DigestUtils.md5DigestAsHex( siteIds.stream().sorted().map(String::valueOf).collect(Collectors.joining(",")) .getBytes(StandardCharsets.UTF_8)); return LARGE_SCREEN_CACHE_PREFIX + endpoint + ":" + idPart; } private BigDecimal decimalValue(Map<String, Object> values, String key) { if (values == null) { return BigDecimal.ZERO; ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/util/task/LargeScreenStatJob.java
New file @@ -0,0 +1,93 @@ package com.ruoyi.order.util.task; import com.ruoyi.order.mapper.TChargingOrderMapper; import com.ruoyi.order.service.TChargingOrderService; import lombok.extern.slf4j.Slf4j; import org.springframework.boot.context.event.ApplicationReadyEvent; import org.springframework.boot.web.context.WebServerInitializedEvent; import org.springframework.context.ApplicationListener; import org.springframework.context.event.EventListener; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import javax.annotation.Resource; /** * 大屏预聚合表 t_large_screen_site_stat 定时任务 * * 背景: /large-screen/* 接口原 SQL 在 MyCat 分表下按 site_id 过滤会广播扫描 * 50 张 t_charging_order 物理表且跨分片 join, 导致读超时。 * 本任务按"月/日/全局"切片把聚合结果写入预聚合表(全局表), 接口改为读该表: * 1. 每日 00:30 全量重建(按月切片逐片查询, 单次查询有界, 规避 MyCat 读超时) * 2. 每 10 分钟刷新当月/当日/全局行, 保证大屏当前月、当日数据近实时 * 3. 启动时检测表为空则异步全量回填, 避免发布后接口无数据 */ @Slf4j @Component public class LargeScreenStatJob implements ApplicationListener<WebServerInitializedEvent> { private Integer port; @Resource private TChargingOrderService chargingOrderService; @Resource private TChargingOrderMapper chargingOrderMapper; @Override public void onApplicationEvent(WebServerInitializedEvent event) { port = event.getWebServer().getPort(); } // 每日 00:30 全量重建 @Scheduled(cron = "0 30 0 * * ?") public void fullRebuild() { if (!TaskUtil.isScheduledTaskPort(port)) { return; } try { chargingOrderService.rebuildLargeScreenStat(); } catch (Exception e) { log.error("大屏预聚合表全量重建失败", e); } } // 每 10 分钟刷新当月/当日/全局 @Scheduled(fixedRate = 1000 * 60 * 10) public void refresh() { if (!TaskUtil.isScheduledTaskPort(port)) { return; } try { chargingOrderService.refreshLargeScreenStat(); } catch (Exception e) { log.error("大屏预聚合表刷新失败", e); } } // 启动时表空则异步全量回填, 不阻塞启动 @EventListener(ApplicationReadyEvent.class) public void backfillIfEmpty() { if (!TaskUtil.isScheduledTaskPort(port)) { return; } try { Long count = chargingOrderMapper.statTableCount(); if (count != null && count > 0) { return; } log.info("大屏预聚合表为空, 启动异步全量回填"); Thread backfill = new Thread(() -> { try { chargingOrderService.rebuildLargeScreenStat(); } catch (Exception e) { log.error("大屏预聚合表启动回填失败", e); } }, "large-screen-stat-backfill"); backfill.setDaemon(true); backfill.start(); } catch (Exception e) { log.error("大屏预聚合表启动回填检查失败", e); } } } ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
@@ -3120,4 +3120,128 @@ AND site_id IN (${siteIdsCsv}) </if> </select> <!-- ==================== 大屏预聚合表 t_large_screen_site_stat ==================== --> <!-- 任务用: 按月切片聚合, 口径与 getLargeScreenSummary 完全一致(按月边界裁剪) --> <select id="monthSliceAgg" resultType="java.util.Map"> SELECT charging_order.site_id AS site_id, COUNT(DISTINCT charging_order.id) AS order_count, COUNT(DISTINCT charging_order.app_user_id) AS user_count, COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS capacity, COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electric_fee, COALESCE(ROUND(SUM(CASE WHEN charging_order.order_source IN (0, 1) THEN accounting.period_service_price ELSE accounting.period_service_price * 0.8 END), 2), 0) AS service_fee FROM t_charging_order charging_order LEFT JOIN t_charging_order_accounting_strategy accounting ON accounting.charging_order_id = charging_order.id WHERE charging_order.del_flag = 0 AND charging_order.status = 5 AND charging_order.recharge_payment_status = 2 AND charging_order.create_time >= #{startTime} AND charging_order.create_time < #{endTime} GROUP BY charging_order.site_id </select> <!-- 任务用: 按日切片聚合, 口径与 getLargeScreenChargingPower 一致(无 status 过滤) --> <select id="daySliceAgg" resultType="java.util.Map"> SELECT site_id AS site_id, COUNT(1) AS order_count, COALESCE(SUM(IFNULL(power, 0)), 0) AS total_power FROM t_charging_order WHERE del_flag = 0 AND create_time >= #{startTime} AND create_time < #{endTime} GROUP BY site_id </select> <!-- 任务用: 注册用户总数 --> <select id="globalRegisteredUsers" resultType="java.lang.Long"> SELECT COUNT(*) FROM charging_pile_account.t_app_user WHERE del_flag = 0 </select> <!-- 任务用: 批量写入聚合行 --> <insert id="replaceSiteStatBatch"> REPLACE INTO t_large_screen_site_stat (stat_type, period, site_id, order_count, user_count, capacity, electric_fee, service_fee, income, total_power, registered_user_count, update_time) VALUES <foreach collection="rows" item="row" separator=","> (#{row.statType}, #{row.period}, #{row.siteId}, #{row.orderCount}, #{row.userCount}, #{row.capacity}, #{row.electricFee}, #{row.serviceFee}, #{row.income}, #{row.totalPower}, #{row.registeredUserCount}, NOW()) </foreach> </insert> <!-- 任务用: 表行数(启动回填判断) --> <select id="statTableCount" resultType="java.lang.Long"> SELECT COUNT(*) FROM t_large_screen_site_stat </select> <!-- 接口用: 累计指标(M 行汇总) --> <select id="summaryFromStat" resultType="java.util.Map"> SELECT COALESCE(SUM(order_count), 0) AS validOrderCount, COALESCE(SUM(user_count), 0) AS chargingUserCount, COALESCE(SUM(capacity), 0) AS totalChargingCapacity, COALESCE(SUM(electric_fee), 0) AS electricityFee, COALESCE(SUM(service_fee), 0) AS serviceFee, COALESCE(SUM(income), 0) AS totalIncome FROM t_large_screen_site_stat WHERE stat_type = 'M' <if test="siteIds != null and siteIds.size() > 0"> AND site_id IN <foreach collection="siteIds" item="siteId" open="(" separator="," close=")"> #{siteId} </foreach> </if> </select> <!-- 接口用: 近 N 月收入趋势(M 行按 period 分组, period 为 yyyy-MM 字典序可比) --> <select id="incomeTrendFromStat" resultType="com.ruoyi.order.dto.SixChargingDto"> SELECT period AS month, COALESCE(SUM(electric_fee), 0) AS electrovalence, COALESCE(SUM(service_fee), 0) AS service_charge FROM t_large_screen_site_stat WHERE stat_type = 'M' AND period >= #{sixBefore} <if test="siteIds != null and siteIds.size() > 0"> AND site_id IN <foreach collection="siteIds" item="siteId" open="(" separator="," close=")"> #{siteId} </foreach> </if> GROUP BY period </select> <!-- 接口用: 日功率曲线(D 行按 period 分组, period 为 yyyy-MM-dd 字典序可比) --> <select id="powerFromStat" resultType="java.util.Map"> SELECT period AS day, COALESCE(SUM(total_power), 0) AS total_power, COALESCE(SUM(order_count), 0) AS order_count FROM t_large_screen_site_stat WHERE stat_type = 'D' AND period >= #{dayStart} AND period < #{dayEnd} <if test="siteIds != null and siteIds.size() > 0"> AND site_id IN <foreach collection="siteIds" item="siteId" open="(" separator="," close=")"> #{siteId} </foreach> </if> GROUP BY period </select> <!-- 接口用: 注册用户数(G 行) --> <select id="registeredFromStat" resultType="java.lang.Long"> SELECT registered_user_count FROM t_large_screen_site_stat WHERE stat_type = 'G' AND period = 'ALL' AND site_id = 0 LIMIT 1 </select> </mapper> sql/large_screen_stat.sql
New file @@ -0,0 +1,91 @@ -- ============================================================================ -- 大屏 /large-screen/* 慢查询优化 SQL 脚本 -- 背景: /large-screen/summary 在 MyCat 分表下按 site_id 过滤会广播扫描 50 张 -- t_charging_order 物理表(无索引、无时间边界、跨分片 join),导致超时。 -- 方案: 新建站点级预聚合表 t_large_screen_site_stat(全局表),由后端定时任务 -- 按"月/日/全局"切片汇总写入,大屏接口改为读该表;本脚本为 50 张分表 -- 补充 create_time 索引,供聚合任务按月/按日切片范围扫描使用。 -- -- 执行方式: 在 MySQL 192.168.110.74 上直接执行(不要走 MyCat)。 -- 幂等: 可重复执行,不会报错。 -- ============================================================================ -- 1. 预聚合表(全局表,不分区) CREATE TABLE IF NOT EXISTS `charging_pile_order`.`t_large_screen_site_stat` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `stat_type` char(1) NOT NULL COMMENT '统计粒度:D=日 M=月 G=全局', `period` varchar(16) NOT NULL COMMENT 'D:yyyy-MM-dd / M:yyyy-MM / G:ALL', `site_id` int NOT NULL COMMENT '站点ID, G行固定为0', `order_count` bigint NOT NULL DEFAULT 0 COMMENT '订单数', `user_count` bigint NOT NULL DEFAULT 0 COMMENT '充电用户数(按站点统计, 跨站充电会重复计数)', `capacity` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '充电量(kWh)', `electric_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '电费', `service_fee` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '服务费(含三方0.8折算)', `income` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '收入(电费+服务费)', `total_power` decimal(16,2) NOT NULL DEFAULT 0.00 COMMENT '功率合计(仅D行使用)', `registered_user_count` bigint NOT NULL DEFAULT 0 COMMENT '注册用户数(仅G行使用)', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_stat_period_site` (`stat_type`,`period`,`site_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大屏站点统计预聚合表'; -- 2. 给 50 张 t_charging_order 物理分表补 create_time 索引(幂等,已存在则跳过) DROP PROCEDURE IF EXISTS `charging_pile_order`.`add_large_screen_create_time_index`; DELIMITER $$ CREATE PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`() BEGIN DECLARE i INT DEFAULT 0; DECLARE tbl_name VARCHAR(64); DECLARE idx_exists INT; WHILE i < 50 DO SET tbl_name = CONCAT('t_charging_order_', i); SELECT COUNT(*) INTO idx_exists FROM information_schema.statistics WHERE table_schema = 'charging_pile_order' AND table_name = tbl_name AND index_name = 'idx_create_time'; IF idx_exists = 0 THEN SET @ddl = CONCAT('ALTER TABLE `charging_pile_order`.`', tbl_name, '` ADD KEY idx_create_time (create_time)'); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL `charging_pile_order`.`add_large_screen_create_time_index`(); DROP PROCEDURE `charging_pile_order`.`add_large_screen_create_time_index`; -- 3. 索引结果核对(应返回 50 行) SELECT table_name, index_name FROM information_schema.statistics WHERE table_schema = 'charging_pile_order' AND table_name LIKE 't_charging_order\_%' AND index_name = 'idx_create_time' ORDER BY table_name; -- ============================================================================ -- 上线后数据一致性对比(只读,可在线执行;对比新老口径差值,应≈0) -- 老口径: 现接口 getLargeScreenSummary 的原始 SQL(挑少量站点对比,避免生产压力) -- ============================================================================ -- SELECT COUNT(DISTINCT charging_order.id) AS validOrderCount, -- COALESCE(ROUND(SUM(accounting.charging_capacity), 2), 0) AS totalChargingCapacity, -- COALESCE(ROUND(SUM(accounting.period_electric_price), 2), 0) AS electricityFee, -- COALESCE(ROUND(SUM(CASE WHEN charging_order.order_source IN (0,1) -- THEN accounting.period_service_price ELSE accounting.period_service_price * 0.8 END), 2), 0) AS serviceFee -- FROM charging_pile_order.t_charging_order charging_order -- LEFT JOIN charging_pile_order.t_charging_order_accounting_strategy accounting -- ON accounting.charging_order_id = charging_order.id -- WHERE charging_order.del_flag = 0 -- AND charging_order.status = 5 -- AND charging_order.recharge_payment_status = 2 -- AND charging_order.site_id IN (101, 102); -- -- SELECT SUM(order_count) AS validOrderCount, -- SUM(capacity) AS totalChargingCapacity, -- SUM(electric_fee) AS electricityFee, -- SUM(service_fee) AS serviceFee -- FROM charging_pile_order.t_large_screen_site_stat -- WHERE stat_type = 'M' AND site_id IN (101, 102);
