insert和update的一些性能对比测试

这是学习笔记的第 1933 篇文章

  今天在做中间件的测试时，突然想到之前的一些思路也可以借鉴一下，这块的内容还是比较有意思，简单剧透一下，如何把drop操作转换为alter,如何把alter操作转换为DML操作，其实都是设计中的一些经验法则，而今天要聊的是另外一个，如何把一个update转换为一条insert.

先说下问题的背景，最近在测试一个业务时，业务的并发很高，我们在应用架构改造中，根据模型的特点，最终对事务做了降维处理，本来这种操作和改进带来的收益是很明显的，结果在压测的时候就碰到了明显的问题，程序的逻辑是

1)根据id查看对应的userid是否存在

2.1)如果存在进行update,

2.2)如果不存在进行insert.

看起来这个逻辑已经很清晰了，但是在程序高并发的情况下，在第1步到第2步之间产生了一些并发带来的副作用，假设线程1步骤1的判断userid不存在，正在insert的瞬间，线程2也进行了同样的insert操作，在毫秒级别就会出现主键冲突，这种情况相对还是比较频繁的，从业务架构上，其实可以有更好的解决方案，而在数据库层面能做些什么呢。 

其实我们也可以想一些办法，MySQL里面有两种类型的语句可以支撑这种动态的逻辑，一种是replace一种是insert on duplicate。

那么这两种方式既然功能是类似的，总得有些差别吧。

总体来说从功能上，replace的原理的最高代价是delete+insert，而insert on duplicate的最高代价是select+update,两者的大家看起来是类似的，相对来说更推荐是on duplicate。

insert on duplicate的方式其实只涉及一条DML,而且从索引的维护角度来看，在基于主键的条件下，其实是不需要索引维护的，而replace操作在delete+insert本质是两条DML，从索引的角度维护索引的代价要高一些。

而insert on duplicate的方式在存在userid的情况下所做的update逻辑和单纯的update性能如何呢？

我们可以写个程序来模拟测试一下。 

首先我们可以导出一份数据列表，假设我们存在一个文件 data.txt

生成insert on duplicate语句

cat data.txt| head -100000|awk -F, '{print "insert into dbo_grw_matchtime(userid,grwid,value,moddate,crtdate) values(",$1","$2-1","$3",'\''"$4"'\'','\''"$5"'\'') on duplicate key update value=values(value),moddate=now();"}'>tmp_data.localhost2

生成update语句

cat data.txt |tail  -100000|awk -F, '{print "update dbo_grw_matchtime set value="$3"-1,'moddate=\''"$4"'\'','crtdate=\''"$5"'\'' where userid="$1  " and  grwid="$2" ;"}' >tmp_data.localhost3

经过多次测试，insert on duplicate key的结果在10万数据量下，基本稳定在99秒，而update则稳定在89秒，在10万量级下，这个差异是10秒钟，我们来换算一下。

update的方式1毫秒能写入1.1行数据，而insert on duplicate的方式基本是持平的，在1毫秒。

>select 100000/89/1000;

+----------------+

| 100000/89/1000 |

+----------------+

|     1.12359551 |

+----------------+

>>select 100000/99/1000;

+----------------+

| 100000/99/1000 |

+----------------+

|     1.01010101 |

+----------------+

相差的这0.1行数据其实是很低的比例，同时update操作是和select的逻辑同时存在的，如果按照这个业务场景，insert on duplicate的性能要明显优于update.

后续也会对线上系统的压力进行更细致的测试，可以持续关注。