使用batch insert解决MySQL的insert吞吐量问题-程序员宅基地
最近使用了一个非常简单易用的方法解决了业务上的一个insert吞吐量的问题，在此总结一下。
首先我们明确一下，insert吞吐量其实并不是指的IPS（insert per second），而是指的RPS（effect rows per second）。
其次我们再说一下batch insert，其实顾名思义，就是批量插入。这种优化思想是很基本的，MySQL中最出名的应用就是group commit。
简单的来说，就是将SQL A 变成 SQL B
SQL A : insert into table values （$values）; SQL B : insert into table values ($values),($values)...($values);
下面，我们来看看这种异常简单的改动会带来什么样子的变化。
测试环境交代：单id的表结构，10w个int values，本地使用socket连接mysql server，使用shell单进程测试。
首先，我们看下使用SQL A将10w个int values插入到test表中所需的耗时，耗时1777秒。
real 29m37.090s user 9m11.705s sys 5m0.762s
然后，我们看下使用SQL B（每次insert，插入10 values）将10w个int values插入到test表中所需的耗时，耗时53秒
real 0m53.871s user 0m19.455s sys 0m6.285s
这是整整近33倍的时间提升。这部分性能提升的原因在于以下几点：
1、每次和MySQL server建立连接都需要经过各种初始化、权限认证，语法解析等等多个步骤，需要消耗一定的资源。
2、更新一个values和更新n个values耗时基本一致。（下面对比一下insert 单values核insert 10 values的profile耗时）
单values：
+------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | starting | 0.000056 | | checking permissions | 0.000010 | | Opening tables | 0.000034 | | System lock | 0.000010 | | init | 0.000011 | | update | 0.000061 | | Waiting for query cache lock | 0.000003 | | update | 0.000015 | | end | 0.000003 | | query end | 0.000053 | | closing tables | 0.000009 | | freeing items | 0.000021 | | logging slow query | 0.000002 | | cleaning up | 0.000003 | +------------------------------+----------+
10 values： +------------------------------+----------+ | Status | Duration | +------------------------------+----------+ | starting | 0.000061 | | checking permissions | 0.000008 | | Opening tables | 0.000027 | | System lock | 0.000008 | | init | 0.000012 | | update | 0.000073 | | Waiting for query cache lock | 0.000003 | | update | 0.000010 | | end | 0.000008 | | query end | 0.000053 | | closing tables | 0.000010 | | freeing items | 0.000021 | | logging slow query | 0.000002 | | cleaning up | 0.000003 | +------------------------------+----------+
但是，是否values积攒的越多，效率越高吗？ 答案自然是否定的，任何优化方案都不会是纯线性的，肯定会在某个条件下出现拐点。
我们按照不同的values number进行测试，分别为1、10、50、100、200、500、1000、5000、10000.
从下图我们可以看出，随着values number的增加，耗时先是急剧下降，从1777s变成53s，然后在增加values number就不会有太大的变化，直到values number超过200，最后的10000个values number耗时达到了2分钟。
从下图我们可以看到随着values numbers的增加，QPS（蓝线）先是猛增，然后下降，最终小于1/s。而RPS（绿线）随着增加猛增到一个高level，然后随着增加逐步下降，超过5000个values number之后开始急剧下降。
另，最关键的是，QPS最高峰和RPS的最高峰并不在同一个values number下，也就是说QPS最高的时候并不代表着insert的吞吐量就最高。
在我这个简单测试场景中，values number最合适的值是50，和单values对比，耗时减少97%，insert吞吐量提升36倍。
而这个值和表结构和字段类型及大小都有关系。需要根据不同的场景进行测试之后才可以得出，但是普遍来说，50-100是比较推荐的考虑值。
至于这个如何实现，只要前端写入的时候加入队列即可，可以按照2个条件进行合并
- 队列中积攒到n个values number后在写入数据库，优点是性能最高，缺点是时间不可控，有可能等到第n个需要n秒，这时候业务已经不可接收了。
- 队列中积攒1s之后，有多少个就写入多少个，优点是时间可控，缺点就是values number数目不可能，高并发的情况，可能1s已经积攒上千个values了。
- 最优的方案其实是2个条件同时起作用，即进行个数效验，也进行时间效验，无论达到那个条件都触发后续写数据库操作。
总结：
1、使用batch insert可以提高insert的吞吐量。
2、叠加的values number需要根据实际情况测试得出。
3、同时使用个数和时间控制阀值。
附简单测试的记录值：
| ValuesNum |
Time |
QPS |
Rows |
| 1 |
1777 |
56 |
56 |
| 10 |
53 |
188 |
1886 |
| 50 |
49 |
40 |
2040 |
| 100 |
50 |
19 |
2000 |
| 200 |
51 |
10 |
1960 |
| 500 |
57 |
3 |
1754 |
| 1000 |
60 |
2 |
1666 |
| 5000 |
69 |
0.3 |
1449 |
| 10000 |
133 |
0.07 |
751 |
智能推荐
Linux】date命令用法详解（-程序员宅基地
文章浏览阅读445次。显示时间是个常用的命令，在写shell脚本中也经常会用到与日期相关文件名或时间显示。无论是linux还是windows下都是date命令。Linux下date命令用法date [OPTION]… [+FORMAT]date [-u|--utc|--universal] [MMDDhhmm[[CC]YY][.ss]]date命令参数-d, –dat..._linux date
sourcetree 修改密码-程序员宅基地
文章浏览阅读511次。关于记住 sourcetree 还是使用老密码的问题 记录一下mac 找到应用程序 ->使用工具 ->钥匙串访问 找到对应的密码删除掉 重新拉取或者推送的时候 重新输入一遍新密码即可
计算机专业学习浅谈_编程卓越之道 pdf-程序员宅基地
文章浏览阅读973次。摘自：http://kb.cnblogs.com/page/186346/ 一、广泛了解，从科普书籍开始 选择计算机专业的同学，也许是因为原先有一定的基础，也许是因为一时的激情，但更多的人，可能对自己的选择没有深刻的认识，或多或少对计算机专业有一些神秘的感觉。自己究竟是否喜欢这个学科？才华能够在哪个分支领域有所施展？抑或是真的不适合这个专业？诸多疑问，解决的方法_编程卓越之道 pdf
安装openssl ERROR:POD document had syntax errors at /usr/bin/pod2man line 71_openssl pod2man 未找到-程序员宅基地
文章浏览阅读8.9k次。installing man1/cms.1cms.pod around line 457: Expected text after =item, not a numbercms.pod around line 461: Expected text after =item, not a numbercms.pod around line 465: Expected text after _openssl pod2man 未找到
Git-集中式VS分布式_git概念-集中式vs分布式-程序员宅基地
文章浏览阅读269次。集中式版本控制系统_git概念-集中式vs分布式
起步X5开发HTML5通过ajax调用WebService服务，调用的两种方式SOAP和Result_h5如何调用webservice-程序员宅基地
文章浏览阅读8.6k次。// 服务的地址var wsUrl = 'http://192.168.1.101:9080/bdposback/webServicesPOS/restBase/addUser2';var jsonStr;var ajaxData = '{"name": "acb"}';$.ajax({"type" : "post","async" : false,// "dataTy_h5如何调用webservice
随便推点
Java性能调优_java 性能调优-程序员宅基地
文章浏览阅读731次。Java性能调优调优步骤：衡量系统现状、设定调优目标、寻找性能瓶颈、性能调优、衡量是否到达目标(如果未到达目标，需重新寻找性能瓶颈）、性能调优结束。寻找性能瓶颈性能瓶颈的表象：资源消耗过多、外部处理系统的性能不足、资源消耗不多但程序的响应速度却仍达不到要求。资源消耗：CPU、文件IO、网络IO、内存。外部处理系统的性能不足：所调用的其他系统提供的功能或数据库操作的响应速度不够。资源消耗..._java 性能调优
内核配置中的：Multifunction device drivers-程序员宅基地
文章浏览阅读4.1k次。Multifunction Device Drivers(多功能设备Linux下驱动开发）先来一个英文简介：Multifunction devices embed several functions (e.g. GPIOs, touchscreens, keyboards, current regulators, power management chips, etc...
Android 禁止Edittext弹出系统软键盘 的几种方法_android 禁用软键盘-程序员宅基地
文章浏览阅读4k次。第一种方法：在XML文件下添加：android:focusable="true" android:focusableInTouchMode="true"第二种方法：直接关闭输入法在onCreate中加上：getWindow().setSoftInputMode(WindowManager.LayoutParams.SOFT_INPUT_STATE_ALWAYS_..._android 禁用软键盘
如何利用TNAS创建虚拟磁盘？_swdtnas-程序员宅基地
文章浏览阅读694次。您可以将NAS设备或服务器的iSCSI Target 挂载到TNAS作为虚拟磁盘（Virtual Disk），以此来扩充TNAS的储存容量。TNAS 最多支持挂载8个虚拟磁盘。1.要为TNAS挂载虚拟磁盘，在执行该操作前需要在其它NAS设备或服务器上先创建好iSCSI Target；2.添加虚拟磁盘，前往TOS桌面-控制面板–存储管理-虚拟磁盘；3.启用虚拟磁盘；4.创建虚拟磁盘；5.输入iSCSI Target服务器的IP地址，点击获取iSCSI LUN，点击下一步（在执行该操作前需要在_swdtnas
day-20: 安装软件包及rpm yum的介绍-程序员宅基地
文章浏览阅读40次。系统下是如何安装软件：windows下是.exe的下一步安装法：那linux如何安装软件呢：7.1:linux安装软件包的方法：rpm yum 源码包.2-7.3 rpm工具rpm包介绍（准备工作）挂载系统安装盘镜像（挂载到一个空目录，此处/mnt/目录未使用，所以直接挂载到/mnt/目录下）[root@adai002~]#df-h文件系统容...
Android Studio Gradle Already disposed Module-程序员宅基地
文章浏览阅读313次。Question：Failed to complete Gradle execution. Cause: Already disposed: Module: 'MYMODULENAME’Solution:注意，这是IDEA/AS导致的问题，不断地gradlew clean | Build > Clean | Build > Rebuild没有用，只会浪费时间根本原因在于部分...