mysql快速插入百万条数据

作者：在赤道吃冰棍儿

www.jianshu.com/p/36b87cb3a05a

前言

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

1.生成sql文件

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

图1

图2

2.进行sql插入

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

图3

图4

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

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

3.生成批量插入sql文件（Python3）

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

图5

图6

4.批量sql插入

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

图7

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

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

5.更换引擎

图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。

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

为什么老外不愿意用 MyBatis？
8 种最坑的 SQL 错误用法，第一个最坑
为什么大家都说SELECT * 效率低
Java电商平台 - API 接口设计之 token、timestamp、sign 具体架构与实现
好文章，我在看