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

MySQL大批量数据导入速度太慢的处理方法大致分为三种,通过修改MySQL配置增加insert操作的效率,利用MySQL load data命令进行数据导入,或者借助mysqlimport导入数据。

修改MySQL配置

关闭binlog(临时)

避免写入日志

--shell-* 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进行大批量数据插入操作时,就会变的非常慢,MySQL提高insert into速度的方法从以下几个方面入手。

修改bulk_insert_buffer_size

如果数据库中的数据已经很多(几百万条),那么可以加大mysql配置中的bulk_insert_buffer_size,这个参数默认为8M,可以设置为

bulk_insert_buffer_size=100M

使用insert delayed into

改写所有insert into语句为insert delayed into,insert delayed不同之处在于:立即返回结果,后台进行insert处理。

insert多条数据

--sql-* insert into table values('11','11'),('22','22'),('33','33')...;

使用into outfile 和 load data infile导入导出备份数据

使用LOAD DATA INFILE命令

LOAD DATA INFILE的输入文件,可以是来源自SELECT INTO OUTFILE命令。LOAD DATA INFILE语句以非常快的速度,从文本文件中读取行到表中,注意by后面跟的分隔符,可以是自定义的,取决于文件中的内容。

--shell--> load data infile "/data/mysql/e.sql" into table e fields terminated by ',';

使用SELECT INTO OUTFILE

> select * from e into outfile "/data/mysql/e.sql";
或
> select * into outfile "/data/mysql/e.sql" from e;

使用mysqlimport

MySQL不仅提供了load data,还提供了一个可执行文件mysqlimport,这两个命令可以从文件中把格式化的数据导入数据库,如果有一个包含大批量数据的文件,借助这个工具能够实现快速导入的目标。

mysqlimport可以看作是为LOAD DATA INFILE SQL语句提供了一个命令行界面,大部分的参数选项都是一致的,数据源可以是SELECT into outfile导出的文件,用mysqlimport进行导入。

--shell-- mysqlimport -u root -pPassword [--local] dbname filename.sql [OPTION]

以上就是MySQL数据导入时的简单配置方法,加速需要各方面条件的联合支持,才能最大化导入速度。