Java插入大量数据优化

FCAT 2024/12/4 sql

如果每次插入一万条数据需要 12 秒，意味着插入操作的性能已经不理想，尤其是在处理大量数据时。为了提高批量插入性能，以下是一些优化的建议：

# 1. 增大批量插入的数量

目前每批次插入 10,000 条数据，而插入速度相对较慢。你可以尝试增加批次的大小，例如将批次的大小增加到 20,000 或 50,000 条数据，看能否进一步提高性能。大批量插入可以减少数据库与应用程序之间的通信开销。

int batchSize = 20000;  // 增加批次大小
int count = 0;
for (User user : largeDataList) {
    statement.setInt(1, user.getId());
    statement.setString(2, user.getName());
    statement.setInt(3, user.getAge());
    statement.addBatch();  // 添加到批量
    
    if (++count % batchSize == 0) {
        statement.executeBatch();  // 执行当前批次
        connection.commit();  // 提交事务
    }
}
// 处理剩余数据
if (count % batchSize != 0) {
    statement.executeBatch();
    connection.commit();
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 2. 确保事务提交的效率

每次批量插入后，你都会提交一次事务。确保每个批次的事务提交是必须的，且提交的次数越少越好。如果批次更大，可以减少事务提交的次数。

保持 setAutoCommit(false) 来禁用自动提交事务，每次批量插入完成后再手动提交事务：

// 禁用自动提交
connection.setAutoCommit(false);

for (User user : largeDataList) {
    statement.setInt(1, user.getId());
    statement.setString(2, user.getName());
    statement.setInt(3, user.getAge());
    statement.addBatch();
}

// 执行批量插入
statement.executeBatch();

// 提交事务
connection.commit();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 3. 禁用索引（索引优化）

如果表中有索引，尤其是在大批量插入数据时，索引更新会拖慢插入速度。你可以在批量插入之前禁用表的索引，并在插入完成后重新启用索引。

在 MySQL 中，你可以通过以下语句禁用和启用索引：

-- 禁用索引
ALTER TABLE users DISABLE KEYS;

-- 启用索引
ALTER TABLE users ENABLE KEYS;

1
2
3
4
5

# 4. 使用数据库连接池

每次插入时都会创建和销毁数据库连接。为了避免这种开销，使用连接池（如 HikariCP、C3P0、DBCP 等）可以复用数据库连接，减少连接建立的时间。

HikariCP 示例配置：

HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/your_database");
dataSource.setUsername("username");
dataSource.setPassword("password");
dataSource.setMaximumPoolSize(10);

Connection connection = dataSource.getConnection();

1
2
3
4
5
6
7

# 5. 减少锁和事务开销

在插入数据时，尽量避免锁定操作，尤其是在事务并发较高的环境下。可以考虑减少事务的使用范围，只将批量插入操作包裹在一个事务中，避免每次插入都开启新事务。

# 6. 调整数据库配置

数据库的配置可能会影响性能，特别是在处理大量数据时。以下是一些 MySQL 配置建议：

innodb_flush_log_at_trx_commit：该配置决定了事务提交时是否将日志写入磁盘。设置为 2 可以提高写入性能（不完全一致性，适合批量操作）。
```
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
```
1
innodb_buffer_pool_size：增加 InnoDB 缓冲池大小，以便可以缓存更多的数据，减少磁盘 I/O。
```
SET GLOBAL innodb_buffer_pool_size = <value>;
```
1
max_allowed_packet：在进行批量插入时，确保 max_allowed_packet 的值足够大，否则会导致插入的数据包过大而失败。
```
SET GLOBAL max_allowed_packet = 64M;
```
1

# 7. 避免长时间的 `SELECT` 或 `UPDATE` 操作

批量插入操作期间，如果数据库同时执行大量的 SELECT 或 UPDATE 操作，会影响性能。尽量避免在插入大量数据时执行其他可能引起锁的操作。

# 8. 使用 `LOAD DATA` 来替代 `INSERT`

对于非常大的数据量，使用 LOAD DATA 比 INSERT 更加高效。你可以先将数据写入一个 CSV 文件，然后使用 LOAD DATA INFILE 直接将数据导入数据库。

String loadSql = "LOAD DATA LOCAL INFILE '/path/to/file.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (id, name, age)";
statement.executeUpdate(loadSql);

1
2

# 9. 使用 MySQL 复制表插入

如果可能，使用 MySQL 的 REPLACE INTO 或 INSERT INTO ... SELECT 来进行批量数据插入。例如，如果需要从临时表插入数据到目标表：

INSERT INTO target_table (id, name, age)
SELECT id, name, age FROM temp_table;

1
2

这种方法相比于单条 INSERT 语句更加高效，特别是在表中有大量数据的情况下。

# 10. 检查硬件资源

如果数据库的硬件资源（如 CPU、内存、磁盘速度等）不足以支撑大量数据的写入，插入速度也会受到影响。可以通过增加硬件资源，或者优化磁盘 I/O 性能（如使用 SSD）来提高数据库的插入性能。

# 总结

批量大小：增加每批次插入的数据量，减少提交次数。
禁用自动提交：保持事务开启直到批量插入完成再提交。
禁用索引：插入大量数据前禁用索引，插入完成后再启用索引。
数据库连接池：使用数据库连接池提高连接复用效率。
数据库配置优化：调整 MySQL 配置以优化批量插入性能。
LOAD DATA：如果数据量极大，考虑使用 LOAD DATA 从文件批量导入数据。
硬件优化：确保硬件资源充足，特别是磁盘 I/O。

通过这些优化措施，可以显著提高数据插入的效率。你可以根据实际情况进行调整，找到最适合你应用场景的优化方案。

技术架构笔记

Choose mode