Spring 数据库优化实践

数据库是系统性能瓶颈的常见位置，优化策略直接影响系统响应时间。

JPA/Hibernate 优化

批量操作配置

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 50
          batch_versioned_data: true
        order_inserts: true
        order_updates: true

批量插入实现

@Service
@Transactional
public class BatchInsertService {

    @PersistenceContext
    private EntityManager entityManager;

    public void batchInsert(List<User> users) {
        int batchSize = 50;
        for (int i = 0; i < users.size(); i++) {
            entityManager.persist(users.get(i));
            if (i > 0 && i % batchSize == 0) {
                entityManager.flush();
                entityManager.clear();  // 清空一级缓存
            }
        }
        entityManager.flush();
        entityManager.clear();
    }
}

N+1 问题解决

// 问题代码：N+1 查询
@Entity
public class Order {
    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;
}

// 方案1：JOIN FETCH
@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
Order findByIdWithItems(@Param("id") Long id);

// 方案2：EntityGraph
@EntityGraph(attributePaths = {"items"})
@Query("SELECT o FROM Order o WHERE o.id = :id")
Order findByIdWithItems(@Param("id") Long id);

// 方案3：批量获取
@BatchSize(size = 100)
@OneToMany(mappedBy = "order")
private List<OrderItem> items;

查询优化

分页查询

// 基础分页
public Page<User> findByPage(int page, int size) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("createTime").descending());
    return userRepository.findAll(pageable);
}

// 性能优化：只查总数一次
public Page<User> findByPageOptimized(int page, int size) {
    Pageable pageable = PageRequest.of(page, size);
    List<User> users = userRepository.findAllBy(pageable);
    long total = userRepository.count();
    return new PageImpl<>(users, pageable, total);
}

投影查询

// 只查询需要的字段
public interface UserProjection {
    Long getId();
    String getName();
}

@Query("SELECT u.id as id, u.name as name FROM User u WHERE u.status = :status")
List<UserProjection> findActiveUsers(@Param("status") String status);

索引提示

// 强制使用索引
@Query(value = "SELECT * FROM users FORCE INDEX(idx_status_create) WHERE status = ?1 ORDER BY create_time DESC", nativeQuery = true)
List<User> findByStatusWithIndex(String status);

事务优化

只读事务

@Service
public class QueryService {

    @Transactional(readOnly = true)  // 优化只读查询
    public User findById(Long id) {
        return userRepository.findById(id).orElse(null);
    }
}

事务传播

@Service
public class OrderService {

    // 独立事务，不影响外层事务
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void saveLog(OrderLog log) {
        logRepository.save(log);
    }

    // 嵌套事务
    @Transactional(propagation = Propagation.NESTED)
    public void processItem(OrderItem item) {
        itemRepository.save(item);
    }
}

事务超时

@Transactional(timeout = 10)  // 10秒超时
public void longRunningOperation() {
    // 长时间操作
}

连接池监控

HikariCP 监控

@RestController
public class DataSourceController {

    @Autowired
    private DataSource dataSource;

    @GetMapping("/metrics/datasource")
    public Map<String, Object> getDataSourceMetrics() {
        HikariDataSource ds = (HikariDataSource) dataSource;
        HikariPoolMXBean pool = ds.getHikariPoolMXBean();

        return Map.of(
            "activeConnections", pool.getActiveConnections(),
            "idleConnections", pool.getIdleConnections(),
            "totalConnections", pool.getTotalConnections(),
            "threadsAwaitingConnection", pool.getThreadsAwaitingConnection()
        );
    }
}

慢查询分析

开启 SQL 日志

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE

慢查询统计

@Aspect
@Component
@Slf4j
public class SlowQueryAspect {

    @Around("execution(* com.example.repository..*(..))")
    public Object logSlowQuery(ProceedingJoinPoint pjp) throws Throwable {
        long start = System.currentTimeMillis();
        try {
            return pjp.proceed();
        } finally {
            long duration = System.currentTimeMillis() - start;
            if (duration > 1000) {  // 超过1秒记录
                log.warn("Slow query: {} took {}ms", pjp.getSignature(), duration);
            }
        }
    }
}

索引优化建议

常见索引场景

索引示例

-- 单列索引
CREATE INDEX idx_user_status ON users(status);

-- 复合索引（最左匹配）
CREATE INDEX idx_user_status_create ON users(status, create_time);

-- 覆盖索引（避免回表）
CREATE INDEX idx_user_cover ON users(status, name, email);

-- 前缀索引
CREATE INDEX idx_user_name_prefix ON users(name(20));

读写分离

配置多数据源

@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();
    }

    @Bean
    public DataSource routingDataSource(
            @Qualifier("masterDataSource") DataSource master,
            @Qualifier("slaveDataSource") DataSource slave) {

        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", master);
        targetDataSources.put("slave", slave);

        AbstractRoutingDataSource routingDataSource = new AbstractRoutingDataSource() {
            @Override
            protected Object determineCurrentLookupKey() {
                return DbContextHolder.getDbType();
            }
        };
        routingDataSource.setDefaultTargetDataSource(master);
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }
}

动态切换

public class DbContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setMaster() {
        contextHolder.set("master");
    }

    public static void setSlave() {
        contextHolder.set("slave");
    }

    public static String getDbType() {
        return contextHolder.get();
    }

    public static void clear() {
        contextHolder.remove();
    }
}

// 使用
@Transactional
public void saveUser(User user) {
    DbContextHolder.setMaster();
    userRepository.save(user);
    DbContextHolder.clear();
}

要点总结