一、为什么需要批量导入优化
在日常的数据库运维中，我们经常会遇到大规模数据迁移的场景。比如，公司业务扩张需要将旧系统的数据迁移到新系统，或者数据分析团队需要将海量日志导入数据库进行离线分析。如果直接使用传统的单条INSERT语句，效率会非常低，甚至可能导致数据库连接超时或资源耗尽。
举个例子，假设我们要将100万条记录从CSV文件导入PolarDB，如果逐条插入，可能需要几个小时。而通过批量导入优化，可能只需要几分钟。这就是为什么我们需要关注批量导入技术——它能显著提升数据迁移的效率，降低系统负载。
二、PolarDB批量导入的几种方式
1. 使用LOAD DATA INFILE（MySQL兼容语法）
PolarDB兼容MySQL语法，因此可以直接使用LOAD DATA INFILE命令进行高效导入。这个命令的优势在于它是服务器端直接读取文件，避免了客户端到服务端的网络传输开销。
-- 示例：从CSV文件导入数据到PolarDB（MySQL语法）
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' -- 字段分隔符
ENCLOSED BY '"' -- 字段引用符
LINES TERMINATED BY '\n' -- 行分隔符
IGNORE 1 ROWS; -- 跳过CSV文件的第一行（通常是表头）
注意事项：
- 文件必须位于数据库服务器上，或者客户端有权限读取该文件。
- 需要确保PolarDB的
secure_file_priv参数允许读取目标路径。
2. 使用批量INSERT语句
如果数据源不在数据库服务器上，可以使用批量INSERT语句。相比单条INSERT，批量INSERT能减少网络往返和SQL解析开销。
-- 示例：批量插入多条记录（推荐每次插入1000-5000条）
INSERT INTO users (id, name, email) VALUES
(1, '张三', 'zhangsan@example.com'),
(2, '李四', 'lisi@example.com'),
(3, '王五', 'wangwu@example.com');
-- 可以一次性插入成千上万条，但要注意SQL语句长度限制
3. 使用外部工具（如pt-archiver或AWS DMS）
对于超大规模数据迁移，可以使用专业ETL工具，比如Percona的pt-archiver或AWS的数据库迁移服务（DMS）。这些工具支持断点续传、并行导入等高级功能。
# 示例：使用pt-archiver从旧数据库导出数据并导入到PolarDB
pt-archiver \
--source h=old_db_host,D=old_db,t=users,u=user,p=password \
--dest h=polar_db_host,D=new_db,t=users,u=user,p=password \
--where "1=1" \
--limit 10000 \
--commit-each \
--bulk-insert
三、性能优化技巧
1. 调整事务提交策略
默认情况下，每条INSERT语句都会触发事务提交，频繁的磁盘I/O会拖慢导入速度。可以通过以下方式优化：
-- 示例：开启大事务模式（适用于批量导入）
START TRANSACTION; -- 手动开启事务
-- 执行批量INSERT...
INSERT INTO users (...) VALUES (...);
INSERT INTO users (...) VALUES (...);
COMMIT; -- 最后统一提交
2. 关闭索引和约束检查
在大规模导入时，索引维护和唯一性检查会消耗大量资源。可以在导入前临时关闭它们：
-- 示例：禁用索引和约束（导入完成后记得重新启用）
ALTER TABLE users DISABLE KEYS; -- 禁用非唯一索引
SET FOREIGN_KEY_CHECKS = 0; -- 禁用外键检查
-- 执行导入操作...
ALTER TABLE users ENABLE KEYS; -- 重新启用索引
SET FOREIGN_KEY_CHECKS = 1; -- 重新启用外键检查
3. 并行导入
如果数据量极大，可以拆分成多个文件并行导入。例如，使用Shell脚本启动多个LOAD DATA任务：
# 示例：并行导入多个CSV文件（假设数据已按ID范围拆分）
for i in {1..4}; do
mysql -u user -p -e "LOAD DATA INFILE '/data/part$i.csv' INTO TABLE users ..." &
done
wait # 等待所有后台任务完成
四、常见问题与解决方案
1. 导入过程中磁盘空间不足
PolarDB的批量导入可能会产生大量临时数据，尤其是涉及大事务时。建议：
- 监控磁盘使用情况。
- 分批导入，避免单次事务过大。
2. 字符集不匹配导致乱码
如果源文件和数据库字符集不一致，可能导致乱码。解决方案：
-- 示例：指定文件字符集（如UTF8）
LOAD DATA INFILE 'data.csv'
INTO TABLE users
CHARACTER SET utf8mb4
...
3. 主键冲突或唯一键重复
在迁移数据时，可能会遇到主键冲突。可以通过INSERT IGNORE或ON DUPLICATE KEY UPDATE处理：
-- 示例：跳过重复记录
INSERT IGNORE INTO users (...) VALUES (...);
-- 示例：遇到重复时更新记录
INSERT INTO users (...) VALUES (...)
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);
五、总结
批量导入优化是数据库运维中的关键技能，尤其是在处理大规模数据迁移时。通过合理选择导入方式（如LOAD DATA INFILE、批量INSERT或专业工具），并结合事务控制、并行处理等技巧，可以显著提升效率。
在实际操作中，建议先在小规模数据上测试，确认无误后再进行全量迁移。同时，务必注意备份原始数据，避免意外丢失。
评论