为什么使用PHP/MySQL(InnoDB)，TRANSACTION/COMMIT可以大大提高性能? [英] Why does TRANSACTION / COMMIT improve performance so much with PHP/MySQL (InnoDB)?
问题描述
我一直在处理大型CSV数据文件；通常少于100,000条记录.我正在使用PHP和MySQL(InnoDB表).在MySQL INSERT (下面代码中 process_note_data()的一部分)之前，我需要使用PHP转换某些字段并进行一些文本处理.MySQL的 LOAD DATA 不可行，因此请不要提出建议.
I've been working with importing large CSV files of data; usually less than 100,000 records. I'm working with PHP and MySQL (InnoDB tables). I needed to use PHP to transform some fields and do some text processing prior to the MySQL INSERTs (part of process_note_data() in code below). MySQL's LOAD DATA was not feasible, so please do not suggest it.
我最近尝试通过使用 START TRANSACTION 和 COMMIT 的MySQL事务来提高此过程的速度.性能提高令人惊讶.处理时间减少了20倍.因此，一个20分钟的过程只花了大约1分钟.
I recently tried to improve the speed of this process by using MySQL transactions using START TRANSACTION and COMMIT. The performance increase was surprising. Processing time(s) dropped by a factor of 20. So, a 20 minute process only took about 1 minute.
问题.
1.)有谁知道为什么会有这样的性能提升(从20分钟到1分钟)?
1.) Does anyone understand why there was such performance increase (20 mins to 1 min)?
2.)我是否应该担心100,000条记录可以达到多大的交易量?
2.) Should I be concerned about how big the transaction may get with 100,000 records?
3.)我是否应该担心交易中的大量插入和/或更新?
3.) Should I be concerned with a large number of inserts and/or updates in the transaction?
/*
* 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查询的典型数据库对象.
Note: The text/field processing is done in the call to $this->process_note_data() which then calls another helper class that has the INSERT statement code. I didn't have enough room to include all of the code. $db->query() is a typical database object for MySQL queries.
推荐答案
-
请检查此链接:
Please check this link:
https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html
如果该事务对数据库进行了修改，则InnoDB必须在每次事务提交时将日志刷新到磁盘.在每次更改之后都进行一次提交(与默认的自动提交设置一样)时，存储设备的I/O吞吐量将限制每秒可能进行的操作的次数.
InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. When each change is followed by a commit (as with the default autocommit setting), the I/O throughput of the storage device puts a cap on the number of potential operations per second.
大笔交易可能会影响提交期间的性能(请检查上述内容)
Big transactions may affect performance during commit (check above)
仅在回滚的情况下，但是可以使用某些设置(请检查链接)对其进行优化
Only in case of rollback, however it may be optimized using some settings (check the link)
这篇关于为什么使用PHP/MySQL(InnoDB)，TRANSACTION/COMMIT可以大大提高性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
