Perl脚本插入数据每行耗时1秒效率极低，如何优化提升运行速度？

以下Perl脚本执行数据库插入操作时每行耗时1秒，插入大量行时效率极低。若输入文件超过1,000,000行，该脚本预计需要近12天才能完成插入。

样本数据（行） #

/mnt/SYN/TEST-Dropbox/Documents/TempBM/10-07-19.docx
/mnt/SYN/CLIENT-DROPBOX-GSUITE/Dropbox-TEST/My Mac (TEST-iMac-Pro.local)/Documents/TempBM/10-07-19.docx

/mnt/SYN/TEST-Dropbox/Documents/TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-SYNOLOGY/A. TO BE TRANSCRIBED/*TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-SYNOLOGY/DropboxBackup/*TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3
/mnt/SYN/CLIENT-DROPBOX-GSUITE/Dropbox-TEST/My Mac (TEST-iMac-Pro.local)/Documents/TO BE TRANSCRIBED LATER/LASTNAME, FIRSTNAME, Tape 2 (Inaudible)/LASTNAME, FIRSTNAME, TAPE 2 SIDE B (Inaudible).mp3

原脚本内容 #

use strict;
use warnings;
use DateTime;
use DBI;

my $inputfile = shift || "-1";
my $startline = shift || "-1";
my $endline = shift || "-1";

if (($inputfile eq "-1") || ($startline eq "-1") || ($endline eq "-1") ){
        print "USAGE: $0 <name_of_file.txt> START_LINE_NUMBER END_LINE_NUMBER\n";
}

my $group_id = 1;
my $linecounter = 0;
my $dsn = "DBI:MariaDB:Potato1";
my $username = "root";
my $password = "";

my %attr = ( PrintError=>1, RaiseError=>1);
my $dbh = DBI->connect($dsn, $username, $password, \%attr);
print "Connected to MYSQL database";

my $sql = "INSERT INTO GMCfiles_tbl(file_id, file_add_date, group_id, filename, status, status_author, status_date) VALUES (?, ?, ?, ?, ?, ?, ?)";
my $stmt = $dbh->prepare($sql);
open(INFILE, "< $inputfile") or die "Could NOT open $inputfile";

while(<INFILE>) {
    my $data = $_;
    $linecounter++;
        if (length($data) < 5) {
            $group_id++;
            sleep 2;
        } else {
            my $file_add_date = DateTime->now;
            my $filename = $data;
            my $status = "New";
            my $status_author="";
            my $status_date = DateTime->now;

            if ( ($linecounter >= $startline) && ($linecounter <= $endline) ) {
                if ($stmt->execute($linecounter, $file_add_date, $group_id, $filename, $status, $status_author, $status_date)){
                    print "Row inserted successfully LINE $linecounter -- GROUP $group_id\n";
                }
                if ( ($linecounter > 0) && (($linecounter % 10) == 0) ) {
                    print "We're processing LINE $linecounter\n";
                }
            }
        }
}

close(INFILE);
$stmt->finish();
$dbh->disconnect;

效率优化方案 #

• 移除无意义的sleep调用：原脚本遇到长度小于5的行（一般是组间分隔空行）就会休眠2秒，这是最核心的耗时原因，直接删除sleep 2;这一行即可，百万行级别的导入光这一步就能节省几十小时的耗时。
• 开启事务批量提交：原脚本默认开启自动提交，每插入一行就触发一次磁盘落盘，IO开销极高。修改数据库连接属性增加AutoCommit => 0关闭自动提交，每插入1000~10000行调用一次$dbh->commit()，全部插入完成后再提交一次剩余数据，写入效率可提升数十倍。
• 使用批量插入语法：将单条INSERT语句改为多值插入格式，一次提交上百上千条数据，大幅降低和数据库的交互开销，比单条执行预处理语句效率再提升数倍。
• 减少冗余操作：原脚本每行都两次调用DateTime->now生成时间戳，可直接在SQL语句中使用数据库内置的NOW()函数代替，不需要从应用层传值；同时原脚本每行插入都打印日志、每10行打印一次进度，频繁的标准输出也会拖慢速度，可调整为每10000行打印一次进度即可。
• 数据库端临时优化：导入期间临时关闭目标表的非唯一索引、外键校验，导入完成后再重建开启；InnoDB引擎可临时调整innodb_flush_log_at_trx_commit参数为2，降低刷盘频率，导入完成后改回原有配置。
• 修复隐含问题减少无效运行：原脚本参数校验只打印用法但不退出，参数错误时会带着无效参数继续执行，在参数校验逻辑后增加exit 1;即可；另外读入的文件名带换行符，读取后增加chomp $data;处理，避免插入脏数据。

内容的提问来源于stack exchange，提问作者Arsal Shaikh