8.5.5 InnoDBtable 的批量数据加载
这些性能提示补充了有关在第 8.2.4.1 节“优化 INSERT 语句”中快速插入的一般准则。
- 将数据导入
InnoDB
时,请关闭自动提交模式,因为它会为每个插入执行一次磁盘上的日志刷新。要在导入操作期间禁用自动提交,请在其周围加上SET autocommit和COMMIT语句:
SET autocommit=0;
... SQL import statements ...
COMMIT;
mysqldump选项--opt创建的转储文件可以快速导入到InnoDB
table 中,即使不使用SET autocommit和COMMIT语句包装它们也是如此。
- 如果您对辅助键具有
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 KEY
Sequences 插入行会更快。InnoDB
table 使用clustered index,这使得按PRIMARY KEY
的 Sequences 使用数据相对较快。对于不能完全容纳在缓冲池中的 table,按PRIMARY KEY
Sequences 执行批量插入尤为重要。 -
为了在将数据加载到
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
索引。