mysql - Perl 和 mysql，慢得要命，如何加速

Question

unit
id fir_name sec_name
author
id name unit_id
author_paper
id author_id paper_id

我想统一作者['same author'表示名字相同，单位的fir_name相同]，同时要改author_paper表。

这是我的做法:

$conn->do('create index author_name on author (name)');
my $sqr = $conn->prepare("select name from author group by name having count(*) > 1");
$sqr->execute();
while(my @row = $sqr->fetchrow_array()) {
  my $dup_name = $row[0];
  $dup_name = formatHtml($dup_name);
    my $sqr2 = $conn->prepare("select id, unit_id from author where name = '$dup_name'");
    $sqr2->execute();

    my %fir_name_hash = ();
    while(my @row2 = $sqr2->fetchrow_array()) {
        my $author_id = $row2[0];
        my $unit_id = $row2[1];
        my $fir_name = getFirNameInUnit($conn, $unit_id);
        if (not exists $fir_name_hash{$fir_name}) {
            $fir_name_hash{$fir_name} = []; #anonymous arr reference
        }
        $x = $fir_name_hash{$fir_name};
        push @$x, $author_id;
    }

    while(my ($fir_name, $author_id_arr) = each(%fir_name_hash)) {
        my $count = scalar @$author_id_arr;
        if ($count == 1) {next;}
        my $author_id = $author_id_arr->[0];
        for ($i = 1; $i < $count; $i++) {
            #print "$author_id_arr->[$i] => $author_id\n";
            unifyAuthorAndAuthorPaperTable($conn, $author_id, $author_id_arr->[$i]); #just delete in author table, and update in author_paper table 
        }
    }
}

从作者中选择计数(*)； #240,000从作者中选择 count(distinct(name))； #7,7000它非常慢!我已经运行了 5 个小时，它只删除了大约 4,0000 个重复名称。如何让它运行得更快。我渴望你的建议

Answer 1

您不应该在循环中准备第二个 sql 语句，您可以在使用 ? 占位符时真正利用准备工作:

$conn->do('create index author_name on author (name)');

my $sqr = $conn->prepare('select name from author group by name having count(*) > 1');

# ? is the placeholder and the database driver knows if its an integer or a string and 
# quotes the input if needed.
my $sqr2 = $conn->prepare('select id, unit_id from author where name = ?');

$sqr->execute();
while(my @row = $sqr->fetchrow_array()) {
  my $dup_name = $row[0];
  $dup_name = formatHtml($dup_name);

    # Now you can reuse the prepared handle with different input
    $sqr2->execute( $dup_name );

    my %fir_name_hash = ();
    while(my @row2 = $sqr2->fetchrow_array()) {
        my $author_id = $row2[0];
        my $unit_id = $row2[1];
        my $fir_name = getFirNameInUnit($conn, $unit_id);
        if (not exists $fir_name_hash{$fir_name}) {
            $fir_name_hash{$fir_name} = []; #anonymous arr reference
        }
        $x = $fir_name_hash{$fir_name};
        push @$x, $author_id;
    }

    while(my ($fir_name, $author_id_arr) = each(%fir_name_hash)) {
        my $count = scalar @$author_id_arr;
        if ($count == 1) {next;}
        my $author_id = $author_id_arr->[0];
        for ($i = 1; $i < $count; $i++) {
            #print "$author_id_arr->[$i] => $author_id\n";
            unifyAuthorAndAuthorPaperTable($conn, $author_id, $author_id_arr->[$i]); #just delete in author table, and update in author_paper table 
        }
    }
}

这也应该加快速度。