Spring Boot批量处理用户数据文件与MySQL数据库同步需求问询

Nice problem to solve—processing millions of records efficiently while sticking to those business rules requires a mix of smart I/O handling, batch database operations, and leveraging MySQL's strengths. Let's walk through a practical solution tailored for Spring Boot.

We'll break this into three core parts:

1. Efficiently reading large files without memory overload
2. Batch inserting records while skipping existing users
3. Handling users present in the database but missing from the file (assuming common use cases like marking inactive/deletion)

1. Efficient Line-by-Line File Reading #

Loading millions of lines into memory will cause out-of-memory errors, so we'll read the file streamingly using BufferedReader, and batch records for database processing.

@Service
public class UserDataProcessor {

    private final JdbcTemplate jdbcTemplate;

    public UserDataProcessor(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void processUserFile(String filePath) throws IOException {
        try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
            String line;
            List<UserBatchEntry> batch = new ArrayList<>(1000); // Adjust batch size based on your DB capacity
            while ((line = reader.readLine()) != null) {
                String[] parts = line.split("\\|");
                if (parts.length != 2) {
                    // Log invalid lines and skip
                    System.err.println("Skipping invalid line: " + line);
                    continue;
                }
                try {
                    Long userId = Long.parseLong(parts[0].trim());
                    Long departmentId = Long.parseLong(parts[1].trim());
                    batch.add(new UserBatchEntry(userId, departmentId));
                } catch (NumberFormatException e) {
                    System.err.println("Skipping line with invalid numeric values: " + line);
                }

                // Process batch when it reaches our target size
                if (batch.size() == 1000) {
                    insertBatch(batch);
                    batch.clear();
                }
            }
            // Process remaining records in the final partial batch
            if (!batch.isEmpty()) {
                insertBatch(batch);
            }
        }
    }

    // Simple DTO to hold batch data
    private static class UserBatchEntry {
        Long userId;
        Long departmentId;

        public UserBatchEntry(Long userId, Long departmentId) {
            this.userId = userId;
            this.departmentId = departmentId;
        }
    }
}

2. Batch Insert with "Skip Existing Users" Logic #

MySQL offers two efficient ways to handle this: INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE (with a no-op update). First, ensure your users table has a unique constraint on user_id (it should be the primary key).

Option 1: Spring JDBC Batch Update (Balanced Performance & Ease) #

This is the most flexible approach for most use cases:

@Transactional
private void insertBatch(List<UserBatchEntry> batch) {
    String sql = """
        INSERT INTO users (user_id, department_id) 
        VALUES (?, ?) 
        ON DUPLICATE KEY UPDATE user_id = user_id -- No-op update to skip existing records
        """;

    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            UserBatchEntry entry = batch.get(i);
            ps.setLong(1, entry.userId);
            ps.setLong(2, entry.departmentId);
        }

        @Override
        public int getBatchSize() {
            return batch.size();
        }
    });
}

Note: INSERT IGNORE works too, but it skips all errors (not just duplicates). The ON DUPLICATE KEY UPDATE approach is safer if you want to catch other issues.

Option 2: MySQL LOAD DATA INFILE (Ultra-Fast for Mass Imports) #

If you have access to the MySQL server's filesystem (or can upload the file there), this is the fastest way to import millions of records:

public void loadFileDirectly(String filePath) {
    String sql = """
        LOAD DATA INFILE ? 
        IGNORE INTO TABLE users 
        FIELDS TERMINATED BY '|' 
        LINES TERMINATED BY '\\n' 
        (user_id, department_id)
        """;
    jdbcTemplate.update(sql, filePath);
}

For files on your application server, use LOAD DATA LOCAL INFILE and ensure MySQL's local_infile setting is enabled.

3. Handling Users Missing from the File #

Assuming you want to mark these users as inactive or delete them, the most efficient method uses a temporary table to avoid expensive per-record checks:

@Transactional
public void handleMissingUsers(String filePath) throws IOException {
    // Step 1: Create temporary table to store valid user IDs from the file
    jdbcTemplate.execute("CREATE TEMPORARY TABLE temp_user_ids (user_id BIGINT PRIMARY KEY)");

    // Step 2: Load user IDs from the file into the temp table
    String loadSql = """
        LOAD DATA INFILE ? 
        INTO TABLE temp_user_ids 
        FIELDS TERMINATED BY '|' 
        LINES TERMINATED BY '\\n' 
        (user_id, @dummy) -- Ignore department ID for this step
        """;
    jdbcTemplate.update(loadSql, filePath);

    // Step 3: Mark users not in the temp table as inactive (or delete)
    String updateSql = "UPDATE users SET is_active = FALSE WHERE user_id NOT IN (SELECT user_id FROM temp_user_ids)";
    jdbcTemplate.update(updateSql);

    // Alternative: Delete missing users
    // String deleteSql = "DELETE FROM users WHERE user_id NOT IN (SELECT user_id FROM temp_user_ids)";
    // jdbcTemplate.update(deleteSql);

    // Temp tables auto-drop when the connection closes, but we can explicitly clean up
    jdbcTemplate.execute("DROP TEMPORARY TABLE temp_user_ids");
}

Performance Tips #

• Batch Size: Test values between 1000-5000—too large can cause memory pressure, too small increases database round-trips.
• Transactions: Wrap batch operations in transactions to reduce commit overhead.
• Async Processing: Use Spring's @Async or a TaskExecutor to run imports in the background without blocking your application.
• Indexing: Ensure user_id is the primary key to make duplicate checks instantaneous.

内容的提问来源于stack exchange，提问作者Tomasz Bawor