使用 PDO 准备语句插入时 MySQL 性能不佳

最近，我一直在试图弄清楚为什么一个特定的PHP脚本需要这么长时间。 它是一个简单的组权限网格，它从前端为每个组获取 N 个复选框，并相应地更新权限表。 简单的东西。

为了尽量减少与数据库的往返行程，我只是截断了表并为每个权限重新插入行。 这样做，我看到插入步骤的性能绝对糟糕。 一切都包含在事务中，所有查询都使用 PDO 准备语句。

数据库本身是MySQL，所有表都使用InnoDB引擎。 插入的特定表非常简单，仅由 3 列组成，如下所示：

CREATE TABLE `024_group_privs` (
  `group_priv_id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  PRIMARY KEY (`group_priv_id`),
  UNIQUE KEY `group_id` (`group_id`,`activity_id`),
  KEY `activity_id` (`activity_id`),
  CONSTRAINT `024_group_privs_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `023_groups` (`group_id`),
  CONSTRAINT `024_group_privs_ibfk_2` FOREIGN KEY (`activity_id`) REFERENCES `031_activities` (`activity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=latin1

如您所见，一个基本的多对多映射表。

有问题的 PHP 代码（带有生成后续结果的测试基架）如下所示：

<?php
try {
    //Connect to database
    $db = new PDO('mysql:host=localhost;dbname=mydatabase;charset=utf8', 'user', 'password');
    //Begin transaction.  If there are any errors; this will ensure we still have
    //  a good set of permissions when rolled back
    $db->beginTransaction();
    //Prepare insert and select statement
    $ins_perm = $db->prepare("INSERT INTO 024_group_privs (group_id, activity_id) VALUES (:group_id, :activity_id)");
    $get_act_id = $db->prepare("SELECT activity_page_key FROM 031_activities WHERE activity_id = :activity_id");
    //Nuke existing permissions list; it will be re-built shortly and is faster than
    //  trying to 'true up' the differences in existing permissions with the new ones.
    $trunc_s_time = microtime(true);
    $db->query("SET FOREIGN_KEY_CHECKS = 0");
    $db->query("TRUNCATE TABLE 024_group_privs");
    $db->query("SET FOREIGN_KEY_CHECKS = 1");
    $trunc_e_time = microtime(true);
    print("Trunc time = " . ($trunc_e_time - $trunc_s_time) . "<br/><br/>");
    $tot_ins_time = 0;
    $tot_sel_time = 0;
    //Get grout names from DB, do not trust POST object.
    $query = "SELECT group_name, group_id FROM 023_groups";
    foreach($db->query($query) as $row) {
        //Get current group and related checkbox array.  The names of these arrays are
        //  populated from the DB when the initial page is rendered.
        $group_name = $row['group_name'];
        $group_id = $row['group_id'];
        $perms = $_POST[$group_name];
        //Rebuild permissions for this group
        foreach($perms as $activity_id) {
            //Testing for select statement timing
            $sel_s_time = microtime(true);
            $get_act_id->bindValue(':activity_id', $activity_id, PDO::PARAM_INT);
            $get_act_id->execute();
            $result = $get_act_id->fetchAll(PDO::FETCH_ASSOC);
            $sel_e_time = microtime(true);
            print("Sel time = " . ($sel_e_time - $sel_s_time) . "<br/>");
            //Insert new permission <-- THIS IS THE SLOW QUERY
            $ins_s_time = microtime(true);
            $ins_perm->bindValue(':group_id', $group_id, PDO::PARAM_INT);
            $ins_perm->bindValue(':activity_id', $activity_id, PDO::PARAM_INT);
            $ins_perm->execute();
            $ins_e_time = microtime(true);
            print("Ins time = " . ($ins_e_time - $ins_s_time) . "<br/><br/>");
            $tot_ins_time += ($ins_e_time - $ins_s_time);
            $tot_sel_time += ($sel_e_time - $sel_s_time);
        }
    }
    print("<br/>Total Sel time = " . $tot_sel_time . "<br/>");
    print("Total Ins time = " . $tot_ins_time . "<br/>");
    print("Total time = " . $tot_ins_time + $tot_sel_time . "<br/>");
    //Commit changes
    $db->commit();
    //Close DB connection
    $db = null;
} catch (Exception $ex) {
    set_mysql_status($sid, "An error occurred while processing your request: " . $ex->getMessage());
    $db->rollBack();
}
?>

我的测试结果如下（为简洁起见，缩短）：

Trunc time = 2.1458051204681
Sel time = 0.00010895729064941
Ins time = 1.2832479476929
Sel time = 0.00010108947753906
Ins time = 1.2832760810852
Sel time = 0.00010204315185547
Ins time = 1.2749929428101
Sel time = 0.00011420249938965
Ins time = 5.0466451644897
Sel time = 0.00012516975402832
Ins time = 4.5408680438995
...
Total Sel time = 0.010221719741821
Total Ins time = 168.57392191887

问题：什么给了？ 我希望编译后的插入语句的执行速度会比这快得多，尤其是对于如此简单的数据，更像是 select 语句。 数据很小;每个表少于 100 行。 没有表锁，因为这是我的私人（专用）开发环境，盒子本身没有其他东西运行，并且有足够的马力来执行简单的数据库事务。

我的理解/期望：由于准备好的语句和事务"包装器"，DB往返应该最小化。 我唯一的想法是，这是由于表上的两个外键造成的，但考虑到与处理它的机器相比的数据大小，这将是非常可悲的。 也许MySQL在每次插入时都会强制写入完整的硬盘驱动器，但这似乎首先会破坏事务的目的。

我已经搜索了Google和SO，尽管我发现的大多数问题都集中在准备循环中的查询或类似的东西上。

最后一点：此代码尚未针对安全性进行优化，旨在测试慢速插入。 提前感谢任何帮助。