本文目录导读：
下面通过一个完整的实际案例,详细展示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
}
| 优化维度 | 优化前 | 优化后 | 性能提升 |
|---|---|---|---|
| 查询方式 | 全表扫描 | 索引查询 | 10-100倍 |
| 分页方式 | OFFSET分页 | 游标分页 | 5-10倍 |
| 关联查询 | N+1查询 | 批量查询 | 大幅提升 |
| 统计查询 | 实时统计 | 缓存+汇总表 | 100倍以上 |
| 大数据量 | 单线程 | 并行分片 | 3-5倍 |
最佳实践建议
- 索引策略：根据查询模式创建合适的复合索引，遵循最左前缀原则
- 分页优化：避免使用OFFSET深分页，采用游标分页或延迟关联
- 批量操作：避免N+1查询，使用批量插入、批量更新
- 数据分区：对于超大表考虑使用分区表（按时间或范围分区）
- 读写分离：读多写少的场景使用主从复制
- 缓存策略：对热点数据和统计结果使用Redis缓存
- 异步处理：对非实时性要求不高的操作使用异步处理
- 监控告警：定期分析慢查询日志，使用EXPLAIN分析执行计划
通过以上优化方案,大表查询性能可以从秒级提升到毫秒级，满足业务高并发需求。
