MySQL插入性能优化 原 荐

栏目: 数据库 · 发布时间: 5年前

内容简介:标签: 博客[TOC]可以从如下几个方面优化MySQL的插入性能。

MySQL插入性能优化

标签: 博客

[TOC]

可以从如下几个方面优化 MySQL 的插入性能。

代码优化

values 多个

即拼接成一个insert values sql, 例如

INSERT INTO MyTable
  ( Column1, Column2, Column3 )
VALUES
  ('John', 123, 'Lloyds Office'), 
  ('Jane', 124, 'Lloyds Office'), 
  ('Billy', 125, 'London Office'),
  ('Miranda', 126, 'Bristol Office');

一个事务

开启一个事务,批量操作完了才提交事务,而不是,操作一次就提交一次,这样io太高,插入太慢。

插入字段尽量少,尽量用默认值

注意事项: max_allowed_packet 默认是1M,如何insert values sql 太大需要上调这个值

关闭 unique_checks

优化效果不是很明显,下面截图 选自 《MySQL 数据库开发、优化与管理维护 第2版》书籍 MySQL插入性能优化 原 荐

bulk_insert_buffer_size

这个参数只能对 MyISAM使用,innodb无效

配置优化

innodb_buffer_pool_size 缓冲区配置

什么是 innodb_buffer_pool_size

MySQL 缓存表数据,索引数据的地方。增加它的值可以减少 磁盘 io ,提升 读写性能。 提升读的原理 :因为 buffer_pool_size 设置的比较大, 很多表数据和索引已缓存到 buffer pool , 要查询的数据在缓存中找到了,就不需要访问磁盘了。读性能就得到了提升。 提升写的原理 :因为 buffer_pool_size 设置的比较大, 写的数据,暂时以脏页的方式放在内存,然后慢慢落到磁盘,如果buffer_pool_size 太小就没办法缓存写操作,写一次访问一次磁盘 ,写入性能就比较慢。(实际自测增大buffer_pool_size后,并未带来纯写操作的性能提升, 这块有待进一步研究)

设置多大的 innodb_buffer_pool_size 合适?

通常将innodb_buffer_pool_size其配置为物理内存的50%到75%

相关参数设置

innodb_buffer_pool_instances

一般将 innodb_buffer_pool_size 值增大后,需要增加配置 innodb_buffer_pool_instances 的值。

innodb_buffer_pool_instances 是 buffer_pool 实例数量,默认为1。增加它的值,可以减少数据库内部的资源竞争,增加并发处理能力。

如何设置innodb_buffer_pool_instances?innodb_buffer_pool_instances 的范围是 1 (the default) up to 64 (the maximum). 可以将 innodb_buffer_pool_instances 的个数设置为 buffer pool size 的 十分之一, 比如 innodb_buffer_pool_size 是 30g ,那 innodb_buffer_pool_instances 就设置为 3;

innodb_buffer_pool_size 注意事项

因为有额外内存的使用,如果指定 innodb_buffer_pool_size 为 12g 实际占用内存可能是 14g +

事务日志配置

innodb_log_file_size

默认值 48MB 设置的太小 :比如用默认值48MB,当一个日志文件写满后,innodb会自动切换到另外一个日志文件,而且会触发数据库的检查点(Checkpoint),这会导致innodb缓存脏页的小批量刷新,会明显降低innodb的性能。

设置的太大:设置很大以后减少了checkpoint,并且由于redo log是顺序I/O,大大提高了I/O性能。但是如果数据库意外出现了问题,比如意外宕机,那么需要重放日志并且恢复已经提交的事务,如果日志很大,那么将会导致恢复时间很长。甚至到我们不能接受的程度。

总结:innodb_log_file_size设置得太小无法释放数据库性能,设置得太大,会增加宕机后日志重放恢复的时间。

innodb_log_files_in_group

重做日志组中的日志数量,默认值是2 ,一般用默认值也可以。

innodb_log_buffer_size

将日志写入磁盘日志文件前的缓冲大小,默认值 8MB,一般用默认值也可以。

读写线程增加

合理增加 innodb_write_io_threads,innodb_read_io_threads 两个配置的值即可。

实践比较

环境:centos 7, MySQL 6.7 , 8G,i5 操作:jmeter 64个并发插入数据,每个并发插入320条数据,每条数据插入前都会随机查询一次数据库。

优化前的配置

innodb_buffer_pool_size = 134217728 //128MB
innodb_buffer_pool_instances = 1

优化后的配置

innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1

性能结果buffer_pool_size 128MB:耗时58s buffer_pool_size 1GB:耗时172s

结论

buffer pool 缓冲区增加8倍内存, 换来 3 倍 读性能提升,3 倍 含读的写性能提升(含读的写,指的是在insert 前,进行了数据库查询,将查得的数据赋值给了 insert 字段), 对于纯写几乎没有性能提升。同理,如果缓冲区增加5.3倍内存,理论可以提升2倍性能提升。

硬件优化

最影响数据库性能的是磁盘 io,上 ssd 可以大大提升性能,其次是 cpu,内存 。

架构优化

读写分离,降低单机io的压力。


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

算法设计与应用

算法设计与应用

迈克尔 T. 古德里奇(Michael T. Goodrich)、罗伯特·塔马契亚(Roberto Tamas / 乔海燕、李悫炜、王烁程 / 机械工业出版社 / 2017-11-20 / CNY 139.00

本书全面系统地介绍算法设计和算法应用的各个领域,内容涵盖经典数据结构、经典算法、算法分析方法、算法设计方法以及算法在各个领域的应用,还包含一些高级主题。本书采用应用驱动的方法引入各章内容,内容编排清晰合理,讲解由浅入深。此外,各章都附有巩固练习、创新练习和应用练习三种类型的题目,为读者理解和掌握算法设计和应用提供了很好的素材。 本书可作为高等院校计算机及相关专业“数据结构和算法”课程的本科生......一起来看看 《算法设计与应用》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

URL 编码/解码
URL 编码/解码

URL 编码/解码

MD5 加密
MD5 加密

MD5 加密工具