MySQL加速load数据

# 关闭binlog

mysql> set sql_log_bin = 0;

# 关闭唯一性检查

mysql> set unique_checks=0; 

# 关闭外键检查

mysql> set foreign_key_checks=0; 

# 调整myisam引擎参数

set session bulk_insert_buffer_size=256217728;

set session myisam_sort_buffer_size=256217728;

set global key_buffer_size=256217728;

mysql> use database;

mysql> alter table {table} engine=myisam;

# 导入指令

mysql> load data infile '/data.txt' into table {table} fields terminated by ',' lines terminated by '\n' ignore 1 lines;

# 导入完毕后进行恢复

mysql> set sql_log_bin = 1;

mysql> set unique_checks=1;

mysql> set foreign_key_checks=1; 

mysql> alter table {table} engine=innodb;