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

如果循環仍然太慢，可能是其他原因導致速度減慢。