使用PHP快速将数百万条记录插入SQL Server的方法

Question

我有一个包含2,000,000行文本的文件，并希望将每一行插入到数据库行中，因此需要使用php文件将2,000,000条记录插入到sql server中。如何以最快的方式完成这个任务？

为了测试，我正在使用以下代码：

for ( $i=1 ; $i<=2000000 ; $i++)
    {
        $sql = "INSERT INTO BlastSequenceDim (Seq_id) VALUES ('$i')";
        $stmt = sqlsrv_query( $conn, $sql);
        if( $stmt === false ) 
        {
            die( print_r( sqlsrv_errors(), true));
        }
    }

但是，这需要很多时间。这个查询能在几秒钟内执行吗？

谢谢。

- Alaa

---

这个文本文件包含什么内容？ - Charlotte Dunois

我认为在通过连接和相关开销时，没有办法在几秒钟内插入200万条记录，但批量插入会比逐条插入更快。详情请参见https://dev59.com/Mm035IYBdhLWcg3wQtsg。 - Technoh

这应该不是一个真正的问题，可能只需要几秒钟来运行它。 如果可以的话，最快的方法是按块进行多个插入。 - Loenix

@David Vogel 连接仅在 for 循环外部打开一次。 - Alaa

Answer 1

请使用批量插入方法，这将显著提高性能并节省时间。

在 SQL 中了解有关批量插入查询的更多信息，请访问：https://msdn.microsoft.com/en-us/library/ms188365.aspx 您也可以通过跳过 PHP 直接访问 SQL 数据库来节省时间。这是初始填充数据库的常见做法。

另一个链接：http://www.dotnettwitter.com/2012/02/bulk-import-insert-and-update-using.html

- Shadi Zidan

---

你有这个批量插入的快速示例吗？我会阅读链接，但如果你有一个简单的示例会更好。 - Alaa

不要复制粘贴示例。我更希望你阅读以下文章，它应该是你所寻找的示例。[链接] http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file - Shadi Zidan

我想先进行测试。我现在没有文本文件。我的一个应用程序任务是生成这个文本文件，然后将其插入到数据库中。我现在处于测试阶段。我想知道将数百万条记录插入SQL Server需要多长时间。你可以看到我的代码中我只插入了“i”值，因为我现在还处于测试阶段，还没有深入研究。 - Alaa

亲爱的Alaa。这是插入大量行的最高效方式。如果你真的对测试感兴趣，请在此链接中找到相关内容。https://venzi.wordpress.com/2012/03/31/loading-data-fast-regular-insert-vs-bulk-insert/ - Shadi Zidan

如果您认为这有帮助，请标记为正确答案。谢谢。 - Shadi Zidan

Answer 2

不要单独运行每个查询。将查询连接在一起，一次性运行。

for ( $i=1 ; $i<=2000000 ; $i++)
{
    $sql .= "INSERT INTO BlastSequenceDim (Seq_id) VALUES ('$i');";
}

$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) 
{
    die( print_r( sqlsrv_errors(), true));
}

- Ray

---

虽然这是一个解决方案，但还有更有效的方法。 - C_B

@Ray，我正在尝试你的代码，但出现了以下错误：致命错误：在C:\inetpub\wwwroot\webclient\saveResult.php的第106行尝试分配225778224字节时，已用尽134217728字节的允许内存大小。第106行是：$stmt = sqlsrv_query( $conn, $sql); - Alaa

@Alaa 这并不是一个好的解决方案 - 我强烈不建议实施它。该解决方案有一个由文件大小确定的终点。你当然可以增加PHP内存，但那又怎样呢？ - Peter VARGA

Answer 3

通常瓶颈在于PHP不支持多线程。这意味着在您的情况下，一个进程按顺序读取文件并逐行插入，即使您的服务器/PC有8个或更多核心。

我曾经遇到过相同的问题，需要将大量值插入到memcache键中，我使用php pthread解决了这个问题。

当它是一个重复的任务[就像我的情况一样]时，肯定值得用pthread实现，尽管需要更多的工作。

提示：启动的线程总数不应该超过number_of_cpu_cores x 1.5。

只有一个小问题：如何协调从文本文件中读取数据。在这里，我建议向每个线程传递两个数字：

• 从第X行开始
• 偏移量，添加到X中，以便跳过其他线程读取的行。

您会惊讶于性能的提高！结合其他答案回答您的问题，它将是无与伦比的...

- Peter VARGA

---

我认为这个答案是误导性的。在大多数情况下，如果您在循环中执行“读取行；插入到SQL”操作，PHP进程空闲时间超过95%。每次插入后，PHP必须等待数据传输到SQL服务器（可能通过网络），并等待SQL将其提交到磁盘。并行运行更多线程将提高吞吐量，但“最佳”线程数不太可能与核心数相关，因为在这种情况下，PHP不受CPU限制。 - 9072997

Answer 4

一种实现方法是将整个数据集编码为JSON并在单个查询中发送。有几种不同的方法可以实现这一点。Microsoft的文档此处讨论了其中一种方式。他们在示例中插入了JSON对象（因此，如果您想要批量处理，则应使用对象数组）。

相反，我将使用数组的数组。这会使SQL略微不太容易阅读，但会降低开销。此外，我是通过PDO访问MSSQL，但您也可以轻松地使用sqlsrv。

$stmt = $db->prepare(<<<EndSQL
CREATE TABLE #exampleTable (
    A VARCHAR(200),
    B VARCHAR(200)
)

INSERT INTO #exampleTable
SELECT * FROM OPENJSON(?) WITH (
    A VARCHAR(200) '$[0]',
    B VARCHAR(200) '$[1]'
);

DROP TABLE #exampleTable;
EndSQL);

$data = [];
for($i = 0; $i < 100000; $i++) {
    $data[] = [
        'A' => 'A-val-' . $i,
        'B' => 'B-val-' . $i
    ];
}
$stmt->execute([json_encode($data)]);

这类似于将一堆插入语句粘在同一个语句中的策略，但它允许您使用静态SQL语句，而无论您有多少个插入语句。

INSERT INTO #exampleTable VALUES
(?, ?),
(?, ?),
(?, ?),
(?, ?),
(?, ?),
(?, ?),
(?, ?),
...

如果您使用了这个方法，下一个限制可能会遇到的是内存。在将其发送到 SQL 之前，它会将所有内容加载到 PHP 内存中。有两种方法可以解决这个问题：一次处理大约 10,000 行或者在生成 JSON 的同时将其流式传输到 SQL Server。

- 9072997