默认 MySQL 设置 AUTOCOMMIT=1 可能会对繁忙的数据库服务器施加性能限制。在可行的情况下，通过发出 SET AUTOCOMMIT=0 或 START TRANSACTION 语句，将多个相关的数据更改操作包装到单个事务中，然后在进行所有更改后发出 COMMIT 语句。The default MySQL setting AUTOCOMMIT=1 can impose performance limitations on a busy database server. Where practical, wrap several related data change operations into a single transaction, by issuing SET AUTOCOMMIT=0 or a START TRANSACTION statement, followed by a COMMIT statement after making all the changes.

InnoDBInnoDB 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.

或者，对于仅包含单个 SELECT 语句的事务，打开 AUTOCOMMIT 会有所帮助InnoDB 识别只读事务并优化它们。有关要求，请参阅第 10.5.3 节“优化 InnoDB 只读事务”。Alternatively, for transactions that consist only of a single SELECT statement, turning on AUTOCOMMIT helps InnoDB to recognize read-only transactions and optimize them. See Section 10.5.3, “Optimizing InnoDB Read-Only Transactions” for requirements.

避免在插入、更新或删除大量行后执行回滚。如果大事务正在降低服务器性能，则回滚可能会使问题变得更糟，执行时间可能是原始数据更改操作的数倍。终止数据库进程并没有帮助，因为回滚会在服务器启动时再次开始。Avoid performing rollbacks after inserting, updating, or deleting huge numbers of rows. If a big transaction is slowing down server performance, rolling it back can make the problem worse, potentially taking several times as long to perform as the original data change operations. Killing the database process does not help, because the rollback starts again on server startup.

要尽量减少发生此问题的可能性：To minimize the chance of this issue occurring:

要消除失控回滚，请增加缓冲池，使回滚成为 CPU 密集型并快速运行，或者终止服务器并使用innodb_force_recovery=3重新启动，如第 17.18.2 节“InnoDB”中所述。To get rid of a runaway rollback once it occurs, increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or kill the server and restart with innodb_force_recovery=3, as explained in Section 17.18.2, “InnoDB Recovery”.

如果您能够承受发生意外退出时丢失一些最新提交的事务，则可以将 innodb_flush_log_at_trx_commit 参数设置为 0。 InnoDB 无论如何都会尝试每秒刷新一次日志，尽管不能保证刷新。If you can afford the loss of some of the latest committed transactions if an unexpected exit occurs, you can set the innodb_flush_log_at_trx_commit parameter to 0. InnoDB tries to flush the log once per second anyway, although the flush is not guaranteed.

当修改或删除行时，行和关联的撤消日志不会立即物理删除，甚至在事务提交后也不会立即删除。旧数据将被保留，直到较早启动或并发的事务完成为止，以便这些事务可以访问已修改或已删除行的先前状态。因此，长时间运行的事务可以防止 InnoDB 清除由其他事务更改的数据。When rows are modified or deleted, the rows and associated undo logs are not physically removed immediately, or even immediately after the transaction commits. The old data is preserved until transactions that started earlier or concurrently are finished, so that those transactions can access the previous state of modified or deleted rows. Thus, a long-running transaction can prevent InnoDB from purging data that was changed by a different transaction.

当在长时间运行的事务中修改或删除行时，其他使用 READ COMMITTED 和 REPEATABLE READ隔离级别如果读取相同的行，则必须做更多工作来重建较旧的数据。When rows are modified or deleted within a long-running transaction, other transactions using the READ COMMITTED and REPEATABLE READ isolation levels have to do more work to reconstruct the older data if they read those same rows.

当长时间运行的事务修改表时，来自其他事务的对该表的查询不会使用覆盖索引技术。通常可以从二级索引检索所有结果列的查询，而不是从表数据中查找适当的值。When a long-running transaction modifies a table, queries against that table from other transactions do not make use of the covering index technique. Queries that normally could retrieve all the result columns from a secondary index, instead look up the appropriate values from the table data.

如果发现二级索引页的 PAGE_MAX_TRX_ID 太新，或者二级索引中的记录被删除标记，InnoDB 可能需要使用聚集索引查找记录。If secondary index pages are found to have a PAGE_MAX_TRX_ID that is too new, or if records in the secondary index are delete-marked, InnoDB may need to look up records using a clustered index.