MySQL :: MySQL 8.4 参考手册 :: 10.5.3 优化 InnoDB 只读事务

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 10 优化 / 10.5 InnoDB 表的优化 / 10.5.3 优化 InnoDB 只读事务

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

10.5.3 优化 InnoDB 只读事务10.5.3 Optimizing InnoDB Read-Only Transactions

InnoDB可以避免为已知只读的事务设置事务 ID（TRX_ID字段）相关的开销。仅可能执行写入操作的事务或锁定读取（例如SELECT ... FOR UPDATE）才需要事务 ID。消除不必要的事务 ID 可以减少每次查询或数据更改语句构造读取视图时所参考的内部数据结构的大小。InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only. A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE. 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.

InnoDB 在以下情况下检测只读事务：InnoDB detects read-only transactions when:

事务以 START TRANSACTION READ ONLY 语句。在这种情况下，尝试对数据库（对于 InnoDB、MyISAM 或其他类型的表）进行更改会导致错误，并且事务会继续处于只读状态：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:
```
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
```
您仍然可以在只读事务中更改特定于会话的临时表，或对其发出锁定查询，因为这些更改和锁定对任何其他事务都不可见。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.
开启autocommit设置，这样保证事务是单个语句，组成事务的单个语句是“非锁定” SELECT 语句。也就是说，不使用 SELECT 或 LOCK IN SHARED MODE 子句的 FOR UPDATE。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.
事务在没有 READ ONLY 选项的情况下启动，但尚未执行显式锁定行的更新或语句。在需要更新或显式锁定之前，事务将保持只读模式。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.

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以及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.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.

有关 START TRANSACTION 和 autocommit，请参阅第 15.3.1 节“START TRANSACTION、COMMIT 和 ROLLBACK 语句”。For information about START TRANSACTION and autocommit, see Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

Note

事务符合自动提交、非锁定和只读 (AC-NL-RO) 的条件不包含在某些内部 InnoDB 数据结构中，因此不会在 SHOW ENGINE INNODB STATUS 输出中列出。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.

首页上一级下一节