InnoDB表批量插入数据

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

• 将数据导入时InnoDB，请关闭自动提交模式，因为它会在每次插入时对磁盘执行日志刷新。要在导入操作期间禁用自动提交，请在其周围加上SET autocommit nd COMMIT语句：

  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 yourtable VALUES (1,2), (5,5), ...;
  

  该技巧适用于插入到任何表中，而不仅限于InnoDB表。

• 在具有自动增量列的表中进行批量插入时，设置innodb_autoinc_lock_mode为2（交错）而不是1（连续）。有关详细信息，请参见“ InnoDB中的AUTO_INCREMENT处理”。
• 执行批量插入时，按PRIMARY KEY顺序插入行会更快。InnoDB表使用聚集索引，这使得使用数据顺序相对快速PRIMARY KEY。PRIMARY KEY对于不能完全放入缓冲池中的表，按顺序执行批量插入尤为重要。

• 为了在将数据加载到InnoDBFULLTEXT索引中时获得最佳性能，请遵循以下步骤：

  1. FTS_DOC_ID在创建表时定义类型为的列BIGINT UNSIGNED NOT NULL，并使用名为的唯一索引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=utf8mb4;
     CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
     

  2. 将数据加载到表中。
  3. 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调用时将其添加为隐藏列。但是，此方法需要重建表，这将影响性能。