MySQL加速导入数据

MySQL导入数据时,加快导入速度的方法。

修改配置

关闭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 OUTFILE

select * 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]