From d4dceed9ed67d5e2b7ab73f860f2ac894f8e2ed1 Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期二, 04 八月 2026 11:14:15 +0800
Subject: [PATCH] perf: reduce battery statistics order queries
---
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java | 34 ++++++-----------
ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | 6 +-
sql/20260804_battery_statistics_index.sql | 32 ++++++++++++++++
3 files changed, 47 insertions(+), 25 deletions(-)
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
index f31b54e..02c93a4 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
@@ -7159,34 +7159,24 @@
.in(TChargingOrder::getChargingGunId, gunIds));//符合条件的充电枪
}
vo.setTotal(total.intValue());
- //2.折线图
- /* // 计算日期范围：前七天（不含今日）直接查找数据量太大,内存报错
- LocalDate today = LocalDate.now();
- LocalDateTime startDate = today.minusDays(7).atStartOfDay();
- LocalDateTime endDate = today.minusDays(1).atTime(LocalTime.MAX);
- // 查询数据库
- List<OrderCountByDate> orderCounts = this.baseMapper.countOrdersByDate(startDate, endDate, gunIds);*/
-
-
List<LocalDate> dates = new ArrayList<>();
List<Integer> counts = new ArrayList<>();
LocalDate today = LocalDate.now();
- // 直接查找数据量太大，内存报错，分每天查找
+ LocalDateTime startDate = today.minusDays(7).atStartOfDay();
+ LocalDateTime endDate = today.minusDays(1).atTime(LocalTime.MAX);
+ Map<LocalDate, Long> countByDate = new HashMap<>();
+ if (gunIds != null && !gunIds.isEmpty()) {
+ List<OrderCountByDate> orderCounts = this.baseMapper.countOrdersByDate(startDate, endDate, gunIds);
+ if (orderCounts != null) {
+ for (OrderCountByDate orderCount : orderCounts) {
+ countByDate.put(orderCount.getDate(), orderCount.getCount());
+ }
+ }
+ }
for (int i = 7; i >= 1; i--) {//升序
LocalDate day = today.minusDays(i);
- LocalDateTime startOfDay = day.atStartOfDay();
- LocalDateTime endOfDay = day.atTime(LocalTime.MAX);
- //统计
- Long count = 0L;
- if (gunIds != null && !gunIds.isEmpty()) {
- count = this.baseMapper.selectCount(new LambdaQueryWrapper<TChargingOrder>()
- .eq(TChargingOrder::getDelFlag, 0)//未删除
- .in(TChargingOrder::getStatus, Arrays.asList(3, 5))//充电中、已结束
- .in(TChargingOrder::getChargingGunId, gunIds)//符合条件的充电枪 站点 超充快充
- .between(TChargingOrder::getCreateTime, startOfDay, endOfDay));//每天
- }
dates.add(day);
- counts.add(count.intValue());
+ counts.add(countByDate.getOrDefault(day, 0L).intValue());
}
vo.setDates(dates);
vo.setCounts(counts);
diff --git a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
index ee8b8d0..ecfb877 100644
--- a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
+++ b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
@@ -2536,7 +2536,7 @@
<select id="countOrdersByDate" resultType="com.ruoyi.order.vo.OrderCountByDate">
- SELECT DATE_FORMAT(create_time, '%m/%d') AS `date`, COUNT(*) AS `count`
+ SELECT DATE(create_time) AS `date`, COUNT(*) AS `count`
FROM t_charging_order
WHERE
del_flag = 0
@@ -2548,8 +2548,8 @@
#{item}
</foreach>
and create_time BETWEEN #{startTime} AND #{endTime}
- GROUP BY DATE(create_time, '%m/%d')
- ORDER BY DATE(create_time, '%m/%d') ASC
+ GROUP BY DATE(create_time)
+ ORDER BY DATE(create_time) ASC
</select>
diff --git a/sql/20260804_battery_statistics_index.sql b/sql/20260804_battery_statistics_index.sql
new file mode 100644
index 0000000..25b8bb2
--- /dev/null
+++ b/sql/20260804_battery_statistics_index.sql
@@ -0,0 +1,32 @@
+-- Speeds up battery safety statistics across every physical charging-order shard.
+DROP PROCEDURE IF EXISTS add_battery_statistics_indexes;
+DELIMITER //
+CREATE PROCEDURE add_battery_statistics_indexes()
+BEGIN
+ DECLARE finished BOOLEAN DEFAULT FALSE;
+ DECLARE shard VARCHAR(64);
+ DECLARE existing_index INT;
+ DECLARE shards CURSOR FOR
+ SELECT table_name FROM information_schema.tables
+ WHERE table_schema = DATABASE() AND table_name REGEXP '^t_charging_order(_[0-9]+)?$';
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
+
+ OPEN shards;
+ add_indexes: LOOP
+ FETCH shards INTO shard;
+ IF finished THEN LEAVE add_indexes; END IF;
+ SELECT COUNT(*) INTO existing_index FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = shard AND index_name = 'idx_battery_statistics';
+ IF existing_index = 0 THEN
+ SET @sql = CONCAT('ALTER TABLE `', shard, '` ADD INDEX `idx_battery_statistics` '
+ '(`charging_gun_id`, `status`, `del_flag`, `create_time`)');
+ PREPARE statement FROM @sql;
+ EXECUTE statement;
+ DEALLOCATE PREPARE statement;
+ END IF;
+ END LOOP;
+ CLOSE shards;
+END //
+DELIMITER ;
+CALL add_battery_statistics_indexes();
+DROP PROCEDURE add_battery_statistics_indexes;
--
Gitblit v1.7.1