MySQL 自动提交模式下事务级隔离如何影响性能？
我的服务器上运行着一个 VBulletin 4.x 论坛。出于性能原因，一些论坛表根据 此说明。论坛本身根本不使用事务（源代码中没有 START TRANSACTION 或 BEGIN WORK），并且使用 InnoDB 表只是为了防止表在 UPDATE 查询上锁定。当然，论坛是在自动提交模式下运行的。
我是否理解正确，在这种情况下我可以将默认服务器事务隔离级别更改为 READ UNCOMMITED 并通过这种方式获得一些性能提升？
I have a VBulletin 4.x forum running on my server. Some forum tables were converted into InnoDB for performance reasons according to this instruction. Forum itself does not use transactions (no START TRANSACTION or BEGIN WORK in source code) at all and InnoDB tables are used just to prevent tables from locking on UPDATE queries. Forum is functioning in autocommit mode of course.
Do I understand correctly, that I can change the default server transaction isolation level into READ UNCOMMITED in this case and get some performance gains this way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
TL;DR：如果您的论坛速度很慢，事务隔离级别很可能不是其原因，将其设置为默认值以外的任何其他值几乎没有帮助。设置 innodb_flush_log_on_trx_commit = 2 会有帮助，但会导致崩溃的持久性后果。
长版本：
我在 中写下了什么事务隔离级别http://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html。查看 http://mysqldump.azundris.com/categories/32- 中的所有 3 篇 InnoDB 概述文章InnoDB。
结果是，在任何情况下系统都必须能够回滚，因此即使 READ UNCOMMITTED 也不会更改写入时需要完成的任何操作。
对于读取事务，当导致读取事务的视图的撤消日志记录链较长时，读取速度会变慢，因此 READ UNCOMMITTED 或 READ COMMITTED 可能比默认的 REPEATABLE READ 稍快一些。但您必须记住，我们在这里讨论的是内存访问，而磁盘访问会减慢您的速度。
关于自动提交：这会将每个写入语句同步到磁盘。如果您以前使用过 MyISAM 并且这已经足够好了，您可能需要
在 my.cnf 文件中进行配置并重新启动服务器。
这将使提交从 mysqld 写入文件系统缓冲区高速缓存，但会延迟将文件系统缓冲区高速缓存刷新到磁盘，以便每秒只发生一次。 mysqld 崩溃时您不会丢失任何数据，但硬件崩溃时您可能会丢失最多 1 秒的写入数据。即使在硬件崩溃后，InnoDB 也会自动恢复，并且即使不是完全 ACID，其行为仍然比之前的 MyISAM 更好。如果没有该设置，它将比 AUTOCOMMIT 快得多。
TL;DR: If your forum is slow, the TRANSACTION ISOLATION LEVEL is most likely not the cause of it and setting it to anything else than the default will hardly help. Setting innodb_flush_log_on_trx_commit = 2 will help, but has durability consequences for crashes.
The long version:
What TRANSACTION ISOLATION LEVEL does I have written up in http://mysqldump.azundris.com/archives/77-Transactions-An-InnoDB-Tutorial.html. Check out all 3 InnoDB overview articles in http://mysqldump.azundris.com/categories/32-InnoDB.
The upshot it that in any case the system must be able to ROLLBACK, so not even READ UNCOMMITTED is changing anything that needs to be done on a write.
For reading transactions, the read is slower when the chain of undo log records leading to the view for the reading transaction is longer, so READ UNCOMMITTED or READ COMMITTED may be very slightly faster than the default REPEATABLE READ. But you have to keep in mind that we are talking memory accesses here and it is the disk accesses that slow you down.
On the matter of AUTOCOMMIT: This will synchronize every single write statement to disk. If you have been using MyISAM before and that was good enough, you may want to configure
in your my.cnf file and restart the server.
That will make the commit write from the mysqld to the file system buffer cache, but delay flushing the file system buffer cache to disk so that it happens only once a second. You will not lose any data on mysqld crash, but you may lose up to 1s worth of writes on hardware crash. The InnoDB will recover automatically, even after hardware crash, though, and the behavior is still better than it was with MyISAM before, even if it is not full ACID. It will be much faster than AUTOCOMMIT without that setting.
是。它肯定会带来一些性能提升。但是，如果您正在进行任何更新，则必须手动进行提交。
我对自动提交模式的了解是，它将在数据库操作后自动执行提交，这会导致表上的索引在每次提交时重建，从而降低性能。
Yes. It will surely give some performance gain. But then you have to manually do the commits, if you are doing any updates.
What i know about AutoCommit mode is, it will do the commits automatically after db operations, which causes index on tables to rebuild on every commit, that makes down the performance.