mysql导入1000万数据有什么方法_面试题-批量向MySQL导入1000万条数据有什么方法？...

批量向MySQL导入数据

直接导入

使用存储过程循环拼接

使用load data infile

修改ENGINE=InnoDB为MyISAM(v5.1之前是MyISAM，之后是InnoDB)

减少IO次数

SQL写法优化(一条SQL语句插入多条数据)

合理设置批量大小(在事务中进行插入处理，不要一条数据提交一次，肯定要分批处理)

尽量顺序插入(减少索引的维护压力)

合并事务+事务+有序数据的优化插入方式

注意SQL批量插入的大小必须合理

事务执行时间不要太长

合理设置MySQL相应配置参数，增加缓存或减少不必要日志磁盘读写

合并SQL语句

一条SQL插入一条数据

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');

insert into tb1(id,name,age,sex) values(2,'green',24,'w');

一条SQL插入多条数据

insert into tb1(id,name,age,sex) values(1,'khue',25,'m'),(2,'green',24,'w');

合并SQL语句能够提高程序的插入效率(原因在于合并后日志量减少了，降低日志刷盘的数据量和频率)，也能减少SQL语句解析的次数，减少网络传输的IO

在事务中插入处理

将插入修改为

start transaction;

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');

insert into tb1(id,name,age,sex) values(2,'green',24,'w');

commit;

在事务中可以提高数据的插入效率，因为在进行一个insert操作时，MySQL内部会建立一个事务，在事务内才能进行真正的插入处理操作，这样可以减少创建事务的消耗，让所有插入都在执行后才进行提交操作

数据有序插入

数据有序插入是指插入记录在主键上是有序排列

无序插入：

insert into tb1(id,name,age,sex) values(3,'joo',26,'m');

insert into tb1(id,name,age,sex) values(5,'green'25,'w');

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');

有序插入：

insert into tb1(id,name,age,sex) values(1,'khue',25,'m');

insert into tb1(id,name,age,sex) values(3,'joo',26,'m');

insert into tb1(id,name,age,sex) values(5,'green'25,'w');

由于数据库在插入时，需要维护索引数据，无序的记录会增大维护索引的成本(参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，则需要B+tree进行分裂合并等操作，会消耗比较多的计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作)

测试

合并数据+事务的方法在较小数据量时，性能提高很明显；数据量较大时(1000万以上)，性能急剧下降，因为此时数据量超过了innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作；使用合并数据+事务+有序数据则表现良好，因为有序数据索引定位较为方便，无需频繁对磁盘进行读写操作

注意：

SQL语句是有长度限制的，在合并数据时，不能超过SQL长度限制(通过max_allowed_packet配置可以修改，默认1M)

事务也需要控制大小，事务过大会影响执行效率(MySQL有innodb_log_buffer_size配置项，超过值会把innodb数据刷到磁盘，此时效率会有所下降)

参考：

1.MySQL批量SQL插入各种性能优化