MySQL快速导入千万条数据(3)

原创

雪人

发布于 2022-10-13 09:37:48

1.3K0

发布于 2022-10-13 09:37:48

文章被收录于专栏：DataOps

接上文，本次在较高性能的X86物理机上，做真实生产环境的大数据量导入测试。

一、测试环境

■ CPU是24核，每核2线程，即48CPU

$ lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                48
On-line CPU(s) list:   0-47
Thread(s) per core:    2
Core(s) per socket:    12
座：                 2
NUMA 节点：         2
厂商 ID：           GenuineIntel
CPU 系列：          6
型号：              85
型号名称：        Intel(R) Xeon(R) Gold 5118 CPU @ 2.30GHz
步进：              4
CPU MHz：             2301.000

■ 内存376G

$ free -g
              total        used        free      shared  buff/cache   available
Mem:            376          21           5         126         349         226
Swap:            16           0          16

■ 数据磁盘是SAS磁盘

Model: AVAGO HW-SAS3508 (scsi)
Disk /dev/sdb: 12.0TB

■ 磁盘IO写入性能测试，结果：1.1GB/s

[root@adg1:0 /u01]# time dd if=/dev/zero of=/u01/test bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 7.51518 s, 1.1 GB/s

■ 磁盘IO读取性能测试，结果：3.9GB/s

[root@adg1:0 /u01]# time dd if=/u01/test of=/dev/null bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 2.09241 s, 3.9 GB/s

■ 操作系统环境是红帽7.4

NAME="Red Hat Enterprise Linux Server"
VERSION="7.4 (Maipo)"
PRETTY_NAME="Red Hat Enterprise Linux Server 7.4 (Maipo)"

■ 文件系统采用xfs

Filesystem                  Type      Size  Used Avail Use% Mounted on
/dev/mapper/vgora-lv_u01    xfs        11T  792G   11T   8% /u01

■ db version

Server version: 5.5.56-MariaDB MariaDB Server

二、命令行导入方式

使用最原始的命令行方式测试，如下：

mysql -uroot -ppasswd' --default-character-set=utf8 dbname < mysql.sql

用一个简单的导入脚本，用于记录时间：

cat > test.sh << EOF
echo "Start ...("\`date "+%Y%m%d-%H:%M:%S"\`")"
mysql -uroot -p'passwd' --default-character-set=utf8 dbname < \$1
echo "Completed.("\`date "+%Y%m%d-%H:%M:%S"\`")"
EOF
chmod +x test.sh

测试结果如下：耗时57分钟

Start ...(20220227-12:22:12)
Completed.(20220227-13:19:04)

可见这个命令行导入方式，即使在实际的高性能生产环境，几千万条数据的导入，长达一个小时的时间，也是几乎无法接受的。但如果几百万行数据的导入，十几分钟时间还可以勉强接受。

三、LOAD DATA导入方式

关于LOAD DATA的详细介绍请网搜，此处不再赘述。

首先，修改原SQL文件格式为LOADDATA可用的csv文本格式：

sed -i "s/INSERT INTO \`tablename\` VALUES (//g" mysql.sql
sed -i "s/);//g" mysql.sql

经过以上自动编辑处理，原SQL文件内容成为如下格式：

'40601438', 'CF_0105', '121589425857.3000', '56814', null, '121589425857.3000'
'40601439', 'CF_0105', '113776588.1400', '56815', null, '113776588.1400'

然后，执行导入，如下所示：导入3000万条数据，耗时104秒

root@localhost:dbname> LOAD DATA LOCAL INFILE '/u01/mysql/mysql.sql'
    -> INTO TABLE tablename
    -> FIELDS TERMINATED BY ', '
    -> ENCLOSED BY "'"
    -> LINES TERMINATED BY '\n'
    -> IGNORE 34 lines;
Query OK, 30578985 rows affected, 5932 warnings (1 min 43.76 sec)
Records: 30578985  Deleted: 0  Skipped: 0  Warnings: 5932
root@localhost:dbname> select count(*) from tablename;
+----------+
| count(*) |
+----------+
| 30578985 |
+----------+

创建索引耗时1分钟左右：

root@localhost:dbname> ALTER TABLE tablename ADD INDEX `master_id` (`master_id`);
Query OK, 0 rows affected (1 min 2.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost:dbname> ALTER TABLE tablename ADD INDEX `code` (`code`);
Query OK, 0 rows affected (1 min 43.77 sec)
Records: 0  Duplicates: 0  Warnings: 0