MySQL快速导入/插入大量数据
2,774 total views, 1 views today
在工作中，经常遇到将大量数据插入、导入到MySQL数据库中，本文介绍如何高效完成导入、插入操作。
优化insert操作的速度，需要将多个小的操作合成一个大的操作。理想状态：只做一次连接；一次发送所有数据；延迟索引更新和一致性校验，直到insert结束再做。
insert操作各部分占的比例：
|
1 2 3 4 5 6 |
Connecting: (3) Sending query to server: (2) Parsing query: (2) Inserting row: (1 × size of row) Inserting indexes: (1 × number of indexes) Closing: (1) |
使用下列办法可以提高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。节省每次插入操作将日志刷到磁盘的操作。格式如下：
|
1 2 3 |
SET autocommit=0; ... SQL import statements ... COMMIT; |
这种方式所有数据插入后，才执行一次提交，节省了将日志刷到磁盘的资源。
在使用mysqldump导出数据时，加上–no-autocommit选项，这样在insert语句前后加入SET autocommit=0和commit。这样导入时，速度会提高不少。如，
|
1 |
mysqldump -uroot -p --hlocalhost --all-databases --no-autocommit >/tmp/all.dmp |
2、如果在二级索引是唯一索引，可以临时关闭唯一性校验，然后插入数据。确保导入的数据没有重复键值。
大表临时关闭唯一性校验，可以节省大量磁盘I/O。因为InnoDB可以批量将change buffer中的改变的数据写入到二级索引中。
|
1 2 3 |
SET unique_checks=0; ... SQL import statements ... SET unique_checks=1; |
3、临时关闭外键。如果表上有外键，临时关闭外键校验，然后再执行插入操作。节省了I/O。
|
1 2 3 |
SET foreign_key_checks=0; ... SQL import statements ... SET foreign_key_checks=1; |
4、使用Insert后面跟着多个VALUE的方式。减少了客户端和服务器的通信成本。在本文开始时，Connecting: (3)操作的比例占到了3，不小的开销。
|
1 |
INSERT INTO yourtable VALUES (1,2), (5,5), ...; |
该操作同样适用于其他存储引擎。
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列，在该列上创建唯一键
|
1 2 3 4 5 6 7 |
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=latin1; CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID); |
b.加载数据到表中
c.数据加载完毕，创建全文索引
|
1 |
CREATE TABLE fulltext_sample(copy TEXT,FULLTEXT(copy)) TYPE=InnoDB/MyISAM; |
参考：
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