mysql - 如何提高从一个数据库服务器中选择 100 万多行并将它们插入到另一个数据库服务器的 Perl/DBI 脚本的性能？

Question

我使用的是最新版本的 MySQL(工作台)、Perl 和 DBI。

我有两个独特的数据库，每个数据库都在独特的服务器上。我在 Server1 上的 DB1 上查询表 1，加载到一个数组中，然后使用 while 将所述表插入到 Server2 上的 DB2 上的表 2 中。

这不是直接复制，因为我实际上是在表 1 上进行选择。

我正在尝试加快我的脚本或在必要时更改查询。

我正在使用绑定(bind)并将准备语句移出循环，这确实很有帮助。但是，我最终还是进行了 160 万次调用以插入到 table2/db2/server2，这会占用大量时间(每次调用 3100 秒或大约 2-3 毫秒)。

while 循环中使用了两个 subs &insert2tempData 来加载我的 DB2 table2。它被调用了 160 万次 AND query_dev_load_local_dbs 用于查询 DB1 然后调用第二个子。

为简单起见，我将此准备留在循环中，但在我的代码中，它不在 while 循环中，而是在调用此子程序的子程序中。

sub insert2tempData {
    $query4tmpData = $Locdbh_NetCool->prepare ("INSERT INTO kpincpolldata.kpitempData
        (monitoredObjectId, monitoredInstId, pollTime, tdwTime, errorCode, value)
        VALUES(?, ?, ?, ?, ?, ?)");

    $query4tmpData->execute($row[0], $row[1], $row[2], $row[3], $row[4],
                            $row[5]);
    warn "Problem in retrieving results", $query4tmpData->errstr(), "\n"
        if $query4tmpData->err();

} #End sun insert to tempData

这是用于查询 server1、DB1 和 table1 的主子。它调用 while 循环

sub query_dev_load_local_dbs {
    $queryRemote = $Devdbh_NetCool->prepare("SELECT * FROM ncpolldata.pollData
                                   WHERE pollTime >= $STARTU AND pollTime < $ENDU
                                   AND monitoredObjectId = 1");

    $queryRemote->execute();

    while (@row = $queryRemote->fetchrow_array()) {

        #** Call to sub insert2tempData**
        &insert2tempData($Locdbh_NetCool);

        warn &print2log ("Problem in retrieving results"), $queryRemote->errstr(), "\n"
            if $queryRemote->err();
    } # End while

} # End sub query_dev_load_local_dbs

Answer 1

您在上面发布的代码完全有可能与您感知到的性能问题无关。在您使用的数据库服务器上插入那么多行可能需要很长时间。这是我要查看的内容:

1. 关闭 AutoCommit，它已启用。

   每次 insert 都提交会对 Server2 产生严重的性能影响，具体取决于它的配置方式。完全有可能将整个过程分批处理成一个事务可以加快速度。但这实际上取决于服务器的配置方式、索引的构建方式等。

   这将我们带到:

2. 称职的 DBA 可能会提供帮助。

   请与管理您的 MySQL 服务器的人联系，看看他们是否可以帮助您解决问题。谁知道呢，他们甚至可能拥有 DBA 工具，无需您在代码中做任何不同的事情就可以帮助解决您的问题，甚至允许您完全废弃此脚本。

   除非您是 DBA:

3. 您可能会浏览 Stack Exchange site对于 DBA。

您发布的代码中可能有一些小问题需要修复，但如果数据库是瓶颈，那也没关系。找到瓶颈!