PostgreSQL Foreign Table 性能优化实战

@Entity
public class Order {
    @Id private Long id;
    @ManyToOne(fetch = FetchType.LAZY)  // 或 EAGER
    @JoinColumn(name = "user_id")
    private User user;  // 关联外部表
}

// 查询 100 个订单
List<Order> orders = orderRepository.findAll();
// EAGER: 1 + 100 次 SQL（N+1 问题）
// LAZY: 访问 user 属性时触发 100 次额外查询

// Repository 方法
@Query("SELECT o FROM Order o JOIN FETCH o.user WHERE o.status = :status")
List<Order> findWithUserByStatus(@Param("status") String status);

@Entity
@NamedEntityGraph(
    name = "Order.withUser",
    attributeNodes = @NamedAttributeNode("user")
)
public class Order { ... }

// Repository
@EntityGraph("Order.withUser")
List<Order> findByStatus(String status);

// 或动态指定
@EntityGraph(attributePaths = {"user"})
List<Order> findByStatus(String status);

@Entity
@BatchSize(size = 50)  // 每次批量加载 50 个关联
public class Order {
    @ManyToOne
    private User user;
}

// 接口投影
public interface OrderSummary {
    Long getId();
    String getProductName();
    String getUserName();  // 来自关联表
}

// Repository
@Query("SELECT o.id as id, o.productName as productName, u.name as userName " +
       "FROM Order o JOIN o.user u WHERE o.status = :status")
List<OrderSummary> findSummariesByStatus(@Param("status") String status);

// 第一步：查主表
List<Order> orders = orderRepository.findByStatus("PAID");
List<Long> userIds = orders.stream()
    .map(Order::getUserId)
    .distinct()
    .toList();

// 第二步：批量查关联表
Map<Long, User> userMap = userRepository.findByIdIn(userIds)
    .stream()
    .collect(Collectors.toMap(User::getId, Function.identity()));

// 第三步：内存组装
orders.forEach(o -> o.setUser(userMap.get(o.getUserId())));

@Entity
@Cacheable
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class User {
    @Id private Long id;
    private String name;
}

// 配置缓存
@EnableCaching
@Configuration
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        CaffeineCacheManager manager = new CaffeineCacheManager();
        manager.setCaffeine(Caffeine.newBuilder()
            .maximumSize(10000)
            .expireAfterWrite(Duration.ofMinutes(30)));
        return manager;
    }
}

@SqlResultSetMapping(
    name = "OrderWithUserMapping",
    entities = {
        @EntityResult(entityClass = Order.class),
        @EntityResult(entityClass = User.class)
    }
)
@Entity
public class Order { ... }

// Repository
@Query(value = """
    SELECT o.*, u.* 
    FROM orders o 
    LEFT JOIN foreign_users u ON o.user_id = u.id 
    WHERE o.status = :status
    """, nativeQuery = true)
List<Object[]> findOrdersWithUsersNative(@Param("status") String status);

// 或使用投影
@Query(value = """
    SELECT o.id, o.product_name, u.name as user_name 
    FROM orders o 
    LEFT JOIN foreign_users u ON o.user_id = u.id
    """, nativeQuery = true)
List<OrderUserDto> findOrderUserProjection();

// 问题：JOIN FETCH + 分页 = 内存全量查询
@Query("SELECT o FROM Order o JOIN FETCH o.user")
Page<Order> findAll(Pageable pageable);  // 警告：全表加载到内存

// 解决方案 1：两步查询
@Query(value = "SELECT o FROM Order o WHERE o.status = :status",
       countQuery = "SELECT COUNT(o) FROM Order o WHERE o.status = :status")
Page<Order> findByStatus(@Param("status") String status, Pageable pageable);

// 然后用 @BatchSize 或 EntityGraph 加载关联

// 解决方案 2：子查询 + IN
@Query("SELECT o FROM Order o WHERE o.id IN " +
       "(SELECT o2.id FROM Order o2 WHERE o2.status = :status)")
List<Order> findIdsByStatus(@Param("status") String status, Pageable pageable);

// 1. 使用 DTO 投影，避免实体管理开销
public interface OrderUserView {
    Long getId();
    String getOrderNo();
    @Value("#{target.user_name}")  // 外部表字段映射
    String getUserName();
}

// 2. Native Query 直接控制 SQL
@Query(value = """
    SELECT o.id, o.order_no, u.name as user_name, u.status as user_status
    FROM orders o
    LEFT JOIN foreign_users u ON o.user_id = u.id
    WHERE o.created_at > :since
    ORDER BY o.id
    LIMIT :limit OFFSET :offset
    """, nativeQuery = true)
List<OrderUserView> findRecentOrders(
    @Param("since") LocalDateTime since,
    @Param("limit") int limit,
    @Param("offset") int offset
);

// 3. 分步查询 + 本地缓存
@Service
public class OrderService {
    
    private final LoadingCache<Long, User> userCache = Caffeine.newBuilder()
        .maximumSize(10000)
        .expireAfterWrite(Duration.ofMinutes(10))
        .build(this::loadUser);
    
    private User loadUser(Long userId) {
        return userRepository.findById(userId)
            .orElse(User.EMPTY);
    }
    
    public List<OrderVO> getOrdersWithUsers(List<Long> orderIds) {
        List<Order> orders = orderRepository.findByIdIn(orderIds);
        return orders.stream()
            .map(o -> new OrderVO(o, userCache.get(o.getUserId())))
            .toList();
    }
}