MySQL 10w+数据 insert 优化

作者：殷天文

https://www.jianshu.com/p/09689e6cc2d6

点击“蓝字”关注，一起燥起来!

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

方案1. jdbc批处理

5w+ 数据测试，分别使用了mybatis insert()()（拼接xml）, mybatis的批处理和 jdbc的批处理。
可以看到在jdbc执行时间方面是差不多的，但是在方法执行时间上，批处理要稍微快了一些，但是还是不理想

5w+ 数据测试1

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测试一下效率，这速度简直感人！

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，效率提升的并不明显。

10w+数据测试2

总结

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

• 1. 性能瓶颈优化还是要从数据库下手，目前来看MySQL 大数据量时很依赖 innodb_buffer_pool_size （缓冲池）

  
  

  参考

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

  （完）

  看完本文有收获？请转发分享给更多人