使用batch insert解决MySQL的insert吞吐量问题

时间：2022-03-14 19:31

最近使用了一个非常简单易用的方法解决了业务上的一个insert吞吐量的问题，在此总结一下。

首先我们明确一下，insert吞吐量其实并不是指的IPS（insert per second），而是指的RPS（effect rows per second）。

其次我们再说一下batch insert，其实顾名思义，就是批量插入。这种优化思想是很基本的，中最出名的应用就是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 |
+------------------------------+----------+