MySQL快速导入/插入大量数据

在工作中,经常遇到将大量数据插入、导入到MySQL数据库中,本文介绍如何高效完成导入、插入操作。

优化insert操作的速度,需要将多个小的操作合成一个大的操作。理想状态:只做一次连接;一次发送所有数据;延迟索引更新和一致性校验,直到insert结束再做。

insert操作各部分占的比例:

使用下列办法可以提高insert速度:

  • 使用insert语句后面跟着多个VALUES列表的形式,这种形式比insert后面跟一个VALUE高效的多。如果向一张非空表添加数据,调整bulk_insert_buffer_size变量,使之插入更快。bulk_insert_buffer_size在每个thread上生效,如果设为0,表示不开启该功能。默认值为8M。
  • 加载text文本,使用 LOAD DATA FILE方式。它比INSERT 语句方式快了约20倍。
  • 如果列上有默认值,插入的值和默认值不同才考虑执行INSERT操作。这样可以减少解析时间,提高插入速度。

 

下面着重介绍InnoDB表和MyISAM表插入数据,如何提高速度。

一、InnoDB表快速导入/插入大量数据

1、关闭autocommit。节省每次插入操作将日志刷到磁盘的操作。格式如下:

这种方式所有数据插入后,才执行一次提交,节省了将日志刷到磁盘的资源。
在使用mysqldump导出数据时,加上–no-autocommit选项,这样在insert语句前后加入SET autocommit=0和commit。这样导入时,速度会提高不少。如,

2、如果在二级索引是唯一索引,可以临时关闭唯一性校验,然后插入数据。确保导入的数据没有重复键值。
大表临时关闭唯一性校验,可以节省大量磁盘I/O。因为InnoDB可以批量将change buffer中的改变的数据写入到二级索引中。

3、临时关闭外键。如果表上有外键,临时关闭外键校验,然后再执行插入操作。节省了I/O。

4、使用Insert后面跟着多个VALUE的方式。减少了客户端和服务器的通信成本。在本文开始时,Connecting: (3)操作的比例占到了3,不小的开销。

该操作同样适用于其他存储引擎。

5、如果批量插入的表使用auto-increment 自增长列,设置innodb_autoinc_lock_mode为2,默认值为1。
原理:http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-handling.html

6、如果数据按照主键的顺序排列,那么批量插入速度很快。

7、全文索引表大数据Insert的最优办法
a. 创建表加上FTS_DOC_ID列,在该列上创建唯一键

b.加载数据到表中
c.数据加载完毕,创建全文索引

参考:
http://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html

二、MyISAM表快速导入/插入数据

MyISAM存储引擎使用的越来越少,这里就不做过多的介绍。
需要的请去官网查看 : http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注