InnoDB表批量插入数据
这些性能提示补充了“优化INSERT语句”中有关快速插入的一般准则。
将数据导入时
InnoDB
,请关闭自动提交模式,因为它会在每次插入时对磁盘执行日志刷新。要在导入操作期间禁用自动提交,请在其周围加上SET autocommit
ndCOMMIT
语句:SET autocommit=0;...SQL import statements ...COMMIT ;该mysqldump的选项
--opt
创建这样的快速导入到转储文件InnoDB
表,即使没有与他们包装SET autocommit
和COMMIT
报表。如果
UNIQUE
对辅助键有限制,则可以通过在导入会话期间暂时关闭唯一性检查来加快表的导入:SET unique_checks=0;...SQL import statements ...SET unique_checks=1;对于大表,这可以节省大量磁盘I / O,因为
InnoDB
可以使用其更改缓冲区批量写入辅助索引记录。确保数据不包含重复的密钥。如果
FOREIGN KEY
表中有约束,则可以通过在导入会话的持续时间内关闭外键检查来加快表导入:SET foreign_key_checks=0;...SQL import statements ...SET foreign_key_checks=1;对于大表,这可以节省大量磁盘I / O。
INSERT
如果需要插入许多行,请使用多行语法来减少客户端和服务器之间的通信开销:INSERT INTO yourtableVALUES (1,2), (5,5), ...;该技巧适用于插入到任何表中,而不仅限于
InnoDB
表。- 在具有自动增量列的表中进行批量插入时,设置
innodb_autoinc_lock_mode
为2(交错)而不是1(连续)。有关详细信息,请参见“ InnoDB中的AUTO_INCREMENT处理”。 - 执行批量插入时,按
PRIMARY KEY
顺序插入行会更快。InnoDB
表使用聚集索引,这使得使用数据顺序相对快速PRIMARY KEY
。PRIMARY KEY
对于不能完全放入缓冲池中的表,按顺序执行批量插入尤为重要。 为了在将数据加载到
InnoDB
FULLTEXT
索引中时获得最佳性能,请遵循以下步骤:FTS_DOC_ID
在创建表时定义类型为的列BIGINT UNSIGNED NOT NULL
,并使用名为的唯一索引FTS_DOC_ID_INDEX
。例如:CREATE TABLE t1 ( FTS_DOC_ID BIGINTunsigned NOT NULLAUTO_INCREMENT , title varchar(255) NOT NULLDEFAULT '', text mediumtext NOT NULL,PRIMARY KEY (`FTS_DOC_ID`) )ENGINE =InnoDBDEFAULT CHARSET =utf8mb4;CREATE UNIQUE INDEX FTS_DOC_ID_INDEXon t1(FTS_DOC_ID);- 将数据加载到表中。
FULLTEXT
加载数据后创建索引。
注意
FTS_DOC_ID
在创建表时添加列时,请确保在FTS_DOC_ID
更新FULLTEXT
索引列时更新该列,因为FTS_DOC_ID
必须与INSERT
或单调增加UPDATE
。如果您选择不在FTS_DOC_ID
表创建时添加,并InnoDB
为您管理DOC ID,InnoDB
则将FTS_DOC_ID
在下一次CREATE FULLTEXT INDEX
调用时将其添加为隐藏列。但是,此方法需要重建表,这将影响性能。