• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • InnoDB表批量插入数据

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

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

      SET autocommit=0;... SQL import statements ...
      COMMIT;
      

      mysqldump的选项--opt创建这样的快速导入到转储文件InnoDB表,即使没有与他们包装SET autocommitCOMMIT报表。

    • 如果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 KEYPRIMARY 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调用时将其添加为隐藏列。但是,此方法需要重建表,这将影响性能。