MySQL 5.7-8.5.3 Optimizing InnoDB Read-Only Transactions

InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only.

InnoDB可以避免为已知的只读事务设置事务ID (TRX_ID字段)所带来的开销。

A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE.

事务ID仅用于可能执行写操作或锁读的事务，如SELECT…为更新。

Eliminating unnecessary transaction IDs reduces the size of internal data structures that are consulted each time a query or data change statement constructs a read view.

消除不必要的事务id可以减少每次查询或数据更改语句构造读视图时所查询的内部数据结构的大小。

InnoDB detects read-only transactions when:

InnoDB在以下情况下检测只读事务:

The transaction is started with the START TRANSACTION READ ONLY statement. In this case, attempting to make changes to the database (for InnoDB, MyISAM, or other types of tables) causes an error, and the transaction continues in read-only state:

事务由START transaction READ ONLY语句启动。在这种情况下，试图更改数据库(对于InnoDB、MyISAM或其他类型的表)会导致错误，并且事务继续处于只读状态:

 You can still make changes to session-specific temporary tables in a read-only transaction, or issue locking queries for them, because those changes and locks are not visible to any other transaction.

您仍然可以在只读事务中更改特定于会话的临时表，或者对它们发出锁定查询，因为这些更改和锁对任何其他事务都是不可见的。

The autocommit setting is turned on, so that the transaction is guaranteed to be a single statement, and the single statement making up the transaction is a “non-locking” SELECT statement. That is, a SELECT that does not use a FOR UPDATE or LOCK IN SHARED MODE clause.

打开自动提交设置，以保证事务是一条语句，而组成事务的语句是一条“非锁定”SELECT语句。也就是说，一个不使用FOR UPDATE或LOCK IN SHARED MODE子句的SELECT。

The transaction is started without the READ ONLY option, but no updates or statements that explicitly lock rows have been executed yet. Until updates or explicit locks are required, a transaction stays in read-only mode.

事务在没有READ ONLY选项的情况下启动，但是还没有执行显式锁定行的更新或语句。在需要更新或显式锁之前，事务保持在只读模式。

Thus, for a read-intensive application such as a report generator, you can tune a sequence of InnoDB queries by grouping them inside START TRANSACTION READ ONLY and COMMIT, or by turning on the autocommit setting before running the SELECT statements, or simply by avoiding any data change statements interspersed with the queries.

因此,报告等操作应用程序生成器,您可以调整一系列InnoDB查询通过把他们放进START TRANSACTION READ ONLY和COMMIT,或通过将autocommit设置在运行SELECT语句之前,或者只是通过避免任何数据变化穿插的查询语句。

For information about START TRANSACTION and autocommit, see Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

Note

Transactions that qualify as auto-commit, non-locking, and read-only (AC-NL-RO) are kept out of certain internal InnoDB data structures and are therefore not listed in SHOW ENGINE INNODB STATUS output.

符合自动提交、非锁定和只读(AC-NL-RO)的事务被排除在某些InnoDB内部数据结构之外，因此不会在SHOW ENGINE InnoDB STATUS输出中列出。