php - 从 MySQL 数据库中批量获取大数据(就像 PhpStorm 那样)

Question

在我的数据库中，我有包含数十亿结果的巨大表，问题是我的 PHP 代码无法正确处理。我等待数小时的结果，几乎总是收到“服务已移走”或“传递数据的最大限制为 xx”之类的错误。

表具有索引列。查询被简化到最低限度。我在 PHP 中尝试过一些测试，但都失败了。

// approach #1 with PHP
$pdo = new PDO("mysql:host=".$data["host"].";dbname=".$data["dbname"].";charset=utf8", $data["username"], $data["pass"]);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2'";

// ends up with error with no results
$pdo->query($sql)->fetchAll();

// approach #2 with PHP
$pdo = new PDO("mysql:host=".$data["host"].";dbname=".$data["dbname"].";charset=utf8", $data["username"], $data["pass"]);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$resultsCounted = 5000000;
$offset = 0;

for($i = 0; $offset < $resultsCounted; $i += 500) {
    $sql = "SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2' LIMIT ".$offset.", 500";

    // ends up with error with no results
    $result = $pdo->query($sql)->fetchAll();

    // do some stuff with results
    do_stuff($result);

    $offset += $i;
}

奇怪的是，当选择“执行到文件”功能时，此 SQL 在 PhpStorm 数据库控制台中起作用:

SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2'

我可以看到 PhpStorm 如何将 100 x 100 个结果保存到文件中，直到所有结果都保存为止。

我需要找到一种方法，如何使用 PHP 重新创建 PhpStorm 数据库数据保存，或者找到另一种方法来正确管理结果保存。

Answer 1

LIMIThugenumbersmallnumber 通常效果不佳:这是一种臭名昭著的性能反模式。 MySQL 必须努力跳过大量记录，然后才能返回小数字。然后它必须一遍又一遍地做这一切......

而是一次处理一行。PHP 中的大多数查询都是缓冲的。也就是说，fetchAll() 及其等效项会一口气吞掉查询结果集中的所有内容。

这对于具有大量结果集的查询来说效果很差，部分原因是大多数 php 实现都有 RAM 限制。

使用 unbuffered query为了这。一次处理您需要的行，然后丢弃它们。这样你的结果集就会连续流过你的 php 程序。 (我没有调试这个例子)。

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$sql = "SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2'";
$unbuff = $pdo->query($sql);
if ($unbuff) {
   while ($row = $unbuff->fetch(PDO::FETCH_ASSOC)) {
       echo $row['whatever']; /* handle the row */
   }
}