修改配置
关闭binlog
避免写入日志set sql_log_bin = off;
导入完成还原配置set sql_log_bin=on
调整innodb_flush_log_at_trx_commit
加快数据刷盘速度。set global innodb_flush_log_at_trx_commit=2;
show VARIABLES like '%max_allowed_packet%';
set global max_allowed_packet=10485760;
批量insert
MySQL执行大批量insert操作时变慢时,提升速度的方法如下:
修改bulk_insert_buffer_size
若数据库已存大量数据(几百万条),可加大bulk_insert_buffer_size,默认8M,调整为:bulk_insert_buffer_size=100M
使用insert delayed into
改写所有insert into语句为insert delayed into,insert delayed不同之处在于:立即返回结果,后台执行insert。
insert多条数据insert into table values('11','11'),('22','22'),('33','33')...;
导入/出备份
使用LOAD DATA INFILE
LOAD DATA INFILE输入文件,可以是来自SELECT INTO OUTFILE命令。
LOAD DATA INFILE语句能快速从文本文件中读取数据行到表中load data infile "/data/mysql/e.sql" into table e fields terminated by ',';
注:by后面的分隔符,可以是自定义,取决于文件内容。
使用SELECT INTO OUTFILEselect * from e into outfile "/data/mysql/e.sql";
或
select * into outfile "/data/mysql/e.sql" from e;
mysqlimport
MySQL mysqlimport,能从文件中将格式化数据导入数据库,若有一个大批量数据的文件,此工具能够快速导入。
mysqlimport可看作是LOAD DATA INFILE SQL语句的命令行界面,大部分参数选项都是一致的,数据源可以是SELECT into outfile导出的文件。mysqlimport -u root -pPassword [--local] dbname filename.sql [OPTION]