PDO/PHP/MySQL中的性能:事务与直接执行 [英] Performance in PDO / PHP / MySQL: transaction versus direct execution
问题描述
我正在遍历多个值(例如1到100)，并在循环内执行准备好的语句.
I am looping through a number of values (1 to 100 for example) and executing a prepared statement inside the loop.
与在循环内部直接执行相比，使用事务在循环结束后提交是否有好处?
Is there and advantage to using a transaction - committing after the loop ends - compared to a direct execution inside the loop?
这些值并不相互依赖，因此从该角度来看不需要进行交易.
The values are not dependant on each other so a transaction is not needed from that point of view.
推荐答案
If your queries are INSERTs, the page 7.2.19. Speed of INSERT Statements of the MySQL manual gives two interesting informations, depending on whether your are using a transactionnal engine or not :
使用非事务引擎时:
要加快INSERT操作的速度 用多个语句执行 非交易表，请锁定您的 桌子.
To speed up INSERT operations that are performed with multiple statements for nontransactional tables, lock your tables.
这有益于性能，因为 索引缓冲区仅刷新到磁盘 在所有INSERT语句都包含一次之后 完全的.通常情况下， 许多索引缓冲区刷新 INSERT语句.显式锁定 如果可以的话，不需要声明 用一个INSERT插入所有行.
This benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally, there would be as many index buffer flushes as there are INSERT statements. Explicit locking statements are not needed if you can insert all rows with a single INSERT.
并且，使用交易引擎:
为了更快地插入 事务表，您应该使用 开始交易并提交 锁表.
To obtain faster insertions for transactional tables, you should use START TRANSACTION and COMMIT instead of LOCK TABLES.
所以我猜想使用事务可能是一个好主意-但是我想这可能取决于服务器上的负载，是否同时使用同一张表有多种用途以及所有这些...
So I am guessing using transactions might be a good idea -- but I suppose that could depend on the load on your server, and whether there are multiple uses using the same table at the same moment, and all that...
我链接到的页面上有更多信息，所以请随时阅读;-)
There are more informations on the page I linked to, so don't hesitate to read it ;-)
而且，如果您正在执行更新语句:
And, if you are doing update statements :
获得快速更新的另一种方法是 延迟更新，然后进行许多更新 以后连续.执行多个 一起更新要比更新快得多 一次锁一次 桌子.
Another way to get fast updates is to delay updates and then do many updates in a row later. Performing multiple updates together is much quicker than doing one at a time if you lock the table.
所以，我想可以说与插入相同.
So, I'm guessing the same can be said than for inserts.
顺便说一句:当然，您可以尝试两种解决方案，并在PHP方面使用 microtime 进行基准测试，例如；-)
BTW : to be sure, you can try both solutions, benchmarking them with microtime, on the PHP side, for instance ;-)
这篇关于PDO/PHP/MySQL中的性能:事务与直接执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
