PHP 和 MariaDB 能否提供更有效的解决方案来执行多次插入？

Question

所以我正在尝试建立一个小型数据库来跟踪我们联盟中弹球机的高分。我有一个用户表，其中只有一个 AI id 列和一个包含他们的电子邮件地址的列。然后我有一个游戏表，它是一个 AI id 列，以及机器的名称。由于它是多对多关系，因此我创建了第三个表，名为“scores”，其中包含 user_id、game_id 和 Score 作为列。

编辑：包括读取代码：

$file = fopen('scores.txt', 'r') or die("Unable to open file.");

// Loop through the file line by line
$line_number = 1;
while (($line = fgets($file)) !== false) {

    // Reset flags
    $email_exists = 0;
    $game_exists = 0;
    if (isset($email_id)) unset($email_id);
    if (isset($game_id)) unset($game_id);
    echo ($line_number . " ");

    // Split the line into components
    $line = rtrim($line);
    $array = explode(",", $line, 3);
    $email = strtolower($array[0]);
    $game = $array[1];
    $score = $array[2];
    $stmt = $db->prepare ("SELECT email_id FROM users WHERE email_address = ?");
    $stmt->execute(array($email))
        if ($stmt->rowCount() < 1) {
        $stmt = $db->prepare("INSERT INTO users (email_address) VALUES (?)");
        $stmt->execute(array($email))
        $email_id = $db->lastInsertId();
    } else {
        $row = $stmt->fetch();
        $email_id = $row['email_id'];
        $email_exists = 1;
    }

我使用类似的代码来检查数据库中是否已经列出了游戏。我需要获取游戏的 ID 号和第三部分的电子邮件地址，第三部分是查看用户是否已经有该游戏的分数，以及如果他们已经有分数，新分数是否更高。

    if ($email_exists == 0 || $game_exists == 0) {
        // New user or game added to DB - no prior score can exist
        $stmt = $db->prepare("INSERT INTO scores VALUES (?,?,?)");
        $stmt->execute(array($email_id,$game_id,$score));
    } else {
        $stmt = $db->prepare("SELECT * FROM scores WHERE email_id = ? AND game_id = ?");
        $stmt->execute(array($email_id,$game_id));
        if ($stmt->rowCount() == 0) {
            // No previous score for this game
            $stmt = $db->prepare("INSERT INTO scores VALUES (?,?,?)");
            $stmt->execute(array($email_id,$game_id,$score));
        } else {
            // Previous score exists
            $row = $stmt->fetch();
            if ($score > $row['score']) {
                // New score is higher
                $stmt = $db->prepare("UPDATE scores SET score = ? " .
                    . "WHERE email_id = ? AND game_id =?");
                $stmt->execute(array($score, $email_id, $game_id));
                // No action needed if new score is lower
            }
        }
    }

代码看起来工作正常，但速度很慢。另外，它似乎会使脚本超时或在几千条记录之后发生某种情况。有没有更好的方法来完成这项工作？

我尝试用 Python 对其进行重新编码，但速度甚至更慢，而且似乎甚至没有将行插入数据库中。我几乎不懂 Python，这可能没什么帮助。

我正在考虑创建一个数组并存储需要插入的项目，然后一次插入 100 行或类似的东西，但我需要获取分数连接表的 id。我还考虑在数据库中使用 UNIQUE 约束，并试图找出如何重写插入代码以使用它来防止重复的电子邮件地址或游戏。

Answer 1

这里还有很大的改进空间。当谈到数据库速度时，您的主要目标通常应该是减少对数据库服务器的命中次数。

首先，您要对每个 CSV 行执行电子邮件到 ID 查询，但没有必要这样做。最多，您应该为每个用户执行一次并缓存它。更好的是，您可以对整个集合执行一次，将整个内容读入内存数组中。像这样的东西：

$stmt = $db->prepare('SELECT email_address, email_id FROM users');
$idMap = array_column($stmt->execute(), 'email_id', 'email_address');

这会给你一个像这样的数组：

[
    'foo@bar.com' => 1,
    'baz@bar.com' => 2,
]

在脚本开始时执行一次此操作，并在整个过程中将其保留在内存中。由此，您可以立即查找给定电子邮件的 ID。这会删除数据库中的 7999 次点击。您实际上是在用内存换取 CPU 和磁盘时间。如果您遇到数组中尚未包含的电子邮件，您可以将其插入并将其添加到数组中。

接下来，将准备工作移到循环迭代之外。这会删除数据库中至少 3 * 7999 次点击，甚至可能删除多达 5 * 7999 次点击。

接下来，使用 fgetcsv() 而不是explode()，因为它更容易并且可以正确处理引用。并在执行单个插入之前处理整个 CSV。如果您只是要丢弃大部分记录，那么创建如此大量的数据库流量是愚蠢的。因此，首先计算最高的，然后只用这些来访问数据库：

$top = [];
$fp = fopen('scores.txt', 'r');
while ([$email, $gameId, $score] = fgetcsv($fp)) {
    if ($score > ($top[$email][$gameId] ?? 0)) {
        $top[$email][$gameId] = $score;
    }
}

给定输入文件：

foo@bar.com,g1,3
foo@bar.com,g1,1
foo@bar.com,g2,2
baz@bar.com,g1,4
baz@bar.com,g2,5
baz@bar.com,g2,6

这将为每个用户生成最高分数的数组：

Array
(
    [foo@bar.com] => Array
        (
            [g1] => 3
            [g2] => 2
        )

    [baz@bar.com] => Array
        (
            [g1] => 4
            [g2] => 6
        )

)

然后，您可以迭代该数组，并且仅根据这些记录执行插入/更新。这将为每个冗余 CSV 行保存两个查询。

foreach ($top as $email => $scores) {
    foreach ($scores as $gameId => $score) {
        // INSERT INTO scores ($idMap[$email], $gameId, $score)
    }
}

Answer 2

创建一个存储过程，其中包含参数 email、game_id 和 Score。让该过程为您完成所有 SQL 工作。您的 PHP 代码将被缩减为调用该过程的单个循环。结果应该更快并且更容易维护：

create procedure sp_add_email_score (
in_email varchar(320),
in_game_id int,
in_score int
)
begin

declare v_email_id int;

select email_id into v_email_id
from users 
where email_address = in_email;

if (v_email_id is null) then
  INSERT INTO users (email_address) VALUES (in_email);
  
  set v_email_id = LAST_INSERT_ID();
end if;

INSERT INTO scores (email_id, game_id, score) 
VALUES(v_email_id, in_game_id, in_score) 
ON DUPLICATE KEY UPDATE score=if(in_score>score, in_score, score);;

end

如果循环仍然太慢，则可能是其他原因导致速度减慢。