MySQL :: MySQL 8.4 参考手册 :: 10.5.5 InnoDB 表的批量数据加载

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 10 优化 / 10.5 InnoDB 表的优化 / 10.5.5 InnoDB 表的批量数据加载

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

10.5.5 InnoDB 表的批量数据加载10.5.5 Bulk Data Loading for InnoDB Tables

这些性能提示补充了第 10.2.5.1 节“优化 INSERT”中快速插入的一般准则语句”。These performance tips supplement the general guidelines for fast inserts in Section 10.2.5.1, “Optimizing INSERT Statements”.

将数据导入InnoDB时，关闭自动提交模式，因为它会为每次插入执行日志刷新到磁盘。要在导入操作期间禁用自动提交，请使用 SET autocommit 和 COMMIT 语句：When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:
```
SET autocommit=0;
... SQL import statements ...
COMMIT;
```
mysqldump 选项--opt 创建转储文件，这些文件可以快速导入到InnoDB表，即使没有使用 SET autocommit 和 COMMIT 语句。The mysqldump option --opt creates dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.
如果对辅助键有 UNIQUE 约束，则可以通过在导入会话期间暂时关闭唯一性检查来加快表导入速度：If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:
```
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
```
对于大表，这可以节省大量磁盘 I/O，因为 InnoDB 可以使用其更改缓冲区批量写入辅助索引记录。确保数据不包含重复键。For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.
如果您的表中有 FOREIGN KEY 约束，您可以通过在导入会话期间关闭外键检查来加快表导入速度：If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning off the foreign key checks for the duration of the import session:
```
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
```
对于大表，这可以节省大量磁盘 I/O。For big tables, this can save a lot of disk I/O.
如果需要插入多行，请使用多行INSERT语法来减少客户端和服务器之间的通信开销：Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:
```
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
```
此技巧对于插入到任何表中都有效，而不仅仅是InnoDB表。This tip is valid for inserts into any table, not just InnoDB tables.
当使用自动递增列，将 innodb_autoinc_lock_mode 设置为 2（交错）而不是 1（连续）。有关详细信息，请参阅第 17.6.1.6 节“InnoDB 中的 AUTO_INCRMENT 处理”。When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 (interleaved) instead of 1 (consecutive). See Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB” for details.
执行批量插入时，按 PRIMARY KEY 顺序插入行会更快。 InnoDB 表使用聚集索引，这使得按照PRIMARY KEY的顺序使用数据相对较快。对于不完全适合缓冲池的表，按 PRIMARY KEY 顺序执行批量插入尤其重要。When performing bulk inserts, it is faster to insert rows in PRIMARY KEY order. InnoDB tables use a clustered index, which makes it relatively fast to use data in the order of the PRIMARY KEY. Performing bulk inserts in PRIMARY KEY order is particularly important for tables that do not fit entirely within the buffer pool.
为了在将数据加载到 InnoDB FULLTEXT 索引时获得最佳性能，请遵循以下一组步骤：For optimal performance when loading data into an InnoDB FULLTEXT index, follow this set of steps:
1. 在创建表时定义列 FTS_DOC_ID time，类型为 BIGINT UNSIGNED NOT NULL，具有名为 FTS_DOC_ID_INDEX 的唯一索引。例如：Define a column FTS_DOC_ID at table creation time, of type BIGINT UNSIGNED NOT NULL, with a unique index named FTS_DOC_ID_INDEX. For example:
```
CREATE TABLE t1 (
    FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL DEFAULT '',
    text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
```
2. 将数据加载到表中。Load the data into the table.
3. 加载数据后创建 FULLTEXT 索引。Create the FULLTEXT index after the data is loaded.
Note
When adding FTS_DOC_ID column at table creation time, ensure that the FTS_DOC_ID column is updated when the FULLTEXT indexed column is updated, as the FTS_DOC_ID must increase monotonically with each INSERT或者UPDATE. If you choose not to add the FTS_DOC_ID at table creation time and have InnoDB manage DOC IDs for you, InnoDB adds the FTS_DOC_ID as a hidden column with the next CREATE FULLTEXT INDEX call. This approach, however, requires a table rebuild which can impact performance.When adding FTS_DOC_ID column at table creation time, ensure that the FTS_DOC_ID column is updated when the FULLTEXT indexed column is updated, as the FTS_DOC_ID must increase monotonically with each INSERT or UPDATE. If you choose not to add the FTS_DOC_ID at table creation time and have InnoDB manage DOC IDs for you, InnoDB adds the FTS_DOC_ID as a hidden column with the next CREATE FULLTEXT INDEX call. This approach, however, requires a table rebuild which can impact performance.
如果将数据加载到新 MySQL 实例中，请考虑使用ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG 语法。禁用重做日志记录有助于避免重做日志写入，从而加快数据加载速度。有关详细信息，请参阅禁用重做日志记录。If loading data into a new MySQL instance, consider disabling redo logging using ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG syntax. Disabling redo logging helps speed up data loading by avoiding redo log writes. For more information, see Disabling Redo Logging.
Warning
此功能仅用于将数据加载到新的 MySQL 实例中。 请勿在生产系统上禁用重做日志记录。允许在禁用重做日志记录时关闭并重新启动服务器，但在禁用重做日志记录时服务器意外停止可能会导致数据丢失和实例损坏。This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system. It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption.
使用 MySQL Shell 导入数据。 MySQL Shell 的并行表导入实用程序 util.importTable() 为大型数据文件提供快速数据导入到 MySQL 关系表的功能。 MySQL Shell 的转储加载实用程序 util.loadDump() 还提供并行加载功能。请参阅 MySQL Shell 实用程序。Use MySQL Shell to import data. MySQL Shell's parallel table import utility util.importTable() provides rapid data import to a MySQL relational table for large data files. MySQL Shell's dump loading utility util.loadDump() also offers parallel load capabilities. See MySQL Shell Utilities.

首页上一级下一节