mysql - 使用 Perl 脚本为非常大的文件 (60GB) 提高在 MySQL 中插入表的速度

Question

我正在使用 Perl 脚本从 xml 文件(包含超过十亿行)填充 MySQL 中的表以查找感兴趣的行。该脚本运行非常顺利，直到第 15M 行，但之后它开始呈指数增长。就像第一个 1000000 行一样，它需要大约 12 秒的时间来解析并将其写入数据库，但在 1500 万行之后，解析和写入相同数量的行所需的时间约为 43 秒。

我按照建议将 innodb_buffer_pool_size 从 128M 增加到 1024M

Insertion speed slowdown as the table grows in mysql answered by Eric Holmberg

时间要求分别下降到 ~7 秒和 ~32 秒，但它仍然很慢，因为我有一个巨大的文件要处理，而且它的时间要求不断增加。

我还删除了任何 Primary key 和 Index 的创建，认为它可能会导致一些问题(虽然不确定)

下面是代码片段:

$dbh = DBI->connect('dbi:mysql:dbname','user','password') or die "Connection Error: $DBI::errstr\n";

$stmt = "DROP TABLE IF EXISTS dbname";
$sth = $dbh->do($stmt);

$sql = "create table db(id INTEGER not null, type_entry VARCHAR(30) not null, entry VARCHAR(50))";
$sth = $dbh->prepare($sql);
$sth->execute or die "SQL Error: $DBI::errstr\n";
open my $fh1, '<', "file.xml" or die $!;

while (<$fh1>)
{
    if ($_=~ m/some pattern/g)
    {
        $_=~ s/some pattern//gi;
        $id = $_;
    }
    elsif ($_=~ m/some other pattern/)
    {
        $_=~ s/\s|(\some other pattern//gi;
        $type = $_;
    }
    elsif ($_=~ m/still some other pattern/)
    {
        $_=~ s/still some other pattern//gi;
        $entry = $_;
    }
    if($id ne "" && $type ne "" && $entry ne "")
    {
        $dbh->do('INSERT INTO dbname (id, type_entry, species) VALUES (?, ?, ?)', undef, $id, $type, $entry);
    }
}

该数据库将包含大约 170 万个条目。还可以做些什么来减少时间？

提前致谢

编辑 1:

谢谢大家的帮助从早上开始，我一直在努力执行所有被告知的事情，并检查我是否在时间上有明显的减少。所以我做了什么:

1. 我按照@ikegami 的指示删除了两次匹配模式，但是是的，我确实需要替换。
2. 我使用了hash(正如@ikegami 所说)
3. 我使用了LOAD DATA LOCAL INFILE(如@ikegami、@ysth 和@ThisSuitIsBlackNot 所述)。但是我已经将它嵌入到我的代码中以占用文件然后将其处理到数据库。这里的文件由脚本动态写入，当它达到 1000 个条目时，它被写入数据库。

连续1000000行的运行时间是

13 秒

11 秒

24 秒

22 秒

35 秒

34 秒

47 秒

45 秒

58 秒

57 秒 .....

(想发布图片，但......声誉)

编辑2:

我检查了时间并跟踪了脚本将其写入数据库所需的时间；令我惊讶的是它是线性的。现在我从这里得出的结论是，while 循环存在一些问题，我认为它会以指数方式增加时间，因为它必须在每次迭代时转到行号，并且当它深入到文件时，它必须计算更多的数字到达下一行的行数。

对此的任何评论

编辑 3

$start_time = time();
$line=0;
open my $fh1, '<', "file.xml" or die $!;
while (<$fh1>)
{
$line++;
%values;
if ($_=~ s/foo//gi)
{
$values{'id'} = $_;
}
elsif ($_=~ s/foo//gi)
{
$values{'type'} = $_;
}
elsif ($_=~ s/foo//gi)
{
$values{'pattern'} = $_;
}
if (keys(%values) == 3)
{
$no_lines++;
open FILE, ">>temp.txt" or die $!;
print FILE "$values{'id'}\t$values{'type'}\t$values{'pattern'}\n";
close FILE;
if ($no_lines == 1000)
{
#write it to database using `LOAD DATA LOCAL INFILE` and unlink the temp.txt file
}
undef %values;
}
if($line == ($line1+1000000))
{
$line1=$line;
$read_time = time();
$processing_time = $read_time - $start_time - $processing_time;
print "xml file parsed till line $line, time taken $processing_time sec\n";
}
}

回答:

首先，很抱歉这么久才回复；当我再次从 root 到 top 开始使用 Perl 时，这一次使用 use strict 变得很清楚，这帮助我保持了线性时间。在处理大型 Xml 文件时，使用 XML 解析器 也是一件好事。

补充一点，MySQL 的插入速度没有任何关系，它始终是线性的

感谢大家的帮助和建议

Answer 1

我猜瓶颈是实际的插入。生成 INSERT 语句，将它们放在一个文件中，然后使用 mysql 命令行工具执行该文件肯定会更快一些。

您可以尝试创建插入大量行的 INSERT 语句，而不是单个语句。

或者最好完全避免 INSERT 语句。我认为 mysql 命令行工具具有从 CSV 文件填充数据库的功能。这可能会产生更多的速度。

更好的是，如果您有权访问托管数据库的机器的文件系统，则可以使用LOAD DATA INFILE。

---

您的 Perl 代码也可以进行一些清理。

• 您对每个模式都搜索了两次？变化

  if (/foo/) { s/foo//gi; $id = $_ }
  

  到

  if (s/foo//gi) { $id = $_ }
  

• 实际上，您是否需要替换？这可能会更快

  if (/foo (.*)/) { $id = $1 }
  

• 看起来您可以按照以下方式做更多的事情

  my ($k, $v) = split(/:\s*/);
  $row{$k} = $v;
  

  而不是那个巨大的 if。

• 此外，如果您使用散列，则可以使用以下内容进行最后检查:

  if (keys(%row) == 3)