PHP+MySQL 在一台机器上速度太慢

Question

我已经尝试了所有我能从类似问题中找到的建议，但似乎都没有帮助。

我正在运行一个 PHP 脚本来将一些数据添加到数据库中——只有大约 1000 行。这是我正在使用的完整代码:

<?php
header('Content-Type: text/plain');
$t_start = microtime(true);
require('../php/connect.php');
$data = json_decode(file_get_contents('base/en/stringlist.js'));
foreach ($data as $cat => $values) {
  $category = mysql_real_escape_string($cat);
  foreach ($values as $key => $info) {
    $name = mysql_real_escape_string($key);
    $text = mysql_real_escape_string($info->text);
    $tip = mysql_real_escape_string(isset($info->tip) ? $info->tip : '');
    $query = "INSERT INTO locale_strings (name,text,tip,category) VALUES ('$name','$text','$tip','$category')";
    if (!mysql_query($query)) {
      echo mysql_error() . "\n";
    }
  }
}
echo 'Time: ' . round(microtime(true) - $t_start, 4) . ' seconds.';
?>

(我为 mysql_ 语法道歉)。我已经在 3 台 PC 上使用过它，它们都运行 Win7/8，并安装了相当新的 XAMPP。在其中两台机器上，该脚本执行大约需要 2-3 秒，而在第三台机器上，它会在 30 秒后超时(添加约 900 行后)。什么会导致它运行速度慢 10 倍？

如果我注释掉 mysql_query 行，运行需要 0.012 秒，所以代码本身不是问题。数据库位于本地主机上，并包含在 etc/hosts 文件中 - 与其他机器上的相同。

这是表结构:

CREATE TABLE IF NOT EXISTS `locale_strings` (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `category` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `locked` int(11) NOT NULL DEFAULT '0',
  `tip` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4833 DEFAULT CHARSET=utf8;

ALTER TABLE `locale_strings`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `locale_strings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4833;

Answer 1

InnoDB 存储引擎是 ACID合规。每个查询都以自动提交模式运行，并且会使用磁盘的输入输出操作，这是相当昂贵的。这意味着每个查询都会强制硬盘驱动器 (HDD) 写入一条记录(而不是让操作系统安排它)。您的 HDD(如果是机械硬盘)每秒大约有 300 个 I/O。这意味着对于大约 900-1000 条记录，写下它们可能需要 2-3 秒。

相比之下，正如我提到的，使用 MyISAM 存储引擎可以让操作系统安排写入。记录不会立即写入磁盘；他们是buffered在保存到磁盘之前在内存中。虽然这意味着 MyISAM 更快，但代价是数据丢失的风险(如果系统崩溃或断电，而缓冲区中的数据尚未写入磁盘)。

要对此进行优化，通常的方法是将多个插入包装到一个事务中。一种方法是使用 PDO ，准备语句，启动事务并在完成 1000 次插入后提交。这使得硬盘驱动器可以在一次操作中写下大量数据。

您使用 PDO 重写的代码将像这样流动(注意 - 没有测试，不要复制粘贴，仅供引用):

// connect.php - note that the code around PDO should go in the file connect.php as per example
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try
{
    $pdo = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
    echo 'Connection failed: ' . $e->getMessage();
}

//===================================================================
// The rest of the code
//===================================================================

// Decode JSON
$data = json_decode(file_get_contents('base/en/stringlist.js'));

// Check if there were any errors decoding JSON
if(empty($data))
{
    echo "Something went wrong, error: " . json_last_error_msg();
    exit;
}

try
{
    // Prepare the statement - it's prepared once, used multiple times
    $stmt = $pdo->prepare("INSERT INTO locale_strings (name, text, tip, category) VALUES (:name, :text, :tip, :category)");

    // Start the transaction

    $pdo->beginTransaction();

    // Loop through the data, bind parameters, execute and when done - commit
    foreach ($data as $cat => $values)
    {
        foreach ($values as $key => $info)
        {
            $stmt->bindValue(':name', $key, PDO::PARAM_STR);
            $stmt->bindValue(':text', $info->text, PDO::PARAM_STR);
            $stmt->bindValue(':tip', isset($info->tip) ? $info->tip : '', PDO::PARAM_STR);
            $stmt->bindValue(':category', $cat, PDO::PARAM_STR);

            $stmt->execute();
        }
    }

    // And finally, tell the HDD to write down the info.
    // Note - for this example, we issued all the contents in a single commit.
    // That might not be the sweet spot so you can optimize later on with checking
    // how many records are optimal go do down to the disk in a single I/O
    $pdo->commit();
}
catch(PDOException $e)
{
    if($pdo->inTransaction())
    {
        $pdo->rollBack();
    }

    // I assume you know how to handle exceptions, messages, trace etc.
}