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.

推荐答案

如果您的查询是 INSERT，页面 7.2.19.MySQL 手册的 INSERT 语句速度 提供了两个有趣的信息，具体取决于您是否使用事务引擎:

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 插入所有行.

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.

并且，使用事务引擎:

为了获得更快的插入事务表，你应该使用START TRANSACTION 和 COMMIT 代替锁表.

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 ;-)

而且，如果您正在执行 更新语句 :

另一种获得快速更新的方法是延迟更新然后做很多更新一排之后.执行多个一起更新比一起快得多一次做一个，如果你锁定表.

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.

顺便说一句:可以肯定的是，您可以尝试两种解决方案，使用 microtime 对它们进行基准测试，在 PHP 方面，例如 ;-)

BTW : to be sure, you can try both solutions, benchmarking them with microtime, on the PHP side, for instance ;-)

这篇关于PDO/PHP/MySQL 中的性能:事务与直接执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！