mysql8.0千万数据量性能优化指南（一）：如何快速插入千万条测试数据_mysql 8.0 大批量insert性能优化

在处理大规模数据插入时，选择合适的插入方法对性能至关重要。本文将介绍三种在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();
1
2
3
4
5
6
7
8
9
10
11
12
13

方法二：批量插入

通过合并多个插入语句为一个大的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", ...);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

方法三：使用 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", ...);

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

然后，在数据库中运行以下命令：

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';   -- 指定行之间用换行符分隔

1
2
3
4
5
6
7
8

总结

通过对比三种插入方法，LOAD DATA 语句在处理大数据量插入时表现最佳，其次是批量插入，而存储过程适用于较小规模的数据插入。根据具体的数据量和性能要求，可以选择最适合的方法来优化MySQL数据库的插入性能。