Bolehkah PHP dan MariaDB menyediakan penyelesaian yang lebih cekap untuk melaksanakan berbilang sisipan?

Question

Jadi saya cuba membina pangkalan data kecil untuk menjejaki markah tinggi mesin pinball dalam liga kami. Saya mempunyai jadual pengguna yang hanya mempunyai lajur id AI dan lajur yang mengandungi alamat e-mel mereka. Kemudian saya mempunyai jadual permainan yang merupakan lajur id AI, dan nama mesin. Memandangkan ia merupakan perhubungan banyak-ke-banyak, saya mencipta jadual ketiga yang dipanggil "skor" yang mengandungi user_id, game_id dan Score sebagai lajur.

EDIT: Termasuk kod bacaan:

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

Saya menggunakan kod yang serupa untuk menyemak sama ada permainan itu sudah disenaraikan dalam pangkalan data. Saya perlu mendapatkan nombor ID permainan dan alamat e-mel bahagian ketiga, bahagian ketiga adalah untuk melihat sama ada pengguna sudah mempunyai skor untuk permainan itu dan jika mereka sudah mempunyai skor, jika skor baharu lebih tinggi.

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

Kod nampaknya berfungsi dengan baik, tetapi sangat perlahan. Juga, ia seolah-olah menyebabkan skrip tamat masa atau sesuatu berlaku selepas beberapa ribu rekod. Adakah terdapat cara yang lebih baik untuk melakukan kerja ini?

Saya cuba mengekodnya semula dalam Python tetapi ia lebih perlahan dan baris tidak nampak pun dimasukkan ke dalam pangkalan data. Saya hampir tidak tahu Python, yang mungkin tidak membantu.

Saya berfikir untuk mencipta tatasusunan dan menyimpan item yang perlu dimasukkan dan kemudian memasukkan 100 baris pada satu masa atau sesuatu seperti itu, tetapi saya perlu mendapatkan id jadual gabungan skor. Saya juga sedang mempertimbangkan untuk menggunakan kekangan UNIK dalam pangkalan data dan cuba memikirkan cara menulis semula kod sisip untuk menggunakannya untuk mengelakkan alamat e-mel atau permainan pendua.

Answer 1

Masih banyak ruang untuk penambahbaikan di sini. Apabila bercakap tentang kelajuan pangkalan data, matlamat utama anda secara amnya adalah untuk mengurangkan bilangan hits ke pelayan pangkalan data.

Pertama sekali, anda perlu melakukan pertanyaan E-mel ke ID pada setiap baris CSV, tetapi ini tidak perlu. Paling banyak, anda perlu melakukannya sekali bagi setiap pengguna dan cachenya. Lebih baik lagi, anda boleh melakukannya sekali untuk keseluruhan koleksi, membaca keseluruhan kandungan ke dalam tatasusunan memori. Sesuatu seperti ini:

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

Ini akan memberi anda tatasusunan seperti ini:

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

Laksanakan ini sekali pada permulaan skrip dan simpan dalam ingatan sepanjang masa. Daripada ini, anda boleh mencari ID e-mel yang diberikan dengan serta-merta. Ini akan memadam 7999 klik daripada pangkalan data. Anda pada dasarnya berdagang memori untuk CPU dan masa cakera. Jika anda menjumpai e-mel yang belum ada dalam tatasusunan, anda boleh memasukkannya dan menambahnya pada tatasusunan.

Seterusnya, alihkan penyediaan ke luar lelaran gelung. Ini akan memadam sekurang-kurangnya 3 * 7999 klik daripada pangkalan data, dan mungkin sehingga 5 * 7999 klik.

Seterusnya, gunakan fgetcsv() dan bukannya explode() kerana ia lebih mudah dan mengendalikan rujukan dengan betul. dan proses keseluruhan CSV sebelum melakukan satu sisipan. Adalah bodoh untuk mencipta sejumlah besar trafik pangkalan data jika anda hanya akan membuang kebanyakan rekod. Jadi hitung yang tertinggi dahulu dan kemudian akses pangkalan data dengan hanya ini:

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

Fail input yang diberikan:

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

Ini akan menjana tatasusunan markah tertinggi untuk setiap pengguna:

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

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

)

Anda kemudian boleh mengulangi tatasusunan itu dan melakukan sisipan/kemas kini hanya berdasarkan rekod tersebut. Ini akan menyimpan dua pertanyaan untuk setiap baris CSV yang berlebihan.

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

Answer 2

Buat prosedur tersimpan dengan e-mel parameter, game_id dan Score. Biarkan proses melakukan semua kerja SQL untuk anda. Kod PHP anda akan dikurangkan kepada satu gelung yang memanggil prosedur. Keputusan harus lebih cepat dan lebih mudah untuk dikekalkan:

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

Jika gelung masih terlalu perlahan, mungkin ada sebab lain untuk kelembapan.