MySQL 10w+数据 insert 优化

川长思鸟来 2021-11-16 14:10 296阅读 0赞

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi05NWE1OGUwZjg0ZjRiN2Y0P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

由于业务原因，遇到了如题所述的业务问题，事务执行时间在30s~50s 不等，效果非常不理想

方案1. jdbc批处理

5w+ 数据测试，分别使用了mybatis insert()()（拼接xml）, mybatis的批处理和 jdbc的批处理。

可以看到在jdbc执行时间方面是差不多的，但是在方法执行时间上，批处理要稍微快了一些，但是还是不理想

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNDFiMDk2NTFmOTNmZGFmP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

5w+ 数据测试1

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi02NjM1YmZiZGNiM2M2OTBmP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

5w+ 数据测试2

方案2. 优化MySQL 参数

修改 my.ini

**innodb\_buffer\_pool\_size :**

> InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
> 
> row data. The bigger you set this the less disk I/O is needed to
> 
> access data in tables. On a dedicated database server you may set this
> 
> parameter up to 80% of the machine physical memory size. Do not set it
> 
> too large, though, because competition of the physical memory may
> 
> cause paging in the operating system. Note that on 32bit systems you
> 
> might be limited to 2-3.5G of user level memory per process, so do not
> 
> set it too high.

Innodb的缓冲池会缓存数据和索引，设置的越大访问表中的数据所需的磁盘I/O就越少。

修改innodb\_buffer\_pool\_size = 512M测试一下效率，这速度简直感人！

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNTg4ZThlMDgzZTNmZWI3P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

10w+数据测试1

**innodb\_log\_buffer\_size :**

> The size of the buffer InnoDB uses for buffering log data. As soon as
> 
> it is full, InnoDB will have to flush it to disk. As it is flushed once per second anyway, it does not make sense to have it very large
> 
> (even with long transactions).

表示InnoDB写入到磁盘上的日志文件时使用的缓冲区的字节数，默认值为8M。当缓冲区充满时，InnoDB将刷新数据到磁盘。由于它每秒刷新一次，所以将它设置得非常大是没有意义的 (即使是长事务)。

**innodb\_log\_file\_size :**

> Size of each log file in a log group. You should set the combined size
> 
> of log files to about 25%-100% of your buffer pool size to avoid
> 
> unneeded buffer pool flush activity on log file overwrite. However,
> 
> note that a larger logfile size will increase the time needed for the
> 
> recovery process.

该值越大，缓冲池中必要的检查点刷新活动就会越少，节省磁盘I/ O。但是越大的日志文件，mysql的崩溃恢复就越慢

设置上述两个参数innodb\_log\_file\_size=64M innodb\_log\_buffer\_size=16M，效率提升的并不明显。

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi1lODcxNjlkYTA5Y2EyMDQzP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

10w+数据测试2

总结

数据量大时，批处理在方法执行时间上要比 mybatis xml拼接快一点 （批处理只编译一条SQL，而拼接的方式SQL会很长）

性能瓶颈优化还是要从数据库下手，目前来看MySQL 大数据量时很依赖 innodb\_buffer\_pool\_size （缓冲池）

参考

https://my.oschina.net/realfighter/blog/368225

***扩展阅读***

[教你88秒插入1000万条数据到mysql数据库表][88_1000_mysql]

[MySQL每秒57万的写入，带你飞~][MySQL_57]

[在一个千万级的数据库查寻中，如何提高查询效率？][Link 1]

[设计抗住千万级流量的架构思路][Link 2]

[6月份Github上最热门的Java开源项目！][6_Github_Java]

作者：殷天文

来源：http://www.jianshu.com/p/09689e6cc2d6

![aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNzczNzFjYzJhZGQ4ZGQ1P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw][]

[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi05NWE1OGUwZjg0ZjRiN2Y0P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/5bfa5a3e72604a94b3511315501d55c4.png
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNDFiMDk2NTFmOTNmZGFmP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/6a5c65a151124843b09e1e30d03eac55.png
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi02NjM1YmZiZGNiM2M2OTBmP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/58ee96a0937945b2b3ed335ecc546ff7.png
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNTg4ZThlMDgzZTNmZWI3P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/c2d3dbf701e7447da6a43a9ce8b727f4.png
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi1lODcxNjlkYTA5Y2EyMDQzP2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/0da6f2721131437285c94ca22e1875eb.png
[88_1000_mysql]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247485856&idx=2&sn=740e8c89d4eb15f7c09a1c36aa0ed9de&chksm=fe795878c90ed16e41b6bece1ac71cbaff50a980098ba565c5328330649b5bbc9cad1ba83145&scene=21#wechat_redirect
[MySQL_57]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247485926&idx=1&sn=8a99195ff79e36ccfeed335270b05412&chksm=fe79583ec90ed12833ca813bbe7695572a7e3e480a20b6282d918f30a6d95b295a1cca69a189&scene=21#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247484151&idx=3&sn=14ce9b08e5f01520eb5a445d88d31aaf&chksm=fe79532fc90eda39ef6b14efa98a434971c2d31119e6e3e4990de22e6bc17edccb572ee32f9c&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247486097&idx=3&sn=d8ecc33466f61eb7e00b438720215153&chksm=fe795b49c90ed25f6203e6ca92aac7bbc983d8173137e7c055788c3bb5efe4b2abd6fe92a8d7&scene=21#wechat_redirect
[6_Github_Java]: http://mp.weixin.qq.com/s?__biz=MzU5NTAzNjM0Mw==&mid=2247486846&idx=2&sn=d28228db68019d062b8ef72779053414&chksm=fe795ca6c90ed5b0150dc88d6059f18ac8c00dbb263b67a9f8702edf2bc415e050bce0339591&scene=21#wechat_redirect
[aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNDI2NjYwMi0zNzczNzFjYzJhZGQ4ZGQ1P2ltYWdlTW9ncjIvYXV0by1vcmllbnQvc3RyaXAlN0NpbWFnZVZpZXcyLzIvdy8xMjQw]: /images/20211116/221c44161cce456ba1ebaa4a141cadcf.png