文档章节

MySQL插入性能优化

蒋先生66
 蒋先生66
发布于 2019/01/17 20:41
字数 1185
阅读 8K
收藏 206

阿里云携手百名商业领袖、技术大咖,带您一探行进中的数字新基建!>>>

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版》书籍 image.png-345.2kB

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:耗时172s
  • buffer_pool_size 1GB:耗时58s

结论

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

硬件优化

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

架构优化

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

配置优化参考文章

https://mp.weixin.qq.com/s/DjM4jl5v0IleXLl_QFoyOw https://github.com/jdaaaaaavid/mysql_best_configuration

© 著作权归作者所有

蒋先生66
粉丝 13
博文 23
码字总数 12409
作品 0
成都
程序员
私信 提问
加载中

评论(9)

蒋先生66
蒋先生66 博主

引用来自“sxgkwei”的评论

引用来自“蒋先生66”的评论

引用来自“zhuliu”的评论

缓冲区扩大了10倍,时间少了一半 这是算值还是不值

这个要看情况了,我们是代码层已优化完毕,sql该调优的调优了,再增加内存提升性能就有价值。而不是一来就加内存那样是不对的。
在我印象里,优化这些数据库啊之类的,一般情况下,只做到查,并增加索引就行了。其它,还是多查查自己项目中的烂代码比较靠谱。。。。烂代码太多才是导致性能上不去的根本。

哈哈哈,是的
sxgkwei
sxgkwei

引用来自“蒋先生66”的评论

引用来自“zhuliu”的评论

缓冲区扩大了10倍,时间少了一半 这是算值还是不值

这个要看情况了,我们是代码层已优化完毕,sql该调优的调优了,再增加内存提升性能就有价值。而不是一来就加内存那样是不对的。
在我印象里,优化这些数据库啊之类的,一般情况下,只做到查,并增加索引就行了。其它,还是多查查自己项目中的烂代码比较靠谱。。。。烂代码太多才是导致性能上不去的根本。
蒋先生66
蒋先生66 博主

引用来自“zhuliu”的评论

缓冲区扩大了10倍,时间少了一半 这是算值还是不值

这个要看情况了,我们是代码层已优化完毕,sql该调优的调优了,再增加内存提升性能就有价值。而不是一来就加内存那样是不对的。
rushA
rushA
mysql有6.7?是5.7吧
zhuliu
zhuliu
缓冲区扩大了10倍,时间少了一半 这是算值还是不值
蒋先生66
蒋先生66 博主

引用来自“enzozhong”的评论

good,就是mysql6.7没有用过😂
😎
蒋先生66
蒋先生66 博主

引用来自“mickelfeng”的评论

性能结果 buffer_pool_size 128MB:耗时58s buffer_pool_size 1GB:耗时172s 是不有问题
写反了,感谢指正
mickelfeng
mickelfeng
性能结果 buffer_pool_size 128MB:耗时58s buffer_pool_size 1GB:耗时172s 是不有问题
罗马钟
good,就是mysql6.7没有用过😂
MYSQL优化有理有据全分析(面试必备)

   目标   · 了解什么是优化   · 掌握优化查询的方法   · 掌握优化数据库结构的方法   · 掌握优化MySQL服务器的方法   什么是优化?   · 合理安排资源、调整系统参数使M...

java进阶架构师
2019/01/09
0
0
MySql5.6性能优化学习笔记

目标  了解什么是优化  掌握优化查询的方法  掌握优化数据库结构的方法  掌握优化MySQL服务器的方法 什么是优化?  合理安排资源、调整系统参数使MySQL运行更快、更节省资源。  ...

知止内明
2018/06/28
0
0
【转】MYSQL优化有理有据全分析

本文为转载,只作学习备份,原创: 瘦斌 java进阶架构师 目标 · 了解什么是优化 · 掌握优化查询的方法 · 掌握优化数据库结构的方法 · 掌握优化MySQL服务器的方法 什么是优化? · 合理安...

osc_7hvnvgh9
2019/02/17
4
0
1000W 数据插入到数据库要多久

看了 社区的大牛们都在说 千万级别的数据 在各个数据库中的 速度和性能 心里痒痒的 自己没有做过你那么大的项目 最多的纪录才 300万 实在是说不上话 想想何不自己试一试 插入 1千万的数据 具...

李永波
2012/07/29
1W
29
Explain 执行计划 和 SQL优化

Explain 介绍 在分析查询性能时,考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。exp...

osc_ymlf86ez
2018/10/31
4
0

没有更多内容

加载失败,请刷新页面

加载更多

什么是 PL/SQL? 怎么用?

PL/SQL 1.什么是PL/SQL? PL/SQL(Procedure Language/SQL)是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支/条件、循环、变量、类型等),使SQL语言具有过程处...

煌sir
51分钟前
109
0
dayjs时间处理库基本使用

Day.js 是一个轻量的 JavaScript 时间日期处理库,与 Moment.js 的 API 设计保持一致。 本文只介绍了一些常用操作,关于国际化、插件、自定义等高级内容详见官方文档。 其主要特性如下: 与 ...

whoru
54分钟前
11
0
Delphi xe使用TJSONObject解析JSON数据

在Delphi 10 Seattle中重写 “ 使用TJSONObject分析JSON数据 ”。 由于不推荐使用某些方法,因此已对其进行了更改。 要使用TJSONObject,请添加“ System.JSON”。 uses System.JSON; 使用T...

simpower
今天
11
0
树莓派使用 OLED 屏显示图片及文字

树莓派默认是不带显示屏的,如果想要查看系统的一些信息,需要使用电脑登录到树莓派,或者通过 HDMI 连接外接显示器查看。这样做总是有点麻烦,我们可以通过外接一个 OLED 屏来显示一些关键参...

良许Linux
今天
13
0
BIO学习

1. BIO介绍 同步并阻塞,服务器实现模式为一个连接一个线程,即客户端有连接请求时服务器端就需要启动一个线程进行处理,如果这个连接不做任何事情会造成不必要的线程开销,当然可以通过线程...

steven-黄笑笑
今天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部