mysql快速插入百万条数据-技术圈

作者：在赤道吃冰棍儿
www.jianshu.com/p/36b87cb3a05a

假设现在我们要向mysql插入500万条数据，如何实现高效快速的插入进去？暂时不考虑数据的获取、网络I/O、以及是否跨机操作，本文将在本地进行数据的插入，单纯从mysql入手，把优化到底。

首先我们来生成500万条inster的sql文件，图1为生成sql文件的程序，运行大概需要25s左右，采用Python3实现，生成的文件如图2。

图1

图2

这里我写了个简单的sh，进行mysql的导入,请看图3，我里面的密码用的环境变量，大家写的时候写自己的密码就行，经过了漫长的运行，才插入完成，请看图4，让人吐血的运行时间。

图3

图4

看到图4，运行了两个多小时，才插入了500万条数据???，显然是不能接受的，如果我要插入三五千万条数据岂非要睡上好几觉了。优化必须走起来.....

500万条数据，频繁的磁盘I/O操作，插入效率缓如蜗牛。我们来试试批量插入，先来减少磁盘I/O操作。

在这里，我的批量插入式一次性插入1000条数据，inster进行5000次就行，相当于5000次I/O操作，比第一次的操作数，大大降低，来看图5-6，为生成的文件。

图5

图6

为保证尽可能的准确性，两次插入的表结构，类型及内容都一致。

图7

看到图7的运行时间，才花了41秒，就插入了500万条数据，性能提升了近200倍左右，性能达到了量级提升。优化继续在路上.....

之前看到mysql的引擎对比，说在频繁批量插入时，MyIASM引擎比InnoDB引擎性能更好。我们来试试看???.....

图8

看到图8，我这边默认的引擎还是InnoDB。

如图9，我们执行命令：alter table batch_jq engine=MYISAM;进行更改引擎。（小知识点：mysql终端想清屏，可以使用system clear命令）

图9

图10

如图10，更改引擎后，只用了25秒就插入了500万条数据，性能又有了一个新的提升。我们在数据插入完成后，再将引擎更改回InnoDB即可。

扩展：

（1）如若插入海量数据，建议可以先不考虑建立索引，因为索引也是需要维护的，会降低插入性能，可以等插入完成后，再去建立索引。如若是MyISAM，可以忽略，因其延迟更新索引的特性，可以使插入性能大大提升（上述例子两个表，均未建立索引）。

（2）MySQL为了保证ACID中的一致性和持久性，使用了WAL。

Redo log就是一种WAL的应用。当数据库忽然掉电，再重新启动时，MySQL可以通过Redo log还原数据。也就是说，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新Redo log就足够了。相比写数据文件时的随机IO，写Redo log时的顺序IO能够提高事务提交速度。

在没有开启binlog时，Redo log的刷盘操作将会是最终影响MySQL TPS的瓶颈所在。为了缓解这一问题，MySQL使用了组提交，将多个刷盘操作合并成一个，如果说10个事务依次排队刷盘的时间成本是10，那么将这10个事务一次性一起刷盘的时间成本则近似于1。

有什么问题请留言，大家一起探讨学习???。

好文章，我在看