Java SQL优化案例怎么落地代码

wen java案例 2026-06-29 273

Java SQL优化案例落地代码实战

案例场景

假设有一个订单查询系统，需要查询某个用户最近30天内的订单列表,并按下单时间倒序排列。

Java SQL优化案例怎么落地代码

原始SQL（性能较差）：

SELECT o.*, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.user_id = ?
  AND o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY o.create_time DESC
LIMIT 20;

优化方案及代码实现

方案1：使用覆盖索引

建立复合索引：

-- 在orders表上创建复合索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time DESC);

优化后的查询SQL：

-- 只查询需要的字段，避免回表查询
SELECT o.order_id, o.order_no, o.amount, o.status, o.create_time,
       u.name, u.phone
FROM orders o 
INNER JOIN users u ON o.user_id = u.id
WHERE o.user_id = ?
  AND o.create_time >= ?
ORDER BY o.create_time DESC
LIMIT 20;

方案2：分页优化（游标分页）

/**
 * 使用游标分页替代传统LIMIT分页
 */
@Service
public class OrderService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 传统分页（性能较差）
     */
    public List<OrderVO> getOrdersByPage(Long userId, int page, int size) {
        String sql = "SELECT o.*, u.name, u.phone FROM orders o " +
                     "INNER JOIN users u ON o.user_id = u.id " +
                     "WHERE o.user_id = ? AND o.create_time >= ? " +
                     "ORDER BY o.create_time DESC LIMIT ? OFFSET ?";
        return jdbcTemplate.query(sql, 
            new BeanPropertyRowMapper<>(OrderVO.class),
            userId, 
            LocalDateTime.now().minusDays(30),
            size, 
            (page - 1) * size);
    }
    /**
     * 游标分页（性能优化）
     */
    public List<OrderVO> getOrdersByCursor(Long userId, 
                                           LocalDateTime cursorTime, 
                                           Long cursorId,
                                           int size) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT o.*, u.name, u.phone FROM orders o ")
           .append("INNER JOIN users u ON o.user_id = u.id ")
           .append("WHERE o.user_id = ? AND o.create_time >= ? ");
        // 添加游标条件
        if (cursorTime != null) {
            sql.append("AND (o.create_time < ? OR ");
            sql.append("(o.create_time = ? AND o.id < ?)) ");
        }
        sql.append("ORDER BY o.create_time DESC, o.id DESC LIMIT ?");
        List<Object> params = new ArrayList<>();
        params.add(userId);
        params.add(LocalDateTime.now().minusDays(30));
        if (cursorTime != null) {
            params.add(cursorTime);
            params.add(cursorTime);
            params.add(cursorId);
        }
        params.add(size);
        return jdbcTemplate.query(sql.toString(), 
            new BeanPropertyRowMapper<>(OrderVO.class), 
            params.toArray());
    }
}

方案3：使用查询缓存

@Component
public class OrderCacheService {
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static final String ORDER_CACHE_PREFIX = "user_orders:";
    private static final long CACHE_EXPIRE = 300; // 5分钟
    /**
     * 带缓存的订单查询
     */
    public List<OrderVO> getOrdersWithCache(Long userId) {
        String cacheKey = ORDER_CACHE_PREFIX + userId;
        // 1. 尝试从缓存获取
        List<OrderVO> cachedOrders = (List<OrderVO>) redisTemplate.opsForValue().get(cacheKey);
        if (cachedOrders != null) {
            return cachedOrders;
        }
        // 2. 缓存未命中，查询数据库
        String sql = "SELECT o.*, u.name, u.phone FROM orders o " +
                     "INNER JOIN users u ON o.user_id = u.id " +
                     "WHERE o.user_id = ? AND o.create_time >= ? " +
                     "ORDER BY o.create_time DESC LIMIT 20";
        List<OrderVO> orders = jdbcTemplate.query(sql, 
            new BeanPropertyRowMapper<>(OrderVO.class),
            userId, 
            LocalDateTime.now().minusDays(30));
        // 3. 存入缓存（防止缓存穿透）
        if (orders != null && !orders.isEmpty()) {
            redisTemplate.opsForValue().set(cacheKey, orders, CACHE_EXPIRE, TimeUnit.SECONDS);
        } else {
            // 缓存空值防止穿透
            redisTemplate.opsForValue().set(cacheKey, new ArrayList<>(), 60, TimeUnit.SECONDS);
        }
        return orders;
    }
}

方案4：批量操作优化

@Service
public class OrderBatchService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 批量更新订单状态（优化前：逐条更新）
     */
    @Deprecated
    public void updateOrderStatusOld(List<Long> orderIds, String status) {
        for (Long orderId : orderIds) {
            jdbcTemplate.update(
                "UPDATE orders SET status = ?, update_time = NOW() WHERE id = ?",
                status, orderId
            );
        }
    }
    /**
     * 批量更新订单状态（优化后：批量更新）
     */
    public void updateOrderStatusBatch(List<Long> orderIds, String status) {
        // 方式1：使用IN语句
        String sql = "UPDATE orders SET status = ?, update_time = NOW() WHERE id IN (?)";
        // 构建参数占位符
        String placeholders = orderIds.stream()
            .map(id -> "?")
            .collect(Collectors.joining(","));
        String finalSql = sql.replace("?", placeholders);
        List<Object> params = new ArrayList<>();
        params.add(status);
        params.addAll(orderIds);
        jdbcTemplate.update(finalSql, params.toArray());
    }
    /**
     * 批量插入优化
     */
    public void batchInsertOrders(List<Order> orders) {
        String sql = "INSERT INTO orders (order_no, user_id, amount, status, create_time) " +
                     "VALUES (?, ?, ?, ?, NOW())";
        // 使用JDBC批处理
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Order order = orders.get(i);
                ps.setString(1, order.getOrderNo());
                ps.setLong(2, order.getUserId());
                ps.setBigDecimal(3, order.getAmount());
                ps.setString(4, order.getStatus());
            }
            @Override
            public int getBatchSize() {
                return orders.size();
            }
        });
    }
}

方案5：读写分离

@Component
public class OrderReadWriteService {
    @Autowired
    @Qualifier("masterDataSource")
    private DataSource masterDataSource;
    @Autowired
    @Qualifier("slaveDataSource")
    private DataSource slaveDataSource;
    /**
     * 写操作使用主库
     */
    public void createOrder(Order order) {
        JdbcTemplate masterJdbc = new JdbcTemplate(masterDataSource);
        String sql = "INSERT INTO orders (order_no, user_id, amount, status, create_time) " +
                     "VALUES (?, ?, ?, ?, NOW())";
        masterJdbc.update(sql, 
            order.getOrderNo(), 
            order.getUserId(), 
            order.getAmount(), 
            order.getStatus());
    }
    /**
     * 读操作使用从库
     */
    public List<OrderVO> getOrdersFromSlave(Long userId) {
        JdbcTemplate slaveJdbc = new JdbcTemplate(slaveDataSource);
        String sql = "SELECT o.*, u.name, u.phone FROM orders o " +
                     "INNER JOIN users u ON o.user_id = u.id " +
                     "WHERE o.user_id = ? AND o.create_time >= ? " +
                     "ORDER BY o.create_time DESC LIMIT 20";
        return slaveJdbc.query(sql, 
            new BeanPropertyRowMapper<>(OrderVO.class),
            userId, 
            LocalDateTime.now().minusDays(30));
    }
}

性能测试与监控

使用JMH进行性能基准测试

@BenchmarkMode(Mode.Throughput)
@OutputTimeUnit(TimeUnit.SECONDS)
@State(Scope.Thread)
public class QueryPerformanceTest {
    private JdbcTemplate jdbcTemplate;
    @Setup
    public void setup() {
        // 初始化数据源
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        dataSource.setUsername("root");
        dataSource.setPassword("password");
        jdbcTemplate = new JdbcTemplate(dataSource);
    }
    @Benchmark
    public List<OrderVO> testOriginalQuery() {
        String sql = "SELECT o.*, u.name, u.phone FROM orders o " +
                     "LEFT JOIN users u ON o.user_id = u.id " +
                     "WHERE o.user_id = ? AND o.create_time >= ? " +
                     "ORDER BY o.create_time DESC LIMIT 20";
        return jdbcTemplate.query(sql, 
            new BeanPropertyRowMapper<>(OrderVO.class),
            1001L, 
            LocalDateTime.now().minusDays(30));
    }
    @Benchmark
    public List<OrderVO> testOptimizedQuery() {
        String sql = "SELECT o.order_id, o.order_no, o.amount, o.status, o.create_time, " +
                     "u.name, u.phone FROM orders o " +
                     "INNER JOIN users u ON o.user_id = u.id " +
                     "WHERE o.user_id = ? AND o.create_time >= ? " +
                     "ORDER BY o.create_time DESC LIMIT 20";
        return jdbcTemplate.query(sql, 
            new BeanPropertyRowMapper<>(OrderVO.class),
            1001L, 
            LocalDateTime.now().minusDays(30));
    }
}

SQL执行监控

@Component
public class SqlMonitorAspect {
    private static final Logger logger = LoggerFactory.getLogger(SqlMonitorAspect.class);
    @Autowired
    private MeterRegistry meterRegistry;
    @Around("execution(* org.springframework.jdbc.core.JdbcTemplate.*(..))")
    public Object monitorSqlPerformance(ProceedingJoinPoint joinPoint) throws Throwable {
        long startTime = System.currentTimeMillis();
        try {
            Object result = joinPoint.proceed();
            long duration = System.currentTimeMillis() - startTime;
            // 记录慢查询
            if (duration > 100) { // 超过100ms视为慢查询
                logger.warn("Slow SQL detected: method={}, duration={}ms, args={}",
                    joinPoint.getSignature().getName(),
                    duration,
                    Arrays.toString(joinPoint.getArgs()));
                // 发送到监控系统
                meterRegistry.timer("sql.execution.time")
                    .record(duration, TimeUnit.MILLISECONDS);
            }
            return result;
        } catch (Exception e) {
            logger.error("SQL execution failed: method={}", joinPoint.getSignature().getName(), e);
            throw e;
        }
    }
}