MYSQL开发性能研究——INSERT,REPLACE,INSERT-UPDATE性能比较

时间:2022-07-23 19:42:16

一、为什么要有这个实验

我们的系统是批处理系统,类似于管道的架构。而各个数据表就是管道的两端,而我们的程序就类似于管道本身。我们所需要做的事情无非就是从A表抽取数据,经过一定过滤、汇总等操作放置到B表。如果出现了错误,那么就从重新跑这一个管道。所以说,我们的系统其实根本就不要什么事务性,无非就是挂了把表给TRUNCATE(或者有条件地DELETE)一下,然后重跑就行了。

这样一来,对于select语句就相对比较容易,基本上不需要做JOIN操作。然而对于写操作就有一些要求。比如说,需要处理主键重复(可能之前跑挂了,现在需要重跑,到底是提示错误呢,还是做个REPLACE或者UPDATE)等等问题。

在引入了MYSQL之后,我们发现MYSQL在SQL语句层面就提供了对于类似问题的解决。包括了INSERT,REPLACE,INSERT-ON-DUPLICATE的操作。具体的说明请查看这里。唯一需要注意的是INSERT-ON-DUPLICATE这个操作,在UPDATE里面的VALUES的含义是INSERT列表里的那个固定值,如果需要引用数据表中原来的值,还是直接使用列名即可,无需用VALUES包装一下。

 

 

二、实验准备

我仍然是采用了在我们这里可能用到的最大的表,该表有近200个字段。实验环境也和上一篇文章中的一样。有了那篇文章中的比较,我就直接使用了10条多行插入的方法,也是每5000条提交一次。为了做个比较,我特意制作了一个传统的INSERT-UPDATE操作。该操作先进行INSERT插入动作,然后检查输出,如果是出现了“主键重复”的错误,那么直接调用UPDATE语句,用相同的数据替换那行(就是直接原值覆盖)。注意,这种办法是没有办法做到多行插入的。

同样,为了让场景更加真实。我在同一个MYSQL服务上创建了三个数据库,其中都创建了该表。而且所有的操作都直接针对该三张表进行。我在代码里使用的工具是我自己写的一个类库。通过多线程连接到多库(一库一连接)然后主线程向所有线程发送一句INSERT/REPLACE/INSERT-UPDATE/INSERT-ON-DUPLICATE-KEY命令,等待所有线程都返回继续向下。所有的COMMIT操作都是线程主动根据AFFECTED ROWS的累积量自己选择做。

再强调一下,机器很烂,TPS没有意义。只是看个趋势。

 

三、实验结果

说明:

  • 多行INSERT空表——使用”INSERT INTO … VALUES (..), (..), (..), … “的方式往一张空表里面插入数据。
  • INSERT-UPDATE——在上一步骤的基础上,该操作先进行INSERT插入动作(一条一条INSERT),然后检查错误输出,如果是出现了“主键重复”的错误,那么直接调用UPDATE语句,用相同的数据替换那行(就是直接原值覆盖)。
  • 多行REPLACE空表——使用“REPLACE INSERT INTO … VALUES (..), (..), (..), … ”的方式往一张空表里面插入数据。
  • INSERT-DUPLICATE——使用 INSERT INTO .. VALUES (..), (..), (..), … ON DUPLICATE KEY UPDATE …”的语法在上一步骤的基础上进行操作。

MYSQL开发性能研究——INSERT,REPLACE,INSERT-UPDATE性能比较

 

结论如下:

  • 对于空表操作,REPLACE的性能和INSERT的差不多,但是他还有一个额外的好处,就是可以进行覆盖操作。这就给了我们一点提示,如果我们真的不用去关心DUPLICATE KEY错误,而且希望做到覆盖效果,那么使用REPLACE真心不错;如果不用关心DUPLICATE KEY错误,同时也不想要覆盖,那么INSERT IGNORE更好。
  • 传统的INSERT-UPDATE方式真心慢,理解起来也不复杂,送过去-返回来-再送过去-再返回来。还是改用INSERT-ON-DUPLICATE-KEY-UPDATE吧。