批量导入数据到InnoDB表速度优化

1、使用Load data；

2、

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

3、

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

4、

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

5、

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

6、

 set innodb_autoinc_lock_mode to 2 instead of the default value 1

 

time mysql -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile 'infile.txt' into table load_test fields terminated by '\t' lines terminated by '\n' (col1, col2);"

脚本来源： https://www.percona.com/blog/2008/07/03/how-to-load-large-files-safely-into-innodb-with-load-data-infile/