为什么在PHP/MySQL（InnoDB）中使用TRANSACTION / COMMIT会如此大幅提升性能？

Question

我一直在处理导入大型CSV数据文件，通常少于10万条记录。使用PHP和MySQL（InnoDB表）。在MySQL的INSERT操作之前，我需要使用PHP对某些字段进行转换和文本处理（在下面的代码中的process_note_data()部分）。由于不可行，请不要建议使用MySQL的LOAD DATA。

最近，我尝试通过使用MySQL事务（使用START TRANSACTION和COMMIT）来提高这个过程的速度。性能的提升令人惊讶。处理时间减少了20倍。所以，一个20分钟的过程只用了大约1分钟。

问题:

1.) 有人知道为什么会有如此大的性能提升（从20分钟到1分钟）吗？

2.) 我应该担心100,000条记录可能造成的交易大小吗？

3.) 在事务中大量插入和/或更新是否值得关注？

/*
 * Customer Notes Data:
 * Rows are either a meeting, call or note!
 */
$row = 1;
$data = array();
$fields = array();
$line = '';

$db->query('SET autocommit=0;');
$db->query('START TRANSACTION;');

if (($handle = fopen("modules/".$currentModule."/Data/customernote.csv", "r")) !== FALSE) {
  while (($data = fgetcsv($handle, 4096, ',', '"')) !== FALSE && $row < 999000) {
    //Row 1 - CSV header row with field names
    if ($row == 1) {
      $csv_fields = $data;
    } elseif ($row > 1) {
      $fields = $this->process_note_data($data, $csv_fields, $row);
    }
    $row++;
  } // end while
  fclose($handle);
}

$db->query('COMMIT;');
$db->query('SET autocommit=1;');

注意：对于文本/字段的处理是在调用$this->process_note_data()时完成的，然后调用另一个辅助类来执行INSERT语句的代码。我没有足够的空间包含所有的代码。$db->query()是MySQL查询的典型数据库对象。

- jjwdesign

---

我这里没有看到任何更新/插入操作，也许是因为移除这些操作导致了更快的执行速度 ;) - dev-null-dweller

文本/字段处理是在调用$this->process_note_data()中完成的，然后调用另一个帮助程序类，该类具有INSERT操作。我没有足够的空间包含所有代码。请注意，它会正确插入所有记录。 - jjwdesign

事务 ~= 内存处理; 提交 ~= 刷新到存储器 - जलजनक

不需要（也不应该）执行SET autocommit=0;，然后再执行相反的操作。请参考官方文档：使用START TRANSACTION命令后，直到你使用COMMIT或ROLLBACK结束事务前，都会保持自动提交禁用状态。 - BlueRaja - Danny Pflughoeft

Answer 1

1. 请查看此链接：

   https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html

   如果InnoDB事务对数据库进行了修改，则在每个事务提交时，必须将日志刷新到磁盘上。当每个更改后跟随提交（与默认的自动提交设置一样）时，存储设备的I/O吞吐量将限制每秒的潜在操作次数。

2. 大型事务可能会影响提交期间的性能（见上文）。

3. 仅在回滚的情况下可能会影响性能，但是它可以通过一些设置进行优化（请查看链接）。

- MiGro

---

你会建议每插入1000个数据就进行一次COMMIT，以减少COMMIT时的处理量，还是我有点过于担心了？ - jjwdesign

1k应该没问题，但这取决于硬件。我建议在这里运行一些测试。无论如何，请注意数据的一致性（例如-您加载了100k记录中的20k条记录，系统崩溃）。 - MiGro

在“COMMIT”时不需要进行太多处理，因此没有理由定期提交。但是，如果系统在进行非常大的事务时崩溃，可能需要大量时间来回滚（并且这将在服务器启动时完成而不接受请求）。然而，仅有100k行，您可能不需要担心这个问题。 - jeremycole

Answer 2

我在 .Net 中做了一个小测试（每个记录有 4 个字段）：

插入 1 条记录，不使用事务：60 毫秒

插入 1 条记录，使用事务：158 毫秒

插入 200 条记录，使用事务，在每条记录后提交：17778 毫秒

插入 200 条记录，不使用事务：4940 毫秒

插入 200 条记录，使用事务，只在最后一条记录后提交：4552 毫秒

插入 1000 条记录，使用事务，只在最后一条记录后提交：21795 毫秒

客户端在丹麦，服务器在比利时（Google Cloud f1-micro）。

我原本想把这个放在评论里，但格式不好……所以提前向大家道歉 ;-)

- MrCalvin

---

只有200条记录，你不会看到太大的差异。如果有超过100,000条记录，你应该开始看到很大的性能优势。 - jjwdesign

有趣的是使用事务提交位置的差异...谢谢！ - J. Fdez

现在对SELECT执行相同的测试。 - Maurice