php - MySQL INSERT 或 PHP PDO 性能不佳

Question

通常，当用户访问我的网站时，他平均会一次“订阅”大约 50 个事件，我们将这些事件保存在一个将许多用户链接到许多事件的 mapping_table 中。 mapping_table 有大约 5 万个条目，并且每天都在增长数百个。

插入映射表中的这大约 50 行大约需要 10 到 20 秒，这使得我的网站显得非常慢。

我已经无法弄清楚如何改进以下 PHP 或 SQL 以减少 INSERT 时间，因为它看起来非常基本。

这是SHOW CREATE语句:

    CREATE TABLE `mapping_table` (
     `user_id` char(21) NOT NULL,
     `event_id` char(21) NOT NULL,
     `update_id` char(10) NOT NULL,
     PRIMARY KEY (`user_id`,`event_id`),
     KEY `event` (`event_id`),
     CONSTRAINT `mapping_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_table` (`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
     CONSTRAINT `mapping_table_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `events_table` (`event_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

我的插入部分的 php 脚本:

    $update_id = 'RandomString';
    $conn = new PDO($pdo_serv, $db_user, $db_pwd);
    $stmt = $conn->prepare("INSERT INTO `mapping_table` (`user_id`, `event_id`, `update_id`)
                    VALUES (:uid,:eid,:upd)");

    //GO THROUGH EACH USER SUBSCRIBED EVENTS AND INSERT THEM
    foreach($subscribed_events AS $event) {
        $stmt->bindParam(':uid', $userid);
        $stmt->bindParam(':eid', $event->id);
        $stmt->bindParam(':upd', $update_id);
        $stmt->execute();
    }

我愿意接受任何建议。

其他信息:

如果我对每个插入进行微计时，那么每次插入平均时间为 300 毫秒到 1 秒。

我尝试删除UNIQUE KEY，然后删除所有FOREIGN KEYS，然后删除所有INDEXES，但这些对性能没有任何影响。

Answer 1

根据我的问题的评论，我修改了我的 php，如下 batch INSERT :

$update_id = 'RandomString';
$conn = new PDO($pdo_serv, $db_user, $db_pwd);

$sql = "INSERT INTO `mapping_table` (`user_id`, `event_id`, `update_id`)
                VALUES ";
$values = "";

//GO THROUGH EACH USER SUBSCRIBED EVENTS GENERATE VALUES STRING
foreach($subscribed_events AS $event) {
    $values .= "('".$userid."','".$event->id."','".$update_id."'),"
}

//Proceed batch insert
$values = rtrim($values, ",");
$sql .= $values;
$stmt = $conn->prepare($sql);
$stmt->execute();

这将我的平均插入时间减少到每行插入时间不到 10 毫秒(之前每行插入时间为 300 毫秒到 1 秒)。没想到差别会这么大。谢谢。