mysql 8.0 load data_MySQL 8.0 多线程 load data infile

测试环境：

4 core CPU

8 GB RAM

SSD Disk

Centos 7

导入的文件load_data.csv，大约8G左右。

单线程测试：

mysql > load data infile '/usr/local/mysql/mysql-files/load_data.csv'

into table single.single_load fields terminated by ',' optionally

enclosed by '"' lines terminated by '\n'

(user_id,visible,name,size,latitude,longitude,timestamp,public,description,inserted);

Query OK, 14244516 rows affected, 0 warnings (39 min 35.5036 sec)

Records: 14244516 Deleted: 0 Skipped: 0 Warnings: 0

一共耗时 39 min 35.5036 sec。

多线程测试：

mysql shell > util.importTable("/usr/local/mysql/mysql-files/load_data.csv",

{schema: "parallel", table: "parallel_load", columns:

["user_id","visible","name","size","latitude","longitude","timestamp","public","description"

,"inserted"], dialect: "csv-unix", skipRows: 0, showProgress: true,

fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy:

"\n",fieldsEnclosedBy: '"',threads: 8, bytesPerChunk: "1G", maxRate: "2G"})

Importing from file '/usr/local/mysql/mysql-files/load_data.csv' to table

`parallel`.`parallel_load` in MySQL Server at /tmp%2Fmysql.sock using 8 threads

[Worker01] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker02] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker03] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker04] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker05] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker06] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker07] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

[Worker08] parallel.parallel_load: Records: 1780564 Deleted: 0 Skipped: 0 Warnings: 0

100% (8.06 GB / 8.06 GB), 535.17 KB/s

File '/usr/local/mysql/mysql-files/load_data.csv' (8.06 GB) was imported in 6 min

30.0411 sec at 18.81 MB/s

Total rows affected in parallel.parallel_load: Records: 14244516 Deleted: 0 Skipped: 0

Warnings: 0

一共耗时：6 min 30.0411 sec

结果对比：

从测试结果上看，单线程耗时 39 min 35.5036 sec，多线程耗时6 min 30.0411 sec，多线程效率大约是单线程的6倍。

多线程load data infile的几个参数：

threads，指定线程数

bytesPerChunk，每个chunk的大小

maxRate，线程每秒能够处理的数据最大限制

多线程load data infile内部处理过程：

分析要导入的数据文件

将大的数据文件分割成数据块(chunk)

多线程将数据块导入到MySQL中