Mysql 插入百万条数据

发布于: 2020 年 07 月 13 日
Mysql插入百万条数据

前言

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

1.生成sql文件

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

2.进行sql插入

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

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

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

3.生成批量插入sql文件(Python3)

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

4.批量sql插入

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

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

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

5.更换引擎

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

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

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

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

>微信搜索:【Java小咖秀】专注Java领域,更是一个有温度的公众号 

用户头像

Java小咖秀

关注

公众号:Java小咖秀,专注Java相关领域。 2020.06.18 加入

公众号【Java小咖秀】,回复“面试”白嫖一份博主Java面试题。 个人网站:https://www.javaxks.com。

评论 (1 条评论)

发布
用户头像
很详细,感谢分享~
2020 年 07 月 13 日 09:48
回复
没有更多了
Mysql插入百万条数据