Können PHP und MariaDB eine effizientere Lösung für die Durchführung mehrerer Einfügungen bieten?

Question

Deshalb versuche ich, eine kleine Datenbank aufzubauen, um die Highscores der Flipper in unserer Liga zu verfolgen. Ich habe eine Benutzertabelle, die nur eine AI-ID-Spalte und eine Spalte mit ihrer E-Mail-Adresse enthält. Dann habe ich eine Spieltabelle, die eine KI-ID-Spalte und den Namen der Maschine enthält. Da es sich um eine Viele-zu-Viele-Beziehung handelt, habe ich eine dritte Tabelle namens „scores“ erstellt, die user_id, game_id und Score als Spalten enthält.

EDIT: Enthaltener Lesecode:

$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;
    }

Ich verwende einen ähnlichen Code, um zu überprüfen, ob das Spiel bereits in der Datenbank aufgeführt ist. Ich brauche die ID-Nummer des Spiels und die E-Mail-Adresse des dritten Teils. Der dritte Teil besteht darin, zu sehen, ob der Benutzer bereits eine Punktzahl für dieses Spiel hat und ob die neue Punktzahl höher ist, wenn er bereits eine Punktzahl hat.

    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
            }
        }
    }

Der Code scheint gut zu funktionieren, ist aber sehr langsam. Außerdem scheint es nach ein paar tausend Datensätzen zu einer Zeitüberschreitung des Skripts oder zu etwas anderem zu kommen. Gibt es eine bessere Möglichkeit, diesen Job zu erledigen?

Ich habe versucht, es in Python neu zu kodieren, aber es war noch langsamer und die Zeilen schienen nicht einmal in die Datenbank eingefügt zu werden. Ich kenne Python kaum, was wahrscheinlich nicht hilft.

Ich habe darüber nachgedacht, ein Array zu erstellen und die einzufügenden Elemente zu speichern und dann jeweils 100 Zeilen oder so etwas einzufügen, aber ich muss die ID der Score-Join-Tabelle erhalten. Ich denke auch darüber nach, eine UNIQUE-Einschränkung in der Datenbank zu verwenden und herauszufinden, wie ich den Einfügecode umschreiben kann, um damit doppelte E-Mail-Adressen oder Spiele zu verhindern.

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

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