PHP et MariaDB peuvent-ils fournir une solution plus efficace pour effectuer plusieurs insertions ?

Question

J'essaie donc de créer une petite base de données pour suivre les meilleurs scores des flippers de notre ligue. J'ai un tableau d'utilisateurs qui n'a qu'une colonne d'identifiant AI et une colonne contenant leur adresse e-mail. Ensuite, j'ai une table de jeu qui est une colonne d'identifiant AI et le nom de la machine. Puisqu'il s'agit d'une relation plusieurs-à-plusieurs, j'ai créé une troisième table appelée « scores » qui contient user_id, game_id et Score sous forme de colonnes.

EDIT : Code de lecture inclus :

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

J'utilise un code similaire pour vérifier si le jeu est déjà répertorié dans la base de données. J'ai besoin d'obtenir le numéro d'identification du jeu et l'adresse e-mail de la troisième partie, la troisième partie consiste à voir si l'utilisateur a déjà un score pour ce jeu et s'il a déjà un score, si le nouveau score est plus élevé.

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

Le code semble fonctionner correctement, mais il est très lent. En outre, cela semble provoquer l'expiration du script ou quelque chose se produit après quelques milliers d'enregistrements. Existe-t-il une meilleure façon de faire ce travail ?

J'ai essayé de le recoder en Python mais c'était encore plus lent et les lignes ne semblaient même pas insérées dans la base de données. Je connais à peine Python, ce qui n'aide probablement pas.

Je pensais créer un tableau et stocker les éléments qui doivent être insérés, puis insérer 100 lignes à la fois ou quelque chose comme ça, mais j'ai besoin d'obtenir l'identifiant de la table de jointure de score. J'envisage également d'utiliser une contrainte UNIQUE dans la base de données et d'essayer de comprendre comment réécrire le code d'insertion pour l'utiliser afin d'éviter les adresses e-mail ou les jeux en double.

Answer 1

Il y a encore beaucoup de place à l’amélioration ici. En ce qui concerne la vitesse de la base de données, votre objectif principal doit généralement être de réduire le nombre d'accès au serveur de base de données.

Tout d'abord, vous souhaiterez effectuer une requête Email to ID sur chaque ligne CSV, mais ce n'est pas nécessaire. Tout au plus, vous devriez le faire une fois par utilisateur et le mettre en cache. Mieux encore, vous pouvez le faire une fois pour toute la collection, en lisant l'intégralité du contenu dans une matrice mémoire. Quelque chose comme ça :

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

Cela vous donnera un tableau comme celui-ci :

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

Exécutez ceci une fois au début du script et gardez-le en mémoire tout au long. À partir de là, vous pouvez trouver instantanément l’ID d’un e-mail donné. Cela supprimera 7999 clics de la base de données. Vous échangez essentiellement de la mémoire contre du temps CPU et disque. Si vous rencontrez un e-mail qui ne figure pas déjà dans le tableau, vous pouvez l'insérer et l'ajouter au tableau.

Ensuite, déplacez la préparation en dehors de l'itération de la boucle. Cela supprimera au moins 3*7999 clics de la base de données, et éventuellement jusqu'à 5*7999 clics.

Ensuite, utilisez fgetcsv() au lieu de éclater() car c'est plus simple et gère correctement les références. et traitez l'intégralité du CSV avant d'effectuer une seule insertion. Il serait insensé de créer une telle quantité de trafic dans la base de données si vous souhaitez simplement supprimer la plupart des enregistrements. Alors calculez d'abord le plus élevé puis accédez à la base de données avec uniquement ceux-ci :

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

Fichier d'entrée donné :

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

Cela générera un tableau des scores les plus élevés pour chaque utilisateur :

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

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

)

Vous pouvez ensuite parcourir ce tableau et effectuer des insertions/mises à jour basées uniquement sur ces enregistrements. Cela enregistrera deux requêtes pour chaque ligne CSV redondante.

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

Answer 2

Créez une procédure stockée avec les paramètres email, game_id et Score. Laissez le processus faire tout le travail SQL à votre place. Votre code PHP sera réduit à une seule boucle qui appelle la procédure. Les résultats devraient être plus rapides et plus faciles à maintenir :

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

Si la boucle est encore trop lente, il peut y avoir d'autres raisons au ralentissement.