Java数据库资源优化实操

wen java案例 2026-06-26 33

Java数据库资源优化实操指南

连接池优化

配置文件示例 (HikariCP)

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 20000
      max-lifetime: 1200000
      validation-timeout: 3000
      leak-detection-threshold: 60000

连接池监控代码

@Component
public class DataSourceHealthIndicator {
    @Autowired
    private DataSource dataSource;
    @Scheduled(fixedRate = 60000)
    public void monitorConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikariDS = (HikariDataSource) dataSource;
            HikariPoolMXBean poolMXBean = hikariDS.getHikariPoolMXBean();
            log.info("活跃连接: {}, 空闲连接: {}, 等待线程数: {}, 总连接数: {}", 
                poolMXBean.getActiveConnections(),
                poolMXBean.getIdleConnections(), 
                poolMXBean.getThreadsAwaitingConnection(),
                poolMXBean.getTotalConnections());
        }
    }
}

SQL性能优化

慢查询日志配置

@Configuration
public class SQLPerformanceConfig {
    @Bean
    public PerformanceInterceptor performanceInterceptor() {
        PerformanceInterceptor interceptor = new PerformanceInterceptor();
        interceptor.setMaxTime(100); // 超过100ms记录慢查询
        interceptor.setFormat(true);
        return interceptor;
    }
}

批量操作优化

@Service
public class BatchOptimizationService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    // 批量插入优化
    @Transactional
    public void batchInsert(List<User> users) {
        String sql = "INSERT INTO users(name, email, age) VALUES (?, ?, ?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                User user = users.get(i);
                ps.setString(1, user.getName());
                ps.setString(2, user.getEmail());
                ps.setInt(3, user.getAge());
            }
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }
    // 使用NamedParameterJdbcTemplate进行批量操作
    public void batchInsertWithNamedParams(List<User> users) {
        String sql = "INSERT INTO users(name, email, age) VALUES (:name, :email, :age)";
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users.toArray());
        namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }
}

事务管理优化

@Service
public class TransactionOptimizationService {
    // 事务超时设置
    @Transactional(timeout = 30)
    public void processWithTimeout() {
        // 业务逻辑
    }
    // 只读事务优化
    @Transactional(readOnly = true)
    public List<User> findUsers() {
        // 查询操作，优化数据库资源
    }
    // 事务传播行为优化
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void independentTransaction() {
        // 独立事务操作
    }
}

数据库连接泄漏检测

@Component
public class ConnectionLeakDetector {
    private static final ThreadLocal<Long> CONNECTION_START_TIME = new ThreadLocal<>();
    @EventListener
    public void handleConnectionCreated(ConnectionCreatedEvent event) {
        CONNECTION_START_TIME.set(System.currentTimeMillis());
    }
    @EventListener
    public void handleConnectionClosed(ConnectionClosedEvent event) {
        CONNECTION_START_TIME.remove();
    }
    @Scheduled(fixedRate = 60000)
    public void checkLeakConnections() {
        if (CONNECTION_START_TIME.get() != null) {
            long duration = System.currentTimeMillis() - CONNECTION_START_TIME.get();
            if (duration > 300000) { // 5分钟
                log.warn("检测到可能的数据库连接泄漏，连接持续时间: {} ms", duration);
                // 发送告警
            }
        }
    }
}

缓存优化策略

@Service
public class CacheOptimizationService {
    @Autowired
    private CacheManager cacheManager;
    // 使用Redis缓存减少数据库访问
    @Cacheable(value = "users", key = "#id", unless = "#result == null")
    public User getUserById(Long id) {
        return userRepository.findById(id).orElse(null);
    }
    // 缓存失效策略
    @CacheEvict(value = "users", key = "#user.id")
    public User updateUser(User user) {
        return userRepository.save(user);
    }
    // 批量缓存
    @Cacheable(value = "users", key = "'all_users'")
    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
}

数据库查询优化

@Repository
public class QueryOptimizationRepository {
    @PersistenceContext
    private EntityManager entityManager;
    // 使用JOIN FETCH避免N+1问题
    @Query("SELECT u FROM User u JOIN FETCH u.orders WHERE u.id = :id")
    User findUserWithOrders(@Param("id") Long id);
    // 分页查询优化
    @Query(value = "SELECT * FROM users WHERE status = :status", 
           countQuery = "SELECT count(*) FROM users WHERE status = :status",
           nativeQuery = true)
    Page<User> findUsersByStatus(@Param("status") String status, Pageable pageable);
    // 使用投影减少数据传输
    @Query("SELECT new com.example.dto.UserSummary(u.id, u.name, u.email) FROM User u")
    List<UserSummary> findUserSummaries();
    // 批量更新优化
    @Modifying
    @Query("UPDATE User u SET u.status = :status WHERE u.id IN :ids")
    int batchUpdateStatus(@Param("status") String status, @Param("ids") List<Long> ids);
}

数据库资源监控

@Component
public class DatabaseResourceMonitor {
    @Autowired
    private DataSource dataSource;
    @Scheduled(fixedRate = 300000) // 5分钟执行一次
    public void monitorDatabaseResources() {
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            // 检查数据库连接数
            String query = "SELECT COUNT(*) FROM pg_stat_activity";
            try (PreparedStatement ps = connection.prepareStatement(query);
                 ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    int activeConnections = rs.getInt(1);
                    log.info("当前数据库活跃连接数: {}", activeConnections);
                }
            }
            // 检查死锁
            String deadlockQuery = "SELECT * FROM pg_locks WHERE NOT granted";
            // 实现死锁检测逻辑
        } catch (SQLException e) {
            log.error("数据库资源监控失败", e);
        }
    }
}

JDBC连接优化

@Configuration
public class JDBCResourceOptimization {
    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        // 连接池基本配置
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/db");
        config.setUsername("user");
        config.setPassword("password");
        // 连接池优化参数
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);
        config.setConnectionTimeout(20000);
        config.setMaxLifetime(1200000);
        // 连接测试
        config.setConnectionTestQuery("SELECT 1");
        config.setValidationTimeout(3000);
        // 性能监控
        config.setMetricRegistry(metricRegistry);
        config.setHealthCheckRegistry(healthCheckRegistry);
        return new HikariDataSource(config);
    }
}

数据库资源使用分析

@Component
public class ResourceUsageAnalyzer {
    @EventListener
    public void analyzeQueryPerformance(QueryExecutionEvent event) {
        QueryExecutionMetrics metrics = event.getMetrics();
        // 记录慢查询
        if (metrics.getExecutionTime() > 1000) {
            log.warn("慢查询告警: SQL={}, 执行时间={}ms, 返回行数={}", 
                metrics.getSql(), 
                metrics.getExecutionTime(),
                metrics.getResultSize());
        }
        // 记录大量数据返回
        if (metrics.getResultSize() > 10000) {
            log.info("大数据量返回: SQL={}, 返回行数={}", 
                metrics.getSql(), 
                metrics.getResultSize());
        }
    }
    @Scheduled(cron = "0 0 2 * * ?") // 每天凌晨2点执行
    public void generateDailyReport() {
        // 生成数据库资源使用日报
        log.info("开始生成数据库资源使用日报");
        // 收集指标
        // 生成报告
        // 发送邮件
    }
}

最佳实践总结

连接池配置优化
- 根据应用负载动态调整连接池大小
- 设置合理的超时和存活时间
SQL优化
- 使用EXPLAIN分析执行计划
- 避免SELECT *，只查询需要的字段
- 合理使用索引
事务管理
- 控制事务范围，避免长事务
- 合理使用隔离级别
监控告警
- 建立完整的监控体系
- 设置告警阈值
定期维护
- 清理无效连接
- 优化数据库表结构
- 更新统计信息

这些优化措施需要根据实际业务场景和数据库类型进行适当调整,建议在生产环境进行充分的性能测试后再实施。