关于多表联查数据量过大无法导出问题，java大数据量导出，java导出超时

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.*;

// 实体类定义
class User {
    private String userId;
    private String userName;
    // 构造器、getter
    public User(String userId, String userName) {
        this.userId = userId;
        this.userName = userName;
    }
    public String getUserId() { return userId; }
    public String getUserName() { return userName; }
}

class Order {
    private String orderId;
    private String userId;
    private String orderTime;
    // 构造器、getter
    public Order(String orderId, String userId, String orderTime) {
        this.orderId = orderId;
        this.userId = userId;
        this.orderTime = orderTime;
    }
    public String getOrderId() { return orderId; }
    public String getUserId() { return userId; }
    public String getOrderTime() { return orderTime; }
}

class OrderItem {
    private String itemId;
    private String orderId;
    private String productId;
    private int quantity;
    // 构造器、getter
    public OrderItem(String itemId, String orderId, String productId, int quantity) {
        this.itemId = itemId;
        this.orderId = orderId;
        this.productId = productId;
        this.quantity = quantity;
    }
    public String getOrderId() { return orderId; }
    public String getProductId() { return productId; }
    public int getQuantity() { return quantity; }
}

class Product {
    private String productId;
    private String productName;
    // 构造器、getter
    public Product(String productId, String productName) {
        this.productId = productId;
        this.productName = productName;
    }
    public String getProductId() { return productId; }
    public String getProductName() { return productName; }
}

public class MultiTableExporter {
    // 生成测试数据（模拟多表数据）
    private List<User> generateUsers(int count) {
        List<User> users = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            users.add(new User("user_" + i, "用户" + i));
        }
        return users;
    }

    private List<Order> generateOrders(List<User> users) {
        List<Order> orders = new ArrayList<>();
        for (User user : users) {
            // 每个用户生成3个订单
            for (int i = 1; i <= 3; i++) {
                orders.add(new Order(
                    "order_" + user.getUserId() + "_" + i,
                    user.getUserId(),
                    "2024-07-" + (10 + i)
                ));
            }
        }
        return orders;
    }

    private List<Product> generateProducts(int count) {
        List<Product> products = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            products.add(new Product("product_" + i, "商品" + i));
        }
        return products;
    }

    private List<OrderItem> generateOrderItems(List<Order> orders, List<Product> products) {
        List<OrderItem> items = new ArrayList<>();
        Random random = new Random();
        for (Order order : orders) {
            // 每个订单包含2个商品
            for (int i = 1; i <= 2; i++) {
                Product randomProduct = products.get(random.nextInt(products.size()));
                items.add(new OrderItem(
                    "item_" + order.getOrderId() + "_" + i,
                    order.getOrderId(),
                    randomProduct.getProductId(),
                    random.nextInt(5) + 1 // 购买数量1-5
                ));
            }
        }
        return items;
    }

    // 方法1：优化的多表导出（哈希表预存关联ID，避免多层嵌套）
    public long exportWithHashJoin(
            List<User> users, List<Order> orders,
            List<OrderItem> items, List<Product> products,
            String filePath) {
        long startTime = System.currentTimeMillis();

        // 1. 哈希表预存：将关联ID映射到对象（O(n)时间构建，O(1)查询）
        Map<String, User> userMap = new HashMap<>();
        for (User user : users) {
            userMap.put(user.getUserId(), user);
        }

        Map<String, List<Order>> orderMap = new HashMap<>();
        for (Order order : orders) {
            orderMap.computeIfAbsent(order.getUserId(), k -> new ArrayList<>()).add(order);
        }

        Map<String, List<OrderItem>> itemMap = new HashMap<>();
        for (OrderItem item : items) {
            itemMap.computeIfAbsent(item.getOrderId(), k -> new ArrayList<>()).add(item);
        }

        Map<String, Product> productMap = new HashMap<>();
        for (Product product : products) {
            productMap.put(product.getProductId(), product);
        }

        // 2. 分步关联：用户 -> 订单 -> 订单项 -> 商品
        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            writer.write("用户ID,用户名,订单ID,订单时间,商品ID,商品名称,购买数量\n");

            // 遍历用户，关联其所有订单
            for (User user : users) {
                String userId = user.getUserId();
                List<Order> userOrders = orderMap.getOrDefault(userId, Collections.emptyList());

                // 遍历订单，关联其所有订单项
                for (Order order : userOrders) {
                    String orderId = order.getOrderId();
                    List<OrderItem> orderItems = itemMap.getOrDefault(orderId, Collections.emptyList());

                    // 遍历订单项，关联商品
                    for (OrderItem item : orderItems) {
                        Product product = productMap.get(item.getProductId());
                        if (product == null) continue; // 跳过无效商品

                        // 写入关联数据
                        writer.write(String.format(
                            "%s,%s,%s,%s,%s,%s,%d\n",
                            userId, user.getUserName(),
                            orderId, order.getOrderTime(),
                            product.getProductId(), product.getProductName(),
                            item.getQuantity()
                        ));
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return System.currentTimeMillis() - startTime;
    }

    // 方法2：普通多层嵌套导出（四层嵌套循环，性能极差）
    public long exportWithNestedLoops(
            List<User> users, List<Order> orders,
            List<OrderItem> items, List<Product> products,
            String filePath) {
        long startTime = System.currentTimeMillis();

        try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
            writer.write("用户ID,用户名,订单ID,订单时间,商品ID,商品名称,购买数量\n");

            // 四层嵌套循环：用户 -> 订单 -> 订单项 -> 商品
            for (User user : users) {
                for (Order order : orders) {
                    if (!order.getUserId().equals(user.getUserId())) continue;

                    for (OrderItem item : items) {
                        if (!item.getOrderId().equals(order.getOrderId())) continue;

                        for (Product product : products) {
                            if (item.getProductId().equals(product.getProductId())) {
                                writer.write(String.format(
                                    "%s,%s,%s,%s,%s,%s,%d\n",
                                    user.getUserId(), user.getUserName(),
                                    order.getOrderId(), order.getOrderTime(),
                                    product.getProductId(), product.getProductName(),
                                    item.getQuantity()
                                ));
                                break;
                            }
                        }
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return System.currentTimeMillis() - startTime;
    }

    public static void main(String[] args) {
        MultiTableExporter exporter = new MultiTableExporter();

        // 生成测试数据（1000个用户，3000个订单，6000个订单项，500个商品）
        List<User> users = exporter.generateUsers(1000);
        List<Order> orders = exporter.generateOrders(users);
        List<Product> products = exporter.generateProducts(500);
        List<OrderItem> items = exporter.generateOrderItems(orders, products);

        // 复制数据用于对比测试
        List<User> usersCopy = new ArrayList<>(users);
        List<Order> ordersCopy = new ArrayList<>(orders);
        List<OrderItem> itemsCopy = new ArrayList<>(items);
        List<Product> productsCopy = new ArrayList<>(products);

        // 测试优化后的导出
        long hashJoinTime = exporter.exportWithHashJoin(
            users, orders, items, products, "multi_table_hash_join.csv");

        // 测试普通多层嵌套导出（数据量较小时才能运行，否则耗时极长）
        long nestedTime = exporter.exportWithNestedLoops(
            usersCopy, ordersCopy, itemsCopy, productsCopy, "multi_table_nested.csv");

        // 输出时间对比
        System.out.println("优化的多表导出耗时: " + hashJoinTime + " 毫秒");
        System.out.println("普通多层嵌套导出耗时: " + nestedTime + " 毫秒");
        System.out.println("性能提升: " + ((double) (nestedTime - hashJoinTime) / nestedTime * 100) + "%");
    }
}