在处理大规模数据插入时,选择合适的插入方法对性能至关重要。本文将介绍三种在MySQL
8.0中快速插入千万条测试数据的方法,并比较它们的性能和适用场景。总的来说,效率排序如下: 方法一 < 方法二 < 方法三
方法一:存储过程
存储过程适用于数据量较小的场景。当数据量较大或字段较多时,性能会显著下降。其优点是实现简单,但扩展性和性能较差。
mysql>
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into table_name values(i, i, i, ...);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
方法二:批量插入
通过合并多个插入语句为一个大的INSERT INTO ... VALUES
语句,可以减少循环次数,提高插入效率。下面是使用Java生成批量插入SQL文件的代码:
public static void genData() {
String fileName = "insert_data.sql"; // SQL 文件名
int numRecords = 5000000; // 要生成的记录数
int id = 1;
int maxId = id + numRecords;
String tableName = "table_name"; // 表名
int batchSize = 500; // 批处理大小
try (BufferedWriter writer = new BufferedWriter(new FileWriter(fileName))) {
for (int i = id; i < maxId; i++) {
if ((i - id) % batchSize == 0) {
if (i != id) {
writer.write(";\n");
}
writer.write("INSERT INTO " + tableName + " (`col1`, `col2`, ...) VALUES\n");
}
String values = generateValues(i);
if (i != id && i % batchSize == 0) {
writer.write(values);
} else {
writer.write(values + ",\n");
}
}
System.out.println("SQL 文件生成完成:" + fileName);
} catch (IOException e) {
e.printStackTrace();
}
}
private static String generateCaValues(int id) {
return String.format("(%s, %s, ...)", id, "value1", "value2", ...);
}
方法三:使用 LOAD DATA 语句
LOAD DATA
语句可以非常快速地从文本文件中读取数据到数据库表中,适用于需要插入大量数据的场景。
首先,生成数据的文本文件
public static void genData() {
String outputFileName = "data.txt"; // 文件名
int numRecords = 5000000; // 要生成的记录数
int id = 1;
int maxId = id + numRecords;
try (BufferedWriter writer = new BufferedWriter(new FileWriter(outputFileName))) {
for (int i = id; i < maxId; i++) {
String record = generateCaValues(i);
writer.write(record);
writer.newLine();
}
System.out.println("Data exported to " + outputFileName);
} catch (IOException e) {
e.printStackTrace();
}
}
private static String generateValues(int id) {
return String.join("\t","value1", "value2", ...);
}
然后,在数据库中运行以下命令:
mysql>
LOAD DATA INFILE '/path/to/data.txt'
INTO TABLE table_name
FIELDS TERMINATED BY '\t' -- 如果数据由制表符分隔
-- FIELDS TERMINATED BY ' ' -- 如果数据由空格分隔
OPTIONALLY ENCLOSED BY '"' -- 特定字段用引号包裹
LINES TERMINATED BY '\n'; -- 指定行之间用换行符分隔
总结
通过对比三种插入方法,LOAD DATA 语句在处理大数据量插入时表现最佳,其次是批量插入,而存储过程适用于较小规模的数据插入。根据具体的数据量和性能要求,可以选择最适合的方法来优化MySQL数据库的插入性能。