8.5.5 InnoDBtable 的批量数据加载

这些性能提示补充了有关在第 8.2.4.1 节“优化 INSERT 语句”中快速插入的一般准则。

  • 将数据导入InnoDB时,请关闭自动提交模式,因为它会为每个插入执行一次磁盘上的日志刷新。要在导入操作期间禁用自动提交,请在其周围加上SET autocommitCOMMIT语句:
SET autocommit=0;
... SQL import statements ...
COMMIT;

mysqldump选项--opt创建的转储文件可以快速导入到InnoDBtable 中,即使不使用SET autocommitCOMMIT语句包装它们也是如此。

  • 如果您对辅助键具有UNIQUE约束,则可以通过在导入会话期间暂时关闭唯一性检查来加快 table 导入的速度:
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

对于大 table,这可以节省大量磁盘 I/O,因为InnoDB可以使用其更改缓冲区来批量写入辅助索引记录。确保数据不包含重复的密钥。

  • 如果 table 中具有FOREIGN KEY约束,则可以通过在导入会话期间关闭外键检查来加快 table 导入:
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

对于大 table,这可以节省大量磁盘 I/O。

  • 如果需要插入许多行,请使用多行INSERT语法减少 Client 端与服务器之间的通信开销:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;

该技巧适用于插入到任何 table 中,而不仅仅是InnoDB个 table。

  • 在具有自动增量列的 table 中进行批量插入时,请将innodb_autoinc_lock_mode设置为 2,而不是默认值 1.有关详细信息,请参见第 14.6.1.6 节“ InnoDB 中的 AUTO_INCREMENT 处理”

  • 执行批量插入时,以PRIMARY KEYSequences 插入行会更快。 InnoDBtable 使用clustered index,这使得按PRIMARY KEY的 Sequences 使用数据相对较快。对于不能完全容纳在缓冲池中的 table,按PRIMARY KEYSequences 执行批量插入尤为重要。

  • 为了在将数据加载到InnoDB FULLTEXT索引中时获得最佳性能,请遵循以下步骤:

  • 在 table 创建时定义类型为BIGINT UNSIGNED NOT NULL的列FTS_DOC_ID,并使用名为FTS_DOC_ID_INDEX的唯一索引。例如:

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=latin1;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
  • 将数据加载到 table 中。

  • 加载数据后创建FULLTEXT索引。

Note

在创建 table 时添加FTS_DOC_ID列时,请确保在更新FULLTEXT索引列时更新FTS_DOC_ID列,因为FTS_DOC_ID必须与每个INSERTUPDATE单调增加。如果选择不在 table 创建时添加FTS_DOC_ID并让InnoDB为您 ManagementDOC ID,则InnoDB将在下一个创建全文索引调用中将FTS_DOC_ID添加为隐藏列。但是,这种方法需要重建 table,这会影响性能。