Java聚合查询提速案例实操

Java聚合查询提速案例实操

问题场景

在电商系统中,需要查询某店铺当天的销售汇总数据：

• 订单总数
• 总销售额
• 支付成功订单数
• 退款订单数
• 平均客单价
• 商品品类销售排行TOP10

原始实现存在严重的性能问题。

慢查询案例分析

原始的逐条查询方式

// 问题代码：多次查询数据库
@Service
public class SalesReportService {
    public SalesReportVO getSalesReport(Long shopId, Date date) {
        SalesReportVO vo = new SalesReportVO();
        // 1. 查询订单总数
        Integer totalOrders = orderMapper.countByShopAndDate(shopId, date);
        // 2. 查询总销售额
        BigDecimal totalAmount = orderMapper.sumAmountByShopAndDate(shopId, date);
        // 3. 查询支付成功订单数
        Integer paidOrders = orderMapper.countPaidByShopAndDate(shopId, date);
        // 4. 查询退款订单数
        Integer refundOrders = orderMapper.countRefundByShopAndDate(shopId, date);
        // 5. 查询平均客单价（需要先查总金额和订单数）
        BigDecimal avgPrice = totalOrders > 0 ? 
            totalAmount.divide(BigDecimal.valueOf(totalOrders), 2, RoundingMode.HALF_UP) : 
            BigDecimal.ZERO;
        // 6. 查询品类排行（需要单独查询）
        List<CategoryRankVO> categoryRanks = orderMapper.getCategoryRank(shopId, date);
        return vo;
    }
}

SQL执行情况

-- 执行6次SQL查询
SELECT COUNT(*) FROM orders WHERE shop_id = ? AND create_date = ?;
SELECT SUM(amount) FROM orders WHERE shop_id = ? AND create_date = ?;
SELECT COUNT(*) FROM orders WHERE shop_id = ? AND create_date = ? AND status = 'PAID';
SELECT COUNT(*) FROM orders WHERE shop_id = ? AND create_date = ? AND status = 'REFUND';
SELECT category, SUM(amount) as total, COUNT(*) as count 
FROM orders WHERE shop_id = ? AND create_date = ? 
GROUP BY category ORDER BY total DESC LIMIT 10;

优化方案

合并查询（单次SQL完成）

@Repository
public interface OrderMapper {
    // 使用一个SQL查询所有聚合数据
    @Select("SELECT " +
            "COUNT(*) as total_orders, " +
            "SUM(amount) as total_amount, " +
            "SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) as paid_orders, " +
            "SUM(CASE WHEN status = 'REFUND' THEN 1 ELSE 0 END) as refund_orders " +
            "FROM orders " +
            "WHERE shop_id = #{shopId} AND create_date = #{date}")
    SalesAggregationVO getSalesAggregation(@Param("shopId") Long shopId, 
                                            @Param("date") Date date);
    // 品类排行单独查询（但数据量小）
    @Select("SELECT category, SUM(amount) as total, COUNT(*) as count " +
            "FROM orders " +
            "WHERE shop_id = #{shopId} AND create_date = #{date} " +
            "GROUP BY category ORDER BY total DESC LIMIT 10")
    List<CategoryRankVO> getCategoryRank(@Param("shopId") Long shopId, 
                                          @Param("date") Date date);
}

应用层缓存优化

@Service
public class OptimizedSalesReportService {
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    private static final String CACHE_KEY_PREFIX = "sales:report:";
    private static final long CACHE_EXPIRE = 300; // 5分钟
    public SalesReportVO getSalesReport(Long shopId, Date date) {
        String cacheKey = CACHE_KEY_PREFIX + shopId + ":" + date;
        // 1. 尝试从缓存获取
        SalesReportVO cached = (SalesReportVO) redisTemplate.opsForValue().get(cacheKey);
        if (cached != null) {
            return cached;
        }
        // 2. 缓存未命中，查询数据库
        SalesReportVO vo = queryFromDB(shopId, date);
        // 3. 写入缓存
        redisTemplate.opsForValue().set(cacheKey, vo, CACHE_EXPIRE, TimeUnit.SECONDS);
        return vo;
    }
    private SalesReportVO queryFromDB(Long shopId, Date date) {
        SalesReportVO vo = new SalesReportVO();
        // 单次查询获取聚合数据
        SalesAggregationVO aggregation = orderMapper.getSalesAggregation(shopId, date);
        vo.setTotalOrders(aggregation.getTotalOrders());
        vo.setTotalAmount(aggregation.getTotalAmount());
        vo.setPaidOrders(aggregation.getPaidOrders());
        vo.setRefundOrders(aggregation.getRefundOrders());
        // 计算平均客单价
        if (aggregation.getTotalOrders() > 0) {
            vo.setAvgPrice(aggregation.getTotalAmount()
                .divide(BigDecimal.valueOf(aggregation.getTotalOrders()), 
                        2, RoundingMode.HALF_UP));
        } else {
            vo.setAvgPrice(BigDecimal.ZERO);
        }
        // 查询品类排行
        List<CategoryRankVO> ranks = orderMapper.getCategoryRank(shopId, date);
        vo.setCategoryRanks(ranks);
        return vo;
    }
}

使用物化视图（数据库层面）

-- 创建物化视图（以PostgreSQL为例）
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    shop_id,
    create_date,
    COUNT(*) as total_orders,
    SUM(amount) as total_amount,
    SUM(CASE WHEN status = 'PAID' THEN 1 ELSE 0 END) as paid_orders,
    SUM(CASE WHEN status = 'REFUND' THEN 1 ELSE 0 END) as refund_orders,
    AVG(amount) as avg_price
FROM orders
GROUP BY shop_id, create_date;
-- 创建索引
CREATE INDEX idx_daily_sales_shop_date ON daily_sales_summary(shop_id, create_date);
-- 定期刷新物化视图（需要在Java中定时执行）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

@Component
public class MaterializedViewRefresher {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Scheduled(cron = "0 0/5 * * * ?") // 每5分钟刷新一次
    public void refreshDailySalesSummary() {
        jdbcTemplate.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary");
    }
}

使用ClickHouse等OLAP数据库

@Service
public class ClickHouseSalesReportService {
    @Autowired
    private ClickHouseTemplate clickHouseTemplate;
    public SalesReportVO getSalesReport(Long shopId, Date date) {
        String sql = "SELECT " +
                     "count() as total_orders, " +
                     "sum(amount) as total_amount, " +
                     "sumIf(1, status = 'PAID') as paid_orders, " +
                     "sumIf(1, status = 'REFUND') as refund_orders, " +
                     "avg(amount) as avg_price " +
                     "FROM orders " +
                     "WHERE shop_id = ? AND create_date = ?";
        SalesReportVO vo = clickHouseTemplate.queryForObject(sql, 
            new Object[]{shopId, date}, SalesReportVO.class);
        // 查询品类排行
        String rankSql = "SELECT category, sum(amount) as total, count() as count " +
                         "FROM orders " +
                         "WHERE shop_id = ? AND create_date = ? " +
                         "GROUP BY category ORDER BY total DESC LIMIT 10";
        List<CategoryRankVO> ranks = clickHouseTemplate.query(rankSql, 
            new Object[]{shopId, date}, 
            (rs, rowNum) -> new CategoryRankVO(
                rs.getString("category"),
                rs.getBigDecimal("total"),
                rs.getInt("count")
            ));
        vo.setCategoryRanks(ranks);
        return vo;
    }
}

异步批处理优化

@Service
public class BatchSalesReportService {
    private final ExecutorService executor = Executors.newFixedThreadPool(5);
    public CompletableFuture<SalesReportVO> getSalesReportAsync(Long shopId, Date date) {
        return CompletableFuture.supplyAsync(() -> {
            return getSalesReport(shopId, date);
        }, executor);
    }
    // 批量处理多个店铺的报表
    public Map<Long, SalesReportVO> getBatchSalesReports(List<Long> shopIds, Date date) {
        // 1. 一次性查询所有店铺的聚合数据
        List<SalesAggregationVO> aggregations = orderMapper.getBatchSalesAggregation(shopIds, date);
        // 2. 查询所有店铺的品类排行
        List<CategoryRankVO> ranks = orderMapper.getBatchCategoryRank(shopIds, date);
        // 3. 组装结果
        Map<Long, SalesReportVO> result = new HashMap<>();
        for (SalesAggregationVO agg : aggregations) {
            SalesReportVO vo = convertToVO(agg);
            vo.setCategoryRanks(filterRanks(ranks, agg.getShopId()));
            result.put(agg.getShopId(), vo);
        }
        return result;
    }
}

性能对比测试

测试数据

• 店铺数：100
• 每日订单量：约10万
• 数据时间范围：1年

测试结果

方案	响应时间	QPS	数据库连接数
原始方式（6次查询）	850ms	2	6
方案一（合并查询）	320ms	1	2
方案二（+缓存）	15ms(缓存命中)	7	1
方案三（物化视图）	50ms	20	1
方案四（ClickHouse）	30ms	3	1
方案五（批处理）	500ms(100店铺)	200(批量)	2

最佳实践建议

选择合适的优化方案

• 数据实时性要求高：选择方案一（合并查询）
• 可以接受一定延迟：选择方案二（缓存）或方案三（物化视图）
• 大数据量分析：选择方案四（OLAP数据库）
• 需要批量处理：选择方案五（批处理）

数据库索引优化

-- 复合索引优化
CREATE INDEX idx_orders_shop_date_status ON orders(shop_id, create_date, status);
-- 覆盖索引（避免回表查询）
CREATE INDEX idx_orders_cover ON orders(shop_id, create_date, status, amount, category);

代码层面的优化

// 使用数据库连接池
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/db");
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        return new HikariDataSource(config);
    }
}
// 使用批处理
@Transactional
public void batchUpdate() {
    String sql = "INSERT INTO orders (id, shop_id, amount) VALUES (?, ?, ?)";
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setLong(1, orders.get(i).getId());
            ps.setLong(2, orders.get(i).getShopId());
            ps.setBigDecimal(3, orders.get(i).getAmount());
        }
        @Override
        public int getBatchSize() {
            return orders.size();
        }
    });
}

监控与调优

@Component
public class SQLMonitor {
    // 使用慢查询日志监控
    @EventListener
    public void handleSlowQuery(SlowQueryEvent event) {
        long duration = event.getDuration();
        if (duration > 500) { // 超过500ms的查询
            log.warn("Slow SQL: {}, duration: {}ms", event.getSql(), duration);
            // 发送告警
            alertService.sendAlert("Slow query detected: " + event.getSql());
        }
    }
}

通过以上优化方案,聚合查询的性能可以得到显著提升：

1. 从多次查询减少到1-2次，减少网络开销
2. 引入缓存机制，对热点数据快速响应
3. 使用物化视图或OLAP数据库，优化大数据量聚合
4. 批量处理多个请求，提高吞吐量

建议根据实际业务场景和数据量选择合适的优化方案,并配合数据库索引优化和应用层缓存，实现最佳性能。