perf: reduce battery statistics order queries

longz

2026-08-04 d4dceed9ed67d5e2b7ab73f860f2ac894f8e2ed1

perf: reduce battery statistics order queries

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

 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>



 sql/20260804_battery_statistics_index.sql

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