mysql批量加载

基础概念

MySQL 批量加载是指一次性将大量数据插入到 MySQL 数据库中的过程。相比于逐条插入数据，批量加载可以显著提高数据插入的效率，减少网络开销和数据库负载。

优势

1. 提高性能：批量加载减少了与数据库的交互次数，从而提高了数据插入的速度。
2. 减少网络开销：通过减少网络请求的数量，降低了网络传输的数据量。
3. 降低数据库负载：批量操作减少了数据库的 I/O 操作和锁竞争，提高了数据库的整体性能。

类型

1. 使用 LOAD DATA INFILE：这是 MySQL 提供的一个高效的文件导入命令，可以直接从文件中批量加载数据到表中。
2. 使用 INSERT INTO ... VALUES (...), (...), ...：通过在 INSERT 语句中列出多组值，实现批量插入。
3. 使用 INSERT INTO ... SELECT ...：从一个表中选择数据并插入到另一个表中，适用于数据迁移或复制。

应用场景

1. 数据导入：将外部数据源（如 CSV 文件）导入到 MySQL 数据库中。
2. 数据迁移：将数据从一个数据库表迁移到另一个表。
3. 批量更新：一次性更新大量记录。

示例代码

使用 LOAD DATA INFILE

假设我们有一个 CSV 文件 data.csv，内容如下：

id,name,age
1,Alice,30
2,Bob,25
3,Charlie,35

我们可以使用以下 SQL 语句将数据批量加载到 users 表中：

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

使用 INSERT INTO ... VALUES (...), (...), ...

假设我们要插入多条记录到 users 表中：

INSERT INTO users (id, name, age)
VALUES (1, 'Alice', 30),
       (2, 'Bob', 25),
       (3, 'Charlie', 35);

常见问题及解决方法

问题：批量加载时遇到 ERROR 2013 (HY000): Lost connection to MySQL server during query

原因：可能是由于数据量过大，导致 MySQL 服务器在处理过程中超时。

解决方法：

1. 增加 max_allowed_packet：可以通过修改 MySQL 配置文件（如 my.cnf）来增加允许的最大数据包大小。
2. 增加 max_allowed_packet：可以通过修改 MySQL 配置文件（如 my.cnf）来增加允许的最大数据包大小。
3. 调整 innodb_buffer_pool_size：增加 InnoDB 缓冲池的大小，以提高处理大文件的能力。
4. 调整 innodb_buffer_pool_size：增加 InnoDB 缓冲池的大小，以提高处理大文件的能力。
5. 分批加载：如果数据量非常大，可以考虑将数据分成多个小批次进行加载。

参考链接

• MySQL官方文档 - LOAD DATA INFILE
• MySQL官方文档 - INSERT Statement

通过以上方法，可以有效地解决 MySQL 批量加载过程中遇到的常见问题，提高数据插入的效率和性能。