Java大表查询优化案例

本文目录导读：

1. 案例背景
2. 优化前：问题代码
3. 优化方案一：索引优化
4. 优化方案二：分页查询优化
5. 优化方案三：批量操作优化
6. 优化方案四：汇总统计优化
7. 优化方案五：并行查询优化
8. 优化方案六：主从分离
9. 优化效果对比
10. 最佳实践建议

下面通过一个完整的实际案例,详细展示Java大表查询优化的全过程。

案例背景

业务场景：电商订单系统，订单表包含5000万+数据 表结构：

CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(64) NOT NULL COMMENT '订单号',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `product_id` bigint(20) NOT NULL COMMENT '商品ID',
  `order_status` int(11) NOT NULL COMMENT '订单状态',
  `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

优化前：问题代码

@Service
public class OrderService {
    /**
     * 问题1：全表扫描查询
     */
    public List<Order> queryOrders(String keyword, Date startTime, Date endTime) {
        String sql = "SELECT * FROM t_order WHERE order_no LIKE '%' ? '%'" +
                    " AND create_time BETWEEN ? AND ?";
        // 直接查询，无分页，无索引优化
        return jdbcTemplate.query(sql, new Object[]{keyword, startTime, endTime}, 
                                 new BeanPropertyRowMapper<>(Order.class));
    }
    /**
     * 问题2：N+1查询问题
     */
    public List<OrderDetail> getOrderDetails(List<Long> orderIds) {
        List<OrderDetail> details = new ArrayList<>();
        for (Long orderId : orderIds) {
            // 循环查询，每行数据都发一次SQL
            OrderDetail detail = orderDetailMapper.selectByOrderId(orderId);
            details.add(detail);
        }
        return details;
    }
    /**
     * 问题3：统计查询无优化
     */
    public BigDecimal getTotalAmount(Long userId) {
        String sql = "SELECT SUM(order_amount) FROM t_order WHERE user_id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{userId}, BigDecimal.class);
    }
}

优化方案一：索引优化

-- 1. 创建复合索引（遵循最左前缀原则）
ALTER TABLE t_order ADD INDEX idx_user_create (user_id, create_time);
ALTER TABLE t_order ADD INDEX idx_order_no (order_no);
ALTER TABLE t_order ADD INDEX idx_create_time (create_time);
-- 2. 创建覆盖索引（避免回表）
ALTER TABLE t_order ADD INDEX idx_user_amount (user_id, order_amount);
-- 3. 冗余字段优化，避免LIKE查询
ALTER TABLE t_order ADD COLUMN order_prefix varchar(8) 
    GENERATED ALWAYS AS (LEFT(order_no, 8)) STORED;
ALTER TABLE t_order ADD INDEX idx_order_prefix (order_prefix);

优化方案二：分页查询优化

@Service
@Slf4j
public class OptimizedOrderService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 优化1：使用游标分页（基于索引的分页）
     */
    public PageResult<Order> queryOrdersByCursor(Long lastOrderId, 
                                                 Integer pageSize,
                                                 Date startTime, 
                                                 Date endTime) {
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sql.append("SELECT id, order_no, user_id, product_id, ")
           .append("order_status, order_amount, create_time ")
           .append("FROM t_order WHERE 1=1 ");
        // 利用id主键索引作为游标
        if (lastOrderId != null && lastOrderId > 0) {
            sql.append("AND id > ? ");
            params.add(lastOrderId);
        }
        if (startTime != null) {
            sql.append("AND create_time >= ? ");
            params.add(startTime);
        }
        if (endTime != null) {
            sql.append("AND create_time < ? ");
            params.add(endTime);
        }
        sql.append("ORDER BY id ASC LIMIT ?");
        params.add(pageSize + 1); // 多查一条判断是否有下一页
        List<Order> orders = jdbcTemplate.query(sql.toString(), params.toArray(),
            new BeanPropertyRowMapper<>(Order.class));
        // 判断是否有下一页
        boolean hasNext = orders.size() > pageSize;
        if (hasNext) {
            orders = orders.subList(0, pageSize);
        }
        return new PageResult<>(orders, hasNext, 
            orders.isEmpty() ? 0 : orders.get(orders.size()-1).getId());
    }
    /**
     * 优化2：延迟关联分页（先查ID再查数据）
     */
    public List<Order> queryOrdersWithDeferredJoin(Long offset, Integer pageSize,
                                                   Date startTime, Date endTime) {
        // 第一步：只查询主键ID（利用覆盖索引）
        String countSql = "SELECT id FROM t_order " +
                         "WHERE create_time BETWEEN ? AND ? " +
                         "ORDER BY create_time DESC LIMIT ?, ?";
        List<Long> ids = jdbcTemplate.queryForList(countSql, Long.class, 
            startTime, endTime, offset, pageSize);
        if (ids.isEmpty()) {
            return Collections.emptyList();
        }
        // 第二步：根据ID批量查询完整数据
        String placeholders = String.join(",", Collections.nCopies(ids.size(), "?"));
        String detailSql = "SELECT * FROM t_order WHERE id IN (" + placeholders + ") " +
                          "ORDER BY create_time DESC";
        return jdbcTemplate.query(detailSql, ids.toArray(),
            new BeanPropertyRowMapper<>(Order.class));
    }
}

优化方案三：批量操作优化

@Service
@Slf4j
public class BatchOrderService {
    /**
     * 优化3：分批查询，解决IN查询限制
     */
    public Map<Long, OrderDetail> batchQueryOrderDetails(List<Long> orderIds) {
        Map<Long, OrderDetail> resultMap = new HashMap<>();
        // 每次查询500条
        int batchSize = 500;
        for (int i = 0; i < orderIds.size(); i += batchSize) {
            List<Long> batchIds = orderIds.subList(i, 
                Math.min(i + batchSize, orderIds.size()));
            String placeholders = String.join(",", Collections.nCopies(batchIds.size(), "?"));
            String sql = "SELECT * FROM t_order_detail WHERE order_id IN (" + 
                        placeholders + ")";
            List<OrderDetail> details = jdbcTemplate.query(sql, batchIds.toArray(),
                new BeanPropertyRowMapper<>(OrderDetail.class));
            for (OrderDetail detail : details) {
                resultMap.put(detail.getOrderId(), detail);
            }
        }
        return resultMap;
    }
    /**
     * 优化4：批量更新
     */
    @Transactional
    public void batchUpdateOrderStatus(List<Long> orderIds, Integer status) {
        int batchSize = 500;
        for (int i = 0; i < orderIds.size(); i += batchSize) {
            List<Long> batch = orderIds.subList(i, Math.min(i + batchSize, orderIds.size()));
            String sql = "UPDATE t_order SET order_status = ?, update_time = NOW() " +
                        "WHERE id IN (" + String.join(",", Collections.nCopies(batch.size(), "?")) + ")";
            List<Object> params = new ArrayList<>();
            params.add(status);
            params.addAll(batch);
            jdbcTemplate.update(sql, params.toArray());
        }
    }
}

优化方案四：汇总统计优化

@Service
@Slf4j
public class StatisticsService {
    /**
     * 优化5：使用缓存汇总统计
     */
    @Cacheable(value = "userOrderStats", key = "#userId", 
               unless = "#result == null")
    public UserOrderStats getUserOrderStats(Long userId) {
        // 查询汇总统计（利用覆盖索引）
        String sql = "SELECT COUNT(*) as order_count, " +
                    "COALESCE(SUM(order_amount), 0) as total_amount, " +
                    "AVG(order_amount) as avg_amount " +
                    "FROM t_order " +
                    "WHERE user_id = ? " +   // 使用idx_user_amount覆盖索引
                    "AND create_time >= DATE_SUB(NOW(), INTERVAL 1 YEAR)";
        return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            UserOrderStats stats = new UserOrderStats();
            stats.setOrderCount(rs.getInt("order_count"));
            stats.setTotalAmount(rs.getBigDecimal("total_amount"));
            stats.setAvgAmount(rs.getBigDecimal("avg_amount"));
            stats.setCacheTime(new Date());
            return stats;
        }, userId);
    }
    /**
     * 优化6：使用汇总表（定时任务维护）
     */
    @Scheduled(cron = "0 0 1 * * ?") // 每天凌晨1点执行
    public void updateDailySummary() {
        String sql = "INSERT INTO t_order_daily_summary (summary_date, user_id, " +
                    "order_count, total_amount) " +
                    "SELECT DATE(create_time), user_id, COUNT(*), SUM(order_amount) " +
                    "FROM t_order " +
                    "WHERE create_time >= CURDATE() - INTERVAL 1 DAY " +
                    "AND create_time < CURDATE() " +
                    "GROUP BY DATE(create_time), user_id " +
                    "ON DUPLICATE KEY UPDATE " +
                    "order_count = VALUES(order_count), " +
                    "total_amount = VALUES(total_amount)";
        int count = jdbcTemplate.update(sql);
        log.info("Daily summary updated: {} records", count);
    }
}

优化方案五：并行查询优化

@Service
@Slf4j
public class ParallelQueryService {
    @Autowired
    private TaskExecutor taskExecutor;
    /**
     * 优化7：分片并行查询
     */
    public List<Order> parallelQueryByDateRange(Date startTime, Date endTime) {
        // 按天分片
        List<Date[]> dateSegments = splitDateRange(startTime, endTime, TimeUnit.DAYS.toMillis(1));
        List<CompletableFuture<List<Order>>> futures = new ArrayList<>();
        for (Date[] segment : dateSegments) {
            CompletableFuture<List<Order>> future = 
                CompletableFuture.supplyAsync(() -> {
                    return queryOrdersByDate(segment[0], segment[1]);
                }, taskExecutor);
            futures.add(future);
        }
        // 合并结果
        return futures.stream()
            .map(CompletableFuture::join)
            .flatMap(List::stream)
            .collect(Collectors.toList());
    }
    private List<Order> queryOrdersByDate(Date start, Date end) {
        String sql = "SELECT * FROM t_order WHERE create_time BETWEEN ? AND ? " +
                    "LIMIT 1000";
        return jdbcTemplate.query(sql, new Object[]{start, end},
            new BeanPropertyRowMapper<>(Order.class));
    }
    private List<Date[]> splitDateRange(Date start, Date end, long segmentMs) {
        List<Date[]> segments = new ArrayList<>();
        long startMs = start.getTime();
        long endMs = end.getTime();
        long current = startMs;
        while (current < endMs) {
            long next = Math.min(current + segmentMs, endMs);
            segments.add(new Date[]{new Date(current), new Date(next)});
            current = next;
        }
        return segments;
    }
}

优化方案六：主从分离

@Configuration
public class DataSourceConfig {
    // 读写分离配置
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }
}
@Aspect
@Component
public class DataSourceRouter {
    @Around("@annotation(readOnly)")
    public Object route(ProceedingJoinPoint pjp, ReadOnly readOnly) {
        if (readOnly.value()) {
            // 只读操作路由到从库
            DataSourceContextHolder.setSlave();
        } else {
            // 写操作路由到主库
            DataSourceContextHolder.setMaster();
        }
        try {
            return pjp.proceed();
        } catch (Throwable e) {
            throw new RuntimeException(e);
        } finally {
            DataSourceContextHolder.clear();
        }
    }
}

优化效果对比

@Test
public void performanceTest() {
    // 优化前
    long startTime = System.currentTimeMillis();
    List<Order> orders = orderService.queryOrders("2023", 
        new Date(System.currentTimeMillis() - 86400000 * 30), new Date());
    long cost = System.currentTimeMillis() - startTime;
    System.out.println("优化前耗时: " + cost + "ms"); // 可能耗时 3000ms+
    // 优化后
    startTime = System.currentTimeMillis();
    PageResult<Order> result = optimizedOrderService.queryOrdersByCursor(
        null, 100, null, null);
    cost = System.currentTimeMillis() - startTime;
    System.out.println("优化后耗时: " + cost + "ms"); // < 50ms
}

最佳实践建议

1. 索引策略：根据查询模式创建合适的复合索引，遵循最左前缀原则
2. 分页优化：避免使用OFFSET深分页，采用游标分页或延迟关联
3. 批量操作：避免N+1查询，使用批量插入、批量更新
4. 数据分区：对于超大表考虑使用分区表（按时间或范围分区）
5. 读写分离：读多写少的场景使用主从复制
6. 缓存策略：对热点数据和统计结果使用Redis缓存
7. 异步处理：对非实时性要求不高的操作使用异步处理
8. 监控告警：定期分析慢查询日志，使用EXPLAIN分析执行计划

通过以上优化方案,大表查询性能可以从秒级提升到毫秒级，满足业务高并发需求。