如何用PHP+MariaDB优化弹珠台分数批量插入的效率？

核心优化思路：减少数据库交互次数 #

原代码的瓶颈在于每条记录都要发起3-4次数据库请求，数千条记录会产生上万次IO往返，这是性能低下的根本原因。以下是具体落地的优化方法：

1. 批量预处理：先缓存已有用户和游戏数据 #

• 一次性把users表的所有邮箱-id映射、games表的所有机器名称-id映射读取到内存中
• 遍历scores.txt时，先在内存缓存中查找用户/游戏是否存在，不存在的暂存为待插入列表
• 最后统一批量插入新用户和新游戏，再更新内存中的id映射

示例PHP代码：

// 预加载已有数据到内存
$emailMap = [];
foreach ($pdo->query("SELECT id, email FROM users") as $row) {
    $emailMap[$row['email']] = $row['id'];
}

$gameMap = [];
foreach ($pdo->query("SELECT id, machine_name FROM games") as $row) {
    $gameMap[$row['machine_name']] = $row['id'];
}

// 暂存待插入的用户和游戏
$newUsers = [];
$newGames = [];
$scoreData = [];

// 遍历文件收集数据
foreach (file('scores.txt') as $line) {
    list($email, $gameName, $score) = explode(',', trim($line));
    $score = (int)$score;

    // 处理用户ID
    if (!isset($emailMap[$email])) {
        $newUsers[] = $email;
        $emailMap[$email] = 'pending';
    }

    // 处理游戏ID
    if (!isset($gameMap[$gameName])) {
        $newGames[] = $gameName;
        $gameMap[$gameName] = 'pending';
    }

    $scoreData[] = [
        'email' => $email,
        'gameName' => $gameName,
        'score' => $score
    ];
}

// 批量插入新用户
if (!empty($newUsers)) {
    $placeholders = rtrim(str_repeat('(?),', count($newUsers)), ',');
    $stmt = $pdo->prepare("INSERT INTO users (email) VALUES $placeholders");
    $stmt->execute($newUsers);
    // 更新ID映射
    $lastId = $pdo->lastInsertId();
    foreach (array_reverse($newUsers) as $email) {
        $emailMap[$email] = $lastId;
        $lastId--;
    }
}

// 批量插入新游戏
if (!empty($newGames)) {
    $placeholders = rtrim(str_repeat('(?),', count($newGames)), ',');
    $stmt = $pdo->prepare("INSERT INTO games (machine_name) VALUES $placeholders");
    $stmt->execute($newGames);
    $lastId = $pdo->lastInsertId();
    foreach (array_reverse($newGames) as $gameName) {
        $gameMap[$gameName] = $lastId;
        $lastId--;
    }
}

2. 用数据库原生语句批量处理分数（插入或更新） #

无需逐条检查分数，直接利用INSERT ... ON DUPLICATE KEY UPDATE语法让数据库一次性处理所有分数数据，前提是给scores表的user_id和game_id添加联合唯一约束：

ALTER TABLE scores ADD UNIQUE INDEX idx_user_game (user_id, game_id);

然后批量构造分数插入语句，仅保留更高的分数：

// 构造批量插入参数
$scoreValues = [];
$scoreParams = [];
foreach ($scoreData as $item) {
    $userId = $emailMap[$item['email']];
    $gameId = $gameMap[$item['gameName']];
    $score = $item['score'];
    $scoreValues[] = '(?, ?, ?)';
    $scoreParams[] = $userId;
    $scoreParams[] = $gameId;
    $scoreParams[] = $score;
}

if (!empty($scoreValues)) {
    $stmt = $pdo->prepare("
        INSERT INTO scores (user_id, game_id, score)
        VALUES " . implode(',', $scoreValues) . "
        ON DUPLICATE KEY UPDATE
        score = GREATEST(score, VALUES(score))
    ");
    $stmt->execute($scoreParams);
}

3. 额外性能优化点 #

• 关闭自动提交：处理开始前执行$pdo->beginTransaction()，所有操作完成后再$pdo->commit()，减少事务提交开销
• 优化文件读取：用file_get_contents一次性读取文件再拆分，比逐行读取更高效（文件不大时适用）
• 使用数据库长连接：避免频繁建立/断开连接的开销

内容的提问来源于stack exchange，提问作者Josh Warren