在理想的情况下,希望使用单个链接,进行多行的数据。
插入需要的时间由以下几个因素决定,数字表示大概占比:
- 连接【3】
- 向服务器发送查询【2】
- 解析查询【2】
- 插入行【1 * 行大小】
- 插入索引【1 * 索引数】
- 关闭【1】
可以使用下面方法提高写入速度:
- 如果要同时从同一客户端插入许多行,请使用INSERT 具有多个VALUES列表的语句一次插入多个行。
如果要将数据添加到非空表,则可以增大 bulk_insert_buffer_size 变量以使数据插入更快。
- 加载文本或表格时,使用 LOAD DATA。这通常比使用INSERT语句快20倍。
- 对于有默认值的列,仅当要插入的值与默认值不同时才明确插入。这减少了MySQL必须执行的解析,并提高了插入速度。
InnoDB表的批量数据加载
可以使用下面的方式,提高插入速度
- 关闭自动提交模式,因为它会在每次插入时对磁盘执行日志刷新
SET autocommit=0;
... SQL import statements ...
COMMIT;
SET autocommit=1;
- 关闭唯一性检查来加快表的导入
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
对于大表,这可以节省大量磁盘I/O,因为 InnoDB可以使用将change buffer批量写入辅助索引中。但需要确保数据不包含重复的键值。
- 关闭外键检查来加快表导入
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
对于大表,这可以节省大量磁盘I/O
- 使用多value的写法
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
- 具有自动增量列的表中进行批量插入时,设置 innodb_autoinc_lock_mode为2
参考:15.6.1.6 AUTO_INCREMENT Handling in InnoDB
- 执行批量插入时,数据数据已根据主键列排序,那么插入会很快。
- 全文索引的表
在批量导入完数据后再创建全文索引
参考:https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html